Count Unique in Excel Pivot Table

In a previous article,  Roger Govier showed us a couple of different ways to get a Unique count with a Pivot Table.

  • Create a Pivot of a Pivot Tables results (fast)
  • Add a calculated column to the source data (much slower)

Now Roger has added another technique that you can use, in Excel 2013 and later. I’ll hand things over to Roger, so he can describe the steps.

Continue reading “Count Unique in Excel Pivot Table”

Pivot Table Running Total Percent

At the beginning of every month, I download the previous month’s statistics for my web site, to see which pages and files were the most popular. After the data is imported to Excel, I use pivot tables to get a quick overview of the activity.

If a page is getting lots of hits, I might add a new section to it, to make the content even better. And, seeing which sample files get downloaded most frequently gives me ideas for creating new examples.

I’m sure you do something similar for your month end, to see how things are going. And the raw statistics are interesting, but with a pivot table (or 12), you can dig even deeper into the results.

Continue reading “Pivot Table Running Total Percent”

Find and Fix Pivot Table Source Data

After you create a pivot table, you might add or change records in the source data. Sometimes the pivot table does not update correctly, to show the new data. Or if you’ve inherited the pivot table file from someone else, you might not even be able to find the source data, in a large workbook.

To help with these problems, I’ve added a new page on my Contextures site, with tips for finding and fixing the source data for a pivot table.

Continue reading “Find and Fix Pivot Table Source Data”

Hide Pivot Table Subtotals

Sometimes the Excel macro recorder creates code that gets you off to a good start. Other times, it’s not so helpful.

This week, I was working on a pivot table macro, and wanted to turn off all the Row Field subtotals. Fortunately, there is a handy command for this on the Ribbon, on the Design tab, under PivotTable Tools. One click, and the subtotals disappear, or reappear.

Let’s get the code for that, which I’m sure will be equally clean and simple. Ha!


Continue reading “Hide Pivot Table Subtotals”

Add a Counter Field to Excel Table

On my pivot table blog yesterday, I wrote an article about using counts in a calculated field. A helpful tip is buried in the 8-minute video that shows how to create the calculation, so I’m posting the tip separately here.

This tip can help prevent data entry problems, if you’ve set up a named Excel table for a client or co-workers to use, or even if you’re the only one using the table. Excel will automatically enter the data for you!

Continue reading “Add a Counter Field to Excel Table”