Have you ever received a poorly constructed and hard-to-understand spreadsheet?  The data is scattered all over the place, the fonts and colors are inconsistent… It’s just a pain to read and nearly impossible to use. Laying out a spreadsheet so users can quickly read, find, filter, sort and print its data is one of the most important skill a spreadsheet creator can have.

In this post you will learn some rules that, when followed, will help you make a spreadsheet that you and others will find useful. Applying these rules will save you time and will allow you and others to quickly locate and edit information, and you’ll be prepared to take advantage of all those “cool” features like frozen panes, print titles, data tables, auto filters, and pivot tables!

Rule #1: Use multiple columns

Spreadsheets are composed of multiple rows and columns. Instead of cramming all your data in one or two columns, use many columns. For example, if you are listing contact information, use separate columns for the organization name, first name, last name, street address, city, state, zip, phone number, email etc.

If you keep your data in separate columns you will be able to easily sort, filter, move and summarize the data. But if you combine multiple pieces of data into one column you will find it difficult if not impossible to sort, filter, move and summarize the data as desired.

Figure 1: Bad Design – Data should be separated into multiple columns

Figure 2: Good Design – Data is separated into multiple columns

Rule 2: Separate the column heading and the table heading

When you sort the data in your table, the spreadsheet will try to detect if the table has column headings. If the column headings appear just below a table heading, then your spreadsheet may mistake the table heading as the first column heading, causing it to assume the other columns don’t have headings. This will frustrate your ability to sort the data, as shown in Figure 3 .

To help the spreadsheet know the difference between column and table headings simply insert a short blank row between them as shown in Figure 4.

Figure 3: Don’t put column headings just below a table heading

Figure 4: Place an extra row between the table heading and the column headings

Rule #3: Make sure each column has a unique column heading

Once you have separated your column headings from you table heading you should make sure that each column has its own heading (it should never be blank) and that heading must be unique. Otherwise the program will be confused and you will have problems similar to those shown in Figure 5.

Figure 5: Don’t have multiple columns with the same heading. Don’t have blank column headings either.

Rule #4: Don’t use a multiple cell column headings

The sort and other features that use column headings only recognize column headings that appear in individual cells. Cells that are merged don’t work. See Figure 6. Column headings that occupy two rows like the ones shown in Figure 7 also cause problems.

In most spreadsheet programs you can however create a multi-line column heading in one cell by press Alt Enter between the lines that should be separate. See Figure 8.

Figure 6: Don’t use a merged column heading

Figure 7: Don’t use column headings that span two or more rows

Figure 8: A column heading with two or more lines is okay provided the entire heading is in the same cell.

Rule #5: Avoid blank rows and columns

Spreadsheets are programmed to use contiguous data. The dictionary defines “contiguous” as “being in actual contact: touching along a boundary or at a point.”

Inserting a blank column or a blank row in the midst of your data makes it not contiguous. The data in Figure 9 is not contiguous. Thus when you go to sort or filter it only part of the data gets sorted or filtered. Thus it is best to avoid blank rows and blank columns in the midst of your data.

Figure 9: Don’t insert blank rows and columns in the midst of your data

Rule #6: Don’t include subtotals in the midst of your data

Manually inserting subtotals in the midst of your data will cause the spreadsheet to treat the subtotals as data. Thus, when you sort or filter the data, the subtotals may change position, as shown in Figure 10, and it will usually yield an inaccurate calculation.

Figure 10: Don’t manually insert a subtotal in the midst of your data because it will be moved when you sort and will yield an inaccurate value when you filter.

Rule #7: Use consistent formulas

Mixing formulas and values in the same column compromises the integrity of the spreadsheet. Suppose for example that you have one column for a quantity, another for the unit price, and a third for the calculated extended price (quantity * unit price). Now consider the chaos that will occur if in the extended price column you override one, two, or many of the formulas by typing in a value. For those rows the extended price will no longer change as users update the respective quantities and unit prices. Therefore, for consistency and integrity’s sake, don’t mix formulas and values in the same column.

Figure 11: Don’t mix and match values in a formulas column.

Rule #8: Use consistent formatting

Consistent formatting in a column makes the data easier to read and understand. As shown in Figure 12, columns with an inconsistent number of decimal places, different alignments, date formats, and/or fonts and font sizes are hard to read.

Figure 12: Inconsistent formats are hard to read

Rule #9: Put comments in their own column or use the comments feature

Comments should not be mixed in the same cells with the data. Doing so, as shown in Figure 13, will make it difficult to sort and filter the data. You can however place comments in a separate “comments” or “notes” column, see figure G1.

Figure 13: Don’t mix comments with data

Another alternative is the program’s comment feature . This feature attaches a comment to the cell but doesn’t actually insert the comment’s text into the cell. See Figure 14.

Figure 14: Comments should be in their own column

Rule #10: Use the “Format as Table” feature

The “Format as Table” feature that comes with Excel 2007 and above makes viewing, sorting and filtering your Excel database easy. See Figure 15. The shading of every other rows makes it easier for viewers to stay on the same record as their eyes scan across the columns. The drop downs included with the column headings allow the user to quickly sort and filter by the selected column.

Figure 15: Data formatted as a table

Do the following to apply the table format feature in Excel 2007/2010 for windows:

• Click on any one data cell within your data block
• On the ribbon’s home tab click the Format as Table button, and then choose the style you prefer.