Table of Contents
- INTRODUCTION
- WHEN TO USE SPARKLINE CHARTS ?
- STEPS TO CREATE SPARKLINES IN GOOGLE SHEETS
- SYNTAX OF SPARKLINE FUNCTION
- HOW TO CREATE THE SYNTAX FORMULA OF SPARKLINE TO CREATE THE CHART OF YOUR CHOICE?
- EXAMPLE 1: CREATE A SPARKLINE SHOWING THE SALES DATA FOR 15 EMPLOYEES FOR FIVE WORKING DAYS.
- SOLUTION:
INTRODUCTION
CHARTS are the graphic representation of any data .
Analysis of data is the process of deriving the inferences by finding out the trends, averages etc. about different parameters.
A SPARKLINE is a special type of very compact chart which is created in a single cell.
These charts are useful in checking the trend with respect to time.
Sparkline is mostly used in a group due to their compactness. Sparkline are just like the charts but very small in size. This is a separate feature from the charts.
Difference between charts and sparkline is that , in a chart, we can create charts using many series but sparkline will create a chart only for one data series.
WHEN TO USE SPARKLINE CHARTS ?
WE CAN USE SPARKLINE CHARTS WHEN :
- We need to show the time trend of any parameter.
- We need to show small sized charts (Not with the details as there is no space for details in the sparklines).
- We have many parameters and need to show the corresponding time trend of the parameter. [How the parameter values is going with the time].
- We want to show the LEVEL of the VALUE in a cell itself.
STEPS TO CREATE SPARKLINES IN GOOGLE SHEETS
Let us learn to create the sparkline in Google Sheets.
Currently Google Sheets allow the creation of Sparkline using the FUNCTION only and not through any direct access to the graphical interface like the SPARKLINE IN THE EXCEL.
Let us learn the syntax of the SPARKLINE FUNCTION before we use it.
SYNTAX OF SPARKLINE FUNCTION
The syntax of the SPARKLINE FUNCTION is
=SPARKLINE(DATA, OPTIONS)
- 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.
The following table shows the different options available for the different chart types.
| CHARTTYPE | |
| line | line graph (the default) |
| bar | stacked bar chart |
| column | column chart |
| winloss | special type of column chart that plots 2 possible outcomes: positive and negative (like a coin toss, heads or tails). |
| LINE GRAPHS | |
| xmin | set the minimum value along the horizontal axis. |
| xmax | set the maximum value along the horizontal axis. |
| ymin | set the minimum value along the vertical axis. |
| ymax | set the maximum value along the vertical axis. |
| color | set the color of the line. |
| empty | set how to treat empty cells. Possible corresponding values include: |
| nan | set how to treat cells with non-numeric data. Options are: |
| 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. |
| Column and winloss sparklines: | |
| color | sets the color of chart columns. |
| lowcolor | sets the color for the lowest value in the chart |
| highcolor | sets the color for the highest 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 Charts: | |
| 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. |
HOW TO CREATE THE SYNTAX FORMULA OF SPARKLINE TO CREATE THE CHART OF YOUR CHOICE?
We already saw the syntax of the sparkline.
Let us now learn the way to create the function in a way so that we can present the data in our own style.
The standard parameters are DATA, OPTIONS.
The DATA argument is very clear.
It is simply the range containing the data. For Example, the data can be in A1:A10 or A1:G1 or any other range.
*EITHER CHOOSE A SINGLE ROW OR A SINGLE COLUMN FOR THE DATA.
The second argument i.e. OPTIONS need to be put in the following format to create perfect sparklines.
{“PROPERTY NAME”,”PROPERTY VALUE”; ” PROPERTY NAME 2 “,” PROPERTY VALUE 2″, AND SO ON }
For example,
Suppose we want to create a sparkline in the COLUMN FORMAT and the color of the columns should be red.
Simply refer to the table above and spot the properties you need.
In this case we need two properties only- Charttype and Color.
The option becomes something like
{“Charttype”,”column”; “color”,”red”}
It is that simple!!
So, we’ll explore this further in the examples.
In this way we can create the sparkline chart in Google Sheets.
EXAMPLE 1: CREATE A SPARKLINE SHOWING THE SALES DATA FOR 15 EMPLOYEES FOR FIVE WORKING DAYS.
SOLUTION:
Before the steps let us formulate the function to be used.
The given sales data is given below.
| MONDAY | TUESDAY | WEDNESDAY | THURSDAY | FRIDAY | |
| EMPLOYEE 1 | 1 | 3 | 0 | 10 | 2 |
| EMPLOYEE 2 | 0 | 9 | 3 | 0 | 7 |
| EMPLOYEE 3 | 0 | 4 | 10 | 0 | 0 |
| EMPLOYEE 4 | 4 | 6 | 3 | 0 | 1 |
| EMPLOYEE 5 | 7 | 7 | 5 | 3 | 5 |
| EMPLOYEE 6 | 10 | 4 | 8 | 6 | 2 |
| EMPLOYEE 7 | 7 | 8 | 5 | 2 | 6 |
| EMPLOYEE 8 | 6 | 2 | 9 | 0 | 9 |
| EMPLOYEE 9 | 9 | 7 | 1 | 7 | 8 |
| EMPLOYEE 10 | 1 | 5 | 1 | 2 | 9 |
| EMPLOYEE 11 | 8 | 6 | 7 | 9 | 9 |
| EMPLOYEE 12 | 1 | 0 | 4 | 3 | 3 |
| EMPLOYEE 13 | 5 | 1 | 8 | 7 | 0 |
| EMPLOYEE 14 | 2 | 9 | 10 | 5 | 8 |
| EMPLOYEE 15 | 3 | 6 | 5 | 9 | 0 |
FOLLOW THE STEPS TO CREATE A SPARKLINE FOR THE 5 days sales data
- 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(D4:H4,{“CHARTTYPE”,”COLUMN”;”COLOR”,”BLUE”})
- The SPARKLINE will be created for the data and we can see that it is simply a column chart within a cell.
- For other employees, simply drag the formula down.
- The problem is solved.
YOU CAN LEARN SPECIFIC SPARKLINES HERE.
HOW TO CREATE WINLOSS SPARKLINE IN GOOGLE SHEETS ?