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

Cybersecurity is no longer the domain of an organization's IT staff. It's moved to the boardroom, and in a big way. Accountants and financial managers may have been thinking it's just the province of the tech...
You probably don't want to think about how many times you access the File menu in Excel 2010 or 2013. Personally I think Excel 2010 has the best possible File menu arrangement, other than having Print Preview grafted...
Following other recent high-profile hacking events, investigators discovered yesterday that hackers broke into the draft work paper files of several famous CPA firms. Revealing images of the scantily clad documents have been...

Already a member? log in here.

Upcoming CPE Webinars

Sep 24
In this jam-packed presentation Excel expert David Ringstrom, CPA will give you a crash-course in creating spreadsheet-based dashboards. A dashboard condenses large amounts of data into a compact space, yet enables the end user to easily drill down into details when warranted.
Sep 30
This webcast will include discussions of important issues in SSARS No. 19 and the current status of proposed changes by the Accounting and Review Services Committee in these statements.
Oct 21
Kristen Rampe will share how to speak and write more effectively by understanding your own and your audience's communication style.
Oct 23
Amber Setter will show the value of leadership assessments as tools for individual and organizational leadership development initiatives.