Category: Layout

When you add fields to the row labels area in a pivot table, the field’s items are automatically sorted. See how you can manually move those labels, to put them in a different order. There’s a video and written steps below.

Continue reading “Move Row Labels in Pivot Table”

A pivot table shows a summary of the source data, and in the screen shot below you can see all the products that were sold, and the quantities for each colour.

When you filter the pivot table, some of that data might disappear. For example, in the pivot table shown below, the Customer field has been filtered to show XYZ Inc. orders.

They didn’t order all the colours for each product, so the pivot table is smaller. It’s only showing the products and colours that XYZ Inc. ordered.

Instead of having the pivot table change size, so it shows only the applicable items, you can change a field setting, to keep it consistent.

Show All Items in Excel 2010

To make all the items appear, even if the pivot table is filtered, you can change a Layout setting in the pivot table. This setting applies to a single field, so you’ll have to make the following change to each field in which you want to see all the items.

To show all items for a pivot field in Excel 2010 or 2007:

1. Right-click an item in the pivot table field, and click Field Settings
2. In the Field Settings dialog box, click the Layout & Print tab.
3. Check the ‘Show items with no data’ check box.
4. Click OK

After the setting is changed, all the colours are listed for each product, even if they weren’t sold for the selected customer.

To see the instructions for changing the setting in Excel 2003, please go to my Contextures website: Pivot Table Field Settings

Watch the Video

Watch this video to see the steps for changing the layout setting in Excel 2010 or 2007.

_____________________

When you have a long list of fields in an Excel 2003 pivot table, it can take a long time to drag them into the pivot table layout, using the PivotTable Wizard.

For a quicker way to add the fields, don’t go into the Layout screen in Step 3 — just click the Finish button.

Add Fields from the PivotTable Field List

When the PivotTable Wizard closes, you’ll see a blank pivot table on the worksheet.

Note: If the PivotTable Field List isn’t visible, click the Show Field List button on the PivotTable Toolbar.

To add the pivot fields:

1. Then, in the Field List, select one of the layout areas from the drop down list.
   • 
2. In the Field List, double-click on each field that you want to add to the selected layout area.
   • 

Repeat Steps 1 and 2, for all the layout areas, leaving the Data Area to fill in last.

____________

In the PivotTable Field List, the fields usually appear in the same order that they appear in the source data. If the pivot table source data has lots of fields, it might be hard to find a specific field in the list.

To make it easier to find the fields in the long list, you would like the field list in alphabetical order.

Sort the List

You can change a pivot table option, to make the PivotTable Field List show the fields in alphabetical order.

1. Right-click a cell in the pivot table, and in the pop-up menu, click PivotTable Options.
2. In the PivotTable Options dialog box, click the Display tab.
3. At the bottom of the Display tab, in the Field List section, select Sort A to Z, and then click OK

Later, if you want to return the field list to its original order, select Sort in Data Source Order, instead of Sort A to Z.

_______________

Occasionally, you might need to change a column heading in the pivot table’s source data. For example, you could change a column heading from Qty to Quantity, to make it easier to understand.

However, when you refresh the pivot table after making the changes, the renamed field will disappear from the pivot table layout. In our example, the Qty field is removed from the layout. It isn’t automatically replaced by the Quantity field, even though it’s in the same column heading cell in the source data.

If you change the column headings, you’ll have to add the revised field to the pivot table layout again. If you had number formatting in the old pivot field, you’ll have to reapply the number formatting you had previously applied. 

Change Captions Instead

If possible, instead of changing the column headings in the source data, create custom names for the fields in the pivot table instead.

For example, to create a custom name for the Qty field:

1. In the pivot table, click on the cell that contains the pivot field name, Qty.
2. Type the custom name, Quantity.
3. Press the Enter key, to complete the renaming.

_____________________

For more information on Pivot Tables, please see the Pivot Table Tutorials on the Contextures Website.

_________________

In Excel 2003, and earlier versions, the items in a page field’s dropdown list don’t have check boxes to indicate which items to show and which to hide, like there are in the Report Filters for Excel 2007 and later.

In the pivot table shown below, the Product field is in the page area.

It’s easy to select one product, such as Chocolate Chip. Or, you can select (All), and see the data for all the products.

However, you might want to hide some of the products instead of viewing the data for all the items. Without the check boxes, it’s not obvious how you can hide them.

Hide Some of the Page Items

Follow these steps to hide one or more of the page field items:

1. Double-click the page field button to open the PivotTable Field dialog box.
2. In the Hide items list, click on the items that you want to hide. In the screen shot below, Banana and Cheese will be hidden.

3.    Click OK to close the PivotTable Field dialog box.
4.    Until you unhide the items, the page field will show (Multiple Items) instead of (All), unless an individual item is selected.

Tip: You could temporarily move the page field to the row area, remove checkmarks for the items you want to hide, and move the field back to the page area.

_________________