Tech Trends

Best Practices for Managing Lists in Excel

Written by David Ringstrom

As I’ve written in the past, Excel is full of features that can quickly transform a mind-boggling list of data into instant reports. However, users are often unaware of nuances in Excel that can make or break how features work. Furthermore, if you’re exporting reports from QuickBooks to Excel you’ll often find yourself at a disadvantage. QuickBooks reports often land in Excel in a print-friendly format rather than an analytical format. However, you can overcome some of the challenges by exporting reports to a comma-separated value (CSV) file instead of directly to Excel.

Anchoring the List

The flexibility of Excel in allowing you to type data anywhere you like can be a blessing and a curse. The blessing is that you can make a spreadsheet look any way that you like, but the curse is that in doing so you may inadvertently make Excel harder to use. When working with any type of data set in Excel, it’s best to treat your worksheet as if it’s a table within a database. As shown in Figure 1, a table in Microsoft Access has a single row of headings that are referred to as field names, and then consistent columns of data. The equivalent in Excel is to add a title to the top of each column of your list that fits within a single cell. If the title is too long to display within a single column, resist the urge to simply insert new rows, and fit the text within one cell instead:

  • The most common approach to achieve this is to click the Wrap Text feature on the Home tab in Excel’s menu known as the Ribbon.
  • A less well-known approach is to type some text, and then press Alt-Enter to force a line break within the cell.
Eliminate Blank Rows

Figure 1: Excel’s data analysis features work best with lists that emulate database tables.

Eliminate Blank Rows

Many users realize the importance of eliminating blank rows from within a list of data. However, as I’ve written previously, it’s also important to position a blank row or the worksheet frame above your data. If the report you’ve exported from QuickBooks contains any blank rows, you can quickly remove them by using the techniques shown in Figure 2.

  1. Click the top left-hand corner of Excel’s worksheet frame to select the entire worksheet.
  2. Click on the Data menu in Excel.
  3. Click Filter (or press Ctrl-L in lieu of steps 2 and 3).
  4. Click the filter arrow in a column that contains account names or other data that appears on every row.
  5. In Excel 2010 and later press a single left parenthesis in the Search box to display the blank rows. If your column contains data that has parentheses in the text, or you’re using Excel 2007, then uncheck the Select All box and then scroll to the bottom of the list and check the Blanks box.
  6. Click OK to display the blank rows within your data set.
  7. Use the worksheet frame to select all of the blank rows.
  8. Press Ctrl-Minus to delete the selected rows. Alternatively, you can click the Delete command on Excel’s Home tab.
  9. Click the Filter command to display the remainder of the list (or press Ctrl-L again).
Eliminate Blank Rows

Figure 2: Always eliminate blank rows from reports to head off unexpected issues with Excel’s analytical features.

Eliminate Blank Cells

Now that you’ve eliminated blank rows, it’s time to turn our focus to blank cells. On certain reports, QuickBooks will list an account name on the first row of a section but leave the remaining cells in that column blank, as shown in Figure 3. These blank cells prevent you from being able to sort or filter the list effectively and also prevent you from analyzing the information with a pivot table. Sometimes users spend inordinate amounts of time manually copying and pasting data to fill the blank cells. Users with more experience in Excel may fill in columns by writing formulas out to the side that rely on Excel’s IF function. They can then copy and paste as values over the column with blank cells. As illustrated in Figures 3 and 4, most Excel users are unaware of an even easier approach:

  1. Click on any cell within your list of data and press Ctrl-A to select all cells. This saves you from selecting all of the cells within your list by hand.
  2. On the Home tab of Excel, select Find and Select, and then Go To Special. Alternatively, press Ctrl-G or press F5 to display the Go To window, and then click the Special button.
  3. Double-click on Blanks. Doing so will select all of the blank cells within your list and close the Go To Special window simultaneously, which obviates the need to click the OK. You can use this double-click trick to get through window-related tasks a little faster.
  4. At this point, any blank cells in your list should be selected. If you encounter a prompt that says “No cells were found” then you may have missed one of the preceding steps, or your list doesn’t have any blank cells.
  5. To fill the blank cells within your list, press the equal sign, press the Up arrow, and then press Ctrl-Enter. The Ctrl key is critical here, since if you press the Enter key you’ll only fill a single cell. Holding down the Ctrl and Enter keys together instructs Excel to put the formula you typed in all of the cells that you selected. If you slip up and forget the Ctrl key, simply type the equal sign again, tap the Up arrow key, and then press Ctrl-Enter.
  6. At this point, all blank cells should be filled. Although you could leave the formulas in place, it’s best to convert them to values. The first step is to tap the Up arrow key again so that only a single cell is selected.
  7. Press Ctrl-A again to highlight the entire list.
  8. Press Ctrl-C or click the Copy command on Excel’s Home tab.
  9. Right-click any cell and click the 123 icon that appears in Excel 2010 and later to paste as values. In Excel 2007 and later, choose Paste Special and then double-click Values.

At this point even the most troublesome report format is ready for analysis. In Excel 2013 and later when you press Ctrl-A to select the entire list, an icon appears in the bottom-right corner of your list. Click this to display the Quick Analysis feature that will give you a jump start on using several data visualization tools.

Eliminate Blank Rows

Figure 3: Use Excel’s Go To Special feature to select all of the blank cells within your list.

 

Eliminate Blank Rows

Figure 4: Make sure to press Ctrl-Enter to copy the formula to all of the blank cells.

 

About the author

David Ringstrom

David Ringstrom, CPA, is the president of Accounting Advisors, Inc., an Atlanta-based spreadsheet and database consulting firm he started in 1991. Throughout his career, David has spoken at conferences on Excel, and he currently leads dozens of webinars each year on Excel, QuickBooks, and other software. He has served as the technical editor for over 25 books, including several editions in Wiley’s QuickBooks for Dummies and Quicken for Dummies series. In addition to writing for QuickBooks and Beyond, David is the Tech Editor at Large for AccountingWEB and Going Concern. He also offers live webcasts and self-study courses through CPE Link. His freelance articles on spreadsheets have been published as far afield as Pakistan. During training sessions, you’ll often hear David state, “Either you work Excel, or it works you!”