Sometimes a quick visual representation of something is all you need. It could be that details are too difficult to look at without melting your brain – or they are just info that not everyone needs. There is a cool trick I’d like to share which will allow you a nice visual cue for certain kinds of info.
In my scenario, I run a sandwich shop. It’s almost lunchtime, what can I say?
Let’s say that the data in my pivot table looks like this:
|Sandwich Type||Ingredient||Ingredient Type|
|Veggie Supreme||Pickles||On the side|
|Veggie Supreme||Onions||On the side|
|Cheese on the side||Cheese||American|
This is making me hungry. I hope you’ve already eaten.
But let’s say that for the sake of the customers, I want to just show what type of ingredients come with a sandwich, not what type of ingredient comes with by default? Meaning – “Does the sandwich come with cheese, not “what type of cheese does it normally come with?”
To get that effect, you would move the “Ingredient Type” field from the Row Labels to the Values Row. You’ll now notice that you get some ugly nonsense number. I’m pretty sure there is no such thing as 3014 cheese. It doesn’t matter if you use Count or Sum here – the only items which have a number at all are the ones which contain that ingredient, no matter what type it is.
Now move the “Ingredient” row to the Column Values. It should now look like this:
|Cheese on the side||66|
The first step is to replace all the numbers with an X. Right click somewhere in the values section and select “Value Field Settings”. Then click on “Number Format”. We’re going to enter a custom format. All you have to put in that little box is a nice capital letter X.
Now you’ve got this:
|Cheese on the side||X|
You can stop here if you don’t want to get fancy… but who doesn’t want to get fancy? We could make this much, much prettier if we want! (you don’t have to do the “X” step to do the next one, but the pretty icons don’t copy and paste, so I usually do it just in case)
Highlight a cell in the values area and go into conditional formatting. Select “Icon Sets” from the drop-down.
Select the set you like. You could do stars, circles, and even checkmarks. I picked this one.
Now you have to set the conditional formatting up like this:
Make sure you get the “show icon only” box checked!
Now press ok.
Now you have pretty green checkmarks!
Nothing to it! Hope you enjoy.