How to use sparklines in google sheets

How to use sparklines in google sheets

Create mini charts in your spreadsheets to give your data a small visual. You can insert and customize sparklines in Google Sheets.

Sparklines are nifty mini charts that take up only a cell’s worth of space in a spreadsheet. This lets you display your data visually without the need for much space at all, so they work well in many situations.

Microsoft Excel offers a specific feature to insert sparklines, but Google Sheets doesn’t currently offer such a feature. However, that doesn’t mean you can’t use sparklines in Sheets, you just have to go about it a little differently. Here, we’ll show you how to insert and customize sparklines in Google Sheets.

Insert a Basic Sparkline in Google Sheets

We’ll first show you how to insert a basic sparkline to get you started. So, head to Google Sheets, sign in, and open the spreadsheet you want to use.

  1. Select the cell where you want the sparkline.
  2. Go up to the formula bar and type: =SPARKLINE and hit your Enter or Return key.
  3. You may see a small pop-up below the formula bar with the syntax you can use the customize your sparkline. You can click the X to close that box if you like and reopen it anytime by clicking the question mark in the formula bar.
  4. Your cursor should still be in the formula bar waiting for the cells you want to use for the sparkline. You can enter the cell range or simply drag through it which will populate the range for you.
  5. Hit your Enter or Return key and you should see your newly created sparkline.

The default type of chart Google Sheets uses for a sparkline is a line chart. And while this type of graph can accomplish what you need, you might find a different kind more useful.

Sparkline Chart Types and Options

Aside from a line chart, you can use a bar, column, or win/loss graph for your sparkline. You’ll simply add more options to the sparkline function.

The basic syntax: =SPARKLINE(data,)

So after you enter the sparkline function, your data (cell range) comes next, with your options at the end. Here are the example formulas you’d 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”>)

How to use sparklines in google sheets

Each chart type offers its own attributes that can also be added to the formula. This lets you customize things like colors, line width, minimum or maximum value, alignment, and more.

Google offers an inclusive list of options for the sparkline chart type in their help section. So here, we’ll use just a handful of samples.

Line Chart Customization

Since a line chart is just one color, you can actually set it by changing the font color for the cell. So for this example, we just want our line to be wider.

Now our line is much thicker and easier to see:

Bar Chart Customization

For this example, we’re going to change the colors of the bar chart. First, we add the “charttype” option and then the “color1” and “color2” sets.

And for the data we’re using, our bar sparkline chart would look like this:

Column Chart Customization

The next example is for a column chart where we want the highest value columns a specific color.

Here’s the data for the sparkline and how it looks:

Win/Loss Customization

For our win/loss chart, we want to add an axis and color it red.

Take a look at how our win/loss sparkline looks:

Notes on Colors for Sparklines in Google Sheets

Here are just a few notes when customizing your sparkline with colors.

  • Like you can set the color for a line chart by changing the font color for the cell, you can do the same for a win/loss chart.
  • When you add color to a formula, you can use either the color name or the six-character hex code for the color.

As a reminder, you can check Google’s Docs Editors Help section for additional sparkline chart attributes, values, and definitions.

Google Sheets Sparkline Mini Charts Make Great Visuals

Once you start using sparklines, you’ll be surprised at just how handy they can be. This type of space-saving chart might be just the ticket to a nice and neat visual of your data without distracting from it.

How to use sparklines in google sheets

If you want a quick way to analyze your spreadsheet data visually without building full charts, the SPARKLINE function in Google Sheets is a great solution. It allows you to create mini charts inside individual cells, so you can quickly visualize trends.

Take a look at the example below. Imagine I’m a teacher and I want to analyze my students’ test scores:

How to use sparklines in google sheets

As you can see, the results don’t exactly jump out at you from a sea of numbers. And when the data extends further across the spreadsheet, it becomes increasingly difficult to see how your students are doing over time. If I have 100 students who have all taken 15 tests, there’s no way I can analyze the trends with my bare eyes.

It isn’t feasible, time-wise, to build a chart for every single student, but that’s where SPARKLINE function in Google Sheets comes to the rescue. The function allows you to create a mini sparkline chart in a single cell. There are four main types:

  • Line sparkline — this is the default sparkline in Google Sheets
  • Column sparkline — like a mini column chart in one cell
  • Bar sparkline — a mini bar chart in one cell
  • Winloss sparkline — a mini column chart for yes/no or positive/negative results

Syntax

  • data – this is the reference to the range of cells that you want to plot as a sparkline
  • options – these are used to customize the chart. Google has a full list here. These can be applied in two ways:
    • As an array of option key and option value pairs, following the data parameter.
    • As an address reference to a two column range, where the first column cells hold the option keys, and the second column cells hold the corresponding option values.

    How to use SPARKLINE function

    SPARKLINE(data_range)

    To accommodate the sparklines, I will insert a blank column after the Name column and give it a header name ‘Trend’. Now, in cell B2, I’ll type in the function =SPARKLINE(C2:F2):

    How to use sparklines in google sheets

    Hit the Enter key, and the Sparkline appears in cell B2. As a result, the miniature version of a chart is nicely embedded within the area of the cell.

    How to use sparklines in google sheets

    To create a sparklines for each and every student, just drag the formula all the way down (or copy paste):

    How to use sparklines in google sheets

    This sparkline make it way easier for you to quickly interpret large datasets. In this example, I can spot upward or downward trends for my students much more quickly than analyzing each student’s scores individually.

    SPARKLINE(data_range, color_option as key value pair)

    Next I’ll show you how to change the color of the lines in the sparkline. Notice that both the key (“color”) and value (“red”) are within double quotes, and that they are within curly braces/brackets that indicate an array of key value pairs:

    How to use sparklines in google sheets

    SPARKLINE(data_range, [color_option and line_thickness as key value pairs])

    If the lines are too thin, you can simply increase the width. To do that you need to include an additional option key-value pair, as shown below. Notice that the two key-value pairs are separated by a semi-colon.

    How to use sparklines in google sheets

    SPARKLINE(data_range, [column chart_type, other color_options as key value pairs])

    What if you don’t want line graphs at all? You can change the sparkline to other types of chart. Here I’ll try a column chart and different colors. As you can see, it’s probably not the most attractive or readable sparkline, but it gives you an idea of what you can do.

    How to use sparklines in google sheets

    You can also use Hex codes to indicate the colors, like this:

    How to use sparklines in google sheets

    Win/Loss SPARKLINE

    At this point you’ve seen examples of both the line and column types of sparklines. What about the win/loss chart type? This is useful for binary data – items that are either ‘yes’ or ‘no’, 1 or 0, heads or tails, etc.

    In the example below, I’ve changed the data to a binary value of 1 (if the student passed the test) or 0 (if they failed). By using the win/loss format, you can quickly analyze this type of data.

    How to use sparklines in google sheets

    Barchart for quick data visualization

    In this example, I’ll show how to use the bar chart sparkline type to quickly visualize data in a simple way. This is a basic example, but a great way to put simple one-dimensional data into an easy-to-view format.

    How to use sparklines in google sheets

    Chart options referenced in other cells

    In this example, instead of hardcoding the chart options into the formula, I’ll put the options into a table that will be referenced in the function. This allows for easy changing of the options. It’s especially useful if you want to play around with different options to see what looks best.

    How to use sparklines in google sheets

    That’s it, that’s how you use the SPARKLINE function in Google Sheets. It has a ton of customization options and it’s a really handy visualization tool to make your spreadsheets more user-friendly and make the data easier to read.

    Want to make larger charts with your data however? Read our guide on how to create a dashboard in Google Sheets.

    Editor’s note: This is a revised version of a previous post that has been updated for accuracy and comprehensiveness.

    Sparklines can be added to Google Sheets to act as miniature charts to show trends in data. They are simpler than Charts but not as versatile. If you have ever tried to insert a decent chart into a spreadsheet, you can appreciate the simplicity of using Sparklines.

    More specific sparkline articles…

    Video explanation

    To insert a Sparkline, you must use a formula as there is no Sparkline function in the menus. The syntax is

    Using the formula above on a simple data set with no options specified would result in simple Sparklines charts as shown below.

    If you wish to make Sparklines charts that look different than the simple line chart, there are many options available. Be careful when designating the type of chart as the syntax deviates from typical syntax used when creating spreadsheet formulas, or at least it is different from typical Excel syntax. Prepare for curly braces and lots of commas and quotes…

    Chart types

    The default option for Sparklines, the same as shown above. There is no need to specify this as it would be redundant. See more information on line chart options in this post.

    This options makes a “stacked bar chart”.

    Column

    Make a chart with columns representing the values. See more information on line chart options in this post.

    WinLoss

    Shows only two different outcomes i.e, true/false, thunder/lightning, peanut butter/jelly. OK, maybe only one of those was a good example. See more information on line chart options in this post.

    As you may know, we can easily create miniature column charts using Sparkline function. You must know the Sparkline column chart options in Google Sheets to effectively use this miniature chart.

    There are 14 chart options available in the Sparkline column chart. The same options are applicable, except one or two, to another miniature chart called as Winloss. I’ll discuss that in another post.

    The main purpose of using the Sparkline Column Chart in Google Sheets is to compare values across different categories. Each value is represented by vertical bars.

    Understand All Sparkline Column Chart Options in Sheets

    There are a few points that you must know while using the Sparkline function for Column charts. I have explained that after the Syntax.

    Points to be Noted:

    1. “data” can be in columns or rows.
    2. “options” must be enclosed in curly brackets.
    3. Each “option” must be entered as a string (within double quotes).
    4. Each option value must also be entered as a string (within double quotes) but there are exceptions. Enter the Boolean values TRUE or FALSE as well as numbers without double quotes.
    5. Each option must be separated by semicolon and option and option value by a comma .

    In the below formula in cell D4, I have included all the Sparkline Column Chart options in Google Sheets.

    I have merged D4 :O4 to improve the visual appearance of the Sparkline columns as well as readability. Of course, the chart can reside within cell D4 itself.

    You can check the above points within this formula. I’ll explain each and every option after this.

    How to use sparklines in google sheets

    Before starting the explanation section on how to use Sparkline Column chart options in Google Sheets, one more important point.

    You can bring some dynamism to the Sparkline column chart by referring the options outside the formula. Didn’t get?

    See the above same chart with a simple formula in cell D4 this time. The formula is simple as I have entered the Sparkline column chart options outside the formula. The range A2 :B15 contain the Sparkline Column options.

    This way you can control the Sparkline Column chart from outside the formula cell. Now time to learn each and every Sparkline Column chart options in Google Sheets.

    Here is one example to the basic use of Sparkline Column chart.

    “ymin” Sparkline Column Chart Option in Google Sheets

    Use this option to set the minimum value that used for scaling the height of columns in the column chart.

    There are 14 chart options available in the Sparkline Column miniature chart. Then why I am starting with the “ ymin ” option?

    The reason is simple. Even if you can use the SPARKLINE function to render a column chart, only using the “ charttype ” won’t make the chart visually ‘perfect’.

    In the second formula, I have set the “ ymin ” value to 0. Since there is no “ ymin ” value defined in the first formula it takes the min value in the range A2 :B2 as the “ ymin value. It’s like;

    This is the default behavior (the MIN formula returns the min value in the range A2 :B2 ).

    “ ymax ” Sparkline Option in Column Chart

    Use this option to set the maximum value that used for scaling the height of columns.

    I normally set the “ ymax ” value as the maximum value in the data range. I use the Max function for that.

    After “ ymin ” and “ ymax ” Sparkline Column chart options in Google Sheets, we must pay attention to the color options.

    Control the Color of Sparkline Column Chart in Google Sheets

    There are six color controls present in the miniature Sparkline column chart.

    1. “color” – To set the color of columns.
    2. “ lowcolor ” – To set the color of the column of the lowest value.
    3. “ highcolor ” – To set the color of the column of the highest value.
    4. “ firstcolor ” – To set the color of the very first column.
    5. “ lastcolor ” – To set the color of the last column.

    Other than the above, there is one more color option in the Column Sparkline chart formula. That is to set the x-axis color. I’ll come to that later.

    To make you understand, I am going to use all the above Sparkline Column chart options together. Of course, I’ll try to mark what change each option is making in the appearance of columns.

    How to use sparklines in google sheets

    “axis” and “axiscolor” Sparkline Options in Column Chart

    No doubt use these options to turn on the horizontal axis and color it.

    “axis” value TRUE/FALSE decides if an axis needs to be drawn. Use “axiscolor” option to set the axis color.

    When you enable x-axis in the Sparkline column chart, don’t forget to set “ymin” value to 0. If you omit “ymin”, you won’t see the horizontal axis.

    “empty”, “nan”, and “rtl” Options in Sparkline Colulmn Chart Formula

    These options are also a must to use as it controls empty cells, text strings between the data and right to the left rendering of data.

    Example to “rtl” option:

    Unlike the Bar Sparkline chart, in a Column chart, the “ rtl ” doesn’t mean right to left.

    Actually here the “ rtl ” option set to TRUE reverses the max to min. I think it doesn’t even make the columns topsy-turvy.

    Example to “empty” option:

    This option helps you to treat empty cells with the values “zero” or “ignore”.

    I have deleted the value in cell H2 and now it’s blank. See how “empty” option works.

    Example to “nan” option:

    This option controls the cells containing non-numeric data with the values “convert” and “ignore”.

    That’s all about Sparkline Column Chart Options in Google Sheets. Enjoy!

    Excel Solution 1:40 AM Leave a Reply

    How to use SPARKLINE function in Google Sheets?

    ABOUT:

    This function used to create miniature charts contained within a single cell in Google Sheets. This function returns a chart based on the values provided from the reference data set.

    Different type of charts like “Line”, “Bar”, “Column”, “Winloss” can be created by using the same function based on the requirement.

    PURPOSE OF THE FUNCTION:

    To create miniature charts contained within a single cell

    OUTPUT VALUE:

    A miniature chart

    SYNTAX:

    ARGUMENTS:

    data: Required. The range or array containing the data to plot

    options: Optional. A range or array of optional setting and associated values used to customize the chart

    The “Charttype” option defines the type of chart to plot in cell, which include:

    • line” for line graphs (this is default)
    • bar” for stacked bar charts
    • column” for a column charts
    • winloss” for a special type of column chart that plots two (2) possible outputs that is positive and negative.

    Further this charts also can be formatted by giving additional setting to the tool.

    For “line” charts:

    • “xmin” sets the minimum value along the horizontal axis.
    • “xmax sets the maximum value along the horizontal axis.
    • “ymin” sets the minimum value along the vertical axis.
    • “ymax” sets the maximum value along the vertical axis.
    • “color” sets the color of the line.
    • “empty” sets how to treat empty cells. Possible corresponding values include: “zero” or “ignore”.
    • “nan” sets how to treat cells with non-numeric data. Options are: “convert” and “ignore”.
    • “rtl” determines whether or not the chart is rendered right to left. Options are true or false.
    • “linewidth” determines how thick the line will be in the chart. A higher number means a thicker line.

    For “Column” and “Winloss” charts:

    • “color” sets the color of chart columns.
    • “lowcolor” sets the color for the lowest value in the chart
    • “highcolor” sets the color for the higest value in the chart
    • “firstcolor” sets the color of the first column
    • “lastcolor” sets the color of the last column
    • “negcolor” sets the color of all negative columns
    • “empty” sets how to treat empty cells. Possible corresponding values include: “zero” or “ignore”.
    • “nan” sets how to treat cells with non-numeric data. Options are: “convert” and “ignore”.
    • “axis” decides if an axis needs to be drawn (true/false)
    • “axiscolor” sets the color of the axis (if applicable)
    • “ymin” sets the custom minimum data value that should be used for scaling the height of columns (not applicable for win/loss)
    • “ymax” sets the custom maximum data value that should be used for scaling the height of columns (not applicable for win/loss)
    • “rtl” determines whether or not the chart is rendered right to left. Options are true or false.

    Bar type chart is used when two different values are available to plot into chart.

    • “max” sets the maximum value along the horizontal axis.
    • “color1” sets the first color used for bars in the chart.
    • “color2” sets the second color used for bars in the chart.
    • “empty” sets how to treat empty cells. Possible corresponding values include: “zero” or “ignore”.
    • “nan” sets how to treat cells with non-numeric data. Options are: “convert” and “ignore”.
    • “rtl” determines whether or not the chart is rendered right to left. Options are true or false.

    EXAMPLE 1:

    By default the Line chart is selected by syntax “SPARKLINE”. Hence, the above formula will take the data from the given range B2:B13 and returns a line type chart.

    EXAMPLE 2:

    In this function we have specified the type of chart in arguments. We required column chart for the data set reference in B2:B13. Accordingly, the column chart will be generated.

    EXAMPLE 3:

    In this function we required winloss type chart for the data set referenced in B2:B13. Accordingly, we have selected “winloss” chart type in arguments.

    These charts are useful in checking the trend with respect to time.
    Sparklines are mostly used in a group due to their compactness. Sparklines are just like the charts but very small in size. This is a separate feature from the charts.

    Difference between charts and sparklines is that , in a chart, we can create charts using many series but sparkline will create a chart only for one data series.

    In this article we’ll learn creating a specific type of sparkline which is known as WINLOSS SPARKLINE only.

    WHAT IS WINLOSS SPARKLINE ?

    A winloss sparkline is a cell sized chart which only depicts two values, win or loss i.e. a positive value and a negative value.

    Have a look at the picture below.

    How to use sparklines in google sheetsWINLOSS SPARKLINE

    WHEN SHOULD WE USE WINLOSS SPARKLINE ?

    WE CAN USE WINLOSS SPARKLINE CHARTS WHEN :

    • We need to show only the Two or Three states only i.e. Positive, Absent or Negative.
    • When we don’t need to show the levels but the states only.
    • Any WIN LOSS situation.

    STEPS TO CREATE WINLOSS SPARKLINES IN GOOGLE SHEETS

    Kindly first learn the basics here.

    Let us learn to create the sparklines in Google Sheets.

    Currently Google Sheets allow the creation of Sparklines using the FUNCTION only and not through any direct access to the graphical interface like the SPARKLINES IN THE EXCEL.

    Let us learn the syntax of the WINLOSS SPARKLINE FUNCTION before we use it.

    SYNTAX OF SPARKLINE FUNCTION

    The syntax of the SPARKLINE FUNCTION i s

    • DATA is the range which contains the data for the chart.
    • OPTIONS are the different options for customizing our sparkline such as choosing the chart type and other options.
    • The options list is given below.
    • The options can be put of left blank. If left blank a line chart will be created as the sparkline.

    EXAMPLE 1: CREATE A WINLOSS SPARKLINE SHOWING THE PERFORMANCE OF EACH TEAM THROUGHOUT THE TOURNAMENT

    SOLUTION:

    The winloss data is shown below.

    MATCH 1 MATCH 2 MATCH 3 MATCH 4 MATCH 5 MATCH 6
    TEAM A 1 -1 1 1 1 1
    TEAM B -1 1 0 1 -1 -1
    TEAM C 1 1 -1 -1 -1 1
    TEAM D 1 -1 1 -1 1 -1
    TEAM E -1 -1 -1 -1 -1 -1

    DATA SAMPLE

    Winning is shown by a positive value i.e. 1,

    Defeat is shown by a negative value i.e. -1.

    A draw is shown by a zero. i.e. 0

    IT DOESN’T MATTER IF THE POSITIVE VALUE IS 100 OR 1 , IT’LL CREATE A POSITIVE COLUMN I.E. UPRIGHT COLUMN.

    SIMILARLY FOR NEGATIVE VALUES , -1 OR -100, IT’LL CREATE A NEGATIVE COLUMN I.E. INVERTED COLUMN

    Now, let us draw the sparklines for all the tems to check out their performance.

    STEPS TO CREATE WINLOSS SPARKLINE IN GOOGLE SHEETS

    • Select the cell where you want to create the sparkline. For our example, we’ll select the cell I4.
    • Enter the formula in the format = SPARKLINE( DATA RANGE, OPTION RANGE OR OPTIONS IN THE SHOWN FORMAT ABOVE”)
    • For our example, the formula will be =SPARKLINE(C4:H4,<“CHARTTYPE”,”WINLOSS”;”COLOR”,”RED”;”NEGCOLOR”,”GREEN”>)
    • Press ENTER and we can see the sparkline for the TEAM A as shown in the picture below.

    How to use sparklines in google sheetsSPARKLINE FOR TEAM A

    • Now we have created the SPARKLINE for the first Team.
    • For the rest, we can simply drag down the formula and we’ll be all good.
    • Click the small square on the right of the selection rectangle , keep the mouse button pressed and drag down through TEAM E.
    • All the sparklines will be created for all the teams.

    The animation shows the process.

    EXPLANATION OF THE FORMULA USED:

    Let us discuss the formula used to create our sparkline.

    The formula used is

    • The first argument is C4:H4 which is the range of the data.
    • The second arument contains the arguments to set the properties of the sparkline.
    • CHART TYPE fixes the type to the WINLOSS type of sparkline.
    • ; delimits each property from the other.
    • COLOR fixes the color of the bars, which we have set to RED.
    • NEGCOLOR fixes the color of the negative bars which we have set to GREEN.

    You can add as many properties by referring to the table mentioned above.

    ALWAYS REMEMBER

    WINLOSS type of sparkline takes three types of values.

    1. POSITIVE
    2. NEGATIVE
    3. BLANK

    POSITIVE VALUES , WHATEVER BE THE MAGNITUDE OR VALUE, IF IT IS POSITIVE A POSITIVE COLUMN WILL BE SHOWN.

    NEGATIVE VALUE, WHATEVER BE ITS MAGNITUDE OR VALUE, IF IT IS NEGATIVE A NEGATIVE COLUMN WILL BE SHOWN.

    BLANK WILL KEEP A BLANK SPACE WITHOUT ANY COLUMN. THIS SETTING CAN BE SUPPRESSED TOO.

    Sparklines, as seen in this overview post on sparklines, are quick, simple charts that can be inserted directly a the cell of a spreadsheet created with Google Sheets. One of their strengths is their simplicity. However, there are several options that can be used to expand a sparkline’s functionality. Below, we focus on the options available for use with the bar chart type of sparkline.

    sparkline barchart with no options

    Bar chart sparkline options

    max determines the maximum value on the horizontal (x) axis

    “max”,12 Note that this is the value in the first cell so the entire chart is representing just that one value.

    “max”,21 Note that this is the sum of the values in the first two cells so the entire chart is representing just these two values.

    “max”,61 This is the value of all of the cells added together the chart is the same as if you had not specified a max value.

    “max”,61 This is the value of all of the cells added together the chart is the same as if you had not specified a max value. Note that barcharts must use absolute values as the chart is rendered the same way whether or not the cells are negative.

    “ma”,75 setting of 75. This chart illustrates that you can specify a chart max larger than the chart itself and the chart will scale down.

    color1 determines the first color color used for bars in the chart

    “color1″,”red” setting of red. Note that is changes the 1st, 3rd, 5th color, etc

    color2 determines the second color color used for bars in the chart

    “color1″,”red” and “color2″,”yelow” Note that this changes not only the first and second colors, but all of the colors.

    empty how to treat empty cells

    No empty parameter is set for this sparkline bar chart

    zero give the cell a value of zero for the sparkline

    ignore ignore the cell, rendering the chart as if it does not exist

    nan how to treat cells with non-numeric data

    • convert let Sheets try to convert the character(s) in the cell to a number. Good luck on this one.
    • ignore ignore the cell, rendering the chart as if that value does not exist
    • Note in the image that the “ignore” option behaves the same as not designating this option at all.

    rtl changes the direction of the chart from left-to-right to right-to-left

    • true The direction of the chart is flipped
    • false The direction of the chart stays the same
    • Note that the “false” option behaves the same as not designating this option at all.

    Video explanation

    This post is all about the superbly easy to create Sparkline charts in Google Sheets.

    I often use different Sparkline charts in Google Sheets as well as in Excel. This way I try to make my reports stand out from the rest and to the core improve my reports.

    Sparklines are the possibly simplest solution to create charts in a flash. There are four different Sparkline Charts in Google Sheets. You can clearly understand this from the below Sparkline Examples.

    We can create complex charts using the chart menu chart options, no matter whether it’s in Google Sheets or Excel.

    I use these complex forms of charts when I want to submit progress reports or other types of reports to my client or third parties. Otherwise, for the internal purposes, I’m happy to set with my simple Sparkline mini charts.

    In Excel different sparkline charts are available under the Insert menu. But unlike Excel, in Google Spreadsheets, we require to use simple formulas to create this simplest form of charts.

    Update: The Sparkline Chart is now available in the Chart Menu also.

    How to Use the Four Different Sparkline Charts in Google Sheets

    It’s obvious we cannot create combination charts or any similar complex charts using Sparkline. Just use these tiny forms of charts to show trends over a period. I’ll explain the same with custom formula and images below.

    There are four different Sparkline Charts available in Google Doc Spreadsheet. They are “Line”, “Column”, “Bar” and “Winloss”. For me, among these four, the best Sparkline charts are column and line.

    The below syntax and following formulas will explain the use of tiny sparklines.

    Line Sparkline Chart in Google Sheets and Formula Example

    How to use sparklines in google sheets

    Column Sparkline Chart in Google Sheets and Example Formula

    How to use sparklines in google sheets

    Bar Sparkline Chart and Example Formula in Google Sheets

    How to use sparklines in google sheets

    Winloss Sparkline Chart and Example in Google Sheets

    Winloss Sparkline chart is only beneficial when there are negative values in the trends. So for the above range, we can’t apply this formula.

    See how to create a Win-Loss Chart in Google Sheets. The below formula is keyed in N3 and then copied to N4.

    For Winloss Formula Options please refer the Column Sparklines Options above. There are a few changes though.

    1. The “ymin” and “ymax” in Column Sparkline is not applicable in Win-Loss Sparkline Chart.
    2. The “negcolor” option is not applicable in Column Sparkline. You can use this option in Winloss to change the color of all negative columns.

    How to Change the Default Color of the Sparkline Charts in Google Sheets

    I have already detailed this in the above formula options posts. But here is a round-up.

    You can change the color of the all the above four sparkline charts in Google Sheets. See that one by one.

    You can either use the color names (e.g., “red”) or hex codes (e.g., “#DA0000”)

    In this example, I am changing the sparkline color and line width of the Line chart.

    For Bar Sparkline Chart you can specify two colors. I have changed the default color of sparkline here to red and yellow.

    I know without screenshot it’s difficult to understand the changes in the chart. See the below screenshot

    The Winloss and Column sparklines are almost identical. The color setting is also the same for them. You can set the high color and low color on these charts.

    Here, for example, I am changing the high color, low color, and linewidth of both these charts.

    The Winloss Sparkline Formula in H3:

    The Column Sparkline Formula in H4:

    How to use sparklines in google sheets

    Conclusion:

    Use these simple charts in your reports as it can potentially improve the quality of your reports. Just impress your boss, client or customer. Make this part of your dashboard reports.

    The purpose of the above different Sparkline charts is simplicity in use. So we only require the above simple formulas to create.

    I have already detailed all the required information to create beautiful Sparklines in Google Sheets. For more options refer to this Google Doc Help article.

    Sparklines, as seen in this overview post on sparklines, are quick, simple charts that can be inserted directly into the cell of a spreadsheet created with Google Sheets. One of their strengths is their simplicity. However, there are several options that can be used to expand their functionality. Below, we focus on the options available for use with the columns and winloss types of sparkline.

    Column and winloss sparkline options

    color determines the color of the chart’s columns

    lowcolor determines the color for the lowest value in the chart

    highcolor determines the color for the highest value in the chart

    firstcolor determines the color for the first column in the chart

    lastcolor determines the color for the last column in the chart

    negcolor sets the color for the columns with negative values

    empty how to treat empty cells

    • zero give the cell a value of zero for the sparkline
    • ignore ignore the cell, rendering the chart as if it does not exist
    • Note in the image that the “ignore” option behaves the same as not designating this option at all.

    nan how to treat cells with non-numeric data

    • convert let Sheets try to convert the character(s) in the cell to a number. Good luck on this one.
    • ignore ignore the cell, rendering the chart as if that value does not exist
    • Note in the image that the “ignore” option behaves the same as not designating this option at all.

    Video explanation

    axis determines if the chart will have an axis

    • true let Sheets try to convert the character(s) in the cell to a number. Good luck on this one.
    • false ignore the cell, rendering the chart as if that value does not exist
    • Note that the “false” option behaves the same as not designating this option at all

    axiscolor determines if the color of the axis, if you use the axis option to create one

    ymin determines the minimum data value for scaling the height of columns (not applicable for win/loss)

    ymax determines the maximum data value for scaling the height of columns (not applicable for win/loss)

    rtl changes the direction of the chart from left-to-right to right-to-left

    • true The direction of the chart is flipped
    • false The direction of the chart stays the same
    • Note that the “false” option behaves the same as not designating this option at all.

    Follow image below for the live Google doc with sparklines

    Leave a Reply Cancel Reply

    Google Sheets – Learn the Basics

    How to use sparklines in google sheets

    Learn all of the foundational skills necessary to create and use spreadsheets.

    • Create and style your sheet
    • Use cell references
    • Create formulas
    • Sort and filter
    • Share and protect
    • Analyze with pivot tables

    10 downloadable videos, LINKED GOOGLE SHEETS for you to copy and use, quizzes, and built-in notes. Lifetime access to the course.

    Prolific Oaktree on YouTube

    This site has a companion YouTube channel that has pretty much, well almost exactly, the same content. If you like this, you’ll like that.

    With the help of the below examples that include images, you can learn all the available Sparkline Bar chart formula options in Google Sheets.

    In the below SPARKLINE formula syntax, the “options” inside the square brackets are optional. I am going to elaborate on that “options” in this post.

    By default, the Sparkline formula will plot/return the Line chart. So you must use the option “ charttype ” to plot a bar chart using the Sparkline formula. Another must use option is “max”.

    Each option in the formula must be separated by a semicolon. Also, the Sparkline bar chart formula options must put inside curly brackets. Refer the below two formulas to understand this.

    How to use sparklines in google sheets

    In the first formula, the max value which is set to 100 will help to render the bar in the full cell.

    That means if the value in cell A2 is 100 you can see the bar filled the cell else proportionate to the value in cell A2. It’s a must to use the “max” option in a single series bar chart.

    In the second formula, since there are multiple values (stacked Sparkline bar chart), you can omit the max option. If omitted the formula will take the Sum of all the values in the series as the max value.

    All the Sparkline Bar Chart formula options are detailed below under separate subtitles.

    Sparkline Bar Chart Formula Options as Cell Reference

    In the first few examples, instead of directly using the Sparkline options within the formula, I am using them as cell references. Don’t worry, I will show you how to use the options within the formula too.

    I have all the Sparkline Bar chart formula options entered in the range A3 :B9 as below. I am using all of that options first. Then I will explain one by one.

    How to use sparklines in google sheets

    I have the below Sparkline formula in cell E4 which is dragged to down until cell E7.

    This is the simplest way of using all the options in the Sparkline formula in Google Docs Sheets.

    If you manually type the options, the formula in cell E4 will be as follows.

    Now time to learn the options used in the formula leaving the “charttype” which is obviously the “bar”.

    “rtl” Bar Chart Option in Sparkline in Google Sheets

    Use the Boolean FALSE or TRUE to change chart rendering from right to left.

    rtl set to TRUE means render the bar right to left.

    “empty” Sparkline Bar Chart Option

    The “empty” bar chart option in Sparkline function controls empty cells using the values “zero” or “ignore”.

    For this example, I have deleted the value in cell D6. You can set empty to “zero” to remove #N/A error in the formula applied cell.

    Let’s move to the next chart option which is “nan”.

    “nan” Option in Sparkline Bar Chart Formula

    In a Sparkline Bar chart, this option determines what to do with the non-numeric data. If a cell that referred in the formula contains text, you can use the convert “nan” option.

    “convert” and “ignore” are the supporting options in “nan”.

    We will discuss the color options separately. Before that let me clarify what is the option “max” does in a Sparkline Bar chart.

    “max” Bar Chart Option

    This option sets the max value in the horizontal axis in the chart.

    As an example, consider a mini single series bar chart that shows the marks of a set of students out of 50. In this case, you can set the “max” option to 50.

    Note: Formula in cell C2 copied down.

    How to use sparklines in google sheets

    The bar will be rendered in the full cell wherever the students’ marks are 50. If the mark is 25, then the bar will be rendered in half of the cell.

    If the data (see the syntax on the top) contains multiple values, by default, the max value will be set to the sum of the values.

    For example, in the below formula you can exclude the max option which is underlined.

    In my example, see the second screenshot in this tutorial above, you can see the value 64000 in cell B9.

    I have used the below Max formula (not the “max” chart option) in cell B9 to render the chart proportionately to the max value in the range D4:D7.

    What you need to do is change the above formula as below in cell B9 and see the difference.

    “color” Bar Sparkline Chart Option in Sheets

    Color is one of the options that you may want to pay much attention in Sparkline charts. There are the options “color1” and “color2” which determines the color of bars.

    That means, in a bar chart using Sparkline, you can customize the color of the stacked bars (bar 1 and bar 2).

    color1 – To set the first color used for bars in the chart.

    color2 – To set the second color used for bars in the chart.

    In the Sparkline Bar charts, we can use color hex codes instead of color names. For example we can replace “red” with “#FF0000”.

    You can do a Google search to find the hex color codes of your choice. Also, you can use tiny free tools like Instant Eyedropper to identify any pixel on the screen and copy the code.

    Conditional Coloring of Bars in Sparkline Using IF Statement

    You can dynamically change the color of bars in a stacked Sparkline bar chart. I have used the IF logical function to conditionally change colors of a Sparkline bar chart.

    The value in D16 is 50 and E16 is 10. See how the coloring changes based on the values.

    By effectively using the “color” option, we can create a GANTT chart in Sheets. Here is that must to read tutorial – Create a Gantt Chart Using Sparkline in Google Sheets.