How to Use Excel Sparklines

Excel Sparklines were introduced in Excel 2010 version. It is small line chart that could be easily embedded with the text and gives a great presentable output for easier depiction when aligned next to the tabular data. They are supported in Excel 2010 and above version only. Although sparkline’s design is not limited to lines they can also be represented as columns or win loss. Below example depicts how sparklines look:

When to Use Excel Sparklines

Excel Sparklines can be useful if you have your data in a tabular format. You can place the sparklines next to each row to give a clear graphical presentation of the data selected in that particular row.

How to Use Excel Sparklines

Below listed steps give a clear understanding on how to use excel sparklines:

• Click on the cell where you want to insert the “Sparkline(s)”.
• Go the “Insert” tab on the top ribbon.
• Select the Type of “Sparkline” (Line, Column, Win/ Loss), in this case we will take “Line Sparkline” as example.

• After the above selection a dialog box will open to select the data range for creating sparklines as shown in below image:

• You can enter the data range manually by typing in the details or you can use your mouse to select the data range on excel and it will automatically get reflected in the dialog box.
• After selecting the data range you can simply click on “OK” button and sparklines will be added in each row as shown in below image:

Type of Excel Sparklines

• Line – These sparklines are displayed in the format of simple lines. You could change the style of line, sparkline color and marker color.

• Column – These sparklines are displayed in the format of bars. If the data is of positive value then the column would be lying on the upper axis. For negative data value the column would be below the axis. Likewise for zero value the column would not be displayed and an empty space would be left at that data point. In these sparklines too, you can change the style of column, sparkline color and marker color.
• Win / Loss – These sparklines are displayed in the format of bars like “Column” sparklines although these only represent profit or loss. If the data is showing profit (positive data points) then bars would be above the axis and if the data is showing loss (negative data points) then the bars would be inverted and will be below the axis. If the data is of zero value then the column would not be displayed and an empty space would be left at that data point, as shown in above figure as well. In these sparklines as well, you can change the style of column, sparkline color and marker color.

You can change the type of sparklines for the whole group or for single row by un-grouping the sparklines first. Thus each row can have a different type of sparkline.

Formatting Excel Sparklines

After creating the sparklines you can easily edit \ format sparklines by selecting the sparklines and then by clicking on “Design” tab available at top ribbon. Below are the various options available for formatting sparklines in Excel 2010 and above versions. Below image suggests the various actions that could be performed on sparklines. Most of them are self – explanatory, however below sections provide detailed overview about all of these.

Edit Data: Using Edit Data you can either change the data range or the location of sparklines or both. Follow below steps to change data range and \ or location of sparklines.

• Edit Group Location and Data – You can select this option if you want to change the location and \ or data range for all the sparklines in the group.

You can achieve this by clicking on the entire cells where sparklines are displayed and then selecting “Edit Group Location and Data” from “Edit Data” option from “Design” tab in the ribbon.

• Edit Single Sparkline’s Location and Data – You can select this option if you want to change the location and \ or data range of a single sparkline.

You can achieve this by clicking on the cell where sparkline is displayed and then select “Edit Single Sparkline’s Location and Data” from “Edit Data” option from “Design” tab in the ribbon.

• Hidden and Empty Cells in Data Range – You can use this feature if the data range used in your sparklines is having some empty cells or hidden cells.
  • Empty cells – If you are having empty or blank cells then you can choose how to show them in sparkline.
    • Gaps – You can choose to show the empty cells as “Gaps” by selecting the Radio Button against “Gaps”, as shown in below image.
    • Zero – You can choose to show the empty cells as “Zero” by selecting the Radio Button against “Zero”, as shown in below image.
    • Connect Data Points – Else you can ignore the empty cells by selecting “Connect data points with line”, as shown in below image.

• • Hidden cells – If you have some hidden rows or columns in the then you can easily select how you want these cells to be displayed in Excel.
    • Show Data of Hidden Rows or Columns – You can show the data of the hidden columns or rows by selecting the check box under “Design” tab in “Edit Data” option for “Hidden and Empty Cell Option”.
    • Hide Data of Hidden Rows or Columns – You can hide the data of the hidden cells by simply unchecking the check-box.

Excel Sparkline Highlights

Highlight is a feature that allows you to show the important points in sparklines by using high points, low points, first and last points, etc detailed in below section.

• High Point – By selecting this you can highlight the highest points of data in the sparklines. It works in all types of the sparklines (line, columns and win/loss) as shown in above image.
• Low Point – By selecting this you highlight the lowest points of data in the sparklines. It also works for all types of the sparklines (line, columns and win/loss) as shown in above image.
• Negative Point – By selecting this you can highlight the negative points of data (data having value less than zero). It works in all types of the sparklines (line, columns and win/loss) as shown in above image.
• First Point – It highlights the starting point of the data in the sparklines as shown in above image.
• Last Point – It highlights the end point of the data in the sparklines as shown in above image.
• Markers – It highlights all the data points (highest, lowest, first, last and everything) in all types of sparklines as shown in above image.

How to Style your Sparklines

Using the “Style” section available under “Design” tab you can change the color of lines and columns of sparklines using a pre-defined set of designs.

• Sparkline Color – Using this option you can change the color of the sparkline and also change the weight of the line.
• Marker Color – Using this you can change the color of markers (high, low, negative, first and last point).
• Axis – This section gives you an option to change scaling and visibility details of horizontal and vertical axis of sparkline.

How to Group \ Ungroup Sparklines

If you are using multiple sparklines in a single sheet then you could group them by selecting “Group” option. By grouping you could easily apply all the changes in format, style, color, weight, etc to all the sparklines available in the group. If you want each sparkline to be unique then you can ungroup the sparklines by selecting “Ungroup” option. Below listed steps could be used to Group the Sparklines:

• Select the set of sparklines which you want to “Group”. It is not necessary that the sparklines should be adjacent.
• After selecting the sparklines, go to the Design Tab and select “Group” Option.
• Now the grouping is done and you could easily apply same set of styles to the grouped sparklines.

Now, same set of style is applied to the Group Sparklines, as shown in below image. Likewise, you can select the “Ungroup” option to ungroup the sparklines.

How to Clear a Sparklines

If you want to erase the sparkline and remove its data, location range everything then you could select the “Clear” option. To achieve this just follow below steps:

• Select the Cell(s) whose sparklines have to be removed.
• Go to “Design” tab and select clear option.

After this the selected sparklines will be removed, as if they didn't exist. This was all about excel sparklines. Hope this article helps you out to make your excels much more lively using sparklines.