Use Conditional Formatting To Insert A Line At Each Column Change In A Table In Excel

In this tutorial we are going to use Conditional Formatting to show two ways that we can draw a line in a table at each change in the list of a specific column of that table. One method will draw a thin line, the other will make that line much bolder.

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 column B is a list of salespeople. I want to draw a line each time that name changes:

We’ll choose Conditional Formatting from the Home tab, and enter this formula:

The formula says that whenever the item in column B does not equal the one directly below, apply the formatting we choose to the entire row.

Then we’ll click on the Format button, choose the Border tab, select the bottom option and insert it at the bottom, and click OK, and OK. Here is the result:

The only issue is that the line is not very bold, and unfortunately, there is no option to select to make it bolder.

However, there is a work around. In the next option, we are going to start by making a bold line at all the rows by using the border option on the Home tab:

This is the result:

Now what we can do is insert a Conditional Formatting formula that says to insert a light line whenever the cell in column be IS THE SAME AS the one below, rather than not equal to:

I’ll choose a light dotted line in the Format, Border section of Conditional Formatting, and here is the result:

Take just a few minutes to watch the video to see exactly how we did it!

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