Gilligan on Data by Tim Wilson

I’ll put my standard big, fat, hairy disclaimer here that this blog is not about Excel tips. There are lots of resources for that. As a matter of fact, the Contextures blog is one that I stumbled across after Debra commented on my last Excel tip.

Nevertheless, here’s a handy one that requires no customization of Excel, but that I guarantee you’ll be hooked on if you start using it: <F4>

<F4> and <Ctrl>-Y do the same thing, actually, and they work in MS Word, too. What do they do? Pretty simple:

Repeat the Last Action Taken

That’s it. If you’ve just formatted a cell or set of cells with a new border and background color, then you can click on a cell and press <F4> and it’ll apply the same formatting to the new cell. And then do it again! In this case, it does the same thing as the Format Painter…but does it faster (with limitations, as described below).

If you’ve just inserted a row and you want to insert another row lower down on the spreadsheet, highlight the next row and press <F4>.

As you can imagine (if you stop and try to imagine it…and I recognize it’s got to be a pretty bleak day of creativity for this to bubble up as worthy of your imagination), this is particularly handy when doing some oddball work on non-contiguous cells.

This is handier than you might think. And, it does have it’s limitations. The main one is that it only repeats the immediately preceding action. In the Format Painter example above, <F4> is no use if you have a cell already formatted as you want and you want to copy that format to other cells. That’s what the Format Painter is for.

And, another limitation is that it doesn’t work with every possible action. For instance, if you type a value into a cell and then want that same value in another cell…<F4> doesn’t work. You’ll have to copy and paste. It becomes pretty intuitive in a hurry as to where it works and where it doesn’t.

Happy repetition!

For a variety of reasons, I find myself using Excel (2003) through remote desktop fairly regularly these days, and I just haven’t gotten around to setting up some of the basics that I’ve got set up on my main system. The big one is my setup of <Ctrl>-<Shift>-<V> as Paste Special»Values.There are scads of “Excel Tips”-type sites and blogs, and I’m not putting myself out there as an expert. Really, just looking to share one of my handy favorites with my readers, who are mostly somewhere within a standard deviation or two of my Excel skill level and might find this useful.

Backing up just a little bit. Paste Special is really, really nice to have when you need it. Specifically, pasting values, formats, formulas…and occasionally Transpose. But…mostly (for me) pasting values. When I’ve got a well-formatted table of data and need to move some data around, it’s just annoying to need to then go and fix the formatting. So, pasting formulas only or values only avoids all that. The problem is that the fastest way to do this is:

1. Copy the cell(s) you want to relocate (<Ctrl>-<C>)
2. Right-click anon the cell in the new location
3. Select Paste Special
4. Select Values
5. Click OK

All in all, not too painful…unless you find yourself needing to do it two or three times in a row (between separate workbooks, for instance).

This got annoying enough to me a several years ago that I recorded a macro and dropped it in Personal.xls so that I’d have a faster way to do this. It’s now the first thing I set up on any new computer I get.

The Result: After copying cells (this doesn’t work with cutting data), simply click on the cell where you want the values pasted and press <Ctrl>-<Shift>-<V>. That’s it.

How to Set It Up

This may look like a real hassle. It really isn’t (those four years as a technical writer tend to make my procedure writing a bit…er…detailed). But, it’s a one-time setup, and it really isn’t that bad.

If you’ve read this far and aren’t thinking, “MAN! That would be HANDY!” then just bail now. Otherwise, read on:

1. Launch Excel 2003
2. Select Window»Unhide
3. Select Personal.xls
4. Select Tools»Macros»Visual Basic Editor. This should bring up the VBA editor
5. Select Insert»Module
6. Copy and paste the following into the window:Sub PasteSpecial_Values()
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End Sub
7. Click on the X to close the Visual Basic Editor (you don’t need to save anything yet). You should be back on the Personal.xls workbook
8. Select Tools»Macro»Macros
9. Select PasteSpecial_Values
10. Click Options
11. Click in the Shortcut key box
12. Press <Shift>-<V>
13. Click OK
14. Click the X to close the Macros window
15. Press <Ctrl>-<S> to save Personal.xls
16. Select Window»Hide to hide Personal.xls
17. Close Excel. If you are prompted to save Personal.xls, do so.

