Alternating gray rows on my Excel reports

S

ScottSisk

I have a report that is nearly 1000 rows long and to make it easier t
read when I print it, I would like to have every other row, starting a
row 1, to have a gray background.

Is there a way to do this easier than having to select each ro
individually and filling it with gray color?

(I hope this question is in the right forum.
 
M

Max

Select all the required rows / range
Click Format > Conditional Formatting
Under condition 1, Formula is: =MOD(ROW(),2)
Click Format button > Patterns tab > Light gray > OK
Click OK at the main dialog
 
S

SteveG

Scott,

You can use conditional formatting. In the first row that you want t
color gray, select the cells that you wish to have filled (i.e. A2:G2)
Go to Format>Conditional Formatting. Change the option from Cell Valu
is to Formula is and enter this formula.

=MOD(ROW()-1,2)+1<2

Select the Gray fill format and click on ok. Your row should be Gray.
Use the format painter to apply to the rest of your rows by selectin
the cells that you just applied the CF to, cilck on the format painte
and highlight all the rows that you want it to apply to (i.e
A3:G1000).

Cheers,

Stev
 
D

Dave Peterson

One more option:

Select your range
format|autoformat

You may find something prettier. (But you'll have to reapply if you
insert/delete a row).
 
J

jaaltonen

I've tried both Max's and Steveg's versions and for some reason can't
make then work (I've tried copying and pasting to avoid misspelling but
no luck).
For example in Max's version I get an error in formula and number 2 is
highlighted.
In SteveG's version Excel seems to accept the formula but then nothing
happens.
Any pointers where I'm doing wrong
 
J

jaaltonen

OK problem solved. Thanks Max.
In my my previous attempts I used a comma (,) but in the example
workbook you directed me to a semicolon (;) was used, now it works.
I used a Finnish version of Excel at work and UK version at home with
computer localization set to Finland so maybe that has something to do
with it (just before you posted I had tested it with my UK version
without luck, but as I said now it works).
 
Top