Combination charts

Q: Is it possible to overlay one chart atop another chart in Excel 2013 for comparison purposes?

A: Yes, chart overlays are possible. Excel allows you to produce a combination chart that places one chart atop another using a percentage scale for each of the two data ranges. This arrangement allows the reader to visually compare the figures to determine if any relationships exist between the two sets of data in a single chart. For example, consider the following data (shown at right), which summarize a firm’s monthly billable hours and monthly revenue. Following are steps for creating a combination chart using these data:

Create a 2-D Line chart. Start by creating a 2-D Line chart using the entire data range by highlighting the data (A3:C15 in this example) and then from the Insert tab, in the Charts group, select the 2-D Line option.

Separate the data into two chart axes. Right-click one of the chart lines (either one), and from the pop-up menu select Format Data Series. In the resulting Format Data Series panel, click the Series Options icon, select the Secondary Axis option, and then click Close. This action will create a separate axis for each set of data in the same chart. (Note that data labels are now displayed on both sides of the chart below.)

 

Change one of the lines to display bars. Click one of the lines to select it (either one is fine), then right-click that selected line and from the pop-up menu select Change Series Chart Type. In the resulting Change Chart Type dialog box, make sure that the Combo option is selected in the left panel, and then select the Custom Column—Line on Secondary Axis icon toward the top of the dialog box, as pictured below. (Note that this Combo option does not appear in Excel 2010 or 2007, but you can still create the combo chart by selecting the Clustered Column icon in the Change Chart Type dialog box.)

 

In the Choose the chart type and axis for your data series section, make sure that the Chart Type for the two data series are set to Clustered Column and Line (as pictured below), and then click OK.

 

Finishing touches. Right-click one of the newly created chart bars and select Format Data Series from the pop-up menu. In the Format Data Series panel, apply finishing touches by adding a chart title, applying 3-D bevel formatting to the data bars, and adjusting the font sizes, etc. An example of the completed combination chart is shown below.

 

You can download an Excel workbook containing these data and an example combination chart at carltoncollins.com/combo.xlsx.

J. Carlton Collins (carlton@asaresearch.com) is a technology consultant, CPE instructor, and a JofA contributing editor.

Submit a question
Do you have technology questions for this column? Or, after reading an answer, do you have a better solution? Send them to jofatech@aicpa.org. We regret being unable to individually answer all submitted questions.