Excel: Colour Striping Rows Using Conditional Formatting

Have you ever wanted to colour in certain rows in a worksheet to make it easier to read, but found it irritating to have to update the fill colours across the whole sheet if you ever added or deleted rows?

You can actually use conditional formatting to do it automatically for you. Here's how:

1. Click and drag to select the area you'd like striped. Go to Conditional Formatting, it's on the ribbon in the 'Home' tab, in the 'Styles' group. Choose "New Rule...".

2. Under 'Select a Rule Type:', pick 'Use a formula to determine which cells to format'.

3. Type in the following equation (substituting your own values for the variables, of course):

=MOD(ROW()-x,y*2)+1<=y

'x' is the number of the row that you want the striping to start on. 'y' is the number of rows you'd like in each stripe. For example, if you wanted the stripes to start on row 4, and each stripe should be 5 rows tall, you'll want to type in:

=MOD(ROW()-4,5*2)+1<5

4. Click 'Format', go to the 'Fill' tab, and choose how you'd like the stripes to look (colours, patterns...). Click 'OK', then 'OK' again... and tada! You'll have even sets of stripes that are based on the row numbers and the formula you entered, as opposed to manual formatting.

5 comments:

  1. FABULOUS!!! Thanks SO much... been wasting a TON of time trying to figure this out - when it should be a simple function on the Format menu since many of us who work in sheets would love this feature. Anyway - you're fab.

    ReplyDelete
  2. Glad I could help you out! Thanks for the kind words :)

    ReplyDelete
  3. YES! Thank you!!! Been trying to do this for ages.

    ReplyDelete