Excel pivot table: YOUR OBJECTIVE
You have a lengthy Excel table containing months of data for orders placed with your company. To better understand the data and analyze it from various perspectives, you created a pivot table. But now you would like to make some changes to it. For one thing, you want to change the pivot-table layout, but you also want to rename, rearrange, format and/or delete certain fields.
This learning module shows you how to modify your excel pivot table‘s layout and how to edit and delete its various fields.
Excel pivot table: HOW DOES IT WORK?
Making changes to a pivot table’s layout and formatting is quite easy. In fact, one formats a pivot table just as one formats a conventional Excel table. Changing a pivot table’s overall layout and how the various fields are used can also be done with similarly little effort. The kinds of changes you can make include:
- Editing field labels in the excel Pivot Table
- Formatting various excel Pivot Table elements
- Reordering columns and rows in the excel Pivot Table
- Repositioning fields in the excel Pivot Table
- Removing fields from the excel Pivot Table
Excel pivot table: Editing field labels in the PivotTable
When generating a pivot table, Excel does not just use names from the original data table to label its various fields. It also uses generic names to describe certain fields in the excel pivot table. Terms such as Column Labels and Row Labels don’t provide much help in interpreting the displayed data, however. For that reason, many Excel users rename these fields.
You can easily change the automatically generated terms or other labels in your excel pivot table just by clicking on a cell where such a term or label appears and then replacing it with the original field name or any other term meaningful to you. To make it easier to understand the data presented in your pivot table, it is recommended that you rename fields, as you see fit.
Below, you see a pivot table. While the row and column labels were drawn from your original table, other labels – in particular, those outlined in red – were automatically given default names. In the table below that, we edited the generic labels to make them more meaningful:
Excel pivot table: Formatting various pivot table elements
When you first create a pivot table, it is completely unformatted: column widths are established by default; monetary amounts are displayed without dollar signs, etc. To improve the pivot-table’s appearance and readability, you can apply the formatting functions with which you are already familiar. For instance, you can apply the dollar sign to numbers by clicking the Accounting Number Format button in the Number group, on the Hometab. Or, if you click the Format button in the Cellsgroup, on the Home tab, you can change the column width by choosing the Column Width option.
Below, you see a pivot table before and after it was formatted:
Excel pivot table: Reordering a pivot table’s columns and rows
The order in which columns or rows appear in a pivot table can be easily changed. First select a column or row label in the pivot table itself. Then open its context menu by right-clicking it with your mouse. Near the middle of the command list that appears, you will see the Move command with which you can specify a new position for the column or row. After clicking Move, you can select one of the submenu options: Move to Beginning, Move Left, Move Right or Move to End. As soon as you click one of these options, the column or row label will be moved to its new position.
Excel pivot table: Repositioning fields in a pivot table
Using an excel pivot table, you can reveal much information that is inherent in your source data but that because of the sheer volume of data may not be easy to see. To make your pivot table reveal the hidden meaning of your data, i.e., to view your data from various points of view, you can change your source data’s fields in the pivot table’s Report Filter, Column Labels or Row Labels areas. You can move a field from a Column label to a Row label or from a Report filter to a Column label, and so on…
To do this, simply click on any one of the fields in one in the report areas (in the excel Pivot Table Field List), and then, while holding the mouse button depressed, drag the entry from its current place into another of the report areas. When you release the mouse button, the pivot table will be immediately recalculated.
The following picture illustrates the point::
The first pivot table (below) summarizes the Order Value, with Customers defining the rows and Customer Groups defining the columns. The Report Filter is defined by the Sales Area.
In the second pivot table, the Customer Group field was dragged from the Column Labels area into the Report Filter area, while the Sales Area field was dragged from the Report Filter area into the Column Labels area. Although the total Order Value (rightmost) column has not changed at all, this view of the data now tells a new story, clearly showing how sales from your company’s sales territories are distributed.
Alternatively, you can make the same sort of changes to the pivot table layout without dragging field labels into other positions (as described above). You can move a field by clicking directly on its corresponding field label in one of the four report areas in the excel Pivot Table Field List. Having clicked in it, a command list will appear. In the middle of that list, you will see Move-to options, which you can use to send the field to another pivot-table area. The two methods for moving fields from one area to another are equivalent; there is no reason to prefer one to another.
Excel pivot table: Removing fields from a PivotTable
You can also remove field names from a pivot table layout. Doing so causes the corresponding data to be removed from the pivot table, triggering an immediate pivot-table recalculation.
To remove a field from the excel pivot table, click on the relevant field label, and then, while holding the mouse button depressed, drag it away from the task pane. When you release the mouse button, it will have disappeared from the area where you grabbed it. Alternatively, you can simply uncheck the field in the upper section of the task pane, which has the same effect.
In the illustration below, we removed the Customer Group field from the Report Filter area.