How to Insert Multiple Rows in Excel without breaking a sweat!

In this tutorial, we’re going to learn how to insert multiple blank rows (and columns) quickly in an Excel spreadsheet. Excel is really by and far the best data management tool out there. If you’re new to it, we strongly recommend you first try out an easy beginners course to get a feel of Excel. We’ll be using Excel 2010 for the demonstration, but you can do this on Excel 2007 and Excel 2013 too – the process is exactly the same for those versions. The ability to insert multiple rows and columns should help you edit and manage your spreadsheets far better than before.

Inserting a single row in Excel

Let’s start out small, first, before we learn how to insert multiple rows. You can create a sample spreadsheet for the purpose of this exercise (any spreadsheet with some data will do). This is the spreadsheet we’ll be using:

Now, to add a single row, you need to select any cell in the row below the row where you want the new row to be created. You then right click and select insert in the drop down menu that appears. A new small window will pop up. Select “Entire Row” and click Ok. And there you go- your new row has been created.

Sounds complicated and somewhat convoluted?  Let’s work with our example spreadsheet to help you understand better.

Suppose we wanted to create a new blank row between rows 5 and 6 in our example spreadsheet. You can do that by clicking on any cell in row 6, right clicking and choosing the insert option. You then choose add “Entire Row” and click ok. That’s all there is to it:

And there you go- a new blank row has popped up. You can also use the prominent Insert button (Insert Sheet Rows) that is on the Home menu at the top for the same result:

If you wanted to add a row between 3 and 4, you would select a cell in row 4 and insert a new row. If you wanted to add a row between 4 and 5, you would select a cell in 5 and then insert… and so on and so forth.

Inserting a Single Column in Excel

The process is similar if you want to insert a single column in Excel. To insert a blank column, click on a cell in the column to the immediate right of where you want the new column to be placed in. For example, if you wanted a new column to be placed between Column A and Column B, click on any cell in Column B and click Insert, and then select “Entire Column”. Just undo the changes you’ve made to your worksheet (Ctrl + z) and try it out for yourself:

If you wanted to insert a column between column B and column C, you click on any cell in C and choose Insert> Entire Column. A new column will always appear to the immediate left of where you’re clicking. You can also use the Insert button at the top- but this time you choose the Insert Sheet Columns option.

The F4 Shortcut Key

The F4 shortcut key repeats the last action you’ve performed in Excel. It works as a sort of Mini Macro. If you need to insert multiple single rows (or columns) in your spreadsheet at different locations, using the F4 key is much quicker than doing Insert > Entire Row all the time. The only problem is that it’s tedious to keep pressing the F4 key for adding a single row. Do you keep pressing the F4 key 10 times if you wanted to add 10 rows in Excel? No, you don’t!

Inserting Multiple Rows in Excel

Finally, let’s learn how to add multiple rows in excel. We learnt while adding a single new row that it appears immediately above the location we’ve selected. The same thing happens when we add multiple rows. To specify the number of rows that are to be added, you need to select the same number of rows on your spreadsheet. You then right click and then choose Insert> Entire Row.

For example, let’s insert three rows in our example spreadsheet between rows 5 and 6. We will need to select a cell in row 6, because we want our new rows to appear immediately above it. Because we want three rows to appear, we select cells in three rows, starting from 6. So we select cells in 6, 7 and 8, like in the example:

To select multiple cells, just drag and drop. Then right click, click on Insert and choose “Entire Row” for the following result:

If you wanted to add 7 rows, you would choose (by drag and drop) 7 rows:

Adding Multiple Columns in Excel

The process is similar for adding multiple columns in Excel. Just choose cells to the right of where you want your columns to be added and choose the Insert> Entire Columns option. In our spreadsheet example, we’re adding 5 new columns between column B and column C:

Using the F4 Key

What if you wanted to add 20 rows to your spreadsheet? Would you select 20 rows by drag and drop? You could, of course. What if you wanted to add a hundred new rows? It’s possible to do that, but it’s tedious and time consuming. An easy solution would be inserting 10 rows, and then hitting the F4 key 10 times to get a hundred new rows!

Using a Macro for Inserting Multiple Rows

If you are a developer and you know something about Visual Basic, you can create macros to insert multiple rows and columns. In fact, Microsoft has provided code that lets you do just that.  Visual Basic for Applications is built into the Microsoft Office suite. To add macros, you need to hit alt + F8, name a macro and then choose create. Then just copy paste the following code:

Sub Insert_Rows_Loop()
   Dim CurrentSheet As Object
   ' Loop through all selected sheets.
   For Each CurrentSheet In ActiveWindow.SelectedSheets
       ' Insert 5 rows at top of each sheet.
       CurrentSheet.Range("a1:a5").EntireRow.Insert
   Next CurrentSheet
End Sub

Here, “a1” will track the row number below which a new row is to be added and “a5” will specify the number of rows you want added plus one. For example, if you wanted 4 rows to be added between a3 and a4, just replace “a1” with a3 and “a2” with “a3”.

If you’re a casual user, you probably won’t need macros. Just follow the regular drag and drop method.