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.
Steps
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
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
Step 2. Select your table
In your worksheet, click on the table to which you plan to add a column.
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
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.
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.
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.
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.
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.