Last week, someone asked me if there is a menu command for moving the fields in a pivot table layout. For example, if the Region field is in the Columns area, can you use a command to move it to the Rows area, or do you have to drag it with the mouse, in the PivotTable Field List?
Category: Pivot Table
Pivot Table Source Data Articles
The key to success with Excel pivot tables is having good source data. I’ve written many articles with pivot table data source tips, and this list will help you find the information that you need. Some of the articles are on my Contextures website, and others are on my Pivot Table blog.
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.
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.
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!
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!
Create Custom Reports From Pivot Tables
If you’re a pivot table fan, like I am, you know how quick and easy it is to summarize a massive amount of data, with just a few clicks. You can show sums, counts, averages, and other totals, without using any fancy formulas.
In the screen shot below, the pivot table is summarizing income and expenses, and there is a Slicer at the top left, for quick filtering.
Formatting Restrictions
As wonderful as pivot tables are, they do have some limitations, and you might not be able to get the layout exactly the way you need it. In the screen shot below, you can see a P & L statement, based on the same data as the previous pivot table.
You’d never be able to get the pivot table in exactly this layout, with its blank rows, and formatting, and there are additional formulas at the right side too.
Create a Custom Report
Roger Govier is a pivot table fan too, and he has created a solution for building his own custom reports, like the P & L statement shown above. Roger creates a pivot table first, and then he uses the GetPivotData function, to pull specific data into his custom layout.
In the formulas, Roger uses cell references to the row and column headings, so he just has to create one GetPivotData formula, then copy it into all the data cells of the custom layout.
Another smart trick is that Roger adds headings at the top of the sheet too, and refers to those cells, instead of hard coding the field names into the formulas.
Use INDEX and MATCH Instead
If you don’t want to use the GetPivotData function, Roger also show how you can create named ranges, based on the pivot table. Then, use the INDEX and MATCH functions to extract the applicable data, and build the custom report.
He even has sample code that you can use, to automate building the named ranges.
Download the Sample File
To see the detailed instructions, and download Roger’s sample file (with or without the VBA code), please visit the Build Custom Reports With GetPivotData page, on my Contextures website.
________________
Excel Report Diagnostic Display
Did you ever print and distribute pivot table reports in Excel, only to discover – too late – that they weren’t showing the correct information? To help save you from wasted time and paper, and possible embarrassment, AlexJ is sharing his Report Diagnostic Display technique today.
You might remember some of his other handy tips, such as
Before you read the rest of this article, remember that the 20% discount ends today, Oct. 16th, for Mynda Treacy’s Excel Dashboard course. To get a bonus of one of my products, enter DebraD in the coupon code box.
Unique Count in Excel Pivot Table
A pivot table can sum and count and average, and do several other functions. However, a normal Excel pivot table doesn’t have a built-in Unique Count (Distinct Count) function.
Fortunately, this page shows 4 different ways that you can get a unique count in a pivot table – for any version of Excel pivot tables.
Change Pivot Table Filters With Drop Down Cell
Happy Thursday! I’ve got two news items today, and you can read the details below.
- a new sample file on my Contextures website
- a Microsoft Consumer Camp event in the Toronto area
Continue reading “Change Pivot Table Filters With Drop Down Cell”