Accidentally Create Calculated Items

Accidentally Create Calculated Items

In a pivot table, you can create calculated fields and calculated items, by inserting following a few steps, to insert formulas. There are detailed instructions on my web site

But did you know that you can accidentally create a calculated item too, without going through all the usual steps?

Create a Calculated Item by Dragging

It’s possible to accidentally create a calculated item if you select a cell that contains a Row or Column label, and drag the fill handle, at the bottom right of the selected cell.

  • In the screen shot below, I selected the Banana item, in the Product field.
  • Then, I pointed to the fill handle – the small black square in the bottom right corner of the selected range.
  • When I dragged down a couple of rows, two calculated items were created – Formula1 and Formula2.

pivotitemaccidental01

The new calculated items not only show up in the Bars category, where I created them – they’re in all the other categories too.

Problems with Calculated Items

If you don’t need or want a calculated item, you should remove it. These items can slow down a pivot table, especially if it’s based on a large set of data.

Also, there are other drawbacks to using a calculated item in a field. For example, you will:

  • NOT be able to move the field to the Report Filters area
  • NOT be able to add multiple copies of a field to the Values area.

Remove a Calculated Item

Follow these steps to remove any unwanted calculated items:

  • Select the cell that contains the label for the Formula1 calculated item.
  • On the Ribbon, under the PivotTable Tools tab, click the Analyze tab (or the Options tab in Excel 2010).
  • In the Calculations group, click Fields, Items and Sets (Click Formulas in Excel 2010).
  • Click Calculated Item.
  • From the drop-down list of formulas, select the formula you want to delete.
  • Click the Delete button
  • Select and delete any other unwanted items, and then click the Close button.

pivotitemremove01

Watch the Create a Calculated Item Video

To see the steps for creating a calculated item, please watch this short video tutorial.

______________________

4 thoughts on “Accidentally Create Calculated Items”

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.