How to Insert, Format and View Trends in Excel Sparklines

Sparklines is a cool feature that lets you analyze cell data in graphical terms. This tutorial shows you how to insert sparklines in Excel 2010, then detail steps on how to edit the Sparkline's data source and if required delete a sparkline. The Create Sparklines dialog box contains the Data Range text box which allows you to enter the Sparkline data range and the Location Range text box that allows you to enter the cell location where you want to insert the Sparkline.

How to Insert Sparklines in Excel 2010

1. On the Insert tab, in the Sparklines group, click a Sparkline type.

2. The Create Sparklines dialog box is displayed. In the Data Range text box, click the Collapse Dialog toggle button and navigate to the sheet that contains the data range.

3. To insert a Sparkline in a single cell, select one single data range. To insert more than one Sparkline, select the entire data range or as required.

4. Click the Expand Dialog toggle button and place the insertion point in the Location Range text box.

5. Click the Collapse Dialog toggle button, navigate to the sheet where you want to insert the Sparkline(s) and click OK.

6. If you have inserted a Sparkline in a single cell, you can drag the fill arrow to fill the Sparklines in the remaining cells.

How to Insert Sparklines in Excel 2010

How to Edit Sparklines in Excel 2010

If you want to edit data that sources the Sparkline, here’s what you need to do.

1. Select the Sparkline.

2. In the Sparkline Tools Design contextual tab, in the Sparkline group, click Edit Data.

3. Modify the information in the Data Range and Location Range text boxes.

How to Delete a Sparkline

You can delete a Sparkline by performing the below mentioned steps.

1. Select the Sparkline.

2. On the Sparkline Tools Design contextual tab, in the Group group, from the Clear drop down, select Clear Selected Sparklines to clear a selected Sparkline or select Clear Selected Sparkline Groups to clear a selected Sparkline group.

Formatting Sparklines

You can improve your data analysis in Excel by adding a few touches to the Sparklines that you created. Understanding the various options in the Sparklines Tool tool tab will enable you to get started with this process. Let’s first look at some of the groups in the Sparklines Tool tool tab and examine what actions we can perform using the commands in this tool tab. After that, we'll see how to format Sparklines.

Sparkline Group

Contains the Edit Data drop-down that allows you to edit the data range and location for a single Sparkline or a group of Sparklines. You can also determine how hidden cells or empty cells are displayed. You can also switch data from the horizontal axis to the vertical axis or vice versa.

Type Group

typegroup.png

Contains the Line, Column, WinLoss Sparklines. You can insert or modify a Sparkline in your worksheet by clicking the desired type group.

Show Group

showgroup

Contains options to highlight key points in the Sparkline such as high and low points, first and last points, negative points and markers.

Style Group

stylegroup

Displays a gallery of Sparkline styles. Also allows you to add a Sparkline color and a color for the key points and markers.

Group Group

group group sparkline

The Group Group option allows you to set the horizontal and vertical axis options. You can also group or ungroup Sparklines and clear Sparklines.

Using these commands, you can change a Sparkline type, add color to a Sparkline to differentiate one Sparkline from another, highlight data points in a Sparkline to display the key points in the data, add color to the highlighted markers and much more.
Here are some of the ways in which you can bring your Sparklines to life.

Group Sparklines

To view Sparklines in a range of cells, simply click and drag the fill arrow that is displayed at the bottom of the cell. This will automatically group the Sparklines and any formatting changes made to a Sparkline in one cell will automatically be updated to all Sparklines in the group. If you want to format individual Sparklines, you can ungroup them and then apply the formatting for each individual Sparkline.

How to Group a Sparkline

1. Select a Sparkline.

2. On the Sparkline Tools Design contextual tab, in the Group group, click Group.

How to Ungroup a Sparkline

1. Select a Sparkline.

2. On the Sparkline Tools Design contextual tab, in the Group group, click Ungroup.

How to Change the Sparkline Type

1. Select a Sparkline.

2. On the Sparkline Tools Design contextual tab, in the Type group, select a Sparkline type.

How to Add a Color to a Sparkline

1. Select a Sparkline.

2. On the Sparkline Tools Design contextual tab, in the Style group, from the Sparkline Color drop down list, select a color.

sparkline color options

How to Highlight Data Points in a Sparkline

1. Select a Sparkline.

2. On the Sparkline Tools Design contextual tab, in the Show group, select a Sparkline data point type.

How to Add a Color to the Highlighted Markers

1. Select a Sparkline.

2. On the Sparkline Tools Design contextual tab, in the Style group, from the Marker Color drop down list, select a marker type and from the sub-menu select a color option.

marker color

How to View Trends in Data Using Excel Sparklines

Looking at a whole bunch of numbers in your Excel worksheet may not make sense to you or your clients. One of the significant features of Excel is its ability to let users analyze and interpret data. Microsoft Excel Sparklines is a fantastic tool that helps to do just that; spot trends in data. Before we discuss how to view trends in data using Sparklines, we’ll take a look at what Sparklines are and the types of Sparklines that can help you read numbers in a manner that you can better understand.

View Trends in Data Using Sparklines

Sparklines are small chart-type graphics inserted within a cell, displaying the trend in a range of cells. Excel 2010 includes 3 types of Sparklines, Line, Column, Win/Loss. Unlike a chart, a Sparkline can display only one data series. You can insert a Sparkline by selecting the Insert tab and choosing a Sparkline type from the Sparklines group.

Types of Sparklines

Line Sparkline : This Sparkline displays the trends in data over a period of time.

Line sparkline

Column Sparkline: This Sparkline type is ideal for displaying trends in data for categories.

column.png

WinLoss Sparkline : This Sparkline type displays the trends as a data series in blocks. A high block represents a win and a low block represents a loss.

win-loss


One of the key factors that you must remember when using Sparklines is to maintain the right colors. If you wish to focus on the highs and lows of your chart, let your colors complement that factor. Brighter colors for the highs and lows against a much muted background will highlight what’s important in your trends.

Source: Microsoft Support

Related Post

0
Shares
Adeline Gear
 

Adeline is a tech enthusiast who loves exploring the latest tools and applications in the marketplace. She also loves her Android and spends time reviewing apps and playing games when she has the time.

Click Here to Leave a Comment Below 0 comments