I. Intro
Calculated fields are an advanced pivot table function in Sheets that allows you to customize how data is collated and calculated to create specialized fields. They offer powerful ways to analyze and process data according to your business needs.
Calculated fields simplify calculation processes. You can easily reuse previous calculation methods by adjusting the pivot table structure, eliminating the need for rewriting formulas.
II. Steps
Create and manage calculated fields
Calculated fields can be found in the Fields tab in the pivot table editor.
- Click Add Calculated Field at the bottom to create a new field.
- Click the Down arrow icon to the right of a calculated field to edit or delete it.
250px|700px|reset
Selected calculated fields will appear under Values, where you can:
- Click the Down arrow icon to the right of a field to remove or rename it.
- Click a label below the field to specify how the value is displayed.
250px|700px|reset
Edit a calculated field
When adding or editing a field, you can open the field editor, which has the following settings:
- 1. Field name: Enter a field name.
- 2. Fields: All fields in the source data are shown here, and can be referenced in formulas.
- 3. Formula: Enter a custom formula here (see the next section for details).
- 4. Select how the field is summarized.
- 5. Click the + icon to add a calculated field.
- 6. Calculated fields: Hover over a field to bring up the Copy and Delete icons.
250px|700px|reset
Edit a formula
250px|700px|reset
- Calculated fields support a majority of common Sheets functions, except:
- The calculation object can only be a source data field in the current pivot table, wrapped in single quotes ('). It can't be another calculated field, nor can cell data be referenced like in standard formulas.
- Some functions are not supported, such as RAND, NOW, TODAY, VLOOKUP, and IMPORTRANGE. If you enter an unsupported function into the editor, you will get a corresponding prompt.
- Calculated fields support two types of formula summary:
- SUM: Adds up the fields referenced in the formula according to the row area dimensions in the pivot table. The syntax of the SUM() formula can be omitted. Entering ='Field 1' in SUM mode is equivalent to entering =SUM('Field 1') in custom formula mode.
- Custom formula: The calculation object is all the data points in the fields named in the formula. It can be aggregated in any way, such as SUM, COUNT, or AVERAGE.
III. Examples
We'll use an online advertising example to demonstrate how calculated fields can be used. The source data contains the following fields:
Date | Ad ID | Impressions | Clicks | Installs | Cost |
6/4/2023 | Ad 22 | 198 | 2 | 0 | 43.6 |
6/11/2023 | Ad 23 | 203 | 2 | 1 | 1.4 |
6/14/2023 | Ad 30 | 301 | 1 | 0 | 0.1 |
...... | ...... | ...... | ...... | ...... | ...... |
Example | Screenshot |
Calculate common metrics After fixing common metrics for online advertising using calculated fields, you can view data such as the click-through rate (CTR), cost per click (CPC), and cost per action (CPA), change how the data is summarized, and view business data holistically. 250px|700px|reset | 250px|700px|reset |
Filter data After the criteria for eliminating advertising items have been added to the calculated field, you can quickly filter for items that are worth keeping. For example, the formula in the image below is filtering for items with more than 1,200 downloads and a CPA no greater than $2. 250px|700px|reset | 250px|700px|reset |
Monitor daily ad performance By calculating the proportion of items with a high download rate, you can see how many items are trending every day. 250px|700px|reset | 250px|700px|reset |
Convert data to create charts To see downloads by day of the week, you can convert the Date column to a day of the week using the WEEKDAY formula, then insert a column chart to visualize the difference in the number of downloads from Monday to Sunday. 250px|700px|reset | 250px|700px|reset |
IV. FAQs