Sparklines in Excel: What is, How to Use, Types & Examples

GURU 99

Sparklines in Excel: What is, How to Use, Types & Examples

What is Sparklines in Excel?

Sparkline in Excel is a small graph which is used to represent a series of data. Apart from a well-fledged chart, it fits into a single cell. Three different data visualizations available in Excel Sparkline are:

Line
Column
Win/Loss

It is an instant chart that prepares for a range of values. Sparklines in Excel is used to showcase the data trend for a while.

In this Excel Sparklines tutorial, you will learn

Why use Sparklines?

Sparkline graph helps you to avoid the chore of creating a big chart which can be confusing during analysis. It is a common visualization technique used in dashboards when you want to picture a portion of data from a large dataset.

How to write a TEST CASE Software Testing Tutorial

Sparklines in Excel is not an object like Excel graphs; it resides in a cell as ordinary data. When you increase the size of the Excel, Sparkline automatically fit into the cells according to its size.

Types of Sparklines

From the Insert menu, select the type of Sparkline you want. It offers three types of Sparklines in Excel.

Line Sparkline: Line Sparkline in Excel will be in the form of lines, and high values will indicate fluctuations in height difference.
Column Sparkline: Column Sparkline in Excel will be in the form of column chart or bar chart. Each bar shows each value.
Win/Loss Sparkline: It is mainly used to show negative values like ups and downs on the floated costs.

Depending on the type, it gives different visualization to the selected data. Where the line is a tiny chart similar to the line chart, the column is a miniature of bar chart and win/loss resembles waterfall charts.

How to insert Sparklines in Excel?

You need to select a particular column data, to insert Sparklines in Excel.

Consider the following demo data: Status of some pending stock for different years is below. To make a quick analysis, let's make a Sparkline for each year.

YearJanFebMarchAprilMayJune2011201084510105482012481000787420131210210001002014120340560

Download Excel used on this tutorial

Step 1) Select the next column to 'June' and insert Sparkline from insert menu. Select anyone from the three types of Sparkline.

Step 2) A selection window will appear to select the range of cells for which the Sparkline should insert.

By clicking the arrow near data range box, a range of cells can be choosen.

Step 3) Select the first row of the data for the year 2011 in 'Data Range' text box. The range will shown as B2: G2.

Step 4) Another range selection indicates where you want to insert the Sparkline. Give the address of the cell you need the Sparkline.

Step 5) Once you set the 'Data Range' and 'Location Range' press 'OK' button.

Step 6) Now the Sparkline is created for the selected data, and it gets inserted in the selected cell H3.

Create a Report with a Table

You have a sales report for four years: 2015, 2016, 2017 respectively. Details included in this table are country, product, and gross sales.

Let's find the trend of sales for this product for different years.

Step 1) Create a column analysis next to gross sales for 2018. And in the next step, you are going to insert the Sparkline.

S#CountryProductGross Sales 2015Gross Sales 2016Gross Sales 2017Gross Sales 20181CanadaCarretera$ 32,370.00

$ 30,216.00

$ 352,625.00

$ 34,440.00

2GermanyCarretera$ 26,420.00

$ 352,100.00

$ 43,125.00

$ 7,210.00

3FranceCarretera$ 32,670.00

$ 4,404.00

$ 600,300.00

$ 4,473.00

4SingaporeCarretera$ 13,320.00

$ 6,181.00

$ 34,056.00

$ 9,282.00

5United States of AmericaCarretera$ 37,050.00

$ 8,235.00

$ 32,670.00

$ 22,296.00

6MexicoCarretera$ 529,550.00

$ 236,400.00

$ 13,320.00

$ 423,500.00

7ChinaCarretera$ 13,815.00

$ 37,080.00

$ 534,450.00

$ 17,703.00

8SpainCarretera$ 30,216.00

$ 8,001.00

$ 645,300.00

$ 17,340.00

9JapanCarretera$ 37,980.00

$ 603,750.00

$ 36,340.00

$ 41,250.00

10Korea (Republic of)Carretera$ 18,540.00

$ 10,944.00

$ 962,500.00

$ 32,052.00

11ItalyCarretera$ 37,050.00

$ 32,280.00

