[ad_1]
Area charts do a great job of exposing trends in Microsoft Excel data. The good news is that they’re easy to create; the bad news is that the default chart is often unsuitable for distribution because of the formatting. The chart is ugly because Excel applies different colors to each area, and they don’t look good together. Fortunately, it takes only a few minutes to tweak the default formatting, and the results are more than suitable.
In this tutorial, I’ll show you a few tweaks that can quickly improve the looks of Excel’s default area chart. Although we’re working with an area chart, you can apply these simple format changes to most chart types.
SEE: Google Workspace vs. Microsoft 365: A side-by-side analysis w/checklist (TechRepublic Premium)
I’m using 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 tutorial.
How to create an area chart in Excel
An area chart represents numeric data, usually over time. Visually, it’s a combination of a bar and line chart. Another way to describe an area chart is a line chart with the area below shaded to the baseline. You can use an area chart to plot a single line and shaded area, but most area charts plot multiple lines (data series) so you can break down the trend between groups compared to the whole.
Figure A displays a simple sheet of sales data over twelve months for five regions.
Figure A
To create an area chart based on this data, do the following:
1. Click anywhere inside 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, choose the Stacked Area chart (Figure B).
Figure B
Even though the chart is not pleasing to the eye, you can discern a few interesting trends. Specifically, all of the regions seem to share common dips in sales during May and November. This is certainly worth looking at closer. Furthermore, all the regions see a significant increase in December.
Unfortunately, the chart is rather ugly (Figure C). Many users might not realize how easy it is to quickly turn this chart into a professional and readable chart with little effort.
Figure C
How to apply a more professional choice of colors 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 each color is ugly by itself: It’s the combination of the colors that’s unsightly. The solution is to apply shades of the same color. Let’s go with blue.
To change the color of a series, do the following:
1. Click the bottom series, the one for the Southeast region. Doing so will select only that region (Figure D). You can work from the top down if you prefer. As you select an area, Excel will select the corresponding row in the table.
Figure D
2. Right-click the selected region 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 Figure E, the bottom series is now a dark blue. The darkest blue almost looks black next to the orange.
Figure E
I recommend that you work with shades in the same column and apply the darkest of the shades on the bottom and choose lighter shades as you move up.
Repeat the steps above, selecting one region and applying a shade in the blue column. Figure F shows my results. I think most will agree that this chart looks better already, 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 the chart title in Excel
The chart title at the top center needs a descriptive title. Click it, select the default title and replace it with 2021 Monthly Sales (Figure G).
Figure G
If the chart is crowded, delete the Chart Title text box and enter the title above the chart and then center it between the chart’s left and right margins (Figure H).
Figure H
To accomplish this, do the following:
1. Move the chart down to make room for the title. You won’t always need to do this.
2. Enter the title, 2021 Monthly Sales, in B11, or the cell at the left border just above the chart.
3. Select all the cells from the left to right border of the chart. In this case, that’s B11:N11.
4. Click Merge and Center.
5. The one drawback is that you must remember to move the title if you move the chart.
If you go this route, delete the chart title from the top of the chart.
How to add a separating line between the top area and the plot background in Excel
As 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 only the top region as follows:
1. Right-click 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 pane, expand the Border options.
4. Click Solid Line.
5. From the Color dropdown, choose a dark blue (Figure I).
Figure I
As you can see, you’ve added a subtle border to the entire area, and not only the top border, but with the colors in use, it looks fine.
How to match formats for both titles
If you want to display the data table with the chart, you might want the chart title and the table heading row to look the same. This is extremely easy using Excel’s Format Painter.
First, click any row inside the table header. Then, click Format Painter in the Clipboard group on the Home tab. Next, select E11:N11 and Excel applies the formatting from the header to the chart’s title row (Figure J). Once you do, you’ll need to center the chart title again.
Figure J
Excel gave us an ugly chart and, within a few minutes, we changed a few settings and turned the ugly duckling into a beautiful swan.
[ad_2]
Source link
