It's really tempting to let terrible Excel graphs creep into your marketing. 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 adopt inbound marketing, 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 ensure 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 tips for how to use Excel to make your graphs convincing, easy-to-read, and beautiful.
Note: I'm using Excel for Mac 2011. If you're using another version or operating system, implementing the following tips may look different.
Excel Design Tricks for Sprucing Up Ugly Charts and Graphs in Microsoft Excel
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, while bar graphs can compare pretty much anything ... which means it's often best to just use a bar graph. Bar graphs are easier to read and highlight incremental differences between categories, so they're 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, which look kind of like a horizontal version of bar 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 data into one of these charts in Excel, highlight the data you want to morph into a chart, then choose "Charts" in the top navigation (or select "Insert" > "Charts" if you have a different version). Then choose the chart most appropriate for your data.
2) Sort bar graph data so it's intuitive.
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) Shorten Y-axis labels.
Long Y-axis labels, like large number values, take up a lot of space and can look a little messy, like in the chart below:
To shorten them, right-click one of the labels on the Y-axis and choose "Format Axis" from the menu that appears. Choose "Number" from the lefthand side, then "Custom" from the Category list. (De-select "Linked to Source" if it's selected, otherwise you won't be able to click "Custom.")
Enter the custom format code \$0,, \m (as shown below) and click "OK" to close.
The resulting graph will look much cleaner:
4) 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" > "Gridlines," then choose "No Gridlines" under "Horizontal" and "Vertical" options.
5) Remove default line margins.
Unless you remove them from your line charts, Excel will automatically add margins before the first data point and after the last data point, as you can see on the left in the image below. But, as you can see on the right, line charts often look better without these margins:
Most standard Excel graphs come pre-styled -- but 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:
7) 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?
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."
8) Delete the legend if it's not necessary.
Legends tell you information you can read easily on the graph. If you have a ton of X-axis categories or multiple data points per category, then using legends makes sense. But if you're only comparing a few data points, they can be pretty useless. So if that's the case, just delete the legend altogether.
To delete a legend in Excel, you can just double-click on the legend text box, then hit "Delete" on your keyboard:
9) 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 ensure 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," choose "Color" > "More Colors."
In the popup window, select the second icon from the left with the sliders. See that little dropdown? Choose "RGB Sliders," then input the RGB numbers you found in the beginning. Voila! Perfectly on-brand colors and a gorgeous graph.
10) Add a shaded area to a line chart.
Ever seen a line chart with a shaded area below the line? They can make your line chart stand out.
To add in that shaded area, you have to trick Excel by adding another data series. To do it, go back to the table in Excel you used to create the line chart, and highlight the data points that make up the Y-axis (in this case, the dollar amount). Then, copy it and paste it to the row below so there are two identical data series.
Next, highlight the data values only of the two identical data series -- not including the labels. Below, this area is highlighted in blue.
A line of a different color (in this case, red) will appear on top of our original line on the chart. Click the line to select it, then right-click and choose "Change Series Chart Type" from the drop-down menu.
In the menu that appears, choose the first type under the "Area" category.
Next, double-click the shaded area on the chart (in this case, the red area) and a "Format Data Series" menu will appear. Click on "Fill" from the lefthand side, and choose "Solid Fill." Under "Fill Color," choose the same color as the line in the chart. You can change the transparency to whatever you'd like -- a transparency of 66% looks good.
Then, under "Border Color," choose "Solid Fill." Under "Fill Color," choose the same color as the line in the chart. Change your transparency to the same transparency as the border color's transparency.