CREATE SPARKLINE IN EXCEL

Table of Contents


INTRODUCTION

CHARTS are the graphic representation of any data . As we know that EXCEL is a super analytical tool.

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

BUTTON LOCATION FOR SPARKLINES CHARTS

The button for column chart is found under the INSERT TAB > SPARKLINES SECTION adjacent to the charts section. The location is shown in the picture below.

EXCEL:BUTTON LOCATION FOR INSERTING SPARKLINES CHARTS

The above picture shows the button location for line sparkling, winloss sparkline and column sparkline.

WHAT ARE THE DIFFERENT TYPES OF SPARKLINE CHART AVAILABLE IN EXCEL?

Excel provides the option to create three types of sparklines.

  • LINE SPARKLINE CHART: It is just like a LINE CHART. It represent the data in the form of the line. We can also choose for data points if we need to show them.
  • COLUMN SPARKLINE CHART: These are similar to the column chart. The data is shown as the columns.
  • WIN/LOSS SPARKLINE CHART:  It is a completely new type of sparkline. It shows three type of values.  Positive, Zero or negative. POSITIVE value by a column or bar, NEGATIVE value by an inverted column and a ZERO by absence of both.

HOW TO CREATE A LINE SPARKLINE IN EXCEL?

EXAMPLE DETAILS

The best way to understand any concept is using it. Let us take an example land insert SPARKLINES in the cell next to the table for each row. 

LINE SPARKLINE EXAMPLE
         
         
PRESENCE OF A WEEK OF DIFFERENT STANDARDS
         
STANDARDDAY 1DAY 2DAY 3DAY 4DAY 5DAY 6DAY 7TREND
I45444643443044 
II45454644474644 
III46444547444447 
IV44474745444444 
V44454646464646 
VI47444744444745 

We have taken the attendance of different standards of a school.

The attendance is taken for seven days.The column named TREND will be filled with sparklines.

Follow the steps to create LINE sparklines.

  •  Create the table with data.
  • Remember that the sparkline can be created for a single series. It can be either a row or a column.
  • We are going to create the sparklines for all the standards for the seven days attendance.
  • Select all the rows and go to INSERT>LINE SPARKLINES .[SELECT ONLY NUMERICAL DATA].
  • A dialog box will open which has two fields. DATA RANGE and OUTPUT RANGE.
  • It has two fields. DATA RANGE and LOCATION RANGE. DATA RANGE will be filled already , if we selected the table before inserting the sparkline, otherwise put the range manually.
  • Put the output location range. The range should be such that it is same as the number of rows, if the output is in the COLUMN and vice versa.
EXCEL: STEPS TO CREATE LINE SPARKLINE

So , we have successfully created LINE SPARKLINES in Excel. We can see that the line varies as per the values and makes it quite easy to judge the trend.

Let us try to create the other two types of SPARKLINES for the same data.

MAJOR NOTICEABLE POINTS ABOUT LINE SPARKLINES:

HOW TO INSERT A COLUMN SPARKLINE IN EXCEL

EXAMPLE DETAILS

We already checked the LINE SPARKLINES.Now, let us check the column sparklines. Column sparklines represent the data as the miniature form of columns but represent the difference and not the absolute value of the series values.  

Let us take the example of the seven days of attendance of different standards in a school.

LINE SPARKLINE EXAMPLE
         
         
PRESENCE OF A WEEK OF DIFFERENT STANDARDS
         
STANDARDDAY 1DAY 2DAY 3DAY 4DAY 5DAY 6DAY 7TREND
I45444643443044 
II45454644474644 
III46444547444447 
IV44474745444444 
V44454646464646 
VI47444744444745 

We have taken the attendance of different standards of a school.

The attendance is taken for seven days.

The column named TREND will be filled with the sparklines.

Follow the steps to create COLUMN sparklines.

  •  Create the table with data.
  • Remember that the sparkline can be created for a single series. It can be either a row or a column.
  • We are going to create the sparklines for all the standards for the seven days attendance.
  • Select all the rows and go to INSERT>COLUMN SPARKLINES.[SELECT ONLY NUMERICAL DATA].
  • A dialog box will open which has two fields. DATA RANGE and OUTPUT RANGE.
This image has an empty alt attribute; its file name is image-60.png
  • It has two fields. DATA RANGE and LOCATION RANGE. DATA RANGE will be filled already , if we selected the table before inserting the sparkline, otherwise put the range manually.
  • Put the output location range. The range should be such that it is same as the number of rows, if the output is in the COLUMN and vice versa.
EXCEL:STEPS TO CREATE COLUMN SPARKLINE

So, we have successfully created COLUMN SPARKLINES in Excel. We can see that the columns correspond to the attendance of the particular standard in the week.

The SPARKLINES are just telling the trend and not the absolute values.

COLUMN SPARKLINES show the comparative heights of the column rather than absolute values. So always be careful that it just shows the trends and not the values.

