To the Cloud. We were recording. So welcome everybody to this latest and Biden school student workshop. Today we are going to be talking about pivot tables in Excel. And so I'm going to hand it over to Ellen shank is going to be talking about pivot tables. You should have received a downloadable Excel document if you want to follow along and feel free to do so. If you don't, that's okay too. You can just watch Ellen and the Excel spreadsheet and the recording will be posted on the school website within the week, hopefully. So i feel free to interrupt as we go and ask questions. You can also put in the chat. That's fine too. And I'll be monitoring those for Ellen. So take it away. Let me share my screen. Sorry. Hey, everyone can see this. Alright, great. So I'm going to be talking about pivot table. So pivot tables are one of the most powerful and useful features in Excel. With very little effort, you can use a pivot table to build good-looking reports for a large dataset. So pivot table is a way to summarize your data. It's also interactive. So once you make a pivot table and we'll do this multiple times today. You can drag your data around, make your table look different. So you can look at the same data from many different perspectives and learn more about your data. Some of the things you can do, you can group data into categories. You can filter it to include and exclude different categories and also build sharks, which I may, which we may get to. If we don't. I'll show you like the button and you can play around with that. Everything that you can do in a pivot table you can do to your larger dataset. However, using a pivot table makes it easier. It's faster. So just keep that in mind as we're kind of moving through this process. So this is the dataset that we're going to be working with today. So this data has 452 records with five-year-old with information. So we have data, color, region, unit, and sales. So this data actually makes no sense. There is no light. Yeah, makes no sense. It's not like from any sort of actual existing dataset. But what's great about this is that it's really a way that you can learn pivot tables by having different fields that are different values. For example, we have sales, which is a money value. We have region and color, which are string values, and we have deep. So again, just the way that we can play around with the different forms of what we can do with a pivot table. But I'm not going to explain what it is because it makes absolutely no. Alright. So let's talk about how let me drag you guys around because he just got moved right up to where I need to click. Let's talk about how we actually insert a pivot table. So I'm just going to click right here on our first value, this cell A1. And I'm going go to Insert and then to PivotTable. And can everyone see this dialog box that popped up? Great. So Excel, it's asking me the data that I want to analyze. Excel is smart. It's actually highlighting my entire table. You might need to set that this yourself. If you only wanted to put the first two columns in your pivot table, maybe you have multiple tables, so just keep that in mind. It is best practice to put your pivot table on a new worksheet. However, I'm, since we're learning and we want to look at our data, I'm going to put it on the same worksheet. So I'm going to override this and click Existing worksheet and let's just put it right down here. And click Okay. So we now have a pivot table. This is our empty Pivot Table. And to the right we have the pivot table fields. So we have the field names, which are the headings of our columns. It's important to keep in mind that when you are making a pivot table, all of your columns, should I have headaches? 1, that makes it easier for you when you're actually creating your pivot table. But also excel will sometimes yell at you if all of your columns do not have headings. So just keep that in mind. So let's get started. And first you can see we have four different boxes down here and you'll see how all of these work. What we can do is we can basically drag each of our field names into one of these boxes. So if I drag Sales into values, this now gives me the sum of all of our sales. And our, in our guess, some of all to itself. If I drag color into rows, think yeah. If I drag color into rows, we now have sales by color. So that's super cool. Let's say I wanted to make these sales look like Corinthians. They'll have the dollar sign, have a comma. I'm going to right-click and go to number format. So I'm just right-clicking on any salad at, in that sales column. And of course my Excel, it's deserted, defined to be slow today. So I'm just going to get occurring. See it go to this first value. I know that I have no sense in my sales, so I'm just going to have decimal places equal to, equal to 0. Click Okay. And it actually applies that formatting to all of the other cells, to all of my sales. Another thing that we can do is let's say we want to look at these cells from the largest to the, from big to small. So I'm again going to right-click. I'm going to go to Sort, Sort Largest. And now this style format. So having it be as well as our sort will stay no matter how we reconfigure our fields. So if I drag in different fields, you'll see that fails will all leave. Will always look like that and it will always be from big to small. Any questions without inserting a heavy table, doing any sort of number formatting and sorting things like that. So Eleni, you might choose to go over this later in which case you can skip my question, but I just wanted to point out or maybe have you illustrate the fact that if you move color to columns, it's the same information. It's just organizing it in as columns instead of rows. And so that can be valuable if you end up having multiple features that you are trying to illustrate. So Azure, as people are practicing and going along with Ellen's tutorial, you might want to play around and was kind of organizing things and seeing where they go in order. Once you have multiple items in your rows and columns. And that affects how the information ischium? Yes. And we'll play around with all of that later, but thank you. So if you end up changing the data in your pivot table, sorry, in your original data here, you need to refresh your pivot table. So Excel isn't going to automatically know that the data here has changed. So let's say that I want to change this first item right here instead of $11, I want it to be $10 thousand. Nothing updated here because red should move to the top. Since with that, how we sorted here. So what I need to do is I need to refresh. Two ways to do this. Can right-click, hit refresh, or you can go to the PivotTable, Analyze tab and hit refresh. And now you can see that Excel has known that our data has changed and has refreshed based on that, if I add another column here, I actually need to create a whole new. I need to go back to insert pivot table and create a whole new one. So let me change that back to $11 and hit Refresh. So just an important thing to keep in mind that if your data does change, you need to be refreshing. Your Yes. Make sure that you're refreshing. So now let's drag a second value to our value field. So let's drag unit to values. So now we can feed the thumb of our unit and our sales by these things in our rows. We can also display these values in different ways. So let's get rid of this unit. I'm going to add a second sales value. So even though I already have a sales value down here, I can actually added again. And what I'm going to deal with, I'm going to go down to these two dots right here. And I want to right-click and go to Field Settings. So the first thing I'm gonna do is I'm going to say show data as and do it as a percent of the overall sales. Click Okay, and now these are a percent of this cell right here. I could also show the average. So again, let's go right down here too. I've, to our values, I'm right-clicking. And incentive show data as I'm going to go to summarize by and go to average. And now it's going to show me the average of ourselves. And that's still present. So give me one seconds. So I actually need to go back to show data as and change this to. Just ignore this. No calculation I only want summarized by. And now we get the average. Other things I could do is I could show the count. So this will basically show the number of sales in blue, silver, green, red. So I go to count and we have a 137 blue, 95 silver, cetera. Questions about changing the value. All right, we can also group these two together. Let's say I want to do blue and green. So I'm going to highlight will hold Command on Mac, control. On a Windows computer. I'm going to right-click and I'm going to hit grep, this create a new group. So I now see those two together. And I can name this group. I don't know, let's call this blue angry. And now this has also become another field that I can use. So this is called Color 2. So if I move that around, just blue and green are going to change. I can move that back. So this might be helpful if you have, I don't know, states and you want to group them into north, south, east, and west. If you have the first half of the alphabet of names in the second half of the alphabet, you can group the answer to see these overall characteristics. Let me get rid of this. We don't want that anymore. And if I just and I could also right-click and ungroup them as well. And that would also get rid of it. Or I could've dragged that field up here in case I want it to use it, to use it in a feature table. So we also can filter our data. Adding region to filter. It adds this little thing up here, and I can click on this arrow and only look at UTS. Only look at East, for example. So this is just sales in the East. I go look at east and south. So again, really great way if you want to look at individual sections of your data. Questions on anything so far. All right, so let's talk about a two way table. So the Sarah hinted at this. So I am going to look at regions as my columns. And let me get rid of this. So regions of my columns, I still want sales. So now I can look at each region. Now, notice that my filter is actually still set. So I need to change this to show all of my east, north, east, south, and west. So now I have region and color. I can switch these. My table is going to be the exact same. So I go to region and dried color up. Table the exact same. Unless that's what I did before. I don't remember. I think I had colors here, so I try to go good, but the numbers are the same. So just a way of how you look at your data, depending on how you want to present your table at the very end. All right, so let's go back to if you were to region, go back to color. And let me change the sales to be currency again. So I'm just right-clicking, going to number format, currency. All right, so now let's play around with date. Going to drag Date to columns. If we click this plus Br, we can actually see the different quarters of our year. So Excel knows that the first quarter of the year is January through March, second quarter, etc. So let's say I don't want that. I just wanted to show years. So I'm going to get rid of that. I'm going to right-click on this D. And I'm going to go to group here. And you can see that this brings up different way that you can group based on the be the date. So I'm going to de-select month and quarters and I just want to show years. Now that plus sign is gone. You can go into even more. If you wanted to look at month. We can do month. Now it's way show me every single month. So really cool thing that you can do if you have a D variable like I do over here. Something else, if we go back to our years, is that you can see that in silver and the Spurs two years there were actually no sales. So that's something that I may not know looking at my raw data by using this summary, I can see it better. All right, let's see. Let's move on to calculated fields. So the calculated field, they're useful when we may not have access to our original data. Maybe we just have a summary. It can expand our analysis. It also allows you to do calculations here rather than go back to your original table and have to, to do them. So I'm actually going to do this using a new data set that is not in the workbook that there are sent out, but when we sent out send out an updated one after the workshop, it will be in there. So I apologize. But I wanted this thing to actually make them. So that's why I have some new data. This new data shows by country different fails of food and their amount. So I'm going to click on this first cell right here, go to Insert Pivot Table. And for this one I'm going to put it in a new sheet. Click Okay. You can see that Excel has created new sheet for me down here. So let's drag amount to our values and let's make that currency. I'm just doing this because I think it looks better and it reminds you of what that fell actually knew. And then let's drag Country two rows. So let's say that I want to impose a 3% tax on all sales over a 100 thousand. So what I'm going to do is I'm going to click on any cell in my amount column, go to the PivotTable Analyze tab, and go to this field, items and sets. So I'm going to click on this little drop-down and it gives me the option of Calculated Field. Now I can name this, so let's call this tax. And from the very first week we learned if, so, if our amount and it's important that we get amount from this field is greater than a $100 thousand. One-to-many arrows. Yeah, 12, eight zeros or make there. I'm going to impose a 3% tax on the amount. And if it is not greater than a $100 thousand, There's going to be no tax. So my other option is a so no tax there. So remember, if says, if this thing is true, this is what I want you to do. If it's not true, this is what I want you to, to do. You're going to press OK. And now it's added tax as a completely new field. So I can actually come over here and feed that tax had been added. And now I can use this calculated field, add them creating new summaries. And something that I'm not going to do because the when I did it this morning, my Excel crashed so we can create calculated items. So let's say I wanted to look at tax for Europe. So I would add France, Germany, and the UK together. And I could see tax by that grouping. But again, not going to do that because my fellow crash. But when I send out the workbook at the very end, we can take a look at that. So let's play around with pivot chart. So PivotCharts is this button up here, I'm in the PivotTable Analyze tab. And that's basically going to create a graph of my data that I have in my table. So again, we can play around with this. I did not plan well, I'm going to do with this. So we're just going to see what happened. Since I have a deep field, I can actually insert timeline, which is really cool. So I could look at my failed and my taxes. Or I can make this graph only be for June. I can make it for September. I can make it for multiple things. So that's cool. And this would be a great thing to have on the first page of an Excel workbook that you send to a client, to your boss and be like, Hey, play around with the. But the data your, yourself. So let's get rid of that. Going back to this charts, I could filter it. So I could only look at certain products will appear at adding me too. It's adding a filter here. Flip, they only wanted to look at, I don't know, apples. Look at Apple's. Look at apples and oranges together. I'm really just like freestyle and years though. I don't agree. I think we could look at the, you will do it. Now I'm looking at bananas as well. So either we have a category and a product which are two different items, I guess so that's why it's adding a second bar. So again, if you like one to create a chart based on your table, the other thing that, that you could do is that you could copy your pivot table into a separate sheet and you could create a chart just like Sarah at last week. If you go to Insert, go to the bar chart and it would basically create the same thing, but this is static compared to the pivot chart over here which you can play around with. Yeah, I know that was super fast. By any other questions I can answer in pivot tables are things that we talked about before. I'll just add a user case while people are thinking of questions they might guys. So this is a private sector example, but back when I worked in customer service, we had to provide, gosh, what were they called forecasts every month for how much, how much sales we were going to have. So we were able to do data import of all of the purchase request that we knew that we had. And then I could do something very similar to what Ellen's doing now and say, okay, well, these are the ones that I know are going to shift this month. These are the ones I know orange next month. And by having it in a pivot table, I was able to have all of these charts and graphs and everything set up. So all I had to do is refresh my data and everything, just re updated for me. So if you're working on any project where you're continually getting updates in data. But all of your outlets are the same. You can basically create this like kinda like a data dashboard. It's like basically what Tablo now does through a subscription service you can create through a pivot table in Excel. So and it doesn't have to be sales obviously, but I mean, it could be health policy, information, education policy could be all kinds of any kind of data. And, and you could set it up. Oh, did you want to show them a slicer without art of your part of your plan. I did Internet, I definitely can. So just to elaborate on the kind of data dashboard part of this, if you could imagine, you could set up your sheet, kinda be visual dashboard and then on a slice or a similar to a filter, but it has a more visual look to it. So I'm Alan can show you that too. So now we can, yeah, this is going to change our table and our graph. So definitely a lot of visual. You can imagine a scenario where you're the one that is doing the data analysis. And somebody calls you and says, Hey, quickly, I need to know all of the sales of broccoli. And last month. You can quickly click on broccoli and there you go, you have it. That can be one of the benefits if you're tracking any information and are responsible for providing up to them and it updates for people internally. Something else I did want to mention is that you can call these different cells from your pivot table on a normal sheep. So I could go into the sheet and I say equals sum. So happens they don't NameError things equal sum of all these things. But keep in mind that if your PivotTable changes, this isn't going to work. So if I change something around and my pivot table. So let's, I don't know, Let's change this filter. Actually it, yeah, So now we have less items here. So I get to work. So sometimes that will give you an error if your pivot table drastically changes though if I like completely changed, let's say I don't want amount over here anymore. And I want to do like product and my rows. It's possible that your calculated field in your other, that was not a good word to use because we just learned how to do that. In that it your cell and your other she may change. So I've used this before. Using it in a separate sheet, not related to a pivot table, but calling items for my pivot table when I knew I had my data table was done, I wasn't going to change it. So yeah, just keep that in mind if you're doing any sort of calculations on the side, calling item in here, but then it changes, you're going to get errors. And that's again, if you want to do calculations here, then maybe the best option is to copy your pivot table onto a separate sheet. So now it's static and you can work with it. Any questions from anyone. There's also a lot more things you could do. A pivot table that I definitely didn't go over. And they're like Googling. It's probably your best friend. With this. Also, the Mac version of Excel is less robust than the Windows version of Excel when it comes to pivot tables. So there are things you can do in Windows that Mac just doesn't allow you to deal. So like also keep that in mind. If you're doing anything for our project, could always use the virtual lab if you only have access to Mac and do pivot tables on a Windows Excel there. But things should mostly look the same. It just you're going to have more options than this pivot table tab. Then you do it on Mac. Now. Awesome. Thank you so much. Now we'll give people a couple of minutes in case they have questions. Also, you know, we've done several Excel workshops now. So if you had a question from one of the previous ones and you want to ask that we can certainly answer those questions too. I'm going to put the evaluation again in the chat. So if you can take some time to take that, we do really appreciate your feedback and try to always try to refer to it when we're making updates for the next ones. Yeah. So if anybody has any questions, feel free to ask. Otherwise, we can just call it a day. No questions. It was just really helpful. Thanks Allen. Awesome. And the other thing I'll say is if you are really comfortable with the software and want to teach other people or any other type of information and you would be interested in teaching a workshop. I'm always open to that. I'm more than happy to have students contribute. And I already knew of Ellen's amazing skills. And she she was willing to do this. I'm very appreciative of that. But it's not the exclusives. They're Island club. We're more than happy to have additional people contribute if anyone has the ideas or recommendations. All right, so with that, I'm going to stop the recording. And once again, thank you very much all of you for attending. And Alan, thank you so much for showing us how did you put the tables today. Thank you.
Student Workshop: Excel: Pivot Tables
From Sarah Marshall March 22, 2021
2 plays
2
0 comments
0
You unliked the media.