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.
Tag Archives: pivot table
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.