How to Add Custom Calculated Fields to Pivot Tables in Excel

PDF download Download Article
Easily customize your pivot table in Microsoft Excel with this in-depth guide
PDF download Download Article

Do you want to add more information to your pivot table without altering your source data? You easily can add custom calculated fields to create formulas within the pivot table instead of adding more columns to your worksheet. Custom fields can do nearly any calculations you need, like displaying averages, percentages, variances, and maximum values for fields. We'll walk you through adding a custom calculated field to a pivot table in your Microsoft Excel spreadsheet, and show you how to edit the field any time you need to change the formula.

Adding a Custom Field in a Pivot Table: Quick Steps

  1. Open your Excel workbook and select the pivot table you're editing.
  2. Select the PivotTable Analyze tab from the top.
  3. Open the Fields, Items, and Sets menu.
  4. Click Calculated Field from the list.
  5. Enter the name and formula for the custom field.
  6. Click Add and then Okay to complete the field.
Method 1
Method 1 of 2:

Adding Custom Fields

PDF download Download Article
  1. Double-click the file that has the source data and pivot table you'll be working with. [1]
    • Keep in mind that calculated fields can't use references or named ranges in formulas —this means you can't use functions like INDEX, OFFSET, or VLOOKUP in your custom field. However, functions like IF, SUM, COUNT, and any others that don't require references or named ranges are fine.
  2. When you click the pivot table, the "PivotTable Fields" panel will expand on the right. You'll also see the PivotTable Analyze menu at the top of Excel.
    Advertisement
  3. You'll see this tab at the top of Excel.
    • The PivotTable Analyze menu is only visible when you click to activate the pivot table. If the menu ever disappears, just click the pivot table again to bring it back up.
    • This tab is called Analyze in some versions of Excel.
  4. You'll see this in the "Calculations" area of the toolbar at the top of Excel. [2]
  5. Once you click this menu option, the "Insert Calculated Field" dialog box will appear. [3]
  6. Type a descriptive column label for your custom field into the "Name" box.
  7. In the "Formula" box, type the formula you want to use to calculate your custom field. [4]
    • For example, to find the tax rate for the "Items" field at 8.5%, you could name your custom field "Tax" and use this formula: =Items * 8.5% .
    • Another example: If you want to divide values in the "Sales" column by values in the "Quantity" column, you could use: =Sales/Quantity .
    • To insert data from another pivot table field in your formula, select the field and click Insert Field .
  8. This adds your custom field to the Fields list.
  9. This closes the "Insert Calculated Field" box and returns you to the pivot table.
  10. Now that you've added a new field, you'll see it in the "PivotTable Fields" panel on the right side of your sheet in the "Values" column.
  11. You can now drag your custom calculated field from the Values area to Filters, Rows, or Columns to apply the formula to your data.
    12. Advertisement
Method 2
Method 2 of 2:

Editing Custom Fields

PDF download Download Article
  1. Editing the formula in this one calculated field is much easier than creating—and later editing—a formula in the source data. This can be useful when the amount by which you are calculating your field changes often. Clicking the pivot table will activate the PivotTable Analyze or Analyze menu at the top of Excel.
  2. You'll see these at the top of Excel. [5]
  3. You'll see this in the "Calculations" area of the toolbar at the top of Excel.
  4. Once you click this menu option, the "Insert Calculated Field" dialog box will appear. [6]
  5. All fields, including your custom fields, appear in the "Fields" area. When you click your custom field, you'll see its formula in the "Formula" box, and its name in the "Name" box.
  6. You can now make any adjustments you need to your custom field. [7]
    • If you want to delete the field, just click Delete instead.
  7. Your field is now updated. If your pivot table doesn't refresh automatically, right-click the table and select Refresh to see your new calculations.
    8. Advertisement

Expert Q&A

