How to Add a Calculated Item to an Excel Pivot Table

By Stephen L. Nelson, E. C. Nelson

You can add calculated items to a Excel pivot table. Now, frankly, adding a calculated item usually doesn’t make any sense. If, for your pivot table, you have retrieved data from a complete, rich Excel list or from some database, creating data by calculating item amounts is more than a little goofy. However, in the spirit of fair play and good fun, imagine this scenario.

Assume that your Excel list omits an important product item. Suppose that you have another roast coffee product called Volcano Blend Decaf. And even though this product item information doesn’t appear in the source Excel list, you can calculate this product item’s information by using a simple formula.

Also assume that sales of the Volcano Blend Decaf product equal exactly and always 25 percent of the Volcano Blend product. In other words, even if you don’t know or don’t have Volcano Blend Decaf product item information available in the underlying Excel data list, it doesn’t really matter. If you have information about the Volcano Blend product, you can calculate the Volcano Blend Decaf product item information.

Here are the steps that you take to add a calculated item for Volcano Blend Decaf to the Roast Coffee Products pivot table:

  1. Select the Product button by simply clicking the Row Labels button in the pivot table.

  2. Tell Excel that you want to add a calculated item to the pivot table.

    Click the Analyze ribbon’s Fields, Items & Settings command and then choose Calculated Items from the submenu that appears. Excel displays the Insert Calculated Item in “Product” dialog box.

    In Excel 2007 or Excel 2010, click the PivotTable Tools Options tab’s Formulas command and then choose Calculated Items from the Formulas submenu that appears.

    image0.jpg

  3. Name the new calculated item in the Name text box.

    In the example, the new calculated item name is Volcano Blend Decaf, so that’s what you enter in the Name text box.

  4. Enter the formula for the calculated item in the Formula text box.

    Use the Formula text box to give the formula that calculates the item. In the example here, you can calculate Volcano Blend Decaf sales by multiplying Volcano Blend sales by 25 percent. This formula then is =.25*‘Volcano Blend’.

    1. To enter this formula into the Formula text box, first type =.25*.

    2. Then select Volcano Blend from the Items list box and click the Insert Item button.

      image1.jpg

    3. 5. Add the calculated item.

    After you name and supply the formula for the calculated item, click OK. Excel adds the calculated item to the pivot table. Here is the pivot table of roast coffee product sales by month with the new calculated item, Volcano Blend Decaf.

    This isn’t an item that comes directly from the Excel data list, as you can glean from the preceding discussion. This data item is calculated based on other data items: in this case, based on the Volcano Blend data item.

    image2.jpg