A calculated field is a custom formula that lives inside your PivotTable and works on the other fields in it. To add one, select the PivotTable, go to PivotTable Analyze > Fields, Items, & Sets > Calculated Field, type a name and a formula such as =Sales*15%, and select Add. This guide covers that process, then the limits that trip people up, the calculated item, and grouping dates by week, month or year.
A PivotTable summarizes your data by adding it up, counting it or averaging it. Sometimes the number you need is not in the source data. You have Revenue and Units, but you want revenue per unit. You have Sales, but you want a 15% commission. You have Sales and Cost, but you want Profit.
You could add a helper column to the source table. A calculated field is the alternative: the formula lives in the PivotTable, appears in the Field List like any other field, and can be dragged into the Values area. Microsoft describes the difference this way: a calculated field uses data from other PivotTable fields, while a calculated item works with items inside a single field.
The formula can use the names of fields, much like a worksheet formula uses cell references. A simple example from Microsoft's documentation is =Sales*15%.
The path is the same in recent Windows versions of Excel (Microsoft's documentation covers Microsoft 365 and Excel 2024, 2021, 2019 and 2016).
If you have never built a PivotTable, start with the basics first. Our lesson on PivotTable fundamentals walks through the layout, and a thirty-second PivotTable summary shows how quickly one can be built.
Here are formulas people use most. Field names are examples, so swap in the ones from your own data.
| Goal | Name | Formula |
| Profit | Profit | =Sales - Cost |
| Commission at 15% | Commission | =Sales*15% |
| Revenue per unit | AvgPrice | =Revenue/Units |
| Margin percentage | Margin | =(Sales - Cost)/Sales |
| Response rate | ResponseRate | =Responded/Tickets |
| Bonus on a total | RepBonus | =Total*3% |
For the margin and response-rate formulas, format the result as a percentage. Our guide to Excel percentage formulas covers the number formats and the common mistakes.
Yes, many people do. A typical case is paying a commission only when a status says so. You can write something like =IF(Status="Commission", Sales*15%, 0). There is a catch: the calculation runs on the summed data for each cell of the PivotTable, so a text field like Status is evaluated at the level of the whole group, not row by row. If the logic has to run on every record, add a helper column to the source data and summarize that instead. To practice the IF logic itself, try our IF function examples and exercises.
Open PivotTable Analyze > Fields, Items, & Sets > Calculated Field again. In the Name box, pick the field from the dropdown. Change the formula and select Modify. To remove it, choose Delete in the same dialog. This is the documented way to edit one.
Two habits save headaches. First, give each field a name that says what it measures, because it sits next to your source fields in the list. Second, check the formula after any change to the source column names, since the formula refers to fields by name.
Most frustration with calculated fields comes from one fact: the formula works on summed values, not on each row. Microsoft's documentation states the main restrictions: calculated fields cannot use cell references or defined names, cannot refer to totals, and cannot be created in OLAP-based reports.
See the effect with an example. Say you have three orders:
| Order | Revenue | Units |
| A | 100 | 10 |
| B | 300 | 20 |
| C | 50 | 5 |
Some practical consequences:
Before you write a formula, check whether the calculation is already built in. Right-click a value in the PivotTable, choose Show Values As, and pick from options such as percent of grand total, difference from, running total and rank. Microsoft notes that you can use Show Values As instead of writing your own calculated fields, and lists Difference From, Running Total and Rank among the custom calculations.
A quick rule for choosing:
| You want | Use |
| Share of the total, running total, rank, change from last period | Show Values As |
| A new number built from two or more fields (profit, price per unit, bonus) | Calculated field |
| A new row or column that combines items within one field | Calculated item |
| A result that depends on filters and context | A measure in the Data Model |
People searching for the calculated field often land on the calculated item, because both sit under the same menu. The difference matters. A calculated field creates a value from other fields. A calculated item creates a new item from items in the same field. If your Region field has East, West and Central, a calculated item could be Coastal = East + West.
To create one, per Microsoft's steps:
Date grouping is the other half of what readers ask about, and it is often what makes a PivotTable useful. A column of daily dates becomes months, quarters or years in a few clicks.
Microsoft's procedure for Windows is: right-click a value in the PivotTable, select Group, set Starting at and Ending at if needed, choose a period under By, and select OK. The guide applies to Microsoft 365, Mac, and Excel 2024, 2021, 2019 and 2016. Excel's date grouping supports seconds, minutes, hours, days, months, quarters and years.
To group by month, select Months. If your data spans more than one year, also select Years. Otherwise January 2025 and January 2026 are combined into one "Jan" row, which is rarely what you want.
Week is not on the list of periods. The workaround is to group by Days and set Number of days to 7. According to Microsoft Learn, when day grouping is selected, the By value specifies the number of days. Choose your Starting at date carefully, because the first week begins on that date. If you want weeks to start on Monday, enter a Monday. Deselect any other period in the dialog, or the 7-day grouping will not be available.
Two causes cover most cases. A Microsoft Q&A answer points out that grouping is unavailable when the source values are text that only look like dates, and that an oversized source range with empty cells can also break it. The fixes:
Imagine a sales sheet with columns Date, Region, Rep, Revenue and Cost. You want monthly profit and margin by region.
> The calculated field is a good example of why we built LogicExcel around practice instead of watching. Reading the steps takes a minute. Understanding why a ratio in the grand total is not the average of the rows only sinks in when you type the formula, see the number, and get it wrong once.
>
> That is the moment our exercises are designed for. You enter the formula in an Excel-like environment, get instant feedback, and see an explanation of the mistake. No account, no installation, so nothing sits between you and the repetition that builds muscle memory.
>
> Our advice to analysts, managers and career changers: learn the three tools together. Show Values As for quick comparisons, calculated fields for your own formulas, and clean date columns for grouping. Together they cover most of the reports people are asked to produce.
>
> Thomas Houza
Reading about a calculated field is the easy part. Building one, breaking it and fixing it is what makes it stick. LogicExcel offers 95 free Excel exercises with instant feedback, no sign-up and nothing to install. If you want a structured path from the basics to PivotTables and beyond, look at our Excel I to IV courses.
Preparing for a job test where PivotTables may come up? Try the free Excel test for interviews and assessments.
Click inside the PivotTable and open PivotTable Analyze > Fields, Items, & Sets > Calculated Field. Type a name, enter a formula using field names, such as =Sales - Cost, then select Add and OK. The new field appears in the Field List, ready to drag into Values.
Open the same Calculated Field dialog and choose the field name from the Name dropdown. The formula appears in the Formula box. Edit it and select Modify, or select Delete to remove it. Per Microsoft's documentation, this is the way to view and change a calculated field.
A calculated field builds a new value from other fields, for example Profit from Sales and Cost. A calculated item builds a new item from existing items in one field, for example a Coastal region from East and West. Ungroup the field before adding a calculated item.
Usually the dates are stored as text, or the source range includes empty cells. Convert the text to real dates, make sure the date column has no blanks, or turn the data into an Excel Table, then refresh. Weekly grouping needs the Days option with 7 days.
No. Calculated fields cannot use cell references or defined names. Type the value into the formula, or add it as a column in the source data so the PivotTable can use it as a field.
Guides people read next.
Learn how to create a pivot chart in Excel on Windows and Mac, filter it, change fields, avoid common limits and build one for a dashboard.
14 min
Sort an Excel pivot table by value, largest to smallest, by a specific field, or in a custom order, and fix the cases where the sort won't hold.
13 minCreate a drop down list in Excel with Data Validation: typed items, cell ranges, growing tables and dependent lists, with Windows, Mac and web steps.
24 min