If you're reading this article, you have probably at the very least seen a spreadsheet. It's likely that you have built one a time or two. My hope is that by the end of this article, you begin to understand the importance of usability as it pertains to your spreadsheet activities.
What is usability? The term "Usability" as it is used in the User Experience profession is a measure of how well a design meets the needs or expectations of a user without causing hindrance, hesitation or question. There are some common characteristics (such as learn-ability, efficiency, memorability, errors, user satisfaction, etc...) of an interface that are additionally taken into account to better determine the usability of an interface.
The below list of tips and best practices was formed through forum trend analysis, consultation with spreadsheet users, and the application of some common Information Architecture / User Experience heuristics and best practices. The tips and best practices mentioned below are intended to help you make your spreadsheets data be more consumable by your end users, help with collaboration, or help with professionalism.
Severity RankingsIn order for me to best relay to you the importance of each item as it pertains to the usability of your spreadsheet, I've included a ranking system that I hope you take time to read before you start working through the different suggestions below.
High SeverityThese tip are viewed as having the greatest potential impact to the user's experience. These things are largely and widely excepted as best practices.
Moderate SeverityThe tips that have a moderate severity are intended to help you meet the bare minimum expectations of your users. These tips generally help by making the user more efficient.
Low SeverityIf time permits, you should look into these lower priority tips and best practices. Consider these items as "honorable mentions" as they were not mentioned too often during our research phase.
TipsAnd now, the moment you've been waiting for. Below are the list of tips and best practices for improving the usability of your spreadsheet listed in order of severity (highest to lowest).
1. Font Style Effects Page PriorityIn terms of global usefulness, font is a key player in how you will communicate to your users what is most important on screen. Use color, font weight, font size, font face, and deliberate spacing to raise or lower the importance of a section of information. For example, you would likely want your column headings to stand out more on screen than you would the bulk of your data. Even more visible on screen would be the title of your data set.
2. Establish and use font stylesBecause each font style that you introduce to your spreadsheet results in causing your user to spend more time learning and comprehending the new style, it is best to limit the font family to just two or three choices (i.e. "Georgia" for titles, "Trebuchet" for the rest). Utilizing the spreadsheet software's built in styles allows screen readers to more easily pick up the page hierarchy and intention as well.
3. Use Readable FontsFont style can play a part in accessibility for many people who have visual impairments. Remember to choose a font that is known to be easy to read. With this in mind, it is good to also understand that screen-readers do not pick up conditional formatting.
4. Don't only rely on colorIf you are using color codes as a data element, you should also include a labeling method to ensure that screen-readers pick up your data story.
5. Less is bestEvery additional formatting adjustment you add to your data results in a greater cognitive load being forced upon your users. That means that they must take extra time to "comprehend" what they are looking at when there are MANY style variations, resulting in poor efficiency.
Cell Background Styling
1. Background Style Effects Page PriorityBackground color on cells, rows and columns is another key player in communicating to the user what is most important on the screen. Use this characteristic subtly to draw the users attention to the content that is most important.
2. Don't only rely on colorYou may be tempted to use cell background color to communicate a set grouping. To people who can see in color, this is not a problem, but there are a number of people who have a visual impairment that prevents them from seeing your groupings. They will find it difficult to impossible to pick up on this point. If you choose to use color to identify groupings on your spreadsheet, remember that the color should not be the only way to identify the group. Consider additionally providing a column that labels the groupings as such.
3. Follow a Consistent Color SchemeIf you are going to use background colors, be consistent throughout the workbook (that is to say, all the worksheets within a file). Do this in order to leverage a users existing know-how.
4. Best Zebra Striping PracticeWhen it comes to spreadsheets, the term "Zebra Striping" refers to the practice of alternating background color from line to line (example: one line white, one line yellow, one line white, one line yellow, etc...). There is a natural tendency for a user's eyes to loose their line focus in situations where the data extends an excessive distance to the right. This "Zebra Striping" can help the user stay in line. With that said, the usability focus point here is that the zebra stripe is part of the design and should not be a greater focus point then that of the data itself. Keep the background colors highly differing in contrast from that of the content, but similar to each other.
5. Less is bestEvery additional data element on the screen competes for your users attention resulting in a greater cognitive load being forced upon your users. That means that they must take extra time to "comprehend" what they are looking at. This equals poor efficiency with your sheet.
1. Specify Data TypesWhen working with spreadsheets, remember that the tool is very "data type" oriented. On one hand, humans don't really need to care or know what type of data a cell holds (whether it's a text field, number field, or date field). This is not the case however with programs and computers. When you specify the correct data type, your computer can better determine the most efficient way to store and process the information. As a general rule, "Text" data takes up more memory in a computer then "numeric" data. When you have decided on the layout of your table, you should have the option in your spreadsheet tool to manually set the data type for that cell. If it's a cell that has a data type of "Date", then set the field to be a "Date" field. By doing this, you help to eliminate possible calculation errors, you actually end up aligning yourself with what other spreadsheet users have learned in school, AND you increase efficiency in scanning and comprehending your lists.
2. Make the most of systsem memory by writing conciselyAs mentioned in the "Specify Data Types" section, each extra letter takes up your user's computer's processing capabilities. It is best to keep sentences complete, but brief. Of course, don't leave out the full picture for the sake of saving a byte or two. In doing this, you help the user by making your information quicker to take in AND you potentially improve information load speeds.
3. Data should be sortedYou'll have to evaluate your data a little bit in order to determine the sorting method that most meets your user's expectations. Of course, when in doubt ask a user or two.
4. Break data apartWhen you have a column that can be broken up (such as "Full Name"), by all means break that data into additional fields (i.e. "First Name" and "Last Name"). By doing this, you make the information more accessible to the user.
5. Reference, don't copyWhen you need to have the value of one cell in another location, it's always best to reference the original cell rather than copy. When you copy data from one cell to another, you introduce the possibility of the data becoming corrupted, or inaccurate. Get in the habit of referencing to save you and your collaborative team effort.
6. Write headlines instead of titlesWhen you are deciding on column labels, it's a good idea to ask yourself if the name you are choosing for the column is something that your target audience or those that you are collaborating with would know. Now, slap yourself in the face because you can't assume other users are like you. You must assume that they are different people with a different way of thinking. Fully spell things out so that the newest of new users of your spreadsheet don't find themselves asking what a "DHLTL Ttl %", or a "EE#" is. By all means, ask your users what would most make sense.
7. Spreadsheet should grow downwardStudies have shown that when data extends to the right instead of down, that it takes more time to take in the data. Remember that it is most efficient to have data build in a way that new records are added to the bottom of the spreadsheet instead of the right.
8. Is a spreadsheet the right tool for the job?Don't push the spreadsheet tool to it's limits by filling EVERY row in the whole workbook. If you have that much data, it's best to use a database tool such as Microsoft Access.
9. Keep cell contents briefkeep cell contents brief to improve scanning. This means that you should try and avoid having repeated sentences or paragraphs as the cells content. If there are too many words, it slows the user down.
Avoid rounding numbers which can lead to inaccuracy (maybe increasing decimal places can help with this).
10. Keep formulas simpleThis particular point is especially important when collaborating with others. There's nothing worse then having to figure out someone else's highly complex functionality in order to correct a figure that is showing incorrectly. Not only that, but when functions are complex, there is a raised likelihood that the calculation is producing incorrect data. Avoid bad data and simplify maintenance for other collaborators by keeping it simple.
11. Arrange columns to set page priorityLike text style and background style, you can also help set page priority through the order of the columns. Studies have shown that in the United States, most people are in the habit of reading things from top to bottom and from left to right. Even when it's a not a document, it has still be proven that people seem to continue this learned habit. Leverage this knowledge to get the user to the most important column by placing the priority columns further left, and the less important data columns to the right.
12. Keep related data groupedThere happens to be a lot of research (see Gestalts Law of Proximity) that has shown that people tend to parse and comprehend things by way of mentally grouping, sorting, and calculating. In this particular case, the "User Experience Recommendation" for you is to leverage this human habit to better draw attention to like information. It makes sense that information that is similar be closely coupled on screen so that the user can a) comprehend the screen faster, and b) so the user can scan and locate the information of interest faster.
Border and Grid-line Styling
1. Hide grid lines by defaultThe concept of minimalist design has been long understood (see Aesthetic and Minimalist Design on nngroup.com) in the design world. Minimalist design seeks to eliminate any data element on screen (i.e. text, images, fields, bars, grid-lines, etc...) that would try to complete with more important things on screen for the users attention.
In other words, hide the grids so that users don't spend time mentally comprehending them.
2. Use borders IntentionallyThe goal of this whole effort is to make intentional design decisions. When necessary, use borders to your advantage. Draw your users or collaborative partners in to important areas with borders. Don't over use them though. Remember that "Less is Best".
Work-space and Layout Strategy
1. Only include columns that are neededOne concept that came up time and time again while conducting research for this project was the suggestion to never start a spread in the A1 position. It was suggested several times also to leave row 1 and column 1 blank. The underlying usability focus point here actually has to do with having sufficient white space so as to prevent unnecessary screen tension by way of a cluttered look.
To take this a step further, it is best practice to be intentional with work-space. This means that you should not have more blank rows and columns then are needed. This minimalist approach can actually help screen-readers better parse the screen, resulting in a more accessible spreadsheet.
As a last measure before you share your results out to your boss, make sure to remove those extra columns to the right, and rows towards the bottom.
2. Use blank rows and cells to reinforce page priorityAs with all the other visual elements that can appear in a spreadsheet, it's best practice to be strategic about how you place blank rows and columns. Through adequate white space separation, you can either pull the users focus to what's important, or you can create a cluttered unintentional feel.
Positioning and Alignment
1. Re-size rows and cols appropriatelyWhen items are to close together they become difficult to visually scan. This cluttered effect should be avoided to increase readability of your data. On the flip side however, too much white space can leave your lines difficult to track from left to right. There is no hard fast rule for how much or little space should be included in your columns.
One trick you can use for determining if it's enough space is to squint your eyes until your screen becomes blurry. If they appear to be one group rather than separate columns, then space them out. If they appear to be separate, shrink them in a bit. Do this back and forth adjustment until you've found the sweet spot.
2. Text Alignment StrategyThe problem this topic raises is this. On one hand, studies have shown that in the United States people are better at scanning things when they are left aligned. On the other hand however is the need to meet user expectations by having numbers right aligned. This is a call that you should make after you have talked with those people who will be consuming your spreadsheet. If your boss wants speed and doesn't care about alignment, then go left aligned for all, but if they want it to match standard accounting and numerical practice, then go right aligned.
3. Alignment Effects Page PriorityThrough intentional alignment decisions, you can effectively draw your users attention to the most important information. Things that appear on screen in the top left (In the United States) are seen before things that are seen in the bottom right. When deciding what goes where, remember the goals of the consumer of the spreadsheet information and set information architectural priority appropriately.
Graph, Chart, and Data Visualization Styling
1. A graph says a thousand wordsIncluding a graph, chart or data visualization of some kind can greatly improve the experience that your users have by simply saving them time. Spelling out the high level greater truth in a way that they can easily take in takes advantage of our innate tendency to comprehend through visualization.
2. Remember to keep data accessibleYes, data can be easier to take in for someone who does not have a visual impairment. It's your responsibility to make sure that if your spreadsheet is shared to someone with a visual impairment by your boss or one of your collaborators, that it is accessible. Don't hide the data in a way that is inaccessible to a screen reader.
3. Keep it clean and simpleKeep your data visualizations simple by avoiding fancy formatting (like 3D, shadows, gradients, chart borders, etc...). Remember that when working with spreadsheets, your data is the main thing. If you use a data visualization, build it to accompany the data rather than overpower it. Less is best.
4. Pie charts slow your userWhile pie charts are still used in our business world, there has been evidence that would reveal that we think in a linear fashion, while a pie chart unnecessarily slows us down to think is a circular way (which is not something we do all the time according to Edward Tufte, a leader in the study and implementation of Data Visualization). This slow down coupled with it being difficult for some folks to be able to judge wedge sizes can lead to an overall less than ample experience.
Remember that our focus in this article is to make intentional strategic design decisions to help the user by removing things that cause hindrance, hesitation or question. If we use a chart style that is more in line with the way we think we leverage a users existing habit and thus gain in processing speed.
5. Remember to sortAs mentioned earlier, we strive to help the user take information in by eliminating things that cause them slowdown, or hesitation. By sorting the data within the visualization in a logical way (depends on your data set), you allow them to continue their attention very near to where they left off at the passing of each line. Without sorting, the user's attention is potentially pulled back and forth resulting in additional time spent comprehending.
Images and Word Art
1. A picture says a hundred wordsYou didn't read that wrong. When it comes to spreadsheets, a picture can really say less than a graph or data visualization. That doesn't mean there isn't room in your spreadsheet for a tasteful, data appropriate image.
It's been widely publicized that humans process visual information 60,000 times faster than text. After my own digging around, I was simply unable to find research proof (here's someone who was willing to pay $60.00 to anyone who can provide him the empirical proof we need to believe the actual number of 60,000), so that may not be an exact figure.
What is true however is that we do rely heavily on vision in communication. Something else we know is that the significance of data can go unnoticed unless people can see some visual representation of the data. That's why graphs outweigh your standard photo or clipart.
Use images sparingly to support the content. The moment you place an image, it get's the attention as it will likely be much more interesting then even your most compelling numbers.
2. Remember Accessibility!This is just another reminder that things that tend to be visual... Also tend to be inaccessible to a percentage of the potential demographic of users. When placing images in your spreadsheet, always remember to put alternative text so that a screen reader can describe the image to someone who may sight impaired.
Worksheet Tab Tips
1. Announce additional worksheetsWhile it is a best practice to have all worksheets associated to a project in the same workbook, the tab mechanism just seems to not be the best for visibility. The purpose for this probably due to how it is deprioritized down to the bottom of the screen.
When you need to include another worksheet in your spreadsheet workbook, it is a good idea to announce the existence of the tab in the introduction of your spreadsheet. Do this to increase the likelihood that someone will be made aware that there are other worksheets in your workbook.
2. Tab color matches schemeAdding color isn't always possible, but for those Spreadsheet softwares that allow you to adjust the color of your worksheet tabs, they are a great way to draw users attention and help establish screen importance priority. Remember when doing this that it's also a great way to make your spreadsheet ugly. When choosing a color, it's a good idea to stick to any existing color scheme.
3. Use plain languageRemember to use plan language headings on tabs. Just like the rest of your spreadsheet, we're trying to make your spreadsheet as useable and useful as possible. That means making things easy to understand even for the first time user. Avoid acronyms, slang, jargon, or conversational metaphors as there is always a possibility that your reader will might misinterpret your phrasing.
Hidden Information Strategy
1. Hide vulnerable or distracting areasOne of the problems I hear from people that I know to use spreadsheets through as a bulk of their job is that they get frustrated with people going in and messing with calculations. Another problem I hear about is when the spreadsheet author shares out a piece of data with someone, that someone get's distracted by a sheet or group of cells that has very little to do with what they are supposed to be looking at.
There is an old saying that says "Out of site, out of mind". What this saying means is that if you have a worksheet in your workbook, or a section of cells, or even a row or column that is not part of what you want your audience to be viewing, then it's best to hide or remove it.
2. Hiding causes a possible accessibility issueAlthough there are times that it's good to hide section, it's also good to keep in mind that screen-readers do not work well with hidden sections on spreadsheets. If there is a person who needs to access your spreadsheet who is visually impaired, it's best to make sure you are unhiding areas that they need to be able to get into.
3. use "=iferror" function
On occasion, people will make spreadsheets that will be completely working and accurate, but have a cell that is throwing an error similar to this one...
The usability concern with this is that it is a distraction that draws the user's attention away from what's really important in your sheet. It creates potential undue alarm or concern in your user, and it creates a situation in which your credibility as an expert spreadsheet-monger is lowered a tad.
If your spreadsheet software offers you a function that allows you to test for errors, learn to use it to display a blank field instead of the error.
1. Set spreadsheet language preferencesI haven't seen this personally, but there were some mentionings of this being something that would help with accessibility. Most spreadsheets allow you to set language or location preferences. Setting this allows a screen-reader or translation software know how best to work with your spreadsheet. This is especially important if you don't speak the same language that your audience does.
2. Plain language, plain language, plain language
I've said it previous a number of times now, but it is very important that you understand that lingo, jargon, conversational metaphors, and acronyms can be roadblocks to your user. This is especially important when working with someone that does not speak your language as their native language. Use plain language when possible. Best to think about your phrasing as if you were trying to explain something complicated to a 7 year old child. Spell everything out. Don't use acronyms EVEN if you think everyone on earth knows the acronym.
Data Validation StrategySome spreadsheet softwares will allow you to configure cells to only take in certain types of data, such as a cell that is intended to only ever hold an email address. Many spreadsheets give the capability to test the data being entered to make sure it has certain characteristics. With the email example, maybe the field has to have an 'at' sign (@), and the text ".com". If those pieces are missing, then the software throws an error at your user saying that they must enter valid information.
The reason why this suggestion can contribute to the user experience in a positive way is that it can reinforce to your user that you care enough about their data that you want to make sure it's correct. Yes, an error box can be irritating, but this will be a win for avoiding data corruption as well.
This point can go either way. You choose what's best for the data you are working with.
Freeze Pane for improved efficiency
Considerations on locking or protecting areas
1. Remember AccessibilityRemember when you are locking or protecting cells, rows, columns, or sheets that screen-readers may find these areas impossible to navigate to. If there is a chance that users of your spreadsheet will be visually impaired, it's probably a good idea to explore other ways to safeguard the data without using the locking mechanism that your spreadsheet software offers.
2. Lock vulnerable areasAlthough area protecting can cause an accessibility problem, there may be situations where you are either collaborating or working on something for someone else where there may be a complex calculation. Maybe the situation is that the data in a cell is highly sensitive. Whatever the reason, this is a time that warrants your intentional setting of the security protecting mechanism. If this is the case, a more elegant solution may be to place all your calculations and data points that are sensitive in another worksheet or workbook and reference them. This will allow you to better set permissions.
Usage of Tooltips, Comments and NotesIf your audience is possibly going to be visually impaired, it is good to know that screen-readers don't read tooltips. They can however read comments and notes. If you are going to add in this additional meta info, then use either comments or notes as a practice to avoid potentially roadblocking users.
Merging StrategyOne of the great things about spreadsheets is that you have the flexibility to merge cells to create blocks of varying sizes... Well, this is also one of the problems with spreadsheets. There is a saying that goes "Don't paint yourself into a corner". This saying means that you should evaluate the potential needs of the future when designing something. In this case, it is possible that you merge two or three cells to create an effect, but then later, decide that one of the columns that would normally be where the merged section would be should have a different format all together. At this point, you must then go and facilitate a great effort to get it back to being visually, or aesthetically pleasing and balanced. This large change late in the life of the spreadsheet can also result in broken calculations or other such corrupted data.
In terms of usability, think of this as preventative maintenance. If you are going to choose to merge cells, just be warned that there could be a large potential for problems later. Be very deliberate on this choice, or opt for other means of layout.
Page OrientationMuch of the information in this set of tips to this point has been mentioned in order to cater to the needs of the soft copy user. Page orientation is one of those attributes of a spreadsheet that may rarely come up. If you know of any likelihood that a user is going to print this sheet out, you may consider configuring any page orientation settings that your software offers in advance. If these are setting that are customized by your users, a small note suggesting the best page orientation setting may suffice.
Page Header and FooterJust like Page Orientation above, a page header and footer can be setup in some spreadsheet softwares. Again, this is an attribute or characteristic of your spreadsheet workbook that may only really cater to your hard copy users. Setting the page header or footer is a great way to indicate the tables purpose when it is printed. If a person is printing, it is likely that they are doing it a lot. If they pick up some printed spreadsheet from their desk, they may find it confusing or ambiguous if they don't remember what it's purpose is. If the document has this purpose printed, they are likely to be better able to know what their next steps are.
Templates are a good reference, but you'll be happy to know your spreadsheets inner workings aren't packed with hidden calculation-messing-up functionality.
Version control is important when sharing or working in collaboration. Most spreadsheet tools have this already built in and running. Of course, with so many spreadsheet softwares out there, you should make sure. Having a backup copy is a great idea.
For those who are hearing impaired, one useful method for helping is to provide video closed captions.
For those who are visually impaired, audio descriptions or video scripts may be helpful. Video scripts because they are likely to contain text descriptions of what is happening in the video.
Usage of Intro SheetsHaving an introduction sheet that acts as a gateway to other worksheet tabs may be a good way to summarize the purpose of the work at a holistic level. Remember though that placing this intro sheet is also placing another step between the user and their target information. If the need is there to intro the data then by all means, but if there is only one worksheet... consider an alternative means to intro the data to the user.
Here's one last little animated gif that I wanted to add that might be fun to watch. It's not using all the tips in this article, but it is using many. You may find some other tips in it that are useful...
Through deliberate design decisions while you build out your spreadsheet, you can effectively make it more clear, more meaningful, and ultimately more usable. With your commitment and effort, we can make this world a better place... One data set at a time.
My research search-terms: Tips for improving your spreadsheet, Tips for making your spreadsheet easier to understand, tips to improve your spreadsheet, Tips for creating effective spreadsheets, Tips for improving spreadsheet usage efficiency, Making your spreadsheet easier to read, Improve your spreadsheet, Spreadsheet design best practices, spreadsheet design for others, tips for creating spreadsheets for others, Making your spreadsheet more delightful, Help users comprehend your spreadsheet, tips for creating a delightful spreadsheet for others, Tips for making a delightful spreadsheet, Providing meaningful results when creating spreadsheets, Tips for pimping your spreadsheet, Improving your spreadsheets efficiency, Tips for a better spreadsheet experience, Easy adjustments for improving your spreadsheet, Help your boss understand your spreadsheet, Help your boss get the most from your spreadsheet, Help users understand your spreadsheet, Spreadsheet Authors: Remember your users!, spreadsheet user experience, User centered approach to making a spreadsheet, path to a user centered spreadsheet, Streamlining your spreadsheet, Tips for maturing your spreadsheet skills, A target user approach to building your spreadsheet, Mature your spreadsheet skills, Tips for applying User Experience tactics to your spreadsheet, Creating a delightful journey through your spreadsheet, Improve your spreadsheets affordance for comprehension, Journey to a compelling spreadsheet, Tips for making your spreadsheet more consumable