It’s really tempting to let terrible Excel graphs creep into your reports. Your boss doesn’t care about little things like how graphs look, right? And whatever Excel comes up with as the default is probably fine … right?
Not really. You’re using data to spur action. Maybe you pull data to convince your boss to take certain decissions, give you an extra sliver of budget, or adjust your team’s strategy, among other things. Regardless of what you use data for, you need it to be convincing — and if you display data poorly, the meaning of your data is more likely to get lost.
To make sure you’re making your data as convincing as possible, you should always customize your graphs in Excel. And by customization, we’re not talking about big sweeping changes. Below are some quick tweaks you can make to your graphs convincing, easy-to-read, and beautiful.
Note: I’m using Excel 2010 on Windows. If you’re using another version or operating system, implementing the following tips should be identical.
1) Pick the right graph.
Before you start tweaking design elements, you need to know that your data is displayed in the optimal format. Bar, pie, and line charts all tell different stories about your data — you need to choose the best one to tell the story you want.
Bar graphs and pie graphs help you compare categories. Pie graphs usually compare parts of a whole and bar graphs can compare pretty much anything … which means often you should just use a bar graph. Bar graphs are easier to read and notice incremental differences between categories, so it’s a good go-to. Pie graphs are best used when one of the categories is way larger than the other.
Want to see the difference? Here’s an example of the same data set displayed as a pie graph and a bar graph:
Line charts help you display a changing trend over time. You can track multiple values over that time, but the key to a line chart is the time component.
To turn your hard data into one of these charts in Excel, highlight the data you want to morph into a chart, then select ‘Insert’ > ‘Charts’ (or choose ‘Charts’ in the top navigation if you have a Mac Version). Then choose the chart most appropriate for your data!
2) Sort bar graph data before designing.
If you’re using a bar graph to display your data, this tip can make a big difference. Most bar graphs look like this:
They’re kinda random. You spend just a fraction of a second too long figuring out which ones are outliers. Instead, you should reorder your data points to go from largest to smallest. Here’s what that looks like:
If your bar graph is horizontal, larger values should be at the top. If your bar graph is vertical, order them from left to right. Why? That’s how people read English (if you’re presenting this data in another language where that isn’t true, change up your order to better reflect reading patterns).
To order the graphs in Excel, you’ll need to sort the data from largest to smallest. Click ‘Data,’ choose ‘Sort,’ and select how you’d like to sort everything.
3) Remove background lines.
Graphs allow you to roughly compare data within a set, not dig into it. No one’s looking at your graph to see incremental differences between data points — they want to see general, overarching trends.
To help people focus on those trends, remove the lines in the background of your graph or chart. These lines are superfluous, unhelpful, and distracting — cut them from your graph to help people focus on the big takeaways.
To remove background lines, choose ‘Chart Layout’ then ‘Gridlines,’ then choose ‘None’ under ‘Primary Horizontal Gridlines’ and ‘Vertical’ options.
4) Remove unnecessary styling.
Most standard Excel graphs come pre-styled … and these styles will often get in the way of communicating information. Shadows? Outlines? Rotations? Get rid of them all. They don’t add to the data’s story.
To fix the styling in Excel, right click on the graph and choose ‘Format Chart Area.’ Remove all extra and unnecessary effects on your data:
5) Stay away from 3D effects.
This falls under the previous bullet point, but I wanted to include it as its own point because it’s one of the most overused data visualization effects.
To make data look extra fancy, people will often make bar, line, and pie graphs 3D — but it actually just makes the data harder to read. Because of the way the data is tilted, it gives the reader a skewed sense of what the data actually means. Since you’re using data to tell a broad story, you don’t want to weaken your argument due to poor design. See how different a pie chart looks when it’s in 2D versus 3D?
And if you actually look at the area each section takes up on the screen, you’ll see why it’s easy to misinterpret 3D graphs:
To remove the 3D styling from your graphs, double click on the bars, lines, or pie sections you’d like to change, choose ‘3D Format’ and set ‘Top’ and ‘Bottom’ to ‘None.’
6) Use legends strategically.
If you’re only comparing a few data points, legends can be pretty useless — they tell you information you can read easily on the graph. When you have a ton of X-axis categories or multiple data points per category, legends make sense. Until you have data like that … just delete the legend altogether.
To do this in Excel, you can just double click on the legend text box, then hit ‘Delete’ on your keyboard:
7) Include branded colors.
The colors that come preset in Excel are pretty drab — one quick way to spruce them up is to make them your brand’s colors. It’s a little detail that’ll make your charts look slick and clean.
To make sure you’re always using the proper brand colors, you’ll need to grab the HEX color code of your brand’s colors. Then, plop that code into this HEX –> RBG color converter. In Excel, double click on the parts of your graph you’d like to change the color of. Under ‘Fill’, Select ‘Solid Fill’, choose ‘Color’, select ‘More Colors’ and then ‘Select Custom’ tab.
Then input the RGB numbers you found in the beginning. Then, voila! Perfectly on-brand colors and a gorgeous graph.
What other little Excel graph tips do you have up your sleeve? Share them with us in the comments.