Have you ever had to use a spreadsheet that is hard to follow or cluttered with numbers? If so, you’ll begin to appreciate the importance of good design and layout. The other day, an acquaintance sent me an Excel spreadsheet that he had created for his own use and thought it might be useful to others. Here was a spreadsheet that had been created by someone with a good understanding of formulas and functions. But… it wasn’t obvious at first glance what to do with it. Do I click one of these buttons? Do I need to enter some data? Where do I enter the data?
On closer examination, these were the issues that made it difficult to use:
– There was no heading or title.
– It was very dense in terms of the number of cells displayed on the screen.
– Cell ranges were formatted in five different colors. What did it all mean?
– The help provided was limited to short comments in some cells, and some of these were in hidden columns.
– The data entry sections and the results sections were not clearly separated.
– The macro buttons had a square shape.
The end result was that I simply didn’t want to use this particular spreadsheet, despite its useful calculations. So what could have been done to improve it? Here are 7 tips to help you create more user-friendly spreadsheets.
1/ Consider the end user
Who will use the spreadsheet? Do you know Excel? Do they know the content of the spreadsheet? The answers to these questions will determine the design, security issues, the amount of help provided, and possibly the format.
2/ Get the design right
It often helps to put pencil on paper and draw a rough layout on a spreadsheet beforehand. It’s easier to change things at this stage than when the spreadsheet is running.
3/ Provide the right help
There are many ways to help. It could be a header or title that describes what the spreadsheet is or does, a comment in a cell, information associated with data validation, information in a text box, a separate worksheet, or even separate documentation. The amount of help provided will be determined by how intuitive the spreadsheet is to use and also by the answer to that first question, WHO Will you use the spreadsheet?
4/ Data entry separate from results
The areas of the spreadsheet for data entry should be kept separate from the areas that provide the results of the calculations. If not done, it can be confusing for the end user and also makes the spreadsheet more difficult to maintain. This separation could mean using separate worksheets or simply ensuring a clear distinction between the two areas on one worksheet.
5/ KISS
Too much information in a worksheet can be overwhelming, and generally the simpler the better. The spreadsheet mentioned at the beginning of this article could have been improved by distributing the information among other worksheets. Other ways to achieve a simple look are:
– Use graphs to convey information instead of data tables.
– Increase the row height so that the worksheet is not too dense with data.
– Shade every second row on a worksheet with a light color where there are are lots of numbers
6/ Consider the Normal Flow of a Document
The usual way to read a book, newspaper or simply text on the screen is from left to right and from top to bottom. Your spreadsheet must also follow this convention. The information that the user needs to see or respond to first should be at the top left or top center.
7/ Format for the description
The general rule is; format for description, not for decoration. What seems good to one person may seem terrible to another. Multiple colors can be confusing, and again, usually the simpler the better. If you have created a table or database in Excel, the headings may be in bold to separate them from the data. Perhaps more important than the choice of format is that it be consistent throughout the document.
Bonus tip:
stick with the conventions
I mentioned earlier that the buttons on the hard-to-use spreadsheet were square. If you look at almost any website using a button, the width-to-height ratio falls roughly in the range of 2:1 to about 5:1. Sizes that are too outside of this range look a bit odd and aren’t as easily identifiable as buttons.
Hyperlinks you create in Excel can be formatted with any color you like, but unless there’s a good reason, stick with the familiar blue and underline, as in ExcelProductivityTips. Spreadsheet templates that used to ship with Excel have been formatted with light yellow for data entry areas and light green for results. Some people still use this convention.
