Area charts show trends in a great way in Microsoft Excel data. The good news is that they are easy to make; the bad news is that the default chart is often unsuitable for distribution due to its formatting. The chart is ugly because Excel applies different colors to each area, and they don’t look good together. Fortunately, it only takes a few minutes to adjust the default formatting and the results are more than adequate.
In this tutorial, I’ll show you a few tweaks that can quickly improve the look of Excel’s default area chart. Although we’re working with an area chart, you can apply these simple formatting changes to most chart types.
TO SEE: Google Workspace versus Microsoft 365: a side-by-side analysis with checklist (Tech Republic Premium)
I use Microsoft 365 Desktop on a Windows 10 64-bit system, but you can use earlier versions of Excel. Excel for the web supports charts.
You can download the Microsoft Excel Demo File for this lesson.
Create an area chart in Excel
An area chart displays numerical data, usually over time. Visually, it is a combination of a bar and line chart. Another way to describe an area chart is a line chart where the area below is shaded up to the baseline. You can use an area chart to plot a single line and a shaded area, but most area charts plot multiple lines (data series) so you can split the trend between groups compared to the whole.
Image A displays a simple sheet of twelve-month sales data for five regions.
Image A
To create an area chart based on this data, do the following:
1. Click anywhere in the data.
2. Click the Insert menu.
3. Click the Insert Line or Area Chart option in the Charts group. It’s the first option on the second line.
4. In the resulting dialog box, choose the Stacked Area chart (Figure B).
Figure B
Even though the graph is not pleasing to the eye, there are some interesting trends to discover. In particular, all regions seem to have common sales declines in May and November. This is definitely worth a closer look. Furthermore, all regions see a sharp increase in December.
Unfortunately the graph is rather ugly (Figure C). Many users may not realize how easy it is to convert this chart into a professional and readable chart quickly and with little effort.
Figure C
Apply a more professional color choice in Excel
The first thing you might want to fix is the mix of colors. Understandably, each region has its own color, but they don’t have to be such ugly colors. It’s not that every color is ugly on its own: it’s the combination of the colors that is ugly. The solution is to apply shades of the same color. Let’s go for blue.
To change the color of a series, do the following:
1. Click on the bottom series, the one for the Southeast region. If you do this, only that region will be selected (Figure D). If you want, you can work from the top down. As you select an area, Excel selects the corresponding row in the table.
Figure D
2. Right-click the selected area to display the menu toolbar just below the submenu.
3. Click Fill and then choose the darkest blue in the blue column. If you choose a color other than blue, work with the shades in that column.
As you can see in Digits E, the bottom series is now dark blue. The darkest blue appears almost black next to the orange.
Digits E
I recommend working with shades in the same column and applying the darkest shades at the bottom and picking lighter shades as you go up.
Repeat the above steps, select an area and apply a tint in the blue column. Figure F shows my results. I think most would agree that this chart already looks better, and it took about a minute to change the colors for each region. Excel even updates the legend at the bottom of the chart accordingly.
Figure F
How to Change Chart Title in Excel
The chart title in the top center needs a descriptive title. Click on it, select the default title and replace it with 2021 Monthly Sales (Figure G).
Figure G
When the chart is full, delete the Chart Title text box and enter the title above the chart, centering it between the left and right margins of the chart (figure H).
figure H
To achieve this, do the following:
1. Move the chart down to make room for the title. You don’t always have to do this.
2. Enter the title, 2021 Monthly Sales, in B11, or the cell on the left edge just above the chart.
3. Select all cells from the left to the right edge of the chart. In this case it is B11:N11.
4. Click Merge & Center.
5. The only downside is that you have to remember to move the title if you move the chart.
If you go this route, remove the map title at the top of the map.
Add a divider between the top area and the plot background in Excel
As it is, the color of the top area is so light that it fades into the plot area, which is white. You can easily fix this by adding a dark border to just the top area as follows:
1. Right-click in the top area and choose Format Data Series.
2. In the Format Data Series pane, click the Fill icon.
3. At the bottom of the panel, expand the Border options.
4. Click Solid Line.
5. From the Color drop-down list, choose a dark blue (Figure I).
Figure I
As you can see, you’ve added a subtle border to the whole area, and not just the top border, but with the colors used it looks good.
How to match formats for both titles
To display the data table with the chart, you may want the chart title and table header row to look the same. This is extremely easy using Excel’s Format Painter.
First, click on a row in the table header. Then click Format Painter in the Clipboard group on the Home tab. Then select E11:N11 and Excel will apply the header formatting to the chart’s title row (figure J). Once you’ve done this, you’ll need to re-center the chart title.
figure J
Excel gave us an ugly chart and within minutes we changed a few settings and turned the ugly duckling into a beautiful swan.
