Excel Tips


Show Months with Zero Sales »

February 1, 2023

I built a pivot table to show sales by month for one customer. For the large customers, I get all 12 months.


Show Customer Account & Name »

January 31, 2023

My source data comes from the sales reps. They don’t use consistent names, so I end up with multiple customer names for one account number.


Calculations Outside of Pivot Tables »

January 30, 2023

I need to add a calculation in the grid outside of the pivot table that points to cells in the pivot table. Whenever I copy the formula, I get the exact same result!


Group Text Fields to Build Territories »

January 27, 2023

As shown in the previous topic, adding calculated items causes the totals to be wrong. I want to test grouping offices into territories. How can I do it?


Add a Calculated Item to Group Items in a Pivot Table »

January 26, 2023

I’m working with the small data set shown here.


Calculated Fields in a Pivot Table »

January 25, 2023

I need to include in a pivot table a calculation that is not in my underlying data. My data includes quantity sold, revenue, and cost. I would like to report gross profit and average price.


Pivot Ranks Don’t Match RANK() »

January 24, 2023

I set up a pivot table and showed the values as a rank, using Rank Largest to Smallest. Why is the fourth product assigned a rank of #3?


Better Calculations with Show Values As »

January 23, 2023

Excel offers eleven functions on the Summarize Value By tab of the Value Field Settings dialog. Those are not the good ones. The good ones are on the Show Values As tab. Ninety percent of the time that I change the calculation, I am using one of the relatively hidden Show Values As calculations.


Create a Report That Shows Count, Min, Max, Average, Etc. »

January 20, 2023

Most of the Pivot Table examples shown thus far are for summing revenue. What if I need to find out the average sale by customer or the smallest sale?