Tag: Sparklines

Sparklines, as seen in this overview post on sparklines, are quick, simple charts that can be inserted directly a the cell of a spreadsheet created with Google Sheets.  One of their strengths is their simplicity.  However, there are several options that can be used to expand a sparkline’s functionality.  Below, we focus on the options available for use with the bar chart type of sparkline.  

Bar chart sparkline options

max determines the maximum value on the horizontal (x) axis

"max",12 Note that this is the value in the first cell so the entire chart is representing just that one value.

"max",21 Note that this is the sum of the values in the first two cells so the entire chart is representing just these two values.

"max",61 This is the value of all of the cells added together the chart is the same as if you had not specified a max value.

"max",61 This is the value of all of the cells added together the chart is the same as if you had not specified a max value. Note that barcharts must use absolute values as the chart is rendered the same way whether or not the cells are negative.

"ma",75 setting of 75. This chart illustrates that you can specify a chart max larger than the chart itself and the chart will scale down.

color1 determines the first color color used for bars in the chart

"color1","red" setting of red. Note that is changes the 1st, 3rd, 5th color, etc

color2 determines the second color color used for bars in the chart

"color1","red" and "color2","yelow" Note that this changes not only the first and second colors, but all of the colors.

empty how to treat empty cells

No empty parameter is set for this sparkline bar chart

zero give the cell a value of zero for the sparkline

ignore ignore the cell, rendering the chart as if it does not exist

nan how to treat cells with non-numeric data

• convert let Sheets try to convert the character(s) in the cell to a number. Good luck on this one.
• ignore ignore the cell, rendering the chart as if that value does not exist
• Note in the image that the “ignore” option behaves the same as not designating this option at all.

rtl changes the direction of the chart from left-to-right to right-to-left

• true The direction of the chart is flipped
• false The direction of the chart stays the same
• Note that the “false” option behaves the same as not designating this option at all. 

Video explanation

Related Post

Learn how to use bar charts to show ranking data in Google Sheets.

Sparklines, as seen in this overview post on sparklines, are quick, simple charts that can be inserted directly into the cell of a spreadsheet created with Google Sheets.  One of their strengths is their simplicity.  However, there are several options that can be used to expand their functionality.  Below, we focus on the options available for use with the columns and winloss types of sparkline.

Column and winloss sparkline options

color determines the color of the chart’s columns

lowcolor determines the color for the lowest value in the chart

highcolor determines the color for the highest value in the chart

firstcolor determines the color for the first column in the chart

lastcolor determines the color for the last column in the chart

negcolor sets the color for the columns with negative values

empty how to treat empty cells

• zero give the cell a value of zero for the sparkline
• ignore ignore the cell, rendering the chart as if it does not exist
• Note in the image that the “ignore” option behaves the same as not designating this option at all.

nan how to treat cells with non-numeric data

• convert let Sheets try to convert the character(s) in the cell to a number. Good luck on this one.
• ignore ignore the cell, rendering the chart as if that value does not exist
• Note in the image that the “ignore” option behaves the same as not designating this option at all.

Video explanation



axis determines if the chart will have an axis

• true let Sheets try to convert the character(s) in the cell to a number. Good luck on this one.
• false ignore the cell, rendering the chart as if that value does not exist
• Note that the “false” option behaves the same as not designating this option at all

axiscolor determines if the color of the axis, if you use the axis option to create one

ymin determines the minimum data value for scaling the height of columns (not applicable for win/loss)

ymax determines the maximum data value for scaling the height of columns (not applicable for win/loss)

rtl changes the direction of the chart from left-to-right to right-to-left

• true The direction of the chart is flipped
• false The direction of the chart stays the same
• Note that the “false” option behaves the same as not designating this option at all. 

Follow image below for the live Google doc with sparklines

Sparklines, as seen in the overview post on sparklines, are quick, simple charts that can be inserted into the cell of a spreadsheet created with Google Sheets.  One of their strengths is their simplicity.  However, there are several options that can be used to expand their functionality.  Below, we focus on the options available for use with the line graph type of sparkline.

Line graph options

xmin sets the minimum value along the horizontal axis (not shown)

xmax sets the maximum value along the horizontal axis (not shown)

ymax sets the maximum value along the vertical axis.

color sets the color of the line

empty how to treat empty cells

• zero give the cell a value of zero for the sparkline
• ignore ignore the cell, rendering the chart as if it does not exist
• Note in the image that the “ignore” option behaves the same as not designating this option at all.

Video explanation

nan how to treat cells with non-numeric data

• convert let Sheets try to convert the character(s) in the cell to a number. Good luck on this one.
• ignore ignore the cell, rendering the chart as if that value does not exist
• Note in the image that the “ignore” option behaves the same as not designating this option at all.

rtl changes the direction of the chart from left-to-right to right-to-left

• true The direction of the chart is flipped
• false The direction of the chart stays the same
• Note in the image that the “false” option behaves the same as not designating this option at all.

linewidth specifies the thickness of the line in the chart

Follow image below for the live Google doc with sparklines

Sparklines can be added to Google Sheets to act as miniature charts to show trends in data.  They are simpler than Charts but not as versatile.  If you have ever tried to insert a decent chart into a spreadsheet, you can appreciate the simplicity of using Sparklines.

More specific sparkline articles…

• Bar charts
• Column and and win loss
• Line graph

Video explanation

To insert a Sparkline, you must use a formula as there is no Sparkline function in the menus.  The syntax is

=SPARKLINE(data,[options])

Using the formula above on a simple data set with no options specified would result in simple Sparklines charts as shown below.

If you wish to make Sparklines charts that look different than the simple line chart, there are many options available.  Be careful when designating the type of chart as the syntax deviates from typical syntax used when creating spreadsheet formulas, or at least it is different from typical Excel syntax.  Prepare for curly braces and lots of commas and quotes…

Chart types

Line

The default option for Sparklines, the same as shown above.  There is no need to specify this as it would be redundant.  See more information on line chart options in this post.

=SPARKLINE(A1:E1,{"charttype","line"})

Bar

This options makes a “stacked bar chart”.

=SPARKLINE(A5:E5,{"charttype","bar"})

Column

Make a chart with columns representing the values.  See more information on line chart options in this post.

=SPARKLINE(A5:E5,{"charttype","column"})

WinLoss

Shows only two different outcomes i.e, true/false, thunder/lightning, peanut butter/jelly.  OK, maybe only one of those was a good example.  See more information on line chart options in this post. 

=SPARKLINE(A13:E13,{"charttype","winloss"})

Follow image below for the live Google Sheet with sparklines