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

• How to Create a Calculated Field
• Calculated Items in Pivot Tables

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.

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.

Watch the Create a Calculated Item Video

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

______________________