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

2018 financial reporting survey: Challenges and trends

Learn the top reporting challenges that emerged in a survey of more than 800 finance, accounting, and compliance professionals across the world, and compare them with your organization's obstacles.

PODCAST

How the skill set for today’s CFO is changing

Scott Simmons, a search expert for large-company CFOs, gives advice for the next generation of finance leaders and more, including which universities are regularly producing future CEOs and CFOs.