CHAPTER 17
Dashboarding with sparklines in Excel 2019
In this chapter, you will:
Create sparklines
Scale sparklines
Format sparklines
Create a dashboard
A feature that’s been around since Excel 2010 is the ability to create tiny, word-size charts called sparklines. If you are creating dashboards, you will want to leverage these charts.
The concept of sparklines was first introduced by Professor Edward Tufte, who promoted sparklines as a way to show a maximum amount of information with a minimal amount of ink.
Microsoft supports three types of sparklines:
Line—A sparkline shows a single series on a line chart within a single cell. On a sparkline, you can add markers for the highest point, the lowest point, the first point, and the last point. Each of those points can have a different color. You can also choose to mark all the negative points or even all points.
Column—A spark column shows a single series on a column chart. You can choose to show a different color for the first bar, the last bar, the lowest bar, the highest bar, or all negative points.
Win/loss—This is a special type of column chart in which every positive point is plotted at 100% height and every negative point is plotted at –100% height. The theory is that positive columns represent wins and negative columns represent losses. With these charts, you always want to change the color of the negative columns. It is possible to highlight the highest/lowest point based on the underlying data.
Creating sparklines
Microsoft figures that you will usually be creating a group of sparklines. The main VBA object for sparklines is SparklineGroup. To create sparklines, you apply the SparklineGroups.Add method to the range where you want the sparklines to appear.
In the Add method, you specify a type for the sparkline and the location of the source data.
Say that you apply the Add method to the three-cell range B2:D2. Then the source must be a range that is either three columns wide or three rows tall.
The Type parameter can be xlSparkLine for a line, xlSparkColumn for a column, or xlSparkColumn100 for win/loss.
If the SourceData parameter is referring to ranges on the current worksheet, it can be as simple as "D3:F100". If it is pointing to another worksheet, use "Data!D3:F100" or "'My Data'!D3:F100". If you’ve defined a named range, you can specify the name of the range as the source data.
Figure 17-1 shows a table of S&P 500 closing prices for three years. Notice that the actual data for the sparklines is in three contiguous columns: D, E, and F.
FIGURE 17-1 Arrange the data for the sparklines in a contiguous range.
In this example, the data is on the Data worksheet, and the sparklines are created on the Dashboard worksheet. The WSD object variable is used for the Data worksheet. WSL is used for the Dashboard worksheet.
Because each column might have one or two extra points, the code to find the final row is slightly different than usual:
FinalRow = WSD.[A1].CurrentRegion.Rows.Count
The .CurrentRegion property starts from cell A1 and extends in all directions until it hits the edge of the worksheet or the edge of the data. In this case, the CurrentRegion reports that row 253 is the final row.
For this example, the sparklines are created in a row of three cells. Because each cell is showing 252 points, I am going with fairly large sparklines. The sparkline grows to the size of the cell, so this code makes each cell fairly wide and tall:
With WSL.Range("B1:D1")
.Value = array(2012,2013,2014)
.HorizontalAlignment = xlCenter
.Style = "Title"
.ColumnWidth = 39
.Offset(1, 0).RowHeight = 100
End With
The following code creates three default sparklines:
Dim SG as SparklineGroup
Set SG = WSL.Range("B2:D2").SparklineGroups.Add( _
Type:=xlSparkLine, _
SourceData:="Data!D2:F" & FinalRow)
As shown in Figure 17-2, these sparklines aren’t perfect (but the next section shows how to format them). There are a number of problems with the default sparklines. Think about the vertical axis of a chart. Sparklines always default to have the scale automatically selected. Because you never really get to see what the scale is, you cannot tell the range of the chart.
FIGURE 17-2 Three default sparklines are shown here.
Figure 17-3 shows the minimum and maximum for each year. From this data, you can guess that the sparkline for 2015 probably goes from about 1850 to 2150. The sparkline for 2016 probably goes from 1800 to 2300. The sparkline for 2017 probably goes from 2225 to 2690.
FIGURE 17-3 Each sparkline assigns the minimum and maximum scales to be just outside these limits.
Scaling sparklines
The default choice for the sparkline vertical axis is that each sparkline has a different minimum and maximum. There are two other choices available.
One choice is to group all the sparklines together but to continue to allow Excel to choose the minimum and maximum scales. You still won’t know exactly what values are chosen for the minimum and maximum.
To force the sparklines to have the same automatic scale, use this code:
' Allow automatic axis scale, but all three of them the same
With SG.Axes.Vertical
.MinScaleType = xlSparkScaleGroup
.MaxScaleType = xlSparkScaleGroup
End With
Note that .Axes belongs to the sparkline group, not to the individual sparklines themselves. In fact, almost all the good properties are applied at the SparklineGroup level. This has some interesting ramifications. If you want one sparkline to have an automatic scale and another sparkline to have a fixed scale, you have to create each of those sparklines separately, or at least ungroup them.
Figure 17-4 shows the sparklines when both the minimum and the maximum scales are set to act as a group. All three lines nearly meet now, which is a good sign. You can guess that the scale runs from about 1850 up to perhaps 2700. Again, though, there is no way to tell. The solution is to use a custom value for both the minimum and the maximum axes.
FIGURE 17-4 All three sparklines have the same minimum and maximum scales, but you don’t know what it is.
Another choice is to take absolute control and assign a minimum and a maximum for the vertical axis scale. The following code forces the sparklines to run from a minimum of 1829 up to a maximum that rounds up to 2191:
Set AF = Application.WorksheetFunction
AllMin = AF.Min(WSD.Range("D2:F" & FinalRow))
AllMax = AF.Max(WSD.Range("D2:F" & FinalRow))
AllMin = Int(AllMin)
AllMax = Int(AllMax + 0.9)
With SG.Axes.Vertical
.MinScaleType = xlSparkScaleCustom
.MaxScaleType = xlSparkScaleCustom
.CustomMinScaleValue = AllMin
.CustomMaxScaleValue = AllMax
End With
Figure 17-5 shows the resulting sparklines. Now you know the minimum and the maximum, but you need a way to communicate it to the reader.
FIGURE 17-5 You’ve manually assigned a minimum and a maximum scale, but it does not appear on the chart.
One method is to put the minimum and maximum values in A2. With 8-point bold Calibri, a row height of 113 allows 10 rows of wrapped text in the cell. So you could put the maximum value, then vbLf eight times, then the minimum value. (Using vbLf is the equivalent of pressing Alt+Enter when you are entering values in a cell.)
On the right side, you can put the final point’s value and attempt to position it within the cell so that it falls roughly at the same height as the final point. Figure 17-6 shows this option.
FIGURE 17-6 Labels on the left show the minimum and the maximum. Labels on the right show the final value.
The following code produces the sparklines in Figure 17-6:
Sub SP500Macro()
' SP500 Macro
'
Dim SG As SparklineGroup
Dim SL As Sparkline
Dim WSD As Worksheet ' Data worksheet
Dim WSL As Worksheet ' Dashboard
On Error Resume Next
Application.DisplayAlerts = False
Worksheets("Dashboard").Delete
On Error GoTo 0
Set WSD = Worksheets("Data")
Set WSL = ActiveWorkbook.Worksheets.Add
WSL.Name = "Dashboard"
FinalRow = WSD.Cells(1, 1).CurrentRegion.Rows.Count
WSD.Cells(2, 4).Resize(FinalRow - 1, 3).Name = "MyData"
WSL.Select
' Set up headings
With WSL.Range("B1:D1")
.Value = Array(2015, 2016, 2017)
.HorizontalAlignment = xlCenter
.Style = "Title"
.ColumnWidth = 39
.Offset(1, 0).RowHeight = 100
End With
Set SG = WSL.Range("B2:D2").SparklineGroups.Add( _
Type:=xlSparkLine, _
SourceData:="Data!D2:F250")
Set SL = SG.Item(1)
Set AF = Application.WorksheetFunction
AllMin = AF.Min(WSD.Range("D2:F" & FinalRow))
AllMax = AF.Max(WSD.Range("D2:F" & FinalRow))
AllMin = Int(AllMin)
AllMax = Int(AllMax + 0.9)
' Allow automatic axis scale, but all three of them the same
With SG.Axes.Vertical
.MinScaleType = xlSparkScaleCustom
.MaxScaleType = xlSparkScaleCustom
.CustomMinScaleValue = AllMin
.CustomMaxScaleValue = AllMax
End With
' Add two labels to show minimum and maximum
With WSL.Range("A2")
.Value = AllMax & vbLf & vbLf & vbLf & vbLf _
& vbLf & vbLf & vbLf & vbLf & AllMin
.HorizontalAlignment = xlRight
.VerticalAlignment = xlTop
.Font.Size = 8
.Font.Bold = True
.WrapText = True
End With
' Put the final value on the right
FinalVal = Round(WSD.Cells(Rows.Count, 6).End(xlUp).Value, 0)
Rg = AllMax - AllMin
RgTenth = Rg / 10
FromTop = AllMax - FinalVal
FromTop = Round(FromTop / RgTenth, 0) - 1
If FromTop < 0 Then FromTop = 0
Select Case FromTop
Case 0
RtLabel = FinalVal
Case Is > 0
RtLabel = Application.WorksheetFunction. _
Rept(vbLf, FromTop) & FinalVal
End Select
With WSL.Range("E2")
.Value = RtLabel
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlTop
.Font.Size = 8
.Font.Bold = True
End With
End Sub
Formatting sparklines
Most of the formatting available with sparklines involves setting the color of various elements of the sparkline.
There are a few methods for assigning colors in Excel 2019. Before diving into the sparkline properties, you can read about the two methods of assigning colors in Excel VBA.
Using theme colors
Excel 2007 introduced the concept of a theme for a workbook. A theme is composed of a body font, a headline font, a series of effects, and then a series of colors.
The first four colors are used for text and backgrounds. The next six colors are the accent colors. The 20-plus built-in themes include colors that work well together. There are also two colors used for hyperlinks and followed hyperlinks. For now, focus on the accent colors.
Go to Page Layout, Themes and choose a theme. Next to the theme drop-down menu is a Colors drop-down menu. Open that drop-down menu and select Create New Theme Colors from the bottom of the list. Excel shows the Create New Theme Colors dialog box (see Figure 17-7). This dialog box gives you a good picture of the 12 colors associated with the theme.
Throughout Excel, there are many color chooser drop-down menus. As shown in Figure 17-8, a section of each color chooser drop-down menu is called Theme Colors. The top row under Theme Colors shows the four font and six accent colors.
FIGURE 17-7 The current theme includes 12 colors.
FIGURE 17-8 All but the hyperlink colors from the theme appear across the top row.
If you want to choose the last color in the first row, the VBA is as follows:
ActiveCell.Font.ThemeColor = xlThemeColorAccent6
Going across that top row of Figure 17-8, these are the 10 colors:
xlThemeColorDark1
xlThemeColorLight1
xlThemeColorDark2
xlThemeColorLight2
xlThemeColorAccent1
xlThemeColorAccent2
xlThemeColorAccent3
xlThemeColorAccent4
xlThemeColorAccent5
xlThemeColorAccent6
Caution The first four colors seem to be reversed. xlThemeColorDark1 is a white color. This is because the VBA constants were written from the point of view of the font color to use when the cell contains a dark or light background. If you have a cell filled with a dark color, you want to display a white font. Hence, xlThemeColorDark1 is white, and xlThemeColorLight1 is black.
On your computer, open the Fill Color drop-down menu on the Home tab and look at it in color. If you are using the Office theme, the last column is various shades of green. The top row is the actual color from the theme. Then there are five rows that go from a light green to a very dark green.
Excel lets you modify the theme color by lightening or darkening it. The values range from –1, which is very dark, to +1, which is very light. For example, the very light green in row 2 of Figure 17-8 has a tint and shade value of 0.8, which is almost completely light. The next row has a tint and shade level of 0.6. The next row has a tint and shade level of 0.4. That gives you three choices that are lighter than the theme color. The next two rows are darker than the theme color. These two darker rows have values of –.25 and –.5.
If you turn on the macro recorder and choose one of these colors, you see a confusing bunch of code:
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorAccent6
.TintAndShade = 0.799981688894314
.PatternTintAndShade = 0
If you are using a solid fill, you can leave out the first, second, and fifth lines of code.
The .TintAndShade line looks confusing because computers cannot round decimal tenths very well. Remember that computers store numbers in binary. In binary, a simple number like 0.1 is a repeating decimal. As the macro recorder tries to convert 0.8 from binary to decimal, it “misses” by a bit and comes up with a very close number: 0.7998168894314. This is really saying that it should be 80% lighter than the base number.
If you are writing code by hand, you only have to assign two values to use a theme color. Assign the .ThemeColor property to one of the six xlThemeColorAccent1 through xlThemeColorAccent6 values. If you want to use a theme color from the top row of the drop-down menu, the .TintAndShade should be 0 and can be omitted. If you want to lighten the color, use a positive decimal for .TintAndShade. If you want to darken the color, use a negative decimal.
Tip The five shades in the color palette drop-down menus are not the complete set of variations. In VBA, you can assign any two-digit decimal value from –1.00 to +1.00. Figure 17-9 shows 201 variations of one theme color created using the .TintAndShade property in VBA.
FIGURE 17-9 These are shades of one theme color.
To recap, if you want to work with theme colors, you generally change two properties: the theme color, in order to choose one of the six accent colors, and the tint and shade, to lighten or darken the base color, like this:
.ThemeColor = xlThemeColorAccent6
.TintAndShade = 0.4
Note One advantage of using theme colors is that your sparklines change color based on the theme. If you later decide to switch from the Office theme to the Metro theme, the colors change to match the theme.
Using RGB colors
For the past three decades, computers have offered a palette of 16 million colors. These colors derive from adjusting the amount of red, green, and blue light in a cell.
Do you remember art class in elementary school? You probably learned that the three primary colors are red, yellow, and blue. You could make green by mixing some yellow and blue paint. You could make purple by mixing some red and blue paint. You could make orange by mixing some yellow and red paint. As all of my male classmates and I soon discovered, you could make black by mixing all the paint colors. Those rules all work with pigments in paint, but they don’t work with light.
Those pixels on your computer screen are made up of light. In the light spectrum, the three primary colors are red, green, and blue. You can make the 16 million colors of the RGB color palette by mixing various amounts of red, green, and blue light. Each of the three colors is assigned an intensity from 0 (no light) to 255 (full light).
You will often see a color described using the RGB function. In this function, the first value is the amount of red, the second value is the amount of green, and the third value is the amount of blue:
To make red, you use
=RGB(255,0,0).To make green, use
=RGB(0,255,0).To make blue, use
=RGB(0,0,255).What happens if you mix 100% of all three colors of light? You get white! To make white, use
=RGB(255,255,255).What if you shine no light in a pixel? You get black:
=RGB(0,0,0).To make purple, you use some red, a little green, and some blue:
RGB(139,65,123).To make yellow, use full red and green and no blue:
=RGB(255,255,0).To make orange, use less green than for yellow:
=RGB(255,153,0).
In VBA, you can use the RGB function just as it is shown here. The macro recorder is not a big fan of using the RGB function, though. It instead shows the result of the RGB function. Here is how you convert from the three arguments of the RGB function to the color value:
Take the red value times 1.
Add the green value times 256.
Add the blue value times 65,536.
Note Why 65,536? It is 256 raised to the second power.
If you choose a red for your sparkline, you frequently see the macro recorder assign .Color = 255. This is because =RGB(255,0,0) is 255.
When the macro recorder assigns a value of 5287936, what color does this mean? Here are the steps you follow to find out:
In Excel, enter
=Dec2Hex(5287936). You get the answer50B000. This is the color that web designers refer to as #50B000.Go to your favorite search engine and search for “color chooser.” Choose a utility that allows you to type in the hex color code and see the color. Type
50B000. You see that #50B000 is RGB(80,176,0).
While at the color chooser web page, you’re offered additional colors that complement the original color. Click around to find other shades of colors and see the RGB values for those.
To recap, to skip theme colors and use RGB colors, you set the .Color property to the result of an RGB function.
Formatting sparkline elements
Figure 17-10 shows a plain sparkline. The data is created from 12 points that show performance versus a budget. You really have no idea about the scale from this sparkline.
FIGURE 17-10 This is a default sparkline.
If your sparkline includes both positive and negative numbers, it helps to show the horizontal axis so that you can figure out which points are above budget and which points are below budget.
To show the axis, use the following:
SG.Axes.Horizontal.Axis.Visible = True
Figure 17-11 shows the horizontal axis. This helps to show which months were above or below budget.
FIGURE 17-11 Add the horizontal axis to show which months were above or below budget.
Using code from the section “Scaling sparklines” earlier in this chapter, you can add high and low labels to the cell to the left of the sparkline:
Set AF = Application.WorksheetFunction
MyMax = AF.Max(Range("B5:B16"))
MyMin = AF.Min(Range("B5:B16"))
LabelStr = MyMax & vbLf & vbLf & vbLf & vbLf & MyMin
With SG.Axes.Vertical
.MinScaleType = xlSparkScaleCustom
.MaxScaleType = xlSparkScaleCustom
.CustomMinScaleValue = MyMin
.CustomMaxScaleValue = MyMax
End With
With Range("D2")
.WrapText = True
.Font.Size = 8
.HorizontalAlignment = xlRight
.VerticalAlignment = xlTop
.Value = LabelStr
.RowHeight = 56.25
End With
The result of this macro is shown in Figure 17-12.
FIGURE 17-12 Use a nonsparkline feature to label the vertical axis.
To change the color of the sparkline, use this:
SG.SeriesColor.Color = RGB(255, 191, 0)
The Show group of the Sparkline Tools Design tab offers six options. You can further modify those elements by using the Marker Color drop-down menu. You can choose to turn on a marker for every point in the data set, as shown in Figure 17-13.
FIGURE 17-13 Show All Markers.
This code shows a black marker at every point:
With SG.Points
.Markers.Color.Color = RGB(0, 0, 0) ' black
.Markers.Visible = True
End With
Instead, you can use markers to show only the minimum, maximum, first, and last points. The following code shows the minimum in red, maximum in green, and first and last points in blue:
With SG.Points
.Lowpoint.Color.Color = RGB(255, 0, 0) ' red
.Highpoint.Color.Color = RGB(51, 204, 77) ' green
.Firstpoint.Color.Color = RGB(0, 0, 255) ' blue
.Lastpoint.Color.Color = RGB(0, 0, 255) ' blue
.Negative.Color.Color = RGB(127, 0, 0) ' pink
.Markers.Color.Color = RGB(0, 0, 0) ' black
' Choose Which points to Show
.Highpoint.Visible = True
.Lowpoint.Visible = True
.Firstpoint.Visible = True
.Lowpoint.Visible = True
.Negative.Visible = False
.Markers.Visible = False
End With
Figure 17-14 shows the sparkline with only the high, low, first, and last points marked.
FIGURE 17-14 This sparkline shows only key markers.
Note Negative markers are particularly handy when you are formatting win/loss charts, which are discussed in the next section.
Formatting win/loss charts
Win/loss charts are a special type of sparkline for tracking binary events. A win/loss chart shows an upward-facing marker for a positive value and a downward-facing marker for any negative value. For a zero, no marker is shown.
You can use these charts to track proposal wins versus losses. In Figure 17-15, a win/loss chart shows the last 25 regular-season baseball games of the famed 1951 pennant race between the Brooklyn Dodgers and the New York Giants. This chart shows that the Giants went on a seven-game winning streak to finish the regular season. The Dodgers went 3–4 during this period and ended in a tie with the Giants, forcing a three-game playoff. The Giants won the first game, lost the second, and then advanced to the World Series by winning the third playoff game. The Giants leapt out to a 2–1 lead over the Yankees but then lost three straight.
FIGURE 17-15 This win/loss chart documents the most famous pennant race in history.
Note The words Regular season, Playoff, and W. Series, as well as the two dotted lines, are not part of the sparkline. The lines are drawing objects manually added with Insert, Shapes.
To create the chart, you use SparklineGroups.Add with the type xlSparkColumnStacked100, like this:
Set SG = Range("B2:B3").SparklineGroups.Add( _
Type:=xlSparkColumnStacked100, _
SourceData:="C2:AD3")
You generally show the wins and losses using different colors. One obvious color scheme is red for losses and green for wins.
There is no specific way to change only the “up” markers, so change the color of all markers to be green:
' Show all points as green
SG.SeriesColor.Color = 5287936
Then change the color of the negative markers to red:
'Show losses as red
With SG.Points.Negative
.Visible = True
.Color.Color = 255
End With
It is easier to create the up/down charts. You don’t have to worry about setting the line color, and the vertical axis is always fixed.
Creating a dashboard
Sparklines provide the benefit of communicating a lot of information in a very tiny space. In this section, you’ll see how to fit 130 charts on one page.
Figure 17-16 shows a data set that summarizes a 1.8-million-row data set. I used the Power Pivot add-in for Excel to import the records and then calculated three new measures:
YTD sales by month by store
YTD sales by month for the previous year
Percent increase of YTD sales versus the previous year
A key statistic in retail stores is how you are doing now compared to the same time last year. Also, this analysis has the benefit of being cumulative. The final number for December represents whether the store was up or down compared to the previous year.
FIGURE 17-16 This summary of 1.8 million records is a sea of numbers.
Observations about sparklines
After working with sparklines for a while, some observations come to mind:
Sparklines are transparent. You can see through them to the underlying cell. This means that the fill color of the underlying cell shows through, and the text in the underlying cell shows through.
If you make the font really small and align the text with the edge of the cell, you can make the text look like a title or a legend.
If you turn on text wrapping and make the cell tall enough for 5 or 10 lines of text in the cell, you can control the position of the text in the cell by using
vbLfcharacters in VBA.Sparklines work best when they are bigger than a typical cell. For all the examples in this chapter I made the column wider, the height taller, or both.
Sparklines created together are grouped. Changes made to one sparkline are made to all sparklines.
Sparklines can be created on a worksheet separate from the data.
Sparklines look better when there is some white space around the cells. This would be tough to do manually because you would have to create the sparklines one at a time. It is easy to do here because you can leverage VBA.
Creating hundreds of individual sparklines in a dashboard
You address all the issues just listed as you are creating this dashboard. The plan is to create each store’s sparkline individually. This way, a blank row and column appear between the sparklines.
After inserting a new worksheet for the dashboard, you can format the cells in Figure 17-17 with this code:
' Set up the dashboard as alternating cells for the sparkline and then blank
For c = 1 To 11 Step 2
WSL.Cells(1, c).ColumnWidth = 15
WSL.Cells(1, c + 1).ColumnWidth = 0.6
Next c
For r = 1 To 45 Step 2
WSL.Cells(r, 1).RowHeight = 38
WSL.Cells(r + 1, 1).RowHeight = 3
Next r
Keep track of which cell contains the next sparkline with two variables:
NextRow = 1
NextCol = 1
Figure out how many rows of data there are on the Data worksheet. Loop from row 4 to the final row. For each row, you make a sparkline.
Build a text string that points back to the correct row on the Data sheet, using this code, and use that as the source data argument when defining the sparkline:
ThisSource = "Data!B" & i & ":M" & i
Set SG = WSL.Cells(NextRow, NextCol).SparklineGroups.Add( _
Type:=xlSparkColumn, _
SourceData:=ThisSource)
In this case, you want to show a horizontal axis at the zero location. The range of values for all stores was –5% to +10%. The maximum scale value here is being set to 0.15 (which is equivalent to 15%) to allow extra room for the “title” in the cell:
SG.Axes.Horizontal.Axis.Visible = True
With SG.Axes.Vertical
.MinScaleType = xlSparkScaleCustom
.MaxScaleType = xlSparkScaleCustom
.CustomMinScaleValue = -0.05
.CustomMaxScaleValue = 0.15
End With
As in the previous example with the win/loss chart, you want the positive columns to be green and the negative columns to be red:
' All columns green
SG.SeriesColor.Color = RGB(0, 176, 80)
' Negative columns red
SG.Points.Negative.Visible = True
SG.Points.Negative.Color.Color = RGB(255, 0, 0)
Remember that the sparkline has a transparent background. Thus, you can write really small text to the cell, and it behaves almost like chart labels.
The following code joins the store name and the final percentage change for the year into a title for the chart. The program writes this title to the cell but makes it small, centered, and vertically aligned:
ThisStore = WSD.Cells(i, 1).Value & " " & _
Format(WSD.Cells(i, 13), "+0.0%;-0.0%;0%")
' Add a label
With WSL.Cells(NextRow, NextCol)
.Value = ThisStore
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlTop
.Font.Size = 8
.WrapText = True
End With
The final element is to change the background color of the cell based on the final percentage so that if it is up, the background is light green, and if it is down, the background is light red:
FinalVal = WSD.Cells(i, 13)
' Color the cell light red for negative, light green for positive
With WSL.Cells(NextRow, NextCol).Interior
If FinalVal <= 0 Then
.Color = RGB(255, 0, 0)
.TintAndShade = 0.9
Else
.Color = RGB(197, 247, 224)
.TintAndShade = 0.7
End If
End With
After that sparkline is done, the column or row positions are incremented to prepare for the next chart:
NextCol = NextCol + 2
If NextCol > 11 Then
NextCol = 1
NextRow = NextRow + 2
End If
After this, the loop continues with the next store.
The complete code is shown here:
Sub StoreDashboard()
Dim SG As SparklineGroup
Dim SL As Sparkline
Dim WSD As Worksheet ' Data worksheet
Dim WSL As Worksheet ' Dashboard
On Error Resume Next
Application.DisplayAlerts = False
Worksheets("Dashboard").Delete
On Error GoTo 0
Set WSD = Worksheets("Data")
Set WSL = ActiveWorkbook.Worksheets.Add
WSL.Name = "Dashboard"
' Set up the dashboard as alternating cells for the sparkline and then blank
For c = 1 To 11 Step 2
WSL.Cells(1, c).ColumnWidth = 15
WSL.Cells(1, c + 1).ColumnWidth = 0.6
Next c
For r = 1 To 45 Step 2
WSL.Cells(r, 1).RowHeight = 38
WSL.Cells(r + 1, 1).RowHeight = 3
Next r
NextRow = 1
NextCol = 1
FinalRow = WSD.Cells(Rows.Count, 1).End(xlUp).Row
For i = 4 To FinalRow
ThisStore = WSD.Cells(i, 1).Value & " " & _
Format(WSD.Cells(i, 13), "+0.0%;-0.0%;0%")
ThisSource = "Data!B" & i & ":M" & i
FinalVal = WSD.Cells(i, 13)
Set SG = WSL.Cells(NextRow, NextCol).SparklineGroups.Add( _
Type:=xlSparkColumn, _
SourceData:=ThisSource)
SG.Axes.Horizontal.Axis.Visible = True
With SG.Axes.Vertical
.MinScaleType = xlSparkScaleCustom
.MaxScaleType = xlSparkScaleCustom
.CustomMinScaleValue = -0.05
.CustomMaxScaleValue = 0.15
End With
' All columns green
SG.SeriesColor.Color = RGB(0, 176, 80)
' Negative columns red
SG.Points.Negative.Visible = True
SG.Points.Negative.Color.Color = RGB(255, 0, 0)
' Add a label
With WSL.Cells(NextRow, NextCol)
.Value = ThisStore
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlTop
.Font.Size = 8
.WrapText = True
End With
' Color the cell light red for negative, light green for positive
With WSL.Cells(NextRow, NextCol).Interior
If FinalVal <= 0 Then
.Color = 255
.TintAndShade = 0.9
Else
.Color = RGB(197, 247, 224)
.TintAndShade = 0.7
End If
End With
NextCol = NextCol + 2
If NextCol > 11 Then
NextCol = 1
NextRow = NextRow + 2
End If
Next i
End Sub
Figure 17-17 shows the final dashboard, which prints on a single page and summarizes 1.8 million rows of data.
FIGURE 17-17 One page summarizes the sales from hundreds of stores.
If you zoom in, you can see that every cell tells a story. In Figure 17-18, Park Meadows in cell I33 had a great January, managed to stay ahead of last year through the entire year, and finished up 0.8%. Lakeside in cell I35 also had a positive January, but then it had a bad February and a worse March. Lakeside struggled back toward 0% for the rest of the year but ended up down seven-tenths of a percent.
Note The report is addictive. I find myself studying all sorts of trends, but then I have to remind myself that I created the 1.8-million-row data set using RandBetween just a few weeks ago! The report is so compelling that I am getting drawn into studying fictional data.
FIGURE 17-18 Note the detail of two sparkline charts.
Next steps
In Chapter 18, “Reading from and writing to the web,” you find out how to use web queries to automatically import data from the Internet to your Excel applications.