Contextures

Pivot Table Comments - Tips and Problems

It is possible to add comments to an Excel pivot table, but they don't work well. See the tips and workaround steps below

Introduction

After you build a pivot table, you might want to add comments to some cells, to explain the data in those cells. However, although you can add comments to a pivot table, they do not work well, and may cause more problems than they solve.

See how to add a comment to an Excel pivot table, and deal with the problems that can occur.

Add Comment to Pivot Table

To insert a comment in a regular worksheet cell, you can right-click the cell, and click Insert Comment

insert comment command

However, that Insert Comment command doesn't appear in the popup menu if you right-click on a pivot table cell.

insert comment command missing

Instead, you can follow these steps to insert a comment, by using a command on the Ribbon

  1. Select the pivot table cell in which you want the comment.
  2. On the Excel Ribbon, click the Review tab
  3. Click New Comment

    insert comment command on Ribbon

  4. Type the comment

    type comment text

  5. Click away from the cell, to close the comment

View the Comments

After you add comments to a pivot table, you might have trouble viewing them.

With the default pivot table settings, contextual tooltips appear when you point to a pivot table cell. Those tooltips will override any comments that are in the cell.

In the screen shot below, you can see a red comment marker, but the comment doesn't display when pointing to the cell -- only the tooltip is visible..

tooltip in comment cell

To see the comments, you can either

  • show all the comments, or
  • adjust a pivot table setting.

Show All the Comments

Although the individual comments will not appear when you point to a pivot table cell, you can show or hide all the comments on the worksheet.

  1. On the Excel Ribbon, click the Review tab
  2. Click Show All Comments

show all comments

All the comments on the sheet are displayed, including any comments in the pivot table. While the comments are displayed, you can also point to a pivot table cell, and see its tooltip.

tooltip and comment

NOTE: To hide the comments again, click the Show All Comments command, to turn it off.

Hide Pivot Table Tooltips

If you would like to see individual comments, instead of showing all comments, you can turn off the tooltips feature for the pivot table.

  1. Right-click a cell in the pivot table, and then click PivotTable options.
  2. In the PivotTable Options dialog box, on the Display tab
  3. Remove the check mark from Show contextual tooltips and then click OK.

turn off tooltips

After you turn off this setting, the comment appears when you point to the cell.

comment appears in cell

Pivot Table Comments Do Not Move

When you add a comments a pivot table, the comment is attached to the Excel worksheet cell, rather than to a specific item in the pivot table.

If you change the pivot table layout, the comment will NOT move with the pivot item -- it will remain in the original cell. This could end up confusing people who use the pivot table, instead of helping them.

There is no way to change this behavior, so it's best to avoid comments, if the pivot table layout will change.

For example, in the screen shot below, a the fields have been moved in the pivot table layout. The comment is still in cell D6, but that cell now has data for Arrowroot Bars, instead of Banana Bars.

comment does not move

Alternative to Comments

Instead of inserting comments in the pivot table, you could add a column to the source data, and enter brief comments there.

new field in source data

Anyone who has a question about the data in the pivot table could double-click on the pivot table value cell. Or, right-click the cell, and click Show Details

show details command

That will trigger the Show Details feature, to extract the source data for the selected data to a new worksheet, where they can read any notes entered.

comments in extracted data

Download the Sample File

Download the sample file with the examples from this tutorial. The file is zipped, and is in xlsx format. The file does not contain macros: Pivot Table Comments sample file

Search Contextures Sites

 

Excel Tools Add-in

Free Pivot Table Tools

 

Pivot Power Premium

 

Excel Data Entry Popup List

 

 

 

Excel UserForms for Data Entry

 

Last updated: August 2, 2016 7:22 PM
Contextures RSS Feed