Reporting Results: Part 2
Based on Chapter 11, Excel 2007 Dashboards & Reports (Alexander) and Create Dynamic Charts in Microsoft Office Excel 2007 and Beyond (Scheck)
Bullet Graph (pp. 200‐205, Alexander, Chapter 9, EXCEL Dashboards) 1. Start with a data table
2. Highlight the entire table and plot the data on a stacked column chart
3. Click the chart and select the switch Row/Column button.
4. Right click the target series and choose change chart type. Change the chart type to a line with markers.
5. Right click the target series again and choose format data series.
6. In the dialog box (format data series dialog) click the series options button and click secondary axis.
7. Still in the format data series dialog box, click the marker options and adjust the marker to look like a dash.
8. Still in the format data series dialog box, click the marker fill button and select the solid fill property to set the color of the marker to black.
9. Still in the format data series dialog box, click the line color button and select the no line option.
10. Go back to chart and delete the new secondary axis that was added to the right of the chart.
11. Right click the value series and choose format data series.
12. In the format data series dialog box click the series options button and click secondary axis.
0%
20%
40%
60%
80%
100%
YTD Rev vs Plan
13. Still in the format data series dialog box under series options, adjust the gap width property so that the value series is slightly narrower that the other columns in the chart – between 205% and 225% is typically okay.
14. Still in the format data series dialog box, click the fill button the select the solid fill property to set the color of the value series to black.
15. Last, change the color for each qualitative range to incrementally lighter hues.
Completion of step 4:
Completion of step 7:
Completed bullet chart:
Performance Against a Target Range (pp. 206‐208, Alexander, Chapter 9, EXCEL Dashboards)
1. Set a limit table.
2. Build a chart feeder to plot the data points for the target range.
3. Add a row for the actual performance values.
4. Highlight the entire chart feeder table and plot the data on a stacked area chart.
5. Right click the values series and choose change chart type – change to a line chart with no markers.
6. Right click the values data series again and choose format data series.
7. In the format data series dialog box click the series option and click secondary axis.
8. Go to chart and delete the new axis.
9. Right click the lower limit data series and choose format data series.
10. In the dialog box click the fill button and select the no fill option.
0
5 10 15 20 25 30 35 40
Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec
On-Hand Boxes of Meat
Inventory Control Trend
(Gray band = acceptable range)
Apply formatting adjust to colors and labels.
0 10 20 30 40 50 60 70 80
Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec
Values Upper Limit Lower Limit
0 5 10 15 20 25 30 35 40
Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec
Upper Limit Lower Limit Values
0 5 10 15 20 25 30 35 40
Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec
Displaying Information
Sparklines (Tufte): intense, simple, word‐sized graphics | http://www.edwardtufte.com/bboard/q‐and‐a‐fetch‐
msg?msg_id=0001OR
Edward Tufte (The Visual Display of Quantitative Information, 2ed, p. 13) states that graphical displays should:
Show the data
Induce the viewer to think about the substance rather than about methodology, graphic design, the technology of graphic production, or something else
Avoid distorting what the data have to say
Present many numbers in a small space
Make large data sets coherent
Encourage the eye to compare different pieces of data
Reveal the data at several levels of detail, from a broad overview to the fine structure
Serve a reasonably clear purpose: description, exploration, tabulation, or decoration
Be closely integrated with the statistical and verbal descriptions of a data set.
Resources:
http://www.bonavistasystems.com/
http://www.bonavistasystems.com/Blog.html http://www.bonavistasystems.com/Articles.html http://www.perceptualedge.com/
BonaVista MicroCharts Live EXCEL Dashboards
http://www.bonavistasystems.com/OnlineDemoReports.html
2008 EXCEL Dashboard Competition
http://blog.xlcubed.com/2008‐excel‐dashboard‐competition‐winners/
Sample Dashboards from EXCEL 2007 Dashboards & Reports for Dummies by Michael Alexander
Source: Create Dynamic Charts in Microsoft Office Excel 2007 and Beyond, Scheck (p. 47)
Range Name Convention
Source: rS1_Method, Scheck (Figure 9) Check Box1
1 Chapter 11, EXCEL 2007 Dashboards & Reports
