Use this Excel quick fill handle trick to insert partial rows and columns

Out of necessity, you probably learned early on how to insert rows and columns. Both are common tasks if you maintain Excel data and fortunately, you can choose between several methods. Today, we’ll review the methods most users know. Then, I’ll show you a quick fill handle trick that might be new to you.

The common method

Perhaps the most commonly used method is to choose Insert Sheet Rows from the Insert dropdown in the Cells group on the Home tab (for ribbon versions). Excel will insert an entire row above the selected cell. The Insert Sheet Columns option works similarly, inserting columns to the left of the selected column.

If you click the Insert option, instead of choosing an option from the dropdown, Excel will insert cells (or partial rows) above the selected cells instead of inserting an entire row. If you select A2:A5, Excel will insert four blank rows above row 2. Excel inserts four rows because you selected four rows.

You can also right-click a selection, choose Insert, click the Entire Row (or Entire Column) option, and click OK.

To eliminate a few clicks, select the entire row (or column) by dragging over the header cells before right-clicking; Excel will insert rows without displaying the Insert dialog. If inserting a single row or column, you can eliminate another step by simply right-clicking the row or column’s header cell – no need to select anything first.

If you prefer keyboard shortcuts, use the following steps to insert rows and columns:

1. Press [Shift]+[Spacebar] to select an entire row. Press [Ctrl]+[Spacebar] to select an entire column.
2. Hold down the [shift] key and use the arrow keys to expand the selection.
3. Press [Ctrl]+[Shift]+= to insert the appropriate number of rows or columns. This shortcut will insert cells instead of entire rows and columns, if you don’t select an entire row or column first.

Quick fill

You’re probably familiar with some, if not all of the above methods. Now, let’s look at one more method – the one using the fill handle:

1. Select the cells above the area where you want to insert a partial row or rows. For instance, to insert a (partial) row between the last two records, you’d select the record in row 4. Be sure to select all of the contiguous cells – A4:D4.
2. Hold down the [Shift] key.
3. Pull the fill handle down the same number of rows you want to insert. If you’re insert three rows, pull it down three rows. Excel will insert three rows between the last two rows.

This method is quick and easy, but it works differently. Specficially, Excel inserts rows below and to the right of the selected row or column, respectively. This behavior is in keeping with the fill handle in general. This method also inserts partial rows (or cells) instead of entire rows and columns. It isn’t superior to any of the other methods, it’s just one more way to get your work done!