Adjust Sparkline Axis Scaling in Excel Dashboards and Reports

By Michael Alexander

Sparklines give you the ability to show data trends in your Excel dashboards and reports. When you create one or more sparklines, they all use (by default) automatic axis scaling. In other words, Excel determines the minimum and maximum vertical axis values for each sparkline in the group based on the numeric range of the sparkline data.

The Sparkline Tools→Design→Group→Axis command lets you override this automatic behavior and control the minimum and maximum value for each sparkline, or for a group of sparklines. For even more control, you can use the Custom Value option and specify the minimum and maximum for the sparkline group.

Axis scaling can make a huge difference in the sparklines. This figure shows two groups of sparklines. The group at the top uses the default axis settings (Automatic For Each Sparkline option). Each sparkline in this group shows the six-month trend for the product, but there is no indication of the magnitude of the values.

image0.jpg

The sparkline group at the bottom (which uses the same data), uses the Same For All Sparklines setting for the minimum and maximum axis values. With these settings in effect, the magnitude of the values across the products is apparent — but the trend across the months within a product is not apparent.

The axis scaling option you choose depends upon what aspect of the data you want to emphasize.