How to Add a Column or Calculated Field in an Excel Pivot Table

PDF download Download Article

Quick step-by-step guide to creating a new pivot table column

PDF download Download Article

This wikiHow teaches you how to insert a new column into a pivot table in Microsoft Excel with the pivot table tools. You can easily change an existing row, field, or value to a column, or create a new calculated field column with a custom formula.

Things You Should Know

  • Show the Field List by going to PivotTable Analyze > Field List.
  • Drag field items to the Columns area in the Field List to create new columns.
  • Go to PivotTable Analyze > Fields, Items, & Sets > Calculated Field to make a custom field.
Method 1
Method 1 of 2:

Changing a Field to Column

PDF download Download Article
  1. Step 1 Open the Excel file with the pivot table you want to edit.
    Find and double-click your Excel file on your computer to open it.
    • If you haven't made your pivot table yet, open a new Excel document and create a pivot table before continuing.
  2. Step 2 Click any cell on the pivot table.
    This will select the table, and show the “PivotTable Analyze” and “Design” tabs on the toolbar ribbon at the top.
    Advertisement
  3. Step 3 Click the PivotTable Analyze tab at the top.
    You can find this tab alongside other tabs like Formulas, Insert, and View at the top of the app window. It will show your pivot table tools on the toolbar ribbon.[1]
    • On some versions, this tab may just be named Analyze, and on others, you can find it as Options under the "Pivot Table Tools" heading.
  4. Step 4 Click the Field List button on the toolbar ribbon.
    You can find this button on the right-hand side of the “PivotTable Analyze” tab. It will open a list of all the fields, rows, columns, and values in the selected table.[2]
  5. Step 5 Check the box next to any item on the “PivotTable Fields” list.
    This will calculate the summary of your original data in the selected category, and add it to your pivot table.[3]
    • Typically, non-numeric fields are added as rows, and numeric fields are added as values.
    • You can uncheck the checkbox here anytime to remove the field from your pivot table.
  6. Step 6 Drag and drop any field item to the "Columns" section.
    This will move the selected category to the Columns list, and re-design your pivot table with the newly added column.[4]
    • For example, if you have a column of years called “Years” in your source data, you can add the “Years” field to the Columns area to create columns for each year, showing values for each individual year.
    • You can drag the field item name from the list of fields or from another area (Filters, Rows, Values).
    • Now you’re ready to analyze the data and make a few charts using the pivot table.
    7. Advertisement
Method 2
Method 2 of 2:

Adding a Calculated Field

PDF download Download Article
  1. Step 1 Open the Excel document you want to edit.
    Double-click the Excel document that contains your pivot table. This method will create a custom field using the existing fields and data.
    • If you haven't yet made the pivot table, open a new Excel document and create a pivot table before continuing.
  2. Step 2 Select the pivot table you want to edit.
    Click the pivot table on your worksheet to select and edit it.
  3. Step 3 Click the PivotTable Analyze tab.
    This tab is in the middle of the toolbar ribbon at the top of the Excel window. It will open your pivot table tools on the toolbar ribbon.[5]
    • On different versions, this tab may be named Analyze, or Options under the "Pivot Table Tools" heading.
  4. Step 4 Click the Fields, Items, & Sets button on the toolbar ribbon.
    This button looks like an "fx" sign on a table icon on the far-right end of the toolbar. It will open a drop-down menu.[6]
  5. Step 5 Click Calculated Field on the drop-down menu.
    It will open a new window where you can add a new, custom column to your pivot table.[7]
  6. Step 6 Enter a name for your column in the "Name" field .
    Click the Name field, and type in the name you want to use for your new column. This name will appear at the top of the column.[8]
  7. Step 7 Enter a formula for your new column in the "Formula" field.
    Click the “Formula” field below “Name”, and type the formula you want to use for calculating your new column's data values.[9]
    • Make sure you type the formula on the right side of the "=" sign.
    • Optionally, you can also select an existing column, and add it to your formula as a value:
      • Select the field you want to add in the Fields section.
      • Click Insert Field to add it to your formula.
    • For example, if you have the fields “revenue” and “costs”, you could subtract them to get a “profit” calculated field.
  8. Step 8 Click OK.
    Doing so will add the column to the right side of your pivot table.
    • Alternatively, click Add to create the calculated field without closing the “Insert Calculated Field” window. This allows you to add additional calculated fields.
    • You can reorder the columns by dragging the field items in the pivot table “Column” area.
    9. Advertisement

