The Pros and Cons of Blank Rows in Spreadsheets

By David H. Ringstrom, CPA

Whenever I lead an Excel training class, I always say “Either you work Excel, or it works you.” In this article I’ll discuss a simple design strategy ‑ minimizing the use of blank rows ‑ that can help you work far more efficiently in Excel. This isn’t to say you should avoid blank rows, as I’ll also discuss two instances where blank rows can be helpful. You should, however, use blank rows judiciously ‑ if you’re seeking white space, simply increase your row heights instead. In this article I’ll discuss ways that you can work more efficiently in Excel, and along the way I’ll share a few of my favorite keyboard shortcuts.

Understand the Current Region

The current region is an ever changing area of your spreadsheet that is anchored by the current location of your cursor. This area includes all adjacent cells enclosed by the nearest blank rows and columns, or the worksheet frame. In the case of Figure 1, the cursor is located in cell A1, so the current region is cells A1 through F6. Rows 7 and below are not included in the current region because row 7 is blank. If I move my cursor to cell C11, then my current region would become cells C8 through F12.

Figure 1: The current region is constantly redefined, based on the position of your cursor.

Understanding the current region can help you in several ways:

• You can copy and paste sections of a worksheet without using your mouse.
• Sorting and filtering is easier
• You don’t have to preselect data for a pivot table

In addition, avoiding blank rows makes it far easier to copy formulas down a column in your spreadsheet.

Copy and Paste Data

Figure 2

Figure 3

In Figure 2, I deleted the blank rows from my spreadsheet by using this technique:

1. Click on cell A1.
2. Press Shift-End-Home to select the entire used area of the worksheet.
3. Display only the blank rows:

Excel 2007: Choose Sort & Filter in the Editing section of the Home Ribbon, and then choose Filter. Click any of the drop-down arrows, and then clear the Select All checkbox, and then scroll down and choose Blanks, as shown in Figure 3.

Excel 2003 and earlier: Choose Data, Filter, and then AutoFilter. Choose any of the drop-down arrows, and then choose Blanks from the list.

4. Use the worksheet frame to select the blank rows, and then press Ctrl-Minus (the minus sign on your number pad) to delete the blank rows. You can also click the Delete icon in the Cells section of the Excel 2007 Home ribbon, or choose Edit, and then Delete in earlier versions of Excel.

Laptop tip: You may have to press Ctrl-Fn-Minus (the key above the letter P) on your laptop keyboard.

5. You can now turn off the filter arrows:

Excel 2007: Choose Sort & Filter, and then Filter in the Editing section of the Home ribbon.

Excel 2003 and earlier: Choose Data, Filter, and then AutoFilter.

Now that you’ve eliminated the blank rows from your spreadsheet, you can easily copy and paste this data to another location.

Tutorials: You can learn more about filtering with free tutorials for Excel 2007 and earlier versions of Excel.

Copy and Paste the Current Region

It’s easy to use your mouse to select a few cells, but your mouse sometimes gets a mind of its own when you’re selecting dozens, hundreds, or thousands of rows. Excel 2007 is helpful in this regard, as your mouse automatically slows down as you approach the bottom of the used area of a worksheet. Earlier versions of Excel don’t offer this capability, so it’s easy to overshoot your intended target by hundreds of rows. Regardless, there’s no need to use the mouse at all when your data is arranged in a contiguous block:

1. Select a cell within your list, and press Ctrl-* (the asterisk on your number pad).

Laptop tip: You may need to press Ctrl-Shift-8 on a typical laptop keyboard.

2. Press Ctrl-C to copy the data
3. Move your cursor to a new location, and then press Ctrl-V (or press Enter) to paste the data.

Easier Sorting

Sorting data is much easier when there are no blank rows in your spreadsheet. In the case of the spreadsheet shown in Figure 2, there’s no need to select the data first, as Excel will automatically sort the current region. Conversely, you would need to manually select the rows first if your spreadsheet has blank rows, such as in Figure 1. You can then carry out the sort function:

