Column Sparklines

To create column sparklines in the workbook:

  1. Click Insert from the menu.

  2. Click Sparkline and select the column sparkline under Bar Sparkline from the list.

  3. In the Create Sparklines dialog box:

    1. Select a range of cells in the same row or the same column.

    2. Select the cell where the sparkline will appear.

    3. Click OK.

  4. The sparkline is displayed in the cell. You can now customize the sparkline by clicking Design from the menu and selecting Settings.

    Note

    If you removed the default option isFormulaSparkline in the previous step, the sparkline is not saved as a function and thus the function does not appear in the fx line. After clicking Design from the menu, the Settings button is replaced by several options. You can customize the sparkline using these options, such as changing the type of sparkline (Line, Column, or WinLoss), controlling which value points are shown (such as high, low, first, last, or any negative values), applying styles, and controlling whether to show the horizontal axis. You can highlight individual data markers (values) in a column sparkline by making some or all of the markers visible.

  5. Set the parameters as described below and click OK.

  6. Copy and paste the cell as needed to finalize your column sparklines.

Column sparkline parameter Description
Data  
Data A range reference that represents sparkline data, such as A1:C3.
Data Orientation

A number that represents the sparkline data orientation:

  • Vertical (0)

  • Horizontal (1)
DateAxisData (Optional) A range reference that represents sparkline date axis data, such as D1:F3.
DateAxisOrientation

(Optional) A number that represents the sparkline date axis orientation.

  • Vertical (0)

  • Horizontal (1)
DisplayEmptyCellsAs

Option to display empty cells as:

  • Gaps

  • Zero

  • Connect
Show data in hidden rows and columns (Optional) Select the option to show hidden data.
Show  
Show First Specifies whether the first data point is formatted differently for each sparkline in this sparkline group.
Show Last Specifies whether the last data point is formatted differently for each sparkline in this sparkline group.
Show High Specifies whether the data points with the highest value are formatted differently for each sparkline in this sparkline group.
Show Low Specifies whether the data points with the lowest value are formatted differently for each sparkline in this sparkline group.
Show Negative Specifies whether the negative data points are formatted differently for each sparkline in this sparkline group.
Show Markers Specifies whether data markers are displayed for each sparkline in this sparkline group.
Group  
MinAxisType

Defines the minimum axis type for the group:

  • Individual (default)

  • Custom
ManualMin

Becomes available when MinAxisType is set to Custom. Enter the desired number; for example, -3.
MaxAxisType

Defines the maximum axis type for the group:

  • Individual (default)

  • Custom
ManualMax

Becomes available when MaxAxisType is set to Custom. Enter the desired number; for example, 5.
RightToLeft Reads the sparkline data range values from right to left and adjusts the sparkline accordingly.
DisplayXAxis Displays the X axis.
Style  
Style Setting

Allows you to change the color of the following items:

  • 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.

The column sparkline formula has the following syntax:

=COLUMNSPARKLINE(data, dataOrientation, dateAxisData, dateAxisOrientation, setting)