Sparklines in Excel

Excel Sparklines (Table of Contents)

• Sparklines in Excel
• How to Create a Sparklines in Excel?

Sparklines in Excel

Sparklines were introduced in the Excel 2010 version. This feature was introduced to represent data in any tiny form of the chart without axis and coordinates. The sparklines can be embedded easily to a single cell and these are typically used to show the trends or variations in numbers entered in your sheet. Sparklines have limited functionality when compared with the regular charts in excel, but it is a user-friendly option as it allows a user to fit their data in a tiny space. There are three types of sparklines in excel which can be used for different examples.

Types of Sparklines in Excel

1. Line: This type of sparkline is a simple line chart similar to the typical line chart, but it is without magnitude and axes.
2. Column: The column sparkline is a column chart which represents values by showing magnitude along it.
3. Win/Loss: The win/loss sparkline type is similar to the column chart, but it does not show a magnitude of the data associated with it. It is used in cases where it represents the output in positive or negative form, which helps the user to compare the results.

How to Create a Sparklines in Excel?

Sparklines in excel is very simple and easy to create. Let’s understand the working of creating Sparklines in Excel with some examples.

Excel Sparklines – Example #1

Consider the below example, which shows fluctuating prices of the display panel in different months.

Steps to insert line sparkline in excel

• Select the empty cell where you wish to insert the line sparkline i.e. K3.

• Click on the Insert tab, under which you need to select the Line option from the Sparklines menu appearing on the toolbar.

• After selecting the Line option a Create Sparklines dialog box will appear.

• In which we need to enter the data range in the Data Range box. In this example we have selected the data range as C3:J3 and in the Location Range box you don’t have to enter as it fills it automatically after you select the empty cell for your sparkline to be inserted. After that, Click OK.

• The line sparkline is inserted.

Excel Sparklines – Example #2

Consider the same example as mentioned above for the column sparklines.

Steps to insert column

• Follow the similar 1st and 2nd step of inserting line sparkline, but instead of selecting the Line option, select Column option from the Sparklines menu on the toolbar.

• The Create Sparklines dialog box will appear in which we enter the data range in the Data Range box. In this example we have selected the data range as C3:J3 and the location range is fixed to K3. After that, Click OK.

• We can see the column sparkline inserted for the above data as entered in the sheet.

Excel Sparklines – Example #3

Consider the below example, which shows if the revenue comparison of the company is in profit or loss.

Steps to insert the win/loss column sparklines in Excel

• Follow the similar 1st and 2nd steps from the previous two types of sparklines, but select the Win/Loss option from the Sparklines menu on the toolbar.

• The Create Sparklines dialog box will appear in which enter the data range in the Data Range box. In this example we have selected the data range as D3:I3 and the location range is fixed to J3. After that, Click OK.

• We can see the win/loss sparkline inserted for the above data as entered in the sheet.

Additional things are done by using Sparklines in Excel

If in case you need to edit your sparkline you enter or delete the data. Consider the below example as shown in the below image.

Here we have entered data for September month. So the sparkline shown above is not an updated one, so let us see how to update the sparkline representing our new data range.

• Right click on your sparkline cell where you had previously inserted it. Then click on Sparklines and then Select the Edit Single Sparkline’s Data option.

• The Edit Sparkline Data dialog box will appear. As our new data is entered, we need to select the data range again. The entered data range is C3:K3. After that, Click OK.

• We can observe how the sparkline is updated after we change or alter our data entered in the sheet.

Advantages of Sparklines

• The easiest way to represent the data in form of a line or column chart and in tiny form.

Disadvantages of Sparklines

• The disadvantage of sparkline is that it does not show the values associated with it, for a new person interpreting your data can face problem to understand it.

Things to Remember About Sparklines in Excel

• The sparkline updates itself automatically for the data changed, but only for the selected or pre-defined data range.
• The size of the sparkline can be changed by changing the height or width of the cell in excel.
• The user needs to make sure that the location range comprises of a single row or single column, or else it would give you an error.
• The hidden or empty cells can also be represented by using the sparklines.
• The user can edit the sparkline for group location and data or single sparkline’s data.
• Sparklines option is not available in Excel versions of 2007 and less.

Recommended Articles

This has been a guide to Sparklines in Excel. Here we discuss its types and how to create Sparklines in Excel along with excel examples and downloadable excel template. You may also look at these suggested articles to learn more –

1. Errors in Excel
2. Record Macro in Excel
3. Dynamic Tables in Excel
4. Random Numbers in Excel