fbpx

A slicer that doesn’t slice [Pivot Table Tricks]

Share

Share on facebook
Facebook
Share on twitter
Twitter
Share on linkedin
LinkedIn

Mary Ellen, one of our readers, has an interesting conundrum,

I have some data that goes to Pivot table then to pivot chart. There is a slicer to filter the data. But when I slice, my pivot chart gets messed up. How to have the slicer, but still see the insights in the chart?!?

See this demo to understand:

slicer-chart-confusion

This is because when you slice by a school, the pivot table gets filtered and hence % row total for that school becomes 100% (as there are no other schools).

How to fix the problem? The easy answer is to remove the slicers. But we want to have our slicers and eat a slice of them too. So we crank up the Excel awesomeness valve and get to work. There are two ways to achieve what we want.

  • Old school method: Two pivot tables, some formulas & a line chart
  • New Excel method: Power Pivot and a line chart

A slicer that won’t slice – Video

Watch below video where you can learn about both methods.

You may see this video on Chandoo.org YouTube channel too.

Download Example Workbooks:

Please use below links to download the example workbooks for this video.

Examine the formulas & measures to learn more.

Learn more about Pivot tables, Slicers & Power Pivot

If you are curious to learn more about pivot tables, slicers & power pivot, check out below links:

Master Power Pivot in your pajamas:

Imagine being able to analyze millions of rows of data, answer any sort of questions about it and building awesome dashboards, all in your pajamas. That is Power Pivot for you. If you want to master this amazing technology, consider joining my online Power Pivot class. This proven program helps you learn all the intricacies of Power Pivot world and helps you build business intelligence solutions using Excel.

Please click here to know more about Power Pivot classes.

Share on facebook
Facebook
Share on twitter
Twitter
Share on linkedin
LinkedIn

Share this tip with your colleagues

Excel and Power BI tips - Chandoo.org Newsletter

Get FREE Excel + Power BI Tips

Simple, fun and useful emails, once per week.

Learn & be awesome.

Welcome to Chandoo.org

Thank you so much for visiting. My aim is to make you awesome in Excel & Power BI. I do this by sharing videos, tips, examples and downloads on this website. There are more than 1,000 pages with all things Excel, Power BI, Dashboards & VBA here. Go ahead and spend few minutes to be AWESOME.

Read my storyFREE Excel tips book

Excel School made me great at work.
5/5

– Brenda

Excel formula list - 100+ examples and howto guide for you

From simple to complex, there is a formula for every occasion. Check out the list now.

Calendars, invoices, trackers and much more. All free, fun and fantastic.

Advanced Pivot Table tricks

Power Query, Data model, DAX, Filters, Slicers, Conditional formats and beautiful charts. It's all here.

Still on fence about Power BI? In this getting started guide, learn what is Power BI, how to get it and how to create your first report from scratch.

two level data validation drop down excel

Two-level Data Validation [Excel Trick]

Ever wanted to create a two-level data validation list? You can use this simple trick to make two-level or cascading drop-down validation lists in Excel. You need some data, a pivot table and simple IF formulas to get this. Read on to understand the process and create your own two-level drop down lists in Excel.

2 Responses to “A slicer that doesn’t slice [Pivot Table Tricks]”

  1. Anthony says:

    This is awesome stuff! Thank you!!!

  2. Shungu Dhlamini says:

    Wow, always on point!

Leave a Reply