Microsoft Excel: An Excel Camera trick for overlaying sparklines
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.
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.
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.
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.
