Free Practice WorkbookWith Pivot Tables I will show you a cool trick on using what we call as flags. For example, we have a defect flag that marks if on that day we had a defect, then we can immediately summarize that data using Pivot Tables. Our main methodology will be using the Product in Pivot Table. What this does is it will multiply all the values together.
Key Takeaways
-
Calculate Multiplicative Totals with Data Model – Excel Pivot Tables don’t natively support the PRODUCT function, but you can achieve it using Power Pivot or the Data Model.
-
Use Calculated Fields for Workarounds – You can use calculated fields and helper columns to simulate product calculations where needed.
-
Power Pivot Enables DAX Functions – By enabling the Data Model, you can use the DAX
PRODUCTXfunction for accurate multiplicative summaries. -
Standard Pivot Tables Only Offer Additive Measures – Out of the box, Pivot Tables support functions like SUM, COUNT, and AVERAGE—not PRODUCT.
-
Ideal for Multiplicative Metrics – Use when you need to calculate things like compounded growth or product of rates across categories.
Table of Contents
Our Data Set
Here is our data set. Notice that I have added a Defects column. If there is a defect that day, we simply mark it as 1. This will be crucial once we use the Product function later.
Product in Excel Pivot Tables
STEP 1: This is our Pivot Table setup. Drag Defects to the Values area:
STEP 2: It will default as Sum of DEFECTS. Click on the arrow and select Value Field Settings
Select Product and click OK.
STEP 3: Now what Excel has done is for that specific duration, it has multiplied all the Defect values. So if any row for that specific duration has a 1, then the result in this Pivot Table is a 1 as well.
We love the ones that show zero because that means there are no defects! For example, would be for Bottles during the month of September 2014. Double click on it to see more details!
You can see that in the data breakdown for September 2014, there are no defects! This is why the Product result is 0.
Now try double-clicking on Ice Cubes during the month of March 2013:
You can see it has defects, so which is why the Product result when you multiply them together is 1.
Frequently Asked Questions
Can I directly use the PRODUCT function in a regular Pivot Table?
No, the standard Pivot Table does not include PRODUCT as a built-in aggregate function.
How can I calculate a product in a Pivot Table?
You can simulate it using calculated columns or by adding your data to the Data Model and using Power Pivot with DAX functions like PRODUCTX.
What is PRODUCTX in Power Pivot?PRODUCTX is a DAX function that multiplies values in a column for each row of a table and returns the total product, ideal for use in Power Pivot.
Do I need to enable anything to use PRODUCTX?
Yes, you must load your data into the Data Model and use Power Pivot to access DAX functions like PRODUCTX.
What are typical use cases for calculating products in Pivot Tables?
Common examples include calculating compounded returns, multiplying quantity and unit rate across filters, or aggregating multiplicative KPIs by category.
Bryan
Bryan Hong is an IT Software Developer for more than 10 years and has the following certifications: Microsoft Certified Professional Developer (MCPD): Web Developer, Microsoft Certified Technology Specialist (MCTS): Windows Applications, Microsoft Certified Systems Engineer (MCSE) and Microsoft Certified Systems Administrator (MCSA).
He is also an Amazon #1 bestselling author of 4 Microsoft Excel books and a teacher of Microsoft Excel & Office at the MyExecelOnline Academy Online Course.
30 Days - Full Access 
