Use an Excel spreadsheet to prototype your reports

An example

This gallery is also available as a TechRepublic Programming and Development blog post and as a TechRepublic Download.

Prototyping is a tried and true method for capturing the design details of user interface design. The prototyping process often reveals ideas that help users of electronic systems get work done effectively and efficiently. Such prototypes also help the project team recognize flaws in system design. While I am a big fan of paper prototyping for user interface design, I've found a very valuable electronic tool for prototyping reports -- Microsoft Excel.

Less-experienced developers sometimes overlook the output requirements of a system when designing it. While user interfaces are sexy and cool, reports are well -- two-dimensional! That said, a designer who does not evaluate system output is in for a big shock -- you can't get something out of a system if it cannot be produced from what someone put into to it. Report prototyping often reveals holes in the design for both input functions and data processing functions.

Microsoft Excel is a natural tool for prototyping reports. It helps me create, organize, and refine the layout and content of a report. Columns become report columns and rows become sections for headings and report details. By creating one report design per workbook (i.e., spreadsheet "tab"), the tabs in a spreadsheet become a repository for a system's report prototypes.

With apologies to left-handed people, I'll describe mouse actions in the following from the perspective of the right-handed. I use the term "click" to indicate a single click on the left-hand mouse button. Similarly, "right-click" indicates a single click on the right-hand button. This explanation assumes you have access to Excel 2002 (or later) and some familiarity with this Microsoft product.

Column headings and detail rows
As a simple example of an Excel report prototype, I've included snapshots of a "Sales Commission" report. Let's start by creating column headings and enter a few lines of representative data.

By Mark Kaelin

Mark W. Kaelin has been writing and editing stories about the IT industry, gadgets, finance, accounting, and tech-life for more than 25 years. Most recently, he has been a regular contributor to,, and TechRepublic.