We, as data analysts and Excel experts, are always trying to hunt down new and better techniques to create dashboards. Be it in terms of either design or interactivity, there is always something out there which will appeal to us. In the same vein, Excel MVP Mynda Treacy is here to show us some cool tips and tricks to make our dashboards appear more interactive.
As depicted in the pictures below, this is something that can serve as part of a dashboard or a standalone, mini-visualization of some data.
There are a few things to note here:
Well, let us not make anyone scratch their head on this one. Check out how it was made below.
As we can see, that table is actually the last two columns of another pivot table. The Slicer was being used to cover up the names of food items in the first column. Also, the monthly data for each item has been hidden. This can be seen from the presence of the grouped columns sign above column O.
The observant amongst would have spotted these hidden columns even without the sign for grouped columns: by looking at the column names. There is no column showing between B and O!
We know how to add Sparklines. We will just unhide the monthly data, add Sparklines on them and then hide the data again. Well, there is one problem with this method: Sparklines will not display the hidden data!
Don’t sweat! There is an inbuilt option in Excel that can remedy the situation. When we have a cell with Sparklines selected, go to the Design tab, click on Edit Data and then select ‘Hidden & Empty Cells…’. This is shown in the image on the right. Now tick the checkbox on ‘Show data in hidden rows and columns’, and click ‘OK’. Alas, it’s working!
We know now what’s the last step. If anyone didn’t, the title of this section might have given it away. What is the trick behind making the rows of the table highlighted as corresponding food items are chosen from the Slicer. Yes, it’s conditional formatting.
The pivot (bar) chart has a pivot table working behind it. The Slicer is applying filters on it. Within that pivot table if we put ‘Category’ (or whatever the column containing the names is called) as a filter, it will appear at the top.
We would now be able to see that the filter (call it cell X) corresponds to the item selected from the Slicer. So, we can apply Conditional Formatting on the table, to highlight the rows which have the same food item as cell X. Simple, right?
Try it out yourself! And share any fun, crazy or creative tips you may have for us.
Also, share this tip with your friends and colleagues. You never know what can earn you some brownie points!
Please log in again. The login page will open in a new tab. After logging in you can close it and return to this page.