What are some of the options that can be Customised with sparklines?

Being able to use simple features to visualize data and have a more effortless working experience is amazing. Among many other applications, Google Sheets is widely known for its various features that make working so much easier and smoother for users, helping them organize and visualize different types of data within their Sheets. One such feature that Google Sheets offers is Sparklines. This sparkling Google Sheets guide will show you everything you need to know.

Sparkline Google Sheets

Sparklines are miniature charts that you can create within a cell. Although they do not have the features offered by standard charts in Google Sheets, such as the ability to save as an image, advanced customization, handling larger data sets, etc. They are helpful when you are creating a dashboard and want to quickly show a trend, seasonal increase or decrease, or outliers visually.

Using sparklines is an excellent alternative to traditional Google Sheets charts because of how easy they are to create. In this article, I will describe how you can insert sparklines in Google Sheets. Before we begin, you must note that Excel and Google Sheets deal with sparklines differently. While many users in Excel see it as a feature that you can access using the options in the ribbon, Sparklines in Google Sheets are created using the =SPARKLINE formula.

There are four types of sparklines in Google Sheets: Line, Column, Bar, and Win-Loss. All of these sparkline options allow all users to create different kinds of miniature charts that present a different visual trend for a dataset.

So, let’s look into the different types of Google Sheets sparklines, their syntax, and how you can easily use these sparklines in your Google Sheet.

Types of Google Sheets Sparklines

As I mentioned above, Google Sheets has different types of sparklines. The four different types of sparklines Google Sheets offers are:

• Line charts: This is the default sparkline option. The line chart option uses lines to represent the trends in your data. You have different customization options for the line chart, including adjusting the linewidth, which helps determine how wide a line will be in the chart.
• Column Charts: The column option in Google Sheets represents the trends in your data using columns. You can customize the columns to your liking by changing their colors, width, etc.
• Bar Charts: A bar sparkline chart, unlike line and column sparklines, does not accept a large number of values per chart. It displays the magnitude of a value through bars by comparing them to the max value in a dataset. Also, you cannot customize the color of your sparkline bar chart.
•  Win-loss charts:  The Win-Loss sparkline charts are similar to column charts, with one exception: they do not display the magnitude of your data.

Each chart type has different changes you can add to the formula, such as colors, size customizations, minimum and maximum values, and alignment, among other options. In their help section, Google provides a comprehensive list of options for sparkline customizations.

Google Sheets Sparklines Syntax

Now that we have discussed the different types of sparklines, let’s discuss their syntax. First, let’s start off with the basic sparklines chart. After you’ve chosen the cell where you want the sparkline to appear, you will type =SPARKLINE, then press the Enter or Return key. You may notice a small pop-up below the formula bar with a syntax to customize your sparkline like so, =SPARKLINE(data,{options}).

For the data field, you must select the cell range containing the data for which you wish to create a sparkline. You can enter a cell range or simply drag through it to select the range. When you press the Enter or Return key, your newly created sparkline should appear. The options field in the curly brackets is where you can customize your sparklines, such as adjusting the line width or color. The syntax for sparklines other than the line chart is also the same. All you have to do is add “charttype” in the options field.

The basic syntax for sparkline is:

Here are the formulas you would use for the other chart types.

Bar chart: =SPARKLINE(data, {“charttype”, “bar”})

Column chart: =SPARKLINE(data, {“charttype”, “column”})

Win/Loss chart: =SPARKLINE(data, {“charttype”, “winloss”})

Using Sparklines

Let’s take the following grocery store visitor sheet to create a step-by-step guide with different sparkline examples:

Creating a Sparkline Line Chart

Step 1. Go to an empty cell and type in the formula =SPARKLINE, and select the cell range with the data.

In the screenshot above, I selected the cell range B4:H4 since I wanted a sparkline to display the trend of visitors in week 1. I have also specified the color in the curly brackets.

Step 2. Use the same method from Step 1 to create a Sparkline for weeks 2, 3, and 4.

Step 3. Merge all four weekly sparklines into one monthly sparkling. I did so with the following formula

In the above screenshot, I have used a combination of the sparkline and query formula to first bring all the data from the different cell ranges together and then create a sparkline from it. You can use this method to combine multiple sparklines into one.