You should be set. Let me know if you give it a shot and find it useful (and if you hit any bumps in implementing it).

I’ve glanced through various folks’ copies of Stephen Few’s Information Dashboard Design on several occasions over the past few years. And, it was a heavy influence on the work that an ad hoc team in the BI department at National Instruments undertook a couple of years ago to standardize/professionalize the work they were putting out.

I finally got around to reading a good chunk of the book as I was flying a three-legged trip out to British Columbia last week…and it is good! One section that particularly struck me started on page 100:

Edward R. Tufte introduced a concept in his 1983 classic The Visual Display of Quantitative Information that he calls the “data-ink ratio.” When quantitative data is displayed in printed form, some of the ink that appears on the page presents data, and some presents visual content that is not data.
:

He then applies it as a principle of design: “Maximize the data-ink ratio, within reason. Every bit of ink on a graphic requires a reason. And nearly always that reason should be that the ink presents new information.”
:
This principle applies perfectly to the design of dashboards, with one simple revision: because dashboards are always displayed on computer screens, i’ve changed the work “ink” to “pixels.”

I’ll actually go farther and say that “dashboards” can be replaced with “spreadsheets” and this maxim holds true. Taking some sample data straight from Few’s book, and working with a simple table, below is how at least 50% of Excel users would format a simple table with bookings by geographic region:

Look familiar? The light gray gridlines in the background turned on in Excel by default. And, a failure to resist the urge to put a “thin” grid around the entire data set.

Contrast that with how Few represents the same data:

Do you agree? This is clearly an improvement, and all Few really did was remove the unnecessary non-data pixels.

So, how would I have actually formatted the table? It’s tough to resist the urge to add color, and I am a fan of alternating shaded rows, which I can add with a single button click based on a macro that adds conditional formatting (”=MOD(ROW()+1,2)=0″ for shaded and “=MOD(ROW(),2)=0″ for not shaded):

In this case…I’d actually vote for Few’s approach. But, even Few gives the okay to lightly shaded alternative rows later in the same chapter, when some sort of visual aid is needed to follow a row across a large set of data. That’s really not necessary in this case. And, does bolding the totals really add anything? I don’t know that it does.

The book is a great read. It’s easy to dismiss the topic as inconsequential — the data is the data, and as long as it’s presented accurately, does it really matter if it’s presented effectively? In my book, it absolutely does matter. The more effectively the data is presented, the less work the consumer of the data needs to do to understand it. The human brain, while a wondrously effective computer, has its limits, and presenting data effectively allows the brain to spend the bulk of its effort on assessing the information rather than trying to understand the data.

Categorize this as the most tactical of Excel posts ever. But, doggonit, it sometimes amazes me how many really, really, really handy features of Excel most people don’t even know exist. Even in Excel 2003. This is one that I showed to a co-worker several weeks back as I was looking over her shoulder at a spreadsheet. It’s worth sharing.

Question: What is the fastest way to get the sum of a small range of cells in Excel?

If you immediately think of something along the lines of: “Click on an empty cell, hit the sigma icon, and then highlight the range of cells you want to sum and press <Enter>,” then this tip is for you.

The approach I just described is great if you actually want to keep the sum in the spreadsheet as a permanent calculation. But, what if you are on the phone and discussing the data with somebody who asks, “What was the total from July through October?” You don’t already have that calculated, and you don’t really want to keep that calculation on the spreadsheet. The approach above would work. But there is a better way.

The tip put really, really simply: Highlight the cells you want totaled and look at the bottom right of your screen. They’re totaled for you there (assuming you haven’t gone in and turned off the Status bar under the View menu, and, let’s face it, if you were that desperate for screen real estate, you really should be out shopping for a larger monitor rather than tooling around the internet reading blogs).

Now, With Pictures…

You’ve got a table with numbers in it:

(The formatting of this table offends my data presentation sensibilities in many, many ways, but it’s the default way that many people format tables, and I don’t want to detract from the core of this tip.)

Let’s say you want to get the sum for January through April. Highlight the data you want to total:

Look down in the bottom righthand corner of Excel (if you don’t see this, select View»Status Bar):

