Author: Debra

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

  1. Select an empty cell away from the pivot table and any other data on the worksheet.
  2. On the Standard toolbar, click the Chart Wizard button.
  3. In Step 1 of the Chart Wizard, select a Chart type and Chart sub-type, then click Next.
  4. 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.
  5. 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.

Turn Off GetPivotData Formulas for PivotTables

In Excel 2002, and later versions, when you type an equal sign, then click on a data (number) cell in a pivot table, you may see a GETPIVOTDATA formula, instead of a cell reference.

GetPivotData formula

In this short video, learn how to turn this feature off.

For written instructions, please see Turn Off GetPivotData Formulas for Excel Pivot Tables.

___________________________

 

Refreshing a Pivot Table on a Protected Sheet

In Excel 2003, if you protect a worksheet, you’ll see a list of options, with specific items that can be allowed.

You can add a checkmark to “Use PivotTable reports”, so that people will be able to use the existing pivot tables on the worksheet.

protect worksheet settings

However, once the sheet is protected, you can’t create a new pivot table. Also, you won’t be able to refresh a pivot table on the worksheet, because that feature is disabled on a protected sheet. On the PivotTable toolbar, you can see that the Refresh button, and other features, are disabled.

pivot table toolbar with buttons disabled

Refresh the Pivot Table on a Protected Sheet

To refresh the pivot table on a protected worksheet,

  • temporarily unprotect the worksheet,
  • refresh the pivot table,
  • protect the sheet.

You can do this manually, or record a macro, then run that macro to refresh the pivot table.

More Protection Tips

There are more pivot table protection tips on my Contextures website.

___________________________

 

Default Functions in a Pivot Table

Usually, when you add a numeric field to the Values area in a pivot table, it automatically uses the Sum function. Sometimes though, a field automatically uses the Count function.

Why does Excel use different functions are used for two fields that contain similar data?

  • If there are blank cells, or non-numeric data in the field, the Count function is used as a default.
  • For fields that contain all numeric data, the Sum function is the default.

Change the Function

Unfortunately you can’t set a default summary function in a pivot table. However, after the field has been added to the Values area, you can change its summary function:

  1. Right-click a cell in the field that you want to change, and click Summarize Values By.
  2. In the Summarize by list, select one of the functions.

Summarize Values By

Use Programming to Change Data to SUM

If you have too many fields to change manually, you can use a macro to change the summary function automatically. There is sample code here.

Use a Pivot Table Add-In

For an even easier solution, you can use my free pivot table add-in, that already has the summary function code in it. Just download and install the add-in, and run it when you want to change several fields, all at once.

__________

 

Error Message When Grouping Dates

In Excel 2007, you added a date field to the pivot table’s Row area, and you want to group the dates by year. However, you get an error message, “Cannot group that selection”, when you try to group the dates.

error - cannot group that selection

This error occurs if there are cells in the source data that contain text, instead of dates. For example, you may have entered text such as N/A, if a date was not available when the data was being entered.

Dates entered as text

How to Fix the Problem

To correct the problem, do the following:

  1. In rows contain text, such as N/A, delete the text, and leave the cell blank, or replace the text with a valid date.
  2. Refresh the pivot table, and try to group the dates.
  3. If you’re still unable to group the dates, remove the date field from the pivot table layout, refresh the pivot table, then add the date field to the pivot table again.

Note: In earlier versions of Excel, blank cells will also cause this error message. You can fill in actual dates, or use dummy dates, such as 2/2/2099, in the source data. Do not leave the cells blank if you want to group the dates.

More Information

For more information on pivot table grouping, please click here.

Changing the Default PivotTable Style in Excel 2007

When you create a pivot table in Excel 2007 or 2010, a PivotTable Style is automatically applied.

pivot table style

If you prefer a different PivotTable Style, you can change the default PivotTable Style in a workbook, to save time when you create new pivot tables.

Follow these steps to change the default PivotTable Style:

  1. Select a cell in the pivot table.
  2. On the Ribbon, under the PivotTable Tools tab, click the Design tab.
  3. In the PivotTable Style gallery, right-click on the style that you want to set as the default.
  4. In the context menu, click on Set As Default.

set default pivot table style

For more information on pivot table styles, click here.

_________