$ 23,436.00

$ 9,192.00

12Costa RicaCarretera$ 333,187.50

$ 36,340.00

$ 527,437.50

$ 148,200.00

13TaiwanCarretera$ 287,400.00

$ 529,550.00

$ 37,980.00

$ 488,950.00

14ArgentinaCarretera$ 15,022.00

$ 10,451.00

$ 11,802.00

$ 754,250.00

15DenmarkCarretera$ 43,125.00

$ 225,500.00

$ 25,692.00

$ 33,210.00

16BelgiumCarretera$ 9,225.00

$ 25,932.00

$ 8,001.00

$ 690,300.00

Step 2) Select the cell in which you want to insert the Sparkline. Go to Insert menu from the menu bar. Select any one of the Sparkline from the list of Sparkline.

Step 3) Select any one of the sparkline types that you want to insert. It will ask for the range of cells. Select the line type from the available sparkline type.

Data Range indicates, which data the Sparkline need to insert. Location range is the cell address where you want to add the Sparkline.

Step 4) Here, the data range is from the cell data contains 'Gross sales 2015 to 2018' and location range is from H3. Press the 'OK' button after this.

Step 5) The Sparkline will be inserted into the H3 cell. You can apply the Sparkline to entire data by dragging the same to downwards.

Now the Sparkline is created.

How to format a Sparklines?

To format Sparkline, first Click on the created Sparkline. A new tab named design will appear on the menu bar which includes the Sparkline tools and will help you to change its color and style. See the available options one by one.

The different properties of Sparkline can customize according to your need. Style, color, thickness, type, the axis is some among them.

How to change the style of the Sparklines?

Step 1) Select a style from the 'Style' option within the design menu. The styles included in a dropdown.

Step 2) Click on the down arrow near to the style option to select your favorite style from an extensive design catalog.

How to change the color of the Sparklines?

The color and thickness of the Sparkline can be changed using the Sparkline Color option. It offers a variety of colors.

Step 1) Select the Sparkline and select the Sparkline Color option from the design menu.

Step 2) Click on the color you want to change. The Sparkline will update with the selected color.

Change the width of lines.

The width of the lines can be adjusted with the option available in the 'Sparkline Color' window.

Step 1) Select the Sparkline then go to design menu in the menu bar.

Step 2) Click on the Sparkline Color' option.

Step 3) Select the Weight option to make changes in thickness to the inserted Sparkline.

Step 4) Move to Weight option. This will give a list of predefined thickness. Customization in Weight is also available.

Highlighting the data points

You can highlight the highest, lowest points, and the entire data points on a Sparkline. By this you to get a better view of the data trend.

Step 1) Select the created Sparkline in any cell then move to the design menu. You can see different checkboxes to highlight the data points.

Step 2) Give checkmark according to the data points you want to highlight. And the options available are:

High/Low Point: Highlights the high spot the high and low points on the Sparkline.
First/Last Point: This will help to highlight the first and last data points on the Sparkline.
Negative Points: Use this to highlight negative values.
Markers: This option applies only to line Sparkline. It will highlight all data points with a marker. Different color and style are available with more marker colors and lines.

Deleting the Sparklines

You can't remove the Sparkline by hitting the 'delete' key on a keyboard. To delete the Sparkline, you should go to the 'Clear' option.

Step 1) Select the cell, which included the Sparkline.

Step 2) Go to Sparkline tools design menu.

Step 3) Click on the clear option this will delete the selected Sparkline from the cell.

Why use Sparklines?

Here are important reasons for using Sparkline:

Visualization of data like temperature or stock market price.
Transforming data into a compact form.
Data reports generating for a short time.
Analyze data trends for a particular time.
Easy to understand the fluctuations in the data.
A better understanding of high and low data points.
Negative values can float effectively by sparkline.
Sparkline automatically adjusts its size once the cell width is changed.

Summary

Sparkline is a small chart which does not recommend an axes or coordinates
Sparkline can apply on a single column or row of data series.
Different formatting properties are available for the Sparkline.
Sparkline is a micrograph which fit into a single cell.
A single delete key press will not remove a created Sparkline.
The different data points can highlight in a Sparkline.

ORIGINAL Article