How to insert a blank row after specific text in Excel?
In Excel, inserting a blank row after specific text can help improve readability and organize data more efficiently. Whether you're working with a list of tasks, transactions, or any structured data, adding blank rows after specific text can separate and group related entries for better clarity. This is particularly helpful when preparing reports, invoices, or logs where blocks of related information need to stand out. Without clear separations, large datasets can become difficult to review, analyze, or navigate. Inserting blank rows manually can be tedious, especially for long lists, but there are automated and semi-automated ways to streamline this process.
 |  |  |
➤ Insert blank rows after specific text with VBA code
➤ Insert blank rows after specific text with Kutools AI
➤ Insert blank rows after specific text using Excel formulas (helper column)
Insert blank rows after specific text with VBA code
Using VBA (Visual Basic for Applications), you can automate the process of inserting blank rows after specific text values in your dataset. This solution is efficient for large datasets or when repeated operations are required. Since VBA allows you to customize the conditions and the range to process, it's well suited if your criteria or worksheet structure vary over time.
⚠️ Note: Always save a backup before running any macro, as VBA can make irreversible changes. Use caution when modifying live workbooks.
1. Hold down the ALT + F11 keys to open the Microsoft Visual Basic for Applications window.
2. Click Insert > Module, and paste the following code into the Module Window:
📜 VBA code: Insert blank rows after the specific text
Sub Insertrowbelow()
' Updated by Extendoffice
Dim i As Long
Dim xLast As Long
Dim xRng As Range
Dim xTxt As String
On Error Resume Next
xTxt = Application.ActiveWindow.RangeSelection.Address
Set xRng = Application.InputBox("Please select the column with specific text:", "Kutools for Excel", xTxt, , , , , 8)
If xRng Is Nothing Then Exit Sub
If (xRng.Columns.Count > 1) Then
MsgBox "The selected range must be one column", , "Kutools for Excel"
Exit Sub
End If
xLast = xRng.Rows.Count
For i = xLast To 1 Step -1
If InStr(1, xRng.Cells(i, 1).Value, "In progressing") > 0 Then
Rows(xRng.Cells(i + 1, 1).Row).Insert shift:=xlDown
End If
Next
End Sub💡 Tip: In the above code, you can replace "In progressing" with any text you need to target. Make sure the text matches exactly (including case and spacing) to avoid incorrect results.
3. Press the F5 key to run the macro. In the dialog box that appears, select the column containing the specific text you want to process. For large datasets, zoom out or use filters to help make an accurate selection.
4. Click OK. The macro will insert blank rows after every cell that contains the specified text. Review your results to ensure the operation meets expectations and adjust your criteria if needed.
| | |
✅ Advantages: Automates repetitive work on large datasets; fully customizable for various keywords and column ranges.
⚠️ Limitations: Requires basic VBA knowledge; actions are irreversible without a saved backup.
🛠️ Troubleshooting Tip: If blank rows are not being inserted, verify that the target text exactly matches the content in your cells. Run the macro on one column at a time to prevent unintended results.
Insert blank rows after specific text with Kutools AI
Struggling to insert blank rows after specific text in Excel? Let Kutools AI simplify the task for you! With its AI-based automation, you no longer need to manually scan your dataset or write any VBA code. Kutools AI instantly identifies the rows with the specific text and inserts blank rows exactly where you need them.
After installing Kutools for Excel, simply go to Kutools > AI Aide to open the "Kutools AI Aide" pane.
- Select the data column that you want to process by inserting blank rows based on specific text.
- In the chat box, type your request clearly. For example:
Insert a blank row after the specific text "In progressing" from the selection - Press Enter or click the Send button. Kutools AI will analyze your instruction. Once finished, click Execute to perform the operation.
💡 Tips:
• Kutools AI is ideal for users who want to avoid formulas or VBA. It’s user-friendly and fast. However, make sure your keywords are unique enough to prevent inserting blank rows in the wrong places. Always review the results after execution.
• To ensure the best results, always specify your criteria clearly in the AI chat. Consider creating a backup of your worksheet before running bulk operations.
✅ Advantages: No need for programming knowledge, works well with large or complex datasets, and allows rapid adjustments for different criteria.
⚠️ Limitations: Requires the Kutools for Excel add-in to be installed. Some complex or highly specific tasks may require clearer instructions to work as expected.
Insert blank rows after specific text using Excel formulas (helper column)
If you prefer not to use VBA or add-ins, Excel formulas combined with a helper column offer a simple and transparent method to identify where blank rows should be inserted. This solution is most suitable for small to moderately sized datasets, especially when you value clarity and manual control over automation.
How this method works: You’ll create a helper column alongside your data to flag rows that contain specific text. Then, by sorting or filtering the helper column, you can manually identify and insert blank rows at the right places.
✅ Advantages: No macros or add-ins required. Offers full transparency and allows you to preview all changes before applying them.
⚠️ Limitations: Requires extra manual steps. Not recommended for very large datasets or highly complex matching conditions.
Steps:
- Add a helper column.
Suppose your data is in column A, and you want to insert a blank row after every cell containing In progressing. Add a new column (e.g., column B), and enter the following formula in B1:=IF(A1="In progressing",ROW(),FALSE) - Drag the formula down.
Use the fill handle to copy the formula down the helper column, evaluating each row. - Identify matching rows.
Filter or sort the helper column to bring all rows with numeric values (i.e., matches) together. - Insert blank rows manually.
For each identified row, use Excel’s context menu to insert a blank row directly below it.
💡 Tip:
• To match partial text instead of exact values, use this formula:
=IF(ISNUMBER(SEARCH("progress",A1)),ROW(),FALSE)• If your dataset has headers, begin your formula in the first data row (e.g., A2), and adjust references accordingly. After inserting rows, you may delete the helper column to tidy up.
⚠️ Common Issues:
• For large datasets, this manual method can be tedious and error-prone.
• Always double-check that blank rows are inserted correctly. Filters, merged cells, or hidden rows may affect accuracy.
Kutools AI is especially suitable if you want to avoid dealing with formulas or VBA. It's user-friendly and excellent for those who prioritize convenience and speed. While Kutools AI generally handles text matching well, double check that your keywords are unique enough to prevent unexpected blank row insertions. If you're interested in exploring additional Excel automation or productivity tools, our website offers thousands of tutorials to help you optimize your workflow.
More relative insert rows articles:
- Copy And Insert Row Multiple Times Or Duplicate The Row X Times
- In your daily work, have you ever tried to copy a row or each row and then insert multiple times below the current data row in a worksheet? For example, I have a range of cells, now, I want to copy each row and paste them3 times to the next row as following screenshot shown. How could you deal with this job in Excel?
- Insert Blank Rows When Value Changes In Excel
- Supposing you have a range of data, and now you want to insert blank rows between the data when value changes, so that you can separate the sequential same values in one column as following screenshots shown. In this article, I will talk about some tricks for you to solve this problem.
- Insert Multiple Rows Or Columns Quickly In Google Sheets
- This article, I will introduce an easy way to insert multiple blank rows or columns in a google sheet.
- Insert Blank Row Above Specific Text In Excel
- When you work on a worksheet, have you ever tried to insert blank row above a specific text in a column? For example, there are some names in column A, now, I want to insert blank rows above the cells which contain the name “Mike” as the left screenshot shown, maybe, there are no direct way for you to solve this task in Excel. This article, I will talk about some good tricks for dealing with this job in Excel.
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
All Kutools add-ins. One installer
Kutools for Office suite bundles add-ins for Excel, Word, Outlook & PowerPoint plus Office Tab Pro, which is ideal for teams working across Office apps.
- All-in-one suite — Excel, Word, Outlook & PowerPoint add-ins + Office Tab Pro
- One installer, one license — set up in minutes (MSI-ready)
- Works better together — streamlined productivity across Office apps
- 30-day full-featured trial — no registration, no credit card
- Best value — save vs buying individual add-in