by contextures.com
Pivot tables let you summarize thousands of rows of data, with just a few clicks. If you want to focus on a specific part of that summarized data, here are a couple of pivot table filter tricks.
Continue reading “Quick Pivot Table Filter Trick: Hide or Show Items”
The Excel Pivot Table Field List helps you build and modify your pivot tables quickly. But you don’t have to stick with its default settings! See how to change the Pivot Table Field List settings, so it works best for your needs.
Continue reading “Customize Your Excel Pivot Table Field List”
Excel doesn’t have a built-in Cluster Stack chart type, but you can make one, with a quick pivot chart.
See how to make the chart shown below, with stacked columns, grouped into regional “clusters”.
Continue reading “Make a Clustered Stacked Column Pivot Chart in Excel”
Do you love Excel pivot tables? And have you tried Excel’s new PIVOTBY function?
With just one formula cell, PIVOTBY can summarize annual sales by year, region, and category!
But is PIVOTBY better than Excel pivot tables?
Continue reading “Is Excel PIVOTBY Function Better Than Pivot Tables?”
Instead of adding a static title to your Excel pivot chart, use a worksheet formula to create a dynamic chart title. When you change a pivot table filter, the chart title updates automatically, to show what you selected!
Continue reading “Make Pivot Chart Title Change with Filters”
In an Excel pivot table, if you try to group a date or number field, you might see an error message. The message box shows a yellow warning label, and the text says, “Cannot group that selection”.
Continue reading “Fix Pivot Table Problem – Cannot Group That Selection”
After you sort an Excel pivot table, the wrong item might appear at the top of a column. For example, in the screen shot below, the list of Sales Rep names has Jan at the top. Below that name, all the other names are sorted in the correct alphabetical order.
Continue reading “Wrong Item at Top of Sorted Pivot Table in Excel”
Do you ever make a quick pivot table, then spend the next 30 minutes trying to make it look good? Here are three quick pivot table layout tips, to make your job a bit easier!
Continue reading “Make Your Pivot Table Look Better – 3 Quick Tips”
If you have a report filter at the top of your pivot table, do the items in the drop down list ever appear out of order? That happens in my pivot tables occasionally, and there isn’t a quick and easy way to fix the problem!
After you create an Excel pivot table, the source data usually changes. New records are added, and old records might be changed or deleted.
Later, when you refresh the pivot table, you should see a summary of your updated data, but sometimes there’s a problem – old data sticks in the drop down lists.
To show this pivot table problem, I made short video, which you can see in the next section.
My sample file has data from a fictional sales company, and the source data was changed:
After making those changes, I refreshed the pivot table. As expected, the Central region’s name disappeared from the Region headings.
However, Central still shows up in the Region drop down.
In this short video, I show how you can clear the old Region name from the pivot table drop down list.
Doing this will also prevent old items from appearing in this pivot table, in the future.
In Excel 365, and Excel 2019, it’s even easier to avoid old items in pivot table drop downs.
Instead of changing this setting for every pivot table that you create, you can change it once, in your Excel default settings for Pivot Tables.
In the video below, I show the steps for changing an individual pivot table, like I did in the previous video.
Then, at the 2:57 mark, I show how to change the default setting, in Excel 365. You can skip to that section, if you’d like!
To stop old items from showing in an existing pivot table, follow the steps below.
NOTE: This setting will affect all the pivot tables that use the same pivot cache.
To get the sample file, go to the Clear Old Items page on my Contextures site.
That page also has Excel macros that you can use, to
_______________________
_______________________
