- Tip 1: Use a background fill color.
- Tip 2: Incorporate colors, but not too many.
- Tip 3: Avoid the use of tabs for navigation.
- Tip 4: Use borders effectively.
- Tip 5: Make data input areas obvious.
- Tip 6: Avoid horizontal scrolling.
- Tip 7: Avoid cell merge at all cost.
- Tip 8: Properly format data.
- Tip 9: Use tables to display large sets of data.
- Tip 10: Don’t turn useful design elements into gimmicks.
- Other Resources
The aesthetic design of a spreadsheet, when done right, can add significant benefits in terms of usability and functionality.
This post within the Spreadsheet Design series begins the focus on Layout in Excel. Any serious spreadsheet developer has the experience of being somewhat dissatisfied with a product’s final design. Sometimes scope creep causes original plans to go off track. Other times, the lack of scalability may prevent the inclusion of some features or limits to amounts of data.
Proper planning can help eliminate some of the most common issues. The following 10 tips provide general guidance for implementing a solid spreadsheet design. Encompassing all of these tips is the need to be consistent. For large projects, this may mean establishing a style guide to ensure users have a seamless experience across your spreadsheet application.
Tip 1: Use a background fill color.
Gridlines exist primarily to denote the border between cells. If your spreadsheet contains a dashboard to be presented to a user, and a user will not enter any data in the sheet, you can make the dashboard look significantly sharper by eliminating gridlines. A background fill will do this. Excel does have an option to turn gridlines off, but this feature is a setting local to your computer. If a user does not have this feature activated, gridlines will appear when they open the spreadsheet.
In most cases, a white or off-white background will be fine. Avoid bright colors, and while dark colors may convey a sense of coolness, they may not be very professional.
Tip 2: Incorporate colors, but not too many.
Colors can be an effective means of breaking up content and guiding a user to important areas. Too many colors may confuse a reader. Here are some good guidelines for colors on the spreadsheet.
- Header color: A neutral color for the header.
- Body color: Generally white or an off white.
- Form container color: a soft neutral color, such as light blue.
- Form field color: if the Form area is a color, these should be the same color as the background. Otherwise, a light hue of any color will suffice.
As was discussed during the Navigation part of this series (see Navigation in Excel with Text Links, Navigation in Excel with Shapes, Navigation in Excel with VBA), users appreciate a strong navigation system. Specifically, anything that works in place of the tab toolbar is a great alternative. There are three primary reasons why this is a good practice. First, the tab toolbar is out of the users view, and it may not be obvious to new users that different tabs contain important items. Second, your workbook may contain a mix of tabs for spreadsheets that handle dashboards, data entry, and data storage. It may not be obvious to a user which tabs they should go to for data entry vs data storage. Finally, implementing your own navigation system gives you full control of the navigation layout, features, and guidance to users.
Tip 4: Use borders effectively.
Borders are great for making data easier to look at, separating components of dashboards, and identifying forms. Some general good practices are not to overdo it with colors, stick to consistent rules across your spreadsheet, and use them to help guide users to meaningful content.
The following displays an effective use of lines to make data easier to read.
Tip 5: Make data input areas obvious.
It’s important to let users know where they can input data and where they cannot. One major struggle that all spreadsheet developers have faced is managing formula cells vs. input cells. While workbook lock features can ensure that users do not overwrite formulas or modify layouts, it’s important to let users KNOW where they can entre data. This can be done by the following:
- Identify a form by a form area; a large area typically surrounded with a thin border and highlighted in as slightly different shade from the background.
- Input cells can be identified with a border and alternate color. Inactive input cells can contain a pattern or darker color.
- Ensure that any input cell has a label to its left.
- A tooltip should be provided, whether it is a cell comment, a data validation comment, or a validation guide in a nearby cell.
In the following example, the use of a form area and colors is used to show the user where data should be placed.
Tip 6: Avoid horizontal scrolling.
In terms of dashboards or reports, it’s helpful to provide users with one action (vertical scrolling) to see the entire report. Horizontal scrolling is unnatural in the realm of viewing documents (can you name any websites that purposely scroll horizontally?). Likewise, it’s easy for users to miss important areas of large documents if they require some combination of vertical and horizontal scrolling.
It’s one thing to eliminate horizontal scrolling in Excel documents on your computer, but you also need to think of users who use smaller resolutions. According to w3schools, 1366 x 768 is the most popular resolution for internet enabled desktops. If you happen to be using a resolution of 1920 x 1080, content you place on the right edge of your spreadsheet will require horizontal scrolling in a 1366 x 768 resolution. It’s important to test your spreadsheet in different resolutions to find out if horizontal scrolling is needed. For example, on a 1920 x 1080 resolution, the right-most column is column Z. In 1366 x 768, the right-most column is column T. The best approach is to limit the content to column T, or a total column width of about 167 if you modify column widths.
Tip 7: Avoid cell merge at all cost.
This is a common rule espoused by Excel experts. NEVER use merged cells. They may help accomplish some design goals, but you will regret using them later. Merged cells can cause conflicts with data filters, copying/pasting, and cell insertion.
As an alternative to merging cells, expand column widths to allow for additional content.
Tip 8: Properly format data.
Make sure data formats are consistent and accurate. Throughout your workbook:
- Dates should be a consistent format.
- Large currency amounts should drop cents.
- In most circumstances, numbers showing amounts should be formatted to show commas (or periods for non-US) separating thousands. Numbers to identify customers or orders typically do not contain comas or decimals.
- When displaying numbers in a table, decimal units should be consistent among categories.
- The “Accounting” format is effective for financial statements, but may not properly line up to the right in regular reports. It’s best to use currency.
- Financial statements with most numbers greater than 1 million should drop the hundreds and should not contain a currency symbol. As an alternative, a note should be placed near the top declaring “Thousands, in US Dollars”.
Tip 9: Use tables to display large sets of data.
Tables are an effective way to manage tabular data. One of the major benefits of a table is that formulas will automatically copy down from the previous row when a new row is added. Tables contain filter features by default, and can calculate sums, averages, and counts automatically at the bottom. Additionally, it’s very easy to change the color scheme of a table.
To change a dataset into a data table:
- Activate the cell on the top-left corner of the data.
- Select Insert.
- Select Table.
- In the dialog box, the range of data should automatically appear. Still check to make sure, as an empty row may lead to an incorrect range.
- Press OK.
The dataset should now convert to a data table and look as follows:
Tip 10: Don’t turn useful design elements into gimmicks.
Excel offers many features to help spruce up dashboards and make data analysis more appealing. Some of these features include conditional formatting, spark lines, pivot tables, and SmartArt. While these can be helpful in presenting and emphasizing data, it’s important not to overuse them as they can distract from the main purpose of the report.
An effective use of sparklines, showing monthly price trends:
An ineffective use of sparklines. It’s difficult to understand the information being conveyed and distract from the data:
Given that most Excel users are familiar with desktop software and web-based applications, it’s a good habit to follow related design standards in your project. By incorporating these elements, users will be able to intuitively learn the spreadsheet’s front end functionality and focus on inputting data. The following are various links focused on website and software design.