In this article, I will discuss how you can use an excel formula to insert rows between data. Often working with Microsoft Excel, we need to insert single or multiple rows in our dataset. I will describe two easy methods to add blank rows between excel data.
Download Practice Workbook
You can download the practice workbook that we have used to prepare this article.
2 Examples to Insert Rows between Data with Excel Formula
1. Excel Formula with MOD & ROW Functions to Insert Rows between Data
Sometimes we need to insert blank rows into our data after every N-th row. In such a case, we can use an excel formula easily. For example, I will use the combination of MOD and ROW functions to insert rows between data. Suppose we have the below dataset containing several people’s names and ages.
Now I will insert blank rows after every 3 rows in this dataset. Follow the below steps to perform the task.
- First, add a helper column to the parent dataset and type the below formula in Cell D5. Next press Enter.
- As a result, we will get the below output. Use the Fill Handle (+) tool to copy the formula to the rest of the cells.
- This is the result we receive after applying the Fill Handle tool. Now, select the helper column and press Ctrl + F to bring the Find and Replace dialog box.
- Then from the Find and Replace dialog, enter zero (0) in the Find what box, click on Options, choose Values from the Look in drop-down, and click on Find All.
- Upon clicking on Find All, excel will return the references of cells in the helper column that contains zero. When the result appears, press Ctrl + A to select all of them.
- Once you select the output of the Find and Replace dialog, those cells will be selected in the dataset too. Close the Find and Replace dialog.
- After that, among all of the selected cells containing 0, unselect only the first one. Then, right-click on any of the selected cells and click on Insert.
- As a consequence, the Insert dialog will appear, click on the Entire Row option, and press OK.
- Finally, we can see blank rows are added after every three rows.
🔎 How Does the Formula Work?
Here the ROW function returns the row number of Cell D5 which is:
Now, the ROW function returns the row number of Cell D4:
Lastly, the MOD function returns the remainder when 0 is divided by 3. Here 3 is the value of N. If you want to insert blank rows between every 4 rows, N will be 4. The result is:
Read More: How to Insert Row in Excel ( 5 Methods)
- Excel Fix: Insert Row Option Greyed out (9 Solutions)
- How to Insert Multiple Blank Rows in Excel (4 Easy Ways)
- Move a Row in Excel (6 Methods)
- How to Insert Multiple Rows in Excel (6 Easy Methods)
- How to Insert a Row within a Cell in Excel (3 Simple Ways)
2. Add Blank Rows between Data Using Helper Column in Excel
Sometimes we need to enter blank rows in a dataset when the value changes. Suppose we have a dataset containing fruit names and their order quantities. There are three types of fruits in my dataset and similar types of fruits are written in sequences. But at first glance, you cannot tell immediately at what rows a fruit’s name is changed. But if you can insert a blank row between the changing data, it will be easier to separate each fruit type.
To do the task follow the below steps.
- First, insert a helper column to the parent dataset and type the below formula in Cell D6.
- Once you hit Enter, the following will be the output.
- Then, add another Helper column to the dataset and type the following formula in Cell E7.
- Consequently, we will get the below result.
- Now, select D6:E7 and drag the Fill Handle down, you will get the below result.
- After that, select both Helper 1 and Helper 2 columns and press Ctrl + F to get the Find and Replace dialog.
- When the dialog comes up, type FALSE in the Find what box, choose Values from the Look in drop-down menu, and click on Find All.
- As a result, we will get the cell that contains FALSE. Use Ctrl + A to select all the results and close the Find and Replace dialog.
- As have selected all the cells that contain FALSE, those cells will be selected in the main dataset too.
- Right-click on the selection and select Insert.
- When the Insert dialog comes up, click on the Entire Row and press OK.
- In conclusion, we can see a blank row is added whenever the fruit names change.
In the above article, I have tried to discuss several examples to insert rows between data with Excel formula elaborately. Hopefully, these methods and explanations will be enough to solve your problems. Please let me know if you have any queries.
- VBA Macro to Insert Row in Excel Based on Criteria (4 Methods)
- Insert Rows in Excel Based on Cell Value with VBA (2 Methods)
- How to Insert a Total Row in Excel (4 Easy Methods)
- Macro to Insert Multiple Rows in Excel (6 Methods)
- Cannot Insert Row in Excel (Quick 7 Fixes)
- How to Insert a New Row in Excel (Top 5 Methods)