Grouping of dates in Excel’s PivotTables is fairly common and in the most recent versions of Excel, automatic. Many people don’t realise that you can perform other types of grouping in Excel.
Tag Archives: pivot table
Distinct Count in Excel
The Data Model to the rescue
Counting is the poor cousin to summing in Excel. Not many people count things, but everyone adds up things. There is a special sort of count that can be useful. A distinct count counts unique entries and is hard to do with a formula. If you have Excel 2013 or a later version you can use a PivotTable to perform a distinct count.
Updates to Pivot Tables
Time-saving improvements
If you have the latest Excel version or the subscription version, you may have noticed some refreshing improvements to PivotTables.
Related Tables and PivotTables
Don't Panic!
When you are building a PivotTable based on two related tables you may see unusual layouts that don’t make sense. Don’t worry, when you add values to the table all will be fixed.
PivotTable Combinations Trick
How to get a list of combinations
I did a recent post on using Power Query to create an all combinations list. I found another technique on chandoo.org to create that list using a PivotTable.
PivotTable Grouping Doesn’t Work
Rogue data the reason
Grouping is a powerful feature in PivotTable reports but sometimes Excel won’t let you apply grouping. There are a few reasons for this.
Creating a Unique Dynamic List in Excel
A Power Query technique
There are a couple of techniques to automate a unique list of items in Excel. I have covered them in previous blog posts (see links below). I thought I would describe how to use Power Query to create a dynamic unique list.
PivotTable Listing Macro
Create a list of all PivotTables in a file
If you inherit a file or you haven’t used a file for while, it can be useful to do an inventory of all the PivotTables. A macro can do all the work for you.
Getting a unique list in Excel
A PivotTable trick
I have blogged before about using the Advanced Filter technique to extract unique entries from a list. Well a PivotTable can do it it too.
Why Does My Pivot Table Default to COUNT?
And how to fix it
When you drag a value column to the value section of a pivot table, sometimes the default calculation is COUNT, not SUM. This is frustrating, but easily fixed.
Pivot Tables and Different Date Grouping
When you use date grouping (by months) in one Pivot Table report it affects other Pivot Table’s date reporting from the same data. There is a work around to allow you to have daily, monthly and quarterly Pivot Table reports.
The New Slicer in Excel 2013
Slicers are a graphic filtering tool added in Excel 2010. They allow you to filter Pivot Tables. Excel 2013 added a new slicer that makes filtering by dates a lot easier.
Format As Table in Excel – Part 1
Tables rule - see why
Excel 2007 updated a little used feature of Excel 2003 called Lists to create the functionality behind the Format As Table icon on the Home Ribbon tab.
Fixing Excel’s Pivot Table headings
Say goodbye to of Sum of
Pivot Tables are incredibly powerful and easy to use. Unfortunately their headings can include the terms “Sum of” or “Count of”. This is not always what you’d like to present to users. A macro to the rescue.
Slice and dice in Excel
Excel 2010 has Slicers
Excel 2010 introduced a new filter interface for Pivot Tables called Slicers. This feature improved the filtering process for Pivot Tables and made filtered selections more visible.
A Classic Pivot Table
Old School Pivot Table
Pivot Tables changed in Excel 2007 and 2010. The default setting doesn’t let you drag and drop the field names on the Pivot Table itself. You could do that in Excel 2003 and you can still do it in the newer versions. All you need to do is change a setting.