Software

Office challenge: How would you improve a too-busy Excel chart?

This week's Microsoft Office Challenge is based on a practical problem: How do you present a large number of data points in an Excel chart without overwhelming your readers?

An Excel chart, based on a large number of values, is too busy to be effective! In addition, the legend is huge and unmanageable. Your boss wants a neater, sharper chart. What do you do?

Last week we asked…

How would you improve this Excel sheet? The example sheet has formulas and formatting, but it's not quite ready for prime time. We asked you what you do to get it ready for distribution. Agency shared a procedure to clear the input cells. Vijaym suggested more formatting and enabling protection. These are some great suggestions. Agency's macro works well, but selecting A6 clears the input cells. Users will complain, even if they're forewarned because the macro might clear the input cells before they're ready. The macro is good, but clearing the input cells should probably be part of the actual input process.

I'm going to leave this challenge open for another week—does anyone know how to move the clearing action to the input process?

Thanks Agency and Vijaym for the great suggestions!

About

Susan Sales Harkins is an IT consultant, specializing in desktop solutions. Previously, she was editor in chief for The Cobb Group, the world's largest publisher of technical journals.

6 comments
Dean Spasser
Dean Spasser

For me, even though your sheet is too busy, you will effective manage it by sorting and stacking the necessary data neatly. Use another sheet but in the same file, if necessary. - Dean Spasser

Mark.Mathews
Mark.Mathews

I have used the technique or having some data shown as a stacked bar and some data shown as data points only (line graph without the line, only showing the data points)

dan
dan

I like to display data points in a faint gray, then overlay bold statistical summaries (e.g. regressions and dashed standard error bars) on top. You can see the data if you really want to, but the first impression is clean and easy to interpret. Base the legend on the stats - limit the fields to make it readable.

ppg
ppg

The solution depends on what type of data you have. If you have sales figures for 100 salepeople you could group by region or product. You could then create additional charts for each region. If you have temperatures taken every day for two years you could draw a continous line and possibly apply averaging. If you are only interested when the temperature went out of a specified range you could generate a seperate column of only those values and plot and annotate them them as a seperate line. If you still have too many points to plot you probably have bigger problems than just how to plot the data.

bboyd
bboyd

Group lesser data sets within the set and present them with separate charts.

toadforce
toadforce

Only label every 10 or 100 or whatever values; choose a continuous chart type, not bars. Or just present some stats, min, max, mean, sdev. If it has a time dimension, display the stats for each week or month.