Excel: Edit sparklines:YOUR OBJECTIVE
You have created a table with sparklines in Excel and want to edit these now. You can modify sparklines in Excel, the axes, data points or the color design, for instance. This learning module “Excel: Edit sparklines”will show you how to do this.
Excel: Edit sparklines: HOW DOES IT WORK?
Excel: Edit the type
Three different sparkline types are available. To change the sparkline type, select the sparklines. Click the appropriate sparkline type button Line, Column or Win/Loss in the Type group on the Design tab of the Sparkline Tools:
Excel: Edit sparklines: HINT
Consider which type would be most appropriate. If you select the Column type, for instance, the height of the column in the default settings of the axes does not directly correspond to the respective value. The lowest value is represented by the lowest column, while the largest value is represented by the highest column in each row. This applies to all types, but when using a line chart, it is possible for the line to show only a trend (up/down) and not the immediate size of the respective value. Alternatively, you can of course adjust the axes so the height of the value corresponds directly to the value (see below).
Excel: Modify the style
Each sparkline type offers a number of formatting styles to define the appearance of the sparklines. You can very easily modify the Sparkline design. To select a different formatting style, select the sparkline first. Then go to the Design tab of the Sparkline Tools. A catalog with different formatting styles is in the Styles group. Select the template you want by clicking it.
Click the formatting style you want to apply.
Excel: Edit the way the points are displayed
You can specify whether the line type sparkline is displayed with or without the data points. You can specify if the lines will be displayed with the highest or the lowest data points only.
To display data points, select the sparklines. Then go to the Design tab of the Sparkline Tools. You can select the following options in the Show group:
Select the High Point option to highlight the highest value in the sparkline line with a point.
Select the Low Point option to highlight the lowest value in the sparkline line with a point.
Select the Negative Points option to indicate negative values with a point.
Select the First Point option to display each first point as data point.
Select the Last Point option to display only the last point of a sparkline as a point.
Select the Markers option to mark all data points in the sparklines.
If you have selected the Markers option, your table will look like this, for instance:
You can also apply the same settings to the other sparkline types, but they do not make too much sense here.
Excel: Deal with hidden and empty cells
The sparkline will be interrupted if there are values missing in the data range.
In some cases even the values that are not missing may not be visualized, unless you have enabled data points display (markers, see above). Your sparklines look like this when values are missing and data points display has been enabled:
Now you can specify how Excel will deal with these missing values. Select the sparklines and go to the Design tab of the Sparkline Tools. Click the arrow of the excel Edit Data button in the Sparkline group and select the Hidden & Empty Cells… command.
The Hidden and Empty Cells dialog box appears:
Gaps is the default setting. This means that if a value is missing, no connecting line is displayed.
Zero means that a missing value will be displayed as if the value was 0.
Connect data points with line means that the missing value is ignored and Excel connects the existing values with a line. This option is only available with line type sparklines.
Show data in hidden rows and columns means that Excel will display the value, even if the rows or columns in which it is located are hidden.
Excel: Edit the axis
Excel does not display the sparklines proportionally to the values by default. You can see this very well in the following example, where the value 600 in the 4th quarter is twice as much as the 300 in the 1st quarter, but the column is several times as long:
The following options appear:
The General Axis type is the appropriate option for most cases. This means that Excel displays the data in equal spacing between each other.
The Data Axis Type… is necessary when you are not going to analyze subsequent quarterly numbers as in the example above, but rather when values to which your evaluation refers were determined in periods that are not in equal intervals to each other. So if you have the values for January 1st, February 1st, but then skip to May 1st, this option allows you to space out the columns or lines a little farther. This can be compared to the point-X-Y chart type in Excel. A dialog box, in which you can specify where the date values are located, opens when you select this option.
The Show Axis option displays the x-axis.
Plot Date Right-to-Left means that the order is reversed, so that Excel starts with the value of the 4th quarter in the sparklines, then the 3rd quarter and so forth.
The Automatic for Each Sparkline will indicate the minimum or maximum value for each axis. This means that for each data row the lowest value gets the lowest height and the largest value the largest height.
Same for All Sparklines means that Excel will use the entire cell range for the sparklines group as scale. So if there is an extraordinarily high value in one of the cells, this value defines the scale for the highest column or line in the entire group of sparklines. The height of the columns or lines then indicates the size of the values with respect to the entire data range.
Custom value… will open a dialog box where you can specify the lowest or the highest value. If you want Excel to always use the number 0 as scale for the lowest value, enter the custom value 0 for Minimum Value. Similarly, also specify the highest value. Values that are above or below these limits are not displayed in the sparklines.
You can set the highest or lowest value independently of each other.