Now, let us try the third one i.e. WIN/LOSS SPARKLINE.

HOW TO CREATE A WIN/LOSS SPARKLINE IN EXCEL?

EXAMPLE DETAILS

We already tried the LINE SPARKLINE and COLUMN SPARKLINES.

Now let us try the third one which is known as WIN/LOSS sparkline.

WIN/LOSS sparkine, as we have already discussed, works like a binary sparkline.

A positive value is represented by a block, negative value by a block of other color and 0 by a gap.

In this example, we have taken a  tally showing the win or loose for six teams for the last seven games.

WIN IS SHOWN BY THE DIGIT 1LOOSE WITH DIGIT -1DRAW WITH DIGIT 0
TOURNAMENT STATUS
         
         
PRESENCE OF A WEEK OF DIFFERENT STANDARDS
         
TEAMSGAME 1GAME 2GAME 3GAME 4GAME 5GAME 6GAME 7TREND
I1110-111 
II111-1111 
III11-111-11 
IV1110-111 
V-1101-101 
VI-1100-1-10 
         
  WIN1     
  LOOSE-1     
  DRAW0     

Follow the steps to create WIN/LOSS sparklines.

  •  Create the table with data.
  • Remember that the sparkline can be created for a single series. It can be either a row or a column.
  • We are going to create the sparklines for all the teams for all seven games.
  • Select all the rows and go to INSERT>WIN/LOSS SPARKLINES .[SELECT ONLY NUMERICAL DATA].
  • A dialog box will open which has two fields. DATA RANGE and OUTPUT RANGE.
This image has an empty alt attribute; its file name is image-60.png
  • It has two fields. DATA RANGE and LOCATION RANGE. DATA RANGE will be filled already , if we selected the table before inserting the sparkline, otherwise put the range manually.
  • Put the output location range. The range should be such that it is same as the number of rows, if the output is in the COLUMN and vice versa.
EXCEL:STEPS TO CREATE WIN/LOSS SPARKLINE

So, we have successfully created WIN/LOSS SPARKLINES in Excel.

We can see that the WINS ARE REPRESENTED BY THE BLUE BLOCKS whereas LOOSE/DEFEATS ARE REPRESENTED BY THE RED BLOCKS AND DRAWS WITH THE BLANKS.

CUSTOMIZE THE SPARKLINES IN EXCEL

Now, when we have learnt to create all types of SPARKLINES in Excel, let us find out the different options which we can apply to customize our SPARKLINES.

To get the options we need to follow the steps given below:

  • Select the SPARKLINE group.
  • The DESIGN TAB will appear on the top. Click the DESIGN TAB.
  • The options will appear as shown in the picture below. The usage of all the options is discussed below.
DESIGN OPTIONS FOR SPARKLINES

Go through the picture above and read the small notes.

There are different options available in the design tab for sparklines. Let us discuss them one by one in a question-answer manner.

REFER THE PICTURE ABOVE FOR ALL THE DISCUSSION BELOW.

FAQs

How to change the type of sparkline in Microsoft Excel?

Select the SPARKLINES and go to DESIGN TAB. Reach the upper left portion in the ribbon and choose the SPARKLINE you want.

How to change the style of sparkline in Microsoft Excel?

Styles are the different looks which Excel provides us to choose from. It can simply be chosen by selecting the Sparkline/Sparkline group and going to DESIGN TAB.

Click the design of your choice as shown in the picture above.

How to group or ungroup sparkline in Microsoft Excel?

Sparklines can be grouped or ungrouped easily. Select the SPARKLINE and go to design tab and click the group or ungroup as shown in the picture above.

How to change the colors of the markers in  sparkline in Microsoft Excel?

Learn about the markers in the next questions. Select the SPARKLINES and go to DESIGN TAB. The color changing options are present on the right side part of the design tab as mentioned in the picture above.

Can we highlight some of the important values in the SPARKLINE? OR

What are markers in the SPARKLINES?

Yes, we can highlight few of the important values in the SPARKLINE. Look at the picture above and the group named SHOW in the left part of the DESIGN TAB for SPARKLINES. In the show group we have different options.

If we select any of the option, it’ll be marked in the sparkline. Check out the picture below.

MARKER OPTIONS IN SPARKLINES

There are different options available as markers such as, high point, low point, negative point , first point and last point. We can set the colors too for each of them. In the picture shown above the markers have given the colors to the different values as shown in the picture.

HIGH POINT: HIGHEST VALUE

LOW POINT: LOWEST VALUE

NEGATIVE POINT: NEGATIVE VALUES

FIRST POINT: THE FIRST VALUES

LAST POINT: THE LAST VALUES

WANT TO LEARN HOW TO CREATE CHARTS IN EXCEL

VISIT HERE [HOW TO CREATE A CHART IN EXCEL].