Author: Debra

Repeat Pivot Table Headings When Printing

If your pivot table spans two or more printed pages, the page, row and column headings might only print on the first page. You can set an option for the pivot table to make the page, row, and column headings appear on every sheet when you print the pivot table. This will make it easier for readers to understand the report, without flipping back to the first page to see the headings.

Before you turn on this option, clear any entries for row and column titles on the worksheet. If either of these boxes contains an entry, the Set print titles option won’t be applied.

  1. On the Excel Worksheet menu, choose File | Page Setup.
  2. On the Sheet tab, under Print titles, clear the Rows to repeat at top and Columns to repeat at left boxes.
  3. Click OK to close the Page Setup dialog box.

Next, you’ll turn on the Print Titles option. Note that only one pivot table per worksheet can have the Set print titles option selected.    

  1. Right-click a cell in the pivot table, and choose Table Options.
  2. Add a checkmark to Set print titles.
  3. Click OK to close the PivotTable Options dialog box.

PTSetPrintTitles[2]

    ___________________________

    For more information on pivot tables, see the Pivot Table Topics on my Contextures web site.

    ___________________________

Show Pivot Table Subtotals at the Top of a Group

In your pivot table, you might like the subtotals to appear at the top of the row field items.

If the pivot table is in outline (Report) layout, you can format the field to control where its subtotals appear:

  1. Double-click the field button to open the PivotTable Field dialog box.
  2. Click the Layout button.
  3. Select Show items in outline form and add a checkmark to Display subtotals at top of group.
  4. Click OK, then click OK to close the PivotTable Field dialog box.

___________________________

For more information on pivot tables, see the Pivot Table Topics on my Contextures web site.

___________________________

Automatically Include New Data in a Pivot Table

If your pivot table is based on Excel data, you probably add new records to that data, on a regular basis. You want the pivot table to automatically include those new records, without you having to manually change the pivot table range every time you add new data.

A range that expands automatically is called a dynamic range, and here are a couple of ways to create one in Excel.

Create a Named Table

In Excel 2010 and 2007, you can format a list as a Named Table, and use that as a dynamic source for your Pivot Table. There are instructions here: Excel Tables — Creating an Excel Table. This is a quick and easy way to create a dynamic range.

Create a Dynamic Range With a Formula

In Excel 2007, and earlier versions, you can use a formula to create a dynamic range, which will automatically expand to include any new rows and columns. Follow these steps to create a dynamic range:

  1. Select the top-left cell in the source table. This step isn’t necessary, but helps you by inserting the cell reference in the name definition.
  2. Choose Insert | Name | Define.
  3. In the Names in workbook box, type a name for the dynamic range, for example, PivotData.
  4. In the Refers to box, type an OFFSET formula that refers to the selected cell. For example, with cell A1 selected on a worksheet named Pivot, you’d type (all on one line)
    =OFFSET(Pivot!$A$1,0,0,
    COUNTA(Pivot!$A:$A),
    COUNTA(Pivot!$1:$1))
  5. Click the OK button.

Change the Pivot Table Source

Then, change the pivot table’s source to the dynamic range that you created:

  1. Right-click a cell in the pivot table.
  2. On the shortcut menu, click PivotTable Wizard.
  3. Click the Back button.
  4. In the Range box, type the name of the dynamic range, and click Finish.

Note

This technique will not work if there are other items in row 1 or column A of the Pivot worksheet. Those items would be included in the count, and would falsely increase the size of the source range.

______________

Locate Source Data For a Pivot Table

You might have to make changes to a workbook that contains a pivot table. If someone else created that workbook, you may not be sure where to find the Excel table that was used as the pivot table’s source data. In some workbooks there can be several Excel tables, and it’s not immediately obvious which table was used. These tips will help you locate the source data for a pivot table.

Continue reading “Locate Source Data For a Pivot Table”

Deleting the Entire Pivot Table

To remove a pivot table from an Excel workbook, you can delete the worksheet that the pivot table is one.

Occasionally, you might want to remove a pivot table, but you can’t just delete the worksheet because it contains other data. To remove a pivot table, and leave the other items on the sheet untouched, you can clear the cells.

Clear the pivot table cells

1. Select a cell in the pivot table

PivotSelectTable

2. On the menu bar, click Edit|Clear|All.

3. On the PivotTable toolbar, click PivotTable|Select|Entire Table

This will remove the pivot table, and all its formatting, from the worksheet.

Note: After deleting the pivot table, you might temporarily see drop down arrows where the pivot fields were located, but these should disappear when you activate another cell.

___________________________

For more information on pivot tables, see the Pivot Table Topics on my Contextures web site.

___________________________

Changing Blank Row Labels

In your Excel pivot table, you might have a few row labels or column labels that contain the text “(blank)”.

This happens when data is missing from the source data. For example, in the source data, you might have a few sales orders that don’t have a Store number entered.

