Tuesday, May 10, 2005

Design and layout presentation tips for Excel

The first 6 months that I worked as a consultant, I worked with a team of consultants. I received a lot of experience in many different industries rapidly. The head consultant taught me some skills that I have never forgotten. Most of these relate to appearance and presentation.
When completing a job for a client, whether it be a financial reporting system, a database, a chart or just a timesheet I always remember what I learnt about presentation. The head consultant would annoy me by spending inordinate amounts of time (or so I thought) on fussy little details, like colour matching, lining up boxes and graphs, font sizes and colours etc. However I quickly learnt from him that making a bad job look good helped sell it to the client. While I hope I haven't learnt his ethics, I did learn that making the job look good earns brownie points like you wouldn't believe.
Here are some of the tips and hints I learnt from him.
Design and layout
One of the easiest ways to set up spreadsheets that calculate or generate results that need to be reported is to separate the function from the form. Just like a shiny exterior on a car hides the internal engine and wiring. I always create my reports and front end menus to look good and generate results and calculations in more functional sheets.
Hiding unnecessary sections
If you must have calculations and working sections visible, then hide the unnecessary bits. Hiding a row or column is only one way of doing this. Using the group function you can rollup whole rows of information, eg components that add to a subtotal or constants and variables such as exchange rates, interest rates, and other indexes. Additionally you can use the ;;; format of a cell rendering the cell contents invisible. Finally colour can be used. Make the background and font the same colour. The danger with these last two approaches is that well intentioned people (users) may delete these seemingly blank rows or columns. If you do this then use cell protection.
Use of colour and graphics
I like to use the company logo or other graphic as a design element in my spreadsheet. Sometimes I do this by using the corporate colours, other times by using the graphic itself. If I have a spreadsheet with a lot of macro buttons, I may use command objects and use the logo as a picture on the button. Beware of using large bitmaps as this will increase file size. The background can be a picture also if the content (and font colours) can be easily displayed. Editing the picture in Photoshop or other graphic editing program you can reduce the brightness and contrast to get a faded picture that can make an attractive background. Alternatively use the corporate colours in background with an accenting colour (usually darker) as the font colour.
Removing excel components
There are a number of excel components that you can turn off. Menu screens and reports screens may not need horizontal or vertical scroll bars, sheet tabs or row and column headings. Using macro buttons to return to a menu can overcome the need for sheet tabs. Not displaying gridlines will give a clean uncluttered look to a layout, and then using borders as necessary can create emphasis in the right areas.
Fonts
Do NOT over use fonts. Every design shop, course or artist will tell you this. I tend to use only one font for an entire spreadsheet, or one for the presentation and the standard Arial font for working sections. Choose a font that reflects the type of spreadsheet, keep the quirky fonts for fun and light stuff not board room presentations. Use bold and italics sparingly.


Posted by: Jethro | 8:39 PM |