How to format rows on Excel spreadsheet?
I want every other row to be gray.
Is there a way to apply this format without highlighting every other row and applying the color?
I want every other row to be gray.
Is there a way to apply this format without highlighting every other row and applying the color?
piquet
Favorite Answer
i answer this only because all the answers thus far are hugely elaborate and not terribly practical (or quick to achieve) since if you delete a row you would then have to manually re-apply colouring to every row under that to make them alternating again.
that said, ebmid2's post is along the right lines. though i haven't tested his particular formula it'll probably work BUT the formula used can be shorter than the above one and applied to a full range of cells at once rather than copying/pasting the conditional formatting:
» select all rows/cells that you want to apply colouring to.
» go to format > conditional formatting.
» in this conditional formatting dialog box, select 'formula is' from the drop-down list, and enter this formula:
=MOD(ROW(),2)=0
» click the 'format' button, select the 'patterns' tab, and specify a colour for the shaded rows.
» click 'ok' twice to return to your worksheet.
whether you insert or delete any of these rows, every other row will remain coloured.
additionally, for columns do the same but with:
=MOD(COLUMN(),2)=0
Barkley Hound
This should work assuming Excel does not have a built in feature to do it.
In an unused column put sequential numbers to be used to return the sequence at the end. In another column put 1 and then 2. Propagate it down so you have 1,2,1,2,1,2......
Now sort the sheet on the 1 and 2 column and every other row will be grouped together. Do the formatting. Resort using the sequential number column.
Remove the two columns.
✩
jeemmm
Here is the fastest way to do it
1 Highlight Row 2 and color it Gray
2 Highlight Rows 1 and 2 and do Edit Copy
3 Highlight the entire spreadsheet by pushing the square above row 1 and to the left of Column A
4 Then do Edit..... Paste Special..... Formats
Done.... will take less then ten seconds
?
You might try sorting the rows using an empty column and formatting the odd rows then sorting it back
for example
J : fill series with numbers 1-whatever
K : =mod(Jx,2) produces 0 and 1 as remainder of divide by 2
sort all by column K
Format first half (like all the 0 rows - even) to grey.
sort all by column J
Now every other row is grey.
Delete J & K
I just did it, so I know it works.
Anonymous
Go to any cell. Go to the Format menu, then select Conditional Formatting. Switch from Cell Value Is to Formula Is. Enter the formula "=ROW()/2= ROUNDDOWN(ROW()/2,0)" Click the Format button, go to the Patterns tab, and pick grey. Click ok, then click ok. Then copy that cell, highlight all the other cells on the worksheet, go to the Edit menu, select Paste Special, select Formats, then click Ok.
That'll make every even row grey. If you want the odd rows to be grey, do the same thing, except instead of the formula "=ROW()/2= ROUNDDOWN(ROW()/2,0)" you should use the formula "=ROW()/2<> ROUNDDOWN(ROW()/2,0)"