Which functionality can be used to insert small charts in a cell to see trends at a glance?

You can add reference lines to a chart to mark the average, median, minimum, and maximum values in the chart or another value that you specify. Reference lines make the chart easier to comprehend at a glance and can help you compare the values in the chart to a benchmark value.

All chart types can have reference lines except stacked charts, 2-axis charts, 3D charts, pie charts, donut charts, and radar charts. A chart can have up to five reference lines.

1. Click the chart.

2. In the Format

   
    sidebar, click the Axis tab, then click the Value button near the top of the sidebar.

3. Click the disclosure arrow next to Reference Lines, then choose any of the following types of reference lines from the pop-up menu:

   • Average: A line that runs through the mean value of the data

   • Median: A line that runs through the middle value of the data

   • Minimum: A line that runs through the lowest value of the data

   • Maximum: A line that runs through the highest value of the data

   • Custom: A line that runs through the value you specify

   

4. To show what the reference line represents, select the Show Name and Show Value checkboxes.

5. To change the settings for a reference line, click the line, then use the controls in the Reference Line tab in the sidebar on the right.

To remove a reference line, click the line to select it, then press Delete on your keyboard.

Note: When you drag the slider on an interactive chart to view different data sets, its reference lines move.

Lesson 23: Sparklines

/en/excel2013/charts/content/

Introduction

Sometimes you may want to analyze and view trends in your data without creating an entire chart. Sparklines are miniature charts that fit into a single cell. Because they're so compact, it's easy to include several sparklines in a workbook.

Optional: Download our practice workbook.

Types of sparklines

There are three different types of sparklines: Line, Column, and Win/Loss. Line and Column work the same as line and column charts. Win/Loss is similar to Column, except it only shows whether each value is positive or negative instead of how high or low the values are. All three types can display markers at important points, such as the highest and lowest points, to make them easier to read.

Why use sparklines?

Sparklines have certain advantages over charts. For example, let's say you have 1,000 rows of data. A traditional chart would have 1,000 data series to represent all of the rows, making relevant data difficult to find. But if you placed a sparkline on each row, it will be right next to its source data, making it easy to see relationships and trends for multiple data series at the same time.

In the image below, the chart is extremely cluttered and difficult to follow, but the sparklines allow you to clearly follow each salesperson's data.

Sparklines are ideal for situations when you need a clear overview of the data at a glance and when you don't need all of the features of a full chart. On the other hand, charts are ideal for situations when you want to represent the data in greater detail, and they are often better for comparing different data series.

To create sparklines:

Generally, you will have one sparkline for each row, but you can create as many as you want in any location. Just like formulas, it's usually easiest to create a single sparkline and then use the fill handle to create sparklines for the adjacent rows. In our example, we'll create sparklines to help visualize trends in sales over time for each salesperson.

1. Select the cells that will serve as the source data for the first sparkline. In our example, we'll select the cell range B2:G2.
   
   Selecting cells B2:G2

2. Select the Insert tab, then choose the desired Sparkline from the Sparklines group. In our example, we'll choose Line.
   
   Clicking the Line command

3. The Create Sparklines dialog box will appear. Use the mouse to select the cell where the sparkline will appear, then click OK. In our example, we'll select cell H2, and the cell reference will appear in the Location Range: field.
   
   Selecting a location for the sparkline and clicking OK

4. The sparkline will appear in the specified cell.
   
   A sparkline

5. Click, hold, and drag the fill handle to create sparklines in adjacent cells.
   
   Dragging the fill handle to create sparklines in adjacent cells

6. Sparklines will be created for the selected cells. In our example, the sparklines show clear trends in sales over time for each salesperson in our worksheet.
   
   Sparklines filled to multiple rows

Modifying sparklines

It's easy to change the way sparklines appear in your worksheet. Excel allows you to customize a sparkline's markers, style, type, and more.

To display markers:

Certain points on a sparkline can be emphasized with markers, or dots, making the sparkline more readable. For example, in a line with a lot of ups and downs, it might be difficult to tell which values are the highest and lowest points. Showing the high point and low point will make them easier to identify.

1. Select the sparkline(s) you want to change. If they are grouped in adjacent cells, you'll only need to click on one sparkline to select them all.
   
   Selecting a group of sparklines

2. From the Design tab, select the desired option(s) from the Show group. In our example, we'll select High Point and Low Point.
   
   Showing the High and Low points on the sparklines

3. The sparkline(s) will update to show the selected markers.
   
   The sparklines with high and low markers

To change the sparkline style:

1. Select the sparkline(s) you want to change.
2. From the Design tab, click the More drop-down arrow.
   
   Clicking the More drop-down arrow

3. Choose the desired style from the drop-down menu.
   
   Choosing a sparkline style

4. The sparkline(s) will update to show the selected style.
   
   The new sparkline style

To change the sparkline type:

1. Select the sparkline(s) you want to change.
2. From the Design tab, select the desired Sparkline type. In our example, we'll select Column.
   
   Choosing a new sparkline type

3. The sparkline(s) will update to reflect the new type.
   
   The new sparkline type

Some sparkline types will be better suited for certain types of data. For example, Win/Loss is best suited for data where there could be positive and negative values (such as net earnings).

Changing the display range

By default, each sparkline is scaled to fit the maximum and minimum values of its own data source: The maximum value will go to the top of the cell, while the minimum will go to the bottom. However, this doesn't show how high or low the values are when compared to the other sparklines. Excel allows you to modify the sparkline display range, which makes it easier to compare sparklines.

To change the display range:

1. Select the sparklines you want to change.
   
   Selecting a group of sparklines

2. From the Design tab, click the Axis command. A drop-down menu will appear.
3. Below Vertical Axis Minimum Value Options and Vertical Axis Maximum Value Options, select Same for All Sparklines.
   
   Modifying the sparklines' display range

4. The sparklines will update to reflect the new display range. In our example, we can now use the sparklines to compare trends for each salesperson.
   
   The updated display range

Challenge!

1. Open an existing Excel workbook. If you want, you can use our practice workbook.
2. Create a sparkline on the first row of data. If you are using the example, create a sparkline for the first salesperson on row 3.
3. Use the fill handle to create sparklines for the remaining rows.
4. Create markers for the High Point and Low Point.
5. Change the sparkline type.
6. Change the display range to make the sparklines easier to compare.

/en/excel2013/track-changes-and-comments/content/

How can tiny charts be added in a cell?

How do I add a small trendline in Excel?

How do you show trends in an Excel cell?

How do I insert a chart in Excel cell?