How To Insert Multiple Rows in Excel: 5 Amazingly Simple Tricks

If you ask anyone about their preferred data crunching and spreadsheet software, chances are they’ll mention Microsoft Excel. Ever since its inception with the Microsoft Office Suite, it has quickly become the industry standard for recording and interpreting data.

Family-owned businesses, e-commerce stores, small to medium enterprises, and even factories of Multinational Companies regularly make use of this powerful software for their diverse needs.

While the software offers a plethora of features and complex techniques, one of the most common operations you are likely to come across is the process of inserting multiple rows in Excel.

There are quite a few ways to do it and the effectiveness of each method depends on your specific use case. This article will detail multiple methods on How To Insert Multiple Rows in Excel to make the job easier for you.

Method 1: Using Menu Options to Insert Rows

This is the most straightforward method that most users can use. There are two steps to this.

  1. Start by dragging your mouse while holding the Control key to select the range of rows depending on the number you want to insert. To insert 4 extra rows, select 4 existing rows. To add 9 extra rows, select 9 existing rows and so on. You can also use the Shift + Space key combination to turn any cell into a row. Then you can press the Shift key in conjunction with the down arrow to select rows further down the sheet. Another neat way to select rows is to use the row numbers to the far left of the cells.
  2. Once the selection has been made, you can go to the Home tab and click on the Insert icon to quickly add the rows above the top selected row. You can also achieve the same result by right-clicking the selected rows and clicking on insert to add the rows. With either method, an insert options icon will appear that you can use to maintain the clear formatting in your excel sheet.

Method 2: Using Keyboard Shortcuts

This is a much faster and more convenient method compared to the first one but it does have a learning curve. Here, you can directly press keys on the keyboard in conjunction to quickly and seamlessly add rows without having to drag your mouse.

  1. Just like the previous method, start by selecting the range above which you want to insert the rows either through the row numbers or using Shift + Space.
  2. Then, use your keyboard to press Alt + I. This begins the initialization process of inserting rows. To perform this operation, simply press R while holding down the Alt button. Your rows should be neatly inserted without much hassle. Another commonly used keyboard shortcut is Alt + H + I + R but serves the same function. This is a wonderful method if you are fast with a keyboard.

Method 3: Using the Numpad

If your keyboard has a Numpad, then this method can be a powerful tool to have in your arsenal. It makes great use of the Numpad’s “plus” button.

  1. Select the range of rows by following the previous steps. 
  2. Press and hold the Control key and tap the Plus key on the Numpad to insert rows.
  3. If your keyboard does not come with a Numpad, you can still make use of this feature by pressing Control + Shift + Plus on the main keyboard layout.

This is a great way to keep in control about the number of rows you are inserting as well as selecting exactly the right number of rows for your given data set.

Method 4: Inserting a Large Number of Rows Using the Name Box

If you are dealing with a heavy data set with large numbers of cells, then this can be a lifesaver. It is often impractical to add rows above one hundred using the methods discussed before and it can be tedious and time-consuming. With this method, you can instantly add your desired number of rows without having to select them beforehand.

  1. Use your mouse to select the cell above which you want to insert the rows. 
  2. Navigate to the Name Box and input the number of rows i.e. the range you want to select. This typically takes the form of “Selected Cell Number: Last Cell Number in the Range”. This means that if you select cell number 6 and want to insert 180 more rows, you have to input “6:186”. Hit Enter after typing the range and Excel will automatically select the rows for you. 
  3. Once the rows have been selected, simply use the Control + Plus or the Alt + H + I + R hotkeys to insert the required number of rows above the selected cell. 

Method 5: Inserting Rows without Changing the Format

Sometimes, inserting rows can ruin the delicately constructed format on your sheet. This is particularly true for rows of data that are to the right of the cell block you want to insert rows in. To circumvent this annoying problem, you just have to create an Excel table.

  1. Select a range of cells and navigate to the Home Tab where you will find a “Format As Table” icon. Click on it and the selected cells will be converted to a table with their unique format independent of all other cells outside the table. You can also use the Control + T shortcut to achieve the same purpose. 
  2. A dialog box will appear which will ask you to specify the range of cells you want to include in the table. 
  3. After the table has been created, you can select any range of cells in the table and use the shortcut Alt + H + I + A to easily insert rows without breaking the format of the rest of the table. Using Control + Plus also has the same function.

Conclusion

With these powerful techniques on How To Insert Multiple Rows in Excel at your disposal, you can make quick work of those large spreadsheets that seem to go on forever.

Or you can drastically simplify the process of adding rows to input more data faster. Either way, we hope you are ready to take your Excel game to the next level.