Charts are a powerful tool in Google Sheets and Excel, but sometimes they're a little too...much. Don't get me wrong, most of the time you want a full-fledged chart with a title, axes, graphs, etc. But sometimes, that's overkill for what you had in mind. Maybe you just want a quick-and-dirty representation of data, and you just need basic information to answer simple questions:
- Are we hitting our revenue budget numbers for the quarter, or are we behind?
- What's the trend for the snowpack depth at the Kelly Campus?
- Are we picking up social media subscribers at the same rate as our peer organizations?
I know of a tool that can create these kinds of data visualizations. And I shall call them...Mini-Charts!
No, actually I shall call them SPARKLINES (because that's what Google and Microsoft call them). In Google Sheets, the SPARKLINE function lets you create a mini-chart inside of a cell. This article will walk you through the basics of how to create mini-charts and some ways you can customize them to create a useful dashboard.
The Basics of the Sparkline Function
The Sparkline function's basic syntax looks like this:
=SPARKLINE(some data, optional customization)
Creating a basic Sparkline is pretty easy. Let's say that I'm looking at how much food waste was produced in the Jackson Campus Dining Lodge during the past couple weeks. I have my data already organized in a neat layout:
I want to create a mini-chart in Cell D2, right the below cell labeled "Ort Trend".
1. Select the cell where you want your mini-chart to be displayed.
2. Begin typing: =SPARKLINE(
3. As you can see, Sheets will try and help you out with a dialog box that gives some information about how to use the SPARKLINE function. You can see that it wants some data first, so go ahead and select your data. I'm going to select the Ort totals in column B.
4. Now, if you wanted any fancy customization, you would type a comma and enter those parameters. For this exercise, though, we just want a simple chart, so we'll close the parentheses and hit 'Enter'.
5. Our glorious mini-chart is now complete! We get a mini line graph, showing us the trend in food waste for the week. As you can see, this chart is super-small, and super-basic. In the later sections, we'll cover how you can change the chart type, the axis height, the color, and many other details about your graph.
A Note on [Options]
To customize a Sparkline (the subject of the rest of this tutorial), you'll need to understand the basic syntax for how to tell Sheets the exact way you want your graph displayed. Don't stop reading! These options are useful, and not too hard to figure out. Here's the basic syntax:
=SPARKLINE(B1:B18,{"option1","setting";"option2","setting"})
To add options, you first enclose them in curly braces {like this}, then set each option with a pair of commands: the name of the setting (examples include "charttype", "color", "max", etc), comma, and your choice for that option ("bar","green",10). If you're setting multiple options, you need to put a semicolon between them, as shown above. If you're using words or plaintext (like "color" or "green"), this information has to be enclosed in double-quotes. If you need to input a number (like 0.4 or 17) or a true/false value (like, uh, TRUE or FALSE), you don't use the double-quotes. Got it? Let's practice below. You can also look up ALL of the options available in Google's help article on the SPARKLINE function.
Line Graphs (and how to customize them)
Let's go back to our Ort Trend chart. It looks OK, but we'd like to make some changes to it. First of all, we want to change the color of the line. In our example, it's blue, but we want to change it to red. Here's the result, with the formula shown next to it:
You can use plaintext color names ("black", "green") or you can look up the hex code for the exact color you want. Say you want TSS logo-colored lines...you can do that:
If you're thinking that those two lines are kind of narrow and you'd like them to be thicker, you can use the "linewidth" option to increase the stroke of the line. Note that the "linewidth" option is expecting a number, so you don't want to enclose your line width value in quotes:
In the same way, you can also the minimum and maximum value for the Y-axis of your mini-chart, using the "min" and "max" options. Depending on your data, this may not make much of a difference to how your chart is displayed.
Column Graphs (and how to customize them)
Like line graphs, column graphs can be used to visualize trends over time. To change your default line graph into a column graph, use the "charttype" option and specify "column":
Column graphs have a few unique options that aren't available in line graphs. In our food waste example, let's say we want to shame our students with their absurdly large amount of food waste at Tuesday's dinner. We can't really highlight this mountain of waste with a line graph, but with a column graph we can use the "highcolor" option.
Note that we're using multiple options now, so we need to use a semicolon between each pair of options. There are similar options that highlight one bar of a column chart: "lowcolor", "firstcolor", and "lastcolor", and they all do pretty much what you'd expect. Here's a graph with purple first bar, an orange last bar, a green low bar, and a red high bar:
Bar Graphs (and how to customize them)
You can also use the charttype "bar" to get vertical bar graphs. These seem to work best for individual data groups, rather than a full data set. Let's say you want to see a clearer breakdown of how much food waste was produced each day.
If you look at the formulas for the graph above, note that each bar has its own formula, which is pointing at a single value, the Ort amount for that day. Also, note that I specified the same minimum and maximum for each bar on this list. If you let them auto-scale, you won't get a good comparative visualization:
One of my favorite features of the bar sparkline is the ability to do stacked bar charts. Let's say you want a breakdown of food waste from the meal, but you want to see how much each of two different schools contributed. I included the formula for the first bar, for reference.
Unfortunately, you can't use more than two colors in a sparkline bar chart. If we had three, four, or more schools in the example above, the bars would just alternate back and forth between two colors, rather than giving each school their own unique color.
Win-Loss Graphs (and how to customize them)
The fourth (and last) type of sparkline chart is a win-loss chart. These charts give you a visualization of data with two possible outcomes, like 0 and 1, win and loss, or succeed and fail. If you're tracking food waste over time for a group and you want to see if they met their goal (less than 5 pounds of food waste at each meal), you could use a win-loss chart:
In this example, we can see that the the school group is mostly doing well on their (somewhat weak) goal. The two 'dropped' points in the sparkline chart represent our two 'losses'. We can customize our "negcolor" to make those two losses have a contrasting color and, while we're at it, we can change the win bars to a different color too:
Of course, win-loss charts are only effective with certain kinds of data. If you're tracking data on DJ Khaled, for instance, you don't get a very interesting chart:
*Sidenote: I'm hilarious.
Putting it all Together
You can use for mini-charts to create a dashboard in a Google Sheet that gives you a quick representation to answer the question: how are we doing? For our examples above, we could turn the first page of our food waste-tracking spreadsheet into a dashboard, so we could quickly show students how we're doing this week. With some formatting and a couple of sparklines...
Additional Reading/Resources
I just tried to cover the basics of sparklines in this tutorial, but there are a lot of other creative ways you can use them. First of all, there are more options than just the ones discussed here. You can view the full list of options at Google's official documentation page. If you're interested in creating dashboards like the one above, I found this article by Ben Collins to be extremely helpful. If you're interested in creating dynamic mini-charts that draw their options from elsewhere in your spreadsheet, this article mentions some ideas for how you could do this.