Today's PivotTable will help in analyzing quarterly expenses. It will be based on the Financial_Report worksheet. It's a good idea to convert the source data into an Excel table before creating a PivotTable. The Financial_Report data was made into an Excel table earlier in this course.
To begin, it is necessary to select a cell within the data.
To select a cell within the Financial_Report data,
Click any cell that contains data
To move to the Insert tab, on the Ribbon,
Click the Insert tab
To open the PivotTable dialog box, in the Tables group,
The Create PivotTable dialog box opens:
This dialog box gives several choices when building a PivotTable. Excel has correctly determined that the data to be analyzed is in the table named Report. Users have the choice to place the PivotTable in a new worksheet or an existing worksheet. For today's purposes, don't make any changes to the dialog box.
To accept the defaults in the Create PivotTable dialog box and create the PivotTable,
The new PivotTable opens in a new worksheet. Before moving on, it's a good idea to name the worksheet something meaningful.
To name the worksheet,
Double-click SheetX, type: QuarterlyExpenses Enter key
Take a moment to familiarize yourself with the PivotTable:
There is a blank PivotTable on the left side of the worksheet. On the right is the PivotTable Field List. The fields listed here are the same as the column headers in the Financial_Report data and will be used to build the PivotTable. By placing these fields in different areas (Filters, Columns, Rows, or Values), the focus of the PivotTable can change greatly.
PivotTables pull from the underlying data, but they don't change data. One of the joys of PivotTables is that they can't easily be broken. If the first attempt at a PivotTable doesn't give the desired results, simply move the fields around until the data is showing in the most useful way.
The purpose of today's PivotTable is to analyze quarterly expenses. In the Financial_Report data, expenses are in the Withdrawal column and quarters are in the Quarters column.
To move the Quarter field, in the Field List,
Press & Drag the Quarter field to the Columns area
The quarters appear across the top of the worksheet.
To move the Category field, in the Field List,
Press & Drag the Category field to the Rows area
The categories appear in the left column of the worksheet.
The PivotTable looks like this:
The PivotTable is taking shape with the rows and columns in place. One essential element is missing: values. At this point, there isn't any data to be analyzed. A field must be specified for calculation. Which field is used for calculation will depend on the purpose of the PivotTable. This PivotTable should show the total (sum) of the withdrawals for each category and each quarter.
To move the Withdrawal field, in the Field List,
Press & Drag the Withdrawal field to the Values area
NOTE: The results and following steps are different for Office for Windows and Office for Mac.
Office for Windows: The PivotTable now looks like this:
Excel has done something unexpected. Instead of adding the expenses, Excel has counted the number of individual withdrawals. This can easily be changed.
Office for Mac: The PivotTable now looks like this:
Excel has correctly added the dollar amounts of the withdrawals. While the information in the PivotTable is correct, it should be formatted to show currency.
To open the Value Field Settings dialog box, in the Field List,
Click, Click Value Field Settings...
To change the type of calculation, in the Summarize value field by area,
There is no immediate change to the PivotTable. That won't happen until the dialog box is closed. Before closing the dialog box, take a moment to format the Withdrawals as currency.
To select the cells to be formatted as currency,
Press & Drag cells B5:G15
The formatting tools are on the Home command tab.
To move to the Home command tab,
Click the Home tab
To change the format to Currency,
Click , Click Currency
To deselect the cells,
Click any other cell
To change the formatting, in the Value Field Settings dialog box,
The Format Cells dialog box opens.
To change the formatting to currency,
Click Currency, Click
To close the Value Field Settings dialog box,
Save the workbook.
The withdrawal amounts are now correctly formatted.