Excel Pivot Tables – Page 36 – by contextures.com

Counting Blank Cells in Source Data

If there are blank cells in one of the fields in your source data, you might want to show a count of the blank cells in the pivot table.

In this example there’s a Region field in the source data, and some of the records have no region name entered. In the pivot table, you’d like to see a count of how many records are missing a region name.

NOTE: For an updated version of this article, go to Count Blank Entries in Pivot Table

Incorrect Count

To find the count, you might add the Region field to the pivot table’s row area, and put another copy of the Region field in the data area, as Count of Region.This seems logical, but there’s no count showing for the blank regions.

Video: Count Blank Entries in Pivot Table

[Update] This 3-minute video shows the steps for correctly counting blank items from the source data for a pivot table.

Get the sample Excel file, and written steps, on the Count Blank Cells in Pivot Table page on my Contextures site.

Get Correct Count

A pivot table can’t count the blank cells when you add a field to the data area and use the Count or CountNum summary function. That’s why no count appears beside the (blank) region in the screen shot above.

Instead, put a different field in the data area, and use it for the count.

For example, if the Units field will always contain data, add Count of Units to the data area, as shown below.

The count of blank Regions is now calculated, and you can see that three records are missing a region name.

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.

On the Excel Worksheet menu, choose File | Page Setup.
On the Sheet tab, under Print titles, clear the Rows to repeat at top and Columns to repeat at left boxes.
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.

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

___________________________

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:

Double-click the field button to open the PivotTable Field dialog box.
Click the Layout button.
Select Show items in outline form and add a checkmark to Display subtotals at top of group.
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:

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.
Choose Insert | Name | Define.
In the Names in workbook box, type a name for the dynamic range, for example, PivotData.
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))
Click the OK button.

Change the Pivot Table Source

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

Right-click a cell in the pivot table.
On the shortcut menu, click PivotTable Wizard.
Click the Back button.
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

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:

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
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:

Right-click a cell in the pivot table, and choose Table Options.
Under Data options, add a checkmark to Refresh on open.
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

In the pivot table, right-click on the Sum of Hours field button
Click on Field Settings
Click the Number button
In the Category list, click on Custom
In the Type box, enter: [h]:mm
Click OK, twice, to close the dialog boxes.
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:

Right-click on one of the data cells, and click on Field Settings.
Click the Options button.
From the dropdown list for Show data as, select Running Total In.
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.