 Welcome to Using Excel and Google Sheets to Analyze Data, part of the Research and Assessment Cycle Toolkit Project offered by the Association of Research Libraries and made possible by a grant from the U.S. Institute of Museum and Library Services. This presentation is part of a module that focuses on organizing and analyzing data and includes both descriptions and demonstrations of how to use Excel and Google Sheets to visualize your data. We hope the content is useful to library practitioners seeking to conduct assessment projects. At the close of the presentation you will find a link to a feedback form. Please let us know what elements were useful to you. This presentation will begin with a discussion of some of the most common chart types that are used for visualizing different types of data. Then I'll open up an Excel file and discuss the different chart types while demonstrating how to create them. I'll also orient you to how to create these types of charts and graphs using Google Sheets. The primary goals for this presentation are that you will develop a basic understanding of the different charts that you can create using Excel and Google Sheets and develop a sense of what types and charts and graphs are good matches for different types of data. To follow along on your own computer through the demonstrations, please download the Excel file using Excel and Google Sheets to visualize data under Score Sample, which is available with the module materials corresponding to this video. What you see displayed here on this screen are five common types of charts that are used to visualize data. All of these charts can be very easily created using both Excel and Google Sheets. There are many other types and variations of charts you can create, but in this presentation we're just going to visualize some of the more traditional forms. Bar charts are very helpful for making comparisons across groups. Data that can be categorized or ordered are nicely visualized using bar charts. Fine graphs are great for visualizing illustrating trends over time. While heat maps are great for determining the density or identifying higher or lower values in a data set at a quick glance. High charts are often used to illustrate parts of a whole. For pie charts, the percentages from all slices must add up to 100%. I recommend using pie charts sparingly, especially if you have more than three slices, as then it becomes more cognitively challenging to make relative comparisons. In fact, finally, scatter plots are nice for visualizing linear relationships between two or more continuous variables like GPA and ACT or SAT scores. Data that can be categorized or ordered are nicely visualized using bar charts. Two simple types of bar charts are horizontal and vertical. Others include stacked and clustered. Horizontal charts are nice when you have nominal variables, data that can be categorized neatly into different types like favorite types of ice cream. They are nice when your row labels are longer as they allow for easier readability. Vertical charts are recommended when you have data that can be ordered like age ranges, year in school, etc. Clustered and stacked bar charts are nice for when you have two different categories of data that you would like to compare. Typically, when creating charts and graphs in Excel and Google Sheets, you'll need to have your data already summarized. The examples I'll share in this presentation, I started with a cleaned data set and from there, created a pivot table. And once the pivot table is organized, then it allows for easier creation of visualizations. If you're not familiar with how to create pivot tables, you can learn more about them in a separate presentation that is part of this module series, Analyzing Quantitative Data. To begin, let's open up the Excel file and navigate to the 2019 Student Survey Data Set. Let's just take a moment to familiarize ourselves with the types of data that are in this data set. You can see these first columns are demographic variables describing who completed the survey and some things about them. How the remaining items look like scale items or categorical items. We're going to just create a few simple bar charts to understand a little bit more about the individuals who completed our survey. So let's click on Tab 1, Simple Bar. In here, I've created a simple pivot table with year in school and then counts of how many participants fell into each year in school. So to create a bar chart from here, I'm just going to click within this pivot table. It activates the whole table. And I'm going to go to Insert. We can go to Recommended Charts. And this vertical bar chart is exactly what I was looking for. And from here, we can do a few simple customizations to make it look how we wish. In this case, since we just have one type of data or one categorical field, we don't really need a legend. So I'm just going to click on that and delete it. I'm going to give it a better title. Let's see. I'm just going to call it year in school. Our viewers may not know what this represents, the vertical numbers here. So let's give ourselves a vertical axis title. So I'm just clicking in this upper right corner for chart elements. And then I'm going to click on this right carrot to select primary vertical, which gives us this little box over here. And I'm just going to change this to total participants. The font's a little bit small. So I'm going to just click on this general chart. I'm going to go to home. And then I'm just going to increase the font size, something a little more readable. This gray box here, count of year in school in this filter, that comes from our pivot table format. If we wanted to hide that, that's very easy to do. You know, just click on one of those elements, right click. And I'm going to say hide all field buttons on chart. If we wanted to add some data labels, which I think are nice, we can just simply click on this plus sign and add data labels. There are other formats for data labels that you could see by clicking the right carrot, like inside end, inside base, put some at the bottom, outside end, or data call out. But for now, let's just stick with inside or outside end. Finally, if we wanted our bars to be a little wider, you see here we have a lot of room. We could reduce the gap between the bars. To do that, you can just go to, I'm just double clicking on one of those bars, which brings up the format data point over here on the right. And it's the gap that we're going to just reduce the gap. And you can just scroll or use the scroll bar to change things. So that's a pretty easy to read simple bar chart. Let's go to this tab number two and play around a little bit with clustered and stacked bars. This time, we have our pivot table that I've created from the clean data set. We have year in school over here on the left in rows, and this time we have first generation status for columns. So we have students who are first gen and students who are not first gen. So this data is nicely suited for clustered bar charts or stacked bar charts. So let's first create a clustered bar chart. I'm just going to go to insert. Let's go to back to recommended charts, and we could do this one. This looks fine. So this is your very simple clustered bar chart. This time we need the legend because we have two different categories of data that we're looking at. We don't really need these pivot table buttons, so I'm going to hide those. And you can move the legend to a different location, if you like, to make room for the bars to spread out by clicking on the plus and let's see legend. So you can move the legend to the top like that or the bottom. I just encourage you to play around with it to familiarize yourself with different options. Our chart needs a title and also a vertical access title. So let's add a chart title. This time maybe we want to say year in school times first generation status. Let's add that vertical access title, primary vertical, and we're going to say total participants. Maybe I wanted to make the font just a little bigger, so I'm just clicking in here to activate the chart area, then go to home and changing it to wealth. Then it might be helpful to have some data labels, so I'm just going to go back to plus and select data labels. If I wanted my bars to be, if I wanted to reduce the gap, I just clicked in one of these red bars which opened up the format data series and then we can reduce the gap a little bit if we'd like. In this time, there's also series overlap that allows you to adjust how close like the blue and reds are to each other. For example, we can move it way down here to the left makes the series further apart or you can bring them a little closer together there they're touching and here they have just a little bit of space. So that is just your simple clustered chart. Maybe we wanted to do a stacked bar chart. I'll show you how to do that too. With stacked bar charts, it's nice to put everything into percentages and to do that, we can do that right here in our pivot table. So maybe I want these numbers here to be percents of total so we know how many, what percentage of freshmen were first gen and what percentage were not first gen. So to change this right now, these are counts, we can change it to percents of total. I just right clicked in one of these count cells and then we're going to do show values as percent of row total. So now if we added up these two numbers going horizontally 68 plus 32 would equal 133 plus 66 plus a few little decimal points would equal 100. I'm going to reduce the decimal points down to no decimal points. Now let's, so this changed our data format, but we really want to change our whole chart type. So I just clicked back in here and I'm going to go to design. I'm going to say change chart type. This time let's see what it looks like if we do a bar chart and then up here on the top option we're going to do a 100% stacked bar. So this allows us to visualize the percentage of first gen students per year in school compared to not first gen. So you can see that our not first gen's are about two thirds of the population in each year in school and the first gen's are about one third. And then finally if you weren't crazy about the red colors you could, let's see, format data series, I just right click to select format data series. You could click on this paint can, click on fill and you could change this to something, a color that you find more appealing which I don't know what that would be right off the top of my head. We'll try yellow for now. Here you can see how simple that is. We probably don't need the total participants access label, it's fairly, fairly apparent but it's your prerogative if you'd like to keep that. Now I'm going to take you over to Google Sheets and I'll orient you to the layout of how to do those same types of activities in Google Sheets. I'm not going to go through every step for every type of chart but I just mostly want you to become familiar with the chart editor. So we're just looking at the simple bar. In Google, Google Sheets pivot tables are not quite as robust as Excel. For example in Excel I could move the order of the years in school around to match what I wanted them to be but it's a little more limited in Google Sheets. So I simply just copied the data and then pasted it as values over here so I could manipulate it for my graph a little easier, more easily. So I'm just going to highlight the date of interest, go to insert, chart, it defaulted to this pie chart which I'm not very interested in for this exercise. But when we inserted a chart it opened up this chart editor on the right which is really what I wanted to show you. So under setup you can select the chart type of interest. In this case I just want this basic bar chart and then if you wanted to make some customizations you could go to customize and you could change some of the coloring. Here's where you could add your titles and adjust the formatting. If you wanted to change things related to the series or the data you could click on series, you could change the color down here, scrolling down you could add data labels, you could change the font of the data labels. If you wanted a legend you could have a legend and tell Google Sheets where you wanted it to be and then make some customizations with the access. Let's return back to our slides. So let's move on to line graphs which are great for visualizing and understanding trends over time at a quick glance. Time that is visualized on a line graph can range from centuries to decades to years, months, days, hours, etc. So this time we're going to work with some library checkout data. I just downloaded a snippet of checkout data and we're just going to create a simple line graph based on how many checkouts occurred during each particular time of day. The data over here is the clean data set that I started with to create the pivot table and everything here was already as is from downloading it from the library system except for I created a new column called time where I just extracted the time of day and converted it to the nearest hour. So let's open our Excel file back up and I'll show you. So there's the checkout data and then let's move to item worksheet three line graph. So here we have our simple pivot table illustrating time of day and count. Let's just go ahead and do insert. You can click recommended charts or you can select this little shortcut for a line graph and I'm going to select this first option. Already it looks pretty good. I'm going to hide these pivot table buttons. We don't really need a legend because we only have one line. If we had two different time trends we were illustrating then we'd need the legend. Just give it a simple title, something like checkouts by time of day, add a vertical axis title so people know what these figures mean, axis title, primary vertical and we can just say total checkouts and then maybe we want to increase the font a little bigger. So I just clicked in the table, went to home, increasing the size of the font. So that is pretty straightforward. Our times are a little bit jumbled. If we just let's see, stretching it out just a little more so it's a little easier to read. Doing that same thing in Google Sheets is very straightforward. One thing I wanted to show you, I'm going to go over here to line graph. I'm going to pop in a line chart, I'm going to change that to line graph and so here you can see that our scale is very, very large and we don't want it to be quite so large and that is because the grand total is in here and we don't want the grand total to be skewing the legend or the axis. So I'm just going to, let's see, there we go, in the pivot table, okay here's the problem, under rows in our pivot table it says show totals. Let's just deselect that and now we are looking at more like what we expected and then just like in the bar graphs we could customize how things look with our axis titles and chart titles etc. Date maps are a great tool to quickly identify highs and lows in a data set through variations and coloring and they can be especially useful for cross-examining data with two variables. Typically you'll have all of the rows as one category and all of the columns are another category to form a matrix. The data contained in each cell is based on the relationship between the two variables and the connecting rows and columns and then a color range is selected with one end of the color range being one column or one color and the other range being another color. I'll show you how to do this in Excel under worksheet tab four heat map. This time we have our pivot table already. We have our time of day and days of week and we can create our heat map right here within our pivot table if we like by highlighting the cells of interest and then just going to I'm under the home so we're not actually creating a new chart this time we're just changing how something is visualized so we're going to home conditional formatting I'm just going to click the downward carrot and select color scales and let's just go ahead and select this one down here on the bottom and so let me make my screen a little bigger. You can see the darker numbers are the times of day for day of week where there were the most checkouts so it looks like Tuesdays at 11 a.m. might be the winner as far as the busiest time during the week. Creating something similar in Google Sheets is equally as easy. I'm just clicking here this time like I said Google Sheets is not quite as robust as Excel pivot tables it put the days of the week in a order that did that was alphabetical rather than time bound so I just copied the data and reordered it into this table down here. Same data just different order for the days of the week and simply I'm going to highlight the cells of interest this time I'm going to go to format conditional formatting and we are going to change this from single color to color scale over on the right and for some reason Google Sheets likes to make the lower numbers the darker color and I like to change that around to have the low numbers be white and then the darker colors represent higher numbers so just as simple as that here we see it once again Tuesdays at 11 are the busiest day and time of week at the circulation desk now let's take a look at pie charts pie charts are most typically used to visualize categorical variables so you can see how different parts add up to a whole as I mentioned at the beginning of the presentation I recommend limiting pie charts to three slices or less our brains are well equipped to compare areas in a circular format such as a pie if there are too many things to interpret at once if you have many more than three categories and really there's no hard and fast rule a bar chart is often easier to interpret at a quick glance for example here we're looking at the same data visualize in two ways in this case is survey data in which students indicated how much they spent on textbook textbooks during the semester for this survey question there were six different cost ranges on the left once you get past the three largest cost ranges or the three largest slices discerning differences between the other categories becomes challenging the same data is visualized on the horizontal bar graph on the right and you can see how each cost range compares with each other at a quick glance let's return back to the excel file and I'll show you how easy it can be to create and customize a part pie chart using some student retention data so I'm going to navigate over here to the pie chart tab or let's first look at the data itself so we have a student's admission status when they matriculated and whether they were retained or not retained we have their incoming high school GPAs and ACT and SAT scores standardized so let's click on the worksheet pie chart and here we're just going to visualize the number of students retained versus not retained in the data set so we can just simply insert and I'm going to use the pie chart shortcut and I really recommend just this most basic pie chart there there really is not any logical reason that I find useful to use anything fancier like 3Ds and such we don't really need this legend we're going to instead use data labels so I'm going to delete that I'm going to give it a title while I'm thinking of it and I'm just going to say retention status among actually I'll say year one to year two retention I'm just going to hide this little card here and now we need to just simply add our data labels so we can do that by clicking the right carrot and then do more options and now here we have some more details so I want the category name to be associated with rate with each piece of pie so we don't have to flip from flip our eyes from a legend to the graph so now we have the actual retained and not retained and we have the raw numbers and when looking at pie charts it's also helpful to have the percentage so now we have the percentages the raw numbers and the retention status they've also selected this check mark show leader lines so if I wanted to bring this out the information out then that's what we could do so and then if if red was too bold for you you could just click in the red go to the pink can and maybe you wanted yellow instead any color would do just a slight nuance and Google sheets same data same pivot table this time let's go in there and create a I'm going to insert chart under setup we're going to change it to a bar chart and bar charts automatically and in Google sheets calculate the percentages but we need to not be showing this grand total so I'm just clicking in the pivot table and just like for that other graph for retained we are going to deselect show totals and so then that tip away that grand total which was skewing or pie these colors are a lot for my eyes so if I wanted to change the colors I could just click I just double clicked on the red for example it actually opened up that pie slice for me because it clicked in there maybe we wanted to change that to purple and maybe with the yellow we want to change to I don't know green's not much better but you get the idea my color choices really weren't very good at all there something like that and then you would of course need a title so under customized you could give it a I've got myself a little bit lost your chart and access title and you could say retention status so simple as that finally let's spend just a couple of minutes talking about scatter plots scatter plots are great for visualizing the linear relationship between two continuous variables essentially continuous variables are numeric variables that have an infinite number of values between any two two values a continuous variable could be numeric or date time continuous variables may be things like the amount of time it takes to complete a project or the height of children the amount of rain that fell in a given period of time temperature grade point average SAT scores etc so in this example we're going to create a scatter plot to look at the relationship between high school GPA and ACT and SAT SAT standardized scores so returning back to the Excel file this time I'm going to click on retention data so we don't need a pivot table for this this visualization we're going to actually use the raw numbers just highlighting both columns of interest I'm going to insert recommended chart you could click here or you could just click this shortcut first scatter plot I'm just choosing the basic option we just need to add some column and or excuse me access titles change the title here and add a trend line I'll show you how to do that under chart elements we can go to access titles this time we want to have both titles for both things so on the bottom the horizontal that is high school GPA weighted vertical or it's a standardized ACT and SAT score in the title of our chart we're going to say high school GPA weighted times standardized and then we need to add a trend line or it's customary to add a trend line in this getter plot so to do that you can just select the plus sign chart elements select trend line which added this very faint polka dot line but we are going to customize that a little so it stands out so under trend line is click this right carrot and say more options the first thing we're going to do is change I'm going to change it to a darker red and I'm going to change the width of it so it stands out a little more and then over here back in under format trend line I'm going to click on trend line options it looks like the little graph and way at the bottom I'm going to display the R squared value on the chart again this is a fairly custom fairly customary thing to do so the R squared value denotes the the strength of the relationship between your two variables if there was a perfect relationship you would have a R square of one if there was absolutely no relationship you would have an R squared of zero and you can refer to least in percentages so this relationship indicates that 33 percent of the variance in GPA is explained by ACT scores to create a a scatter plot in Google sheets is equally as simple I would just click on retention data go on to insert chart wow that's a lot we're just going to do a couple of quick customizations here and I say we want to edit the chart we're going to change the charts the chart set up to be a scatter plot and you already know how to add titles and such in Google sheets the one thing I did want to show you is where to find how to add that trend line and the R squared value so you would do that under series or excuse me trend line is what I was looking for trend line I'm going to change that color so it pops out a little more and change it to red change the sickness to four and show the R squared value which comes up here in the legend and maybe I want the legend to be at the bottom of the chart so I just select the legend and put it at the bottom so pretty much the same same thing in Google sheets except some of the options are in slightly different locations so let's recap by now I hope you've figured out that there are a lot of ways that you can visualize data in excel in Google sheets and we only just really scratch the surface in this presentation I hope that one of the main takeaways for you is to remember that there are different types of data that are often best suited or better suited for different types of visualizations so to recap bar graphs are great for making comparisons especially when it comes to categorical data line graphs should be your go to if you're looking to understand trends related to time heat maps are nice for determining data density or identifying high and low values in a data set high charts are a nice simple way to compare parts of a whole and finally scatter plots are helpful when looking to see the strength of a linear relationship between two variables thank you for viewing this presentation on using excel and google sheets to visualize data please use the link provided to complete a feedback form for the usefulness of this information for your purposes thank you