Sparklines are tiny charts that authors can use to convey a sense of general trends or the overall shape of data. Most spreadsheet applications, including Google Sheets, support them. They make for a useful lightweight alternative to full-blown charts.
You can insert sparklines into any cell in a spreadsheet using Google’s sparkline function. Although they are a cut-down version of the more powerful built-in charts, they offer several customization options. You can use a variety of sparkline types, plot multiple series, and control individual colors.
What Is a Sparkline?
Data visualization can be complicated, but sparklines take a simplistic approach. A sparkline is a minimal line chart, typically drawn without axes or coordinates. It presents the general shape of data in a simple and condensed way. The data usually follows over time, such as the temperature or stock market prices.
Edward Tufte, an expert in information visualization, introduced the term Sparkline in 2006. But the concept of sparklines is much older. In fact, Tufte himself started to popularise the concept in 1983. There are arguments that the genesis of sparklines dates back to the 19th or 18th century.
How Do I Add a Sparkline to My Spreadsheet?
The default sparkline is a line chart representing a series of numbers. Here’s how to create the most basic example:
1. Enter several numbers in cells A1 to A5 in a spreadsheet.
2. Select A7 and type =SPARKLINE(A1:A5).
3. Press ENTER and the spreadsheet will display your sparkline:
A line chart sparkline may reference two series, representing the x-axis and the y-axis. With two columns (or rows) of data, the first will represent the x-axis, and the second will represent the y-axis.
For a convenient way of creating a sparkline with lots of values, consider the RANDARRAY function. You can feed this into SPARKLINE to create a chart with a number of values of your choice:
=SPARKLINE(RANDARRAY(50))
Sparklines usually appear next to their data so, in a spreadsheet, a sparkline occupies a single cell. If you resize the row or column containing a sparkline, it will grow or shrink accordingly. A sparkline maintains this behavior in the case of merged cells:
Working With Different Types of Chart
The formula above is the simplest form of Google Sheets’ sparkline function. The full syntax of this function is:
=SPARKLINE(data, [options])
The second parameter, options, is not required, but you can use it to customize a sparkline in several ways. It can consist of several supported options at once, which you must format like so:
=SPARKLINE(data, {"option1","value1";"option2","value2"})
The charttype option provides three types of a sparkline as well as the basic line: bar, column, and winloss.
The column is the most straightforward since it’s a series of regular vertical bars, roughly equivalent to a line chart. To turn a sparkline into a column chart, use this syntax:
=SPARKLINE(data, {"charttype","column"})
Note that, unlike a line chart, the column chart acts on only a single series, i.e. one row or column of data.
The bar chart is a stacked one-dimensional chart. It represents each value by the width of a horizontal bar, ordered left to right. The bars have one of two alternating colors. Its properties make it better suited to a smaller amount of data than a line chart. It also better highlights consecutive values that are close together.
The final chart type is winloss. This plots bars, similar to the column type, but only in two possible forms, to represent positive or negative values. Positive values show as a bar above the x-axis, negative values below it. You can use a winloss chart, for example, to show a series of sporting results in which the outcome was either a win or a loss.
Customizing the Appearance of Sparkline Charts
Choosing Different Colors
Line, column, and winloss charts all support a basic color option, used to draw the line or columns. This can be set to one of several names (red, green, blue, orange, etc.) or a hex code for a precise value (e.g. #FF00FF for purple):
You can define the two alternating colors of a bar chart using color1 and color2:
Both column and winloss charts support colors to identify certain values. You can pick out the highest or lowest values using highcolor and lowcolor. You can highlight the first or last columns using firstcolor and lastcolor. And you can highlight negative columns with negcolor:
Varying Thickness
The line chart supports a linewidth option, supporting a small number of values. Note that, since you should generally keep sparklines small, values above 5 aren’t useful.
Displaying Axes
Column and winloss charts can display an x-axis. This will only show up if you’re displaying some values below zero. To enable it, use the axis option with a value of true. You can also set its color using axiscolor:
Changing How Data Appears
Several options allow you to refine the data that the sparkline includes. You can reverse the order of values in any of the chart types using the rtl (right-to-left) option:
Line chart sparklines support xmax, xmin, ymax, and ymin options. These scales or clip the chart in either direction. For example, if an x-axis series runs from 1 to 10 and xmax is set at 5, only half the data will display. The ymin and ymax options also apply to column charts. Bar charts support a max option which constrains the data to that cumulative value.
All chart types allow you to define how they handle empty cells and cells which are not valid numbers (nan).
Varying the Options
All the examples so far have used fixed values for options. However, as with most other things in a spreadsheet, these values can refer to the contents of another cell. For example:
=SPARKLINE(A1:A5, {"color",B1})
While this might not seem that useful, it means you can do things like centralizing the color of all your line charts to a single cell. If you then decide you want them all another color, it’s a simple change:
Enhance Your Google Sheets With Sparklines
Sparklines are no replacement for detailed charts, but they can help to explain context in the smallest of spaces. They can be useful at illustrating overall trends, and you can embed them right alongside the data they represent.
Several Google productivity apps support powerful charts. Once you’ve learned how to work with a chart in one, you can apply the same knowledge to others. This includes Google Docs and Google Slides.