Go through Introduction To Pivot Table if not already, since we will use data and pivot table explained in that section.
Two level pivot table Report (Nested Row Report)
Suppose We want to see a two level pivot table report where city wise revenue is displayed for each of the product category as shown below.
To achieve the above shown pivot table layout, place category fist in the row label field and then place City in the row label field as shown below.
Value Field will have Amount field.
Rearrange/Sort data values in a Pivot Table
By default, excel pivot table displays data in a field alphabetically as shown below in the figure.
You could see the country name field is arranged alphabetically (sorted A to Z).
We could easily rearrange the field data by right clicking the value and select any of the below options as required.
| Option | Purpose |
|---|---|
| Move Up | Move the selected field up one position. |
| Move Down | Move the selected field down position. |
| Move to Beginning | Move the selected field to the beginning of the report. |
| Move to End | Move the field to the end of the report. |
Move/Displace a Pivot Table
What if we want to change the place of a pivot table from an existing location to a new location in the same sheet or to a different sheet.
To move or displace a pivot table, select the pivot table and click on Move Pivot Table option in the options tab (Options tab gets activated when the pivot table is selected).
After clicking, specify the new location where you want to move the pivot table.
Select the radio button to specify the New or Existing sheet and select the cell where you want to move the pivot table.
In the example above I want to move the pivot table to cell J4 as shown in the figure.
Apply Number Formatting in Pivot Table
Select the value in the pivot table and then Right click ->Number Format as shown below.
Now select the number format as per your requirement.
In the above example, I have selected currency format since it’s a money field.
You could select any of the number formats that are listed on the left side of the Format Cells menu.
The pivot table formatting has changed to currency as shown below.
