Column sparklines are small bar charts that display data as vertical bars to show trends or comparisons over time or across categories. Each bar represents a single value, such as daily sales, monthly revenue, or website visits.
When to use column sparklines
- Comparing values: Display performance across time periods or categories, such as daily sales or monthly expenses.
- Showing trends in a compact format: Track changes over time, such as monthly revenue or weekly performance, while minimizing space.
- Highlighting outliers or peaks: Quickly identify unusually high or low values.
Create a column sparkline
- On the Insert tab, select Sparklines.
- Under Bar Sparkline, choose Insert Column Sparkline. The Create Sparklines dialog opens.
- In Data Range, select a continuous range of cells (in the same row or column).
- In Location Range, select the cell where the sparkline will appear.
- Uncheck isFormulaSparkline if you do not want the sparkline to be saved as a formula. This removes the formula from the formula bar (
fx) and enables the Sparkline ribbon controls.
- Click Ok. The sparkline is inserted into the selected cell.
Edit a column sparkline
- Select the column sparkline in your workbook.
- On the Sparkline tab, select Settings. The ColumnSparkline Setting dialog opens.
- Adjust the parameters, then click Ok.
- Copy and paste the sparkline cell to duplicate the configuration in another location, as needed.
When the isFormulaSparkline option is unchecked during creation, the sparkline is no longer controlled by a formula. The Settings button is replaced by direct ribbon controls.
- Select the column sparkline in your workbook.
- Use the Sparkline tab to customize the sparkline:
- Change the sparkline type (Line, Column, or Win/Loss).
- Show or hide specific value points (first, last, high, low, negative).
- Apply custom marker and axis styles.
- Adjust sparkline and marker colors, and line thickness.
- To highlight values, enable the Markers option and select which points to display.
Column sparkline parameters
The column sparkline formula supports the following format:
=COLUMNSPARKLINE(data, dataOrientation, dateAxisData, dateAxisOrientation, setting)
Data parameters
| Parameter | Description |
|---|
| Data | Specifies the range containing sparkline data, such as A1:C3. |
| DataOrientation | Specifies the data orientation: Vertical or Horizontal. |
| DateAxisData | (Optional) Sets the date axis range, such as D1:F3. |
| DateAxisOrientation | (Optional) Specifies the sparkline date axis orientation: Vertical or Horizontal. |
| DisplayEmptyCellsAs | Specifies how to show empty cells: Gaps, Zero, or Connect. |
| Show data in hidden rows and columns | (Optional) Enables display of data in hidden cells. |
Show parameters
| Parameter | Description |
|---|
| Show First | Highlights the first data point using a different format. |
| Show Last | Highlights the last data point using a different format. |
| Show High | Applies distinct formatting to the highest value. |
| Show Low | Applies distinct formatting to the lowest value. |
| Show Negative | Formats negative values differently. |
| Show Markers | Display data markers for each sparkline. |
Group parameters
| Parameter | Description |
|---|
| MinAxisType | Defines the minimum axis type: Individual or Custom. |
| ManualMin | Becomes available when MinAxisType is set to Custom. Enter a value such as -3. |
| MaxAxisType | Defines the maximum axis type: Individual or Custom. |
| ManualMax | Becomes available when MaxAxisType is set to Custom. Enter a value such as 5. |
| RightToLeft | Displays the group direction from right to left, instead of the default left to right. |
| Display XAxis | Shows or hides the X-axis for the group in the visualization. |
Style parameters
| Parameter | Description |
|---|
| Style Setting | Defines the color or line weight for each visual element:
- Negative – Color of the negative data points for each sparkline in this sparkline group.
- Markers – Color of the data markers for each sparkline in this sparkline group.
- Axis – Color of the axis.
- Series – Color for each sparkline in this sparkline group.
- High Marker – Color of the highest data point for each sparkline in this sparkline group.
- Low – Color of the lowest data point for each sparkline in this sparkline group.
- First Marker – Color of the first data point for each sparkline in this sparkline group.
- Last Marker – Color of the last data point for each sparkline in this sparkline group.
- Line Weight – Thickness of the line.
|