“For Excel 2010, we’ve implemented sparklines, 'intense, simple, word-sized graphics,’ as their inventor Edward Tufte describes them in his book, Beautiful Evidence. Sparklines help bring meaning and context to numbers being reported and, unlike a chart, are meant to be embedded into what they are describing...”
We’ll explore sparklines by creating a variant of our dashboard that shows trend over time for tickets by state using the second PivotTable. This example builds on our previous work in Part IV.
Right-click on and make a copy of your existing sheet and name it Sparklines (Figure 23-1).
Right-click on the worksheet that contains your dashboard.
Select Move or Copy to show the Move or Copy menu (Figure 23-2).
On the new copy of your dashboard, remove full name from the second PivotTable.
Add State from the People dimension (Figure 23-3).
Tweak the column width or slicer position as required.
To add a sparkline, select the cell, you’d like to insert your sparkline.
Go to the Insert ribbon and choose to insert a Line sparkline (Figure 23-4).
Select the data range for your sparkline, as shown in Figure 23-5. Your sparkline will appear to the right of your PivotTable.
Grab the bottom right corner of the cell that contains your sparkline and fill down to extend it to other rows from your PivotTable (Figure 23-6).
![Insert a sparklineComment [GM9]: need figure title](httpatomoreillycomsourceoreillyimages1715567.png)
To make these sparklines more visible, let’s change the row height to 25 pixels.
On the Page Layout ribbon, you’ll need to reshow the heading. This enables you to select the desired rows.
Set the row height to 25px as shown in Figure 23-7.
Now, using basic Excel formatting skills, format a border and then fill the header with the right colors as shown in Figure 23-8. This allows you to make the sparkline area look like part of the PivotTable.
We’re in the home stretch now! To complete the final cleanup of our dashboard:
Highlight the sparkline.
Use the Design ribbon.
Show the High Point, Low Point, and Markers.
Format the line color to match your PivotTable.
Set the sparkline weight to 3 from the Sparkline Color drop-down (Figure 23-9).
![Completed sparkline exampleComment [GM13]: need figure title.](httpatomoreillycomsourceoreillyimages1715572.png)
In Figure 23-10, we see the final result from our sparkline exercise. We have two sets of slicers focused around filtering by location or filtering by product line. The slicers are connected to a total of four visualizations that include charts, tables, and sparklines showing trend over time information. Using your tabular cube and the data visualization tools in Excel 2010, you have been able to create a compelling solution to understand your help desk ticket information from your LightSwitch application.
By this point, we have a pretty fantastic solution. But wait, there’s more! This is actually a pretty simple addition. Using conditional formatting on our second PivotTable, we can add a data bar to our Average Days to Closure.
Data bars and icon sets provide one more way for us to communicate visually in line with our data. In Figure 23-11, you can see the result of adding a data bar as conditional formatting on the Average Days To Closure from our named set.
![Completed example with DataBarsComment [GM14]: need figure title](httpatomoreillycomsourceoreillyimages1715574.png)
So far in Part IV, we’ve covered tremendous territory. Most folks aren’t aware of the huge number of capabilities for data visualization that are found inside of this simple end-user Office tool. By exposing this power inside the Excel client, any information worker can utilize or even create these solutions.
In Chapter 24, we will configure a Report Gallery in SharePoint to host our Excel dashboard using Excel Services. Then in Chapter 28, we will publish this solution via SharePoint to move our solution from personal business intelligence to community or team BI.