Creating a Sparkline Column Chart

Use the same formula as before sparkline but add “charttype” into the options field.

In the screenshot above, I used the same method as I used in the sparkline line chart but added “charttype”,”column” in the curly brackets. Also, you must separate two options with a semicolon “;” as I did with chart type and color.

Creating a Sparkline Bar Chart

For this method, instead of the cell range, I used the cells with the total visitors for each week.

In the above screenshot, I used the formula:

I also pressed F4 on the cell range I4:I7 to make it constant (marked by the $ sign). I used total visitors for all four weeks to create a sparkline bar chart for each

Creating a Sparkline Win-Loss chart

For this method, I used the profit column (Column J) to display a win-loss trend.

In the screenshot above, I have again used a similar formula to before, but I have added the necessary parameters to create a win-loss chart, such as the chart type and the color formatting. The Sparkline win-loss chart has displayed a negative value as red and a positive value as green because of the formula I used.

Related Reading: Filter By Color in Google Sheets

Editing and Formatting Sparkline Google Sheets Graphs

Once you’ve successfully inserted sparklines into your Google Sheet, you can edit and format them. Now, this can get a little tricky because it involves a wide range of customization options, and to put a professional look to your sheet, you must know how to perfectly customize your sheet, e.g., adding a diagonal line to your headers, using the correct formatting options, etc. Luckily for you, there are a lot of resources available online that will help you become an expert in Google Sheets and start creating professional-looking sheets seamlessly.

For now, I will mention a few formatting tips you can use to make your sparklines look professional. Let’s break them down by char type:

NOTE:

All of the formatting options mentioned below must be added in curly brackets for the options field of the sparkline formula.

i.e.,

For a single option:

For multiple options:

Line Chart

• Linewidth: This customization helps adjust the width of the lines displayed in a sparkline line chart. To use it, you must add “linewidth” in the options field of your sparkline formula. For example, =SPARKLINE(B4:H4,{“Linewidth”,3}).
• Color: Adjust the line’s color.
• Empty: Determine how to treat empty cells. Possible corresponding values are: “zero” or “ignore”.
• Nan: Determines how to treat cells with non-numeric data. The options are: “convert” and “ignore”.
• Rtl: Determines if the chart should be rendered from right to left. The options you can choose from are true or false.
• Xmin: Adjust the min value along the horizontal axis.
• Xmax: Adjust the max value along the horizontal axis.
• Ymin: Adjust the min value along the vertical axis.
• Ymax: Adjust the max value along the vertical axis.

Column Chart

• Color: Sets the color of chart columns.
• LowColor: Adjust the color of the lowest value in your chart
• HighColor: Adjust the color of the highest value in your chart
• FirstColor: Adjust the color of the first column
• LastColor: Adjust the color of the last column
• NegColor: Adjust the color of all negative columns
• Empty:  Determine how to treat empty cells. Possible corresponding values are: “zero” or “ignore”.
• Nan: Determine how to treat cells with non-numeric data. The options are: “convert” and “ignore”.
• Axis: Determine if an axis needs to be drawn (true or false)
• Axiscolor: Adjust the color of the axis (if applicable)
• Ymin: Adjust the custom minimum data value that you want to use for scaling the height of columns (not applicable for sparkline win-loss)
• Ymax: Adjust the custom maximum data value that you want to use for scaling the height of columns (not applicable for sparkline win-loss)
• Rtl: Determines whether or not the chart is rendered from right to left. The options you can choose from are true or false.

Bar Chart

• Max: Determine the maximum value along the horizontal axis of your chart.
• Color1: Adjusts the first color used for bars in your sparkline chart.
• Color2: Adjusts the second color used for bars in your sparkline chart.
• Empty: Determines how to treat empty cells. Possible corresponding values are: “zero” or “ignore”.
• Nan: Determines how to treat cells with non-numeric data. Options are: “convert” and “ignore”.
• Rtl: Determines if the chart should be rendered from right to left. The options are true or false.

Win-Loss Chart

• LowColor and HighColor Options: Adjust the color of the low and high values in your chart.
• FirstColor and LastColor Options: Adjust the color of the first and last values in your chart.
• NegColor: Adjust the color of all negative columns
• Axis and AxisColor:
• Empty: Determines how to treat empty cells. Possible corresponding values are: “zero” or “ignore”.
• Nan: Determines how to treat cells with non-numeric data. Options are: “convert” and “ignore”.
• Rtl: Determines if the chart should be rendered from right to left. The options are true or false.

