Pro Excel Chart Formatting

Mynda Treacy

July 11, 2023

Charts provide a visual representation of data, making it easier to understand and interpret large amounts of information quickly.

At least that’s the objective, but too often poor choice of chart formatting is more of a hindrance than a help.

In this tutorial we’ll look at the types of Excel chart formatting that is an instant give away that you’re new to data visualization, and the formatting that pros would choose instead.



Watch the Video

Subscribe YouTube

Download PDF

Enter your email address below to download the PDF containing all the pro tips in this blog post.

By submitting your email address you agree that we can email you our Excel newsletter.


3D Charts

Don't use 3D Charts

At best 3D effects add zero value to data visualization and at worst they can distort the results and mislead. The pie chart below illustrates this with the red segment falsely appearing far bigger than the yellow segment:

Don't use 3D Pie Charts

Don’t use 3D charts, EVER.


Effects

Dark fill colour, gradient fills and other gimmicks detract from the data.

Dark Background on Chart

Let the data be the star of the show:

Clean, clear chart background

And avoid the Chart Styles in the Chart Design Menu:

Avoid chart styles from Excel Design menu


Vertical Axis

Column chart’s vertical axis should always start at zero because we subconsciously compare column heights and make judgements based on the difference in size.

Just take the chart below from Airwallex illustrating their fees compared to other companies.

They have purposely hidden the vertical axis so you can’t see that it starts way above zero.

The difference between the column heights makes Airwallex appear 75% cheaper than their nearest competitor, when in reality it’s only 31% cheaper:

Misleading chart axis does not start at zero

This is a common marketing ploy to distort the truth. It’s effective because we subconsciously compare the height of the columns and make judgments. This is how the chart should look:

Correct chart using zero on axis

Note: The axis starting at zero is less important with line charts because we tend to refer to axis labels to interpret one line’s position in relation to others.


Axis Labels

Don’t make people turn their head to read your chart labels.

Don't put chart labels on an angle

If your axis labels don’t fit horizontally, then use a different chart, like the bar chart below:

Use a bar chart for long axis labels


Sorting

Sorting data takes the work out of ranking making your charts quicker to interpret:

Sort the data to make it easier to interpret


Chart Choice

Stacked charts make all but the first series difficult to compare. Better to use a line chart if the point of the chart is to compare the different series:

Choose the correct chart


Labels

Excessive labelling clutters and distracts.

Don't use excessive labelling on charts

Only label key data points:

Only label key data points


Legend

Legends slow down interpretation because we have to flit between the data points and the legend. Better to directly label the data:

Avoid legends, label the data instead


Titles

Chart titles are often wasted on stating the obvious.

Don't state the obvious in a chart title

Instead, use this prime real estate to communicate the key message:

Use chart title to communicate important information

Bonus tip: notice the alignment of the title is left in line with the axis. This makes the chart appear tidier than a centred title.


Gridlines

When you have data labels and vertical axis labels, the vertical axis labels and gridlines can often be redundant. Consider whether you really need both:

Turn off gridlines and axis labels if data is labelled

What other amateur chart habits did I miss? Share them in the comments.

AUTHOR Mynda Treacy Co-Founder / Owner at My Online Training Hub

CIMA qualified Accountant with over 25 years experience in roles such as Global IT Financial Controller for investment banking firms Barclays Capital and NatWest Markets.

Mynda has been awarded Microsoft MVP status every year since 2014 for her expertise and contributions to educating people about Microsoft Excel.

Mynda teaches several courses here at MOTH including Excel Expert, Excel Dashboards, Power BI, Power Query and Power Pivot.

4 thoughts on “Pro Excel Chart Formatting”

  1. Pie charts – just don’t (unless you actually want to have a pretty picture to fill up the Board’s monthly PowerPoint presentation – in which case, I’d recommend the exploded 3d doughnut version with gradient fill)
    and, as you’ve demonstrated, less is more (2 colours, 1 font, everything should be there for a reason – no superfluous data, formatting or text)
    “everything” includes the white space – which is sometimes the most important thing

    Reply
  2. Very nice tips Mynda! Thank you.

    I was about to write a comment that the stacked bar chart seems to be a not-so-good choice to convey the category-wise 3 years’ sales figures!
    Then I thought that it’s quite unlike you to make such an elementary mistake… ha ha!

    So I re-read the post and realised that you were improving the chart presentation with each tip as you went down the list of tips. 🙂

    Reply

Leave a Comment

Current ye@r *