by Charley Kyd on June 2, 2012
In a chart of business performance, an upward-sloping line could indicate good news or bad.
If the chart shows profits or sales, an upward-sloping line is good news. But if the chart shows expenses or measures about problem areas, an upward-sloping line, or a rising series of column plots, shows us bad news.
So when a report includes a mix of such charts, how can we help readers to interpret each chart trend quickly and correctly?
The Wall Street Journal uses indicators like the ones shown here. And you can add similar indicators to your own chart figures…if you know three tips.
At first glance, the solution looks obvious. This obvious solution uses two of my three tips. Both of them also work in Excel 2003. But because the third tip only works with New Excel (Excel 2007 and after), I’ll discuss only that program. [click to continue…]
by Charley Kyd on May 12, 2012
For years, The Wall Street Journal has been the most reliable source I know of high-quality charts and tables. Most of their work has been excellent.
Sure, they occasionally use fat-food chart types…doughnut and pie charts. But even those tend to be free of gratuitous junk.
But on April 17, 2012, the WSJ fell off the wagon. They published this horrible chart figure:
Here, the background image acts as camouflage. Instead of forcing readers to search for substance in the clutter, the WSJ should have [click to continue…]
by Charley Kyd on April 5, 2012
For years, I’ve written that we Excel users should create “magazine-quality” charts for our reports and analyses.
However, we must be very careful of the magazines and other publications we emulate.
The Wall Street Journal currently is my favorite source of great chart examples. Most of its charts and tables are very well designed. The Economist magazine also has been a great source. Both Business Week and Fortune used to be great sources, but now publish very few charts.
Unfortunately, if their special issue “The World in 2012″ is an indication of things to come, The Economist magazine should be moved to the do-not-emulate list.
Unlike Business Week and Fortune, however, The Economist still is instructive for Excel chart-builders. But now, that magazine teaches us what not to do.
In the figure above, for example, [click to continue…]
by Charley Kyd on March 25, 2012
These two charts show different versions of the same random data. Notice that as the order of the chart series changes, the legend adapts automatically.
When I saw a chart with a legend like this in the Wall Street Journal, I knew I had to add a similar chart to my Swipe Files…but in Excel, we need dynamic legends!
Using dynamic legends is such a great idea, I also need to blog about it.
What’s A Dynamic Chart Legend?
Dynamic chart legends change as your data changes.
For example, this legend automatically adjusts so that the information about the top line also is at the top of the legend bar, and the information about the bottom line is at the bottom of the bar, and so on.
[click to continue…]
by Charley Kyd on March 22, 2012
If Google search results are any indication, Excel’s VLOOKUP function is about 30 times more popular than the INDEX-MATCH function. This is too bad, because …
1. INDEX-MATCH is much more flexible than VLOOKUP.
2. At its worst, INDEX-MATCH is slightly faster than VLOOKUP; at its best, INDEX-MATCH is many-times faster.
I can think of only two reasons you ever should use VLOOKUP (or HLOOKUP, which does the same thing, but sideways). First, [click to continue…]
by Charley Kyd on February 18, 2012
A recent post in a public forum asked whether Subject Matter Experts (SMEs) ever will drop their spreadsheets in favor of a dedicated budgeting application.
I think the answer to this question is NO. Most SMEs will continue with Excel for budgeting and forecasting, for excellent reasons. Here are a few of them:
1. Excel allows SMEs to begin with their own assumptions, and then build on them. Excel doesn’t force SMEs to adapt to a system created by anonymous programmers on a tight schedule.
That is…
- Excel serves as a ramp. It offers a way to start off gradually and then rise as your knowledge and demands require.
- Excel allows every company to adapt its forecasting and budgeting model to the company’s unique environment.
2. Excel offers the ability to create more sophisticated models than most 3rd-party packages offer.
Third-party Business Performance Management (BPM) companies tend to trivialize Excel. If you want REAL power, they tell us, drop Excel and get our program!
However, Excel offers more power to analyze, calculate, manage exceptions, find outliers, report, and so on than every budgeting package I’ve heard of.
If you need REAL analytical power with your budgets and forecasts, use Excel. [click to continue…]
by Charley Kyd on December 18, 2011
Have you ever griped about an Excel limitation and then suddenly realized there’s a simple solution? That just happened to me.
For a long time, I’ve wished that Excel would anti-alias large font sizes. Without anti-aliasing, Excel’s large fonts have jaggies, as this section of a large letter “B illustrates.
Fonts don’t need to be THIS large for the jaggies to be obvious. You often can see the jaggies when fonts are larger than 20 points or so. But the larger the font, the more obvious the jaggies become.
This morning, I was working on a new dashboard report, which uses a headline of 96 points; so it has bad jaggies. Then I had an idea, which you can see here:
[click to continue…]
by Charley Kyd on November 16, 2011
This figure illustrates a great Excel question that a friend asked today.
His original question was, “In this table, how can I return the date where the lowest value occurs?”
More generally, however, he was asking how to look up a value in two dimensions. This is a different challenge than most Excel lookups, which are limited to a single row or column.
To keep the explanation as simple as possible, I began by [click to continue…]
by Charley Kyd on November 13, 2011
I talked recently with an Excel user who’s making a great living from Excel reports. Because many Excel users are looking for jobs, I thought someone else could use a similar idea.
The idea is based on one that I first heard about as a teenager.
My best friend’s father owned a car dealership. One day, my friend said that his father was [click to continue…]
by Charley Kyd on November 13, 2011
If you want to show tabular data in Excel dashboards, Excel’s Camera tool is the most useful tool you can have. However, Excel 2010 has a bug that appears to limit the usefulness of Camera tools.
(If you don’t know what the Camera tool is all about, I devote a chapter to it in Dashboard Reporting With Excel.)
Here’s the bug: When you print a page that includes the Camera object, Excel sometimes adds row and column titles to the image, so that it looks something like the following figure in your printed results:
Honestly, I don’t know what causes Excel to display these headings. But I do know how to stop them. This figure shows the problem: [click to continue…]