Frequently Asked Questions

How Do You Add a Sparkline in Google Sheets?

You may find that creating a Sparkline may be different In Google Sheets than it is in excel. In order to create a Basic Sparkline in Google Sheets, you must first choose the cell where you want the sparkline to appear. Go to the formula bar and type =SPARKLINE, select the cell range with the data, then press the Enter or Return key. You may also notice a small pop-up below the formula bar with syntax to customize your sparkline. If you wish, you can add various formatting options to your formula to adjust the sparkline chart to your liking.

How Does Sparkline Work in Google Sheets?

In order to understand how it works, you must first understand what it actually is. A sparkline is a miniature graph that depicts the trend of numerical data. Sparklines may consist of a line, column, or bar chart that is drawn without axes. It must be noted that sparkline is a function available on various platforms, but the way it functions on google sheets is different. The SPARKLINE function in Google Sheets allows users to create sparkline charts within a cell. It uses a formula to create a miniature chart in a single cell. Also, it can only be used for a smaller dataset. For larger ones, you may use the built-in charts offered by Google Sheets.

Why Are Sparklines Useful?

Users who prefer to show a visual graph of their data instantly find sparklines to be very useful for various reasons. Sparklines are tiny charts that can be placed inside single worksheet cells to visually represent and show a trend in your data. Sparklines can draw attention to important items such as seasonal changes or economic cycles by using a different color to highlight the maximum and minimum values. A sparkline, like a line chart, is excellent for displaying how values change over time. When multiple lines must be plotted, the sparkline can be a useful alternative to the line chart.

Can You Overlay Sparklines?

Although Google Sheets has not failed at providing users with every feature they may need or find useful when working, the option of overlaying sparklines has still not been added, to both google sheets and excel. However, excel users can find an alternative way of doing so, which is by using Excel’s Camera tool. It is as simple as right-clicking the Quick Access Toolbar and selecting Customize Quick Access Toolbar to add Excel’s Camera tool. Selecting All commands from the Choose commands from box in the resulting Excel Options box, scrolling down and selecting the Camera tool, clicking the Add button, and clicking OK. Now, you can create a column/line chart that you wish to overlay.

What Is the Default Choice for Sparklines in Google Sheets?

As I mentioned above, there are four types of sparkline charts, and all of them represent data in different ways. However, the default type of sparkline on google sheets is a line chart. As a result, the “charttype” option in the SPARKLINE formula syntax is unnecessary for you to add when creating a line chart sparkline. You can simply specify the data range, which is up to two columns or rows, and press enter. Whilst a line chart is easy to use, you do, however, have a few customization options for your line graph, unlike the rest of the miniature charts, which offer a wide variety of customization options.

How Many Colors Can a Google Sheets Sparkline Have?

Not all charts have the same customization options. Various colors can be used for a sparkline line chart, whereas for the bar chart sparkline, you can add only two colors. A column sparkline, along with the win-loss chart type, can both have up to six column colors and one axis color, and they have Color customization for the bottom column. The highest column’s color can also be customized in a column chart. Both of the chart types include color customization for the first column, along with color customization for the last column.

Can You Make a Bar Sparkline Vertical in Google Sheets?

A bar chart sparkline is a graph that displays horizontal bars with the axis values displayed at the bottom. It’s a graphical object that’s used to represent data in an Excel or Google Sheets spreadsheet. In Google Sheets, a vertically arranged bar chart is known as a stacked column chart. You can insert it with the Chart Editor, but not with the SPARKLINE function.

Wrapping Up

To sum up, the sparklines feature is excellent when you want an instant graphical depiction of your data in a Line, Column, Bar, or Win-Loss chart.

You may only use it on a smaller dataset, and you have various customization options to choose from for each chart type. It is definitely a feature you should learn if you work with data and often need to show a visual representation of a dataset.

I hope this sparkline Google Sheets guide sufficiently explains the feature to you, and now all you have left to do is try it out for yourself! If you’d like to learn more about Sheets, you could always try out a course.