by contextures.com
Excel pivot tables don’t show hyperlinks, but there’s a way around that problem! In the video below, you’ll see how to create fake clickable hyperlinks in a pivot table, by using a few lines of Excel VBA code, and a bit of formatting.
Continue reading “Add Clickable Hyperlinks-Excel Pivot Table-Macro Code”
If Excel error messages appear when you try to refresh a pivot table, there are macros on my Contextures site that can help you troubleshoot those problems. I’ve just added another macro on that page, to show a pop-up message with details, if a specific pivot table is behaving badly.
Continue reading “Troubleshoot Pivot Table Problems – Pop-up Info Message”
While you’re working with pivot tables, in complex Excel workbooks, it’s a good habit to make backup copies frequently. With my free Excel backup tool, you can make backups while you work, just by clicking a button.
Continue reading “Back Up Pivot Table Workbooks With Free Excel Tool”
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”
Someone asked me if it’s possible to prevent one item in a pivot table from being expanded, to show its details. They wanted all the other items to be expandable though. There’s nothing built in that will allow this, so I had to use programming instead. This macro keeps one pivot item collapsed in a pivot table, but doesn’t affect any other pivot items. Modify the code, to use the pivot field and pivot item names in your pivot table. Continue reading “Excel Macro Keeps One Pivot Item Collapsed”
Why would a pivot table show the wrong information? In the example below, a couple of the product names are wrong. If you run into a similar pivot table problem, how can you fix pivot item captions in Excel, so they display the correct information again?
If you have a big Excel file, with lots of pivot tables, you might want to document everything that’s in the workbook. Maybe you inherited the workbook, and you’re not sure what’s in it. Or perhaps it’s a file that you only use occasionally, and it’s hard to remember what it contains. To get the details quickly, use the code shown below. This Excel macro lists all pivot tables and pivot fields in the active workbook.
Continue reading “Excel Macro Lists All Pivot Tables and Pivot Fields”
If you want to build a flexible pivot table, you might need to rearrange your source data, before you start. For example, if there is a separate column for each month’s sales, you should “unpivot” the data, to get all the amounts in one column. The good news is that you unpivot Excel data with Get & Transform, and your original data isn’t changed. If you don’t have G&T, there’s a macro that you can use instead
When you’re setting up a pivot table, you can use the Ribbon commands to change its appearance, and the source data, and several other settings. There’s another place where you can control the pivot table’s behaviour too – in the Pivot Table Options. See how to access that dialog box, and run this pivot table options list macro to see the current settings, for a few of the key options.
