Making Spreadsheets More Useable

Financial modeling can be a big part of our work with clients and it's always a challenge to present such incredibly complex information in an understandable package. If you use spreadsheets for internal ad hoc reporting, budgeting or modeling here are some tips for making sure your message doesn't get lost in the clutter.

1. Always use page headers. And try to standardize headers so reports are easily identifiable. In our office we use a simple 3 line header to identify all documents. The first line is the client or engagement, the second line is a short title for the report and the third line is the time period covered by the report. If you're working with a report that is a living document it's a good idea to put the revision date in the header. I favor this location because experience has taught me that people often update the report but forget to change the smaller, less noticed revision date in the bottom right hand corner.



2. Use special formatting to separate column headers and row titles. If everything is in the same font the eye has a hard time separating the data from it's context. Bolding and underlining column headers can make a big difference as can indenting row titles to separate groups of related information.



3. Standardize number formats. There is nothing more confusing than trying to review a column of numbers when some of them have 2 decimal places, some have 1 and others none at all. Additionally, reviewing numbers without a thousands separator can lead to mistakes. Use the same format all the way down the column and make sure your decimal points all line up in a straight row.



4. Use shadow boxes and color to separate different parts of a report or spreadsheet. I have won clients and very profitable engagements because I used this technique and my competitors did not. Our eyes and brains separate data much easier when it is visually distinguishable and there is nothing so simple as color to set one section apart from another. Even if printed in black and white users will still be able to tell a difference between reports that use color and those that do not.



5. Use cell protection on working models. If you are building something dynamic either keep all of your data input fields in one place or highlight the fields with special formatting. Then protect the worksheet so that values in these cells can be changed but the integrity of the rest of the worksheet cannot be compromised. I've sent out unprotected worksheets only to have clients send them back with formulas overwritten, key rows deleted or formats changed.

Microsoft excel is a powerful tool. However, most people don't take the time to give their reports an image and presentation befitting the importance of the information. In 2 or 3 hours you can learn a lot by playing around with formatting, experimenting with color and drawing shapes. Once you know what excel is capable of it's just a matter of spending 2 extra minutes polishing the appearance of your spreadsheets to make them look like they came from a pro. One of the best ways to learn new tricks is to ask questions about worksheets you see or to download some samples from the web and take a look at how the authors did their formatting. Two sites that get a lot of excel press are i4c which is geared toward education and Resources for MS Excel which is a little more technical in nature. Both can teach you a lot and provide sample worksheets you can study.

Joey Brannon is the founder of Axiom Professional Group, a tax, consulting and accounting firm in Bradenton, Florida. Mr. Brannon is both a CPA and an EA. You can find out more about Axiom by visiting www.axiomcpa.com.