Take the 2-minute tour ×

I would like to shade entire rows in Excel based on the value of one cell. For example say I have the rows below:

File No 1122 1122 1144 1155 1155 1155 1166

I would like the first two rows (where the value of the file # is 1122) to be shaded in color 1, the next row (where the value of the file # is 1144) to be shaded in color 2, the next 3 rows (where the value of the file # is 1155) to be shaded color 1, the next row (where the value of the file # is 1166) to be shaded color 2

share|improve this question

4 Answers 4

What you can do is create a new column over on the right side of your spreadsheet that you'll use to compute a value you can base your shading on.

Let's say your new column is column D, and the value you want to look at is in column A starting in row 2.

In cell D2 put: =MOD(IF(ROW()=2,0,IF(A2=A1,D1, D1+1)), 2)

Fill that down as far as you need, (then hide the column if you want).

Now highlight your entire data set - this selection of cells will be the ones that get shaded in the next step.

From the Home tab, click Conditional Formatting, then New Rule.

Select Use a formula to determine which cells to format.

In "Format values where this formula is true" put =$D2=1

Click the Format button, click the Fill tab, then choose the color you want to shade with.

Examples here:

share|improve this answer
I think this is the answer that should be marked as correct as the OP said they wanted rows distinguishable even though their example looked like it was columns –  SelectDistinct Aug 14 '13 at 13:57

Use Conditional Formatting.

In it's simplest form, you are saying "for this cell, if it's value is X, then apply format foo". However, if you use the "formula" method, you can select the whole row, enter the formula and associated format, then use copy and paste (formats only) for the rest of the table.

You're limited to only 3 rules in Excel 2003 or older so you might want to define a pattern for the colours rather than using raw values. Something like this should work though:

alt text

share|improve this answer
Even though this correctly answers the question as worded, I suspect the original asker was trying to achieve alternating color stripes based on a change of data, sometimes called zebra stripes. For that, I think Mike Bain's answer is better. –  KC Baltz Jun 15 '13 at 21:51

If you are using MS Excel 2007, you could use the conditional formatting on the Home tab as shown in the screenshot below. You could either use the color scales default option as I have done here or you can go ahead and create a new rule based on your data set. Conditional Formatting

share|improve this answer

I have found a simple solution to banding by content at Pearson Software Consulting: Let's say the header is from A1 to B1, table data is from A2 to B5, the controling cell is in the A column

  1. Make a new column, C
  2. At first the first row to color make the formula =true in the C2 cell
  3. In the second row make the formula =IF(A3=A2,C2,NOT(C2))
  4. Fill the column down to the last row
  5. Select the data range
  6. Select conditional formatting, choose Use a formula... and put =$C2 as the formula
share|improve this answer

Your Answer


