Change Field Names in Pivot Table Source Data

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.

Pivot Table Field Name 01

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.

Pivot Table Field Name 02

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.

Pivot Table Field Name 03

_____________________

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

_________________

PowerPivot For Excel 2010

Microsoft PowerPivot for Excel 2010 is a data analysis add-in for Excel 2010, and is part of the Office 2010 Beta. You can test PowerPivot in the hands-on Virtual Lab, or in the Office 2010 Beta, if you’ve downloaded that.

That’s where I tested PowerPivot last weekend, and described the experience in my Contextures blog article, Drill Into Data With PowerPivot.

There are step-by-step instructions in the module, and you can experiment a bit on your own too. For example, I tried a few of the Slicer  formatting features.

PPivotLab_02

You can connect each slicer to one or more of the pivot tables, and filter all the connected pivot tables at the same time.

PPivotLab_01

_______________

___________________

Starting Each Pivot Item on a New Page

Normally, a long pivot table prints to the bottom of a page, then continues on the next page. The page break occurs naturally, not at a specific position in the pivot table.

You can control the page breaks, by changing the pivot table settings.

The Sample Layout

In this example, the pivot table has two fields in the Row Labels area – Store and Product Category.

pivotitempage01

We’d like to print this pivot table, with each store’s data starting on a new page. That way, we’d be able to print the entire
report, and send each store its own section.

Change the Field Setting

We’ll change a setting in the Store field, so each pivot item starts on a new page in the printed report.

  1. Right-click one of the Store labels in the pivot table, and then click Field Settings.
  2. In the Field Settings dialog box, on the Layout & Print tab, add a check mark to Insert Page Break After Each Item.
  3. Click OK, to close the dialog box.

PivotItemPage02

Things to Consider

The Insert Page Break After Each Item setting doesn’t force all items for a field fit on one page. It only creates a page break so the next item will start on a new page. If a field has many items, it might print on two or more pages.

Also, with this setting, your printed report could use an excessive amount of paper. You might prefer to manually adjust the page breaks in Page Break Preview.

To Go To Page Break Preview

  1. On the Ribbon’s View tab, in the Workbook Views group, click Page Break Preview.

    RibbonPageBreak

  2. Dashed lines indicate an automatic page break and solid lines are manual page breaks.
  3. To move a page break, point to it, and drag up or down on the page.

To return to Normal view

  1. On the Ribbon’s View tab, in the Workbook Views group, click Page Break Preview.

_____________

Pivot Table Defaults to Sum or Count

When you add a numeric field to the Values area of a pivot table, it usually appears as a Sum. For example, in the pivot table shown below, the Units field became Sum of Units.

ValueSum

Occasionally, when you add a numeric field to the Values area, it shows the Count, instead of the Sum. In the same pivot table, when the TotalSales field is added, it shows a Count.

ValueCount

Pivot Table Default Function

If you add a number field to the Values area, the default summary function is Sum. However, if the source data has blank cells, or nonnumeric data, such as text or errors, in that field, the Count function is the default summary function.

You can’t change the default settings for the data fields, but you can manually change the summary function after you add the fields to the Values area.

To manually change the summary function:

  1. Right-click a cell in the field you want to change, and click Summarize Data By.
  2. Click the summary function that you want to use.

SummarizeDataBy

Pivot Table Add-in

There’s a feature that changes all the data fields to SUM, or any other summary function, in my pivot table add-in. It add a new tab to the Ribbon, with time-saving commands that you can use.

  1. After you install the add-in, select any cell in the pivot table.
  2. Then, on the Ribbon’s Pivot Power tab, click SUM ALL.
  3. Or, click Change ALL to, then click the Summary function that you want to use.

sumselectedvalues

______________

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

_________________

New Pivot Items Out of Order

If you add new products to your pivot table source data, and refresh the pivot table, the new products will appear in the drop down lists. Sometimes though, the new items appear at the end of the list, instead of in alphabetical order. This problem can occur if you have manually rearranged the items in the Row Labels area.

For example, binders were just added to this pivot table’s source data. When the pivot table was refreshed, Binders appeared at the bottom of the Product list, instead of the top.

ListManualSort

Because it’s not in alphabetical order, it’s difficult to find the new product in the list. You’d like the product list sorted alphabetically.

Sort the List

If a field is set for Manual sort, new items will appear at the end of the drop-down list. Follow these steps to sort the field in ascending order:

  1. Right-click a cell in the Product field. For example, right-click the Envelopes cell.
  2. Click Sort, and then click Sort A to Z.

SortAZ

When you sort the Product field, its sort setting changes from Manual to Sort Ascending or Sort Descending. This also sorts the drop-down list, and makes it easier for users to find the items they need.

______________

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

