A slicer that doesn’t slice [Pivot Table Tricks]
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 above demo to understand:
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
Read the rest of this article to know more.
Unpivot and then pivot for clarity (case study)
Or more appropriately titled, the one where Power Query solves the problem in less time than it takes you to say Get & Transform Data.
Recently, one of my students Mr. K, sent me a pivot table problem.
Today my boss asked me “how much we paid to staff since the inception of our business with their respective date of joining?” He wanted to know, level wise summary of the last 16 years (on Quarterly / Year wise basis).
The records appended from the database month wise. Have a look to the file and give your ideas.
Mr. K’s data looked like above.
Use slicers to create a cool selection mechanism [quick tip]
Most advanced Excel users know that slicers are cool. Today, let’s learn how to use slicers to create an awesome selection mechanism for your dashboards and forms.
First see a quick demo
Looks slick, eh? Read on.
CP047: Best Excel tools for Entrepreneurs
Podcast: Play in new window | Download
Subscribe: Apple Podcasts | Spotify | RSS
In the 47th session of Chandoo.org podcast, let’s see how Excel can make you an awesome entrepreneur.
What is in this session?
In this podcast,
- Why Excel for entrepreneurs
- Key areas of a business owner’s work
- Projects & to dos
- Finances
- Customers & marketing
- Planning & strategy
- Processes & workflows
- 5 features of Excel that help
- Conclusions
Unpivot data quickly with Power Query [tutorial]
Power Query (Get & Transform data in Excel 2016) is a must have tool, if you wrangle with data every day. Here is a quick introduction, in case you are new.
Let’s learn how to use Power Query to unpivot data.
Essentially, we are trying to go from left to right in the above picture.
Doing something like this thru either formulas or VBA can be very complex. But Power Query can get you unpivoted data in just a few clicks. Sounds interesting? Read on.
How to use GETPIVOTDATA with Excel Pivot Tables
Pivot tables are very powerful analysis tools. They can summarize vast amounts of data with just few clicks. But they are lousy when it comes to output. Imagine the horror of putting a pivot table right inside your beautiful dashboard. One refresh could ruin the layout and create half-an-hour extra work for you.
How to combine the power of pivot tables with elegance of your dashboards?
The answer is: GETPIVOTDATA()
How to create dynamic sparklines for latest 30 days [video]
Sparklines are fun and very insightful. They are easy to create, easy to maintain and fit into any dashboard.
But there is one tiny problem with them. Usually we have a lot of data, but we don’t to visualize all of it. We just want to visualize latest 30 days trend or last 12 months trend or QTD or something similar. What then?
In this video, learn a powerful and very simple way to create dynamic sparklines using Excel.