Expert Q&A

Ask a Question
200 characters left
Include your email address to get a message when this question is answered.
Submit
Advertisement

Video

Tips

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

Warnings

Advertisement

You Might Also Like

Create Pivot Tables in Excel
How to
Create Pivot Tables in Microsoft Excel to Analyze Data
Create a Chart from a Pivot Table
How to
Create a Chart from a Pivot Table
Add a Custom Field in Pivot Table
How to
Add Custom Calculated Fields to Pivot Tables in Excel
Merge Cells in Excel
How to
Merge Cells in Microsoft Excel: A Quick Guide
Unprotect an Excel Sheet
How to
Unprotect an Excel Sheet
How to
Unhide All Rows and Columns in Microsoft Excel
Use If‐Else in Excel
How to
Use If‐Else in Microsoft Excel: Step-by-Step Tutorial
Insert Pictures in Excel That Automatically Size to Fit Cells
How to
Insert Pictures in Excel That Automatically Size to Fit Cells
Create a Mortgage Calculator With Microsoft Excel
How to
Create a Mortgage Calculator With Microsoft Excel
Create a Graph in Excel
How to
Create a Graph in Excel
Unhide Columns in Excel
How to
Show Hidden Columns in Microsoft Excel & Unhide Column A
Ethernet Doesn't Have a Valid IP Configuration
How to
Fix Ethernet Doesn't Have a Valid IP Configuration on Windows
Freeze Cells in Excel3 Easy Ways to Freeze Panes to Lock Columns or Rows in Excel
Change from Lowercase to Uppercase in Excel
How to
Change from Lowercase to Uppercase in Microsoft Excel
Advertisement

References

  1. ↑ https://support.microsoft.com/en-us/office/create-a-pivottable-to-analyze-worksheet-data-a9a84538-bfe9-40a9-a8e9-f99134456576
  2. ↑ https://support.microsoft.com/en-us/office/use-the-field-list-to-arrange-fields-in-a-pivottable-43980e05-a585-4fcd-bd91-80160adfebec
  3. ↑ https://support.microsoft.com/en-us/office/use-the-field-list-to-arrange-fields-in-a-pivottable-43980e05-a585-4fcd-bd91-80160adfebec
  4. ↑ https://support.microsoft.com/en-us/office/design-the-layout-and-format-of-a-pivottable-a9600265-95bf-4900-868e-641133c05a80
  5. ↑ https://support.microsoft.com/en-us/office/calculate-values-in-a-pivottable-11f41417-da80-435c-a5c6-b0185e59da77#ID0EBF=Windows,_Mac
  6. ↑ https://support.microsoft.com/en-us/office/calculate-values-in-a-pivottable-11f41417-da80-435c-a5c6-b0185e59da77#ID0EBF=Windows,_Mac
  7. ↑ https://support.microsoft.com/en-us/office/calculate-values-in-a-pivottable-11f41417-da80-435c-a5c6-b0185e59da77#ID0EBF=Windows,_Mac
  8. ↑ https://support.microsoft.com/en-us/office/calculate-values-in-a-pivottable-11f41417-da80-435c-a5c6-b0185e59da77#ID0EBF=Windows,_Mac
  9. ↑ https://support.microsoft.com/en-us/office/calculate-values-in-a-pivottable-11f41417-da80-435c-a5c6-b0185e59da77

About This Article

Kyle Smith
Co-authored by:
Kyle Smith
wikiHow Technology Writer
This article was co-authored by wikiHow staff writer, Kyle Smith. Kyle Smith is a wikiHow Technology Writer, learning and sharing information about the latest technology. He has presented his research at multiple engineering conferences and is the writer and editor of hundreds of online electronics repair guides. Kyle received a BS in Industrial Engineering from Cal Poly, San Luis Obispo. This article has been viewed 714,695 times.
How helpful is this?
Co-authors: 16
Updated: September 16, 2024
Views: 714,695
Categories: Microsoft Excel
Article SummaryX

1. Click on the PivotTable.
2. Click the checkbox of the field you want to add.
3. Right-click on the added field.
4. Select Move to... Column Labels or Values.

Did this summary help you?

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

Is this article up to date?

Advertisement