Watching:

Summarize Budget Data Using Excel's PivotTables


show more Summarizing budget data by creating a PivotTable provides you with in-depth training on Business. Taught by Curt Frye as part of the Excel 2007: Creating Business Budgets show less
please wait ...

Summarizing budget data by creating a PivotTable

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.

Summarizing budget data by creating a PivotTable
Video duration: 2m 16s 1h 3m Intermediate

Viewers:

Summarizing budget data by creating a PivotTable provides you with in-depth training on Business. Taught by Curt Frye as part of the Excel 2007: Creating Business Budgets

Subject:
Business
Software:
Excel
Author:
please wait ...