[ad_1]
7 Ways to Improve Excel Spreadsheet Usability
Have you ever had to use a spreadsheet that was hard to follow, or crammed with numbers? If you have, you will 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 might be useful to others. Here was a spreadsheet that had been created by someone with a good knowledge of formulas and functions. But… it was not obvious at first glance what to do with it. Do I click on one of these buttons? Do I need to enter some data? Where do I enter the data?
On closer examination, these were the problems that made it difficult to use:
– There was no heading or title.
– It was very dense in terms of the number of cells showing on screen.
– Ranges of cells were formatted in five different colours. What did it all mean?
– Help provided was limited to brief comments in some cells, and some of these were in hidden columns.
– The sections for data entry and the sections for results were not clearly separated.
– Macro buttons were square in shape.
The end result was that I just did not want to use this particular spreadsheet, despite its useful calculations. So what could have been done to make it better? Here are 7 tips that will help you create more user-friendly spreadsheets.
1/ Consider the End User
Who is going to use the spreadsheet? Are they knowledgeable about Excel? Are they knowledgeable about the contents of the spreadsheet? The answers to these questions will determine the layout, security issues, the amount of help provided, and possibly the formatting.
2/ Get the Layout Right
Often it helps to put pen to paper and sketch the rough layout of a spreadsheet beforehand. It is easier to change things at this stage than when the spreadsheet is well underway.
3/ Provide Adequate Help
There are lots of ways to provide help. It could be a heading or title that describes what the spreadsheet is or does, a comment in a cell, information that is 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 to use the spreadsheet is and also by the answer to that first question, Who is going to use the spreadsheet?
4/ Separate Data Entry from Results
Areas of the spreadsheet for data entry should be kept separate from the areas that provide results of calculations. If not done, it can be confusing for the end user, and also becomes harder to maintain the spreadsheet. This separation might involve using separate worksheets, or just ensuring a clear distinction between the two areas on the one worksheet.
5/ KISS
Too much information on one worksheet can be overwhelming, and generally, simpler is better. The spreadsheet mentioned at the start of this article could have been improved by spreading information over other worksheets. Other ways to achieve a simpler look are:
– Using charts to convey information rather than tables of data.
– Increasing the row height so that the worksheet is not too dense with data.
– Shading every second row on a worksheet with a light colour where there are lots of numbers.
6/ Consider the Normal Flow of a Document
The usual way to read a book, newspaper or just text on screen is left to right and top to bottom. Your spreadsheet should follow this convention too. The information that the user needs to first see or respond to first should be in the top left or top centre.
7/ Format for Description
The rule of thumb is; format for description, not decoration. What looks good to one person can look terrible to another. Multiple colours can be confusing, and again, generally simpler is better. If you have created a table or a database in Excel, the headings could be bolded to separate these from the data. Perhaps more important than the choice of formatting is that it is consistent throughout the document.
Bonus Tip:
Stick with Conventions
I mentioned before that the buttons on the hard-to-use spreadsheet were square. If you look at just about any website using a button, the ratio of width to height falls roughly in the range 2:1 to about 5:1. Sizes too far outside this range look a bit strange, and are not as readily identifiable as buttons.
Hyperlinks that you create in Excel can be formatted any colour you like, but unless there is a good reason, stick with the well-known blue and underlined, as in, ExcelProductivityTips. The spreadsheet templates that used to ship with Excel were formatted with light yellow for areas of data entry, and light green for results. Some people still use this convention.
[ad_2]
Source by Ian K Johnson
