This article explains how to create and insert a new column in a PivotTable in Microsoft Excel with the PivotTable tools. You can turn a row, field or value into a column, or create a new one calculated with a custom formula.
Steps
Method 1 of 2: Turn a Field into a Column
Step 1. Open the Excel file with the pivot table you want to edit
Find the Excel file you are interested in and double click on it to open it.
If you don't already have a PivotTable, open a new Excel document and create one before continuing
Step 2. Click on a cell of the pivot table
In this way, you will select the table by making the tabs appear Pivot table analysis And Design in the toolbar at the top.
Step 3. Click on the PivotTable Analysis tab at the top
You can find it next to others such as Formulas, Insert and View at the top of the program window. Select it to make the pivot table tools appear in the ribbon.
On some versions, this card may simply be called Analyses, while in others you can find it as Options under the "PivotTable Tools" heading.
Step 4. Click the Field List button on the toolbar
You can find it on the right side of the PivotTable Analysis tab. Select it to open a list of all fields, rows, columns and values in the selected table
Step 5. Check the box next to one of the objects in the FIELD NAME list
This way, you will calculate the sum of the original data in the selected category and add it to the pivot table as a new column.
- Usually, by default, non-numeric fields are added as rows, while numeric fields are added as columns.
- You can uncheck the box at any time to remove this column.
Step 6. Drag any field, row or value to the "Columns" section
This will automatically move the selected item to the Columns list, modifying the pivot table with the new column.
Method 2 of 2: Add a Calculated Field
Step 1. Open the Excel document you want to edit
Double-click on the file that contains the pivot table.
If you don't already have a PivotTable, open a new Excel document and create one before continuing
Step 2. Select the pivot table you want to edit
Click on Pivot Table on your spreadsheet to select and edit it.
Step 3. Click on the PivotTable Analysis tab
You will see it in the center of the toolbar, at the top of the Excel window. Select it to open the pivot table tools in the ribbon.
In the various versions, this card can have the name Analyses or Options under the "PivotTable Tools" heading.
Step 4. Click the Fields, Elements and Series button in the toolbar
This button looks like the "fx" symbol on a table and is located at the far right of the bar. Select it to open a drop-down menu.
Step 5. Click Calculated Field in the drop-down menu
Select this item to open a new window where you can add a new custom column to your pivot table.
Step 6. Enter a name for the column in the "Name" field
Click on this field and type the name you want to use for the new column, which will appear at the top of the column.
Step 7. Enter the formula for the new column in the "Formula" field
Click on this field, located under Name and type the formula you want to use to calculate the data values of the new column.
- Make sure you type the formula on the right side of the "=" symbol.
- Optionally, you can also select an existing column and add it to your formula as a value. Select the field to add to the Fields section here, then click Insert field to add it to the formula.
Step 8. Click Ok
This way, you will add the column to the right side of the pivot table.
