Analyze trends in data using spark lines in Excel

Search
Generic filters
Filter by Categories
Select all
Budget
Business Statements
Calendars
Gantt Chart
Health Chart/ Personal Log
Inventory
Invoice/ Bill
Payroll
Project Timelines
Purchase Order/Requisition
Quotation
Sales/ Marketing
SWOT Analysis
Timesheet/ Attendance
Filter by File Types






Analyze Trends in Data using Spark Lines
Share on facebook
Share on twitter
Share on linkedin
Share on whatsapp
Share on mix
Share on tumblr
Share on telegram

Table of Contents

What are Spark Lines ?

Spark lines are tiny charts inside single worksheet cells that can be used to visually represent and show a trend in your data. Spark lines can draw attention to important items such as seasonal changes or economic cycles and highlight the maximum and minimum values in a different color. Showing trends in your worksheet data can be useful, especially when you’re sharing your data with other people.

Worksheet with sparklines

How to Analyze Data using Spark Lines ?
  1. Select a blank cell near the data you want to show in a spark line.
  2. On the Insert tab, in the Spark lines group, click LineColumn, or Win/Loss.Sparkline commands on the Insert tab
  3. In the Data Range box, enter the range of cells that has the data you want to show in the spark line.For example, if your data is in cells A, B, C, and D of row 2, enter A2:D2.

    Create Sparklines dialog box



    If you’d rather select the range of cells on the worksheet, click Button image to temporarily collapse the dialog box, select the cells on the worksheet, and then click Button image to show the dialog box in full.

  4. Click OK.The Sparkline Tools appear on the ribbon. Use the commands on the Design tab to customize your spark lines.

    Sparkline Tools on the ribbon

Tips

  • Because a spark line is embedded in a cell, any text you enter in the cell uses the spark line as its background, as shown in the following example.A cell containing a sparkline and text
  • If you select one cell, you can always copy a spark line to other cells in a column or row later by dragging or using Fill Down (Ctrl + D).
Customize your spark lines

After you create spark lines, you can change their type, style, and format at any time.

  1. Select the spark lines you want to customize to show the Sparkline Tools on the ribbon.Sparkline Tools on the ribbon
  2. On the Design tab, pick the options you want. You can:
    • Show markers to highlight individual values in line spark lines.Show group on the Design tab of the Sparkline Tools
    • Change the style or format of spark lines.Styles on the Design tab of the Sparkline Tools
    • Show and change axis settings.Axis button on the Design tab of the Sparkline Tools

      If you click the Date Axis Type option in this drop-down, Excel opens the Sparkline Date Range dialog box. From here, you can select the range in your workbook that contains the date values you want for your Sparkline data.

      Select the range that contains the date values for the Sparkline data in the Sparkline Date Range dialog box.

      If you click the Custom Value options in this drop-down, Excel opens the Sparkline Vertical Axis Setting dialog box. From here, you can enter either the minimum or maximum value (depending upon which option you selected) for the vertical axis for your Sparkline data. By default, Excel determines how to display the Sparkline data so with these options you can control the minimum and maximum values.



      Enter a minimum value for the vertical axis in the Sparkline Vertical Axis Setting dialog box.

    • Change the way data is shown.Edit Data button in the Sparkline group

      If you click the Edit Single Sparkline’s Data option in this drop-down, Excel opens the Edit Sparkline Data dialog box. From here, you can select the range in your workbook that contains the data you want for your Sparkline data. Use this option if you want to change only one Sparkline.

      Enter a source data range in the Edit Sparkline Data dialog box.

      If you click the Hidden & Empty Cells option in this drop-down, Excel opens the Hidden and Empty Cell Settings dialog box. Use this option to change how Excel treats hidden and null values for the Sparkline data.

      You can choose to show empty cells as GapsZero, or Connect data points with line.

      Select the Show data in hidden rows and columns option to have Excel include data in hidden rows and columns in your Sparkline data. Clear this option to have Excel ignore data in hidden rows and columns.

      Decide how you want Excel to handle hidden or empty cells in sparklines on the Hidden and Empty Cell Settings dialog box.