Join Curt Frye for an in-depth discussion in this video Summarizing budget data by creating a PivotTable, part of Excel 2007: Creating Business Budgets.
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.
- Tracking income and expenses by category and contract
- Using balance sheets
- Designing worksheets to assist decision making
- Creating income statements
- Calculating loans payments and interest
- Creating cell references to other worksheets
- Summarizing data in a chart
- Building alternative budget scenarios