Pivot Table/Chart Question

Question

Hello,

I have a pivot table and I am filtering on a field called Work Center.  The filter on the chart shows that it is filtered but no name is present.  Is there a way to link the values or display the field in the chart?  In this case, I would like the chart to also reflect the "Carpentry" filter.  See pic.

Thanks

Answer 1

Another option is to use a "Slicer" to provide a "Visual" filter.  You can place the slicer in a way that makes it visually clear that it applies to both the table and linked chart.

Take a look at this example file. I could not find an associated article describing how he did it, but maybe you can dive in to it and figure it out:

PivotChart Formatting Changes On Filter Slicer.xlsx **** (35.6 KB) Excel Campus

https://www.excelcampus.com/filedownload/pivot-tables/PivotChart%20Formatting%20Changes%20on%20Filter%20Slicer.xlsx

e pt- PivotChart Formatting Changes on Filter Slicer.xlsx (downloaded)

Click slices to see changes in formatting -> This shape represents a slicer. Slicers are supported in Excel 2010 or later. If the shape was modified in an earlier version of Excel, or if the workbook was saved in Excel 2003 or earlier, the slicer cannot be used. Chart cache stores formatting properties for slices of the pivot

 .

Display User’s Slicer Selections: A Macro to Automatically Create the Formulas           October 2, 2012

Note the “Readout” that Displays the User’s Slicer Selections: Now Do This for Every Pivot in Your Workbook With One Click!

This is something we do all the time at Pivotstream – we write formulas that capture user slicer selections and then display those selections back to them.

We do this via hidden report filters:

(Yes, we could also do this with cube formulas, but we started out (literally years ago now) using this approach and we’ve just kinda stayed with it.  I’m not sure cube formulas would be better, but they might be.)

Why do we do this?  Well, for one thing, the Download Snapshot feature on the server does NOT download the slicers – you get a big blank white space where the slicers were, which isn’t terribly helpful.  It leaves you wondering what you had selected.

It’s also useful when there are slicers on other sheets impacting your current sheet.  And even on a single sheet, it’s often nice to have a compact readout of your selections without having to scroll (or even scan with your eyes) to see what selections you have made.

.

The Latest and Best Way to Catch/Capture/Inspect Slicer Selections            2017 03 17   Rob Collie

Quick post today because, well, even more going on than usual. This is actually the fifth post on this specific topic, which means that it’s something that keeps coming up.  But unlike previous posts, in which we kept DISCOVERING slightly better ways to do things, this latest post is triggered by us GETTING a new way to do it from Microsoft

CONCATENATEX – Requires Excel 2016 or Power BI Desktop

Yes, that’s right, this one will not work in previous versions of Excel, because we’re gonna use CONCATENATEX!

.

! I’ll Have a Slicer That!               2014 08 12https://www.myonlinetraininghub.com/ill-have-a-slicer-that

Slicers were introduced in Excel 2010 and they’re an interactive control that enables you to filter data in PivotTables, PivotCharts, Excel Tables and CUBE functions. Now I know you can already filter using the PivotTable or Excel Table filter tools but Slicers are better for 2 reasons:

   - They can control the filtering of multiple PivotTables/Charts

   - They look nicer and are more intuitive to use

ET MR PivotTables.docx .

!

! Introduction to Slicers – What are they, how to use them, tips, advanced techniques & interactive reports using Excel Slicers                   2015 06 24

http://chandoo.org/wp/2015/06/24/introduction-to-slicers/

What are they, how to use them, tips, advanced techniques & interactive reports using Excel Slicers.

Slicers are *visual filters.*Using a slicer, you can filter your data (or pivot table, pivot chart) by clicking on the type of data you want.

Slicers are one of my favorite feature in Excel. And here is a quick demo to show why they are my favorite.

.  * Slicers – what are they? .  *  How do I Add them .  *  Can I link them to Charts .  *  Can I link them to multiple reports.  *  Are they Formatable

.  *  So What is next

.

@ **50 Things You Can Do With Excel Pivot Tables******https://www.myexcelonline.com/blog/50-things-you-can-do-with-excel-pivot-tables/

Pivot Tables in Excel are one of the most powerful features within Microsoft Excel.   A Pivot Table allows you to analyze more than 1 million rows of data with just a few mouse clicks, show the results in an easy to read table, “pivot”/change the report layout with the ease of dragging fields around, highlight key information to management and include Charts & Slicers for your monthly presentations. I have compiled an interactive tutorial on the 50 different things you can do with an Excel Pivot Table.

Table of Contents Topic 26: Insert a Slicer

 Topic 27: Slicer Styles

 Topic 28: Slicer Connections for multiple pivot tables

 Topic 29: Different ways to filter a Slicer

 Topic 33: Pivot Chart & Slicers

.

@

Answer 2

Add a Chart Title to the chart

Select the Chart Title

Click into the formula bar and type a = sign

Select cell B1 and press ENTER

It is not possible to show the filter value in the chart directly. What you see in the chart is the value of cell A1.

Andreas.