Lookie there! Sum=54,200. Chances are, you never noticed that, and yet it’s been industriously summing away every time you’ve highlighted a range of cells in Excel for years!

“That’s all well and good, Gilligan, but what if I want to see the average for these four months?”

Well, that’s easy, too. Just right-click on the Sum= area on the status bar, and you will get a menu that lets you pick what you want math function that box should perform.

Change it to Average, and, henceforth and forthwith (until you change it to something else), that area will show the average of any set of cells you select.

Pretty handy. And, of course, you don’t have to drag a contiguous set of cells. You can hold down <Ctrl> and select multiple non-contiguous cells (e.g., “What was the total for Jan-07 and Jan-08?”).

Be honest. Did you already know that?

I was on-site at a client for a couple of days last week and, during a break, got into a discussion with their resident CRM system expert. This fellow had managed some crazy stuff in Siebel for a major credit card company and was now working with Salesforce.com and a mishmash of other systems for a nonprofit. We got to talking about data, reporting, and analysis. Inevitably, Microsoft Excel came up — a tool of which both of us are huge fans.

This led to my “Two Ways to Gauge if Someone Really Uses Excel” theory (this fellow clearly did!):

1. Do they use pivot tables? It’s not just if they’ve ever seen a pivot table or struggled to create one in a pinch. It’s, when asked the question, “Do you use pivot tables?” their reaction involves some sort of significant change in facial expression along with a forceful response. Something along the lines of, “Oh my god, YES!!! I don’t know what I would do without them!”
2. Do they use VLOOKUP? Again, the key here is not whether they have heard of VLOOKUP. It’s whether they see it as a function that they simply could. Not. Live. Without!

What is interesting is that both of these features of Excel are database-like functionality. Pivot tables are, basically, a way to do semi-dynamic SQL GROUP BYs (or basic aggregation in MS Access). VLOOKUP is a poor man’s SQL join. But, both are fast (for pivot tables, I’ve learned that I can start the pivot table wizard and then, 99 times out of 100, simply click Finish without going through the intermediate wizard steps to get what I want) and, if data arrives for analysis in Excel, then both are native to the environment the data is already in.

One other note on these features is that, if someone is to the point where they automatically and naturally use pivot tables and VLOOKUP, then it is practically guaranteed that they use string manipulation functions (&, LEFT, RIGHT, MID, LEN, FIND) and conditional and comparison functions (IF, AND, OR, SUMIF). These are every bit as important as VLOOKUP, but, in my experience, they come before or with a VLOOKUP addiction; seldom do they come after.

There is a separate dimension of Excel usage that does not get covered by this test, and that is Excel’s data visualization capabilities. As a matter of fact, up through Excel 2003, pivot tables were particularly difficult to use for charting — charting pivoted data creates a pivot chart, which, while dynamic and manipulable, is pretty ugly. Excel can be used to generate very professional, impactful, clean visual representations of the data. Unfortunately, the tool’s defaults do not do this! But, that is a topic for a whole separate series of posts!

Pie charts are generally bad enough. Mainly, because they take a lot of real estate to provide pretty limited information. But, they do have their place. That place is showing the relative relationship of the parts of a whole when there is no time dimension.

3D pie charts, though, are simply horrid! They actually misrepresent the data and remove whatever instantaneous clarity that a flat pie chart provides.

In the pie chart above, Which product has the greatest portion of the whole?

Product B. That’s not too hard.

Which is greater, Product A or Product D?

Trick question. They’re the same. And, you probably figured that out. But, in order to do so, your brain had to undo the 3D effect, since when it comes to raw area shown, Product A is larger.

When asked a direct question like, “Which is greater, Product A or Product D,” this isn’t too hard to do. But, that’s not usually the approach of interpreting visual displays of data. Rather, the viewer looks at it and says, “What does this chart tell me?” In a 3D pie chart, your brain has to spend extra cycles doing the A vs. D comparison for every wedge in the pie. And it gets pretty hairy when you’ve got, say, 10 or more wedges. What’s happening is your brain has to go through a (subconscious, but real) effort to remove the 3D effect. That’s an effect that somebody else wasted brain cycles and effort on adding in the first place.

