Home > Structure > Worksheets > Insert

Excel Insert Rows or Cells

Debra Dalgleish - Contextures

One of my clients wanted a new section in his Excel worksheet, so he inserted a few blank rows -- one at a time!

My client loves to learn new Excel tricks, so I showed him the quick way to insert multiple blank rows, all at once! You can see that time-saving trick, in the short videos and written steps below.

insert blank rows across entire worksheet

A) Quick Keyboard Shortcuts 🖮

There are mouse shortcuts in the next section, but do you prefer keyboard shortcuts?

If so, you can use the following keyboard shortcuts, and there are detailed notes in the Keyboard Shortcuts section, further down the page.

Press these keys To do this
Shift + Spacebar Select an entire row
Ctrl + Spacebar Select an entire column
Ctrl + Plus Sign (+) Open the Insert dialog box
Ctrl + Minus Sign (-) Open the Delete dialog box

Tip: You can find more keyboard shortcuts on my Excel Shortcuts List page.

B) Insert Blank Rows - Mouse 🖰

If you love mouse shortcuts, this short video is for you!

You'll see how to insert a single row in Excel, and then insert multiple rows. There are written steps below the video.

Insert Blank Rows - Mouse Right-Click

You can quickly insert one or more rows in Excel by using a mouse shortcut.

The steps are slightly different, based on where you're inserting the rows:

-- 1) Normal Worksheet Rows (not in a Named Table)

-- 2) Named Excel Table

1 a) Insert One Row - Worksheet

To insert one row, across the entire worksheet, follow the steps below:

  • At the left side of the Excel sheet, right-click on the row button, directly below where you want the blank row inserted
    • In the screen shot below, I right-clicked the button for row 6
  • In the pop-up menu, click the Insert command

right-click menu and insert command

Excel inserts one blank row, above the selected row, across the entire worksheet.

insert blank row across entire worksheet

1b) Insert Multiple Rows - Worksheet

If you want to insert two or more rows, across the entire worksheet, the steps are almost the same as inserting one row.

Select Multiple Rows

The key step is to start by selecting the number of rows that you want to insert.

For example, if you want to insert 4 blank rows, start by selecting 4 rows:

  • At the left side of the worksheet, point to the row button, directly below where you want the blank rows inserted
  • Press the left mouse button, and drag down, to select 4 rows
    • Tip: The pop-up tooltip shows how many rows you've selected (4R)
    • right-click menu and insert command

  • Next, right-click on any of the selected row buttons
  • In the right-click pop-up menu, click the Insert command

Excel inserts 4 blank rows, above the selected rows, across the entire worksheet.

insert blank rows across entire worksheet

2a) Insert One Row - Named Table

Instead of inserting rows across the entire worksheet, you can

To insert one row, inside a named Excel table, follow the steps below:

  • In the named table, right-click on one cell, in the row directly below where you want the blank row inserted
  • In the right-click pop-up menu, point to the Insert command
  • In the sub-menu, click the Table Rows Above command

right-click menu and insert command excel table

A blank row is inserted above the selected row, across the entire named table.

  • Note: Any other worksheet data, in the same row as the inserted table row, is not affected. Data below the named table will be pushed down

insert blank row across named table

2b) Insert Multiple Rows - Named Table

To insert multiple rows, inside a named Excel table, follow the steps below:

  • In the table, select one or more cell ranges, in the rows directly below where you want the blank rows inserted
    • In the screen shot below, I've selected 3 groups of cells
      1. B5:C5 - - one table row will be inserted above those cells
      2. D7 - - one table row will be inserted above those cells
      3. B8:B9 - - two table rows will be inserted above those cells

right-click menu and insert command excel table

  • Next, right-click on any of the selected cells
  • In the right-click pop-up menu, point to the Insert command
  • In the sub-menu, click the Table Rows Above command

Blank rows are inserted above the selected cell ranges, across the entire named table.

  • Note: Any other worksheet data, in the same rows as the inserted table rows, is not affected. Data below the named table will be pushed down

insert blank rows across named table

C) Insert Blank Cells - Mouse 🖰

In some Excel files, you might need to insert or delete a block of cells, instead of an entire rows. For example, in the screen shot below, I inserted:

  • blank cells in columns A to F
  • no blank cells in any other columns

multiple blank rows inserted with mouse shortcut

In the video below, I show how you can insert a block of blank cells. There are written steps below the video.

Insert Blank Cells - Mouse Shortcut

To insert multiple blank cells on a worksheet, follow the steps below:

  • First, select the range of cells where you'd like to insert blank cells
  • In the screen shot below, cells A5:F5 are selected

select a range of cells

  • Next, point to the Fill Handle (the small back square), at the bottom right corner of the selected range
  • When the pointer changes to a large black plus sign, press the Shift key
    • The pointer will change to a two-headed arrow, with a split bar at its centre (shown in the screen shot below)
  • Drag down with the split pointer, over the cells where you want to insert blanks

insert multiple rows with shift and drag mouse shortcut

  • When you've finished dragging, release the mouse button
    • Blank cells are inserted, below the original range
    • Existing data is pushed down, below the inserted rows

multiple blank rows inserted with mouse shortcut

D) Insert Rows or Cells - Keyboard 🖮

If you'd rather keep your hands on the keys while working in Excel, you can use keyboard shortcuts to select and insert rows or cells in Excel.

The results are different based where the selected cells are located:

  • 1) Normal Worksheet Cells (not in a Named Table)
  • 2) Inside a Named Excel Table

1) Normal Worksheet Cells (not in a Named Table)

You cs the following keyboard shortcuts to select, insert or delete cells on an Excel worksheet.

Press these keys To do this
Shift + Spacebar Select entire worksheet row
Ctrl + Spacebar Select entire worksheet column
Ctrl + Plus Sign (+) Open the Insert dialog box
Ctrl + Minus Sign (-) Open the Delete dialog box

2) Named Excel Table Cells

You can use the following keyboard shortcuts to select or insert blank rows and cells in a named Excel table.

Press these keys To do this
Shift + Spacebar Select entire named table row
Ctrl + Spacebar Select entire named table column
Ctrl + Plus Sign (+) Insert named table columns or rows

Insert Table Columns

If you want to insert table columns, select a tall range of cells -- with more rows than columns. For example, in the screen shot below:

  • There are 3 rows and only 2 columns selected (Tall)
  • As a result, 2 columns are inserted to the left of the selected range
    • tall selected range, columns inserted

Insert Table Rows

If you want to insert table rows, select a wide or square range of cells:

  • more columns than rows (Wide)
  • or, an equal number of columns and rows (Square)

For example, in the screen shot below:

  • There are 2 rows and 2 columns selected (Square)
  • As a result, 2 rows are inserted above the selected range
    • square selected range, rows inserted

E) Get the Sample File

Get the Insert Rows and Cells sample file , to test the shortcuts. The zipped file is in xlsx format, and does not contain any macros.

More Excel Tips

Excel Shortcuts List

Paste, Skip Blanks

Copy Number Cells Only

Fix Copy & Paste Problems

Line Breaks, Add, Remove

Fill Blank Cells

AutoFill Examples

 

 

Last updated: April 13, 2024 11:43 AM