Re-create Pivot Table Source Data Table

If you accidentally delete the Excel 2003 worksheet that has the source data for your pivot table, you may be able to use the pivot table’s Drill to Details feature to re-create it.

Extract the Pivot Table Data

To re-create the source data in Excel 2003, follow these steps to use the Drill to Details feature:

  1. Make sure that none of the items in the pivot table fields are hidden. For page fields, (All) should be selected. For row and column fields, (Show All) should be checked. Note: You don’t need to include all the fields in the pivot table before using the Drill to Details feature.
  2. Show the grand totals for rows and columns. If they aren’t visible, right-click a cell in the pivot table, and click Table Options. Check the options Grand totals for rows and Grand totals for columns, then click OK.
  3. Double-click the grand total cell at the bottom right of the pivot table. This should create a new sheet with the related records from the original source data.

Fix the Extracted Data

If the original source data contained formulas, you will have to re-create them, because the Drill to Details feature exports the data only.

The columns in the extracted data will be in the same order as they were in the original source data.

The extracted data will be formatted with Table AutoFormat List 3. You can apply a different AutoFormat, or apply your own formatting.

Note: If you had made changes to the source data and not updated the pivot table, those changes won’t be in the extracted data.

Connect to the Extracted Data

If you rename the sheet that was created during the Drill to Details process, and use the same name as the worksheet that originally held the source data, the pivot table might automatically connect to the new source data table.

If not, you can connect to the re-created source data:

  1. Right-click a cell in the pivot table, and choose PivotTable Wizard.
  2. Click the Back button, and select the new source data table range.
  3. Click Finish.

 

Show Text in a Pivot Table Values Area

A common question is “How can I show text in a pivot table’s values area, instead of numbers?”

For example, if you add the Region field to the Values area, you’d like to see the region’s name, instead of a Count Of Region number.

PivotTextVal01

If you use Crosstab queries in Microsoft Access, you might have achieved this by using the First or Last summary function, instead of Sum or Count.

Unfortunately, the First and Last functions aren’t available in Excel pivot tables, so there’s no easy way to show text in the Values area.

Workaround #1 – Use the Row Fields

You could add the Region field to the Row Labels area, with the City field. Then add another field in the Values area to show a count of the regions. The layout won’t be exactly what you wanted, but it will show the region name.

PivotTextVal02

Workaround #2 – Create a Custom Number Format

In this example, there are only two region names, so you could use a custom number format to show the region names. You’ll assign a number to each region, then use that number in the pivot table. Note: This technique is limited to 2 items.

Create the Region Number field:

  1. In the source data, add a new column with the heading RegNum. In this column, type a 1 for East region orders and 2 for North region orders.
  2. Refresh the pivot table, so the RegNum appears in the Field List.
  3. Add the RegNum field to the Values area, and right-click on one of the numbers.
  4. In the popup menu, click summarize by Max.

PivotTextVal03

Apply a Custom Number Format:

  1. Right-click a Region value in the Values area in the pivot table.
  2. In the popup menu, click Number Format.
  3. In the Category list, click Custom.
  4. In the Type box, enter [=1]”East”;[=2]”North”;General
  5. Click OK, to close the dialog box.

PivotTextVal04

The pivot table will show the Region names, instead of the Region numbers.

PivotTextVal05

____________

Centre Column Headings in Excel Pivot Table

If your pivot table has column labels, and more than one field in the Values area, you might want to centre the column labels.

For example, in the pivot table shown below, there are Region headings in the Column Labels area. Below each Region label, there are headings for the Qty Sold and $ Sales fields.

LabelColCtr01

The East label is at the far left of its Qty Sold heading. It’s hard to tell which Region headings go with which Value headings.

To make the pivot table easier to read, you can centre the Region labels over their Value field headings.

To centre the column headings in Excel 2007:

  1. Select a cell in the pivot table
  2. On the Ribbon, under the PivotTable Tools tab, click Options
  3. At the far left, in the PivotTable group, click Options

RibbonPTOptions

  1. On the Layout & Format tab, in the Layout section, add a check mark to Merge and Center Cells With Labels

LabelColCtr02

  1. Click OK

Each Region column label is now centred over its Value field headings.

LabelColCtr03

 

Hide Page Items in Excel 2003 Pivot Table

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.

PageHide01

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.

PageHide02

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.

PageHide03

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.

_________________

Pivot Table Error: Excel Field Names Not Valid

Usually, things go smoothly when you when you try to create a pivot table. However, occasionally you might see a pivot table error, Excel Field Names not Valid, if you try to build a new pivot table, or refresh an existing pivot table.

Updated Jan. 1, 2019 – macro to help with troubleshooting the pivot table error

Continue reading “Pivot Table Error: Excel Field Names Not Valid”