This is the sort of inefficiency that process improvement folk salivate over finding in a manufacturing environment: “Person A unwraps a widgetlet and then screws it on to a doohickey and sends it to the next station. Person B then unscrews the widgetlet, inserts a washer, and then screws it back on in the exact same spot.” Obviously, if Person A didn’t screw the widgetlet on in the first place, then the process would have two steps removed: Person A’s screwing on of the widgetlet and Person B’s unscrewing of it.

It’s the same deal with 3D pie charts.

This is my second week in a row in training — just today and tomorrow this week, thankfully. This week, the product is HardMetrics which, frankly, is a pretty damn cool tool. The trainer is the VP of Product Development, who has been architecting and developing in the reporting and analysis space for seven years or so.

HardMetrics actually OEMs a product from Visual Mining for their visual displays. And, there’s a lot of flexibility and power between the two products. However, I asked right off the bat if there was a way to disable the 3D effect from bar charts (there is). I’m not talking about three dimensions of data — just those damn annoying drop-shadows on two dimensions of data.

Below is an example of the 3D effect in a bar chart — quickly and effortlessly generated using Excel 2007.

These sorts of graphs make a southbound feller’s neck hair point due north. Hard! Unfortunately, Microsoft Excel makes it ridiculously easy to spit these charts out. And, as every BI vendor and data visualization tool maker has scrambled to be able to back up their Marketing departments’ claims that their tool will do “everything that you can do in Excel…and then some!” they’ve all gone right along and rolled out the same dastardly functionality.

The problem is: drop shadow adds NO value…AND can make the data harder to read! The reality is, dropping a shadow on a 2D picture is a fairly straightforward transformation. As a matter of fact, something very similar to that is one of the few homework exercises I remember from my C programming class in college (early 1990s).

The Hard Metrics VP who is doing the training admitted that dropping shadows are “eye candy” and help sell the product. That’s. Just. Ridiculous! Unfortunately, it’s also got the faint tingling jingle of truth.

Clearly illustrating data…and making it easy to clearly illustrate data…is what should sell products. If the supposed glitz of a drop shadow is the tipping point with a decision maker at a company, that’s a customer who is focussed on the wrong, wrong, WRONG thing, and, chances are, he/she is going to make other non-value-adding demands of the product. Of course, the real world requires generating revenue, and if there are potential customers who have cash in the bank that matches their misperceptions about best practices, then, well….

Check out the chart above. Quickly…estimate the revenue for Product A in July. Do it. Dont’ keep reading here. Scroll back up and make an estimate!

It looks to be a little more than $10 million, right? Wrong! It’s $10.8 million! Not only is this a 3D chart…but the bars are plopped down right in the middle of no-man’s land — check out the base of the graph. So, you actually have to project the shadow line back (diagonally and up) and then follow the plot over to the values on the left. That’s just silly. But, oh so easy to do in Excel. Thank you, Microsoft!

What absolutely kills me is that, in Excel, you at least have to consciously decide to add this obfuscating crap to a chart. Which too, too many dunderheads decide to do (all too often, I suspect, because they have a bunch of data and don’t know how to interpret it, so they spend extra time and energy making the chart fancier). The killer is that, in all too many analytics programs, this is the default! And, in some cases, it can’t be changed! This was the case with WebTrends OnDemand (sorry, WebTrends — I hate to pick on a solid tool that has a lot of positive features, but, in this regard, it talks, walks, and craps like a duck, so I’ve got to give it a quack out). These are people who should know better. It was a case of perception becoming reality — Marketing decided this was a “cool” feature that everyone else had and didn’t just stand up to not make it their de facto standard. Ugh!!!

Now, fortunately, as I was poking around on the ‘net for some good examples of this abomination…I hit some of the really big players in the BI space…and they had limited use of 3D in the screen caps they showed. I don’t know if that’s because the likes of Stephen Few, Edward Tufte, and the many good folk over at TDWI finally got it through their heads…or if I just hit the wrong pages. (DISCLAIMER: I have never seen Few’s or Tufte’s feelings on this specific subject — they’re two of the most brilliant minds in the visual presentation of data world, so I feel like I’m pretty safe by guessing that they’re not big fans.) It was an encouraging sign.

Some time…when I don’t have four baskets of laundry to fold, I’ll tell you what I really think about pie charts and — the horror! — 3D pie charts! Stay tuned.