What Is Add Rows In Excel?
Add rows in Excel enables one to insert one or more adjacent and non-adjacent rows in a dataset. And we can hide, unhide, and move rows to manipulate the data they contain.
Users can insert rows in Excel when they must add more data to the dataset, whether at the beginning or between the existing rows of data.
For example, the image below shows a dataset containing a list of students and their test scores in various subjects.
And the requirement is to insert two students’ data above row 10.
In such a scenario, we do not have to apply an add rows in Excel formula. Instead, we can use the Insert Sheet Rows option under the Insert option in the Home tab to meet the requirement.
While pressing the mouse’s left button, click the rows 10 and 11 headers to select the entire rows. Otherwise, we can choose a cell in rows 10 and 11 while pressing the mouse’s left button. And then, click the Home tab – Insert option – Insert Sheet Rows option to automatically add rows in Excel.
Excel will add two new rows before the first chosen row or cell. And we will see two empty rows, 10 and 11, inserted atop the original row 10.
Finally, we can update the required students’ data in the newly added rows 10 and 11.
Table of contents
Key Takeaways
- The Add rows in Excel helps users to insert one or multiple contiguous and non-contiguous rows. And if we add a row before the first row in the dataset, the new row will not have any format. But if we add a row before a row within the dataset range, the newly added row will have the same format as the row above it.
- Users can insert rows in Excel to add new or additional data to the given dataset.
- We can add rows in a worksheet using the Insert Sheet Rows option under the Insert option in the Home tab. Otherwise, we can use the Insert option from the contextual menu or the shortcuts Ctrl + Shift + + or Alt + I + R.
- Also, we can hide, unhide, and shift rows to manage them in a worksheet.
How To Add/Insert Rows In Excel?
The steps to add or insert rows in Excel are:
- Click the row header to choose the entire row or a cell in the row atop which we must add a row.
- Choose the Home tab – Insert drop-down – Insert Sheet Rows option.
And if we must add multiple contiguous rows above a row. Then, choose the same count of rows as those required to insert, starting from the row atop which we need to add rows. And then, follow step 2 to complete the action.
But if we must add multiple non-adjacent rows across a worksheet. Then, click the first-row header or a cell, press Ctrl, and click the row headers to choose the corresponding entire rows or a cell in each row, on top of which we must add a row. And then, apply step 2 to complete the action.
However, we can’t add rows in Excel if the sheet or workbook is protected, as the above option and alternative method to insert rows in Excel will be greyed out.
Excel VBA – All in One Courses Bundle (35+ Hours of Video Tutorials)
If you want to learn Excel and VBA professionally, then Excel VBA All in One Courses Bundle (35+ hours) is the perfect solution. Whether you’re a beginner or an experienced user, this bundle covers it all – from Basic Excel to Advanced Excel, Macros, Power Query, and VBA.
Examples
Check out the following examples for understanding how to add and manage rows in Excel.
Example #1 – Add Rows In Excel
The below image shows a grocery list including the items’ quantity and cost details.
And the requirement is to add another item to the list before row 7 data.
Then, as we do not have an add rows in Excel formula, we can use the Insert Sheet Rows option from the Home tab to achieve the desired outcome.
Step 1: Click the row 7 header, or choose a cell in row 7 and press Shift + Space, to choose the entire row 7. Otherwise, select a row 7 cell.
Step 2: Choose Home – Insert – Insert Sheet Rows.
We will instantly see an empty row inserted before the chosen row as the new row 7. And the row originally containing the item Orange data shifts one step down and becomes the new row 8, with all the following rows shifting down by one step.
Step 3: Update the empty row 7 with the required item data.
Next, consider we must add two rows before row 7.
Then, while pressing the mouse’s left button, drag the cursor across rows 7 and 8 to select the two rows entirely. And then, use the Insert Sheet Rows option to add two rows before row 7, as explained in Step 2.
And then, we can update the required data in the newly-inserted rows to complete the dataset.
Furthermore, we can add non-contiguous rows in Excel.
For instance, if we click the row 7 header, press Ctrl and the row 8 header, and use the above Insert Sheet Rows option. Then, we will see a row added before each original row, 7 and 8.
Alternatively, we can select a cell in row 7, press Ctrl, and select a cell in row 8. And then, clicking the Insert Sheet Rows option from the Home tab will automatically add rows in Excel that are non-adjacent.
We can then update the required items’ data in the empty non-adjacent rows.
Furthermore, we can use the above steps to automatically add rows in Excel table.
Example #2 – Alternate Method To Insert Rows In Excel
The image below shows a dataset containing a list of employees, their designations, and their annual appraisal status.
And the requirement is to add a row of employee data before row 5. Then, we can use the alternate method to add a row in Excel, the Insert option from the contextual menu.
Step 1: Right-click the row 5 header to choose the entire row 5 and access the contextual menu. And select the Insert option in the contextual menu.
[Alternatively, we can right-click a cell in row 5 to access the contextual menu. And then, select the Insert option in the contextual menu.
The Insert window will open.
And we must choose the Entire row option and click OK in the Insert window to insert a row in the desired position]
The above step will add an empty row before the chosen row.
Step 2: Update the required employee data in the newly added empty row.
Next, we require to add two adjacent rows above row 5. Then, while pressing the mouse’s left button, drag the cursor over the row 5 and 6 headers to choose the two rows entirely. And then, right-click to select the Insert option from the contextual menu.
The above action will insert two adjacent rows above row 5.
[Alternatively, while pressing the mouse’s left button, drag the mouse over cells in rows 5 and 6 to select them. And then, right-click to select the Insert option from the contextual menu.
The Insert window will open.
We must choose the Entire row option and click OK in the Insert window to add two adjacent rows above row 5]
Next, let us see the steps to automatically add rows in Excel table using the alternate method.
Considering the above example, select the cell range A1:C11 and choose the Insert tab – Table option or use the shortcut Ctrl + T to open the Create Table window.
Ensure the mentioned cell range is correct, and the table headers option is checked in the Create Table.
Click OK in the Create Table window to view the following Excel table.
Next, if the requirement is to insert two rows above row 4. Then, while pressing the mouse’s left button, drag the cursor over the row 4 and 5 headers to choose the two rows entirely. Otherwise, while pressing the mouse’s left button, drag the cursor over rows 4 and 5 cells to choose them.
And then, right-click to select the Insert option from the contextual menu.
The above action will insert two new rows before the original row 4.
Next, consider the requirement to add three non-adjacent rows, one each before rows 4, 7, and 9. Then, in such a scenario, we can select a cell in row 4, press Ctrl, and click a cell in rows 7 and 9 to choose them. Otherwise, we can click the row 4 header, press Ctrl, and then click the row 7 and 9 headers to select the three rows entirely.
And then, right-click to choose Insert – Table Rows Above from the context menu.
The above step will insert three empty rows, as depicted below.
On the other hand, if we must add a row before the last row in the Excel Table. Then, we can right-click a cell in the last row and click Insert in the contextual menu.
However, we will get an additional option, in this case, enabling us to add a row below the last row.
Example #3 – Hide And Unhide Rows In Excel
The table below lists subjects taught at a school and their pass percentage data during 2020-22.
If the requirement is to hide rows 3 to 5. Then, the steps are as follows:
Step 1: While pressing the mouse’s left button, drag the cursor over row 3:5 headers to choose the entire rows. Otherwise, click the row 3 header, press Ctrl and click the row 4 and 5 headers to select the entire rows. And then, choose Home – Format – Hide & Unhide – Hide Rows.
[Alternatively, we can choose a cell in row 3, press Ctrl and select one in rows 4 and 5. Then choose Home – Format – Hide & Unhide – Hide Rows.
Otherwise, while pressing the mouse’s left button, drag the cursor over rows 3:5 headers to choose the entire rows. And then, right-click to select Hide from the context menu.
Further, we can also select cells in rows 3:5 or select the whole rows 3:5 and press Ctrl + 9 to hide the three rows]
Next, consider we must unhide rows 3:5. Then the process is:
Step 1: While pressing the mouse’s left button, drag the mouse cursor over rows 2 to 6 headers to choose the entire range.
And then, select Home – Format – Hide & Unhide – Unhide Rows.
[Alternatively, while pressing the mouse’s left button, drag the mouse cursor over rows 2 to 6 headers to select the entire row range. And then, right-click over the chosen rows and choose Unhide from the contextual menu.
Otherwise, we can choose the row range, which includes the hidden rows, and press Alt and H, O, U, and O, one after the other or Ctrl + Shift + 9]
The above action will unhide the hidden rows.
The above steps will also work for hiding and unhiding non-contiguous rows.
Example #4–Move Rows In Excel
The table below lists product codes and their order details.
And the requirement is to move rows 5 and 6 and place them before the current row 3. Then, the steps are as follows:
Step 1: While pressing the mouse’s left button, drag the cursor across the rows 5 and 6 headers to select the entire rows. And then, choose Home – Cut.
[Alternatively, choose the whole rows 5 and 6 and right-click on the chosen row range to select Cut from the context menu.
Otherwise, after choosing whole rows 5 and 6, press Ctrl + X to cut the rows]
The cut rows will appear as depicted below.
Step 2: Click the row 3 header to choose the entire row and select Home – Insert – Insert Cut Cells.
Alternatively, right-click the row 3 header to choose Insert Cut Cells from the contextual menu.
The above steps will move the required rows to the required location.
However, Excel does not allow us to choose multiple row ranges to shift simultaneously. The chosen row range must be single to simultaneously move the required multiple rows.
Shortcut Keys To Add Rows In Excel
The excel shortcuts to insert rows are:
- Click the row header to choose the entire row or a cell in the specific row, atop which we must add a row, and press Alt + I + R.
- Click the row header to choose the entire row, atop which we must insert a row and press Ctrl + Shift + +.
- Click on a cell in the row atop which we must add a row, and press Ctrl + Shift + +. And then, press R and Enter.
- If our last action was to insert rows in a sheet, pressing F4 will repeat the previous action. It, thus, helps us add multiple rows in Excel simultaneously.
The above shortcuts will work when adding multiple adjacent and non-contiguous rows in a worksheet. (Refer to the How to Add/Insert Rows in Excel? Section to know the steps to select adjacent and non-adjacent rows in Excel, required for the rows addition process.)
But, we can’t add rows in Excel using the abovementioned shortcuts if the worksheet is password-protected.
Important Things To Note
- Ensure the sheet where we need to add rows is not password-protected. Otherwise, the options and shortcuts to add rows in Excel will be greyed out and disabled.
- Ensure we choose the whole row or a cell in the row at the position where we need to add a row to a sheet.
- Consider we want to choose the cells in the rows or entire rows with the count of rows the same as those to insert, and use the Insert option to add adjacent rows in Excel. Then, press the mouse’s left button and select the cells in the required rows or the row headers to select the entire rows.
Otherwise, when we use the Ctrl key to select cells in the required rows or the entire rows, a row will be inserted above each chosen row or row containing a chosen cell.
Frequently Asked Questions
We can add 4 rows after every row in Excel using a helper column.
For example, the table below lists the top smartphones in the US, their brands, and their costs.
And the requirement is to add four rows after every row in the above dataset. Then, here is how to use a helper column to achieve the desired outcome.
Step 1: The dataset range is from columns A to C. So, we will make column D the helper column. And as the dataset contains five rows of data, we shall enter numbers from 1 to 5 in column D cells against the given data.
Furthermore, since we need to add four rows after every row, we enter the numbers 1 to 5 four more times in column D, as shown below.
Step 2: Select a cell in the dataset or the entire dataset, and choose Data – Sort to open the Sort window. Otherwise, press Alt + A + S + S to access the Sort window.
The sorting must be column-wise.
And, since the logic is to sort the Helper column data to arrange the original dataset in a specific order, set the Sort by field as the Helper column.
Further, set the Order field as Smallest to Largest.
Finally, click OK in the Sort window.
Clicking OK will close the Sort window. And we can see the Helper column values sorted in ascending order.
Thus, the empty rows, initially below the dataset, get placed between the rows containing data based on the sorted Helper column data.
We can add rows in Excel when filtered using the Insert option in the context menu.
For example, the table below lists students and their team details.
And each team must contain four students. However, when applying a filter to the given data using the Data – Filter and filtering teams B and C data, we find the two teams do not have four students.
Thus, we can insert three rows, one above each row 6, 7, and 8, for updating two student details for Team B and one for Team C.
Step 1: Select the filtered data range and press F5 to access the Go To window. And click Special in the Go To window to access the Go To Special window.
Step 2: Since we must work with visible data in the filtered rows, choose Visible cells only in the Go To Special window.
And click OK to close the Go To Special window. And all the visible cells in the filtered data appear selected.
The above step will ensure the hidden rows do not get affected.
Step 3: Click the row 6 header and press Ctrl. And then, click the row 7 and 8 headers to choose the three rows entirely. Otherwise, we can select a row 6 cell, press Ctrl, and select a cell in rows 7 and 8.
Next, right-click to choose the Insert Row option from the context menu.
And we will see a row added above each selected row or row cell.
Furthermore, after removing the filter, the dataset containing the newly inserted rows will appear, as shown below.
Finally, we can update the required data in the newly added rows to complete the dataset.
We can add rows in Excel on Mac using the following steps:
1. Click the header of the row before which we need to add a row.
2. Press down CONTROL and click the chosen row. And then, choose Insert in the pop-up menu.
And when the requirement is to add multiple rows, we must choose the same count of rows as those we need to insert. Next, we must follow step 2, mentioned above, but by clicking the chosen rows.
Download Template
This article must be helpful to understand the Add Rows In Excel, with its formula and examples. You can download the template here to use it instantly.
Recommended Articles
This has been a guide to Add Rows In Excel. Here we learn how to add and insert rows/multiple rows along with shortcut keys, examples & downloadable excel template. You may learn more from the following articles –
Leave a Reply