In the world of data analysis, efficiency is paramount, and the key lies in Pivot Table Calculated Fields. If you've ever pondered how to derive new data points seamlessly without altering your source data, you've found your solution. With this guide, you'll unlock the path to mastering calculated fields, becoming an expert in data analysis. No more mysteries, just answers – let's unleash the power of Pivot Tables together.
Part1. Introduction to Calculated Field in Excel
A calculated field in a pivot table enables custom calculations using data from other fields, which is especially useful when built-in summary functions like Count, Sum, Average, Min, Max, etc., fall short. Calculated fields are also valuable for diverse data analysis scenarios, including percentage calculations, differences, and custom metrics.
To understand how to add and use calculated fields in pivot tables, let us take a simple example.
1. Let’s create a very basic pivot table first.
We are going to base our pivot table on the following dataset:
Note: The above dataset contains office supply sales data for a company (not real data, I made it up for this tutorial). And remember that we kept the dataset simple to make it easy for you to follow. Pivot tables are usually used to analyze much more complex datasets.
Note that the fields included are:
OrderDate: Date when the item was ordered
Region: Geographical region where the order was made
Rep: Name of the sales rep who processed the order
Item: Name of the item ordered/sold
Units: Number of units ordered/sold
UnitCost: Cost of one unit of the item
Total: Total sales amount
2. Let’s say we want to create a Pivot table that analyzes this data and displays:
The region-wise total sales.
The region-wise total sales after deducting a 5% tax amount on items sold.
To accomplish the above two tasks, we need to follow these steps:
Create the Pivot Table to display data by Region
Populate the Pivot Table with required rows, columns, and values.
Add a summary column that displays the total sales amount by Region
Add a calculated field that displays the total sales amount (by Region) after deducting a 5% tax amount.
It’s the fourth step (where we need to calculate total sales after deducting 5%) where we would have a need to insert a calculated field in the Pivot Table. Using that calculated field, we will be able to do this calculation.
Part2. How to Add Calculated Field to Pivot Table
In the previous section, we discussed what a calculated field is and why it's valuable. Now, let's delve into the specific steps for adding a calculated field to your pivot table in Microsoft Excel.
Suppose you have a Pivot Table as shown below and you want to calculate the profit margin for each retailer:
Here are the steps to add a Pivot Table Calculated Field:
Step 1: Select any cell in the Pivot Table.
Step 2: Go to Pivot Table Tools –> Analyze –> Calculations –> Fields, Items, & Sets.
Step 3: From the drop-down, select Calculated Field.
Step 4: In the Insert Calculated Filed dialog box:
Give it a name by entering it in the Name field.
In the Formula field, create the formula you want for the calculated field. Note that you can choose from the field names listed below it. In this case, the formula is ‘= Profit/ Sales’. You can either manually enter the field names or double click on the field name listed in the Fields box.
Step 5: Click on Add and close the dialog box.
As soon as you add the Calculated Field, it will appear as one of the fields in the PivotTable Fields list.
Now you can use this calculated field as any other Pivot Table field (note that you can not use Pivot Table Calculated Field as a report filter or slicer).
How to Modify or Delete a Pivot Table Calculated Field
To modify or delete a Pivot Table calculated field in Microsoft Excel, follow these steps:
Step 1: Select any cell in the Pivot Table.
Step 2: Go to Pivot Table Tools –> Analyze –> Calculations –> Fields, Items, & Sets.
Step 3: From the drop-down select Calculated Field.
Step 4: In the Name field, click on the drop-down arrow (small downward arrow at the end of the field).
Step 5: From the list, select the calculated field you want to delete or modify.
Step 6: Change the formula in case you want to modify it or click on Delete in case you want to delete it.
Part3. How to Use Excel for Free in WPS Office
WPS Office, developed by Kingsoft Office Software, is a versatile and powerful office suite known for its stable performance and contemporary design. It provides a wide range of tools for various office tasks, including text processing, table creation, slide production, graphic image editing, and basic database management. WPS Office has gained user trust for its reliability, particularly in handling Excel functions and data linkages. My personal experience using WPS Office has been highly positive, and it has proven to be a valuable alternative to other office software.
Main Features of WPS Office:
Text Processing: WPS Writer offers a comprehensive set of features for creating, editing, and formatting documents, making it a strong contender against other word processors.
Table Production: The spreadsheet application in WPS Office is a robust tool for managing and analyzing data. It supports complex calculations, chart creation, and offers a user-friendly interface.
Slide Production: WPS Presentation allows users to design professional presentations with a wide array of templates, animations, and multimedia support, making it ideal for business presentations.
Graphic Image Processing: WPS Office provides tools for editing and enhancing images, which is particularly useful for creating visually appealing documents and presentations.
Simple Database Processing: While not as advanced as dedicated database software, WPS Office offers basic features for managing simple databases, which can be handy for small-scale data management.
Advantages of Using WPS Office:
Cost-Effective: WPS Office offers a free version with most essential features, making it a budget-friendly choice for individuals and businesses.
Stable and Reliable: It is known for its stable operation and reliability, ensuring your work is secure and uninterrupted.
Compatibility: WPS Office seamlessly handles Microsoft Office formats, reducing compatibility issues when sharing files.
User-Friendly: Its modern and intuitive design caters to users of all levels, making it a user-friendly alternative.
Compared to Microsoft Office, WPS Office offers similar functionality, particularly for everyday office tasks. It excels in providing a free version with essential features, making it a cost-effective choice. While Microsoft Office remains an industry standard, WPS Office is more suitable for cost-conscious users who require powerful office tools without the need for an expensive subscription.
How to download WPS Office for free?
Step 1: Go to WPS and click “Free Download”.
Step 2: Click “Keep” if the following dialogue box pops up. Please be assured that it's safe to download our products.
Step 3: The installation will begin after you agree to the agreement and click “Install Now”.
WPS Office will open automatically when it's done.
After installation, you can launch WPS Office by clicking on its shortcut or by searching for it in your applications or programs menu.
FAQs
How do I add a specific field to a PivotTable in Excel?
To add a field to your PivotTable, select the field name checkbox in the PivotTables Fields pane.
Note: Selected fields are added to their default areas: non-numeric fields are added to Rows, date and time hierarchies are added to Columns, and numeric fields are added to Values.
To move a field from one area to another, drag the field to the target area.
2. What is the difference between calculated field and calculated item in PivotTable?
The key difference between calculated fields and calculated items is that:
Calculated Fields are formulas that can refer to other fields in the pivot table.
Calculated Items are formulas that can refer to other items within a specific pivot field.
Summary
This guide offers a step-by-step tutorial on adding a calculated field to a Pivot Table, with a focus on utilizing Excel for free in WPS Office. It explains how to create a Pivot Table, customize it, and incorporate a custom calculated field while highlighting the cost-effectiveness and feature-rich qualities of WPS Office as a viable alternative to Microsoft Excel.
