Google Spreadsheets for Beginners

okay hello everyone and welcome to Google Spreadsheets for beginners this is Natalia Le Moyne and I'm the instructional technology specialist for the Kildonan Central School District I'm going to stay here for a couple more minutes and wait to see if anybody else is able to join I hear some other people are trying to join so we're going to give them a couple more minutes for them to join you you you you you you you you okay let's go ahead and get started and see if we can go ahead and get started over here I have someone else trying to join we'll give that person one more minute you okay I'm going to go ahead and get started so today's presentation is about Google Spreadsheets one of the main issues people encounter when switching to Google Apps is abandoning Microsoft Excel in favor of google spreadsheets because most of us use the spreadsheet to analyze simple lists of data google spreadsheets are actually superior to excel in many ways not to mention offering many of the same features so in today's workshop you're going to learn the following google spreadsheets vs excel how to create a Google spreadsheet some formatting tools that will help you make better spreadsheets and making your google spreadsheets automatic so they do the work for you so let's talk about Google Spreadsheets versus Microsoft Excel some of the advantages of Google Spreadsheets are that they are one single version at all times and Google main Google's main advantage revolves around collaboration if you are working on an excel sheet with multiple co-workers it will be frustrating time-consuming and even counterproductive to email several different versions of the same spreadsheet back and forth with Google you only need one version of the same spreadsheet that could be shared among a few collaborators or even your entire organization so let's see how we do that I'm going to go to my Google Drive and I'm actually going to pull up the spreadsheet that I have I share with you this spreadsheet on our event page so you can see this and play around with this data so I have my spreadsheet here and I am using the spreadsheet with other co-workers so to do that I can simply go to share the sharing menu will come up and notice that here at the bottom I see the invite people box I can start enter entering people's names here and I can give them certain rights they can edit if they're going to work with me or they can just comment or view so I'm going to leave it as edit I can send them a little message here if I wanted to by clicking here or just simply click send okay I hope you were listening to me I don't know if I had the microphone muted or nod but if I didn't to repeat things again sorry about that alright so today but you're going to learn is the Google Spreadsheets versus Excel and how to create a Google spreadsheet formatting tools and some tools that will help you make your spreadsheet automatic I was just saying that some of the advantages with google spreadsheets over the Microsoft Excel are the following Google spreadsheet has one single version at all times it is multi-user real-time collaboration is full-featured it's got revision history which is great when you make a mistake you can go back and revert to an older version of the same document you can edit view share from any device anytime anywhere it is important that we know our limitations so using Microsoft Excel is important if you are working with complex financial models sorting and editing large amounts of formatted data so if you're that type of user then I would suggest stick to excel that doesn't mean that all these new capacities will come to Google Spreadsheets soon they are working every day to make google spreadsheets better all right one of one of the main advantages revolves around collaboration on google spreadsheets so if you're working with on an excel sheet with multiple co-workers it could be frustrating time consuming and even counterproductive to email several different versions of the same spreadsheet back and forth so with Google you only need one version of the spreadsheet so that's why I was showing you this spreadsheet you only need one version of the same spreadsheet that could be shared among a few collaborators so I'll click on the share button here and then I'll add someone by inviting them over here and type in their email address mind you they do have to have a Google account and from here users can make any changes to the document to the same document in real time they can also chat if they wanted to or add a comment a specific cell spritzee and on that and all this without having to worry about sending back and forth different versions of the same document you can also monitor the progress and the changes if there's ever need to revert back to an older version by clicking on file the revision history that would show me what's done and I can revert to an older version by clicking here there it is building and formatting a Google spreadsheet is also very similar to the process in Excel with a number of tools available to customize a spreadsheet to fit your needs so for example if you look at the toolbar here on the top you'll see that over here you can change the text color you can change the cell color as well and you can do all these other formatting tools that I'm going to show you in a little bit if you ever need to take action upon data there are plenty of functions easily available on the menu and you will click that over here and the functions these are the most frequent used but you can search for more functions over here there is a list of different functions and their explanations also um Google spreads its just like the rest of Google apps are based completely in the cloud so you can view share and edit your files from any device anytime anywhere there are Google Drive apps for Android phones and iOS phones as well as tablets this is great for most of us but for those who are dealing with large amounts of data or complex functions may run into some issues one of the few downsides of using a cloud-based spreadsheet is large amounts of data that can be a bit slow to load and so users who need that functionality may be forced to stick with Excel but I have noticed that as teachers we don't deal with tons and tons of data especially formatted data maybe lists that are longer but not formatted and conditional data so that's why I think Google Spreadsheets a good fit for us so let's look at creating your your first spreadsheet let's go back to our presentations so the uses that you can have for Google Spreadsheets is organize a field trip for example as you can see here you can use spreadsheets to plan a budget you can use for its sheets to gather contact information you can use spreadsheets to calculate exercise statistics or create an invoice and there's so many other uses that you can I can't really think of all of them but I'm sure that you can as we go through this workout to come apart work us to this workshop together one of the things I wanted to share with you is how to create a Google spreadsheet so the first thing you're going to need is obviously a Google Apps account you're going to have to login into your Google account and you'll have to go to drive you're going to have to click on create you're going to click on spreadsheet and then you're going to name your spreadsheet by clicking the untitled spreadsheet on the top of the screen now let me show you what that looks like so I'm going to go back to my drive and I'm going to click on create and I'm going to click on spreadsheet this opens up a new window notice this and as of now is called untitled spreadsheet notice that I have to click here to rename this document I type the new name and click OK and there is name ok notice that I don't have to worry about clicking on file save because Google saves everything automatically in Google Drive it's about every 60 seconds it's saving it automatically so that's a good feature too the other thing that you can do here and let's look at the formatting tools here you have obviously file edit view insert format data tools this is something you just released this last week the add-ons and the Help button let's start by adjusting your column width you can do that by clicking on that line and dragging you add to make it bigger narrower or wider you can do the same here with the columns and with the rows now let's go back to a presentation let's learn some of the basic formatting tools from the tool bar we're also going to learn some filtering and sorting we're going to learn filter views freezing rows and columns conditional formatting and functions so let's get started with some basic formatting tools from the toolbar as a reminder I want to let you know that you can plug in your questions if you have any on the Q&A app on the events page now we'll be seeing those questions as you post them so let's start with the basic formatting tools from the toolbar I'm going to go back to my spreadsheet that I just created I can start simply adding data on to the spreadsheet by clicking on the cell and start typing any information for example I just created a couple of rows here actually hey address I would call those and then I can start plugging in the information by simply clicking on the cell and typing now many times we might want to fix the information that is inside of the cell and notice that for example this one the contact information the last few letters are outside of the margin so to do that what I want to do is click on the cell and then click on wrap text that's going to wrap the text inside this the cell so that it you can actually read it better also the vertical align which is this little tool sometimes you might want to bring the information on the cell to the very top of the cell or just the middle or just at the bottom notice that you can also change the borders and you can just leave either the vertical borders or just the outer borders colors over here for example I want first name with specific color last name I wanted with a different color and so on and so forth I can also change the text color if I wanted you to make it more appealing to those of who are very visual and say for example here I can go and change it to Red's for example like that I can change the font just like in any Word document and the size and the style of that font so those are some of the basic very basic formatting tools and now what we're going to do is we're going to move on to something a little more complex which is filtering and sorting so for filtering and sorting I'm going to go back to that spreadsheet so I can demo what I want to show you and go back to the spreadsheet that I already have with information so it is easier for me to show you so the filter feature in Google sheets makes it easier to analyze and view your data by hiding the data that you temporarily want to take out of the view so for example when you want all of your data to have to - you're searching for something in particular so then you want to filter your data so that you see the only what you want to see so for example I have a list of all the students in a school district and I have in this column is column F is the city where these students were born so say that I want to apply a filter to my spreadsheet because I want to filter this information and by city so I can simply select the whole spreadsheet by clicking on this square over here right below the function box and I can go to this little funnel shaped icon some people say look like a martini glass why not and we can click on filter that now I have the headers over here are have a little drop-down arrow here so I can now select sort this field or this column by 8 to Z or Z to a and I can actually select which cities I want to show since I don't want to show all of them and I just am interested in knowing who is born in Boston then I can clear them all and then just select Boston and then click OK and that's just going to show me all of the students that were born in Boston so I can with this filtered information I can also do something that is very cool in Google Spreadsheets which is called filter views so let's say that I want to save this for future reference or for quick reference so now that I have filtered my information I want to go back to next to the little funnel icon I want to click on the little arrow next to it and now I can click on save as filter view when I click here this window comes up and I can say I want to name this Boston and now I have a filter view called Boston so I don't have to filter the information again I just and simply go back to my filter views as Boston so now I want to name this as Boston Boston born and so now if I go here I'm sorry if I go to my filters now I can see it listed down here so I can quickly go back and reference this Boston born list I click on none and then I want go back to my whoops where you go turn off filter there we go so now if I want to go by to see the rest of my data I just simply click on the little funnel thing again to turn the filter off so I hope the stub is helpful I'm going to do that one more time so if I want to filter the information I simply click on the filter I could have selected my whole spreadsheet over here or just simply click on the little funnel icon here to turn on the filter as you turn it on notice that each of the headers and I didn't have any headers on this spreadsheet but each of the headers here have a little drop-down menu so for example I want to filter now these data by gender so I want to clear this all and I would just want to show all of the male students I'm gonna click OK and then I also want to show all of the male students that were born in Boston and click OK so that's going to narrow it down for me and again I can save this filter view and give it a different name notice that the one that I just did before is still here so if I want to turn this off and I just want to go back to my regular spreadsheet I just simply click on turn off the filter and there it is I can see the filter so I can see my whole spreadsheet now ok if you have any questions just make sure that you're posting them on the the Q&A app you okay so the next thing that I want to show you now is right here are sorting so I'm going to go back to my Google spreadsheet and I can simply select a column say for example I want to sort this is days that the students are present in this school so I want to sort this and I can simply select the column I want to store by clicking on it it noticed that it turns our blue and then I can click over here and I can sort the sheet or I can sort over here where's the damn sort the column a through Z so it's going to give me a obviously is going to be the lowest number Z is the higher number so there it is okay every time you do something to your spreadsheet it is automatically saved so if you want to revert back to what you've done before you can simply do the undo or go to the revision history over here okay alright the next thing I want to show you I recover filter views already and now the next thing I want to show you is freezing rows and columns so over here I'm just going to add a header here because for some reason I had it here before and I don't have it now insert type in here notice that I'm inserting a row on at the beginning of my spreadsheet by simply clicking insert row so this is student ID and this is student name is last name please fur this is the city gender grade-level days in I'm going to delete this column actually here I have a to mine's alright oops alright ok so now that I have some sort of headers here it might be easier to for me to show you this way so I'm going to show you how to phrase the rows on the column so I have my first row here and say that I have all these data and notice that as I move the data scroll up and down then I I lose my headers so I don't know what this information is or what those numbers are or what these numbers are so what I want to do is freeze this header row so that as I scroll I can still see and relate the information with the correct headers so to do that I simply go to right here there's the view and freeze rows and I'm going to freeze Row 3 is Row 1 so as I do that notice that there's a little gray line right below the number 1 the row so if I scroll down notice that that stays in place so that is easier for me to determine whether this is the last name or the first name or the student ID so that makes it really easy I can also do the same with the column so to unfreeze the row I simply go back to view and here is it freezing it again yep it's freezing it again okay so what we're going to do we're just going to on do there we go so now I don't see the gray bar over here so say for example I have an extended spreadsheet over here and I have lots of more information on these columns here and every time I move my information to continue I lose which student I'm talking about so I want to freeze this column over here so again I go to view freeze column so now I can scroll to the left and to the right and I can easily align the student ID with the rest of the information so that is freezing rows and columns so I'm going to just pop a quick question there to see if you're have any questions or to just clarify could you please tell me what do I need to do to freeze a row okay so I have Jennifer asking can you right-click and get any options yes so if I click on if you're talking about a a column I can right click and I can see these options over here I don't know if this is what you were talking about or not but my question was what do I need to do to freeze a row for example if you know the answer you can go ahead and type it in in the Q&A I'm going to demonstrate again without say anything you there it is okay so the next thing that we want to talk about is the conditional formatting now this is a very cool feature because it allows you to set certain conditions to each of the rows that you have on your spreadsheet now this is particularly very helpful helpful if you're working with other people on the same spreadsheet so let's see what that looks like conditional formatting rules help you by getting a visual summary of all the changing data that you have here so sometimes you keep plugging information on these spreadsheet and that starts changing and that it gets a little complicated to read all these data so you can format the cells too to change the text or the background colors if they meet certain conditions so for example if the cell cells contain certain words or number so let's see how that what that looks like so say for example that I want on this spreadsheet I want to make sure that every time that I see for example the students are the days that are in students attended school I want to select highlight the cell or the range of cells that you want to apply the formatting rules tool so I want to apply it to this whole column because this column tells me all the days in so one is selected by clicking on the column letter that selects the whole column and now I want to click on the format menu and I want to select conditional formatting so now with the conditional formatting window open I need to define the conditions for the specific text colors or cell background colors so if the text contains we're talking about numbers here because this is a column that has numbers so if the number is less than 170 days because we have 180 days and if they're list they can only mean something ten days so if less than 170 I want Google to do something I want to format the background and I want Google to apply the red color so I'm going to save that rule and boom now visual quick reference of all the students that have less than 170 days in school so that is conditional formatting and this rule will stay here even if collaborators start adding more students and plugging in these data then as soon as you start plugging it in it will change the background colors you can apply multiple rules and select up to five for a given cell or range and to say for example here I want to go back to the format and conditional formatting this is what I have so far if I don't I want to remove this I can simply click on the X to remove that or I can add another rule if I wanted to format this information even more so I'm going to cancel that's when you specify multiple rules they are evaluated in the order listed so something's going to happen first and then the second condition is going to be applied that that means that the first rule found to be true will define the format of the sale or range and if like if you copy and paste from a cell or a range that has formatting rules these rules will be applied when you paste the copy of data I don't think there are any rule limits per cell or range in the new Google sheets we used to have those before but not now and so that is so far now let's see we check on some other questions that you may be having okay I see under edit click on freeze row and pick how many to free so let's try that mmm I think you want to do that under view to freeze your rose your meaning choose more than just one you simply click on the shift button as you select and they are frozen now so if you wanted to freeze more than just one and if that answers your question you okay so I'm going to move on to the next thing that is functions with functions Google made it really easy with the functions especially because I'm not a very good person with math let's say so this was really easy for me so let's say that for example I want to apply a function to this information tab here so it seems like this is there's students average I'm going to choose name right here oops and let's say that I want to apply a rule sorry a function to to these information that I have here so what I'm going to do I'm just going to delete this one over here what I'm going to do is click on the next cell on the next column and I'm going to start simply by typing the equal sign to start adding a function I can do that or I can simply go here to the functions menu or little icon and I can click on the frequent ones which is sum average count max min there's some other functions here too so I'm going to do average for example so next school I feel let's go ahead and do so let's do some so if I click on that notice that it starts populating so I need to give the first value okay so I can simply go and click on the first value so say for example I want this put my first value and then I want to add my next value here put oops not that sorry for some reason I'm having a delay here I'm going to undo this you hmm and was their senior now but I'm having a huge huge delay here you you you Oh you you you getting this how about we revisit the functions in a little bit I'm going to undo this okay so we're going to go and move on to the next part of our workshop which was the making the Google Spreadsheets work for you so there's a couple of things that you can do to make it automatic and I think these options are really really good for us if you work with Google Spreadsheets one is that about validation notification rules and protected ranges so the data validation that's where we're going to see now so I'm going to go back to my spreadsheet and you can do the following with the with data validation this is important too when you're working with other people you can restrict what values are possible for sales that contain numbers dates and text you can also show people a warning if they enter invalid data so for example I have someone working with me on this same spreadsheet and now what I need to do is on this column over here at their email addresses so I want to make sure that anything that's entered on this column is actual an email address that I don't have to go back and check again that whoever input this information here did not make a mistake so I can tell Google to validate the data as they as the users type it in so or I can prevent them also from actually entering anything that is invalid in the first place so the first thing you want to do is click on the data menu and select validation you can also right-click a range of cells and select data validation so that's like another path to get there so right here and click on data validation you get to the same little window or dialog box and let's select your validation criteria so for example we want to select the cell range which is the sheet on l2 and the criteria would be a for example a number and it has to be between for example and now greater than has to be greater than 10 you can either show the warning or reject input and you can give them some help by and clicking here ok but I want to go back and see if I can get the text contains the valid email that's the thing that's what we're going to do so if the text is a valid email so we want to make sure that they get a warning or we can reject any but so the help button will be here enter a valid email with the @ symbol okay and I can click Save now I can start typing something here and notice that it gave me a little validation here if I hover over it gives me a hint so you for example so that is a valid data that I input there so this attempt to do something that is not correct you oops what happens okay was supposed to tell me that it wasn't correct oh that's because I put the tack off okay well let's try with dad without the dot you you oh wait a minute did I select a wholesale oh that's probably what happened I'm sorry about that so what I'm going to do is here data validation I'm not sure if I selected the whole thing yeah text contains the valid email okay all right now here we go I was just applying it to one cell and now I can see that it tells me that this is an invalid cell because I have this little red triangle on the top this one is okay this one is also incorrect because I just happen to use the whole column but that's what your users will see so they will have to come in here and fix this and s is fixed then they don't see the little red triangle so that is what that data validation is and now we're going to move on to something that is really cool that is called notification rules this is great when you're working with other people on the same spreadsheet and so for example if I want to make sure that I get every notification or anything that somebody has changed to this spreadsheet is notified to me I can set up automatic notifications and this is like again to find out when my peers have modified the spreadsheet or they have they've modified any sales or anything on the spreadsheet so and you can I can also choose how often I would like to be notified so what I need to do is go to the tools and select notification rules and in this window that appears we're going to select when and how often you want to receive the notifications so notify me and my email address when any changes are made or when someone submits the form and notify me when so say for example I want to know when anything has changed and I want you to email me either right away or daily digest which means at the end of the day you'll get a summary of everything that has been done to your spreadsheet so I can do that and then I can click Save then I can also add another notification rule if I wanted to or if I'm good with that I can leave it like that and automatically anything that is done to this spreadsheet you'll get a notification in your email so I'm going to click on done by the way you can do this also with the Google Docs okay so the next thing that I have here we're getting close to the end notification rules that was that and now we're going to move on to protected ranges now if you want to restrict your collaborators from editing specific sheets of your spreadsheet you can protect those sheets in Google Spreadsheets so which is great because sometimes it happened to me that I'm working with someone and they're changing the information on the wrong sheet or changing the information on the cells or columns that I do not need people changing so we can protect the sheet and we can protect the ranges so let's look at that let's look at protecting the sheep so notice that on your spreadsheet you have at the bottom here this is sheet number one and so what you need to do to protect this sheet say you don't want people to modify anything on this particular sheet but we could if we had other sheets on the same document or same workbook as is technically called the other users can modify anything over here on the other sheets so to protect the sheet I simply click on the sheet arrow and click on protect sheet and then I can get I can enter a description for this particular sheet you can either select a range for example or the whole sheet in this case and I can set some permissions so I'm going to call these for example born or City because I don't want people to modify the city so I'm going to go to the range and this city is my F column so I'm going to click on the F actually let's cancel that I'm going to click on the F again here and I'm going to protect it from here notice that I did this by clicking hovering over the litter of that column you see that little triangle if I click there I see the menu in ICP tech range so I have my range selected and I can see I can call it city and I can set certain permissions so I could let anyone work on this and this is the the one I share with you that's why it says anyone has the link and edit so you could be online right now and I've been eating this with me or I can add specific people and I can the people that I've shared this specifically I can either let them edit or just comment on this particular cell okay I'm going to hit done here so want to let that the other user change the information the neck I can click done okay so now I can see that there's a couple protective sheets or ranges and it's listed over here or I can add another one another protected sheet or range over here so I can close this and that is that okay I think we've cover some of the things that I have wanted to cover so I am done but I'm open for questions so I'm going to go back to our hangout and see if you have any questions you and if you have any questions feel free to post them on our Q&A app and I'll be happy to answer them you you you you you you okay so I have one question here that says when a document is shared with you can you view which ranges are protected so collaborators that are who try to edit a cell in a protected range and they don't have edit access they will receive an error message so they are able to see the cell but they're not able to see whether it is protected or not so when opening a spreadsheet with protected ranges individuals will see the ranges they don't have edit access to display with a checkered background sort of so they have sort of like a visual but you won't see like a like a notification box telling you that if the background pattern makes it difficult to read the spreadsheet content then you can hide the protected ranges by pointing your mouse to the View menu and unchecking the protected ranges so say for example if I want to do that I'm going to show you here so I have this was a protected range for the other person that I added so you won't see it like that but the other person would see this column right here would be grayed out or like checkered background so if say that person has lots of ranges or columns that are protected and it makes it difficult for that viewer or that editor to actually view the information on the spreadsheet so what that person can do is actually hide this protected ranges by pointing the mouse to the View menu let's see oh I won't see it because I'm not the person obviously but they will see here on this menu they will they will have to check protected ranges so in that way they will hide it so hope I answer the question let's see any other questions here you you you you okay so Jennifer it says here that you would like to use draw ioad dot Pro I'll have to research on that one a little bit I'm not sure about that one I'm not sure what you're trying to accomplish with that or is are you trying to draw something if you tell me what you want to do maybe I'll find I know a couple of good applications that you can use and I'm glad that you like a filter feature of the spreadsheet that is really good you you you you you okay so here's the thing if I don't have any other questions if I don't have any other questions what I'm going to do is I'm going to ask you to create a spreadsheet that you can share with me sort of like a documentation slash homework for these training that would serve as your so I can put you on my learning plan if you'd like to get the credit for this hour if you like to do that please feel free to create a spreadsheet and send it to me and I'm going to keep on waiting for some other questions over here since like my system is a little slow for some reason you you you you okay I wanted to show a visual of a floater but I don't need to there's a couple of new options yes you can do the homework later I'm looking another question but there's a couple of new options with the new spreadsheets and the new Google Docs that you can actually create beautiful flow charts on Google Docs right there on your Google Doc so if you're interested email me and I can come and show you how to do this but there's tons of little cute apps that can do that and they're really not complicated to make yes you can do the homework later and send it to me if you're interested in getting the credit if you did not register for in my learning plan this was a workshop through narak so let me know and I can register you for that one and if not then we are set you you you you you you you okay everyone thank you for watching and you don't forget that if you have any questions you can always email me or follow me and the channels that you see on the presentation and again the links and everything that you need to revisit this presentation are on the website that of the event thank you very much for watching and yes you can hear not thanks

Channel: NATALIA LEMOYNE

Views: 397,133

Keywords: #hangoutsonair, Hangouts On Air, #hoa

Id: MeT2ZmU2O9Y

Length: 58min 20sec (3500 seconds)

Published: Mon Mar 17 2014