Easy-to-follow video tutorials help you learn software, creative, and business skills.Become a member
Excel tables enable you to analyze financial data in some interesting ways but they're not as flexible or powerful as another Excel tool, the pivot table. A pivot table lets you rearrange a data set on the fly so you can analyze it from several different perspectives with a minimum of effort. To create a pivot table, your source data must be laid out as a table with column headers and no extraneous data surrounding the table. Excel tables are the best source for pivot table data. Now, if you do use a data source that's not in Excel table, make sure the column headers are formatted differently than the data.
What I like to do is format the cell so that the contents are in bold and centered. That way Excel has no problem detecting that the column headers are separate from the data that's in the table. So how do you create a pivot table? Well, you select any cell that's part of the data source and then on the Insert tab, click the Pivot Table button and you got the Create Pivot Table dialog box. So, you should verify that the data you selected is being used to create the pivot table and here we have the Company Revenue table, which is here, and then you've got to choose where you want the Pivot Table to be placed.
Now, I always place my pivot table on a new worksheet and the reason I do that is because the pivot table will take up quite a bit of room and you don't want to crowd either your source data in case you want to go back and look at that, or the pivot table. So it just makes more sense to me to put it on its own worksheet. So when you are ready, you can click OK and you get the bare bones outline of a pivot table. Over here on the right side, you'll see that you have the Pivot Table Field List and a field is simply a fancy name for a data column, something from the source data.
So you will remember we had Year, Quarter, Month, Company, and Revenue and if you don't see the Pivot Table Field List, I'll close it to show you how to open it back up. All you need to do to bring it up is to click anywhere on the pivot table and then on the Pivot Table Options tab on the ribbon, in the Show/Hide group, click Field List and it comes back. Now, you can start adding fields to the pivot table. Let's say that I want to have the companies' names along the top.
That would be the Column Labels. So those have been added to the body of the pivot table, and then we do Year, I'll put that on the Row and then I'll do Month and you can see we have a great outline for the pivot table. Now, I always add the data last, so that I have a better idea of how the pivot table looks structurally. It's just a personal preference. You can do it anyway you like and then to add the Revenue data to the pivot table. You drag it from the Pivot Table Field List, the choose fields to add to report area and drag it down to Values, and there we have the revenue and also Excel gives you a fair number of summary options.
In this case, we have a Grand Total, which is for Firm A and Firm B, and then we also have a subtotal for the year 2008. Now, at this point, the subtotals are at the top of each group. I actually prefer for them to be at the bottom and there is a way for you to change that. So, what you do is you go up to the Design tab on the ribbon, in the Layout group click Subtotals and select the Show all Subtotals at Bottom of Group options. So, now you have 2008 total at the bottom of the 2008 group and I think that just makes more sense.
Now that you've taken the first step and learned how to create a pivot table, you can move forward and discover just how powerful a tool pivot tables really are.
Get unlimited access to all courses for just $25/month.Become a member
82 Video lessons · 64828 Viewers
80 Video lessons · 124385 Viewers
52 Video lessons · 60313 Viewers
59 Video lessons · 46137 Viewers