Debra – Page 36 – Excel Pivot Tables

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.

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:

Right-click a cell in the pivot table
Click on PivotTable options
Click on the Data tab
In the Retain Items section, select None from the drop down list.
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.

Use the Chart Wizard to Create a Pivot Chart

In Excel 2003, if you select a cell in the pivot table and click the Chart Wizard button on the Standard toolbar, a default pivot chart on a chart sheet is created. In some cases you might prefer to step through the Chart Wizard so you can set the options as you create the pivot chart.

Create a Pivot Chart with the Chart Wizard

Select an empty cell away from the pivot table and any other data on the worksheet.
On the Standard toolbar, click the Chart Wizard button.
In Step 1 of the Chart Wizard, select a Chart type and Chart sub-type, then click Next.
In Step 2 of the Chart Wizard, click in the Data range box, then select any cell in the pivot table. The entire pivot table will be automatically selected, and its address will appear in the Data range box.
Continue through the Chart Wizard steps, selecting the options you want, then click Finish to create the pivot chart.

Note: After selecting a pivot table as the data range, you can’t return to Step 2 of the Chart Wizard. To use a different data range, create a new chart.

___________________________

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

Selecting Sections of an Excel Pivot Table

When working with a pivot table, you might want to select a specific section, such as the subtotals, or a set of labels. In this short video I use the Selection Arrow to select specific sections of an Excel Pivot Table.

I also show you how to turn on the Enable Selection feature, if you can’t see the Selection Arrow when working with pivot tables on your computer.

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