Excel Dashboards and Reports: Size and Merge Sparkline Cells

By Michael Alexander

Sparklines give you the ability to show data trends in your Excel dashboards and reports. When you activate a cell that contains a sparkline, Excel displays an outline around all the sparklines in its group. You can then use the commands on the Sparkline Tools→Design tab to customize the group of sparklines.

When you change the width or height of a cell that contains a sparkline, the sparkline adjusts to fill the new cell size. In addition, you can put a sparkline into merged cells. To merge cells, select at least two cells and choose Home→Alignment→Merge & Center.

This figure shows the same sparkline displayed at four sizes resulting from changing column width and row height, and from merging cells.

image0.jpg

It’s important to note that a skewed aspect ratio can distort your visualizations, exaggerating the trend in sparklines that are too tall, and flattening the trend in sparklines that are too wide.

Generally speaking, the most appropriate aspect ratio for a chart is one where the width of the chart is about twice as long as the height. In this figure, the sparkline with the most appropriate aspect ratio is the one located in cell M4.

If you merge cells, and the merged cells occupy more than one row or one column, Excel won’t let you insert a group of sparklines into those merged cells. Rather, you need to insert the sparklines into a normal range (with no merged cells), and then merge the cells.

You can also put a sparkline in non-empty cells, including merged cells. This figure shows two sparklines that occupy merged cells alongside text that describes the graphics.

image1.jpg