How to Create Sparklines in Excel 2016

Sparklines is a feature you have in Excel which can help you in a quick analysis of data. Suppose you want to check the range of marks of a particular student or check the trend of sales of a particular product, this feature can be used. Using sparklines, you can create a quick graph of a particular set of data. This data can be a selected few listings from the whole data. One thing that you must be careful is that the comparison of two different sparklines based on their shape isn’t very reliable. This is because two different sparklines may be drawn based on independent sets of data. So it is advisable not to make comparisons among them solely based on their similarity in appearance. With this in mind, you are ready to go!

See also: How to create funnel chart in excel

How to Create Sparklines in Excel 2016

  • Select the cell in which you want to show the graph. Click Insert in the options at the top.

Screenshot (92)

  • You can see an option Sparklines. Choose the type of illustration you wish. Let us see what it looks like with Line.

Screenshot (93)

  • A dialog box appears asking for the range. Click on the icon next to Data Range.

Screenshot (94)

  • Make a selection of the cells based on which you want to make the graph. The range automatically gets updated in the box. Or else you can manually enter the range.

Screenshot (95)

  • If you want to represent the graph in multiple cells, select the Location Range too. Here I have shown the graph in a single cell.
  • Press Enter key and you can see the graph.
  • There are options to change the design of the representation. You can also choose appropriate colors of your liking. For this, click on Design.

Screenshot (97)

  • If you wish, you can also have Column or Win/Loss graphs. Column graphs are bar graph representations. Win/Loss graphs are helpful if you are dealing with negative data such as in the case of revenue.

Screenshot (99)


Now, if you checked on the sparklines feature and found that you are unable to use it, it means that your worksheet is not compatible with them. There are a few Excel sheet formats that you can use to enable sparklines. The compatible extensions are .xlsx, .xlsb, .xlsm.

Read further to know how to enable sparklines if it is appearing disabled in your worksheet.

How to Enable Sparklines

  • Click on File.

Screenshot (99)


  • Click on Save As and choose the destination of the file.

Screenshot (100)

  • Save the file in any of the formats given below.

Excel Workbook

Excel Macro-Enabled Workbook

Excel Binary Workbook

Screenshot (102)

  • Click Save and close the file. Upon opening it again, you will see the sparklines option being enabled. It is important to close the file and open again. This will make the option appear.

Hope it helps!