#Excel Sparklines: Display Last N Values by David Hager

Sparklines are a relatively recent addition to the myriad of tools in Excel (version 2010). They are very useful in creating a number of different ways to visualize data. In this example, creating sparklines from a vertical range of data in a table reveals some interesting options. If you make a data table and create a sparkline from it, the process goes smoothly, generating a sparkline that will auto-expand its chart data along with added data to the table as shown in the figure.

sline1a

But, what if you want to display only the last N values in the table. In that case, you need to use OFFSET formulas as ranges, as first discussed in

https://dhexcel1.wordpress.com/2014/05/04/excel-did-you-know-tip5/

The formula needed to do this (with input from cell F5) is defined as:

LastN1=OFFSET(Sheet1!$A$4,COUNT(TrendData)-Sheet1!$F$5+1,,Sheet1!$F$5)

The method I used to apply this formula range was to create a sparkline from a random selection in the table body and then go back and Edit the sparkline source data as shown below (initially, I had to enter as =LastN1).

sline2

A similar formula was made accepting input from cell F6.

I hope that this technique adds to your data visualization portfolio.

You can download the example file here.

sline1

Advertisements

One thought on “#Excel Sparklines: Display Last N Values by David Hager

  1. Pingback: #Excel Super Links #76 – shared by David Hager | Excel For You

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s