Software

Use an Excel spreadsheet to prototype your reports

Prototyping is a tried and true method for capturing the design details of user interface design. Joe Goss has discovered a very valuable electronic tool for prototyping reports -- Microsoft Excel.

 

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.

This blog post is also available in the PDF format as a TechRepublic Download and as a TechRepublic Photo Gallery.

Column headings and detail rows

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

Figure A

An example
To clean up the format of the headings, highlight the cells containing the headings, right-click, and select Format Cells from the pop-up menu. (Figure B) Select the Alignment tab, click in the "Wrap text" check-box, and click OK.

Figure B

Format headings
With the cells containing the column headings highlighted, press [Ctrl]-[B] to make the text bold. Next, widen columns B through F to improve readability. (Figure C)

Figure C

Improve readability
Now add the remaining report details. As you do so, try to represent the exceptional data that may appear in the report. In this example, we represent returned goods to Davidson Leadership Training. Commissions are 16 percent of Month-to-Date sales, so we set up a formula in the Commission Amount column that calculates those values for us. (Figure D)

Figure D

Details
Next, we'll clean up some formatting details. Let's right-justify the heading for each numeric field and change the format of the money figures to give them a common look. (Figure E) We'll use the currency format with a standard accounting style for negative numbers. (Figure F)

Figure E

Right justify

Figure F

Accounting style
Let's sort the data by Sales Person and then Customer Name. (Figure G) Highlight cells A1 through F5 and click on the Data menu item, and then Sort. Select the sorting choices shown in Figure H.

Figure G

Sort

Figure H

Sorting choices
As a final step, (Figure I) let's add totals to the sales and commission columns using the Excel AutoSum feature in cells C6, D6, and F6 (See the highlighted AutoSum button on the toolbar).

Figure I

Totals

Notes and explanations

While your customer will appreciate what you've accomplished in showing the format of a needed report, you can add tremendous value to the prototype by including useful narrative. I commonly include text below the report prototype about the report's context, limitations, and variations.

To create this area in the spreadsheet, let's merge a few cells in row 9. (Figure J) Highlight cells A9 through D9, then right-click, choose the Format Cells item from the pop-up menu. Click the Alignment tab, click the "Wrap text" and "Merge cells" check-boxes, and click OK. (Figure K)

Figure J

Merge

Figure K

Text area

This single cell allows us to display nicely formatted narrative text without cramming it into the visual confines of cell A9. I find it helpful to include as many of these rows of merged cells as I need to describe the report.

Let's use the Excel Format Painter to create quickly several similarly formatted rows. (Figure L) Highlight cell A9, and then click on the Format Painter button highlighted on the toolbar.

Figure L

Format Painter
Next, highlight cells A10 through D12 and click on the Format Painter button again. Each row of highlighted cells is now formatted similar to cell A9. (Figure M)

Figure M

Text cells added
Here are some representative notes that might appear in the Notes section. (Figure N)

Figure N

Notes

Prototyping more reports

I find it helpful to right-click on the "tab" at the bottom of the workbook and give the workbook a useful name, particularly if I plan to prototype additional reports. You may also use the pop-up menu available from the tab to create a copy of the workbook. This step is useful when a report variation deserves its own workbook.

Finishing touches

We should have a title for this report at the top of the prototype. Insert a row prior to row 1. Merge cells B1 through D1 to give the heading an attractive format, and type "Sales Commissions by Sales Person" in cell B1. Make the text bold. (Figure O)

Figure O

Title

If you create a number of report prototypes, consider creating a common heading for the work books containing the customer name. You can accomplish this task conveniently by highlighting all of the "tabs", then clicking on View on the menu bar, then selecting Header and Footer and clicking the Custom Header button. Enter a heading, select a font size, and click OK.

As a final step, use Excel's spell check feature (press F7 to invoke it). You may have noticed the typographic error in Note 4 displayed in the next-to-last snapshot. Correcting spelling errors gives your prototype a professional look when you distribute the document to the customer and development staff.

Of course, the report prototypes you'll develop will be more sophisticated than this simple example. For that reason, be sure to check that each prototype prints properly on paper. When you are ready to print a copy, use the Preview button on the Print dialog box to check for proper fit on the page. On the Preview page click on the Setup button. As a quick and easy formatting method, click on the Landscape radio button and the "Fit to" radio button.

Next steps

It is important to remember that a report prototype is another communication tool for working collaboratively with customers. Microsoft Excel is a useful tool for developing this communication method. When the prototype is complete, I can attach the Excel file to an e-mail, send it to a customer, and generally expect him or her to have the necessary software and knowledge necessary to review the prototype.

Editor's Picks