By David Ringstrom, CPA
Data within Excel spreadsheets is commonly organized in columns, with explanatory titles at the top of each section. When carrying out this most basic of data entry tasks, many Excel users often unwittingly cause Excel to be harder to use. Whenever column headings within a worksheet span two or more rows, a cascade of issues can occur. Fortunately, a simple technique can help you avoid frustration and save time when working in Microsoft Excel.
Excel spreadsheets should generally be constructed in the style of a database table, where the first row contains the headings for each column and then the associated data is in a contiguous block of cells. However, users frequently opt for form over function, as shown in Figure 1, where the headings span rows 1 through 3. If you were to sort this data, you may find that the second and third heading rows will get sorted into the body of your data:
- Excel 2007 and later: Click on cell A1 and then choose the Sort command on the Data tab. Click the checkbox for "My data has headers" and then sort based on the Product column, as shown in Figure 2.
- Excel 2003 and earlier: Click on cell A1, choose Data, and then Sort. Double-click on Header Row to sort the data based on the Product column.
Figure 1: Spanning headings across multiple rows can trigger unintended consequences in Excel.
Figure 2: The My Data Has Headers option only treats the first row as headings.
No matter which version of Excel you're using, you'll see that the second and third rows of headings get sifted into the data rows. You can counter this by selecting just the data rows before you sort, but this adds extra steps to the process.
Alternatively, if you arrange your spreadsheet as shown in Figure 3, you can easily sort the data in any version of Excel without issue. To do so, type the headings in a single cell and then use the Wrap Text command:
- Excel 2007 and later: Select the cells that contain headings and then click the Wrap Text command on the Home tab of the ribbon.
- Excel 2003 and earlier: Select the cells that contain headings and then press Ctrl-1 to display the Format Cells dialog box. Choose Wrap Text on the Alignment tab and then click OK.
Figure 3: For best results, place titles in a single row and use the Wrap Text feature.
Utilizing the Wrap Text feature in Excel also makes many other features work better as well. For instance, the Filter command (known as AutoFilter in Excel 2003 and earlier), expects a single heading row, as do the Pivot Table and Table features. The Table feature is known as the List feature in Excel 2003 and earlier.