How to add a column in a pivot table

Table of contents:

How to add a column in a pivot table
How to add a column in a pivot table

PivotTable is a very useful feature of Excel in analyzing and comparing data. If you already have a table and want to complete it by adding columns or a calculated field, it's very easy with the PivotTable tools.


Method 1 of 2: Change a field in a column

Step 1. Open your file

Find the file with the PivotTable you want to edit.

If you haven't created your PivotTable yet, open a new Excel document and do so

Step 2. Click on a table cell

This allows you to select the table and display the tabs. to analyse and design of the dynamic table in the toolbar at the top.

Step 3. Choose Analyze Dynamic Table

You will find this option next to others like Insert, Formulas, Display at the top of the window. You will then see the PivotTable tools in the toolbar.

  • In some versions, this function is called Analysis, but in others, you will find it in the preferences, under the heading PivotTable Tools.

Step 4. Select Field List

This button is located on the right of the dynamic table analysis tab. A list will open, it contains all the rows, columns, all the fields and the values ​​of the selected table.

Step 5. Check a box in the list of field names

This will calculate the summary of your original data in the chosen category and add it to your dynamic table, in a new column.

  • In general, by default, non-numeric fields are added as rows and numeric fields as columns.
  • To remove the column, simply uncheck the box.

Step 6. Drag the fields

You can drag any value, row, or field into the section Columns. The selected category will thus go into the list of columns automatically and change your dynamic table with the new column added.

Method 2 of 2: Add a calculated field

Add a Column in a Pivot Table Step 1

Step 1. Navigate to your file

Double-click on your Excel document to open it. Select the workbook sheet that presents your PivotTable.

In case you don't have your PivotTable yet, first open a new worksheet and do a PivotTable

Add a Column in a Pivot Table Step 2

Step 2. Select your table

In your worksheet, click on the table to which you plan to add a column.

Add a Column in a Pivot Table Step 3

Step 3. Tap the Options tab

Once you tap on your table you will see in the ribbon PivotTable Tools with two tabs below. Select the tab Options to bring up the features.

On Mac, you will need to select PivotTable Analysis

Add a Column in a Pivot Table Step 4

Step 4. Click the Fields, Items, and Sets icon

In the tab Options of PivotTable Tools, select the feature Fields, elements and sets in the section Calculations.

Add a Column in a Pivot Table Step 5

Step 5. Tap Calculated Field

After selecting Fields, elements, and sets, a context menu opens. Choose Calculated field. A pop-up window named Inserting a calculated field appears.

Add a Column in a Pivot Table Step 6

Step 6. Enter the name of the column

Give your column a name by writing it in the field name. This is the first control in the window.

Add a Column in a Pivot Table Step 7

Step 7. Write the calculation formula

Enter a calculation formula in the field Formula which will be used in your new column.

  • Remember, for this to work you must start with the sign "=" when writing your formula.
  • For example, you may find it useful to quadruple the data in the third column of your table. To do this, select the name of your third column and press the button Insert field, then write * 4. Thus, the values ​​which are in your third column will be multiplied by four and the result will be present in the new column.
Add a Column in a Pivot Table Step 8

Step 8. Select the OK button

Once the formula is written, tap OK to validate your calculation and create a new column on the right in your table which will present the results.

  • If you do not want the column to appear in your pivot table, all you have to do is uncheck the box in front of its name in the right part titled PivotTable Field List.

Popular by topic