7 Quick Ways to Insert a Row Within a Cell in Excel

To input new data there are very least alternatives without inserting a row within a cell in Excel. Also in MS Excel, we need to insert row and column in a cell frequently. To Insert a row within a cell in Excel is the most common feature. We will show you 7 different ways to insert a row within a cell in Excel.


📁 Download Excel File

To practice please download the Excel file from here:


Learn to Insert Row Within a Cell in Excel with These Ways

To insert a new row within a cell in Excel, firstly let us consider a workbook. We have a data set of approximately 11 rows and 5 rows. Inputting Student Names, and their marks in Physics, Math, and Chemistry we tried our best to guide you. Once you get our concept, you may develop a workbook on your own.

Sample dataset to insert a row within a cell in Excel

Method

1. Using Top Ribbon to Insert a Row Within a Cell in Excel

The easiest approach is to insert a new row using the Top ribbon in Excel. Here we will show you 3 approaches using Top Ribbon.

Using Top Ribbon

1.1 Creating New Row Using Cell

You can insert a new row by selecting a random cell. Follow the steps below:

⬇️⬇️ STEPS ⬇️⬇️

  • Firstly select a cell i.e. B6.
  • Then click the Home tab from the top Ribbon.
  • After that, click on the Insert menu and select Insert Sheet Rows.

Insert Sheet Row Within a Cell in Excel selecting cell

  • Therefore, a new row is inserted at the 6th row and all the data shift downward.

Inserted a row within a cell in excel

Using Top Ribbon

1.2 Creating New Row Using Row 

Similarly, by selecting a row, you can also insert a new row. Please follow the steps below.

⬇️⬇️ STEPS ⬇️⬇️

  • Selecting Row 8, click on Home from Top Ribbon.
  • Then click Insert.
  • After that click on Insert Sheet Rows.

Insert a row within a cell in Excel selecting a row

  • A new row is inserted on the 8th.

Inserted a row within a cell in Excel.

Using Top Ribbon

1.3 Creating Multiple Sheet Rows Using Cell

Also by selecting multiple random cells you can add the same number of blank rows. Please follow the steps.

⬇️⬇️ STEPS ⬇️⬇️

  • First Click B7 to B9.
  • Click Home from the Top Ribbon and click Insert.
  • Thirdly, click Insert Sheet Rows.

Insert a row within a cell in excel clicking multiple cells

  • Therefore, I have inserted 3 rows from the 7th row to the 9th.

Inserted 3 rows Within a Cell in Excel


Method

2. Using Context Menu to Insert a Row Within a Cell

The context menu can be used with the mouse. By right click on the mouse, you can open the context menu. Rows can also be inserted with this menu.

Using Context Menu

2.1 Inserting Single  Row 

By selecting a cell and using the context menu single row can be added. Please follow the necessary steps.

⬇️⬇️ STEPS ⬇️⬇️

  • Click a cell i.e. B8.
  • Right-click on the mouse.
  • Click Insert.

Insert a row within a cell in excel clicking a cell

  • Select Entire Row to get a new row and click OK.

Inserted a row within a cell in excel

  • At the 8th row, I inserted a new row.

Insert a row in excel

Using Context Menu

2.2 Inserting Multiple Rows

To insert multiple rows, you must select multiple cells. Use of the context menu will guide you to add new rows. Please follow the steps.

⬇️⬇️ STEPS ⬇️⬇️

  • Select multiple cells, i.e. B8 to B10.
  • Then right-click on the mouse and click on the Insert option in the context menu.

Insert rows within a cell in excel clicking multiple cells

  • Then select Entire Row and click OK.

Insert multiple rows within a cell in excel clicking multiple cells

  • We got 3 new rows from the 8th row to the 10th.

Insert multiple rows within a cell in excel clicking multiple cells

Method

3. Using Keyboard Shortcut to Insert a Row Within a Cell

Only with the help of a keyboard, can you also insert rows at any position. For this you follow the steps below:

⬇️⬇️ STEPS ⬇️⬇️

  • Select a cell i.e. B8.
  • Press Ctrl+Shift+= to Insert.
  • Select Entire Row and click Ok.

Insert a row within a cell in excel clicking a cell

  • Therefore, I inserted a new row on the 8th.

Insert a row within a cell in excel clicking a cell

Method

4. Using Numerical Keypad Shortcut to Insert a Row Within a Cell

There is another way to insert a row within a cell using the keyboard. Please follow the steps.

⬇️⬇️ STEPS ⬇️⬇️

  • Select a cell i.e. B9.
  • Press Ctrl+Plus from the numerical keypad to get Insert.
  • Select Entire Row and click Ok.

Insert a row within a cell in excel clicking a cell

  • New Row successfully inserted at the 9th.

Inserted a row within a cell in excel clicking a cell

Method

5. Inserting a Blank Row After Every Existing Row

With the help of an additional column and sorting function, we will create a blank row after every existing row. Please follow the steps.

⬇️⬇️ STEPS ⬇️⬇️

  • AT first, Create a helper column with an ascending order(1,2,3….).

Insert a row within a cell in excel with helper column

  • Secondly, Copy the entire helper column data and paste it to the bottom.

Insert a row within a cell in excel with helper column

  • Thirdly, Select the entire data.
  • Go to Data from the Top Ribbon and select Sort.

Insert a row within a cell in excel with helper column

  • Select helper form sort by.
  • Also, select Smallest to Largest and click OK.

