Often people perform calculations off to the right of Pivot Tables to calculate percentages.
In this short video I show you those calculations can be done inside the Pivot Table itself.
The solution is not intuitive, but it is easy.
This example builds upon the previous One Minute to Excel post.
Note sure why, but Pivot Tables are often seen a “hard” or “advanced”.
In the short video we see how easy they are.
Oops – I go over my one minute time limit by a few seconds because I format the Pivot Table as well.
When you create a top 5 sorted report with a PivotTable, the Pivot Chart isn’t always what you expect, there is an easy solution.
Switching between rows and columns in a normal chart is easy via a button on the Design tab. But how do you do it with a PivotChart?
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.
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.
If you have the latest Excel version or the subscription version, you may have noticed some refreshing improvements to PivotTables.
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.
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.
Grouping is a powerful feature in PivotTable reports but sometimes Excel won’t let you apply grouping. There are a few reasons for this.
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.
If you inherit a file or you haven’t used a file for a while, it can be useful to do an inventory of all the PivotTables. A macro can do all the work for you.
I have blogged before about using the Advanced Filter technique to extract unique entries from a list. Well a PivotTable can do it too.
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.
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.
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.
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.
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.
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.
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.