Md. Araf Bin Jayed
Sometimes, Excel users want to show the deviation in their data set or point out the movement of the data. In this case, using sparklines in Excel will help them. In this article, we will show you the step-by-step procedures of how to use sparklines in Excel.
Download Practice Workbook
You can download the free Excel workbook from here and practice on your own.
Step by Step Procedures to Use Sparklines in Excel
If anyone wants to make their data set in Excel more attractive or more understandable, then using sparklines will be one of the best options in this regard. Using sparklines in Excel helps users analyze data more quickly. In this article, you will find the step-by-step procedures for using sparklines in Excel. Also, you will be able to understand how to customize the existing sparklines. Below is a sample data set that will help us in this regard.
Step 1: Preparing Data Set
We need to prepare a data set for our working purpose. For that,
- First of all, take the following data set, where cells D4 to J4 represent the Sales Per Day.
and cells C6 to C8 represent Week No. - Then, make three extra cells in column K to represent the Weekly Trend.
Step 2: Choosing Sparklines Command
After preparing the data set, we will go for the Sparklines command. To do that,
- Firstly, choose any cell from K6 to K8 in column K under the Weekly Trend header.
- Then, go to the Insert tab in the ribbon.
- Afterward, from there, choose the Sparklines command from the Sparklines group.
- From that option, you will see three types of sparklines.
- Now, choose according to your preference.
- For our example, we will use the Line Sparkline.
Step 3: Selecting Data Range
After choosing the Sparklines command, we have certain things to fill up. They are as follows.
- After choosing the command, a new dialogue box named “Create Sparklines” will appear.
- There are two blank spaces in that box.
- Firstly, in the “Data Range” box, select the range of cells to create a sparkline.
- For example, we will insert the cell range from D6 to J6.
- Secondly, in the “Location Range” box, select the cell location where you want to create the sparkline.
- Finally, press OK.
Step 4: Creating Sparklines
For the final step of creating a sparkline, do the following.
- After pressing OK, you will see a sparkline under the Weekly Trend header in cell K6.
- Similarly, follow the above steps from Step 1 to Step 3 to create more sparklines in cells K7 and K8.
Read More: How to Create Sparklines in Excel (2 Easy Ways)
Formatting Sparklines
Apart from creating the sparklines, you can do a lot more things with them. Sparklines have a lot more other features that will help you to raise the standard of your work. Some of the features and their uses are as follows.
a. Altering the Types of Sparklines
In our steps of creating a sparkline, only one type of sparkline was shown. There are basically three types of sparklines which are-
-
- Line Sparkline
- Column Sparkline and
- Win/Loss Sparkline
To alter the type of an existing sparkline in Excel do as follows.
Step 1:
- Firstly, choose an existing sparkline that you want to alter.
- Here, you will select the sparkline in cell K7.
Step 2:
- Secondly, after choosing the sparkline, you will find a new tab naming Sparkline in the ribbon.
- From there, go to the Type.
- Then, choose the type of sparkline that you will prefer.
- For example, we will choose the Column type.
- Finally, you will be able to see the new sparkline in the cell K7.
b. Making Data Points for Sparklines
If you want to describe more through your sparklines, then making data points for sparklines in Excel will be the best choice. To do so, go through the following steps.
Step 1:
- First of all, choose any existing sparkline.
- For our example, we will choose all of them.
Step 2:
- Secondly, go to the Sparkline tab in the ribbon.
- Here, in the Show group, you will see many options that will mark your sparklines according to their uses.
- From there, we will choose the Markers command.
Step 3:
- Finally, you will see all the data points on the sparklines.
- This feature will help you to analyze your data quickly.
c. Grouping and Ungrouping in Sparklines
Sometimes you have to make changes or add things in more than one sparkline in Excel. Selecting them one by one is much more time-consuming. For this purpose, you can use the group- ungroup feature of sparklines in Excel. For using this feature, do the followings.
Step 1:
- First of all, select the sparklines that you want to group.
Step 2:
- Then, from the Sparkline tab go to the Group.
- There you will find two commands. Group and Ungroup.
- For grouping, your sparklines, choose the Group.
Step 3:
- After choosing, all your sparklines will be grouped.
- Now, if you make any changes to any sparkline, you will be able to see them on other sparklines as well.
- For example, if you make data points for only the sparkline in cell K7, then other sparklines in cells K6 and K8 will also get that.
d. Adding Colors and Styles to Sparklines
If you want to make our sparklines more attractive and beautiful, then you can add colors or change their styles in Excel. After going through the following steps you will be able to use those features.
Step 1:
- First, select the sparkline, that’s color you want to change.
Step 2:
- Then, from the Sparkline tab go to the Style command.
- In that command, you will see two options. Sparkline Color and Marker Color.
Step 3:
- From Sparkline Color, you will be able to change the color of your current sparkline.
- For that choose any color of your preference from the Theme Colors option.
- After that, the color of your sparkline will change.
Step 4:
- To change the style of your sparkline go to the Style group.
- There, you will see many pre-existing styles for sparkline.
- Choose any style from them and make your sparklines more attractive.
e. Setting up Empty Data Cells of Sparklines
Sometimes, you may have empty data cells in your sparkline data set. You can either leave them that way or customize them in Excel. To customize them, you need to follow some steps. You will see the steps in the following.
Step 1:
- First, select the sparkline with empty cells.
- Then, go to the Sparkline tab and from there select the Edit Data option.
- After selecting you will see more commands about editing data.
- From those commands, choose the Hidden & Empty Cells…. command.
Step 2:
- After choosing the command, a new dialogue box naming “Hidden and Empty Cell Settings” will appear.
- There are three options regarding empty cells in there.
- For the first sparkline, choose the Gaps option.
- Then, you will see that, this option will not connect the sparkline where there is gap or empty cells.
Step 3:
- Now, for the second sparkline, we will choose the Zero option.
- After choosing, it will assume the empty cell’s value as zero and will connect the sparkline.
Step 4:
- Finally, for the third sparkline, choose the Conncet data points with line command.
- Now, this command will connect data points by excluding the empty cell.
f. Inserting an Axis for Sparklines
By default, sparklines will take the lowest data point as the bottom value. Sometimes, it may cause some difficulties in understanding. For example, in the below picture, someone may think that the lowest data point here tends to zero in the second sparkline.
So, to overcome this problem, you can use the axis feature of sparkline in Excel. Go through the following steps to do so.
Step 1:
- Firstly, select the sparkline in which you want to insert the axis.
- Then, go to the Sparkline tab and choose the Axis option.
- Now, from that option, select the Custom Value command.
Step 2:
- Secondly, a new dialogue box naming “Sparkline Vertical Axis Setting” will appear.
- Now, here in “Enter the minimum value for the vertical axis”, give a value manually to start the sparkline.
- For example, here we will insert 0 as the value.
- Then, you will see that the shape of the sparkline will change according to the minimum value.
Step 3:
- Now, if you want to show the axis line, then go to the Axis option again from the Sparkline tab.
- Then, from there choose the Show Axis command.
- Finally, you will see the axis under the sparkline.
g. Clearing an Existing Sparkline
Finally, we will show you how you can clear or delete an existing sparkline in Excel. To do that, see the following steps.
Step 1:
- Firstly, select the sparkline that you want to clear.
- Then, go to the Sparkline tab and go to the Clear option in Group.
Step 2:
- Then, select the “Clear Selected Sparklines” command from the option.
- Finally, the existing sparkline will be cleared from the data set.
Read More: How to Change Sparkline Style in Excel (4 Suitable Examples)
Conclusion
That’s the end of this article. I hope you find this article helpful. After reading this article, you will be able to use sparklines in Excel. Please share any further queries or recommendations with us in the comments section below.
