Sparklines and Merged Cells in Excel

I am not a fan of Merged Cells but there may be a case to use them when creating a larger Sparkline chart.

Sparkline charts are charts that exist in a cell. That means their size is determined by the row height and the column width of that cell. When creating reports or dashboards you might not always be able to change the rows and columns.

To get around that limitation you could use a Merged Cell. Merged cells are typically created by using the Merge & Center icon on the Home ribbon.

Creating a Sparkline

Sparkline charts are easy to create. They only plot a single data series. Select the Merge Cell click the Insert tab and click Column in the Sparkline section.

Select the data range in the top box and the Merged cell (top left cell) in the bottom box and click OK.

The result is shown below.

Axis issues

Unfortunately when creating column Sparkline charts the default setting is NOT to start the vertical axis at zero. You can see the issue in the above chart.

The chart has a small column. That column’s value is just above the minimum value for the vertical axis. Basically, the lowest value (10,471) becomes the minimum value of the vertical axis.

The general rule of column charts is that the vertical axis must ALWAYS start at zero. There are very few exceptions to this rule.

Luckily fixing the issue is straightforward.

With the Sparkline selected click the Sparkline tab. Click the Axis drop-down and choose Custom Value.

The weird thing is that the value in the dialog that opens is zero! All you need to do is click OK and the problem is solved.

The final charts are shown below. Note: you can add text to the merged cell and it displays with the Sparkline chart. Positioning the text may be a challenge.

Merged Cell Size

When creating the merged cell a good rule of thumb is to make the cell roughly twice as wide as it is high.

Please note: I reserve the right to delete comments that are offensive or off-topic.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.