Is there a way to shade every other row on an Excel spreadsheet?

Question

We are using Excel as an attendance sheet and would like to shade every other row to make it more readable. How can this be done, other than actually shading every other row, one at a time? We can use either Excel 2000 or 2007, so instructions for either version will be fine. Thank you!

Answer 1

YES

Answer 2

1. Add a column on the far left, and give it a colum title of Sort or Temp or whatever you want to call it.

2. In the first two rows enter 1 and then 2

3. Select those two cells and copy them

4. Paste them all the way down your list

5. Highlight everything, all of your other data too.  Go to Data, Filter, select AutoFilter

6. Now in your Sort column there's a little arrow, click it and select either 1 or 2, whichever rows you want highlighted.  This hides everything else.

7. Select all of your rows and use the fill button to fill any color you want.

8. Click your filter arrow again and select all to unhide everything.

9. You can leave AutoFilter on or select Data, Filter, AutoFilter again to turn it off.

EDIT: jmorge's solution is easier

Answer 3

shade one row

leave the row below it unshaded

now hightlight both rows

click the "format paste" option (looks like a paint brush icon next to the regular paste icon)

now drag your mouse over all the other rows.  The "format" will get copied to all the rows you select.  And since your painting one shaded, one non-shaded row, they get painted that way as well.

or you could copy the 2 rows, then highlight your other rows, right click and select "paste special" and select the option to paste the format only.

much easier than writing some macro of vba script to do it :-)))))

Answer 4

Try this. Select the whole first row. Select Format>Conditional formatting. Select formula in cell option and type the following

=MOD(ROW(),2)=0, select pattern amd chose colour of your choice. Click OK. Select row 1 again, select Format painter and then select as many rows as you need.

If you are using 2003 and making a list it should automatically format for you. If using 2007 there is a multitude of formatting options available on the ribbon.

Answer 5

Re:  VBA macros...

Oh, I don't know.... writing VBA macros for some is like falling off a log.

In addition, macros conserve Excel overhead, whereas 1000's of formulas in 10's of sheets cause application bloat, performance degradation, and maintenance issues.

Not to mention that macros can take you places that simple formulas cannot.