Spreadsheet Charts - Clarification, Not Confusion

It is truly amazing how spreadsheets continue to evolve to make it easy for a user to do a task. Charts are a perfect example. Not that long ago creating a chart involved a lot of steps to get it right. Now, once your data is set in the spreadsheet, Excel or Google will make suggestions for you as to the best format to use.

Even so, it is surprising how often I see charts that are just not as effective as they could. I put "bad" charts into three categories:

  1. Too much data on the chart

  2. Using the wrong type of chart

  3. Poor use of color on the chart

The purpose of a chart is to visually make your point about the data being analyzed. You want the user to be able to see the chart, then immediately understand the data and results of the data analysis.

Here are some general tips that will help you make the chart easy to interpret:

  • Modify the chart layout so all data series are clearly visible

  • Modify the scale of chart axes to clarify the chart output

  • Choose the best chart to express the meaning of your data

The type of data you show or analyze will determine the best chart type to use:

  • Time series - Column or Line Chart

  • Parts of a whole - Pie Chart

  • Multiple series - Stack Bar chart

  • Cause and Effect - XY or Scatter chart

There are many excellent resources on the internet if you want to learn even more about proper charting techniques. Here are just a few that I recommend:

Peltier Tech Blog - if you only look one place for info, this is the site to visit.

Optimize Smart has an excellent article titled "Best Excel Charts Types for Data Analysis, Presentation and Reporting". Please note that when I visited, I got a popup that offered a free ebook titled "Best Excel Charts for Data Analysis and Reporting.

Tom Kleen has an interesting site that is a great Excel resource. Three pages are of particular interest and I recommend a visit:

So for simple charts, you are probably fine using the spreadsheet's built-in features. However, if you want to be sure your charts are effectively conveying your message, take time to visit the resources above, as well as other information available, to learn how to create the best chart for your data every time.

5 views0 comments