How To Insert A Blank Row At Each Change In A Column In Excel

In a previous post I talked about how to insert a line at each change in a column of a table or data range in Excel. In this tutorial we’ll look at how to insert a blank row at those changes.

You can download the file here and follow along. If you get a preview, look for the download arrow in the upper right hand corner.

Here I have a table, and in that table you can see that the Salespersons names in column B change periodically. I want to insert a blank row each time that name changes:

I am going to use a helper column to accomplish this. I will start at cell I3 and insert the formula:


Notice I did not start at row 2 but rather row 3. The result was that Excel inserted a TRUE or FALSE in the entire column:

The FALSE will appear every time the name changes in column B. Next, I will delete the top formula in cell I2, since I don’t want to insert a blank row at the very top.

Now, using Paste Special Values, I will convert the formulas in column “I” to their values of TRUE or FALSE.

Next I will highlight all the cells in column “I” and do a Find All for the word “FALSE”:

Holding down the SHIFT key, I will then click on the last item in this list which will select all the cells that contain FALSE:

Now that all those cells are highlighted, I can close the FIND dialog box. From here I can use the keyboard shortcut Ctrl + + (this is the Ctrl key plus the “+” on the keypad), and it will bring up the Insert dialog box:

I’ll select “Entire row” and click OK, and Excel will insert a blank row above each instance of the work FALSE:

Now all I need to do is delete the helper column and my table has a blank row at each change of Salesperson’s name!

Performing this on the data range is very similar. Take a few minutes and watch the video to see the exact process!

What can you do next?
Share this post with others that can benefit from it!
Leave a comment or reply below – let me know what you think!
Subscribe to this blog for more great tips in the future!
Check out my YouTube channel – click on the YouTube icon below!

Happy Excelling!

Free Download!

Subscribe to Download Your FREE Copy of
"My 70+ Favorite Excel Keyboard Shortcuts" Today!

No comments yet.

Leave a Reply