Integrating report elements in Excel

Effective reports integrate text, tables, and graphs. This 1-page report on Ranking states on SAT scores, 2007-08(full pdf) , which I submitted to a FlowingData data visualization competition uses common axes and zebra striping to achieve seamless integration among multiple graphs and tables. The report was created using Excel 2007.

A few steps are required.

• Graphs are rendered transparent by formatting both the chart area and plot area so they have a white, 100% transparent fill.
• Horizontal and vertical axes are deleted after the min and max values have been fixed. Any key or titles are also deleted. The plot area is then stretched to fill the entire chart area. Use of  ’align’ > ‘snap to grid’ is helpful for getting a perfect fit.
• Axes are created outside of the chart within cells. If you have multiple points on the horizon where tick marks or labels will be added, then place the chart over a number of columns that can be divided so that columns line up evenly with the tick marks.
• Zebra striping are simply highlighted rows, which are now visible across the text and transparent charts. I select the lightest gray in Excel’s default palette since it is sufficient to guide the eye, while minimizing disturbance to the graphs.

More to posts to follow shortly…