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.

SPARKLINE(data, [options])

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.

=sparkline(D3:O3,{"charttype","column";"color","blue";"lowcolor","red";"highcolor","green";"firstcolor","black";"lastcolor","black";"negcolor","blue";"empty","zero";"nan","convert";"axis",TRUE;"axiscolor","cyan";"ymin",4;"ymax",22;"rtl",FALSE})

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.

=SPARKLINE(D3:O3,A2:B15)

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.

=SPARKLINE(A2:B2,{"charttype","column"})

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

Example:

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;

=SPARKLINE(A2:B2,{"charttype","column";"ymin",min(A2:B2)})

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.

=SPARKLINE(A2:B2,{"charttype","column";"ymin",0;"ymax",max(A2:B2)})

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.

=sparkline(A2:L2,{"charttype","column";"color","blue";"lowcolor","red";"highcolor","green";"firstcolor","black";"lastcolor","black";"ymin",min(A2:L2);"ymax",MAX(A2:L2)})

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

=SPARKLINE(A2:B2,{"charttype","column";"ymin",0;"ymax",max(A2:B2);"axis",TRUE;"axiscolor","red"})

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!