Data Analysis Using Spreadsheets | Google Data Analytics Certificate

this video is part of the google data analytics certificate providing you with job-ready skills to start or advance your career in data analytics get access to practice exercises quizzes discussion forums job search help and more on casera and you can earn your official certificate visit grow.google datacert to enroll in the full learning experience today spreadsheets are a powerful and versatile tool which is why they're a big part of pretty much everything we do as data analysts there's a good chance a spreadsheet will be the first tool you reach for when trying to answer data-driven questions so after you've defined what you need to do with the data you'll turn to spreadsheets to help build evidence that you can then visualize and use to support your findings spreadsheets are often the unsung heroes of the data world they don't always get the appreciation they deserve but as a data detective you'll definitely want them in your evidence collection kit i know spreadsheets have saved the day for me more than once i've added data for purchase orders into a sheet set up formulas in one tab and had the same formulas do the work for me and other tabs this frees up time for me to work on other things during the day i couldn't imagine not using spreadsheets math is a core part of every data analyst job but not every analyst enjoys it luckily spreadsheets can make calculations more enjoyable and by that i mean easier let's see how spreadsheets can do both basic and complex calculations automatically not only does this help you work more efficiently but it also lets you see the results and understand how you got them here's a quick look at some of the functions that you'll use when performing calculations many functions can be used as part of a math formula as well functions and formulas also have other uses and we'll take a look at those too we'll take things one step further with exercises that use real data from databases this is your chance to reorganize a spreadsheet do some actual data analysis and have some fun with data data analysts spend a lot of time organizing data and performing calculations luckily there's lots of different tools to help them do just that including spreadsheets in this video we'll take a look at some of the ways data analysts use spreadsheets to help them with their day-to-day responsibilities later you'll get to test out some of these things yourself but for now let's start with a quick look at how data analysts use spreadsheets to do their jobs this will change depending on the work you need to complete but here's an overview of a few of the major tasks imagine you work for a construction company your company needs your spreadsheet skills to analyze some data about their expenses so you access the appropriate data and add it to your spreadsheet we won't cover all the details of this project right now but you will get a chance to see lots of spreadsheet features up close and personal as we move forward what do you do with the data now that it's in your spreadsheet again this will be different for each job but you might start by organizing your data with the task you've been given for example you might put your data in a pivot table we've talked about pivot tables before in this course we'll cover them in more detail later on but for now just think of them as a well-organized and very useful tables next you might filter the data in the pivot table sorting and filtering data is a common part of most jobs this lets you focus only on the data you'll need for your analysis in our example maybe you only need the expenses for a certain time frame like the last three months after you filtered your data you could perform some calculations to learn more about it maybe you need to find out which construction projects ended up costing the most money this is where formulas and functions are really handy we'll talk about them in just a bit but formulas and functions are great for doing some quick math especially once you run out of fingers and toes to count on now you've seen some of the ways data analysts are using spreadsheets in their day-to-day work for a lot of different tasks including organizing their data and making calculations before you know it we'll have you working in your own spreadsheets we'll start with a step-by-step process to show you some tools to organize your data in a spreadsheet consider these steps the basics you won't always have to use them when working with a data set but if your data is a bit messy when you get it these steps can help you get it ready for analysis let's start by opening a new spreadsheet as a data analyst you might not start with a blank spreadsheet but it's good to know how to do it just in case start by opening excel google sheets or whatever spreadsheet software you're using then select a new blank file the first thing you'll want to do when you open a new spreadsheet is give it a title here's a pro tip make your title short clear and have it state exactly what the data in the spreadsheet is about trust me it'll make searching for it a lot easier creating a folder on your computer specifically for spreadsheets and related files can also make it easier to find them for this spreadsheet it's already saved in our drive so we'll open our file menu to click move then we'll create a new folder name it population data and move the spreadsheet there our spreadsheet now has a new home this will save you a lot of unnecessary clicks and headaches when you look for this file there's a few different ways data analysts get data they work with depending on the job you might use data from an open source you might be given data to work with or you might be asked to find your own data you'll experience all of these later in the program there's a lot of open data sources online where data is made available to the public for example we'll use data from worldbank.org that's already in a spreadsheet the data shows the population of latin american and caribbean countries from 2010 through 2019. let's open this spreadsheet okay time to get the data ready for analysis we'll start by selecting the whole sheet and making our columns wider by dragging the boundary of one of the columns this will help us see the data clearly then we can adjust any individual columns that need it you can make columns wider in other ways as well but this will work for now the first row of spreadsheet is for data attributes or variables so it's basically labeling the type of data in each column let's make the attributes stand out from the rest of the rows by selecting it and filling it with color we'll also make the labels bold if we want to add another data attribute between two of the other attributes we can always add a new column just click on any cell within a column and use the insert menu to add a new one it will appear next to the column you originally clicked pretty simple right deleting a column is just as simple to delete right click in a cell in the column you want to get rid of the steps we're showing may be different depending on the spreadsheet program you're using but should be pretty similar let's add one more thing to our data table borders this can help you see each piece of data more clearly to add borders start by clicking the select all button at the top left corner of your spreadsheet this is kind of like a magic button because you can click it whenever you need to make changes to every cell in your spreadsheet then click the border button in the menu and choose the type of borders you want to keep our spreadsheets uniform we'll choose borders for all cells and just like that we've gone from raw to refined now our spreadsheet is filled with data and it's nice to look at too using these organization tools before you analyze can help you focus on the data once you start your analysis [Music] so far we've covered how to start a new spreadsheet enter in data and make it look refined and ready for some serious analysis now we'll learn how to perform calculations in your spreadsheet you may need to calculate everything from sums to averages to finding minimums and maximum amounts you'll use calculations for a lot of different kinds of tasks so in this video we'll focus on learning the basics and then do a little math with some sales data to practice let's talk about formulas first you might remember that a formula is a set of instructions that perform a specific calculation basically formulas can do the math for you now they don't only do math they can do a lot more and soon you learn different ways you can use them throughout the data analysis processes formulas are built on operators which are symbols that name the type of operation or calculation to be performed for example a plus sign is a common operator the formulas you use as a data analyst will usually include at least one operator now let's talk about math expressions or equations these can take a lot of different forms but you might be familiar with them already 3 minus 1 15 plus 8 divided by 2 846 times 513 these are all examples of expressions is this bringing back memories of grade school well back in math class you most likely learn to complete an expression by including an equal sign and the solution it's slightly different with spreadsheets when you create a formula using an expression in a spreadsheet you start the formula with an equal sign for example if we want to subtract we type an equal sign followed by the rest of the expression without any spaces in the formula now let's try an expression that's a bit more challenging we'll type 31982 then a hyphen for a minus sign then 17795 and to calculate we press enter you'll most likely use formulas this way when dealing with large numbers or expressions with multiple steps here are the operators you'll use to complete formulas the plus sign for addition the minus or hyphen for subtraction the asterisk for multiplication and the forward slash for division the division and multiplication symbols might be different than what you're used to small changes but important to keep in mind if you already have data in your spreadsheet you can use cell references in your formulas instead a cell reference is the single cell or range of cells in a worksheet that can be used in a formula cell references contain the letter of the column and the number of the row where the data is a range of cells is a collection of two or more cells a range can include cells from the same row or column or from different columns and rows collected together we'll show you an example in an upcoming video now let's apply what we just learned to some sales data if we want to add these figures to find the total sales for the first row of data you can click cell f2 from there we'll start with an equal sign and use the cell references to input values in your expression we're starting with cell b2 because the year in a2 is not a value we want to add to the total then press enter and just like that your total sales has been calculated for you but what if you realized one of the values in your data was wrong no problem you can change the value in any cell used in the formula and the total will update automatically the great thing about using cell references is that they also automatically update when a formula is copied to a new cell talk about a time saver so instead of entering the same formula again for every new set of cell references just copy the formula using the menu or a keyboard shortcut like control plus c then paste the formula where you want to apply it using control plus v and presto the formula updates all the new cells and values correctly now let's say you also wanted to find the average sales for this you create a new formula in a different cell to group values in a formula use parentheses this lets your spreadsheet know which values to calculate together and the order of the operations to be performed for example open parenthesis then b2 plus c2 plus d2 plus e2 and close parentheses then divide the value of all of this by four by typing slash four so you are adding the values in the four cells together and then using the slash to divide the total by four and just like the last one we can copy and paste the formula here's another formula you can use if you want to find the percent change in sales between june and july once the formula calculates the value you can then use the percent button to change the value to a percentage when you apply the formula to the other rows both the formula and the percent will automatically update uh-oh that doesn't look like the right answer looks like we've got an error don't worry errors can happen at any stage of data analysis and that includes when you're using spreadsheets a formula has to be airtight if there's something wrong with one of the cell references it won't work so what's our error well we can see that the value in cell d4 is missing it might take some time and research on your part to find the correct value but it's worth it you want your analysis to be as accurate as possible when you do add the value the formula takes care of the rest that was a lot to take in thanks for staying with me you'll be able to apply what you learned about formulas here and later in the program to make your analysis more efficient and your job a little easier and soon you'll work in your own spreadsheet happy spreadsheeting [Music] formulas are a great way to become more efficient when using spreadsheets especially when you add shortcuts like copying and pasting into the mix as you progress as a data analyst you'll most likely learn more shortcuts to help your process but now it's time to move on to functions while they're closely related to formulas they're not exactly the same by the end of this video you'll understand the difference and know when to use them both in the world of spreadsheets a function is a preset command that automatically performs a specific process or task using the data you might remember some of the shortcuts we learned that can be used with formulas think of functions as the most useful of these shortcuts the good news is a lot of spreadsheet functions have names that tell you what they do there are tons of functions out there and as you continue to work with spreadsheets you'll find that you use certain ones a lot and others rarely or not at all for now let's take a look at some of the functions that we can apply to our sales data from the previous video we'll start with total sales let's use the sum function for this in cell f2 the first steps are pretty similar to what we did in the last video first we'll select the cell where we want the calculation to appear type equals then add the word sum as our function one of the great things about functions is they don't always need operators like a plus sign for addition in this case after the open parenthesis you can go ahead and select the range of cells you're adding a colon between the cell references shows that you're using a range in this case the range includes cells from the same row after the closed parenthesis we press enter and just like that our total sales number appears just like the formula we used before functions can be copied and pasted into other cells in the same column but let's undo that step so that you can see another way to copy a function or formula spreadsheets have something called a fill handle it's a little box that appears in the lower right hand corner when you click on a cell if you rest your cursor on the box you can then drag the fill handle to the other boxes in the same row or column any formula or function in that cell will automatically be added to the cells you fill plus the fill handle will update the formula so the cell references match the row of the columns of the cells you fill this means the formula is calculated based on the data in each separate row or column filling won't work for every situation but it's still a pretty great trick now let's find the average sale for each month using the average function different functions perform different calculations but they work in the same way keep in mind not every calculation you'll come across has its own function to help you for example to find the percent change in sales between june and july you'd use the same formula you used in an earlier video let's say you're asked to find the lowest monthly sales in this data set there is a function for that it's called the min function which stands for minimum here's how it works say you need to find the lowest monthly sales for the whole set all you have to do is set up the function then after the open parenthesis select the values from all three rows this might be important information for your stakeholders so let's add color to the cell with that value in your data set to make it stand out in this case click on cell d2 and then fill color icon which looks like a paint can then choose a color i'll use yellow here you can follow the same steps for the highest sales by using the wait for it max function looks like we have an error message what could be wrong ah we forgot to include an open parenthesis after the function no worries it's a quick fix but this is a good reminder to continually check the format of your functions and formulas as you use them we'll learn more about error messages and how to work with them later okay that's better now we'll add color to the cell with the highest sales too this is just one way to highlight key data you'll find out about some others later you've now had a peek at some ways you can add and organize data in a spreadsheet you've also seen how powerful formulas and functions can be when applied to real-world data as a data analyst this is just the beginning of your experience with spreadsheets you'll soon find out how much more spreadsheets have to offer in the meantime you're free to practice some of these formulas functions and other processes on your own it can be fun to experiment and see all that spreadsheets can do soon you'll switch from spreadsheets to structure thinking the data analytics pieces are starting to fit together exciting stuff is coming right up so stick around albert einstein once said if i were given one hour to save the planet i would spend 59 minutes defining the problem and one minute resolving it now that might seem extreme but it does show us just how important it is to define the problems before trying to solve them a lot of times teams jump right into data analysis before realizing a few months later that they are either solving the wrong problem or they don't have the right data in this video we will learn how to develop a structured approach to defining the problem domain this is important because if you define the problem clearly from the start it'll be easier to solve which saves a lot of time money and resources in the data world we call this first piece the problem domain the specific area of analysis that encompasses every activity affecting or affected by the problem before we can do anything else we need to understand the problem domain and all of its parts and relationships so that we can discover the whole story actually calling it the first piece makes me think of a jigsaw puzzle say you have a puzzle let's think of that puzzle as our problem domain you have all 500 pieces but you lost the box so you don't know what the image the puzzle will reveal will it be an animal a waterfall a bowl of oranges whatever it is it's going to be tough trying to put it together without an image you can refer to even the greatest puzzler in the galaxy would need a new process and lots of time to complete that puzzle data analysts face the same kinds of challenges too you might remember that data analysts aren't always giving the complete picture at the start of a project a big part of their job is to develop a structured approach and use critical thinking to find the best solution and that starts with understanding the problem domain this is where structure thinking comes into play to successfully solve a problem as a data analyst you need to train your brain to think structurally and that's exactly what you'll learn coming up see you there [Music] so earlier i told you that carefully defining a business problem can ultimately save time money and resources all of this is achieved through structure thinking structure thinking is the process of recognizing the current problem or situation organizing available information revealing gaps and opportunities and identifying the options in other words it's a way of being super prepared it's having a clear list of what you are expected to deliver a timeline for major tasks and activities and checkpoints so the team knows you're making progress in this video we'll look at how structured thinking helps us save time and effort but also makes our job as data analysts easier because it allows us to better understand the work we are doing in the business world it's common for teams to spend hours of valuable time trying to solve an important problem only to end up back where they started not only is the initial problem not resolved but they've spent hours not resolving it this outcome negatively affects you your team and the organization as a whole but it can usually be prevented many times the situation is a result of not fully understanding the issue structure thinking will help you understand problems at a high level so that you can identify areas that need deeper investigation and understanding the starting place for structure thinking is the problem domain which you might have remembered from earlier once you know the specific area of analysis you can set your base and lay out all your requirements and hypotheses before you start investigating with a solid base in place you'll be ready to deal with any obstacles that come up what kind of obstacles well let's say you're asked to predict the future value of an apartment building based on a given data set you have hundreds of variables and every one is crucial to your analysis but what if one variable accidentally gets left out like square footage for example you'd have to go back and redo all your hard work that's because missing variables can lead to inaccurate conclusions another way that you can practice structure thinking and avoid mistakes is by using a scope of work a scope of work or sow is an agreed upon outline of the work you're going to perform on a project for many businesses this includes things like work details schedules and reports that the client can expect now as a data analyst your scope of work will be a bit more technical it'll include those basic items we just mentioned but you'll also focus on things like data preparation validation analysis of quantitative and qualitative data sets initial results and maybe even some visuals to really get the point across let's bring a scope of work to life with a simple example say a couple has hired a wedding planner we'll focus on just one task the wedding invitations here's what might be in scope of work deliverables timeline milestones and reports let's break down just one of these deliverables the wedding planner and couple will need to decide on the invitation make a list of people to invite collect their addresses print the invitations address the envelopes stamp them and mail them out now let's check out the timelines you'll notice the dates and the milestones which keep us on track finally we have the reports which give our couple some peace of mind by telling them when each stop is complete a scope of work can be a simple but powerful tool with a solid scope of work you'll be able to address any confusion contradictions or questions about the data up front and make sure these sneaky setbacks don't stand in your way this is a simple example of what a scope of work might look like but later you'll be able to practice building your own data doesn't live in a vacuum it needs context earlier we learned that is the condition in which something exists or happens so actions can be appropriate in some context but inappropriate in others for example yelling move is root in one context like if your friend is standing in front of the tv but it's entirely appropriate in another like if that friend is about to get hit by a kid on a tricycle do you see the difference in the world of data numbers don't mean much without context i'll let my fellow googler ed tell you a little bit more about that just having tons of data isn't enough we have to do something meaningful with it data in itself provides little value to quote jack dorsey the founder of twitter and square every single action that we do in this world is triggering off some amount of data and most of that data is meaningless until someone adds some interpretation of it or someone adds a narrative around it data is straightforward facts collected together values that describe something individual data points become more useful when they're collected and structured but they're still somewhat meaningless by themselves we need to interpret data to turn it into information look at michael phelps's time in a 200 meter individual medley swimming race one minute 54 seconds doesn't tell us much when we compare it to his competitors times in the race however we can see that michael came in first place and won the gold medal our analysis took data in this case a list of michael's races and times and turned it into information by comparing it with other data context is important we needed to know that this race was an olympic final and not some other random race to determine that this was a gold medal finish but this still isn't knowledge when we consume information understand it and apply it that's when data is most useful figuring out what data means is just as important as collecting it as a data analyst a big part of your job is putting data into context it's also up to you to remain objective and recognize all sides of an argument before drawing conclusions the thing about context is that it's very personal if two people curate the same data set and follow the same directions there's a chance they will end up with different results why because there is no universal set of contextual interpretations everyone approaches it in their own way and even if the data collection process is correct the analysis can still be misinterpreted conclusions can be influenced by your own conscious and subconscious biases which are based on cultural social and market norms for example if you ask a boston resident which baseball team is the best chances are they're going to say boston red sox which brings us to a major limitation of data analytics if the analysis is not objective the conclusions can be misleading to really understand what the data is about you have to think through who what where when how and why it's good to ask yourself questions like who collected the data and what is it about what does the data represent in the world and how does it relate to other data when was the data collected data collected a while ago may have certain limitations given the present day situation for example if we collected phone numbers over the past century at some point mobile phones would have been introduced leading to the need for an additional phone number field you should also think about where was the data collected a lot can change across cities states and countries and how was it collected a survey might not be as effective as an in-person interview for example and of course there's the why the why can have a particularly strong relationship with bias why because sometimes data is collected or even made up to serve an agenda the best thing you can do for the fairness and accuracy of your data is to make sure you start with an accurate representation of the population and collect the data in the most appropriate and objective way then you'll have the facts that you can pass on to your team congratulations on finishing this video from the google data analytics certificate access the full experience including job search help and start to earn the official certificate by clicking the icon or the link in the description watch the next video in the course by clicking here and subscribe to our channel for more from upcoming google career certificates

Channel: Google Career Certificates

Views: 15,782

Rating: 4.9829426 out of 5

Keywords: Grow with Google, Career Change, Tech jobs, Google Career Certificate, Google Career Certificates, Job skills, Coursera, Certification, Google, professional certificates, professional certificate program, Data analyst, Data analytics, Data analysis, Data analytics for beginners, What is data analytics, Sql, Data, R Programming, Spreadsheets, spreadsheets for beginner, google spreadsheets beginner, data visualization, data visualization tutorial, data analyst tutorial

Id: Hneax8l4rnQ

Length: 34min 32sec (2072 seconds)

Published: Tue Apr 13 2021