What You Should Know About Excel Sparklines

What are Excel sparklines and how can you use them to highlight trends in your data? Basically, sparklines are miniature (lightweight) versions of line or bar charts that are easy to create, adapt, and scale. In this article, we’ll find out how to create sparklines and see some typical uses of these useful gadgets!

Excel sparklines provide a quick and easy way to visualize data and trends!

Excel sparklines provide a quick and easy way to visualize data and trends!

Excel Sparklines: Mini Charts

Sparklines are all about convenient data visualization. You can think of them as mini charts. If you just need a quick graphic to illustrate your data, but you don’t want to create a full-blown Excel chart, then Excel sparklines are perfect!

  • Sparklines are not chart objects; instead they exist as background images within a cell.
  • They will instantly update when the data changes.
  • You can drag a sparkline cell to automatically create new sparklines in additional rows.
  • Excel Sparklines are customizable.
  • You can type text, numbers, or formulas directly into the cell containing a sparkline. You can even use conditional formatting on the cell.

Example: Stock Markets

Suppose you want to keep track of the ups and downs of your favorite stocks from week to week.

First you might create a table of stock prices in Excel.

Then click on the cell in which you want to place a sparkline chart. I decided to place the chart into cell H2 because that cell is immediately to the right of the data that I want to visualize.

Next, go to INSERT, and choose the type of chart you want to include. Here, we have chosen “Line.” (You can modify the colors and other characteristics later.)

This brings up a dialog box. Just fill in the range of data (B2:G2 in our example).

creating sparlines

Weekly opening index values for various markets. Data obtained from finance.yahoo.com.

Click OK, and voilà! You should now see you very first sparkline chart!

Customize and Duplicate

Now comes the really fun part! You can add markers at each data point and choose different colors for maximum and minimum points.

Changing the style of Sparkline charts

There are plenty of options to help make your data stand out!

Then, you may drag the cell (H2 in this example) down to instantly create similar charts for all of the data in you table. (The result should look like the screenshot at the beginning of this article.)

Additional Formatting Tricks

One very nice thing about Excel Sparklines is that they are part of the background of the cell in which they are drawn. In other words, you can still put text, numbers, formulas, and other items in the cell. I find that extra textual items clutter up the chart, but you may find this feature quite useful.

Sparklines and Conditional Formatting

On the other hand, you could add conditional formatting rules to the cells in order to catch the reader’s attention. (For a crash course on conditional formatting, check out: How to Use Excel Conditional Formatting.)

For example in our stock market example, we could add a conditional formatting rule to highlight markets that have gone up since last week. In my example, I wanted to shade the cell green if there was a gain from last week to this week, and red if there was a loss. This will take two separate rules.

To do this, click the cell containing the first Sparkline chart (H2). Click Conditional Formatting from the Home tab, and select “New Rule…”

In the next dialog box, select “Use a formula to determine which cells to format.” Type the following into the formula box:

= (G2 – F2) < 0

Then pick a color like red to represent “loss” (because the difference would be negative).

After you’ve completed this series of steps, do it all over again, but now for gains. This time, your formula should have the inequality going the other direction:

= (G2 – F2) > 0

Here, you should select a color like green for “gain.”

Finally, select the cell H2 and drag it down to ensure that your formatting rules will apply to H3 and H4 as well!

Here are screenshots of the completed conditional formatting rules and the result on our sparkline cells.

Using conditional formatting with Sparklines

Conditional formatting rules can be used in conjunction with Sparklines.

Keep track of your gains and losses with Excel Sparklines and conditional formatting!

Keep track of your gains and losses with Excel Sparklines and conditional formatting!

Adjusting the Dimensions

Because Excel Sparklines are background images, they will stretch or compress to fit the cell.

In our stock market example, it’s hard to tell where the ups and downs really are. So let’s increase the height of rows 2, 3, and 4 to better illustrate the differences in height on each chart. I will also make column H more narrow, which further helps to accentuate height differences. Notice how the Sparklines stretch vertically and compress horizontally to fit the cell’s new dimensions.

Sparklines scale to fit their cells

Summary

Excel Sparklines provide an easy way to add lightweight graphics to your data.

  • A Sparkline chart is a good choice for summarizing data within a row of a table. They typically will be placed in the same row, to the right of the data.
  • A Sparkline chart not a full-blown Excel chart. Instead, it exists as a background image within a cell. The chart will scale with the cell.
  • There are many style features, including adding markers for maximum and minimum points and colors for various elements.
  • You can place text, numerical data, formulas, and other items in the same cell with a Sparkline chart.
  • Cells containing Sparkline charts can be enhanced using conditional formatting.
  • Once you have defined a chart in a given cell, then you can drag the cell down to automatically create charts for each row of your data.
Comments are closed.


Magoosh blog comment policy: To create the best experience for our readers, we will only approve comments that are relevant to the article, general enough to be helpful to other students, concise, and well-written! 😄 Due to the high volume of comments across all of our blogs, we cannot promise that all comments will receive responses from our instructors.

We highly encourage students to help each other out and respond to other students' comments if you can!

If you are a Premium Magoosh student and would like more personalized service from our instructors, you can use the Help tab on the Magoosh dashboard. Thanks!