by contextures.com
In a pivot table you might want to see a distinct count (unique count) for some of the data, instead of an overall count.
For example, if pens and binders are sold in different colours, how many unique colours were sold for each product? Here’s how to show a distinct count in Data Model pivot tables.
Continue reading “Show Distinct Count in Data Model Pivot Tables”
When you build a pivot table, Excel automatically creates a numbered name for it, such as PivotTable1. You can leave that automatic name as it is, or change an Excel pivot table name to something more meaningful, such as “SalesPivot”.
Pivot tables are great at summarizing data, but sometimes your data has gaps, and the pivot tables can’t report on numbers that don’t exist. In today’s example, we have health and safety data for the first quarter. Two departments did not have data for that period, and are not listed in the data, but we’d like the report to show zero incidents. Keep reading, to see how to count missing pivot table data as zero.
When you create a pivot table in Excel, a pivot cache is automatically created. The pivot cache is a special memory area where the pivot table records are saved. If there are multiple pivot tables in a workbook, they might use the same pivot cache, or different pivot caches. Use these macros to create a new pivot table from an existing pivot cache, and choose a specific cache – by cache number or pivot table location.
If you’re tracking retail sales year over year, it’s not too helpful to compare sales by calendar date. The dates will fall on different weekdays each year, so slow weekdays will be compared to busy days. To get a better comparison, add a few formulas to the sales data, then use a pivot table to line up the weekdays.
Continue reading “Pivot Table Compares Weekdays in Fiscal Year”
Pivot tables are a quick way to summarize your data, and they have another advantage too. After you build the pivot table, it’s easy to change pivot table layout, to get a different type of summary. Watch the video below, and get the free workbook.
If you plan to build a pivot table, check the source data first, to make sure you have it set up correctly. If there is a heading for each month’s sales, instead of just one column where all the amounts are stored, that won’t work well in a pivot table. You need to “unpivot” your data first, and you can unpivot Excel data with a macro.
A frequent question about pivot tables is how to create one from data on different worksheets, or even in different file. Last week, I updated my page on this topic, which shows several ways you can accomplish this goal.
Thanks to everyone who entered the Pivot Table Training giveaway, for a chance to win an amazing online course from John Michaloudis, at My Excel Online Training.
You shared your biggest pivot table fears, and you can see the voting result below. In the upcoming weeks, I’ll cover some of these topics, and all of them are covered in comprehensive John’s online pivot table course.
The winners are listed below, and each person has been sent an email, with instructions for claiming their prize. Winners will have 24 hours to respond, by adding a comment in this blog post, and if they don’t claim the prize, another random name will be selected.
We’re having a pivot table training giveaway, and there are only a few days left to enter – the deadline is Sunday, November 9th, 2014, at 11:59 PM (Eastern Time)
If you enter, you could win one of the 3 Xtreme Pivot Table Courses ($199 value), thanks to John Michaloudis, from My Excel Online Training. This amazing course has:
