by contextures.com
In some Excel pivot tables, you might want to prevent selection of the “(All)” option in a Report Filter. There’s no built-in option to do that. Instead, you can use this macro to stop people from choosing the ALL option.
Continue reading “Prevent Selection of ALL in Pivot Table Report Filter”
When you try to refresh an Excel pivot table, you might see an error message that warns you about an overlap problem: “A PivotTable report cannot overlap another PivotTable report.” To help you find the pivot table that is causing the problem, use this macro. It lists all the pivot tables where there are two or more on the same worksheet.
Continue reading “How to Find Pivot Table Refresh Overlap Problem”
When you create a new pivot chart in Excel, its number formatting is copied from the pivot table that it’s based on. See how to change the pivot chart number formatting, without affecting the pivot table’s number formatting. If you have several pivot charts to change, use a macro to make the job easier.
Continue reading “How to Use Different Number Format in Excel Pivot Chart”
Most pivot tables show numbers, so you can see a detailed summary of the source data. To add a visual element to the pivot table, add data bars that are similar to a bar chart. This works well in a simple pivot table, with only one or two columns of data.
Continue reading “Show Sales Amounts as Data Bars in Excel Pivot Table”
Usually, it’s easy to sort an Excel pivot table – just select one of the sort options – A to Z or Z to A – in the heading drop downs. However, you might run into a pivot table sorting problem, where the wrong item stays at the top. See why that happens, and how you can fix the pivot sort problem.
Continue reading “Pivot Table Sorting Problem Wrong Item at Top”
This short video shows how to build an Excel pivot table that calculates a correct count, even if there are blank cells in the source data table.
After you add number fields to a pivot table’s Values area, you can format those numbers. To find out which number formats are used most often, I did a 3-question survey. Thanks to everyone who replied, and these charts show the results of that survey, compiled on two different dates.
Continue reading “Pivot Table Number Format Used Most Often”
There are sample Excel files on my Contextures site, with data you can use for testing, or demos. The original sample file has sales data for a fictional office supply company. I’ve added more files recently, so see if they’ll be helpful to you too.
Continue reading “Excel Sample Data for Pivot Table Testing”
A simple data change can cause a strange pivot table refresh error, if you added the data to the Data Model. The long error message starts with, “We couldn’t get data from the Data Model. Here’s the error message we got”. It also mentions “function PCMinorObjectCollection”, which I’d never heard of before. See what causes this problem, and how to fix it.
Continue reading “Excel Data Model Pivot Table Refresh Error”
When you create a pivot table in Excel, it includes all the source data, even if some rows were hidden by filtering. However, if you’re using a version of Excel that has the new Spill (dynamic) functions, you can try this technique, to create a pivot table from filtered list visible rows only
