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.
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.
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.
- Therefore, a new row is inserted at the 6th row and all the data shift downward.
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.
- A new row is inserted on the 8th.
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.
- Therefore, I have inserted 3 rows from the 7th row to the 9th.
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.
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.
- Select Entire Row to get a new row and click OK.
- At the 8th row, I inserted a new row.
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.
- Then select Entire Row and click OK.
- We got 3 new rows from the 8th row to the 10th.
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.
- Therefore, I inserted a new row on the 8th.
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.
- New Row successfully inserted at the 9th.
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….).
- Secondly, Copy the entire helper column data and paste it to the bottom.
- Thirdly, Select the entire data.
- Go to Data from the Top Ribbon and select Sort.
- Select helper form sort by.
- Also, select Smallest to Largest and click OK.
- Now delete the marked data.
- Therefore, you will get a blank row after every existing row.
📕 Read More: 4 Quick Ways to Insert Blank Row After Every Nth Row in Excel
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
- Hold the mouse, press Shift, and drag it to E9.
- Another two blank rows were inserted after the first two selected rows.
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.
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.
- Click on Developer and click on OK.
- Now Developer mode is on.
- Click on the Visual Basic option.
- To insert the VBA code, click on Module from Insert.
- Typing the code, click on Run.
Code
- 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.
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
- Click Run and get new blank rows from the 7th to the 10th.
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.
- Insert the no of rows you want and click OK.
- Insert the row number after you want to insert it and click OK.
- Finally, you will get the blank rows on your desired position.
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
- Select Entire Column and click on OK.
- Now select the Cells i.e. B7 to D8.
- Press Shift+Plus to get the Insert context menu.
- Select Shifts Cells Down and click on OK.
- Finally, you will get blank cells at your desired position.
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.