Buzz
Today, Mytour will show you how to create and insert a new column into a Pivot Table in Microsoft Excel. You can transform existing rows, fields, or data into columns, or create custom formula-based columns for your data analysis.
Steps
Convert Data Field to Column
Open the Excel file containing the Pivot Table you want to edit. Find and double-click on 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 proceeding.
Click any cell in the pivot table to select it. The Pivot Table Analyze and Design tabs will appear on the ribbon at the top of the screen.
Click the Pivot Table Analyze tab at the top. This tab is aligned with other tabs like Formulas, Insert, and View at the top of the application window. The pivot table tools will be visible on the ribbon.
- In some versions, this tab is simply called Analyze or appears as an option under the title "Pivot Table Tools".
Click the Field List button on the ribbon. This button is located to the right of the Pivot Table Analyze tab. All the fields, rows, columns, and values in the selected table will be displayed.
Check the box next to any field in the FIELD NAME list. The sum of the data in the selected category will be calculated and added to the pivot table as a new column.
- Typically, non-numeric fields are added as rows, while numeric fields are added as columns by default.
- You can uncheck this box at any time to remove the column.
Drag any field, row, or value item and drop it into the "Columns" section. The selected item will automatically be moved to the Columns list, and the pivot table will be redesigned to include the newly added column.
Add Calculation Field
Open the Excel document you want to edit. Double-click the Excel file containing your pivot table.
- If you haven't created a pivot table yet, open a new Excel document and create a Pivot Table before proceeding.
Select the pivot table you want to edit. Click on the Pivot Table in the worksheet to select and edit it.
Click on the Pivot Table Analyze. This tab is located in the middle of the Excel window's toolbar ribbon. The pivot table tools will appear on the ribbon.
- In some versions, this tab is simply called Analyze or shows up as an option below the "Pivot Table Tools" title.
Click the Fields, Items, & Sets button on the ribbon. This button, marked with "fx" inside a table icon, is located at the far right of the toolbar. A dropdown menu will appear.
Click on Calculated Field in the dropdown menu. A window will open allowing you to add a new custom column to the Pivot Table.
Enter a name for the column in the "Name" field. Click on the Name data field and type the name you wish to assign to the new column. This name will be displayed at the top of the column.
Enter the formula for the new column in the "Formula" field. Click on the Formula data field below the Name field and input the formula you wish to use for calculating the data in the new column.
- Make sure to enter the formula after the "=" symbol.
- You can also choose an existing column and add it to the formula as a value. Select the field you want to add to the data field section here and click Insert Field to include it in the formula.
Click OK. The column will be added to the right side of the pivot table.
Tip
- Make sure to back up the original Excel file before making any changes to the summary table.
Warning
- Don't forget to save your work once you've finished.
