How to use Excel Sparklines : The Tiny Charts in Cell

The Sparklines are the small charts that reside in a single cell. Sparklines are used to show trends, improvement and win-loss over the period. The sparklines are charts but they have limited functionalities as compared to regular charts. In spite of its limitations, sparklines provide features like highlighting, min and maximum points, first point, last point and negative points. There are many other features sparkline provides, which we will explore in this article.
Since the introduction of sparklines in Excel 2010, it has evolved. I am using excel 2016 and I will explain these in-cell charts in this version.

How To Insert Sparkline Chart in Cell?

1. Select the cell in which you want to insert these tiny charts.
2. Go to Insert-->Sparkline-->
3. Click on the chart type you want.
4. Create Sparkline dialog box will open. Select the data range. It should be one dimensional (horizontal or vertical).
5. Hit OK.

Now you have your sparkline chart in one single cell.

Some points you should know about these in-cell charts.

• Sparklines are Dynamic. They change as data changes.
• They are fast and light.
• You can write in the sparkline cell.
• There is some customisation available for sparkline charts.
• Sparkline’s size is relative to cell size. If you change the cell size, the chart size will also change.
• You can’t delete the sparkline by simply pressing the Delete button. You need to delete the cell itself.

Types of Sparkline Chart in Excel

There are three types of Sparklines in Excel.

1. Line Sparkline: The first Graph in the above image is a line chart. This is best used for showing ongoing progress.
2. Column Sparkline: The second chart is a column chart in a cell. The column sparkline is the best chart to show comparative data. All positive values are shown above the axis and negative values are shown below the axis.
3. Win/Loss Sparkline: The third chart in the image is Win-Loss Sparkline. Any positive value is shown above the axis, and any negative value is shown below the axis. A zero value is not shown.

Note: The column and win-loss sparkline may look similar but they are different. The win-loss sparkline does not reflect the magnitude or values in charts. It just shows binary values. Everything positive is one and everything negative is -1.
In the above image, the win-loss sparkline shows that if we have done better in 2019 or worse as compared to 2018.

Highlight Points in Sparkline

It is common but important to highlight points in graphs, so that you can grab attention of management and help make better decisions. The sparkline in excel provides this feature too. You can highlight these below points in these in-cell charts.

1. 1. High Points

1. Low Points
2. First Point
3. Last Point
4. Negative Points
5. Markers (only for line).

1. High Points: When you check high point, the point with maximum value is highlighted.
2. Low Points: Highlights the lowest point in the sparkline.
3. First Point: Highlights the first point of the data in sparkline.
4. Last Point: Highlights the last point of the data in the sparkline.
5. Negative Points: Highlights all negative points in data.
6. Markers: In a line sparkline, it highlights all the points as dots.

Handling Empty Cells in Sparkline

By default, If your data contains empty cells, the sparkline is shown broken, if its a line sparkline. In other words, sparkline just don’t show that point. Hidden cell points are ignored and not shown in the graph.

Excel provides option to handle these cases.

1. 1. Select the sparkline.
   2. Go to Design.
   3. In the left corner, click on Edit Data.
   4. Then. click on “Hidden and Empty Cells”.

1. 1. You will see three options, Gaps (already selected), Zero, Connect Data Points with Line.
   2. If you select zero, the data point will drop to zero. You’ll not see that gap in the sparkline. If you select “Connect data points with the line”, the last point will be connected to the next non-blank cell. The blank cell will be ignored.

1. There is a checkbox of “show data in Hidden rows and columns”. If you check this, all hidden cell points will be shown in the sparkline graph.

You can clearly see, the blank cell is ignored here. The Excel sparkline is only showing four points.

Axis in Sparkline
By default, all the points in the graph are shown relative to the smallest point in the sparkline. For example, in below data, 2147 is the smallest point. We can see that it is shown too low on the graph. But it, itself a large point.

You may not want this to happen. You would like to set an axis to 2000, as all values are above this value. Excel gives you the option you to set the lowest or highest point of sparklines.

Follow these steps to edit Axis of Sparkline:

• • Select the sparkline
  • Go to Design Tab
  • In the right most corner, you’ll find Axis. Click on the Drop Down.
  • In Vertical Axis Minimum Value Option, click on Custom Value…

• • Write the minimum value you want to set. I type 2000. (once you set it, any value less then this value will not be shown in graph).

• Hit OK.

You can see the change now.

Change Data Source And Location of Sparkline
So, if you want to change data source or location, you can do that too.

• • Go to Design tab.
  • Click on Edit Data. The first two options are used to change data and location.
  • If you want to change the location of sparkline, click on Edit Group Location & Data.

• If you just want to edit sparkline’s data, click on the second option instead.

Sparkline Formatting
It’s there is some scope of formatting the sparkline charts.
How to change sparkline type?

To change a sparklines type follow these steps.

• • Select the sparkline.
  • Go to design tab. In type, select the sparkline type of three available options.

How to change sparkline style?

Sparklines have some predesigned styles. You can change design of sparklines following these steps.

• Select the sparkline
• Go to design
• Go to style section, click on drop down, and select your favorite option.

How to change sparkline’s color and markers color?

• In design, you can see, sparkline color and marker color.
• Click on them. Choose the appropriate color.

And it’s done.

How to group sparklines?

• To group two or more sparklines in excel, select them.
• Go to Design tab.
• In the right most corner. Click on Group.

Once you group the sparklines, all of them will be formatted same as the upper-left sparkline.

How to Delete a Sparkline?

You can’t delete the sparkline by simply pressing delete key on keyboard. To delete a sparkline chart follow these steps.

• Select the cells containing sparklines.
• Go to Design tab.
• In right most corner, find clear. Click on it. The selected sparkline will vanish.

You can also delete the cell, column or row that contains the sparkline to delete it. Just make sure it doesn’t affect your data.

So, this is all about the sparkline in Excel. If you have any sparkline tricks under your sleeves, please do share with us in the comments section below.

Download file:

Related Articles:

Add Trend Line in Excel
Pie Charts bring in Best Presentation for Growth in Excel
Perform “Waterfall” Chart in Microsoft Excel

Popular Articles

50 Excel Shortcut to Increase Your Productivity : Get faster at your task. These 50 shortcuts will make you work even faster on Excel.

How to use the VLOOKUP Function in Excel : This is one of the most used and popular functions of excel that is used to lookup value from different ranges and sheets.

How to use the COUNTIF function in Excel : Count values with conditions using this amazing function. You don't need to filter your data to count specific values. Countif function is essential to prepare your dashboard.

How to use the SUMIF Function in Excel : This is another dashboard essential function. This helps you sum up values on specific conditions.