Google Sheets - Full Course

Key Takeaways

barton paulson is a university professor and excellent instructor in this course he will turn you into an expert in google sheets spreadsheets are a lot more useful than many people think and don't forget to like and subscribe to the freecodcamp.org youtube channel welcome to google sheets what i want to tell you is that data loves spreadsheets and that really you could learn to love them too now you may not think of yourself as a data kind of person but the important thing to know about data is no matter what you do data gives you a map it helps you find your way and you may wonder a map to what well that depends on what your goals are they might be professional organizational social personal political but whatever it is you're interested in data can help you do it better for instance if you have a business it can help you figure out what work that you do is most popular with paying clients it can help you figure out what brings people back to your non-profit organization it can help you say what attracts new people to your political organization it may help you figure out what's the best use of time in your own personal life and it can even help you figure out important questions like how much should you charge for your work or your services so data is a method of approaching any one of these questions but what does that have to do with google sheets well google sheets lets you do several different things that are really important with data first and foremost google sheets gives your data a home now you know where your stuff is and you can find it you can search for it you can share it with other people you get it centralized and now you can do something useful with it and you know then what what are those things that you're going to do well there's a few things that you can do with google sheets that are really helpful number one is you can graph your data you can explore your data think of yourself as an explorer you're out there in really a new area and you're trying to find out what's all around you and what's the best way forward in google sheet you can do that with graphs like this multiple variable timeline it can help you find the places of greatest value and help lead you in that particular direction google sheets can also help you sort and filter through your data now you got a collection of tea bags put them in little boxes it makes it easier to see what you have and also to see what you're missing in the data world you can sort your data by whatever categories you want you can filter it here i'm filtering by a couple of different categories if you've got a business you can find new clients that are in your database and focus on them or you can find open accounts and you can focus on them and take care of it and once you find the data that's most important to you you can calculate things about that data and you can summarize it really it's a way of bringing things into focus the entire point of data analysis is to simplify things in a way that helps you reach practical goals you want to bring it into focus sometimes the focus analogy really can be pretty literal here i have a set of almost 1400 numbers and it's completely overwhelming but over here on the right you can see that the mean the average is 547.5 and the maximum is 9.99 i've got conditional formatting that highlights where those 999s are and also where the minimum values of 100 are it helps you sort through the haystack and find the needles are going to be of most use to you finally believe it or not spreadsheets and google sheets can help you work creatively now not necessarily to create your own art but if you're an artist you will find out that google sheets can help you free up some time and some cognitive bandwidth to focus on the things that are most important to you and you can be creative in spreadsheets here's one that i use when planning a big trip around the world with my family i actually had to get a spreadsheet to help figure out who is going to sit where on all of the plane rides or you can use a spreadsheet to help you make a decision about what you want to focus on in your business to help you reach both your personal and professional goals i'll give you examples of all of these things and as you go through the course you can follow along by downloading the files that we have at this address datalab.cc tools slash gsh that stands for google sheets zero one so that's datalab.cc slash tools slash gsh01 download the sheets follow along and really try google sheets and what you'll find is that it's gonna help you listen to your data and find your way in your own work thanks for joining me i'm excited to have you here and i'm excited to hear from you about how working with data in a simple tool like google sheets can help you find better value in your own work before we really get started in this course there's one important piece of advice i always have to tell everybody and that is when you're working with data always do pictures first the idea is you want to take a look at your data see what's in front of you get the context get the big picture maybe zoom in on some smaller things but you need to start with a visual analysis in other words first graphs then numbers as far as i'm concerned the graphs are the analysis and the numbers are simply a way of lending precision to the numbers i always tell my students when i teach statistics that every single thing we do in the class can be done visually you can graph it or you can calculate it with numbers that's possible too but graphs and visuals come first now i can't talk about this without sharing the world's best known example within the statistics community it's called anscombe's quartet a statistician charles antscom back in the 70s somehow found a set of data that did something very unusual we have four small sets of data here and what you need to see is that these summary statistics on the bottom the sample size the mean that's the average the standard deviation a measure of how spread out things are the correlation between the two variables and the regression equation with the slope and the intercept see those numbers right there they're the same as those numbers right there are the same as those numbers right there are the same as those numbers right there so based on a cursory numerical analysis these four data sets are identical on the other hand when you graph it you see it doesn't really hold up here's the first set of data this is a nice normal looking regression equation things are scattered around a little bit on this line this is what we're expecting in regular analysis on the other hand the second pair of variables has this really clear but curved relationship now the kind of analysis we usually do is looking for straight lines because that's the simplest possible way of describing an association between two things you can draw a straight line through this but you're doing damage to the relationship because really you need to be modeling the curve there are ways to do that but they're relatively advanced and personally i've never had to deal with them the third set of variables you see we've got this really strong straight line but we've got one number boop that's popping up and it's an outlier and the immediate question is what's going on with that number why is it out of line because if you're doing for instance a manufacturing process that might indicate something's broken it might just be a sensor that would be nice but it could be something about the manufacturing process again our standard approach doesn't deal well with outliers and anomalies and so you got to be careful about that and then the last one set for is a very bizarre really kind of a pathological data set where all of the numbers are jammed over here on the left with this one extreme outlier on the right which would normally be an indication that something has gone terribly terribly wrong in the data gathering and so simply by looking at them you can see that there's an enormous difference even though based on common statistical measures they were identical now these are artificial datasets charles anscombe had to work very hard to find these four sets of data but diverged so much graphically but closer to real life there are other reasons why you would want to do a graphical analysis first so for instance take this this is potential data about donations to a non-profit organization by month and you can see that on the left side things are motoring along kind of fine about five thousand bucks a month great and then suddenly halfway through it more than doubles and it settles down a little bit then it goes up another ten thousand and it settles down then it goes up another ten 000 and then it drops by 50 percent the graphical analysis just a simple line here lets you know we've got things going on and we need to figure out what's making it jump up at each of these times because maybe we can do more of that and then also what's making it drop off so much at the end this is the way that even a visual analysis can guide the follow-up questions and can guide the actions that you take within your organization as a way of better understanding what's happening and allowing you to maximize your effectiveness and your efficiency in reaching your own goals and besides there's another thing people are very good at visual analysis it's a high information density medium and so by getting a picture by getting visuals you're going to get a lot more insight than you could with simple tables of numbers and so the moral of all of this is simply always start by looking remember begin with graphs and then follow them up with any numerical analysis that you want to do in doing so you'll get a lot more insight and you'll avoid a lot more problems on the way of getting to the information that you need if we're going to be on the same page and understand what we're talking about in here we need to talk a little bit about the anatomy of a spreadsheet so the vocabulary that i use when describing it can make some sense first thing is the actual document that we're in in google sheets is just called a spreadsheet in microsoft excel is called a workbook and a spreadsheet can consist of more than one page each of which has a tab down here at the bottom in excel these are called worksheets and then google sheets they're just called sheets and that's where you actually do your work now each sheet consists of a number of columns vertical columns that have letters to indicate what column you're in this is column d right here and they have rows that are numbered row number one row number two and what that means is that at the intersection of each column and each row is a cell so right here i've got one cell that's one box one data container within a google sheet that can put something in it and it has an address this is cell b2 so you always give the column name letter first that's b in this case and the row number second b2 this here is c3 this right here is b6 this is a4 and so that's the address you have cells that make a spreadsheet that are arranged in vertical columns and horizontal rows now there's a lot of other things you can do here one thing is i showed you you can have multiple tabs and those allow you to have different kinds of information or arrange things in different ways on each one each tab also has a little drop down arrow right next to it that allows you to do things like duplicate the tab or change the color of the tab there we go now we've got a little bit of color right there i'm going to turn that off actually or to see a list of all the tabs that's good if you've got a lot of them or to add a new tab add a sheet and then when you come up to the top of the spreadsheet now a lot of times the first row is for the names of the columns the variable names and you might want that to stay where it is so if you scroll up it doesn't disappear you can do that by freezing a row or you can also freeze columns and that means that they will stay there now there's a few different ways to do that one of them is to come up here to view and hit freeze and say the first row or the first two rows or wherever i am right now and columns i personally find it easier to come over here and get this little thick line right here and when it turns into a hand you just click on that and drag it down and now the thick line is right here and you can see that when i scroll up and down that first row will stay put you can do the same thing with columns if you want for instance this one to always show column a just come over here until you i have a little hand and then drag it over and now column a will always show and that's called freezing rows and columns and it's a way of making it easier to navigate your spreadsheet also when you come up to the top here you'll find that you have a little drop down menu that shows up in each of these including the ability to sort an entire sheet by values in a column so i can click this and now it sorts the entire sheet by this column right here if i want to get back to where i was i could either do undo or i could just come and sort by a different variable i sort by id sort from ascending values from a to z you can also sort descending that's from z to a you also have a number of toolbars up here those are familiar with any other program you've been using there is one special function i do want to draw to your attention and that is way down here in the bottom corner we have something that sometimes called the quick sum box and what it is let me highlight a few numbers here i've highlighted some numbers and now when i come down here you'll see it gives me the sum i actually use this a lot if i'm putting in dollars i'm putting in values i can get an immediate sum by just dragging over a few numbers and getting their total you can also click on it and see a whole lot of other things the average the minimum maximum the count the number of scores and the number of numbers there's only numbers here so we're fine but that's a way to get immediate information without even having to write a formula so the important thing is a spreadsheet consists of individual sheets or pages each of which has cells that are arranged into vertical columns which are named with letters and horizontal rows which are named with numbers and as we go through the other videos in this course you'll see there's a huge number of things you can do within each document in google sheets more than anything a spreadsheet like google sheets is a container for data and one of the important things to remember is that data comes in lots of different forms a lot of it's numerically based but even then you can format it in many different ways and there is also non-numeric data and google sheets can handle a huge amount of this and i want to show you some of those variations so you know what you can put in and adapt to your own purposes the most obvious one of course is just plain old numbers like this integer 1587 you put those in there google sheets will usually put them flush right text is usually flush left you can type those in you can get a lot out of just those kinds of counting numbers on the other hand you can also get proportions which are red like percentages so this is 0.87 is the same as 87 percent in fact if you want to see it as a percentage you can just take that proportion and you can click here on the format as percent key and there it is it even gives you two decimal places but it's obviously 87 you also have the option of just typing it in as 87 i'll do it like this and that also works as a percent if i press the percent sign there now you see how it works now there's one thing you don't want to do and that is type in the number 87 and then think if you do this it's going to turn it into 87 percent it turns it into 8 700 which is obviously not what you want so you need to start with a proportion the 0.87 and it turns it into percentages you can also put in a number like pi and you can decrease the decimals which is good because pi has infinite decimals so we can make a little smaller if you want or if you have a number with a lot of decimals already entered and i'm using the pi function here you can increase the decimals i don't know that you really need a lot more but they're available if you want them now often the information you're going to be entering into a spreadsheet is going to be financial you can have money you're going to be keeping track of purchases and payments and you have several different options one is the accounting format so if you're an accountant you're familiar with this but you come up to format to number and then come down here to accounting and what it does is it puts the comma in for the thousands it puts the dollar sign way over here okay that's pretty easy you can also do financial formatting which is similar in certain ways i'll come down here and you'll see it puts in the comma but otherwise it doesn't have the dollar sign and it formats a little bit differently and there's also currency and you can set your local currency in google sheets i'm going to use dollars that's where i am and so you can see now it puts a little bit differently and it's three different ways of representing the same information now i realize they're slightly different from each other it becomes a little more pronounced when you make the numbers negative so i'm going to put a negative there and now you see it puts it in parentheses that's a common thing in accounting and with financial it also puts it in parentheses but when i come to currency when i put a negative it sticks in front of the dollar sign so a few different ways of dealing with money depending on what's the most common practice in the work that you do you can also put in dates you just type them in 7 16 it'll format them and then i'll show you in another video there's a lot you can do with dates in google sheets and you can also use time so for instance 10 10 31 just enter it as hours and then colon minutes colon and seconds so those are all numbers and there's a lot of things you can do with numbers but google sheets is able to handle a lot more than that it's able to handle well for instance it can handle text so you can just type in words and it turns out there's a lot of functions that allow you to deal with words and i'll give you examples of those in a later movie you can put in web links if you type in a valid web address i just typed in datalab.cc my address it recognizes that as http although it should be https because i have an ssl certificate and those are live links you can click on them and it'll take you there so here for instance i have my website datalab now opening because i had it here in the cell the same thing works you can put email addresses in there it'll work a lot of different ways you can put web data this is an interesting one this is a special function that calls in google finance data so this is today's stock price for google 1066 dollars a share and by the way notice we get this little uh disclaimer down here at the bottom if i were to delete that the disclaimer would go away of course you can use formulas and i've used a few of them already and have a lot more say about formulas so for instance the formula that pulls up this data is google finance and then in parentheses and [Music] quotation marks goog because that's the symbol for google and financial markets and one other interesting thing you can do in google sheets that you can't do in every spreadsheet is you can include images now this is an image that is on the web it's my company logo and you do image and then you put the url for that image in quotes nice thing is if i make this cell bigger that you know it increases in size as well so you can put an image in a cell if you can link it from a url i'm going to put that one back to where it was and then finally you have the option of including floating images and a number of other things let me give you an example of that i'm going to come right here to insert you can put in a chart an image a drawing a form check boxes comments and i'll talk about a lot of those let me just insert an image for right now the one thing you need to know when you include an image i'm just going to get one off on my desktop i got my datalab icon the one thing you need to know is that it's a floating image and so it's not in the cell you can drag it around it's also really big i'm going to make it smaller so it's not linked to this cell and that's a little different from this web image that we have in the cell but it is possible to include this information especially if you're trying to build up a repository or even have sort of a branding with your spreadsheets it gives you many different options and so what you can see from all of this is that google sheets is able to serve as a container for a lot of different kinds of data each of which might arise in different circumstances in your own work once you have your data in google sheets you have a lot of options for how you format the data now this is mostly cosmetic in terms of changing the appearance of the data but it's also something that can help you organize the data that makes it much easier to follow the argument and bring things to your attention let me give you some of the really obvious things if you've ever used a computer before this is going to be familiar so first off is you get to change the font the default font in google sheets is arial which is very clean and it works well in a lot of situations on the other hand maybe you want a different font maybe you want something like sorts mill gowdy that's one that i like and you come up here and you click on fonts and you go to source meal guide now you may not have that because it's not installed by default if it's not you just go to more fonts that takes you to a web page and you can add a huge number of fonts don't add all of them because then it gets confusing but find something that you need works for your purposes and you can add it in here and it becomes available and it becomes available to anybody who uses your document another option is something like consolis if you're using code a fixed width font is pretty common you can change the font size so you can make it smaller it's ten point by default eight point is well i've got my screen zoomed in super big so it's not too hard to see you can make a lot bigger and obviously this is something you might want to do if you are putting in headings or titles now one thing to be aware of is you're not limited to the choices that are here so for instance i put 20 point but i only had 24 point just come up here select the number and type what you want there's a 20 point font you can also do the bold all you got to do is do command or control b or you can click on that same thing with italics just come up here you can do the other command and and strikethrough i don't use strikethrough very often but it might be important for your purposes okay those are all quick and easy the next one is text color obviously if you've used a computer before you know how to do this stuff but color can be an important indicator of both importance and directing focus and that can be really important when you're putting together visualizations or making a table that you're going to have to come back to and you need help interpreting it or you're sharing it with other people so i can come down and select the color there's red text you can also do fill color that's an option so if i do the fill it puts in the cell now by the way when you have a dark fill like this you usually want to come and pick white text to go on top of it so it's easier to tell what's going on you can do cell borders and i do these only lightly i try to keep it pretty clean to do cell borders select the cells that you want to have borders on and then come up to the borders and you know you can put cells borders on everything but that's kind of a nightmare you don't want to do that there are times i find however that it's nice to have just horizontal lines going through the data it's relatively easy to direct the line across the rows in a table especially when it's printed but i'm going to take that out for right now you have but cell borders are an option row height you can snap it to fit or you can have a consistent size now snap to fit means something that happened automatically here you saw how that got bigger i can drag it down so it's super huge or i can bring it back up either way you saw when i changed the font size it automatically changed the height of the row so that the text would fit similar to that you have options for column width you can snap to fit or do a consistent size this actually requires a small demonstration so i'm going to put three column headings here and i'm going to make them bold because i often find it's helpful to have column headings in bold and they're different sizes if you want to snap to fit you can just select these three columns and then double click here on the right one and then it changes the width of each column so that it adjusts to the size of the text there may be situations in which you want to do that on the other hand i find that having different size columns it can be a little disturbing so you can also do a consistent size so for instance i can select all three of them here and i can make all of them the size of this last one if i just click this and drag it around a little and i let go now all of them will be the same size and that usually is easier to read because you have this uniform movement across the document in addition to that and maybe this is a solution you can do text alignment now all the text that i've got right here so far is flush left you can of course come over here and you can center it you can also make it flush right but text is usually flush left and that works best on the other hand if you're doing a column you may want to have centered text for columns especially with the columns or different sizes that makes things a little easier you also have the option of changing vertical alignment but to make that clear let me show you one other thing and that is text wrapping where it says overflow wrap and clip so let's make this a little more obvious by making all of these smaller narrower okay so you see how this one that says extra extra extra long is now too big one of the options i have is to come right here and to change it so that it is wrapped and that has it you know go to two lines it it's not breaking very well if i put in a space here it should break better because it treats it as a single word there we go now it's breaking better on the other hand maybe you want these other ones on the bottom but maybe you want to vertically orient them towards the top towards the middle anyhow whatever works best for your purposes these are a lot of different ways that you can format the document again the point here is not to noodle away your time and just kind of mess with it because you can but the point of it is to organize the document to make it clearer you want clarity and you want direction you want to guide the eye through your document to make the comprehension and analysis easier and formatting even with this small range of options is going to make that a lot easier to do in google sheets when you're getting ready to work with your data you'll find that your life becomes a lot easier if you have tidy data now it's kind of a funny term but it actually refers to something specific within the data science world i want you to know that if you go to google and type in tidy data two of the first three are going to take you directly to the journal article in the journal of statistical software by hadley wickham that originally explained the idea of tidy data and you can also take you to a page on the r project site that gives a lot more information about what is meant by tidy data the idea is that data that is tidy is easier to manipulate and is easier to take from one software package to another now this is where spreadsheets are a little problematic because spreadsheets are amazingly flexible and they allow you to do a lot of things that might suit your purposes when you're creating the spreadsheet but they make exporting or sharing with others kind of a headache so for instance i've got a spreadsheet right here that does some of the things that i hope you don't have to deal with first and foremost we've got a problem here of merged cells this is when you take a single cell and you spread it all the way across these other ones sort of as a header there's a merged cell there's a merge cell this one is 15 cells merged together and it's smack in the middle of everything the problem with these is they change the way the spreadsheet functions so for instance if i come here and click on column f well you see it's selecting this entire first cell and then selecting all of this but if i then hit bold it only changes this one and if i try to hit bold back it doesn't do anything i have to do undo and if i were to try to move it i'm just going to come right here and try to move it and oops it just doesn't want to work because of the merged cells merge cells are things that people sometimes do to get title centered but they make it really difficult to work in the day so you don't want that the other thing that you don't want is you don't want to have mixed up labels you don't want to have things spread across in different tables that might work for an individual project but again if you're planning on sharing it with other people or if there's the possibility they might take the data out of google sheets and into some statistical application or programming language they're going to need something else specifically tidy data means that each column is the same thing as a variable variables and columns are identical and that rows and observations are the same thing and so we need to get some of this cleaned up and so another thing you can see by the way is with tidy data is that all of the data needs to be in the spreadsheet so this is an important piece of information potentially but it needs to be in a column to mark pieces also we've got a comment kind of hiding right here and it tells us something that maybe this person could be coded as being west or in east depending on how we want to do it this is not the sort of thing you want to have in a comment because if you export the data you lose all of that information and so let's take a few looks at how to tidy things up to make them easier to work with and less problematic first one is get rid of all the merged cells get rid of those titles we had across the top and then the information that said the first 10 were surveyed in person make a new column and put that in there so now we have that information in the spreadsheet it's not included somewhere else i decided not to make a change for the comment because i didn't think that was necessarily critical if it were then you might want to have two locations starting location and ending location for each person now there's a couple of other problems one is that we have gender is just coded in a crazy way we don't even know what the zeros and ones are f and m we can guess and so we need to do a little bit of data cleaning right now i'm going to come here to this tab and you see i've done two things number one is i took the variable and i changed its name from gender to female because when you have a variable that you're going to treat as dichotomous it's really easiest if you use a zero for no a one for yes and you name the variable by what the one is in this case i decided to assign female the one it's arbitrary you can do whichever one you want but for anybody who put down that they were female i put a one for anybody who put down they were male i put a zero similarly for the method of surveying them i decided that if they got surveyed online i would give them a one and if it were anything else like in person it would be a zero and so i've cleaned that up but there's one more step that we need to do and it has to do with this one right here we've got information here about the location the person is in this is a different level of measurement everything else is at the individual level this is now about a larger group that they're in and we're repeating a lot of information if you've ever worked with a sql database or a relational database you know that you set up separate tables for different levels of measurement you want to do the same thing with tidy data so you keep information about the individual but then if you have this information at a different level that's repeated you put that into a separate table and it's just a tiny little table and then we only have to indicate whether a person's location was north east south or west and we can get the rest of this information from this table and when you do those things what you'll end up with ultimately is two separate sheets you'll have one sheet that contains all the individual level information where each column is a variable each row is an observation and all of the relevant information is put into those variables and columns and you'll have a second sheet that contains the variables for the other level of measurement now again this is mostly important if you're going to be exporting your data so it can be analyzed in another program if you're working entirely within google sheets it's never going somewhere else you might be okay doing whatever you want just be aware that the flexibility that you get with spreadsheets gives you a lot of room for creativity but it can potentially create some headaches and so when in doubt take the tidy data approach keep your data clean and make it easy to work with easy to analyze and easy to get meaning out of your analytical life becomes much simpler and much faster if you're able to collaborate with other people and fortunately that's one of the things that google sheets does best to share a document to share a spreadsheet with somebody all you need to do is come up to click on the blue share button and there's a lot of different ways you can share with people now the most direct is to simply put in their email address right here is a fake email address that i just got data at superido.com and you have the option of assigning different levels of sharing that that person actually can change the document they can edit it or they can simply make comments on it but they can't change the actual text or they can simply view it and you can say here is the document and it will email that person and let them know that they've got the document and then they get into it and they can start working on it with you you also have the option of sharing with a link so for instance you can say that anybody at your organization if you have a google business account or you can click on more here you can share anybody who has a link or public on the web which is what i've done on this one and i've said it so that anybody who has the link can view it i could set it up so they could comment or edit but view is going to work best in this case i hit save and then as long as people have this very long link but you click copy link and i'm going to open up a new incognito window click it like this and now this is the way that anybody can see it they can see the document they can't change it it's giving me a little blurb about updates here here it says view only that's fine but you can see that other people this is my professional account with datalab this is my personal account and even though this person can't edit it they can download it as a microsoft excel file or a csv or tsv file and they can start working it on it themselves i'm going to close this one now one of the nice things about the way that sharing works in google docs i'm going to just delete this one i don't really want to share with my fake email address i'm going to press cancel and done but when you're sharing you can see who's sharing right here that's my personal account this is my company account and you're able to see where they are in the document so for instance i'm going to be in this cell right here but i can tell that the other person is surrounded in pink same as we have up here is in this cell and if i go to that account you can see i have the document open that's where i am you can see that i'm now sharing with my company account and it's a good way to see who's working on it where they are and to start collaborating to get your analysis up and running as fast as possible sharing an individual sheet within google sheets may accomplish your purposes but often you're going to work with people on more than one thing or there's more than one resource you need to share and in that case rather than sharing each of them individually it's a lot easier in google drive to share a folder to do that all you need to do is come to the folder that you want to share and you get it selected and you can either come up here to get a shareable link you can click on this to share it or you can for instance option click on it right here and do share and then you're going to add the person you want to share with you can put in a specific email address and then with that include a message to them or you can get a link that you can share with people you've got a lot of different choices here i personally have it said that anybody on the web could actually search for this they searchable on google and get it or they have a specific link lots of other ways and you also have the option of whether they can view it see the contents or whether they can organize add and edit information within each of these now right now i have it set to just view and let's see well if you're looking at these files you know how it works but i'm going to copy the link and i'm going to open up an incognito window so i'm not logged into this one and i'll enter that link and go to it and what you have here are the three different files i've got a little icon file you can look at that one it's just a little data lab icon i've got an empty document that says we're sharing this this is where it's shared and i've got the spreadsheet that i'm working on right now and when i open this one it gives us this little advertisement let's just know that new things are coming you can see who's logged into it that's my personal account that's my company account and although you can only view it here you do have the option of downloading the file just as you did when we were sharing individual files the nice thing of course is anything that anybody puts into the folder is automatically shared with everybody else who has access to it and when you're working on a project with more than one step in it say for instance you have a presentation you have a report you have several different files that build up to it sharing a folder is going to be faster more efficient and more effective way of collaborating within google sheets if you're going to work with data you have to have some data and you have to have it in google sheets now the fundamental way to do this is to enter the data directly into google sheets and there's a lot of times where that's the preferable solution if you're going to be working with a statistical program like spss or sas it's so much easier to put it into a spreadsheet there's a lot of advantages to it one is it's really easy to share and have several people working simultaneously on what you're doing here now i want to show you if you're going to be entering data manually and say for instance is going to be entering it based on paper surveys that you have there's a few things you want to do to make your life a little easy first off you want to have what's called tidy data that's a term developed by hadley wickham who works with the statistical programming language r and it means that a variable is the same thing as a column and that a case is the same thing as row and there's no funny business going on in your sheet so let's start with this one if you're entering paper the first thing you need to do is have an id number so that you can find the piece of paper in case something gets entered wrong now you may need to get at a pen and write these numbers on a piece of paper and then maybe you start putting in the questions now i want to show you something kind of neat that google sheets and microsoft excel are able to do and what that is is propagating so if you take a pattern that's easy to follow so for instance i like to type q01 where q is for question and then do q02 for question two mind you i'm not doing q1 q2 because it's going to think that means quarter one quarter two in the year but if i do the leading zeros especially if there's more than nine questions this is important i can select those first two then i can just drag them over and it will propagate and fill in with the same pattern you can do that for however many you have and then what i'm going to do is i'm going to take this top column i'm going to put it in bold and maybe i will even center the columns you don't have to and there's a there is a precedent for text columns being justified to the left and numbers being justified to the right you know it's up to you sometimes i just like to do this so that the labels are directly above the numbers it's a little easier to see what's going on and then i'm going to freeze the top row by simply dragging this bar down and now when you scroll up and down that top row stays put if you're going to be entering data from pieces of paper like i said you need id numbers and so you go like this 1 two three and it can propagate that same pattern so i'll drag it down a little bit and now we've got the numbers as long as those match up with what's on your paper you're good to go if several people are entering the data then you might want to be able to separate who has what and the first person might start with 101 and then 102 so the 1 the first 100 indicates the person the second number indicates which one they're working on and then the next person can start with 201 and 202 it's okay that there are gaps that you go straight from 107 to 201 you just can't have repeats that's the important thing and this way people can be working separately on their own computers and they can be entering the data simultaneously in the same sheet and it gets in there it's wonderful and so the very simple method for entering this is to type in a number let's say you've got a rating scale and people are putting in a number and then i hit tab go to the next one or a right arrow and nine and i can go through and enter like this i'm just hitting tab at each point when i get to the end i hit return and it takes me back to where i started on the first line and i can go on like this i can type in the data however i want if you have missing data if a person didn't answer a question it's best to simply leave it blank and then if they typed in something you can put in 8 although i would hope that they didn't type in something on what's supposed to be a numerical variable but the idea here is you can put in whatever you want and you enter the data all the way through and now you have entered your data manually and you're prepared to do subsequent analyses although it's possible for you to enter all your data manually in google sheets or another spreadsheet and again there may be situations where you need to do that life is so much easier if you can simply import the data and dump it in there and get going now what i want to show you is slightly different from importing a file into google drive the place that stores files so for instance you can take an excel file drag it into google drive and it can store in either the native excel format or convert it to a google sheets document i actually want to show you how to import something straight into a sheet in an existing google sheet and to do this we're simply going to go up to file and then come down here to import and when you do that it's going to ask you to import something i'm going to go to upload and select a file from my computer actually let me show you what i have here i have three files here that are identical except for their format they're all called import and they're just data files that have an id number a date a response id five rating scale questions and a zip code it's this is made up data this is in excel as an xlsx spreadsheet this over here is the same data in a csv or comma separated value spreadsheet and this one in the middle here is a text document but they're still separated by tabs and so it's identical information in all of them so what i'm going to do is i'm going to come back to google sheets and i'm going to select one of these files i'm just going to take the import.csv so i'm going to hit open and then it's going to bring up a little dialog box and ask me some questions about what i want to do do i want to create a totally new spreadsheet no i don't even want to insert a new sheet i want to replace the current sheet so it's the sheet that i already have the information on put it there and if i want to tell it what the separator is a tab comma or whatever but it can detect it automatically and i'm using uh comma separated values so it's it's easy to deal with and then convert text to numbers and dates yes and i hit import takes just a second and here it is it's in my new sheet and so i've got my data all the rows and the columns are intact if i want to make my life a little easier i'll freeze the top row and maybe i will come here maybe i'll select the whole thing and make it all flush right except for these two which i'll put then back to flush left and my data set's ready to go and now i can start analyzing so importing directly into an existing google sheet very quick and easy way to get up and running with google sheets i have an untested theory that the single most common container for data sets in the entire world is spreadsheets and of those i think the majority are probably going to be microsoft excel spreadsheets so for instance if somebody sends you a spreadsheet or if you download a spreadsheet from online it might be an excel format and even though it's really easy to import excel directly into google drive or into google sheets i do want to show you you also can just resort to plainly copy and paste so i've got a spreadsheet here that's import and it's the same data i've been using for some other examples i'm going to select the entire set by doing shift and then command or control with space i get the rectangle i'm going to hit copy and then just go back to excel sheets and hit paste i'm going to do control or command v in this case and there's my data i'm ready to go on the other hand it may be that you also are going to be using data from web pages on tables i do this occasionally and so for instance if we go to this next tab i have a link here to a page in wikipedia on nobel laureates that has a table in it so let's open this page and it talks about who's won the nobel prize which is a wonderful thing to do and here we have a table with a list of everybody who's ever won a nobel prize from 1901 down through 2017. there we go now what i'm going to do is i'm going to copy this i find it works best if i start at the bottom right and i just click and i drag up and i'm going to go up to 1901 just have to not go past it almost there and i'm going to copy the headings too so now i let go i've got that all selected i do command or control c to copy it then i'm going to go back to my spreadsheet i'm going to come to this cell right here and all i do is i hit command or control v to paste it in now this is going to do something slightly weird and that is these are all links so i've got a million links on this table and truthfully that may cause problems as i'm trying to deal with the data so i'm actually going to ask google sheets to not paste it as links but to paste it as plain text i do that by coming to this little clipboard that pops up when you first paste something and i say give me the values only and now you see it just turns into unformatted text and that's a great way to go i can then if i want to i can delete this first row i can then lock that first row i can put it in bold i can highlight the columns and then just double click right here to resize them and now i've got a data set that i can work with this is great i do want to show you one tiny more little trick about copying and pasting in excel i'm going to go to this sheet called paste transpose and i'm just going to type in a little bit of data here now i showed you in another video how google sheets is able to fill in a pattern if you do one two three it just keeps continuing or if you do q01 or q02 it can also do this with things like days of the week so i've got sunday monday here and i'm just going to highlight those two and i'll drag it down a little bit and look we have now have the whole week now what i want to show you is if you want to switch it from being arranged vertically in a column to being the column headers what you want to do is a special kind of paste called a transpose or transposition so i'm going to copy this by hitting control or command c then i'll just come over here and i'm going to do a control click it's actually a finger click on my track pad and go to paste special and we've got a few options here the one i want to show is this one right here paste transpose and it flips it around from the rows to the columns and if you're trying to set up your data for analysis this can save you a lot of stress about trying to rearrange things manually and so that's just one final tip on getting data by copy and paste into google sheets when you're working with data o