Take a look at the example below. Imagine I\u2019m a teacher and I want to analyze my students\u2019 test scores:<\/p>\n
[\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2020\/12\/SPARKLINE-1-1.png” alt=”sparkline-function-google-sheets-1″ title_text=”sparkline-function-google-sheets-1″ align=”center” _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.16″ global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
As you can see, the results don’t exactly jump out at you from a sea of numbers. And when the data extends\u00a0further 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.<\/p>\n
It isn\u2019t 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:<\/p>\n
- \n
- Line sparkline \u2014 this is the default sparkline in Google Sheets<\/li>\n
- Column sparkline\u00a0 \u2014 like a mini column chart in one cell<\/li>\n
- Bar sparkline\u00a0\u2014 a mini bar chart in one cell<\/li>\n
- Winloss sparkline\u00a0\u2014 a mini column chart for yes\/no or positive\/negative results<\/li>\n<\/ul>\n
[\/et_pb_text][et_pb_text admin_label=”Connections B – Importrange alternative – Horizontal” module_class=”sheetgo-post-no-shadow-img md2-contained-button vertical-banner-container” _builder_version=”4.27.2″ _module_preset=”default” background_color=”#f2f7ff” max_width=”700px” module_alignment=”center” max_height=”300px” custom_margin=”20px|0px|20px|0px|true|true” custom_padding=”25px|25px|25px|25px|true|true” sticky_limit_bottom=”section” border_radii=”on|20px|20px|20px|20px” border_width_all=”1px” border_color_all=”#d9e7ff” global_module=”50475″ saved_tabs=”all” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
![\"\"](\"https:\/\/static.sheetgo.com\/wp-content\/uploads\/2024\/09\/Add-on-sm_sheets-connected-new-connection.webp\")
<\/p>\n![\"39\"](\"https:\/\/static.sheetgo.com\/wp-content\/uploads\/2024\/09\/Recommended-for-Google-Workspace-badge.webp\")
<\/p>\nAn importrange alternative, more efficient and easier to manage<\/span><\/p>\n
![\"\"](\"https:\/\/static.sheetgo.com\/wp-content\/uploads\/2024\/09\/available-on-google-workspace-button-text.webp\")
<\/a><\/p>\n[\/et_pb_text][et_pb_text _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
Syntax<\/h2>\n
[\/et_pb_text][et_pb_text module_class=”spreadsheet-function” _builder_version=”4.16″ border_width_left=”4px” global_colors_info=”{}” theme_builder_area=”post_content”]=SPARKLINE(data, [options])[\/et_pb_text][et_pb_text _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
- \n
- data<\/strong>\u00a0\u2013 this is the reference to the range of cells that you want to plot as a sparkline<\/li>\n
- options<\/strong>\u00a0\u2013 these are used to customize the chart. Google has a full list\u00a0here<\/a>. These can be applied in two ways:\n
- \n
- As an array of option key and option value pairs, following the data parameter.<\/li>\n
- 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.<\/li>\n<\/ul>\n<\/li>\n<\/ul>\n
[\/et_pb_text][et_pb_text _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
How to use SPARKLINE function<\/h2>\n
SPARKLINE(data_range)<\/h3>\n
To accommodate the sparklines, I will insert a blank column after the Name column and give it a header name \u2018Trend\u2019. Now, in cell B2, I\u2019ll type in the function =SPARKLINE(C2:F2)<\/strong>:<\/p>\n
[\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2020\/12\/SPARKLINE-2-1.png” alt=”sparkline-function-google-sheets-2″ title_text=”sparkline-function-google-sheets-2″ align=”center” _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.16″ global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
Hit the Enter<\/strong> 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.<\/p>\n
[\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2020\/12\/SPARKLINE-3-1.png” alt=”sparkline-function-google-sheets-3″ title_text=”sparkline-function-google-sheets-3″ align=”center” _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.16″ global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
To create a sparklines for each and every student, just drag the formula all the way down (or copy paste):<\/p>\n
[\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2020\/12\/SPARKLINE-4-1.png” alt=”sparkline-function-google-sheets-4″ title_text=”sparkline-function-google-sheets-4″ align=”center” _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
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\u2019s scores individually.<\/p>\n
[\/et_pb_text][et_pb_text _builder_version=”4.16″ global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
SPARKLINE(data_range, color_option as key value pair)<\/h3>\n
Next I\u2019ll show you how to change the color of the lines in the sparkline. Notice that both the key (\u201ccolor\u201d) and value (\u201cred\u201d) are within double quotes, and that they are within curly braces\/brackets that indicate an array of key value pairs:<\/p>\n
[\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2020\/12\/SPARKLINE-5-1.png” alt=”sparkline-function-google-sheets-5″ title_text=”sparkline-function-google-sheets-5″ align=”center” _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
The formula here is =Sparkline(C2:F2,{“color”,”red”})<\/strong>.<\/p>\n
SPARKLINE(data_range, [color_option and line_thickness as key value pairs])<\/h3>\n
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.<\/p>\n
[\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2020\/12\/SPARKLINE-6-1.png” alt=”sparkline-function-google-sheets-6″ title_text=”sparkline-function-google-sheets-6″ align=”center” _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
The formula here is =Sparkline(C2:F2,{“color”,”red”;”linewidth”,3})<\/strong>.<\/p>\n
[\/et_pb_text][et_pb_text _builder_version=”4.16″ global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
SPARKLINE(data_range, [column chart_type, other color_options as key value pairs])<\/h3>\n
What if you don\u2019t want line graphs at all? You can change the sparkline to other types of chart. Here I\u2019ll try a column chart and different colors. As you can see, it\u2019s probably not the most attractive or readable sparkline, but it gives you an idea of what you can do.<\/p>\n
[\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2020\/12\/SPARKLINE-7-1.png” alt=”sparkline-function-google-sheets-7″ title_text=”sparkline-function-google-sheets-7″ align=”center” _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.16″ global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
You can also use Hex codes to indicate the colors, like this:<\/span><\/p>\n
[\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2020\/12\/SPARKLINE-8-1.png” alt=”sparkline-function-google-sheets-8″ title_text=”sparkline-function-google-sheets-8″ align=”center” _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text admin_label=”Connections T – One tool – Horizontal” module_class=”sheetgo-post-no-shadow-img md2-contained-button vertical-banner-container” _builder_version=”4.27.2″ _module_preset=”default” background_color=”#f2f7ff” max_width=”700px” module_alignment=”center” max_height=”300px” custom_margin=”20px|0px|20px|0px|true|true” custom_padding=”25px|25px|25px|25px|true|true” sticky_limit_bottom=”section” border_radii=”on|20px|20px|20px|20px” border_width_all=”1px” border_color_all=”#d9e7ff” global_colors_info=”{}” global_module=”50458″ theme_builder_area=”post_content”]
![\"\"](\"https:\/\/static.sheetgo.com\/wp-content\/uploads\/2024\/09\/connections-top-processors-main.webp\")
<\/p>\n<\/p>\nOne tool to merge, split, and filter all your spreadsheet data<\/span><\/p>\n
Find out how<\/a>[\/et_pb_text][et_pb_text _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
Win\/Loss SPARKLINE<\/h3>\n
At this point you\u2019ve seen examples of both the line and column types of sparklines. What about the win\/loss chart type? This is useful for binary data \u2013 items that are either \u2018yes\u2019 or \u2018no\u2019, 1 or 0, heads or tails, etc.<\/p>\n
In the example below, I\u2019ve 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.<\/p>\n
[\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2020\/12\/SPARKLINE-9-1.png” alt=”sparkline-function-google-sheets-9″ title_text=”sparkline-function-google-sheets-9″ align=”center” _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
Barchart for quick data visualization<\/h3>\n
In this example, I\u2019ll 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.<\/p>\n
[\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2020\/12\/SPARKLINE-10-1-1.png” alt=”sparkline-function-google-sheets-10″ title_text=”sparkline-function-google-sheets-10″ align=”center” _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
This is done by typing =Sparkline(B2,{“charttype”,”bar”;”max”,100})<\/strong>.<\/p>\n
[\/et_pb_text][et_pb_text _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
Chart options referenced in other cells<\/h3>\n
In this example, instead of hardcoding the chart options into the formula, I\u2019ll put the options into a table that will be referenced in the function. This allows for easy changing of the options. It\u2019s especially useful if you want to play around with different options to see what looks best.<\/p>\n
[\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2020\/12\/SPARKLINE-11-1.png” alt=”sparkline-function-google-sheets-11″ title_text=”sparkline-function-google-sheets-11″ align=”center” _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.16″ global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
That’s it, that’s how you use the SPARKLINE function in Google Sheets. It has a ton of customization options\u00a0<\/a>and it’s a really handy visualization tool to make your spreadsheets more user-friendly and make the data easier to read.<\/p>\n
Want to know how to insert charts in Google Sheets or make larger charts with your data however? Read our blog posts on how do I insert a chart in Google Sheets <\/a>and how to create a dashboard in Google Sheets.<\/a><\/p>\n
[\/et_pb_text][et_pb_text _builder_version=”4.22.2″ background_color=”#f9f9ff” custom_margin=”40px||40px||false|false” custom_padding=”15px|25px|15px|25px|true|true” border_width_left=”3px” border_color_left=”#808e95″ global_module=”26540″ saved_tabs=”all” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
Editor\u2019s note<\/strong>: This is a revised version of a previous post that has been updated for accuracy and comprehensiveness.<\/em><\/p>\n
[\/et_pb_text][\/et_pb_column][\/et_pb_row][\/et_pb_section]<\/p>\n","protected":false},"excerpt":{"rendered":"
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\u2019m a teacher and I […]<\/p>\n","protected":false},"author":40,"featured_media":29607,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"_et_pb_use_builder":"on","_et_pb_old_content":"In Google Sheets, if you need to visually analyze data, but not by\u00a0building bulky charts every time, SPARKLINE<\/a> formula is the one to go with. It\u00a0helps us come up with mini-sized in-cell charts, that quickly helps us visualize the trends.\n\nLet's assume we have the following set of of data, on which we need to gauge the performance based on the scores of various students of a class.\n\n
![\"SPARKLINE](\"https:\/\/static.sheetgo.com\/wp-content\/uploads\/2017\/11\/sparkline1.png\")
\n\nAt the least, it is not so easy on the eyes. And, when the data extends\u00a0further, it can get even more difficult to grasp how the students are doing over time. To put this in perspective, let's just say we have over a hundred students and they have taken about 15 tests. Of course, we can't imagine establishing trends with bare eyes! And it isn't feasible to build a chart for each student.\n\nWhat, then, would be the solution? It is the SPARKLINE formula! It certainly eases up a lot of things we just discussed.\nSyntax<\/h3>\nSPARKLINE(data, [options])<\/span><\/strong>\n
- \n \t
- data<\/strong> <\/span>- this is the address reference to the range of data cells we want to plot using the SPARKLINE formula<\/li>\n \t
- options<\/strong> <\/span>- these are optional attributes that are used to customize the chart. There are many such options - you may want to take a look at them\u00a0here<\/a>. These can be input in two ways.\n
- \n \t
- As an array of option key and option value pairs, following the data parameter.<\/li>\n \t
- 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.<\/li>\n<\/ul>\n<\/li>\n<\/ul>\n
Usage: SPARKLINE Formula<\/h3>\n
Use Case # 1: SPARKLINE(data_range)<\/h5>\nTo accommodate\u00a0the Sparklines, let us insert a blank column\u00a0after the Name column, and We'll give it a header name 'Trend'. Now, in the cell B2, we'll type in the formula as shown in the snapshot below.\n\n
![\"SPARKLINE](\"https:\/\/static.sheetgo.com\/wp-content\/uploads\/2017\/11\/sparkline2.png\")
\n\nLet's hit the Enter key, and what we see in the cell B2 is the Sparkline. The miniature version of a chart, all of it, nicely embedded within just the area of a cell.\n\n![\"SPARKLINE](\"https:\/\/static.sheetgo.com\/wp-content\/uploads\/2017\/11\/sparkline3.png\")
\n\nWhat we'll need to do now, is that we need such Sparklines for each and every student. It is as simple as dragging the formula all the way down (or just a simple copy paste will do). Here's how it looks like when we do that.\n\n![\"SPARKLINE](\"https:\/\/static.sheetgo.com\/wp-content\/uploads\/2017\/11\/sparkline4.png\")
\n\nThis, certainly,\u00a0is easier to interpret the data, than manually scour the scores against each student. For instance, Brett has done great in consistently improving his scores, which is rather evident from the line that's going upwards. Whereas, Eric has done the opposite. His Sparkline shows that it's a downward trend.\nUse Case # 2: SPARKLINE(data_range, color_option as key value pair)<\/h5>\nSo far, so good. Now, let's try and change the way the lines are colored. Here's how we can do it. Notice that both the key (\"color\") and value (\"red\") are within double quotes, and that they are within curly braces that indicate an array of key value pairs.\n\n
![\"SPARKLINE](\"https:\/\/static.sheetgo.com\/wp-content\/uploads\/2017\/11\/sparkline5.png\")
\nUse Case # 3: SPARKLINE(data_range, [color_option and line_thickness as key value pairs])<\/h5>\nProbably the lines are too thin. Let's try and increase the width. To do that we 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.\n\n
![\"SPARKLINE](\"https:\/\/static.sheetgo.com\/wp-content\/uploads\/2017\/11\/sparkline6.png\")
\nUse Case # 4: SPARKLINE(data_range, [column chart_type, other color_options as key value pairs])<\/h5>\nWhat if we do not want line graphs, after all? We can change it to other chart representations wherever appropriate. Let's try the column chart, shall we? Also, let's explore the other color options as shown in the snapshot below. Not the ideal looking Sparklines, I must admit. But I used this example to drive home the point that chart types can be changed too.\n\n
![\"SPARKLINE](\"https:\/\/static.sheetgo.com\/wp-content\/uploads\/2017\/11\/sparkline7.png\")
\n\nYou can also use Hex codes to indicate the colors, like this.\n\n![\"SPARKLINE](\"https:\/\/static.sheetgo.com\/wp-content\/uploads\/2017\/11\/sparkline8.png\")
\n\nGiven the myriad of customization options<\/a> provided by Google Sheets to SPARKLINE formula, I'd rather encourage you to try\u00a0other variations of the SPARKLINE formula and find out your best suitable combination.","_et_gb_content_width":"","footnotes":""},"categories":[54],"tags":[39,28],"class_list":["post-2550","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-google-sheets-formulas","tag-connections-t","tag-spreadsheets"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.sheetgo.com\/fr\/wp-json\/wp\/v2\/posts\/2550","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.sheetgo.com\/fr\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sheetgo.com\/fr\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sheetgo.com\/fr\/wp-json\/wp\/v2\/users\/40"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sheetgo.com\/fr\/wp-json\/wp\/v2\/comments?post=2550"}],"version-history":[{"count":0,"href":"https:\/\/www.sheetgo.com\/fr\/wp-json\/wp\/v2\/posts\/2550\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.sheetgo.com\/fr\/wp-json\/wp\/v2\/media\/29607"}],"wp:attachment":[{"href":"https:\/\/www.sheetgo.com\/fr\/wp-json\/wp\/v2\/media?parent=2550"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sheetgo.com\/fr\/wp-json\/wp\/v2\/categories?post=2550"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sheetgo.com\/fr\/wp-json\/wp\/v2\/tags?post=2550"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}
- options<\/strong> <\/span>- these are optional attributes that are used to customize the chart. There are many such options - you may want to take a look at them\u00a0here<\/a>. These can be input in two ways.\n
- data<\/strong> <\/span>- this is the address reference to the range of data cells we want to plot using the SPARKLINE formula<\/li>\n \t
- options<\/strong>\u00a0\u2013 these are used to customize the chart. Google has a full list\u00a0here<\/a>. These can be applied in two ways:\n
- data<\/strong>\u00a0\u2013 this is the reference to the range of cells that you want to plot as a sparkline<\/li>\n