Easy-to-follow video tutorials help you learn software, creative, and business skills.Become a member
PivotTables enable you to summarize and reorganize your data dynamically. But you can't summarize just any data set. In most cases, your data needs to be laid out as a data list. So then the question becomes what do I mean by a data list? In this case, a data list is something that you might think of as a table. with distinct columns of data and each one of those columns having a header. In this data list my column headers are Year, Quarter, Month, Company, and Revenue. And then within the list, we have a series of rows, each of which contains a value for each of those headers.
In the first row for example, we have the Year 2009, Quarter 1, Month of January, the Company is FirmA, and then we have the Revenue for that Month. To be able to use the data inside of a PivotTable we need to make sure there are no blank rows. So if I scroll down in this list, you'll see that from A1 to E49 that there are no blank rows. It's okay to have blank cells. That'll just be included in the PivotTable as blanks. Rather than leave your data as a data list, in Excel 2007 and now in Excel 2010 you can create what are called Excel tables.
And Excel tables allow you to do a lot more with your data than you could with just a normal data list. To create an Excel table, click any cell in your data list and then on the Home tab of the ribbon in the Styles group click Format as Table and then select a format for your table. In this case, I'll just use the simple black-and-white style. Then in the Format As Table dialog box verify that Excel has correctly identified where the data for your table resides. In this case, it is A1 through E49 and then Excel has detected that your table has headers. Those are the Year, Quarter, Month, Company and Revenue values in A1 through E1.
So everything looks correct and I can click OK. Excel tables are the best source for PivotTable data because Excel refers to the table as a whole regardless of how many rows it contains and not just a specific group of cells that you might have to change later. I'll cover the differences between Excel tables and data list as sources,in the movie entitled "Updating and refreshing PivotTable data sources," which you can find it later in this chapter. If you do use a data source that's not in Excel table, make sure that the column headers are formatted differently than the data.
In the data list that I used here to create this table, the values Year, Quarter, Month, Company and Revenue were all centered and displayed in bold text. While we're here, I would like to show you a few Excel table features. Those are the total row and filtering. So let's say, for example, that I wanted to filter this table so that it only displayed results for the month of January. To do that, you click the Month columns filter arrow and then select the values that you want to display. In this case, I want to display just results for the month of January.
So to do that, I will clear the Select All checkbox, which clears all of the checkboxes, meaning no values would be displayed if I were to apply this filter, then I'll scroll down, check the box for January, and click OK. When I do, the table displays only the values for January. To remove the filter, I can click the filter arrow again and click Clear Filter From "Month". You can also add what's called a total row. If I scroll down to the bottom of the table and I'll do that using my mouse's scroll wheel, you'll see that the data just ends in Row 49.
However, if I add a total row and to do that I click any cell in the table and then on the Table Tools design tab on the Ribbon, check the Total Row box. When I do, Excel adds a total row to the table and the value here, 3683, is the total of all the values in the Revenue column. And if you want to use a different summary operation, you can click any cell that contains a total. Click the down arrow that appears and you can select any one of these other functions.
So for example, if I wanted to change the Summary to Average, then Excel will create a new formula, finding the average of all the values in that column. Once you have your source data arranged in a data list, you can create a PivotTable. There is one other case, which I will cover in the movie entitled "Consolidating data from multiple sources," which you can find later in this chapter.
Get unlimited access to all courses for just $25/month.Become a member
Access exercise files from a button right under the course name.
Search within course videos and transcripts, and jump right to the results.
Remove icons showing you already watched videos if you want to start over.
Make the video wide, narrow, full-screen, or pop the player out of the page into its own window.
Click on text in the transcript to jump to that spot in the video. As the video plays, the relevant spot in the transcript will be highlighted.
Your file was successfully uploaded.