Sparklines in excel are like a chart in a cell itself, they are tiny visual representations which show the trend of the data whether increasing or decreasing, to insert a sparkline we need to select the cell where we want the sparkline and in the insert tab in the lines section click on sparklines after that we can choose any one of the styles of sparklines.
Excel Sparklines (Table of Contents)
What are the Sparklines in Excel?
- Sparklines are most useful when multiple charts are required to represent the data. This is because the chart will take most of the spreadsheet space and we can save this space by using Sparklines in excel.
- Suppose if we have the data of profit and loss for the first two quarters of 10 sellers and we want to Visualize the trend of profit or loss they make in two quarter’s, then we cannot do this with the help of single traditional charts. This can be possible only if Sparklines are inserted for each of the sellers.
How to Create Sparklines in Excel?
Here we discuss how to create sparklines in excel along with examples.
Example #1 – Using a line in Excel Sparkline
By creating a Sparkline, we meant that we need to insert something in an excel and whenever we need to insert or add something we also need to go to the “Insert” tab of excel.
So first we need to go to the “Insert” tab.
From the ribbon, go to “INSERT” option and choose the line chart from the Sparkline options.
Step 2: From the window, enter or select the “Data Range” that has the data.
Step 3: Enter the location of the cell where the Sparkline is to be created, this is not usually asked in case of creating a chart as a chart is an object for excel but Sparkline is not an object and needs a location to insert in excel.
Step 4: After the location from where the data has to be picked and the location of a cell in which Sparkline is to be inserted in done, then we will get the below Sparkline in excel.
Since we have selected the “line” method for creating a Sparkline, the Sparkline will be very identical to the “Line chart”.
Example #2 – Using Column in Excel Sparkline
Step 1: Since this time we are creating a column in Sparkline, we need to select the column instead of a line from the available options.
Step 2nd and 3rd will be the same as in the case of “Using a line in Sparkline”.
Step 4: The column Sparkline will look like as below.
Example #3 – Using Win/Loss in Excel Sparkline
Step 1: Select the Win/Loss from the available Sparkline options.
Step 2nd and 3rd will be same as explained before.
Step 4: The win/loss Sparkline will look as below.
Example #4 – If in case we have Blank cells or zero value cells in the data
If we have blank cells in the data then the Sparklines will be broken and will look separated as below.
To correct this type of situation we need to change how the empty cell is treated by Sparkline.
Step 1: Select the Sparkline and go the Insert option. From the Design, option selects the “Edit Data” option.
Step 2: From the Edit Data option choose the option of “Hidden and empty cells”.
Step 3: From the available options, we can choose how the gaps are to be treated.
Step 4: Below is how each of the options will treat gaps.
If the data has a gap it makes a broken Sparklines, follow the same steps of Method (1,2 & 3). Below are the Examples of Broken Sparklines.
To Make a “Markers” in the Sparklines, Go to Design tab and Click on the option “Markers”, after inserting sparklines.
Then Your Marker added Sparklines will look like this.
To Show Axis in Sparklines, Go to Design tab and Click on “Axis” then, Click on Show Axis.
Axis added Sparklines will look as given below.
Explanation of Sparklines in Excel
When we only need the visualization instead of full features of traditional charts, then we should use Sparklines instead of charts as sparklines also have many features that are enough to visualize the data.
Sparklines works the same as a chart works, but the only difference is this that Sparklines is inserted in the cell and a chart is always outside a cell and is an object to excel. Getting started with a Sparkline is easy as this just needs the data to be selected and then inserting a Sparkline from the ribbon. When we create a Sparkline in excel, then we have many options about how the Sparklines are to be represented and hence we can choose to use a line, columns or win-loss methods. If we use the line Sparkline then the chart that we get will be more identical to the line chart and in the case of column chart Sparkline, this will match the traditional column chart.
This should be remembered that the column and line chart can be used in case we have data and we need to show the change in the magnitude of that data via Sparkline. We cannot use the Win/Loss type if we want to show the magnitude of change in data as this type of chart works on the “True” or “False” method only and are not capable of showing a change in magnitude of data.
Things to Remember
- Sparklines are not an Object. These are inserted a cell and not on the worksheet area as done in case of charts, that are inserted as an object and on the worksheet.
- Even if Sparkline is created in a cell, we can still type in that cell.
- A Sparkline needs to be deleted from the menu and cannot be deleted with the click of “Delete” button.
- The Height and width of the Sparkline depend on the Height and width of the cell in which it is inserted. This means that Sparkline look will change if any change is made to the width, height of the cell.
- We should not use the “Win/loss” method of Sparkline if we need to show the magnitude of change. This is because they just represent the “True” and “False” situations.
This has been a guide to Sparklines in Excel. Here we discuss how to Create Sparklines in Excel using sparklines options such as Line, Column, Win/Loss, Marker, Broken lines and Axis along with practical examples and downloadable excel template. You may learn more about excel from the following articles –