Using Icon Sets and Cell Formatting to Simplify Pivot Table Data

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
Pastrami Bread Roll
Pastrami Meat Pastrami
Pastrami Cheese Swiss
Pastrami Pickles Sliced
Pastrami Onions Red
Pastrami Mustard Dijon
Veggie Supreme Bread Wheat
Veggie Supreme Cheese Provolone
Veggie Supreme Pickles On the side
Veggie Supreme Onions On the side
Veggie Supreme Mustard Yellow
Cheese on the side Cheese American
Kids Cheese Bread White
Kids Cheese Cheese Cheddar

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:

Sandwich Type Bread Meat Cheese Pickles Onions Mustard
Pastrami 3014 3014 3014 3014 3014 3014
Veggie Supreme 3065   3065 3065 3065 3065
Cheese on the side     66      
Kids Cheese 11939   11939      

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.

3-20-2014 1-37-14 PM

Now you’ve got this:

Sandwich Type Bread Meat Cheese Pickles Onions Mustard
Pastrami X X X X X X
Veggie Supreme X   X X X X
Cheese on the side     X      
Kids Cheese X   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.

 3-20-2014 1-46-02 PM

Now you have to set the conditional formatting up like this:

 3-20-2014 1-40-05 PM

 Make sure you get the “show icon only” box checked!

Now press ok.

Now you have pretty green checkmarks! 

 3-20-2014 1-40-44 PM

Nothing to it!  Hope you enjoy.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s