Excel Tip: Highlighting a specific row or column (even after inserting and deleting)

I was asked this question in response to an earlier post and thought it was worth sharing my response as it has many applications.

The reader asked how to highlight a specific row number (in red, say), without the highlight moving when you insert or delete rows.

This might be to highlight when you have exceeded a target on a list for example.

Say, we want to highlight row 101, this would typically be the 100th record in a list with headers.

If we simply place a red fill on row 101, this will move to row 100 if we delete a row above it, or row 102 if we insert one.

How do we get it to stay on row 101?

The answer is to use Conditional Formatting.

First of all we need to highlight the range that we wish to apply the conditional formatting to. This could be the whole sheet, but this uses unnecessary resource that might be important in a bigger spreadsheet. A better approach would be to only apply the conditional formatting to the cells that we need to.

The width of our highlighted range should be how far across that we want the highlight to go (probably to the last column that we will be using), let's say column H.

The height needs to allow for the maximum amount of deleting rows that might happen as the range will reduce when we delete rows in it (but expand when we add them). Unless we expect to do a huge amount of deleting and very little inserting, then 1,000 rows should be more than adequate.

So, let's highlight the range A1:H1000, then click Conditional Formatting on the Home Ribbon, and New Rule.

From the list of options, select "Use a formula to determine which cells to format".

In the formula box, type:

=ROW()=101

Click the Format button and choose the format for the highlighted cells, e.g. a Red Fill.

Click OK and you're done. Row 101 will be highlighted in red up to column H, and this highlighting will stay on row 101 after inserting and deleting rows!

You can do exactly the same thing with columns, using =COLUMN()=x. Note that x is a column number rather than a column letter, so if we wanted to highlight column H, it would be =COLUMN()=8.





If you enjoyed this post, go to the top of the blog, where you can subscribe for regular updates and get two freebies "The 5 Excel features that you NEED to know" and "30 Chants for Better Charts".