Getting started with PivotTables

Description of the video:

>> >> You can hear me. So if one of my assistants can let me know, or if somebody can give me thumbs up, thumbs down those types of things. You hear me, all right. I can see Oh, great. People are telling me they can hear me. Perfect. I see before we even get started that we have a question about the emails, or the follow up notes. We, before we send those out, we have to have the videos. We're recording the sessions and the videos need to be captioned to meet accessibility requirements. And so the videos are being captioned before we can put them online. And when that happens, the follow up emails will go out. We will put the links in the chat at the end of class today. And you can copy those links and if you need them again, ask and we will put them in the chat again and then you'll have the links without waiting for the follow up email. Okay, that was a question right off the bat. So I'll get started now. My name is April Law. I'm going to be instructing today for getting started with pivot tables. I have a couple of assistants with me today. They are listed as panelists if you see them there, Jason and Rachel. If you have any questions at all, you can put those questions into the chat or into the Q&A at the bottom of Zoom. If you're not familiar with Zoom, if you move your mouse down towards the bottom of the Zoom window, you will get a toolbar. And in that toolbar, there will be a Q&A option. There's also a chat option. And you can put questions in either of those. And one of my assistants will be monitoring that, those questions and they will either answer them in the app or they will interrupt me and have me answer it for you. So, In this session, I'm going to introduce you to pivot tables. We're going to see what you need to do before working with a pivot table. We're going to see why they're called pivot tables. And then we're going to create a pivot table. And at the end of the class, I will be giving you the links for the corresponding web pages and documents or not documents, workbooks that we're using today. I am going to be working in Office 365. That is what is on the computer I have available to me at home. Office 365 works almost exactly like Office 2019, which works almost exactly like Office 2016. Everything I'm going to do do today can be done in any recent version of Excel and will work the same. Only difference maybe they've changed the color of the icons or they've changed the style of the icons. But, but this the process will all be the same. I am going to show share my screen now, Which 1am I going to use? If you have been taking our other workshops, this one starts a little different. Because you have, there's lots of stuff you need to know about pivot tables to create a good pivot table. So there's a lot more talking at the beginning of this webinar. First thing we're going to talk about is what are pivot tables. Pivot Tables are a way in Excel that you can analyze data and make decisions. Pivot Tables are very easy to create. They are dynamic, which means they can be changed very quickly and very easily and they can be pivoted. So you can build your pivot table and you can say, oh, that's pretty good. But that's not quite the view of the data I wanted. And you can flip that pivot table around and then you say ah, that makes the data makes sense. That's why they're called pivot tables. You can use these pivot tables to organize data, compare data, make summaries of your data. And a great way to think about a pivot table is that they are there to answer questions about your data. So a good way to start creating a pivot table is to think to yourself, what exactly do you want answered about your data? What questions? Some examples of some questions you might answer with a pivot table? How many students took accounting 101 and got above a B? How many calls did a support center get by day, month, quarter and year? Like we can do that with a pivot table. How many people purchased tickets to a rated PG movie? What was the total income for a certain product in a certain region? Or what was the salary for a company by the different departments? These are types of questions that you can ask from your data in a pivot table. This is an example of a simple pivot table. On the left, let me get my annotating tools up here. There they are. These are, this is my source data. This is what I have in Excel. Right? I have ID, age and rating in three different columns. On the right, I have my pivot table. The pivot table itself is right here. This is the pivot table field list. This is where I build my pivot table in the field list. So when I take my source data, I have three columns here, ID, age and rating. Those columns correspond to the fields that are in my field list. So whatever columns I have In my Excel worksheet, those are the things I use to build my pivot table. In this pivot table, I have my ratings field in the columns area. You can see rating here in the columns area. I have taken my age field and put it in the rows area, right here. And then I have to put something in the values area. And what works here is the ID. So I bring the ID to the values area. And I can see my simple pivot table here, showing me the age of the people 25 and older, who bought tickets to a rated PG movie. Right? Pretty simple. It gives us the data we need and answers that question, but this data, this pivot table can be pivoted and we get this. This is the same data. Right? This is all the same data. We're using the same source data from our work our worksheet here, but in this one, I took age to columns instead of ratings, and I took rating to row. And this is the same data, but it looks very different. Right? But you look at the numbers are exactly the same. So laid out different, it puts a different emphasis. The one here on the right, the emphasis seems to be on the rating. The one here on the left, the emphasis seems to be on age, but it's the same data. It just gives us a different view. All right, before you begin working in a pivot table, or before you begin creating a pivot table, I should say, you have your source data. This is the data that is in your Excel worksheet, right. This example I have here is a list of employees. I have employee IDs names, hire date, cities, regions, salary right. Some things to remember about your source data that you can do ahead of time, that will give you better pivot tables. First of all, all of the related data should be in the same column. That means all of the hire dates should be in the hire date column, right? And all of the things in those columns should be consistent. For example, if you are working in the region, column here, column H, and you're using Central, you either always type out the word Central, or you always use C for Central. You can't type it out sometimes and sometimes use C. That may make sense to you. But Excel sees that as two different things. So be consistent with how your data goes in. One row equals one record. So across each row here is the information about one employee. We don't add a second employee down there and call them M. We don't add a second row because one employee maybe has a longer name and it needs to go down on a different row or whatever. One row is one record. You want to make sure to have unique headings. These are the headings across across row one. The headings, if you remember earlier we saw are what you use to build your pivot table. So if these are not unique, you're going to have a hard time building a pivot table. So you want the address to be address, city state and zip, not address address address address. If you do that, it's going to be really difficult to build your pivot table so that it makes sense. You want to have a unique identifier. Unique identifier is not required by Excel to make a pivot table but it will make your life much easier. Sometimes the unique identifier is obvious. In this case, it is employee ID. Right. Everybody has a unique ID employee ID. Everybody has a unique student ID, any sort of number that's assigned a membership ID. So sometimes the unique identifiers are obvious, sometimes you don't have one. So you just make them up. You start in row two, and you just number them straight down one through whatever. Even if you don't use that unique identifier for any other reason, it will help Excel see that there is a difference between two people who maybe share the same name, so that Excel knows those are two separate entries, not the same person in there. And then that Excel will count that correctly and work with that data correctly. Okay. Moving on. This is a couple of examples here. The first one is not so great data. In this we have two people with the exact same name, but we have no unique identifier to know if they are the same person who moved or if these to people with the same name. We do not have the street address separated into multiple columns. Without separating this into multiple columns, we have no way to build a pivot table or do any kind of analysis on the states. With the data in one column like this, we can't look we can't have Excel find out who's from Idaho. It can't do that, because we don't have a separate column for that. Same with the class and the grade over here. With the class and the grade in one column, that way, we have no way to look up English 1100 by itself. Excel can't do that. It can only look for English 1100 slash C. So this is some not so great data. This is better data. On the bottom, we have ID numbers so we can see that these are two separate people named Alex Smith. We have the address separated out. So if we wanted to, we could figure out who in our organization is from Delaware. We have the department class and grades also pulled out separately so we could search for people from the [inaudible], people who took classes in the English department specifically who took English 1100 and specifically who took English 1100 and got to see we can do those types of searches and data analysis with this better data. All right, so that's the end of the slideshow. And I'm going to get in now and start showing you some Excel. Here is my exercise file for today. Again, I'm working in Office 365. But this will hold true for any version of Office with slight variations in how the buttons look. So, what we have here is a list of stores with their states region sales rep and the gross sales for each store. This has some things that I just talked about. Notice column A is the store number. So each store has a unique identifier. And notice column B, C and D, where the addresses have been separated out so that we can do some data analysis by state, even by city within the states if we wanted to. So the first thing we're going to do is we're going to make this worksheet into an Excel table. If you were with us last week, we talked a little bit about Excel tables. We'll cover that quickly again. An Excel table by changing plain data into an Excel table, you get a lot of benefits. You get automatic filters built in. You get total row built in. But the reason that this is a good idea, not required, but a good idea to do when you're working with pivot tables is because by turning this into an Excel table before I build my pivot table, if I add data to this, all I have to do is refresh my pivot table. I click the refresh button. If I don't make this a table, and I add data, I'm going to have to rebuild all my pivot tables, right from scratch. But by making this a table first, all I have to do is refresh. So we're going to make this a table. We're going to go to the Insert tab, tables and table. Where's the data for your table? It's here, my table has headers. Okay. Here we go. I'm going to quickly. Where is it? My thing is okay, never mind. My ribbon got much smaller. Okay, so here's the table. That's all we're going to do, we're not going to filter here, we're not going to put in the total row. We're going to begin building our first pivot table. And the first pivot table is going to answer the question, how many stores are there in each region. So to build the pivot table, I'm going to go to my insert tab. And I'm going to click in the tables group, and click pivot table. And it says here, what data do you want to analyze? I want to analyze the data in table one. Where do I want to put this in a new worksheet, so I'm going to click okay. And here is my new worksheet. Every time you create a new pivot table, it puts it in a new worksheet, and it you would be wise to immediately rename this worksheet. So I'm going to immediately rename this worksheet, stores. You may think that you will remember which worksheet is which, but after you do three or four of these, you will wish you had named them. So we're going to name this. And I'm going to zoom this in a bit so that those of you on smaller screens can see what's going on. So what I have here on the left -- where are my annotate tools? On the left, I have my pivot table. This is where I'm going to build the pivot table. More specifically, it's where the pivot table is going to appear as I build it. On the right, I have my field list. Notice the fields in this field list are, they correspond to the column headers of my data. And here are the areas. This is where I drag the fields into these different areas to build the pivot table. Right. So the really great thing about pivot tables is that you can't mess them up. Right, You, I mean you can, but they're really easy to fix. And you're not messing around with your underlying data. You're not adding data or deleting data, or doing anything to your data. You're just rearranging the data in a pivot table. And if you don't like the way it looks, you move things around. Or you take everything off and you start all the way over. You can't really mess up a pivot table. Okay. So, I'm going to get rid of all those drawings. And I'm going to begin building the pivot table. Again, we're trying to answer the question how many stores in each region. So I'm going to start with region here in my field list. Notice when I click on region, it's highlighted in green and I get the move cursor, so I can press and drag region down to the rows area, in my areas there. And now, my field my pivot table. On the left, you can see the regions, Central, Pacific, and Southwest. I'm going to add the store to the values area. The values area is where I want to do some sort of calculation with my pivot table. It can be as simple as counting things, which is what that did. It counted up the number of unique identifiers within each region. And my pivot table is now showing me that in the central region, I have 30. Pacific is 14 and Southwest is 16. Right? So this is a very simple pivot table, but it gives us the information we wanted and answers that question how many stores are in each region? So I'm looking at this and maybe I think, okay, well, this is good. But I need a little more than just that. Maybe I need to know how many stores are in each state within each region. So I can add state to my pivot table and come to my field list and I'm going to pull states down to columns. Wow, that is not great. Right? So I can see here I have California. And I can see that there are eight stores in the Pacific region in California. Right? So the data is there. But it's not necessarily helpful because it's still pretty -- you still have to line things up and look and pay close attention to what you're seeing here. So this is what pivot tables are for. You think okay, well, that's not great. So let's pivot this. Let's, maybe if we bring region up to columns, and we put region under state. Oh, no, that's even worse. Because now we have Arizona, Southwest one, Arizona total one. That's doubled everything, right. Again, not great. But again, this is what pivot tables are for. We just move it around a little more, so let's take region back to rows. And let's bring state down under region. There. See the difference in that? It's the same data. We haven't messed with any data, we haven't added anything or deleted anything. All we've done is changed the looks of that until it makes sense to us. We can now easily see how many stores we have in each region. And how many stores we have in each state. We can change the names. See how here it says count a store, which is excels way of saying it counted up the number of stores. We can just make this say stores or let's make it say number of stores. And then I can Expand column B to fit that heading. All right, we can change what they say. We can change how they look if we -- have to move on my little hidden Zoom things around here. On the pivot tables and working in pivot tables, we get a pivot tables analyze tab and a pivot tables design tab. So we're going to go into this design tab. And we have this option here for report layout. And, by default, we see the report's in compact form. But we can change this to one of these other forms. I'm going to choose tabular form. This is kind of my go to when I'm creating a pivot table is tabular form, because what it did there is it took everything out of column A and spread it across A, B and C. So we now have the regions down column A and the states down column B and the number of stores down column C instead of having them all sort of squished up together. But that's really just a preference thing, how you like it and how much space you have on your screen. We can filter. If we want to filter by state, this drop down right here lets us do that. I want to see only the stores in Kansas. There we go, I filter to see only Kansas, I see there are two stores in Kansas and that that is in the Southwest region. I can remove that filter. Again, click on that, select all, and okay. All right. So that was a fairly simple pivot table. If you have any questions about that, put them in chat. I'm going to move back to my sales reps worksheet, my table here, and I'm going to create another pivot table. This time I'm going to answer the question what were the sales region. Right? So I have -- I've clicked somewhere in my data. I'm going to go to insert tables, Pivot Table. Again, the data I'm using is in table one, and I want this in a new worksheet. I click okay and I'm going to rename this sales. And I'm going to begin building my pivot table. I am answering the question as a reminder, what are the sales for each region? So I'm going to pull region to rows. And I'm wanting to calculate the sales so I'm going to take gross sales and put that in the value. Notice when I pull that down there, down here -- when you pull things into the values, it doesn't say gross sales anymore. It says sum of gross sales. It tells you what Excel has done with that value. The last pivot table it counted. Remember it said count of stores? This time it says sum of gross sales. Excel makes its best guess. Often times Excel is right. But not always, as we know. So we can clear that. We could change this to do something else, but we actually want it to be the sum of the sales. What we don't have that we need is this brings us in even though we were working with currency in the worksheet, in our source data, when Excel brings this into the pivot table, it loses that currency formatting. To get this back, we can click this drop down here, bsome of gross sales, and choose value field settings. So the value field settings dialog box does a lot of stuff. We're not going to see everything it does today. But in the online course analyzing data with pivot tables, you use this value field settings dialog box a lot. You can change the calculations Excel does, you can have Excel do some pretty hefty calculations in a pivot table, but today, we're just going to look at the number format. We're going to change this number format to currency. And we're going to take away the decimal places and click okay. And ok again, and now it looks like money. Right, we can see that the central region had $35 million worth of sales, and the other two had about $19 million worth of sales. So we think, why, right? This is a good start. But maybe we want to know why? Well, did the central region, does the central region have more stores? Do they have more sales reps? Do they have better sales reps? Why is the central region so much more than the other two regions? So we can add some things to the pivot table and try to answer that question, as well. We're going to add the sales reps to columns. That gives us some data. But it's not real easy to read. We can see that Bill here must be in the Southwest region and his gross sales were 2.385 million. But Carl is in the Central region and has 6.5 million, right? So the data's there. But it's, it's kind of confusing to read. We can, again, move things around in this pivot table. Let's take the sales reps under region. There. Now we can see the sales reps. And the regions, we can see the Central region and the Southwest region look like they have kind of similar number of sales reps, but the southwest region is still lagging behind the Central region. What could be causing that? Maybe the number of stores. So let's see. If we take the store field, and we pull it to values, you can have lots of values happening in a pivot table. There's our count of stores. Well, there we go. That answered that question. The central region has 30 stores, and the southwest region has six. Right? So we could continue adding to that, moving things around until we get the answers to our questions in the pivot table. We can add a filter. We haven't seen this yet, but there is a filter area here on our areas list. We filtered earlier on this other pivot table by state, but we can add something to the filter area. I'm going to put state in the filter area and then state appears up here at the top. So even though state is not in my pivot table, I can still add that as a filter. So with state here I can say, oh, let me see all the stores in Indiana. Okay, if we have five stores in Indiana, and one sales rep, Joshua with $6.1 million worth of sales. So even though something's not in the pivot table, we can still use it as a filter. If I click that filter again, and click all and okay, there is our pivot table. We can also still change the report layout. This time, we can put it in outline form, and it Expands out maybe easier or more difficult depending on your preferences. A couple other things to show you. When you're working in a pivot table, as long as your active cell is somewhere in the pivot table, you will see the field list. If you click outside the pivot table, the field list disappears. This can cause a moment of mild panic. And then you just click back in your pivot table and your your field list comes back. Now what if I've decided I don't need my sales reps, I want to get rid of something on this pivot table, but I don't want to start all over. I can take whatever I want to get rid of, say sales reps down here in the rows area, click on sales rep and just drag it off field. And now it's out of my pivot table. That's how you get rid of things that you don't want in a pivot table. You can add them back by pulling them back down there. Okay, if you have questions, you can go ahead and be putting those in chat. I'm going to spend a couple minutes showing you how to find and use our online training courses, our IT Training online training courses. I'm going to share a different screen. If you are interested in learning more about pivot tables, we have a course through IT training called analyzing data with pivot tables. All of the courses we do at it training are online self-paced and available to anyone, regardless of your association with IU. You do not have to be an employee or student at IU to do our courses. To see our courses, we're going to start at the IT Training website, and one of the assistants will put that into chat for you. We're going to click on explore topics. And here we have this option to see all Expand courses. Expand is the portal that IU uses to distribute continuing ed and not for credit courses, and that's where all of our courses are. So to see all of these Expand courses, we click there and now we see a list of all of the training Expand courses. We don't just do Excel. We do audio, video production, web design, graphic design, accessible documents, all sorts of things. But we'll scroll down scroll down today until we see Excel analyzing data with pivot tables. I choose that. And in this course page I choose view course and Expand. Here's the course and Expand. I click login or sign up. This is where things get slightly different for anyone who is outs coming to us from outside of IU. If you are associated with IU and you have an IU username and passphrase, you simply log in. If you are not associated with IU, you create a guest account. The guest account is also free. We just have to have a way for you to work with Expand so you have to create the guest account. Once you do this, you can log in to IU Expand and join any of our courses. Again, they are all self-paced and online. That was a lot of information that I just gave you about how to do this. So we have all of that that I said in about the last two minutes is right here on this web page, which Jen has just put into chat for you if you want to copy that and paste it into your browser. There are instructions here for IU users, as well as instructions for non IU users. Either way, you can get that information there. The file I use today, getting started getting started pivot tables, is available in this box in this box file. Jen just put that in there. It is Open Source. You can get in, you can download this. And then you can play around with pivot tables using this pretend data and not have to work with your own data or make up your own pretend data. You can get that there. Jen has put that link in the chat for you as well. Next week, we have, what do we have next week? On Tuesday, charts and graphics, then premiere rush and accessible documents. This coming Thursday on April 23, we have getting started with Adobe applications. So these are all taking place at either 11am or 2pm. You can get to any of these to register on our getting started page from IT training. >> Hey April, can you try some -- [inaudible]? April. >> Yeah, sure. >> Have a big question about -- this is -- we often get this it's an Excel related question from Britt which is can you use numbers with leading zeros such as ID numbers or zip codes? And I know we don't have an example in here, but could you go to the value field settings for one of those -- >> For one of these numbers stores? >> Yeah, yeah. [inaudible] and just I think that that setting I think that -- >> The tier, right? Custom. >> That custom one. Exactly. >> Yeah so, this was a roundabout way and actually I, let's go at it, not from value field settings. Let's say you had that in a regular set of data like this here. You can change the number format, let me, right here, number. If I go to format cells and then number group and custom, you can set as to force those leading zeros by saying that you something has to have 12 digits. And if it doesn't have 12 digits, you fill in the missing digits with leading zeros. We have what, what classes that in, Jason that we do that? >> I can't remember right now also, can you check out under category? Can you check out special to see if zip code is built in there? Some of them are built-in. >> Yes. So if you're working with zip codes that have leading zeros, this is where we see that a lot. The and I think that is in the working with data class. I think if you do our working with data class, maybe Jason can help me remember. The leading zeros are in that. If you set the number format for zip code, and the zip code starts with the leading zero, it will keep the leading zero. That's how you get those in zip code. If you're talking about account numbers that have leading zeros, it is the custom format here that you then put in how many digits you need it to be, if that makes sense. >> Yeah, yeah. Can you show us in cell h2, can you type like 1234? >> Okay. Yeah. >> I guess. >> Okay. And then I type yes. So I set -- let me go back and show you. I set my custom was to have five digits. So there are five zeros here. And so I click okay. And when I type fewer than five, oh, it didn't do it that time because I didn't do that all the way down did I? I'll do it here. If I type fewer than five, it adds the preceding zeros on the front. So it is in number format and custom. If that didn't get your question answered, who was that, Britt, then I'm happy to keep going and explaining it in a different way. >> Thanks. Thanks was cool. >> Okay. >> Thank you. >> Okay. Thank you. Anybody else? All right. I'm going to stop my share and mute my mic.