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.

0035
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.
0036

  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.
0037

 

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. High Points
0038
  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.
0039
Excel provides option to handle these cases.

    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”.

0040

    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.

0041

  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.

0042
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.
0043
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…

0044

    • 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).

0045

  • Hit OK.

You can see the change now.
0046
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.

0047

  • 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.

0048

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.

0049
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.
0050
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.
0051
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.
0052
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.
Related Articles:

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

Popular Articles
The VLOOKUP Function in Excel
COUNTIF in Excel 2016
How to Use SUMIF Function in Excel

Leave a Reply

Your email address will not be published. Required fields are marked *

Terms and Conditions of use

The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.

Visit Us On TwitterVisit Us On FacebookVisit Us On Google PlusVisit Us On Youtube