How to Add Columns in a Pivot Table

Today's TipsMake will show you how to create and insert new columns into a pivot table on Microsoft Excel using the Pivot Table tool. You can change existing rows, fields, or data into columns, or create columns of calculated data using custom formulas.

Change data fields to columns

Picture 1 of How to Add Columns in a Pivot Table

Open the Excel file with the pivot table you want to edit. Find and double-click the Excel file on your computer to open it.

If you haven't created a pivot table yet, open a new Excel document and create a Pivot Table before continuing.

Picture 2 of How to Add Columns in a Pivot Table

Click any cell in the pivot table to select it. The Pivot Table Analyze and Design tabs appear in the tools ribbon at the top of the screen.

Picture 3 of How to Add Columns in a Pivot Table

Click the Pivot Table Analyze tab at the top. This tab is located alongside other tabs (such as Formulas, Insert, and View) at the top of the application window. The Pivot Table tool appears in the tools ribbon.

On some versions, this tab is called Analyze for short or appears as an option under the "Pivot Table Tools" heading.

Picture 4 of How to Add Columns in a Pivot Table

Click the Field List button in the ribbon. This button is to the right of the Pivot Table Analyze tab. A list of all fields, rows, columns, and values ​​in the selected table will appear.

Picture 5 of How to Add Columns in a Pivot Table

Check the box next to any item in the FIELD NAME list. The sum of the original data in the category you select will be calculated and added to the pivot table as a new column.

Typically, non-numeric data fields are added as rows, while metric fields are added as columns by default.

You can uncheck the check box here at any time to delete the column.

Picture 6 of How to Add Columns in a Pivot Table

Drag any field, row of data, or item of value and drop it into the "Columns" section. The selected category will automatically be moved to the Columns list and the pivot table will be redesigned with the newly added column.

Add calculated fields

Picture 7 of How to Add Columns in a Pivot Table

Open the Excel document you want to edit. Double-click the Excel document that contains your pivot table.

If you haven't created a pivot table yet, open a new Excel document and create a Pivot Table before continuing.

Picture 8 of How to Add Columns in a Pivot Table

Select the pivot table you want to edit. Click Pivot Table on the spreadsheet to select and edit.

Picture 9 of How to Add Columns in a Pivot Table

Click the Pivot Table Analyze tab . This tab is located in the middle of the tool ribbon at the top of the Excel window. The Pivot Table tool opens on the tools ribbon.

On some versions, this tab is called Analyze for short or appears as an option under the "Pivot Table Tools" heading.

Picture 10 of How to Add Columns in a Pivot Table

Click the Fields, Items, & Sets button in the tools ribbon. This button has the word "fx" in the table icon at the bottom right of the toolbar. A drop-down menu will appear.

Picture 11 of How to Add Columns in a Pivot Table

Click Calculated Field in the drop-down menu. A window for you to add a new custom column to the Pivot Table will open.

Picture 12 of How to Add Columns in a Pivot Table

Enter a name for the column in the "Name" field. Click the Name data field and enter the name you want for the new column. This name will appear at the top of the column.

Picture 13 of How to Add Columns in a Pivot Table

Enter the formula for the new column in the "Formula" field. Click the Formula data field below the Name field and enter the formula you want to use to calculate the data value in the new column.

You need to make sure you enter the formula to the right of the "=" sign.

Or you can also select an existing column and add it to the formula as a value. Select the field you want to add to the data fields section here and click Insert Field to insert it into the formula.

Picture 14 of How to Add Columns in a Pivot Table

Click OK . The column will be added to the right of the pivot table.

Update 10 November 2023
Category

System

Mac OS X

Hardware

Game

Tech info

Technology

Science

Life

Application

Electric

Program

Mobile