• Excel 2007: Choose Sort & Filter in the Editing section of the Home ribbon, and then make a choice:
• Sort A to Z
• Sort Z to A
• Custom Sort

The Data ribbon also has a Sort & Filter section with these commands.

Power sorting: As shown in Figure 4, Excel 2007 enables you to sort on up to 64 columns, as well as by color or cell icon. Cell icons are a new conditional formatting feature introduced in Excel 2007.

Figure 4: Excel 2007 offers greatly enhanced sorting capabilities

• Excel 2003 and earlier: Choose Data, and then Sort to carry out a sort command.

Copying Formulas

Many Excel users always use their mouse to drag a formula from one cell down to other cells. However, if you don’t have any blank rows, you can copy formulas with just a double-click of your mouse. To illustrate, I’ll add a Per Case column to the spreadsheet shown in Figure 2:

1. Enter the words Per Case in cell G1.
2. Enter this formula in cell G2:

=F2/E2

3. By default your cursor moves down to cell G3, so press the Up arrow key to return to cell G2, and then double-click the Fill handle. You’re in the correct position when your cursor changes from the white plus-sign symbol to a small black plus-sign, as shown in Figure 5. As shown in Figure 5, Excel automatically copied the formula down to the bottom of the table, in this case through cell G51.

Figure 5: Double-click the Fill Handle to automatically copy formulas down a column

Figure 6: Double-clicking the fill handle in cell G2 copied this formula down through the last row

Trap: The Fill Handle trick is a huge tip saver, but this technique is based on entries in the adjacent column. Excel copies the contents of the current cell down until it finds a blank cell in the preceding column. Thus, if cell F10 were blank in Figure 5, then Excel would only copy the formula down through cell G9. However, it’s easy to double-check your work. After you double-click the fill handle, press Ctrl-Down to jump to the bottom row. This lets you confirm if your formula was copied to the bottom of your table or not. Press Ctrl-Up to return to the top of your table. Ctrl-Left and Ctrl-Right enable you to move to the left or right of your spreadsheet. In any of these cases, the cursor will land on the last non-blank cell in the current contiguous block of cells.

Easier Pivot Tables

As with sorting and filtering, you don’t have to select contiguous data before creating a pivot table. As shown in Figure 7, the pivot table dialog box automatically identifies your current region. If you’re not yet familiar with Pivot Tables, then consider these tutorials for Excel 2007 and earlier versions.

Figure 7: The Pivot Table wizard prefills the Table/Range Field based on the current region

Two Good Reasons to Use Blank Rows

I find it helpful to include a buffer row when I’m summing numbers, so that I don’t have to adjust my formula later. Notice in Figure 8 that the formula extends from row 1 through row 7. If I need to add more data, I can click on row 7 and press Ctrl-+ to add a blank row (laptop users many need to press Ctrl-Shift-=). The SUM function automatically adjusts to include the new row. Conversely, if I didn’t skip a row, and clicked on the Total to add a new row, I would introduce an error into my spreadsheet if I failed to adjust the SUM function.

Figure 8: Include a buffer row to improve the integrity of formulas that use the SUM function

A second reason is when you want to analyze data as you filter it. For instance, in Figure 8 I skipped a row, and then entered this function in cell E53:

=SUBTOTAL(9,E1:E52)

In this context, the SUBTOTAL function only adds the visible rows. Therefore, when I filter the list to only show Apples, I get a total of 48,236 cases sold. This changes to 53,671 when I filter the list for Kiwi. Conversely, the SUM function always adds all rows, hidden or visible, and therefore isn’t of nearly as much benefit for filtering. More information about this capability is available in this Microsoft Knowledgebase article.

Figure 9: Create a simple, yet powerful analysis tool by using the SUBTOTAL function with filtering

 

The views and opinions expressed in this column are those of the author and do not necessarily reflect the opinions of Microsoft.