5 Steps to Creating Beautiful, Eye-Catching Charts in Excel

I decided to start with this post for a few reasons. First, it’s the most basic but useful analytics topic. Second, data design is critical in communicating stories and insights to your audience. Third, and most importantly, I’m writing this because each time I open a PowerPoint presentation and see the standard excel colors, no labels, or tables instead of graphs, a little part of me dies.

Below are 5 simple steps to designing charts, which can be both art and science. The example I walk through is Excel, but the major themes in the post apply to any analytics programs, graphing tools, or infographic creators.

11-1-2013 7-56-15 PM

The chart to the right (or above if you’re on mobile) is what you get if you highlight columns in Excel and insert a chart. It’s the standard out-of-the-box output that Excel offer. Yikes.

Here’s how to fix it…

1. Include The Essential Components

Always include axis labels, chart title, and the legend. These components are necessities for a few reasons. It’s possible – actually more likely than not – your reader will have little or no context before seeing your chart.

I know, I know. Us analytics people tend to think everyone is paying attention, but for the sake of data design, it’s best to assume the audience hasn’t read the headlines you put on the slide, or the bullets, or even the details in your email. It’s also important to include the essentials for when people start sharing your awesome chart.

In Excel, the labels, titles and legend options can be found in the ribbon:

2 - How to Add Necessities

2. Mute/Lighten Everything That Isn’t Important

Black text will draw the eye away from the data; change each element to different shades of gray. Start with the grid lines, which are important for comparing data points, but shouldn’t compete for attention. Lighten the title, axis, axis labels, and legend. I’m a fan of the shade of gray below.

4 - Gray - Gridlines

3. Choose Colors Carefully

Choose colors that put emphasis on the right data points. If you have a line, bar, or bubble that is necessary, but not part of the story you want to tell, give it a color that is lighter as to not distract the reader.

Choose a color scheme that is “on brand” or part of an aesthetically pleasing color palette. If you work with clients, consider creating a template in Excel that uses brand colors. If you aren’t working with a brand, you can Google color palette generator and pick one that suites your needs.

Update lines/data points to reflect colors in the palette. I decided on the color palette below which contains a bold set of colors with light and dark options.

6 - select a color pallate

4. Add Critical Information and Annotations

Add additional information such as data sources, dates, and annotations that come from the analysis. The data source used in this example is from Google Analytics. The data used in this post was from a generous small business that was willing to provide anonymous data in exchange for insights.

Noting date ranges are important, specifically when the chart does not display time/date on the x axis.

The last, extremely useful piece of information you would have in a chart is any relevant annotation that comes as a result of the analysis. In addition, annotation help show what action was taken or show what changes were made on a certain day or period.

5. Adjust The Scale

Scales should accurately represent the trends or story in the chart. I’ve seen hundreds of scenarios where data is misrepresented because of deceitful scales. More often than not, it’s unintentional, but it can cause trouble.

There you have it – A beautiful, eye-catching chart with labels, accurate title, annotations, date, and sources of data.

Final Product

8 - Annotations and Details

You may also like...

1 Response

  1. Rob says:

    This graph would be better if instead of a legend, the series names were displayed right next to the end of the lines. It’s more elegant and requires less processing, i.e. the message of the data series becomes clearer.

Leave a Reply

Your email address will not be published. Required fields are marked *