Eliminate a Common Spreadsheet Design Flaw

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.
 
Read more articles by David Ringstrom. 
 
About the author:

David H. Ringstrom, CPA heads up Accounting Advisors, Inc., an Atlanta-based software and database consulting firm providing training and consulting services nationwide. Contact David at david@acctadv.com or follow him on Twitter. David speaks at conferences about Microsoft Excel, and presents webcasts for several CPE providers, including AccountingWEB partner CPE Link.

 

 

You may like these other stories...

While reputational risk is the No. 1 nonfinancial concern among corporate directors, cybersecurity/IT risk is gaining steam. In fact, both private companies and organizations with more than $1 billion in revenue felt they...
Accountants who specialize in forensic and valuation services point to electronic data analysis, or big data, as the most pressing issue they’ll face in the coming months, according to results of a new survey released...
As complex as federal tax can get, at least you're only dealing with one agency: the IRS. But when you get into state and local sales tax, you're coordinating hundreds of jurisdictions that are constantly changing....

Upcoming CPE Webinars

Jul 31
In this session Excel expert David Ringstrom helps beginners get up to speed in Microsoft Excel. However, even experienced Excel users will learn some new tricks, particularly when David discusses under-utilized aspects of Excel.
Aug 5
This webcast will focus on accounting and disclosure policies for various types of consolidations and business combinations.
Aug 20
In this session we'll review best practices for how to generate interest in your firm’s services.