Menu

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

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

Excel provides the option to create three types of sparklines.

**LINE**: 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**: These are similar to the column chart. The data is shown as the columns.**WIN/LOSS**: 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.

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

STANDARD | DAY 1 | DAY 2 | DAY 3 | DAY 4 | DAY 5 | DAY 6 | DAY 7 | TREND |

I | 45 | 44 | 46 | 43 | 44 | 30 | 44 | |

II | 45 | 45 | 46 | 44 | 47 | 46 | 44 | |

III | 46 | 44 | 45 | 47 | 44 | 44 | 47 | |

IV | 44 | 47 | 47 | 45 | 44 | 44 | 44 | |

V | 44 | 45 | 46 | 46 | 46 | 46 | 46 | |

VI | 47 | 44 | 47 | 44 | 44 | 47 | 45 |

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.

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

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:

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 attendance of different standards in a school.

LINE SPARKLINE EXAMPLE | ||||||||

PRESENCE OF A WEEK OF DIFFERENT STANDARDS | ||||||||

STANDARD | DAY 1 | DAY 2 | DAY 3 | DAY 4 | DAY 5 | DAY 6 | DAY 7 | TREND |

I | 45 | 44 | 46 | 43 | 44 | 30 | 44 | |

II | 45 | 45 | 46 | 44 | 47 | 46 | 44 | |

III | 46 | 44 | 45 | 47 | 44 | 44 | 47 | |

IV | 44 | 47 | 47 | 45 | 44 | 44 | 44 | |

V | 44 | 45 | 46 | 46 | 46 | 46 | 46 | |

VI | 47 | 44 | 47 | 44 | 44 | 47 | 45 |

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.

- 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.
- It has two fields. DATA RANGE and LCOATION 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.

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.

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 1

LOOSE WITH DIGIT -1

DRAW WITH DIGIT 0

TOURNAMENT STATUS | ||||||||

PRESENCE OF A WEEK OF DIFFERENT STANDARDS | ||||||||

TEAMS | GAME 1 | GAME 2 | GAME 3 | GAME 4 | GAME 5 | GAME 6 | GAME 7 | TREND |

I | 1 | 1 | 1 | 0 | -1 | 1 | 1 | |

II | 1 | 1 | 1 | -1 | 1 | 1 | 1 | |

III | 1 | 1 | -1 | 1 | 1 | -1 | 1 | |

IV | 1 | 1 | 1 | 0 | -1 | 1 | 1 | |

V | -1 | 1 | 0 | 1 | -1 | 0 | 1 | |

VI | -1 | 1 | 0 | 0 | -1 | -1 | 0 | |

WIN | 1 | |||||||

LOOSE | -1 | |||||||

DRAW | 0 |

- 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.
- It has two fields. DATA RANGE and LCOATION 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.

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.

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.

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 PICTURE ABOVE FOR ALL THE DISCUSSION BELOW.

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

**Ans:** 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.

**Ans:** 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.

**Ans:**Learn about the markers in the next questions.vSelect 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.

**Ans:** 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.

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.

OTHER WAYS TO REACH THIS ARTICLE

## WHAT IS EXCEL, HOW EXCEL WORKS, WRITE FORMULA IN EXCEL, EXAMPLE OF USING FORMULA IN EXCEL, ANIMATED EXAMLPE OF USING FORMULA IN EXCEL

YOU MAY LIKE

- HOW TO TROUBLESHOOT A FORMULA IN EXCEL?
- USE OF FONTS, FONT SIZE, INDENTATION, TEXT TO THE RIGHT, TEXT TO THE LEFT, TEXT IN THE MIDDLE ETC.
- how to insert table in excel?
- FOR MORE ARTICLES, CLICK EXCEL OR TYPE IN SEARCH BELOW.

- PICTURES ARE TOO SMALL?? Don’t worry, Click them, and pinch zoom to see them properly. All pictures are in high resolution.
- KINDLY LIKE OUR FACEBOOK PAGE BY CLICKING HERE AND IF WE NEED TO IMPROVE, KINDLY SEND US THE FEEDBACK ON gyankosh060309@gmail.com

%d bloggers like this: