HOW TO CREATE SPARKLINE [ CHART IN A CELL ] IN GOOGLE SHEETS?

INTRODUCTION

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

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

SPARKLINE FORMULA AND OUTPUT

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.

MONDAYTUESDAYWEDNESDAYTHURSDAYFRIDAY
EMPLOYEE 1130102
EMPLOYEE 209307
EMPLOYEE 3041000
EMPLOYEE 446301
EMPLOYEE 577535
EMPLOYEE 6104862
EMPLOYEE 778526
EMPLOYEE 862909
EMPLOYEE 997178
EMPLOYEE 1015129
EMPLOYEE 1186799
EMPLOYEE 1210433
EMPLOYEE 1351870
EMPLOYEE 14291058
EMPLOYEE 1536590

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”})
ENTER THE FORMULA
  • 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.
FINAL SPARKLINES
  • The problem is solved.

CLICK THE INDIVIDUAL LINK TO LEARN A SPECIFIC TYPE OF SPARKLINE