Table of Contents
- INTRODUCTION
- WHEN TO USE SPARKLINE CHARTS
- BUTTON LOCATION FOR SPARKLINES CHARTS
- WHAT ARE THE DIFFERENT TYPES OF SPARKLINE CHART AVAILABLE IN EXCEL?
- HOW TO CREATE A LINE SPARKLINE IN EXCEL?
- HOW TO INSERT A COLUMN SPARKLINE IN EXCEL
- HOW TO CREATE A WIN/LOSS SPARKLINE IN EXCEL?
- CUSTOMIZE THE SPARKLINES IN EXCEL
- FAQs
- How to change the type of sparkline in Microsoft Excel?
- How to change the style of sparkline in Microsoft Excel?
- How to group or ungroup sparkline in Microsoft Excel?
- How to change the colors of the markers in sparkline in Microsoft Excel?
- Can we highlight some of the important values in the SPARKLINE? OR
- What are markers in the SPARKLINES?
- WANT TO LEARN HOW TO CREATE CHARTS IN EXCEL
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.
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 | ||||||||
| 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 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.
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 | ||||||||
| 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.
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.
- 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.
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 | ||||||||
| 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 |
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.
- 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.
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.
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.
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