Sparkline Bar Chart Formula Options in Google Sheets

Published on

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.

SPARKLINE(data, [options])

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.

Sparkline Bar Chart Formula Basic

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.

Sparkline Bar Chart Formula Options

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

=sparkline(D4,{$A$3:$B$9})

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.

=sparkline(D4,{"charttype","bar";"rtl",FALSE;"empty","zero";"nan","convert";"color1","red";"color2","blue";"max",MAX($D$4:$D$9)})

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.

"rtl" Bar Chart Option

“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.

empty Sparkline Bar Chart Option

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”.

non-numeric value in sparkline chart

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.

set the max value in the horizontal axis of the Sparkline

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.

Sparkline bar without max

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.

=MAX(D4:D7)

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

=MAX(D4:D7)

“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.

how to set color1 and color2 in Sparkline bar

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.

=SPARKLINE(D16:E16,{"charttype","bar";"color1",if(D16>E16,"red","green");"color2",if(E16>D16,"red","green")})

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

change bar color conditionally in Sparkline

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.

Related Reading:

  1. Use of Four Different Sparkline Charts in Google Sheets.
  2. How to Format Data to Make Charts in Google Sheets.
  3. Google Sheets Charts: Built-in Charts, Dynamic Charts and Custom Charts.
Prashanth KV
Prashanth KV
Your Trusted Google Sheets and Excel Guide Prashanth KV brings a wealth of experience in Google Sheets and Excel, cultivated through years of work with multinational corporations in Mumbai and Dubai. As a recognized Google Product Expert in Docs Editors, Prashanth shares his expertise through insightful blogging since 2012. Explore his blog for practical tips and guidance on maximizing your spreadsheet skills.

Hierarchical Numbering Sequences in Excel

Creating hierarchical numbering sequences in an Excel spreadsheet can significantly improve the way you...

How to Easily Repeat a Sequence of Numbers in Excel

Excel offers multiple ways to accomplish tasks, and the simplicity of each approach depends...

Create a Sequence of Dates at Every Nth Row in Excel (Dynamic Array)

Would you like to create a sequence of dates in every nth row in...

XMATCH Row by Row: Finding Values Across a Range in Google Sheets

Using the BYROW function with XMATCH in Google Sheets allows us to match values...

More like this

XMATCH Row by Row: Finding Values Across a Range in Google Sheets

Using the BYROW function with XMATCH in Google Sheets allows us to match values...

Limit Formula Expansion to a Specific Row in Google Sheets

In this tutorial, I’ll explain how to limit the expansion of an array formula...

3-D Referencing Structured Data Tables in Google Sheets

When you have several tables within a single sheet—not across multiple sheets in a...

14 COMMENTS

  1. Hello!

    I have a formula:

    =SPARKLINE (G30;{"charttype";"column";"color1";IF(G30>75%;"Blue")})

    However, I’m encountering a #REF error. Can you help me correct my formula? Thank you.

  2. Hello,

    I am a teacher working on creating a data sheet for report cards.

    We grade off a competency level with the options ME, AE, and EU.

    I was wondering if it is possible to create a progress chart with sparkline that uses the values “ME, AE, or EU” from a dropdown menu in cells to determine progress.

    For now I have switched to numeric values but hope to switch to values mentioned above.

    Thank you so much for your help!

    • Hi, Amy M,

      That seems possible using a SWITCH formula that replaces the SPARKLINE data.

      Here is an example.

      Current Formula:

      =sparkline(D5:G5,{"charttype","bar";"max",10})

      Replace it with the below one once you replace the numerical values with the said strings.

      =sparkline(switch(D5:G5,"ME",1,"AE",2,"EU",3),
      {"charttype","bar";"max",10})

      Feel free to adjust the SWITCH formula based on grades.

  3. Hello,

    I have made a Sparkline in comparison to the max and I would like to change the color if the value is above or below the average.

    =SPARKLINE((RECHERCHEV($A$1;MUFILTRE!$A$3:$B$100;2));
    {"charttype"\"bar";"max"\max(MUFILTRE!$A$3:$B$51);
    "color"\SI(RECHERCHEV($A$1;MUFILTRE!$A$3:$B$100;2))>
    MUFILTRE!$B$52\"#e00000"})

    • Hi, Anthony A,

      I could find two issues.

      1. Replace color with color1.
      2. It seems the logical test is not correct.

      =SPARKLINE(VLOOKUP($A$1;MUFILTRE!$A$3:$B$100;2;false);
      {"charttype"\"bar";
      "color1"\if(VLOOKUP($A$1;MUFILTRE!$A$3:$B$100;2;
      false)>MUFILTRE!$B$52;"red";"blue");
      "max"\max(MUFILTRE!$A$3:$B$51)})

  4. Hello Prasanth,
    I keep getting ERROR!
    The only result I get is when I input =Sparkline(AH4:AJ4), which is the line chart, but I want the bar chart…… I´ve tried everything.

  5. Hello Prasanth,

    Column I = start time, Column J = end time and Column K = Duration (max 3 hours).

    What I intend that:

    When duration is above 3 hours in column K, the sparkline filled with a color (say yellow).
    Less than 3 hours, a combination of two colors showing complete (yellow) Vs Rest (red).

    I didn’t expect I would be receiving a reply.

    Thanks for your prompt reply.

    • Hi, Rajkumar Rishikesh Sinha,

      I think we were very close. Let’s include one more “option” which is “Max”.

      Instead of my previous SPARKLINE formula, try this new one.

      =sparkline({K3,3/24},{"charttype","bar";"color1","yellow";"color2","red";"max",3/24})

      • Hello Prasanth,

        It is working perfectly.

        If possible, can you please explain what ‘3/24’ is doing. I see it is converting to a percentage.

        [I invested complete 2 days working on it. Read a lot of articles written by you.]

        Thanks again.

        • Hi, Rajkumar Rishikesh Sinha,

          Sorry for not explaining.

          If you directly use 3, it will be treated as 3 days, not 3 hours. So dividing 3 by 24, we are actually formatting it to hours.

          Note: Just format the cell that you get the percentage value to duration (Format > Number > Duration) and see what it returns.

  6. Hi,

    Start time | End time | Duration
    13/08/2020 10:21:04 | 13/08/2020 13:06:42 | 02:45:38

    The allotted time period is 3 hours of the Start time. How to show a sparkline (if possible with arrayformula):
    Duration Vs Allotted time (Start time + 3 hours).

    Thanks.

    • Hi, Rajkumar Rishikesh Sinha,

      Here is my sparkline bar chart formula which should be used in cell D2.

      =sparkline({C2,3/24},{"charttype","bar";"color1","red";"color2","green"})

      I assume the timestamp 13/08/2020 10:21:04 (start time) is in cell A2, 13/08/2020 13:06:42 (end time) is in cell B2, and the duration 2:45:38 is in cell C2.

      If you have further values down the rows, you may copy-paste the formula down (won’t expand using ArrayFormula).

LEAVE A REPLY

Please enter your comment!
Please enter your name here

This site uses Akismet to reduce spam. Learn how your comment data is processed.