Quick Links
Excel offersmany different tools for formatting your charts, but the key to success is choosing which ones to use. In this guide, I’ll share some of my favorite chart-formatting hints, hopefully giving you some new ideas or rekindling some old-school methods you learned years ago.
Excel has many preset styles and designs in the Chart Design tab. However, in my opinion, many of these make the chart difficult to interpret. For this reason, I prefer to make my own formatting changes, and I recommend you do the same.
1Add a Chart Background
Charts don’t need to be boring. In fact, on the contrary, they can be essential graphics that draw people’s attention and present data in an interesting and clear way.
One way to make your charts stand out is by adding a background. In this example, I’vereplaced my columns with pictures of buildingsand added a thematic sunset background to make my chart eye-catching and impressive.
Even using a subtle color tint can help distinguish your chart from the rest of the spreadsheet and make your data stand out. Either way, try to verify there’s a clear contrast between your background and your data points.
To do this, double-click the edge of the chart, and having selected the paint icon in the Format Chart Pane, expand the “Fill” menu.
Now, select the fill type you want to use. If you click “Solid Fill,” select the background color, and then change the transparency using the slider.
If you want to use an image, click “Picture Or Texture Fill.” Then, click “Insert,” and select the picture source. In the building chart above, I used a stock image from Microsoft’s extensive gallery.
The main thing to remember here is that your chart needs to be easy to read. Adding too bold a color or a textured background might cause issues, so aim to strike a balance between clear and impressive formatting.
2Change the Axis Minimum and Maximum Values
One of the more recent chart formatting tools I’ve discovered relates to the charts' axes. In this example, the data is squeezed into a narrower corridor in the chart than is necessary because the y-axis range (0 to 3000) is broader than the data requires (655 to 2424).
Similarly, in the next example, Excel has included 120% on the y-axis, even though my data could never mathematically reach that value. As well as appearing odd, this automatically means my columns can’t make full use of the chart’s height.
So, to adjust the chart’s numerical parameters, double-click the relevant axis, and click the chart icon in the Format Chart Pane.
In the Bounds section, you’ll see that the numbers are labeled as Auto.
As soon as you type new minimum and maximum numbers in the fields and press Enter, your chart will adjust accordingly. Also, the Auto label in the pane will change to Reset, so you’re able to revert your bounds to Excel’s automated values if needed. If your data contains percentages, set the bounds using decimalized numbers (where 1.0 is 100%, 0.5 is 50%, and so on).
Just be aware that if your data changes to include a higher value than your manual axis bounds, you’ll need to adjust them again to ensure your chart reflects the data accurately.
While you’re formatting your chart’s axis, review its units (the option directly beneath where you changed the bounds). The Major Units option dictates the intervals at which numbers appear on the axis. For example, typing3.0will mean that the numbers on the axis will be 3, 6, 9, and so on. The Minor Units option dictates the intervals of the tick marks if activated.
3Reduce the Gaps Between Bars and Columns
Excel measures the distance between data points in a chart as a percentage. In this example, the gap width is 50%, meaning the space between each column is half the width of the columns themselves.
The key here is to help the reader compare the columns as easily as possible. The screenshot below shows Excel’s default gap width being much wider than in the adjusted example above, so comparing the data points might be more challenging.
This tip only works on charts whose data points are plotted separately on a chart. For example, you’re able to’t change the gap width on aline chart, as thereisno gap!
To reduce this gap width, right-click any of the data points on your chart, and click “Format Data Series.” Then, in the Format Chart Pane, click the chart icon, and either type a new gap width into the text field box or click and drag the slider.
The chart will update as soon as you make any changes, so you can keep tweaking the gap width value until you’re happy.
4Moderate Your Visuals
While my aim is to guide you to format your charts to make them stand out, I must emphasize the importance of not overloading them with lots of visuals.
For example, do you need a legendandaxis titles? Do you need gridlinesanddata labels? Because this chart has so many elements, it’s cluttered and overwhelming.
Likewise, this chart has too many colors and fonts, so it’s likely to quite literally scare readers away!
Okay, maybe I’ve gone overboard with my examples. However, my point stands—formatting your chart isn’t about making use ofeverytool Excel has to offer. Instead, it’s about selecting the elements that will allow you to fulfill the aim you set out to achieve when you first decided to create one.
To reduce the number of chart elements, select your chart, click the “+” icon, and uncheck the labels your chart doesn’t require.
To remove all the formatting and reset the chart to one of Excel’s preset styles, select the chart, and click “Reset To Match Style” in the Format tab.
5Remove Spreadsheet Gridlines
This might appear to be an unusual tip, as it doesn’t involve making any changes to the chart itself. However, Excel’s spreadsheet gridlines can make seeing and interpreting the chart more challenging.
For example, if your charts have transparent or semitransparent backgrounds, having vertical and horizontal gridlines behind these charts can make the charts difficult to read.
Byunchecking “Gridlines” in the View tab, the charts instantly become cleaner, more professional, and easier to read.
6Save Your Chart Design as a Template
Finally, once you have finished formatting your chart,save it as a chart templateso that you can use the same design again in the future. This saves you from having to remember the steps you took to perfect your chart’s layout and ensures consistency across your workbook.
Right-click your completed chart, and click “Save As Template.”
In the Save As Template dialog box, rename your template so that it’s easily distinguishable from other templates you might add, and click “Save.” The chart template will then be stored in the Charts Templates folder on your device.
When you go to create a new chart, select your data, and click the icon in the bottom corner of the Charts group in the Insert tab.
Then, switch to the “All Charts” tab, and click “Templates” in the left-hand list. There, you will see your saved chart template, which you can double-click to add to your worksheet.
Regardless of how you format your charts, you should focus on optimizing readability and achieving consistency. After all, there’s no use in having all-singing-all-dancing graphs if they’re difficult to interpret and make your spreadsheet look disorganized.
As well as using and formatting some ofExcel’s most popular graphs, like pie charts and bar charts, don’t miss out on the benefits of some ofthe program’s less-known data visualization graphics, like waterfall charts and sunburst charts.
