Formatting Pivot Table

You are here:
< Back

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.

two level report in pivot table

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.

place both the field in row lbel field to create a nested row pivot table

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).

explaining how to rearrange data values in a pivot table

We could easily rearrange the field data by right clicking the value and select any of the below options as required.

move data value up/down/at the beginning/at end in pivot table

Move UpMove the selected field up one position.
Move DownMove the selected field down position.
Move to BeginningMove the selected field to the beginning of the report.
Move to EndMove 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).

showing how to move a pivot table to a different location

After clicking, specify the new location where you want to move the pivot table.

select these options to move a 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.

apply formats to pivot table

Now select the number format as per your requirement.

apply different types of formatting to a number

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.

formatting of fields in pivot table

Random Posts

  • Sum Numbers Separated By Symbol In Excel

    sum numerical values separated by symbol in excel, excel formula to sum numerical values separated by symbol in excel, excel formula to remove symbol and calculate sum, sum numerical values separated by @ in excel,
    sum numerical values separated by comma in excel

    Share The Knowledge
  • Excel Assign Row Number If Cell Is Not Blank

    automatically assign row number if data is not blank in excel, assign row number to non blank data in excel, assign row number to all non blank values in excel

    Share The Knowledge
  • Strikethrough/Cut Text In Excel

    This post demonstrates how to strikethrough or cut the text in excel.To signify some change in the pre written text […]

    Share The Knowledge
  • Create A Horizontal Scrolling Table In Excel

    Let’s understand how to create a horizontal scrolling table in excel as shown below. The data that is used to […]

    Share The Knowledge