Sparklines in Excel (A Clear Overview)

It is well known that we can draw a chart easily in Excel. But is it possible to graphically summarize data in a single cell? The answer is “Yes”. And the feature that can help you is called sparklines. This article will guide you completely on sparklines in Excel. Here, you will learn how to insert and modify sparklines easily.

Download Practice Workbook

Download the practice workbook from here.

Introduction to Sparklines in Excel

Sparklines are basically mini graphs that can summarize a row or a column of data in a single cell. These graphs are used to display a trend with respect to time or a dataset’s fluctuation. In short, you may use these sparklines to improve the appearance of your data.

Types of Sparklines in Excel

There are mainly three types of sparklines available in Excel. They are:

• Line Sparklines
• Column Sparklines
• Win-loss sparklines

4 Examples of Inserting Sparklines in Excel

Here, we will discuss 4 examples of inserting sparklines in Excel. For this, we have used a dataset (B4:E8) in an Excel worksheet containing the names of some Cities and their temperatures at 15, 16 & 17 Nov. Now, we need to insert sparklines for each city. So, without further delay, let’s get started.

1. Insert Sparklines in One Cell in Excel

Here, we will demonstrate the steps for inserting sparklines in one cell of an Excel worksheet. For example, we will create the sparkline for New York city in cell F5 of the dataset below. Let’s see the steps below to do so.

Steps:

• First of all, select cell F5 or the cell where you want to assign the sparkline.
• Secondly, go to the Insert tab.

• Thirdly, go to the Sparklines group.
• Fourthly, click on Line.

• In turn, a window named Create Sparklines will pop up.
• Now, keep the cursor in the box of Data Range.
• Then, select the range C5:E5 from the dataset.
• Finally, click OK.

• See the sparkline in cell F5.

• However, if you want the sparklines of the rest of the cities, just drag the fill handle up to cell F8.

2. Add Sparklines in Multiple Cells

In the previous method, we discussed the steps to create sparklines in a single cell. We can also add sparklines for all of the cities at once. The steps to do so are below.

Steps:

• To begin with, select the range F5:F8.
• Then, go to the Insert tab.

• Sparklines group > Line.

• As a consequence, the Create Sparklines window will appear.
• At this time, keeping the cursor in the Data Range box, select the range C5:E8.
• Also, check if the Location Range shows the location of the sparklines accurately or not.
• In the end, click OK.

• Thus, we can create the sparklines (F5:F8) of all the Cities at once.

3. Create Column Sparklines in Excel

The above two methods were about adding Line sparklines. In this method, we will learn how to insert Column sparklines. The process of inserting Column sparklines are almost similar to Line sparklines. Column sparklines are mainly charts with vertical bars. See the steps below.

Steps:

• Firstly, select F5:F8 > Insert tab.

• Next, select Column from the Sparklines group.

• After that, Create Sparklines window > Data Range > C5:E8 > OK.

• Lastly, see the Column sparklines in F5:F8.

4. Form Win/Loss Sparklines

Win/Loss sparklines are almost identical to Column sparklines except here all the bars are of the same size. In this type of chart, the positive values are placed above and the negative values are below the x-axis. We will use the dataset (B4:F8) below to explain this method. The steps for plotting Win/Loss sparklines are below.

Steps:

• First of all, select F5:F8 range > Insert tab.

• Subsequently, find Sparklines group > Click Win/Loss.

• Afterward, Create Sparklines window > Data Range box > select C5:E8 > OK.

• In the end, you will see the Win/Loss sparklines in F5:F8 like the picture below.

How to Add Sparklines for Hidden or Empty Cells in Excel

For this method, we will use the dataset (B4:G6) below where the E6 cell is empty. Now, if we plot Line sparklines in cell G6 for the range C6:F6 then we can see that the sparkline is incomplete. This is because of the empty cell (E6). Compare this sparklines with the one in cell G5 which has no empty cell. However, we can modify this empty cell with some options available in Excel. Let’s see the steps below.

Steps:

• First, select cell G6.
• After selecting the cell, a new tab named Sparkline will appear in the ribbon section.
• Eventually, go to the Sparkline tab.
• Then, click on Edit Data.
• After that, select Hidden & Empty Cells from the dropdown.

• As a consequence, the Hidden and Empty Cell Settings window will show up.
• See the three options in the Show empty cells as section.
• It means, you can display the empty cells in the sparklines as Gaps, Zero or you can also Connect data points with line.
• For our case, we selected Connect points with line option.
• Lastly, click OK.

• Thus, we can connect data points with line for an empty cell in sparklines.
• See cell G6 in the screenshot below.

How to Highlight Data Points in Sparklines in Excel

Let’s say, we have a dataset (B4:G6) in Excel. The dataset contains a Line and a Column sparklines in cells G5 & G6 respectively. We can now highlight the sparklines’ data points with some quick steps. The steps are below.

Steps:

• In the beginning, select G5:G6.

• Then, go to the Sparkline tab.
• Thereupon, you can see some options for highlighting in the show group: High Point, Low Point, Negative Points, First Point, Last Point and Markers.
• In this case, we selected the First Point & Last Point by putting a tick mark in the box.

• As a result, the first and last points of the sparklines are highlighted.

How to Change Color and Style of Sparklines in Excel

Assuming, we have some sparklines in the F5:F8 range of the dataset (B4:F8) below. Now, we can change the appearance of the sparklines by changing their colors and styles. The steps to do so are below.

Steps:

• Firstly, select the range F5:F8.

• Therefore, go to the Sparkline tab > Sparkline Color > Select the color you want.

• Consequently, the color of the sparklines (F5:F8) is changed.

• We can also change the style of the sparklines easily.
• For this, first of all, select the cells containing the sparklines.
• Then, Sparkline tab > Style.
• You can choose any style visible in the Style section.
• Moreover, you can click on the dropdown in the bottom right corner for more style options.

• After clicking on the dropdown, you can see the style options like the screenshot below.

How to Add Axis to Sparklines in Excel

The smallest data point is displayed at the bottom of a sparkline by default, and the other data points are shown in relation to it. This is not accepted in some circumstances as it indicates a large variation. Assuming, we have a dataset (B4:F8) below which contains some column sparklines in cells F5:F8. These sparklines show a huge variation but the difference in the data points is very small. Now, we will add axes to the sparklines to see the actual variation of the data points. See the steps below for this.

Steps:

• Firstly, select the sparklines (F5:F8).

• Accordingly, Sparkline tab > Axis > Custom Value.

• In turn, the Sparkline Vertical Axis Settings window will appear.
• Therefore, type 0 in the Enter the minimum value for the vertical axis box.
• Click OK.

• Thus, you will see the result the same as the picture below.

How to Group & Ungroup Sparklines in Excel

Sometimes, we need to group the sparklines as it is easy and quick to edit or change the total group at once rather than changing one by one. However, we can also ungroup them if we do not require grouping anymore. The steps to group & ungroup the sparklines are below:

Steps:

• To begin with, select the desired sparklines.
• Then, go to the Sparkline tab > Group > Group or Ungroup.
• See the steps in the image below for a better understanding.

How to Delete Sparklines in Excel

We can not delete sparklines by pressing the delete key on the keyboard. We need to follow the steps below to do this:

• First, select the sparklines (F5:F8).

• Next, go to the Sparkline tab.
• Then, go to the Group.
• Finally, click on Clear.

• As a result, all the sparklines will be deleted.

Conclusion

I hope this tutorial will be helpful for you to create and customize sparklines in Excel. Download the practice workbook and give it a try. Let us know your feedback in the comment section. Follow our website ExcelDemy to get more articles like this.