December 4, 2020

How to create Sparklines in Excel

Let’s say we have a table containing the death count of animals over 6 months of the year 2020 in Kanha Reserve Forest.

Sparklines are used to show the variation of the dataset over time. This resides in a cell in excel and helps the reader for doing better visual analysis. There are 3 types of Sparklines we can create in excel.

  • Line
  • Column
  • Win-loss

Let’s first create a Line sparkline for the first row of the table.

  1. Select the cell where you want to create the sparkline. Here I have selected the cell H3.
  2. Now go to Insert Tab.
  3. In the ‘Sparkline’ group click the option as ‘Line’

4. Now select the Data Range in the Create Sparklines dialogue box. Here we have selected the data range as B3:G3.

5. The Location Range is pre-populated as H3 since we had already selected this cell as our location for this sparkline to be created.

6. Here is our first sparkline of type ‘Line’ has been created in cell H3. Now if we click on the cell H3 containing our sparkline, the Sparkline Tools will be opened. In ‘Design’ tab there are several options available to customize the selected sparkline. Like you can choose the style of your sparkline from ‘Style’ group. You can change the type of your sparkline from Line to Columns or Win/Loss from ‘Type’ group.

7. Like here I have resized the cell H3 and my sparkline adjusted automatically. In Design tab, I have selected all options to display High, Low, First, Last & Negative points and Markers in ‘Show’ group.

8. Now we are creating the Sparkline of type ‘Column’ by selecting the data range as B4:G4 and the sparkline location as H4.

9. Next we are creating the Sparkline of type ‘Win/Loss’ by selecting the data range as B5:G5 and the sparkline location as H5. A Win/Loss sparkline is like column sparkline with only difference as it doesn’t show the value of the data, rather it shows the outcome as binary like positive/negative, true/false and so on.

10. Here we have created all of the three types of sparklines alternatively in cells H3 to H9 for their respective data sets in each of the 7 rows.

11. Now in H8 currently there is sparkline of type ‘Win-Loss’. We selected the cell and in ‘Design’ tab we changed the Sparkline type as ‘Line’ in ‘Type’ group.

Note : 
  1. Sparklines are dynamic means it changes automatically on any change in the underlying dataset.
  2. You can also enter text in the same cell containing sparkline.

Leave a Reply

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