Windows & Other OS

How to overlay charts in Microsoft Excel

Although Microsoft Excel offers a good selection of chart types, you may have a situation where you need something outside the norm. An overlapping graphic may be what you need. Here’s how to make one.

We will explain two methods to overlay charts in Excel. Which one you choose depends on how much data you have to display and how you want it to be displayed.

Create a combo chart in Excel

If you want to overlay two different types of graphics, a combo box personalized it is an ideal solution. You can combine columns, bars, lines, areas, and some other types of charts into one large image.

Below we have a column chart showing the sales for our two divisions along with the totals. As you can see, the Totals series seems out of place and does not display the data clearly.

Column chart in Excel

If we use a custom combo chart, we can change the Total series to a line in our column chart.

Change your chart – If you already have a chart that you want to change, select it and go to the Chart Design tab. Click “Change Chart Type” on the right side of the ribbon.

Click Change Chart Type

In the Change Chart Type window, select Combo on the left and Custom combination on the right.

Select Combo, custom combination

Create your chart : if you don’t have a chart configured yet, select your data and go to the Insert tab. In the Charts section of the ribbon, click the drop-down arrow for Insert Combo Chart and select “Create Custom Combo Chart.”

Select Create custom combo chart

Set up the combo chart

With the Graph window open and the Custom Combination Table selected, you will see the area you can work with at the bottom.

Next to each data series, select the type of chart you want to use from the drop-down box. Optionally, you can add a secondary axis for any of the series by checking the box to the right.

Select the type of chart for the series

As you make your selections, you’ll see a preview at the top so you can see if it’s what you want. When you are satisfied with your selections, click “OK.”

Click OK to insert the chart.

We now have a combo chart that shows our divisions as columns and our totals as a line. This gives us a much better picture of the data we are trying to convey.

Custom combo box in Excel

Overlay series in Excel

It may not be different types of charts that you want to overlay, but rather certain data on a chart. You can overlay a chart in Excel customizing a series .

For a basic example, we have a bar graph with two data series. The graph shows the amount of traffic to our site before and after our website redesign. There’s nothing wrong with the way the data is displayed, but we’d rather give it a bit of a push for better impact.

Bar chart in Excel

Select the series with the longest bars, here would be our After series in orange. Double click or right click and select “Data Series Format” to open the sidebar.

Select data series format

Confirm that you’ve selected the entire series by clicking the arrow next to Series Options at the top of the sidebar.

Confirm or select series

Select the Series Options tab. Then move the slider for Series Overlay all the way to the right or enter 100 percent in the box.

Set the series overlap

Select the Fill & Line tab and adjust the following settings:

  • Filling – Choose No Fill.
  • Edge : Choose Solid line.
  • (Edge) Color : choose the color you want. For our example, we will keep the color orange.
  • (Edge) Width – Use the top arrow to widen the edge to make it easier to see. We set ours to 2.25 pt.

You can make any other changes you want to the border, such as compound or hyphen.

Change Fill and Line settings

When you’re done, click the X at the top right of the sidebar to close it.

Now you can see that our chart has overlapping data to show a clearer way of the differences between our Before and After. And the legend we are using is also updated to the no-fill edge for that data series.

Overlay chart in Excel

For other types of charts that are a bit out of the ordinary, take a look at how create and customize a people chart in Excel .

RELATED:   Should you buy a 2021 MacBook Pro for gaming?

Related posts

Leave a Comment