What are the Sparklines in Excel?

Sparklines are most useful when multiple charts are required to represent the data. It is because the chart will take up most of the spreadsheet space. We can save this space by using sparklines in Excel.

Suppose we have the profit and loss data for the first two quarters of 10 sellers. We want to visualize the trend of profit or loss they make in two quarters. Then, we cannot do this with the help of single traditional charts. It can be possible only if sparklines are inserted for each seller.

How to Create Sparklines in Excel?

Example #1 – Using Column

Step 1: Since we are creating a column in sparkline this time, we must select the column instead of a line from the available options.

Steps 2 and 3 will be the same as in the case of “Using a line in the sparkline.”

  • So first, we need to go to the “Insert” tab. Go to the “INSERT” option from the ribbon and choose the “Line” chart from the “Sparklines” options. From the window, enter or select the “Data Range” that has the data. Enter the location of the cell where the sparkline is to be created. It is not usually asked when creating a chart, as a chart is an object. But, sparkline is not an object and needs a location to insert in Excel. After the location from where the data has to be picked and the location of a cell in which the sparkline is to be inserted. We will get the below sparkline. Since we have selected the “Line” method for creating a sparkline, the sparkline will be identical to the “Line” chart.

Go to the “INSERT” option from the ribbon and choose the “Line” chart from the “Sparklines” options.

Since we have selected the “Line” method for creating a sparkline, the sparkline will be identical to the “Line” chart.

Step 4: The column “Sparkline” will look as below.

Example #2 – Using Win/Loss

Step 1: Select the “Win/Loss” from the available “Sparklines” options.

Steps 2 and 3 will be the same as explained before.

Step 4: The “Win/Loss” sparkline will look like the one below.

Example #3 – If in case we have Blank cells or zero value cells in the data

If we have blank cells in the data, the sparklines will be broken and look separated as below.

To correct this situation, we need to change how sparkline treats the empty cell.

Step 1: Select the “Sparkline” and click the “Insert” option. From the “Design” tab, select the “Edit Data” option.

Step 2: From the “Edit Data” option, choose the option “Hidden & Empty Cells.”

Step 3: We can choose how the gaps will be treated from the available options.

Step 4: Below is how each of the options will treat gaps.

If the data has a gap, it makes broken sparklines. Follow the same steps of the method (1,2, and 3). Below are examples of broken sparklines.

To make a “Markers” in the sparklines, go to the “Design” tab and click on the option “Markers” after inserting sparklines.

Then, your marker-added sparklines would look like this.

To show the axis in sparklines, go to the “Design” tab and click on “Axis,” then click on “Show Axis.”

The axis added sparklines would look as given below.

Explanation

When we only need the visualization instead of the full features of traditional charts. We should use sparklines instead of charts as they also have many features that are enough to visualize the data.

Sparklines work the same as a chart, but the only difference is that sparklines are inserted in the cell, and a chart is always outside a cell and is an object to Excel. Getting started with a sparkline is easy as this needs the data to be selected and then insert a sparkline from the ribbon. When we create a sparkline, we have many options about how the sparklines are to be represented. Hence, we can use lines, columns, or win-loss methods. If we use the line sparkline, our chart will be more identical to the line chart. In the case of column chart sparkline, this will match the traditional column chart in excelColumn Chart In ExcelColumn chart is used to represent data in vertical columns. The height of the column represents the value for the specific data series in a chart, the column chart represents the comparison in the form of column from left to right.read more.

We should remember that we can use the column and line chart if we have data. However, we need to show the change in the magnitude of that data via sparkline. We cannot use the “Win/Loss” type if we want to show the importance of change in data, as this type of chart works on the “True” or “False” method only. Therefore, they cannot show a difference in the magnitude of data.

Things to Remember

  • The sparklines are not an object. Therefore, these are inserted into a cell and not on the worksheet area, as done in the case of charts that are inserted as an object and on the worksheet.Even if a sparkline is created in a cell, we can still type in that cell.A sparkline must be deleted from the menu and cannot be deleted with the click of the “Delete” button.The height and width of the sparkline depend on the height and width of the cell in which it is inserted. Therefore, the sparkline look will change if any change is made to the cell’s width and height.We should not use the “Win/loss” method of sparkline if we need to show the magnitude of change because they represent the “True” and “False” situations.

This article is a guide to Sparklines in Excel. Here, we discuss how to create sparklines in Excel using sparkline options such as “Line,” “Column,” “Win/Loss,” “Marker,” “Broken Lines,” and “Axis,” along with practical examples and downloadable Excel templates. You may learn more about Excel from the following articles: –

  • Line Chart ExamplesLine Chart ExamplesThe line chart is a graphical representation of data that contains a series of data points with a line. read moreDynamic Chart in ExcelDynamic Chart In ExcelA dynamic chart is a unique chart in Excel which updates itself when the range of the chart is updated. In static charts, the chart does not change itself when the range is updated.read moreStacked Column Chart in ExcelStacked Column Chart In ExcelA stacked column chart in Excel is a column chart where multiple series of the data representation of various categories are stacked over each other. The stacked series are vertical.read moreHistogram FormulaHistogram FormulaA histogram is a type of graphical representation in excel, and there are various methods to make one. Instead of using the Analysis Toolpak or from the Pivot Table, we can also make a histogram from formulas, such as using FREQUENCY and Countifs formulas together.read more