by contextures.com
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”
After you build an Excel pivot table, you might see a few row labels or column labels that contain the text “(blank)”.
This happens when data is missing from the source data. For example, in the source data, you might have a few sales orders that don’t have a Store number entered.
Watch this short video to see how to fix those “(blank)” labels, and there are written steps below the video.
In the pivot table shown below, there are three City heading cells, in column B, that shows (blank), instead of a city name. There is also one Region heading cell in column A that has the same problem.
When working with a pivot table, I find that text distracting, and usually remove it, to get a cleaner look in the pivot table.
The steps to do that are below this screen shot.
First, here’s a solution that you might have tried, to remove the “(blank)” text, and found that it didn’t do what you needed.
After the PivotTable Options dialog box closed, you’d be disappointed to see that the “(blank)” headings were still in the row labels.
That solution does not work for “(blank)” cells, because: that “empty cells” setting has these limitations:
For example, in the screen shot below, you can see that:
Instead of using the PivotTable Options, you can manually change the (blank) labels in the Row or Column Labels areas by typing over them in the pivot table.
However, this technique has a couple of limitations too:
Here are the steps for manually changing a pivot table row label text, or heading labels in the column areas.
To change a blank label cell to “N/A”, follow these editing steps:
After you press the Enter key, you’ll see the following changes in the pivot table, shown in the screen shot below:
To get Excel data for practice with your pivot table skills, go to the Excel Sample data page on my Contextures website.
___________________________
For more information on pivot tables, see these pages on my Contextures site:
Clear Old Items in Pivot Table
__________________________
____________________
It’s November, a month when we expect cooler temperatures here in Canada. However, it was more like summer last weekend, and we enjoyed an afternoon beverage on the patio. Was that normal? What were the November temperatures over the past few years? Let’s use a pivot table with conditional formatting, to find out!
Continue reading “Conditional Formatting Excel Pivot Table Color Scale”
Excel automatically adds grand totals to a pivot table, if there are multiple items in the row area, or in the column area. See how you can change the automatic grand total headings (sometimes), and quickly remove grand totals if you don’t need them.
After you create a pivot table in Excel, you can filter the data, to focus on specific things. Maybe you want a top product report, or a regional summary, or see the sales to a couple of new customers. You can also use filters on date fields, and there are 3 different types you can use.
Continue reading “Pivot Table Date Field Filters – 3 Types to Try”
Quickly check your sales results over time, or by top products, with the % Running Total feature in an Excel pivot table. This feature is available in Excel 2010, and later versions.
Continue reading “Check Sales Progress with Percent Running Total in Excel Pivot Table”
Excel doesn’t have a Cluster Stack chart type, but you can make a pivot chart with stacked columns that are grouped into “clusters”. See the steps in the video below, and get the sample Excel file, to try it for yourself. Continue reading “Easy Steps for Excel Clustered Stacked Pivot Chart”
When you set up a pivot table, and put fields into the Rows Area or Columns area, Excel groups the items, and calculates the totals for each group. For example, see count of products for each Unit Price. Each item should only be listed once in the pivot table, but sometimes you might see duplicates.
Continue reading “Duplicate Numbers in Pivot Table Items Problem”
With a large pivot table, running a macro that refreshes that pivot table might be painfully slow. The usual macro steps, like turning off screen updating, and disabling events, don’t solve this problem. Try adding an extra bit of code to your macro, to see if that makes the refresh go faster.
Continue reading “Faster Pivot Table Refresh in Excel Macros”
You can use conditional formatting in an Excel pivot table, to highlight specific data, such as months with high sales numbers. This example uses conditional formatting to highlight the pivot table values that are connected to weekend dates. Continue reading “Pivot Table Highlight Weekend Data Conditional Formatting”
