Eliminate a Common Spreadsheet Design Flaw | AccountingWEB

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.

 

 

Wait, there's more!
There's always more at AccountingWEB. We're an active community of financial professionals and journalists who strive to bring you valuable content every day. If you'd like, let us know your interests and we'll send you a few articles every week either in taxation, practice excellence, or just our most popular stories from that week. It's free to sign up and to be a part of our community.
Premium content is currently locked

Editor's Choice

WHAT KIND OF FIRM ARE YOU?
As part of our continued effort to provide valuable resources and insight to our subscribers, we're conducting this brief survey to learn more about your personal experiences in the accounting profession. We will be giving away five $50 Amazon gift cards, and a $250 Amazon gift card to one lucky participant.
This is strictly for internal use and data will not be sold
or shared with any third parties.