How To Fix Your Ugly Chart

Dave Bruns's picture
ShareThis

Microsoft Excel is almost legendary for its ability to create ugly charts. The default settings often contain too much “bling.” Chart controls are numerous and intimidating, so many people choose to just leave them alone. As a result, you’ll see a lot of charts that look like this:

This chart is overly complicated and difficult to digest. But following the simple steps below, we can easily improve this chart. Are you ready? Great, let’s do it.
 

Have a Focus

Before you start working with a chart, make sure the chart has a clear purpose. You should know your audience and have one main message you want to focus on in the chart. In this case, let’s assume that the audience is management and the “so what” or “takeaway message” is that Q3 sales were very good and, in fact, were the highest ever.

Remember: if you aren’t clear on the “so what” message yourself, maybe you don’t need a chart at all.
 

Eliminate clutter

The single most effective thing you can do to improve your charts is to eliminate clutter. By default, Excel charts often include gridlines, legends, and tick marks. For a simple chart like this, we can eliminate them all. The legend is completely unnecessary, the tick marks don’t serve a purpose and we’re going to make the gridlines unnecessary when we add data labels.

Excel has a ton of chart controls on the ribbon, so when it comes to removing things it’s easier to just select the item and press delete. Do this for both the legend and the gridlines.

For the tick marks, double-click the X-axis to open the Format Axis dialog. Then navigate to Ticks and set major ticks to “None.” We’ll handle the Y-axis separately below.
 

Directly label with data

The idea of “direct labels” is to place data directly on the chart, so that readers don’t need to scan back and forth between axis labels and data bars. To add data labels to the bars, click once to select the bars, then navigate to Chart Tools > Layout > Data Labels > Outside End. Then select and delete the Y-axis. Our chart now looks like this:


 

Simplify number formats

Things are looking better, but we can make the chart more human-friendly by simplifying the number format used in the data labels. Double-click any label to open the Format Data Labels dialog. Then navigate to Number, and uncheck “Linked to source.” In “Category,” select “Custom,” then add and apply this number format: $0,"K".
 

Add a title that reinforces the “so what”

It’s time to add a title that makes the takeaway message clear. Select the chart and visit Chart Tools > Layout > Chart Title to insert a title. You can also add a new line to the title and add other important information below the title in smaller type. Now we have:


 

Use color to emphasize key findings

Color can be a fantastic way to emphasize a message, as long as it’s used in moderation. To punch up the message in this chart, we’ll format the data bars gray and the Q3 data bar in green.  We’ll also ditch the gradient and shadows on the bars at the same time:


 

Details

We can add a bit more refinement with two final tweaks. First, we’ll increase the width of the bars (double-click and visit Options > Gap width). Finally, we’ll move the data labels inside and color white (double-click and set Label Position to Inside End). Here is the final result:

 


About the Author: Dave Bruns is co-founder of exceljet.net, a resource to help people work in Excel faster. If you use Excel frequently, download his free PDF "220 Excel shortcuts for Mac and PC, side-by-side"

shadow