Tag Archives: sparkline

#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

#Excel: Origin of Sparklines – LineChart VBA User-Defined Function by David Hager

A number of Excel features added to the product by Microsoft over the years were originally made by Excel developers. The addition of sparklines added a whole new way to visualize data. Before they were an Excel feature, sparklines were popularized by Edward Tufte. See:

https://www.edwardtufte.com/bboard/q-and-a-fetch-msg?msg_id=000AI

The inspiration for the implementation of sparklines in Excel actually came from Excel developer Rob van Gelder. In this article

http://dailydoseofexcel.com/archives/2006/02/05/in-cell-charting/

he shares the VBA code for the LineChart user-defined function, which places a chart in the cell where the function is called from. You can view the use of this UDF and the VBA code in this example file.

 UDFLineChart

If you are not familiar with Excel’s built-in sparkline feature, you can read the following Microsoft article.

https://support.office.com/en-us/article/Use-sparklines-to-show-data-trends-1474E169-008C-4783-926B-5C60E620F5CA