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.

Where to find December’s flipbook issue

The Journal of Accountancy is now completely digital. 

 

 

 

SPONSORED REPORT

Get Clients Ready for Tax Season

This comprehensive report looks at the changes to the child tax credit, earned income tax credit, and child and dependent care credit caused by the expiration of provisions in the American Rescue Plan Act; the ability e-file more returns in the Form 1040 series; automobile mileage deductions; the alternative minimum tax; gift tax exemptions; strategies for accelerating or postponing income and deductions; and retirement and estate planning.