Google Sheets Sparkline Function Examples

The SPARKLINE function allows you to create miniature charts within a single cell to rapidly provide an attractive visual representation of data and trends. Google recently added the two new types - column and winloss - along with the existing line graph and bar chart.

When I am using Google Spreadsheets, I realized it wasn’t capable of displaying the data in the way that I wanted to. So I took another look at the Google Spreadsheet SPARKLINE function and found that, in many cases, they provide a more informative and much quicker visualization that charts. Once I got started, I figured I would exhaust the possibilities of the feature and share them here so that you can get started with them quickly and easily when you need to.

There are four different types of sparkline charts. I have a section for each:

• Horizontal Bar Charts
• Line Charts
• Win/Loss Charts
• Column Charts
• Bonus: Use Hex Colors!

Horizontal Bar Charts

Single Series Data

Best for:  Comparing every cell in a column

Basic Bar Chart

=sparkline(A36,{"charttype","bar";"max",100})

It’s pretty straight forward. Make sure to set the max option so that the bar does not take up the full column.

Reverse Direction with Max Argument and Color

=sparkline(E42,{"charttype","bar";"color1","teal";"rtl",true; "max",max(C$36:C$44)})

rtl (right to left) changes the direction of the bars. Use the

max

function for the value of max with the data column as the range to set the width limit to the highest value in the data column.

Stacked Series Data

Best for:  Comparing multiple columns with each row as parts of whole

Stacked “Versus” Chart

=sparkline(E49:F49,{"charttype","bar";"color1","EB4967";"color2","73A4D3"})

Set color to hex codes for to make visually appealing sparklines. No value for max will set the width of the bar to the width of the cell.

Stacked Bar Chart

=sparkline(E58:H58,{"charttype","bar";"color1","A7DBD8";"color2","69D2E7";"max",max(I$58:I$61);"nan","ignore"})

The Q1 and Q2 values for the 2014 row do not have numerical values. Setting nan to “ignore” will exclude these values from the sparkline bar chart. The max option is set to the highest value of the Total column

Line Charts

Single Series (Y) Data

Best for:  Displaying time series data where value change over consistent time intervals

Standard Line Chart and the Effects of ymin

=SPARKLINE(B3:G3,{"charttype","line"; "color","indigo"; "linewidth",2})

=SPARKLINE(B3:G3,{"charttype","line";"color","indigo"; "ymin",0})

This chart uses a custom color and linewidth. It also demonstrates how to change the chart’s background color by changing the cell’s background color. Both charts display the same data, but because it sets the ymin option to 0, the chart looks much different. Be careful when setting limits and cell dimensions. These adjustments can make the data appear to say different things.

Line Chart with Conditional Coloring

=SPARKLINE(C7:H7,{"color",if(H7>C7,"green","red");"ymax",100; "linewidth",2})

The color is set to an if formula. This formula compares the first and last value of the series, and if the last value in the series is greater than the first value, the line color option is set to green; and if not, the color is set to red. The slope formula is also good for this, but it is better with X,Y series data.

Dual Series (X and Y) Data

Best for:  Displaying time series data where value change over inconsistent time intervals

Line Chart with Conditional Coloring

=SPARKLINE(C12:H13,{"color",if(slope(C13:H13,C12:H12)>0,"green","red"); "linewidth",2; "ymax",25; "xmax",15})

There is a lot going on here, but it builds on the previous example. The slope function is used to set the color for the line. If the slope is greater than 0 the line is green; else it is green. Additionally, ymax and xmax are set to values greater than any data value. This creates a margin around the line chart.

Line Drawing… Because you can

=SPARKLINE(C26:O27,{"color","red"; "linewidth",8})

Sparkline Line Drawing

The lines will follow the X and Y coordinates even if they are not consecutive. Go nuts!