Microsoft Excel: An Excel Camera trick for overlaying sparklines

By J. Carlton Collins, CPA

Q. Is it possible to produce a sparkline chart in Excel that displays a column chart with an overlaying line chart?

A. The correct answer is no; you cannot create a sparkline chart in Excel that displays multiple chart types. That said, you can use Excel's Camera tool to achieve this effect. Here's how. Start by adding Excel's Camera tool to your Quick Access Toolbar by right-clicking the toolbar and selecting Customize Quick Access Toolbar. In the resulting Excel Options box, select All commands from the Choose commands from box, scroll down to and select the Camera tool, click the Add button, and click OK. Next, create the column and line sparkline charts you want to overlay, as suggested in the screenshot below.

techqa10


Continue by highlighting the first sparkline line chart (cell H7 in this example) and clicking the Camera tool icon to copy the line chart, then clicking Excel where you want the line chart displayed. (I want the line chart to overlay cell H2 in this example pictured below.) Format the Camera image to contain No fill and No line as suggested, and reposition it atop the columnar sparkline as needed.

techqa11


Repeat this step for each columnar sparkline as suggested in cells H2 through H9 below. Thereafter, both the columnar sparklines and linear sparklines (floating atop the columnar sparklines) will change as the data change. You can also change the colors of the linear sparkline charts or adjust the width of the cells containing the dual sparklines.

The effect is to give the appearance that Excel supports combination sparklines, which might be good enough to meet your needs. You can download an example Excel workbook containing the overlaid sparklines depicted below at carltoncollins.com/spark.xlsx.

techqa12

About the author

J. Carlton Collins (carlton@asaresearch.com) is a technology consultant, a conference presenter, 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

A new line of business to consider

Technology assessments may open the door to new engagement opportunities for your firm. What is a technology assessment? How do you perform one? JofA Tech Q&A author J. Carlton Collins shows you in a detailed explanation.

FEATURE

Maximizing the higher education tax credits

A counterintuitive strategy can save taxes by including otherwise excludable scholarships in gross income.