Category Archives: Calculations

Distinct Count in Excel Pivot Table

When you create a pivot table to summarize data, Excel automatically creates sums and counts for the fields that you add to the Values area. In addition, you might want to see a distinct count (unique count) for some fields, … Continue reading

Posted in Calculations | 4 Comments

Give Your Excel Pivot Table a Makeover

When you create a pivot table, do you just slap it together, and hope for the best, or do you spend time experimenting with different calculations, layouts and field arrangements? Yes, it’s easy to fall into a rut, and create … Continue reading

Posted in Calculations | Leave a comment

Create Calculated Field With a Count

In addition to using fields from the source data, you can create calculated fields in a pivot table, to add your own formulas. For example, add a field that multiplies the total sales by 3%, to show a Bonus amount. … Continue reading

Posted in Calculations | Leave a comment

Create a Calculated Field in a Pivot Table

One of the top pivot table fears in our survey was “How do you insert a calculated field?” And no wonder it was near the top of the list – pivot table formulas can be very confusing! So, let’s take … Continue reading

Posted in Calculations | 2 Comments

Hide Zero Items in Pivot Table

If you create a calculated item, extra items might appear in the pivot table, with zero amounts in some rows. In the example shown below, a calculated item was created in the Category field. Because of that, all the cities … Continue reading

Posted in Calculations | Leave a comment

Show Fiscal Year to Date Totals

Last week, you saw how to calculate the fiscal year and month, by adding formulas in a pivot table’s source data. The formulas referred to a named cell – FYStart – where you enter the start month of the fiscal … Continue reading

Posted in Calculations | 1 Comment

Calculated Field vs Calculated Item

After you create a pivot table, you can enhance the results by writing your own formulas, to create calculated fields and calculated items. e When you’re getting started with formulas, it might not be clear what the difference is. When … Continue reading

Posted in Calculations | 8 Comments

Accidentally Create Calculated Items

In a pivot table, you can create calculated fields and calculated items, by inserting following a few steps, to insert formulas. There are detailed instructions on my web site How to Create a Calculated Field Calculated Items in Pivot Tables … Continue reading

Posted in Calculations | 3 Comments

Refer to Value Cells in GetPivotData Formula

When you type an equal sign, then click on a pivot table value cell, a GetPivotData formula is automatically created. We’ll see how to make those formula more flexible, so you can quickly change the results. For example, in the … Continue reading

Posted in Calculations | 12 Comments

Show Percent Difference in Pivot Table Amounts

With one of the built-in custom calculations in a pivot table, you can quickly show the percent difference between two amounts. In this example, forecast amounts have been entered for each product, for each month. When they become available, the … Continue reading

Posted in Calculations | 1 Comment