Beginning with Excel 2010, Excel allows users to create sparklines, which summarize a row or column of data with a chart in a single cell. We previously discussed line and column sparklines. In this article, we discuss win loss sparklines. A win loss sparkline summarizes a row or column of numbers by creating an “up bar” for any cell containing a positive number, a “down bar” for any cell containing a negative number, and a blank for each cell containing a 0. It is important to note that the magnitude of a cell entry is not reflected in a win loss sparkline. Thus, if cell A1 contains +0.01 and cell A2 contains +10,000, the sparkline creates an up bar of equal size for both cells. We now show you how to use win loss sparklines to create insightful visual summaries for three interesting situations. Our work is in the workbook Beckerjune2020.xlsx.
- NFL team records during the 2009 season.
- Performance of monthly sales vs. monthly sales targets.
- 2019 daily returns on Facebook stock.
The NFL 2009 season
During the 2009 season the Colts opened 14-0 and the Saints opened 13-0, and the Saints went on to win the Super Bowl. The worksheet NFL contains for each team’s 16 games a 1 if the team won and a -1 if the team lost. As shown in figure 1, we created win loss sparklines in B4:B35 to summarize each team’s performance.
Figure 1: NFL sparklines
To create these sparklines simply select the range B4:B35 where you want the sparklines and from the insert tab select win/loss and select the data range D4:S35 that contains the +1 and -1’s for each team.
Figure 2: Dialog box to create NFL sparklines
Figure 1 makes it clear that the Lions were awful, and the Colts and Saints set the league on fire.
Monthly sales vs. monthly targets
As shown in figure 3, the worksheet “Targets” gives monthly sales and sales targets.
Figure 3: Sales vs. target sparkline
In cell G16, we created a win loss sparkline to visualize our company’s performance against monthly targets. You can see that in January-March sales fell short of our target while in April-August we met our target. For September, the blank indicates that Sales = Target. To create this sparkline we copied from G4:G15 the formula
= IF(E4>F4,1,IF(E4<F4,-1,)),
This formula places a 1 in column G for months in which the target was met, a -1 for months in which the target is not meant and a 0 in months where Sales = Target. After selecting cell G16 we again chose win/loss from the insert tab. After inserting the data range of G4:G15 we have created the desired sparkline.
Summarizing Facebook daily returns
The worksheet “Facebook” (see figure 4) contains Facebook daily stock prices during 2019. In G2 we created a win loss sparkline which shows a blue up bar on days that Facebook’s stock price went up, a red down bar on days Facebook’s stock price went down, and a blank for days in which the stock price was unchanged. Copying from G6 to G7:G256 the formula
= (F6-F5)/F5
computes Facebook’s daily returns.
After selecting cell G2 and choosing win/loss from the insert tab we chose the data range G6:G256 and we have obtained the win loss sparkline shown in figure 4. Note that after the first down bar indicates that Facebook stock went down on January 3, the price went up the next 4 days. This was indicated by 4 up bars, that appear as a “thicker” up bar.
Figure 4: Facebook daily returns
I hope you have enjoyed learning about sparklines. Stay tuned as we will share a resource relating to Microsoft’s 365’s amazing dynamic array functions.