Labels Show (Blank)

In the pivot table shown below, there is one store number cell, in column A, that shows (blank).

Instead of that text, you want blank cells in the Row Labels area and Column Labels area to contain the text “N/A.”

In the PivotTable Options dialog box, on the Layout & Format tab, you entered N/A as the text to display in empty cells, and then you clicked the OK button.

However, the empty cells still appear as (blank) in the Row and Column Labels areas.

Does NOT Change Labels

In the PivotTable Options dialog box, the setting for empty cells:

  • affects cells in the Values area
  • does NOT affect the Row or Column Labels areas.

In the screen shot above, you can see that missing data in the Values area has been replace by N/A, but the row labels and column labels haven’t changed.

Manually Change Blank Labels

You can manually change the (blank) labels in the Row or Column Labels areas by typing over them in the pivot table.

You can type any text to replace the (Blank) entry, but you CANNOT clear the cell and leave it empty.

How to Change Label Text

Here are the steps for manually changing a pivot table row label text or labels in the column areas. I used “N/A” in this example, but you could use a different text string, or a space character.

To change a blank label cell to “N/A”, follow these editing steps:

  1. First, select one of the Row or Column Labels that contains the text (blank).
    • Even if there are multiple cells with a “(blank)” label, you only need to select one of them.
    • You DO NOT need to press Ctrl and select all of them
  2. Next, on your keyboard, type N/A in the cell, and then press the Enter key.

Note: All other (Blank) items in that same pivot field will change to display the same text, N/A in this example.

___________________________

More Pivot Table Tips

For more information on pivot tables, see these pages on my Contextures site:

Manually Move Pivot Items

Clear Old Items in Pivot Table

Pivot Table Options

_________________

Refreshing When a File Opens

In Excel 2003, if your pivot table source data changes frequently, you might want to ensure that the pivot table is updated as soon as the file opens.

You can set a pivot table option to refresh the pivot table automatically:

  1. Right-click a cell in the pivot table, and choose Table Options.
  2. Under Data options, add a checkmark to Refresh on open.
  3. Click OK to close the PivotTable Options dialog box.

Tip: To stop a long refresh, as the file opens press the Esc key.

___________________________

For more information on pivot tables, see the Pivot Table Topics on my Contextures web site.

Totaling Hours in a Pivot Table Time Field

In your source data, you might record the amount of time that employees have spent working on projects. For example, Jim worked on Project A for 8 hours Monday, 8 hours Tuesday, 6 hours Wednesday and 3 hours Thursday, for a total of 25 hours.

In the pivot table, you want the total time per project, and Project A should show a total of 25 hours.

However, your pivot table shows 1:00 as the total, instead of 25:00.

This happens because the results are shown as time rather than total time. The first 24 hours are counted as one day, and the remaining hour is 1:00 AM of the second day. The 1:00 in the Project A Total represents the 1:00 AM time.

To see the total time, format the cells that contain total times with the custom number format [h]:mm, and they’ll total correctly.

To apply the custom format

  1. In the pivot table, right-click on the Sum of Hours field button
  2. Click on Field Settings
  3. Click the Number button
  4. In the Category list, click on Custom
  5. In the Type box, enter: [h]:mm
  6. Click OK, twice, to close the dialog boxes.
  7. The pivot table will now show the total time for hours worked on each project.

___________________________

For more information on pivot tables, see the PivotTable Topics on my Contextures web site.

Add Running Totals to an Excel Pivot Table

With a running total in a pivot table, you can see how amounts accumulate over a period of time, or through a range of products, as shown in the short video below.

In the video, the pivot table contains monthly sales figures for several products, and we’ll create a running total by month. In the pivot table, Date is in the row area, grouped by month. Product is in the column area, and Units sold, shown as Sum of Units, is in the data area.

This is what the pivot table looks like before we add the Running Totals custom calculation.

Follow these steps to add a Running Total:

  1. Right-click on one of the data cells, and click on Field Settings.
  2. Click the Options button.
  3. From the dropdown list for Show data as, select Running Total In.
  4. For the Base field, select Date, then click the OK button.

Note: If you select a base field that isn’t in the row or column area, all the results will show an #N/A error.

For more examples of Running Totals, please visit Pivot Tables — Running Totals

___________________________

For more information on pivot tables, see the Pivot Table Topics on my Contextures web site.

Remove Old Items in Excel 2007 Pivot Table

To prevent old items from being retained in an Excel 2007 pivot table, you can change an option setting, as shown in the short video below:

  1. Right-click a cell in the pivot table
  2. Click on PivotTable options
  3. Click on the Data tab
  4. In the Retain Items section, select None from the drop down list.
  5. Click OK, then refresh the pivot table.

Details at www.contextures.com/xlPivot04.html

___________________________

For more information on pivot tables, see the Pivot Table Topics on my Contextures web site.