It’s almost the 4th of July here in the US – probably everywhere else in the world too. But in the US, the 4th of July is Independence Day.
So in honor of the upcoming 4th of July weekend, I’ve declared this week to be Pivot Table week. I’m pretty sure our founding fathers used pivot tables.
Today, I’ll show you a trick to add visualizations into your pivot table.
See those colored triangles in this pivot table? They are actually part of the pivot table. As I filter, drag, or pivot this data, the Delta column keeps up, showing a visual representation of the variance.
I achieved this effect with a calculated field and some font tricks.
Step 1: Create the calculated field
For those of you who don’t know, you can create a calculated field by placing your cursor anywhere inside the pivot table and:
In Excel 2003, select Insert ->Calculated Field
In Excel 2007, go to the Options tab and select Formulas ->Calculated Field
Once you’re in the Calculated Field dialog, build an IF statement that will return either a 5 or a 6.
In this example, if the Variance is greater than zero, a 5 will be returned. If it’s less than zero, a 6 will be returned.
Note in the Name field I called this calculation “Delta”.
Step 2: Change the Font to Webdings
Once you have the calculated field, change the font to Webdings font. Note that the best way to do this is to hover over the title “Sum of Delta” until your cursor turns into a black arrow pointing down. Then you can safely change the font to Webdings
With a little formatting you get this:
Step 3: Use Custom Formatting to apply color
Go into the field settings for your newly created calculated field and apply a custom formatting that will turn any 5 to Blue and any 6 to Red.
Here is the syntax to use: [Blue][=5]#,##0;[Red][=6]#,##0
There you have it.
And like I said, because the calculated field is part of the pivot cache, your visualization will keep up with your pivot table as you move data around.
Now before you get excited and try to use all kinds of crazy calculated fields to make visualizations, you should note that a calculated field can only return a number. That is to say, the formula you place into your calculated can not return a textual result. Thus the use of 5 and 6.
RELATED STUFF
Nice trick, but arrows are too tame. Any self respecting dashboard should instead have the skull and crossbones in it (character 2620 from the Arial Unicode MS set), if not the radioactive sign (2622 of same), biohazard sign (2623 of same) or Jon Peltier’s company logo (143 of webdings. Jon must know some people…)
[you should note that a calculated field can only return a number] – question: does a calculated field accept text as part of an arguement? e.g. would this work = if(Market = “Great Lakes”,[code for skull and crossbones],na())?
I was trying something similar today in Excel 2007, and while excel accepted the arguement in the calculated field dialog box (which of course is too damn small, but don't get me started) the calculated field only returned the false part if the if() statement.
Jeffrey: I’ve always thought it would be funny to put a frowny face on a management report. As in, “We made doody on our financials” – frowny face.
I don’t think the character codes count as numeric values. Excel will still have to return a textual value which will just fail to give you what you want.
Good tip! I used the first part (calculated field) and then used new Conditional Formatting features in 2007 to apply one of their sets of icons. If you then edit the rule, you can set it to track along with your pivot table (or only a certain subset of it) and display the icon only.
I really like the red/white/blue color scheme of that pivot table. Robert Heft would have been proud. The American Flag was designed a few years before pivot tables were introduced, so Heft was forced to use the Excel grid with background colors.
And then Kusleika stole his code…
http://www.dailydoseofexcel.com/archives/2004/05/31/flying-the-flag/
John: You’re the first person who picked up on my subtle homage to old glory. Either you’re the smartest man here, or everyone is on vacation.
Or no one gives a shit.
Options A & C are both correct.
OK Question, I cannot find it however, some place in review of 2007 Excel is there not a way that there are colored symbols, which can be added to show values in plus or minus stages? If it was in a minus stage it was a red ball, in a plus stage a blue ball and no change a green ball or something like that. I remember seeing it at one of the MS Sessions last year. Haven’t used it thus forgotten how, would this work then in a Pivot table, if I remember it correctly?
Mike, that’s a neat trick – although the practical value is elusive. For example, what more insight do you get for the added complexity introduced, compared to say, conditionallly formatting the negative value(s) in the data set to stand out?
Colin: I get your meaining. In this particular example, the justification seems thin. However, In larger datasets with many more data points, these visualizations can offer an at-a-glance view of trouble areas.
Also, these visualizations do offer some value when printing in black and white – where the benefit of color is diminished.
Mike, points well taken. I suppose that my observation here is that if one is concerned about trouble spots, then only the trouble spots need to be highlighted. After all, the visualization is binary – the number is either up or down (or perhaps no change for a third state). Since the visualization doesn’t show the extent of “up” values, it seems redundant to highlight them at all (and potentially distract the reader from the problem areas). Never mind – I like the technique for showing how to create visualization icons in Excel, without having to resort to icon sets (which aren’t available pre-Excel 2007).
Colin: It sounds like we share a general distain for the new Icon sets in 2007. More specifically, the lack of backward compatiblity. What’s the point of a new functionality when 50%+ of users won’t be able to benefit from it.
Mike, agreed. I’m not sure what the best solution would be for this issue, but it’s always been the case when a new version of Excel is introduced. Standing still wouldn’t help (the product needs to evolve), but sometimes unnecessary incompatibilities are introduced. One that’s given me a particularly large headache concerns QueryTables. You can’t create a QueryTable (worksheet object) via MS Query in Excel 2007. When you return data to Excel from Query and choose to create a PivotTable, if the workbook is in compatibility mode, a backward compatible PivotTable is created. In a similar vein, why in God’s name can’t compatibility mode present the option to create a QueryTable instead of a Table when returning data to Excel?