Excel PivotTable Slicer Options Explained

Microsoft has a great tutorial on how to use slicers to filter PivotTable data and I don’t feel that I need to duplicate their tutorial here. You can read all about it here: Microsoft: Use slicers to filter PivotTable data

What the article does not cover is the Slicer settings available by right-clicking on a slicer and selecting that bottom option “Slicer Settings”.

There are 3 main settings that you can set here – I typically only change the last one and leave the rest on their default settings. They are:

1. Name – this is the name by which you can refer to the slicer in formulas. I typically just let this be the default. (e.g. CUBERANKEDMEMBER("PowerPivot Data",Slicer_Name,ROW()))
2. Header – this is the name displayed above the slicer. You can choose to turn it on or off and customize the name if needed. Again, I typically just leave the header visible with the default name.
3. Item Sorting and Filtering – Here’s where you can choose to:
• order the values ascending or descending
• Perhaps most important for visual aesthetics (and the value I typically choose to modify most often) you may hide items with no data
• Choose to visually indicate items with no data (by making them more transparent)
• Choose to show items, which have no data associated with them, last in the list
• Continue to show items which have been deleted from the original data source table

Questions? Suggestions? Leave a comment!

How to Hide Pivot Chart Filters

(Directions below are for Excel 2010)

I recently built a simple dashboard using pivot tables with corresponding pivot charts. I was asked by my client if I could remove the filters from the pivot charts so that they would have a cleaner look for presenting. I had never worried about this before and didn’t know how off the top of my head so I did a couple of quick Google searches & couldn’t find any references on how to hide the filters! I was very surprised that I couldn’t find anyone else asking the question so I thought I would share how to easily do this. Luckily it’s pretty simple – I just didn’t realize these options existed!

Here’s a Pivot chart with the usual filters visible:

To remove these filters, click the “Field Buttons” (or drop down for more control) button on the Analyze tab of the PivotChart Tools section of the menu ribbon (only visible if the chart is selected):

You can then choose which (if any) filters to display so that your chart looks nice & clean:

Hope this helps!

~Yosef B.

The Many Ways to Refresh an Excel Pivot Table

Ed. Note: Click on any picture to view full size

There are relatively few things as aggravating as spending hours trying to track down why the calculations in your PivotTable do not match your data than to finally realize that you forgot to refresh your PivotTable…

Here are a few tips & tricks for automatically refreshing your PivotTables.

Automatically Refresh PivotTables Upon Opening Excel Workbook

This first trick shows you how to set your PivotTable to refresh all calculations automatically as soon as you open your workbook. The obvious drawback is, if you change any of your data after you open the workbook, you will still have to refresh the PivotTables to reflect the new data.

To enable this feature, right-click anywhere on your PivotTable & select “PivotTable Options”

Next, select the “Data” tab and then select the “Refresh data when opening the file” option. Click “OK” to save your selection.

Write a Macro

This second option is very easy to implement and will refresh your PivotTable every time you select the worksheet tab that contains the PivotTable. Unfortunately, I have not figured out how to make your PivotTable refresh every time you change data in your data source (if anyone has a better macro, please share in the comments below!).

The drawback to this trick is, you have to leave the PivotTable tab and then return to it to force a refresh. Still – if you have your data on a different tab & you’re going back and forth anyways to check your calculations than this should help prevent our original aggravating scenario…

Right click on the sheet name where you placed the PivotTable and select “View code”

Copy the following code into the resultant code window (Note: if you copy & paste, you may have an error because the double-quote symbol does not always paste correctly into Excel – if you have a problem, try replacing all the double-quotes with new ones in the code editor.)


Private Sub Worksheet_Activate()
    Sheets(“PivotTable”).PivotTables(“PivotTable1”).RefreshTable
End Sub


You will need to change this code to match your specific information as follows:

• “PivotTable” is the name of the worksheet tab where your PivotTable resides
• “PivotTable1” is the name of your PivotTable

Click the “Save” button. (Highlighted red in the picture below)

To find the name of your PivotTable, simply click anywhere on your PivotTable and then look at the name listed in the PivotTable section on the “Options” Tab on the special “PivotTable Tools” area of the ribbon. (In my example picture below, the PivotTable name is PivotTable4).

Questions or related tips/tricks? Let me know below!