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...

If you're thinking that bitcoin and digital currency generally are merely some one-off flash in the pan, the new Digital Currency Council would argue otherwise. The council launched in September and already had 400...
Sponsored Content from Confirmation.com: AccountingWEB takes a look at confirmations in audit engagements, how they can serve as a doorway to fraud, and what you can do about it. Today, auditors have to fight fire with...
If your small-business clients are on the fence about cloud accounting, they've got plenty of company. A recent survey by market researcher and consultant Software Advice indicates a decidedly mixed view by respondents...

Already a member? log in here.

Upcoming CPE Webinars

Oct 21Kristen Rampe will share how to speak and write more effectively by understanding your own and your audience’s communication style.
Oct 22This webinar will include discussions of important issues in AU-C 800, Audits of Financial Statements Prepared in Accordance with Special Purpose Frameworks.
Oct 23Amber Setter will show the value of leadership assessments as tools for individual and organizational leadership development initiatives.
Oct 30Many Excel users have a love-hate relationship with workbook links.