• WORK
• TOOLS
• TRAINING
• VIEWS
• CONTACT

In the words of Edward Tufte—the inventor of Sparklines—Sparklines are "small, high-resolution graphics usually embedded in a full context of words, numbers, images. Sparklines are datawords: data-intense, design-simple, word-sized graphics."

Below—taken from ET's Beautiful Evidence—is an application of sparklines in finance. The sparkline shows the exchange rate Euro/\$ for 12 months. Red represents the exchange rate at the start and end of the period. Blue represents the low and high during that same period. Red and blue dots indicate the same data onto the sparkline.

Lets build it in Excel. The first thing to do is to get historical financial data into Excel using the STOCKHISTORY function.

1. Enter "Start date" in cell K8 and the date 12/1/2015 in cell L8.
2. Enter "End date" in cell K9 and the date 4/30/2021 in cell L9.
3. Next, we need to define the ticker symbols for the currencies, for that enter EUR, USD, JPY and GBP in cells K11, L11, M11 and N11 respectively. The worksheet should look like this:

# TUFTE IN EXCEL - SPARKLINES

4. Enter the following formula in cell K13:
= STOCKHISTORY(K11 & ":" & L11, L8, L9)

The worksheet should look like this:

5. Enter the following formula in cell M13:
= STOCKHISTORY(\$K\$11 & ":" & M11,\$L\$8,\$L\$9,,,1)
6. Next, select cell M13 and press Ctrl + C to copy the cell into the clipboard. Next, select cell N13 and press Ctrl + V to paste the content of the clipboard into the sheet. The worksheet should look like this:

7. Next, lets prepare the setup for the sparklines. to do that enter the label "Euro foreign exchange" in cells B4, B5 and B6.
8. Then, enter the currency symbols \$, ¥ and ₤ in cells C4, C5 and C6 respectively.
9. And then, enter the labels in cells D2:H2 as follows:

10. Next we need to define the exchange rate at the start and end of the period, the low and the high. For that add the following formulas in the respective cells:
D4 --> = L14
F4 --> = L1425
G4 --> = MIN(L14:L1425)
H4 --> = MAX(L14:L1425)
The worksheet should look like this:

11. Select cell C4, and then, click on the Line icon in the Sparklines group under the Insert tab.
12. In the Create Sparklines dialog box, in the Data Range input line, select the range L14:L1425 as follows:

13. Click the Ok button to close the Create Sparklines dialog box and the worksheet should look like this:

14. Next, on the worksheet, select cell E4. Then, click on the Sparkline Color icon in the Style group under the Sparkline tab. On the dropdown menu click Black from Theme Colors and then click 1/2 pt under Weight.
15. Then, while cell E4 is still selected, check the High Point, Low Point, First Point and Last Point check boxes in the Show group under the Sparkline tab.
16. Next, click on the Marker Color icon in the Style group under the Sparkline tab. On the dropdown menu, set the color of the High Point and Low Point to Blue. The worksheet should look like this:

17. Repeat the steps 10 - 16 above for the Yen and Pound exchange rates, and the worksheet should look like this:

Below is an extended version of the above chart showing the exchange rate of the Euro against three other major currencies—the US dollar, the Japanese Yen and the British Pound—this time for 65 months. The sparklines show 4,236 points in the span of about 25 characters.

Views > Excel charts > Tufte in excel sparklines

18. Finally, do some formatting such as font color, alignment and number formatting. Adjust row height and column width. Switch the gridlines off and the worksheet should look like this:

TUFTE IN EXCEL - SPARKLINES

Thank you for reading, and I hope you found this useful. If you have any questions, find me on Twitter and ask me anything.