How to Rank Numbers in Google Sheets Using Sparklines

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.

Cost of five US colleges
Comparison of School Costs

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.

Insert a Basic Bar Chart Sparkline

Let’s insert a sparkling in row 2 to begin.

  1. Select cell C2.
  2. Enter the following formula:

=SPARKLINE(B2,{"charttype","bar"})

Where B2 is the number that you want to illustrate.

One sparkline in a cell
School Costs with One Bar Chart

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.

Bar chart sparklines showing relative amounts
Sparklines with Relative Lengths

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:

OptionDescription
charttypeSpecifies the type of chart to plot. Valid values include line, bar, column, and winloss.
color1Specifies the color of the first bar in the chart. (We won’t be using color2 in this tutorial.)
maxHighest value on the x-axis
emptySpecifies how to treat empty cells in the data range. Valid values include zero and ignore.
nanSpecifies 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.

A drop down controlling the conditional coloring of sparklines
Dual Colored Bar Charts

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.

YouTube player

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.

Leave a Comment