Insert a row within a cell in excel with helper column

  • Now delete the marked data.
  • Therefore, you will get a blank row after every existing row.

Inserted a row within a cell in excel with helper column

📕 Read More: 4 Quick Ways to Insert Blank Row After Every Nth Row in Excel

Method

6. Inserting Multiple Rows Between Data

The desired number of rows also can be inserted between the data with the following method. Please follow the steps.

⬇️⬇️ STEPS ⬇️⬇️

  • Select some data, i.e. B6 to E7

Inserting multiple rows

  • Hold the mouse,  press Shift, and drag it to E9.

Inserting multiple rows

  • Another two blank rows were inserted after the first two selected rows.

Inserting multiple rows

Method

7. Using VBA Macro to Insert a Row Within a Cell

We can use VBA Macro code to insert a single row or multiple rows in any position. With the help of the Developer option, you can generate a program of your own.  Please follow the steps.

Using VBA Macro

7.1 Inserting Single Row

With a VBA code, you can also insert a row within a cell in Excel. Follow the steps.

⬇️⬇️ STEPS ⬇️⬇️

  • Check first if your Developer option is available or not.
  • To enable the Developer option, right-click on the mouse at the top Ribbon and select Customize the Ribbon.

Insert a row within a cell using VBA

  • Click on Developer and click on OK.

Insert a row within a cell using VBA

  • Now Developer mode is on.
  • Click on the Visual Basic option.
  • To insert the VBA code, click on Module from Insert.

VBA program to insert a new row.

  • Typing the code, click on Run.

Code

Sub Insert_Row()
Range("B6").EntireRow.Insert
End Sub

Insert a row within a cell using VBA

  • To insert the row at the 6th row, we selected the B6 cell.
  • According to our cell selection, the entire row was inserted on the 6th.

New row inserted.

Using VBA Macro

7.2 Inserting Multiple Rows

With a VBA code, you can also insert multiple rows within cells in Excel. Follow the steps.

⬇️⬇️ STEPS ⬇️⬇️

  • Also If we want to insert multiple rows we need to define the range.
  • Selecting B7 to B10, we defined inserting the row from the 7th row to the 11th.

Code

Sub Insert_Row()
Range("B7:B10").EntireRow.Insert
End Sub

Insert multiple row within a cell using VBA

  • Click Run and get new blank rows from the 7th to the 10th.

Inserted 4 new rows.

Using VBA Macro

7.3 Inserting Any no of Rows at Any Cell

You can insert any number of rows using the VBA code. Follow the steps.

⬇️⬇️ STEPS ⬇️⬇️

  • Following the same, insert the code to insert multiple rows.

Code

Sub insert_multiplerow()
Dim p As Long
Dim q As Long
Dim z As Long
q = InputBox(Prompt:="Number of Rows You Want to Insert?")
p = InputBox(Prompt:="Row Number After Which You Want to Insert")
For z = 1 To q
    Rows(p).EntireRow.Insert
Next z
End Sub
  • Click run to execute the program.

Inserting multiple rows in any position

  • Insert the no of rows you want and click OK.

Insert a row within a cell using VBA

  • Insert the row number after you want to insert it and click OK.

  • Finally, you will get the blank rows on your desired position.

Inserted rows using VBA


How to Insert a Cell Within a Cell in Excel

Also, you can insert a cell within a cell using these methods. Using Top Ribbon approaches or Keyboard shortcuts, it can be done. Once you get the Insert menu from the Context box, a New cell can be inserted by clicking Shift cells down. But be careful. While inserting a particular cell, all the data will go downward.


How to Insert Rows in a Table If More Data Exist on the Right of the Table in Excel

We can also insert rows in a table without shifting the data downwards if more data exist on the right. Please follow the steps.

⬇️⬇️ STEPS ⬇️⬇️

  • Select the column you want to shift, i.e. E5 to E11.
  • Press  Ctrl+plus (Num Lock) to get Insert

Insert rows in a table

  • Select Entire Column and click on OK.

Insert rows in a table

  • Now select the Cells i.e. B7 to D8.
  • Press Shift+Plus to get the Insert context menu.

Insert rows in a table

  • Select Shifts Cells Down and click on OK.

Insert rows in a table

  • Finally, you will get blank cells at your desired position.

Insert rows in a table

In this approach, we showed how to insert rows in a specific position without shifting the entire row.


📄 Important Notes

🖊️  Be careful and measure correctly while inserting a row. Sometimes it may insert in the wrong position.

🖊️  Enable Num Lock first when you use the Keyboard shortcut.

🖊️  During working with VBA code, Check first whether your Developer tool is available or not.


📝 Takeaway from This Article

📌  Now you will be able to insert rows correctly in any position using these amazing approaches.

📌  Columns can also be inserted using these methods.


Conclusion

We tried every possible way to show you how a row within a cell in Excel can be inserted. I hope you enjoyed your learning. For better understanding and new learning don’t forget to visit www.ExelDen.com.


Related Article

(Visited 120 times, 1 visits today)
MD Tanvir Rahman

MD Tanvir Rahman

Hello, I am Tanvir. Graduated from BUET. Had a year-long Experience at Operation of Vitacan Industries Ltd. I found Excel is the most unique software to reduce both time and paper. Now, I am an Excel enthusiast, love to play with data and publish content.

We will be happy to hear your thoughts

Leave a reply

ExcelDen
Logo