10 x 10 Tips to improve your (Excel) charts: general tips

This is the first in a series of 10 posts where I’ll suggest a (hopefully) coherent set of tips to improve our charts and, more important, to improve the way we make sense of the data. These are the planned posts:

  1. General charting;
  2. Formating;
  3. Column/Bar charts;
  4. Line charts;
  5. Scatterplots (XY charts);
  6. Pie charts;
  7. Other chart formats;
  8. Dynamic charts;
  9. Dashboards;
  10. Miscellaneous tips;
  11. Bonus post: online resources.

These will be very short tips, and I’m sure some of them will require further explanation. In time, all of them will be properly linked to a more detailed post (that will keep me off the streets for a while…). This is a kind of road map for the next few months and, when finished, a (partial) table of contents for the blog.

So, general tips on charting:

  1. A chart shows trends, patterns, outliers; if you already strive to make them apparent, you don’t need to read the next 99 tips…;
  2. Do you really need a chart? Sometimes the task and the data suggest another method of data analysis;
  3. Know your audience. If your audience is uncomfortable with some formats your message will be lost;
  4. Make sure you have enough data to create a pattern (two data points are not enough to create a trend line);
  5. Make sure you don’t have more than enough data: just because you have it, you don’t have to show it…; keep removing interesting data until only relevant data for your problem remains;
  6. A chart should be able to answer elementary, intermediate and global questions regarding the data;
  7. Don’t assume that the charts you see in the media are the ones you need to run your business;
  8. Learn how to lie with charts and, of course, avoid those lies;
  9. Let the reader see related charts simultaneously;
  10. Chart overload is as bad as information overload.

It’s your turn, now. What are your best tips in this category? Please share them in the comments (if you have specific tips please save them for the next posts). I believe that Tufte’s principles (avoid chart junk, maximize data/ink ratio, high data density…) are already implicit in some of these tips.

7 thoughts on “10 x 10 Tips to improve your (Excel) charts: general tips”

  1. A corollary to 5:

    5.1. Make sure you don’t have more than enough formatting*. Just because you have to ability to apply it, doesn’t mean you have to show it. Keep removing formatting features until only the relevant formatting for your problem remains.

    *More than enough formatting includes too many colors, any color gradient effects, gratuitous fill patterns, 3D effects where the data exists in only two dimensions, shadow and glow effects, and excessively bold lines or text.

  2. “keep removing interesting data until only relevant data for your problem remains”? I hope you mean uninteresting.

    Perhaps you could also describe the scope of your suggestions a bit more – they seem to be aimed a business people using excel graphics for communication?

  3. Good idea, but… What do you mean by “(Excel) charts”? Charts created with MS-Excel? What about (OpenOffice) charts or (R) charts or (Stata) charts… ?

  4. Bernd: I’ll try to keep the tips as generic as possible, but a large majority of charts are done in Excel and I may have some tips that apply to the way charts are designed using it. I will not be able to cover R or Stata but I have StarOffice installed and I’ll try to use it.

    Jon: you’ll like the next post on formating…

    Hadley: I do mean “interesting”, in the sense of “interesting but not essential”. We often are distracted by “interesting” facts that don’t really add to our knowledge. Interesting data can be used to provide context but the chart designer should ensure that there is a visible difference between relevant and interesting.

    Regarding the scope, you are right. I use Excel and BI tools in a context of business intelligence for analysis and communication. So it is difficult for me not to be biased towards the needs of a business environment.

  5. “Know your audience. If your audience is uncomfortable with some formats your message will be lost”

    As a BI consultant I would rather say: Grow with your audience. In the long run there’s no need to limit your charts to those few formats that work in the first meeting.

  6. I have to create an Excel Dashbvoard for my work. I use Excel 2003. I have created different charts, such as line, column and p9e charts and pasted them on Powerpoint slides and called it my dashboard.
    But this is not what i really want.
    What i would like to see in my dashboard is that my charts are dynamic charts.
    The charts should be connected to source data. And I can show what happens if we change one of the parameters that the chart lines or column will change. (something like creating a pivot table chart which will show what I select.

    I cannot create a pivot table because my tables do not have any calculations.
    It is showing total No. of rquisition issued and received by different areas and year/year comparison.
    Two columns for two different years, and two lines showing total No. of requisitions issued and received. This does not requiredany pivot table.
    So what is the trick for creating a dynamic chart that will show the movement which is what i see on Dashboard charts. Besides I donot know macros or VBA.
    I have created very colorful and self explanatory charts and pasted them in PP slides.
    But i cannot attach any movements.

    Is there anyway to learn this or Is it possible to create charts such as the ones i described in my previous paragraphs?
    Is there a simple tutoria that I can watch and see how it is done? Is it possible to do it in Excel 2003?
    I would appreciate any help you can provide.


Comments are closed.