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.

8 comments
Janet Williams
Janet Williams

great example, thanks for posting. Janet Excel-Templates

JNirvaha
JNirvaha

There is really no need to only prototype your reports. I have been able to use Excel to actually produce professional reports. If you are unsatisfied with the amount of labor needed to produce individual statements in Excel, check out this tool here- http://www.oneclickcommissions.com/excel-reports.html Hope this helps. -Conner

Mark W. Kaelin
Mark W. Kaelin

What is your preferred method for prototyping reports? Perhaps a better question, do you prototype reports?

Ron_007
Ron_007

using Excel this way. The only potential problem I can see is that one brighter than average user who takes the next step and asks for the printed reports to be delivered in Excel format. That way they can manipulate the numbers any way they want. But it makes keeping an audit trail a little more difficult.

Neon Samurai
Neon Samurai

My whole day job is devining data out of the databases then feeding it through overly-complicated worksheets to template the final report summary which may go out as a PDF or XLS with cell formula removed (cut, past values.. whammoo). My most complicated at the moment feeds data tables through an XLS, then database for SQL filtering, then too an XLS for formatting; doing it all in one step kept crashing my humble dual core and gig of ram. :D Pivot tables don't offer the same freedom as coding my own sum(if())s though it is time to look at using more macros for the repetitive formatting steps. My worksheet templates rank in the Megs size categories though the final report may be a few hundred K.

Justin James
Justin James

I have always used Excel to prototype reports. In fact, I've learned that users *overwhelmingly* prefer Excel to actually recieve their reports too. Why? Because they don't need to muck about with an unfamiliar application, it is easy to copy/paste to other documents and emails, and because it lets them experiment with the formulas and numbers (in "what if" scenarios) and easily customize the formatting to meet their needs. I've worked with things like Cognos, and beleive me, they might be the "right took for the job" from the programmer's view, but the users want Excel. J.Ja

Justin James
Justin James

Three jobs ago, this is *exactly* what I was doing too! Was it fun? Nope. Did it feel like the "right" way of doing things? Nope. Did it work? Heck yes. In our case, we were getting weekly text files of data, sometimes I would use Perl to pre-pre-process them, then they would go into FoxPro to be turned into a standard format (the text file dumps would often vary from week-to-week, and different reports would need similar data from different souces, then run a bunch of queries to join the data up, massage it, etc. Finally, it would output a number of CSV files. Then, we would open an Excel template which had an "interface" sheet, where would would put the path to the data files, the report meta data (reporting time frame, etc.), and run a maco which would open the data files, and typically do something like: 1. Start a new workbook 2. Copy all of the sheets from myself to the new workbook, except the interface sheet and the data sheets 3. Selectively copy the pertinent data for this particular report (say, only the data for the sales area I am reporting on) to the data sheet in the new workbook 4. Run .Calculate() on each sheet in the new workbook 5. Do .Range().Formula = .Range().Values on each sheet of the new book, to replace the formulars with hard values 6. Delete the data sheets in the new workbook, save it to disk 7. Repeat for each sales area to be reported on It was enough work to keep me occupied for quite a number of hours each week, but over the year I worked there, by refining the process, we got it down from being something like 20 hours of my time each week to 5 or 6. Things like automatically opening the data files instead of needing a copy/paste. When I left there, my next step was to stop using VBA entirely, and manipulate the sheet from a .Net application; the idea was that if I did that, I could do everything in one place (queries, regex's, etc.) which would make it even easier. J.Ja

Editor's Picks