Quick Trick – Add or Move Excel Pivot Fields

Here’s a quick trick to add or move Excel pivot table fields, just by typing.

Add or Move Pivot Table Fields

Instead of dragging a pivot field into the layout, you can type its name over an existing pivot field label.

  • Tip: This is a great shortcut if you like to keep your hands on the keyboard while working in Excel!

Here are the steps to add or move pivot table fields on the worksheet:

  • First, change the pivot table to Outline layout or Tabular layout. This trick will not work in Compact layout.
  • Next, click on a cell that contains a pivot field name – a cell where you want a different field to appear
  • In that cell, type the name of the pivot field that you want to add
    • Be careful to type the field name correctly
    • If you make a typo, the field won’t be added
  • Finally, press Enter,  to complete the pivot table  layout change
type over pivot field label on worksheet
type over pivot field label on worksheet

Pivot Field Added

After you press Enter, the pivot table layout changes.

  • The field that you typed moves into the active cell.
  • The existing fields shift down, and the added field takes its new position.
pivot field added to worksheet layout
pivot field added to worksheet layout

More Tips for Moving Labels

The first video above shows how to move pivot fields.

You can use a similar trick to move the pivot items in a pivot table.

The short  video below shows how to move the Excel pivot items, and you can find written steps on the Move Pivot Table Labels page on my Contextures site.

Get the Sample File

To try the  quick trick to add pivot fields, you can use your own pivot table, or download the Move Pivot Labels sample file from my Contextures website.

The zipped Excel file is in xlsx format, and does not contain any macros.

_________________

Quick Trick – Add or Move Excel Pivot Fields

Quick Trick - Add or Move Excel Pivot Fields
Quick Trick – Add or Move Excel Pivot Fields

____________________

Show Percent of Group Total – Excel Pivot Table

In an Excel pivot table, you can use Excel’s built-in custom calculations, for a different view of the data.

For example, in the video below, I set up a pivot table to show what % of a company’s  monthly sales were Binders. Also, what %  of Binder sales was for each colour – red, blue, and black.

Note: You can get the sample Excel file that I used for this video on the Pivot Table Show Values As page, on my Contextures site.

Binder Sales – % By Colour

In the screen shot below, the pivot table has 4 fields in the worksheet layout:

  • Item and Colour in the Row area
  • Month in the Column area
  • Sum of Units sold, in the Values area
pivot table with monthly sales per product colour
pivot table with monthly sales per product colour

Following the steps listed in the section below, I used pivot table custom calculations, to show additional sales details:

  • the % for each colour‘s sales – Black, Blue and Red
  • compared to the item‘s total sales
  • in each month – Jan and Feb

Show % of Item Sales

In the pivot table screen shot below, I’ve added a 2nd copy of the Units field to the values area.

  • Tip: To add a 2nd copy of a pivot field, drag it from the PivotTable Field list, onto the Values area in the field list

For the 2nd copy of the Units field, I followed the steps below, to change that field’s calculation settings

  • Right-click one of the Units value cells
  • In the pop-up menu, click Show Values As
  • In the next pop-up menu, click % of Parent Row Total

In the pivot table, the second Units field changed, to show:

  • the % for each colour‘s sales
  • compared to the item‘s total
  • in each month.

For example,

  • 195 Binders were sold in January
  • 51% (100 units) of those January Binders were Black colour
Show Percent of Subtotal in Excel Pivot Table
Show Percent of Subtotal in Excel Pivot Table

More Pivot Table Info

For more Pivot Table Subtotal tips, videos and examples, visit the following links:

Show Percent of Subtotal in Pivot Table

Pivot Table Show Values As % of Parent Total

Show Percent Of Subtotal In Pivot Table

______________________

Show All Pivot Table Items To Compare Sales Easily-No Data

By default, the Pivot Table shows only the items for which there is data. In the example shown in this video, not all colours were sold to each customer. You may wish to see all the items for each customer, even those items with no data.

Continue reading “Show All Pivot Table Items To Compare Sales Easily-No Data”

Show Excel Pivot Table Subtotals Top or Bottom

When you create a new pivot table, and add multiple fields to the row or column area, subtotals are automatically created. For Row fields the automatic subtotals usually appear at the top, and you can move them to the bottom, if that’s your preference. There are a few limitations though, which you can see in the video and the written notes below.

Continue reading “Show Excel Pivot Table Subtotals Top or Bottom”

Copy PivotTable Format to Different Excel File

You can create custom PivotTable Styles in an Excel workbook, to fine tune the appearance of your pivot tables. Later, if you want to use those custom styles in a different Excel file, you’ll have to manually copy them, using the steps shown below.

Continue reading “Copy PivotTable Format to Different Excel File”

Pivot Table Calculated Field Counting Problem

A pivot table calculated field always uses the SUM of other values, even if those values are displayed with another summary function, such as COUNT. That can cause results that look wrong, but the video below shows a workaround to fix the problem.

Continue reading “Pivot Table Calculated Field Counting Problem”