Formatting Charts to Make Them More Appealing

Excel makes it easy to convert a data set into a chart. The problem is that often the default chart settings aren’t the greatest. Below I have some sample data that I will convert into a chart:

If I click on the data and go on the Insert tab and click on a new Column Chart it will create the following chart for me (this may vary based on which version of Excel you are using):

There are a number of things that don’t appeal to me here that I am going to change:
– Gridlines are a little darker and more prominent than they need to be
– Gridlines stretch past the axis
– The legend is off to the side, which takes up chart space
– The border around the chart itself
– The gaps are a bit big
– The flat look of the chart

These may appear minor issues but in terms of presentation they can make a big difference. First I will start with the grid lines.

Formatting Gridlines

I am going to change the color to grey so that it does not stand out as much.

Next I will have to format the axis to stop the gridlines from going past the axis. To do this I click on one of the axis labels to select them and again right-click and select Format Axis

 

I will also change the Line Color here to match the grey from the gridlines. I repeat these steps for the other axis to get rid of the tick marks there as well.

Formatting the Legend and Adding a Chart Title

Next, I will change the legend so that it shows at the bottom of the chart. This is an easy fix and all I need to do is select the Layout tab from under the Chart Tools section of the ribbon. From there I select Legend and choose Show Legend at Bottom.

To the left of the Legend drop down is a section for Chart Title. This is where you can select how you want your title to appear.

If you select Centered Overlay Title you don’t lose chart space but then your title is overlapping with your chart. Above Chart will put the title above the actual chart so that there is no overlap.

Removing the Border 

Next, I am going to remove the border around the chart itself. To do so, I need to right-click somewhere on the white space that isn’t on the plot area. Somewhere near an axis or the legend will work. Then I can select Format Chart Area.

If I select Border Color I can change the setting from Automatic to No line to remove the border.

It may look a little odd if your gridlines are showing so you may want the outline. However unless you print with gridlines, then the chart will blend in better without them. Below is an example of the two charts with and without borders in print preview mode:

Shrinking the Gaps

Lastly, I will shrink the gaps in the chart. To do this I will right-click on any of the columns and select Format Data Series.

Changing Colors/Effects

If you wanted to change the colors of the chart you can do so individually or just change the theme. To change the theme go under Chart Tools and this time select the Design tab.

Changing the theme will undo the changes I have made to the gridline colors so if you do those changes you will want to change the theme first.

You don’t have to select a theme, you can change colors one by one. To change the color of an individual series you can do so by right-clicking on one of the columns and select Format Data Series and change the fill color under the Fill section.

Instead, what I am going to do is adjust the shadows. Right now they look flat, and I want a bit of an elevated effect. While still in the above menu I can select the Shadow option. If I click on the drop down in the Presets field, I will have a number of shadow options. I don’t use the inner shadows since they make the columns a bit dark, and the outer ones leave too long of a shadow. In this case I select the Offset Left option.

This is what my chart looks like now after all the changes:

Saving a New Template

Rather than making these changes every time I can save my changes to a template. To do so, just click on Save As Template which is under the Design tab in Chart Tools. Then just assign a name and your template is saved.

If you want to use your template again simply when select chart types select the Templates folder and you will see it there.