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!