Prepare Your Source Table
Your source data should contain at least three columns: a date field, a count of orders, and a count of defective orders. Keep the table tidy—no blank rows or merged cells—and give the table a name (e.g., OrdersTable) so it can be referenced easily.
Build the Pivot Table
Insert a pivot table based on OrdersTable. Drag the Date field to Rows, the Orders count to Values (set to Sum), and the Defective count to Values (also set to Sum). The pivot will now show two numeric columns per day.
Insert a Calculated Field
With the pivot table selected, go to PivotTable Analyze → Fields, Items & Sets → Calculated Field. Name the field "% Defective" and use the formula:
=Defective Orders / Orders
Replace the field names with the exact names that appear in the Values list. Click Add, then OK. The new column will appear in the pivot and show a decimal value.
Calculated Field Formula:
="Defective Orders" / "Orders"
(Use the exact field names shown in the pivot.)Format as a Percentage
Right‑click the new column, choose Value Field Settings, then Number Format. Select Percentage and set the desired decimal places. The column now displays the defect rate as a percent, making it ready for a p‑chart or other visual analysis.
Common Pitfalls and Fixes
If the calculated field shows a #DIV/0! error, ensure that the Orders column never contains zero. If the values look wrong, double‑check that the field names in the formula match exactly what appears in the pivot. Finally, remember that the calculated field uses the aggregated totals, not the raw row values.
Takeaway: A single calculated field lets you turn raw order counts into a ready‑to‑chart defect percentage inside the pivot table.
People also ask
Can I use a calculated field to show a running total?
Yes—create a field with the formula =SUM(Orders) and then set it to show as a running total in the Value Field Settings.
Will the calculated field update automatically when I refresh the pivot?
Yes, as long as the source table is refreshed, the calculated field recalculates on every pivot refresh.
Inspired by a public discussion on Stack Overflow. This article is an original explanation for learners.