by contextures.com
Instead of manually updating all the pivot tables in your workbook, use a macro to do the job quickly. This macro tries to refresh all the pivot caches in the active workbook. If the macro can’t refresh a pivot cache, it will show you a message with the pivot cache index number, and the error description.
Continue reading “Refresh All Pivot Caches with Excel Macro”
Click a pivot table Slicer, to quickly show and hide groups of values. This value group slicer technique saves time and space, when there are lots of numeric fields in your source data table.
Usually, it’s best to set up your Excel file with one pivot table per sheet, so you don’t have to worry about them overlapping. However, if you need to see them side by side, here’s how you can create and filter two pivot tables on an Excel sheet, based on the same source data.
Continue reading “Create and Filter Two Pivot Tables on Excel Sheet”
If you create two or more pivot tables from the same source data (pivot cache), you can connect them to the same Excel Slicers. That will make it easy to filter all the connected pivot tables at the same time.
Continue reading “Connect Excel Slicers to Multiple Pivot Tables”
In Excel 2010 and later versions, you can use the % Running Total calculation, to show the current running total amount, divided by the grand total. This video shows how to analyze sales, from month to month, with the % Running Total In calculation in an Excel pivot table. Continue reading “Analyze Sales with Excel Pivot Table Running Total Percent”
Does your business have a fiscal year that starts in a month other than January? In a pivot table, there’s no built-in way to group or sort the dates by their fiscal month number. See how to calculate those numbers in the source data, and then sort the data by fiscal month in a pivot table, with the month name included. Continue reading “Sort by Fiscal Month in a Pivot Table”
After you set up a pivot table, you can use the plus and minus buttons to show or hide the pivot table details. For example, hide details for the East region, so only its totals are showing, and leave all the West region details visible. Do you know the pivot table shortcut to expand and collapse the details? Keep reading, to see a quick and easy way to do this. Continue reading “Excel Pivot Table Shortcut to Expand and Collapse”
How many times have you heard someone say, “Pivot makes me think of Ross from Friends”? If you make lots of pivot tables in Excel, you’re probably tired of hearing that from your co-workers, right? Well, this little Easter egg might brighten your day!
Continue reading “Pivot Makes Me Think of Ross From Friends”
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?
