Sparklines are miniature charts that you can embed within cells in Google Sheets. They are a great way to visualize data trends and patterns and can also be used to rank numbers.
We’ll be adding sparklines to visualize the differences in these cost amounts.
We want to show each school’s relative cost with miniature line charts. Luckily, line charts are one of the four types of sparklines available. Let’s start by creating one sparkline for the first amount.
Contents
Insert a Basic Bar Chart Sparkline
Let’s insert a sparkling in row 2 to begin.
- Select cell
C2. - Enter the following formula:
=SPARKLINE(B2,{"charttype","bar"})
Where B2 is the number that you want to illustrate.
This formula will create a Sparkline that shows the rank of the $37,846 compared to…itself. Since there is only one number in the range, the bar is at 100%. Next, insert a bar for every school and make the lengths relative. The sparklines will be scaled so that the smallest number is the shortest bar and the largest number is the longest bar.
Now, we will add the max option to set the maximum length of the lines. We will nest the MAX function inside the max attribute, which chooses the largest amount as the maximum line length. Note that lowercase max is an option specific to sparklines, while uppercase MAX is a function you can use anywhere in Google Sheets. The new formula is as follows.
=SPARKLINE(B2,{"charttype","bar";"max",MAX(B$2:B)})
We will copy this formula down through row 6. The row number for B2 will increase by one for each row, while the range B$2:B will not since the $ makes it a fixed reference. Fixing the reference creates the following sparklines.
The sparklines show that Carnegie Mellon University is the most expensive school of the five, while West Virginia University is the most affordable.
You can customize the appearance of sparklines by adding additional options in the Sparkline formula. For example, you can change the chart type, the line color, and the axis labels.
Sparkline Options
The following table shows a list of some of the most common Sparkline options:
| Option | Description |
|---|---|
charttype | Specifies the type of chart to plot. Valid values include line, bar, column, and winloss. |
color1 | Specifies the color of the first bar in the chart. (We won’t be using color2 in this tutorial.) |
max | Highest value on the x-axis |
empty | Specifies how to treat empty cells in the data range. Valid values include zero and ignore. |
nan | Specifies how to treat cells with non-numeric data in the data range. Valid values include convert and ignore. |
Adding Color to Your Sparklines
You can make one bar stand out by conditionally changing its color. For example, our Collge Compare add-on uses colored bar charts to show statistics for your chosen colleges. You can emphasize one college at a time and change the college using the drop-down list in B2:C2.
In order to accomplish this conditional color changing, you must embed an IF statement inside the color option of the SPARKLINE function as follows:
=SPARKLINE(F5,{"charttype","bar";"max",max(F$5:F);"color1", IF(E5=B$2,"red","blue")})
The addition of the IF statement changes the bar’s color from blue to red if the college name matches the name chosen in the drop-down menu.
YouTube Video
See this YouTube video to watch a demo based on these examples.
Template
Install the College Compare add-on to see the colored sparklines in action and copy the formulas for your own use.
Related Posts
Prolific Oaktree describes sparklines overall and bar charts specifically.
Sheetshelp SPARKLINE function page.
Conclusion
Sparklines are a versatile tool for ranking numbers in Google Sheets. Sparklines let you quickly and easily identify a data set’s highest and lowest values. You can also customize sparklines to meet your specific needs.