Easy-to-follow video tutorials help you learn software, creative, and business skills.Become a member
Excel tables enable you to analyze budget data in some interesting ways, but they're not as flexible or powerful as another Excel tool, the PivotTable. A PivotTable 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 PivotTable, you must have the source data laid out as a table with column headers and no extraneous data surrounding the table. Excel tables are the perfect data source for a PivotTable. If you do use a data source that's not in Excel table, make sure the column headers are formatted differently than the data.
That way, Excel recognizes them as headers. To create the PivotTable, you click any cell in the source data list or the Excel table in this case. On the Insert tab, click PivotTable. Verify that Excel has identified the data list you want to use. In this case, it is the table that I named AssetEntries. Click OK and Excel creates a new worksheet that contains a PivotTable. I personally always create a PivotTable on a separate worksheet. That way I don't have to worry about crowding or obscuring the original data.
If I want to see that data, I can just go back to that worksheet. So now I need to add data to the PivotTable. Let's say that I want to add Year and Category to the Row Labels area. So I have 2009, each of my accounts. Then I can add the amount to the Values area. In this case, I can see that in 2009, I had $365,000 worth of current assets and then in 2010, I had over 400,000.
The way the PivotTable's laid out right now, we have the subtotals at the top of the group. I personally prefer them at the bottom of the group. So to change that, I can click any cell in the PivotTable. Go up to Design, click Subtotals, and put the subtotals at the bottom of the group. To me, that's just makes more sense. Also, it gives the PivotTable a little bit more room to breathe, because now instead of having a value here next to 2009, we have some white space. Now that you've taken the first step and learned how to create a PivotTable, you can dive in and manipulate your PivotTable structure and formatting to gain insights into your data.
Get unlimited access to all courses for just $25/month.Become a member
82 Video lessons · 73910 Viewers
80 Video lessons · 129430 Viewers
52 Video lessons · 63771 Viewers
59 Video lessons · 49538 Viewers
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.