Search
Add New Question
  • Question
    Every time that I create a new pivot table and I select a field (revenue) and I move it to values space, it defaults to count of revenue even though I've used sum of revenue in previous pivot tables.
    Nicole Levine, MFA
    wikiHow Technology Writer
    Nicole Levine is a Technology Writer and Editor for wikiHow. She has more than 20 years of experience creating technical documentation and leading support teams at major web hosting and software companies. Nicole also holds an MFA in Creative Writing from Portland State University and teaches composition, fiction-writing, and zine-making at various institutions.
    Nicole Levine, MFA
    wikiHow Technology Writer
    Expert Answer
    It would default to count instead of sum if there are any blank cells or errors in your data--if you have any of either, replace them with 0 (zeros) and that should sort if out. If that doesn't work, you might have non-numerical characters in your data (or numbers that are formatted as text instead of numbers). Make sure your data is purely numerical and you should get a sum instead of the count.
    Thanks! We're glad this was helpful.
    Thank you for your feedback.
    If wikiHow has helped you, please consider a small contribution to support us in helping more readers like you. We’re committed to providing the world with free how-to resources, and even $1 helps us in our mission. Support wikiHow
    Yes No
    Not Helpful 0 Helpful 0
  • Question
    How can I add a new field to the Filters or Rows section of the Pivot Table?
    Nicole Levine, MFA
    wikiHow Technology Writer
    Nicole Levine is a Technology Writer and Editor for wikiHow. She has more than 20 years of experience creating technical documentation and leading support teams at major web hosting and software companies. Nicole also holds an MFA in Creative Writing from Portland State University and teaches composition, fiction-writing, and zine-making at various institutions.
    Nicole Levine, MFA
    wikiHow Technology Writer
    Expert Answer
    Once you create the new field, it will appear in the "Values" section of the PivotTable Fields panel on the right side of your sheet. To use the value as a filter, drag it over to the Filters section.
    Thanks! We're glad this was helpful.
    Thank you for your feedback.
    If wikiHow has helped you, please consider a small contribution to support us in helping more readers like you. We’re committed to providing the world with free how-to resources, and even $1 helps us in our mission. Support wikiHow
    Yes No
    Not Helpful 0 Helpful 0
  • Question
    I would like to display the number of occurrences (count) of incidences where where column A is less than 100 when Column C is equal to a given value.
    Nicole Levine, MFA
    wikiHow Technology Writer
    Nicole Levine is a Technology Writer and Editor for wikiHow. She has more than 20 years of experience creating technical documentation and leading support teams at major web hosting and software companies. Nicole also holds an MFA in Creative Writing from Portland State University and teaches composition, fiction-writing, and zine-making at various institutions.
    Nicole Levine, MFA
    wikiHow Technology Writer
    Expert Answer
    The COUNTIFS formula would get you that result, but you can't use that function in a calculated field. You might want to add a new column to your source data that contains an IFS statement, which you can count in your pivot table without adding any calculated fields.
    Thanks! We're glad this was helpful.
    Thank you for your feedback.
    If wikiHow has helped you, please consider a small contribution to support us in helping more readers like you. We’re committed to providing the world with free how-to resources, and even $1 helps us in our mission. Support wikiHow
    Yes No
    Not Helpful 0 Helpful 0
See more answers
Ask a Question
200 characters left
Include your email address to get a message when this question is answered.
Submit
      Advertisement

      Video

      Tips

      • Calculated fields in a pivot table calculate against the sum totals, not against individual rows. [8] You will need to insert a column and formula in the source data if you need a calculation by individual rows.
        Thanks
        Helpful 0 Not Helpful 0
      • It's not possible to use a calculated field as a report filter or slicer.
        Thanks
        Helpful 0 Not Helpful 0
      Submit a Tip
      All tip submissions are carefully reviewed before being published
      Name
      Please provide your name and last initial
      Submit
      Thanks for submitting a tip for review!
      Advertisement

      You Might Also Like

      How to
      Add Rows to a Pivot Table
      How to
      Create Pivot Tables in Microsoft Excel to Analyze Data
      How to
      Add a Column or Calculated Field in an Excel Pivot Table
      How to
      Create a Currency Converter With Microsoft Excel
      How to
      Create a Mortgage Calculator With Microsoft Excel
      4 Easy Ways to Add the Time and Date Automatically in Sheets
      How to
      Create an IF-THEN Formula in Excel: Basic Syntax, Multiple Conditions, & More
      How to
      Use Google Sheets: The Ultimate Guide for Getting Started
      How to
      Copy the Same Text to Multiple Cells in Excel: Desktop & Mobile
      The 3 Easiest Ways to Sort by Number in Google Sheets
      3 Easy Ways to Get Rid of Dotted Lines in Microsoft Excel
      Easily Embed Documents and Files in an Excel Spreadsheet
      How to
      Change the X Axis Scale in an Excel Chart
      How to
      Make a Google Spreadsheet: Beginners Guide
      Advertisement

      References

      1. ↑ https://edu.gcfglobal.org/en/excel2016/creating-and-opening-workbooks/1/
      2. ↑ https://support.microsoft.com/en-gb/office/create-a-pivottable-to-analyze-worksheet-data-a9a84538-bfe9-40a9-a8e9-f99134456576
      3. ↑ https://support.microsoft.com/en-us/office/calculate-values-in-a-pivottable-11f41417-da80-435c-a5c6-b0185e59da77
      4. ↑ https://support.microsoft.com/en-us/office/calculate-values-in-a-pivottable-11f41417-da80-435c-a5c6-b0185e59da77
      5. ↑ https://support.microsoft.com/en-us/office/create-a-pivottable-to-analyze-worksheet-data-a9a84538-bfe9-40a9-a8e9-f99134456576
      6. ↑ https://support.microsoft.com/en-us/office/calculate-values-in-a-pivottable-11f41417-da80-435c-a5c6-b0185e59da77
      7. ↑ https://support.microsoft.com/en-us/office/calculate-values-in-a-pivottable-11f41417-da80-435c-a5c6-b0185e59da77
      8. ↑ https://support.microsoft.com/en-us/office/calculate-values-in-a-pivottable-11f41417-da80-435c-a5c6-b0185e59da77

      About This Article

      In other languages
      Thanks to all authors for creating a page that has been read 537,102 times.

      Is this article up to date?

      Advertisement