Combination charts

BY J. CARLTON COLLINS, CPA

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

Combination chartsA: 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.

SPONSORED REPORT

How to make the most of a negotiation

Negotiators are made, not born. In this sponsored report, we cover strategies and tactics to help you head into 2017 ready to take on business deals, salary discussions and more.

VIDEO

Will the Affordable Care Act be repealed?

The results of the 2016 presidential election are likely to have a big impact on federal tax policy in the coming years. Eddie Adkins, CPA, a partner in the Washington National Tax Office at Grant Thornton, discusses what parts of the ACA might survive the repeal of most of the law.

QUIZ

News quiz: Scam email plagues tax professionals—again

Even as the IRS reported on success in reducing tax return identity theft in the 2016 season, the Service also warned tax professionals about yet another email phishing scam. See how much you know about recent news with this short quiz.