How to craft good-looking plots with Excel
Crafting good-looking charts in Excel isn’t possible? Well, it is, and here’s my path to a nice-looking chart created with Excel.
For most visualizations, I use tools like Datawrapper or Tableau. They offer a more or less simple way to visualize data nicely. But occasionally, you cannot use an external tool, maybe because of compliance guidelines, or you want to reuse your charts in complex reports. Excel is a standard in most companies and many financial reports are created with more or less beautiful visualizations. This gave me the idea to bring a visualization in Excel to the design level of a Datawrapper default theme. And to be honest, it’s easier than I thought. Excel is capable to create clutter-free and nice-looking charts.
Disclaimer: The purpose of the example plot is to show that actual numbers are not higher than before. It is not about the exact numbers, but to quickly see the progression. The question it should answer is:
Does Germany actual test more for COVID-19 infections than the weeks before?
First, let’s take a look at the desired result. It’s a line chart I’ve created with Datawrapper and saved it as PNG.
When I use the same data with Excel and create a line chart I get this default result:
It’s not bad at all. However, it lacks elegance and clarity. The axis labels are not beautiful and the heading is rather loveless.
Let’s dive into the steps I’ve taken to transform a default Excel chart into a well-designed clutter-free chart.
Clean up and format axes
The first step is cleaning the x- and y-axis and simplify the labeling. I’ve reformatted the labels and the goal is to show a large number in an abbreviated form.
Double-click on the y-axis shows every formatting option. Set Bounds minimum to 0 and maximum to 2000000. Since I don’t need any detailed steps I’m configuring Units major and minor to 1000000. Now we’ve got just 0, 10000000, and 2000000 for our y-axis labels.
Honestly, the labels are bulky, we need to reformat them. That’s a bit more tricky — maybe the most tricky part at all. For abbreviations, the numbers must be formatted. Go to the number section in Format Axis and add a new format code. I’ve used [>0]#.."M";"".
What happens there? It’s conditional formatting with two parts separated by ;. Let's break it down:
[>0]#.."M"means “For every Number greater than 0 abbreviate millions with M.""says just add nothing at all. I’m using this to omit 0 for our base axis.
As you can see, I don’t care for negative numbers. Add the formatting by clicking “add”, and the y-axis labeling is clean and nice.
Now we get our hands dirty at the x-axis. In our final plot, we don’t need all the labels. As before, double-click on the x-axis to open the formatting options. To have labels like Datawrapper generated, it’s necessary to set Specific interval Unit for labels to 6. This means only every sixth value is used.
That’s it for now. We’ll get back to both axes later when we are adjusting colors, fonts, and line width.
Adding annotation
My visualization should include the most recent number at the end of the line. Select the most recent data point, right-click and select Add Data Label.
The position of the label isn’t perfect. There isn’t enough space at the right. We need to decrease the size of the chart. It’s important to select the plot area, not the chart area!
Decreasing the size, our label is right beside the data point. If not, just move it where you want to see it. We are formatting the font and color later.
A beautiful header
Default headers are ugly in Excel. You cannot format them properly. There is no chance to align, nor to add a sub-line. That’s why I’ve just removed the header and added two new text-areas with a headline and sub-line. To have enough space for your headlines you may need to resize the plot area again. Now just add a text box using Insert -> Text Box in your Excel ribbon bar.
Add some beautiful typo
As we are adding our headlines, let’s talk about font sizes. For proper font sizes, I am using Type-Scale.com to get an idea of how the sizes affect users. I’m using Roboto as font and the following sizes.
- Headline: 22
- Sub-line: 14
- Axis labels: 12
- Footer: 9
To set and resize fonts just click on the label, axis, or text box and set your values using the font-selector at the Start ribbon in Excel.
The same applies to the colors. I’m using #888888 for axis labels and #000000for headline and sub-line.
Fine-tuning
Finally, let’s fine-tune the visualization. The First step is to have a slightly wider baseline. Double-click the x-axis, select Fill & Line and set Width to 1pt and color to #000000.
Thereafter, we lighten the lines for y-labels. Double-click at the line at 1M and set color to #D9D9D9 and Width to 0,75pt.
If you like, you can round off the curve and adjust the color. Once again double-click the line of your chart and select Fill & Line → Line and activate Smoothed line. I’ve set the line width to 2pt and color to #B92717
Don’t forget to add a footer. You can insert your name and links to your sources for this chart. Just add another text box and move it to the bottom of your chart. You may have to decrease the plot area again to be able to maintain reasonable distances.
That’s all. Here’s a comparison of the chart plotted with Datawrapper and my chart created using Excel
Save it as a template
There are many steps to get a good-looking visualization. And to be honest, I don’t want to go through every step again and again. Fortunately, Excel allows us to save a template with all our settings. Right-click on the chart and select Save as Template.
With a template, we can apply our new styles on a chart. Just plot a default diagram, right-click it, select Change Chart Type and choose from Templates
Conclusion
You see it isn’t really difficult to create nice-looking visualizations using Excel. You don’t need to live with default plots. That doesn’t mean they are useless. If you want to visualize contexts to gain insights, defaults are good enough. But for some reports, it’s worth spending some time to create clutter-free and nice-looking charts, without using some other tool.
If you want to create good-looking diagrams, you may invest some time into customizing and save them as a template using your CI and with good-looking typography.
