How to format rows on Excel spreadsheet?

Question

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?

Answer 1

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

Answer 2

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.
✩

Answer 3

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

Answer 4

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.

Answer 5

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)"

Answer 6

This will take less that every other (exponential application of format). & you can control the color!!
Highlight every other row about eight times.
Select 16 rows (8 highlight, 8 plain)
edit-copy
select the row following your selection
edit- paste special- radio button formats (OK)
reselect 32 this time (original 16 plus the new 16 rows)
edit-copy
select the row following your selection
edit- paste special- radio button formats (OK)
reselect 64 this time (original 16 plus the new 16 rows)
edit-copy
select the row following your selection
edit- paste special- radio button formats (OK)


This gets you to 128 formated rows..

or....
If you can handle grey
on a blank spreadsheet
format - autoformat- select list 1
about 30-45 seconds later select
row 1 & delete

Answer 7

Excel Highlight Every Other Line

Answer 8

I use macros when I do this, create a macro and paste this into it

Sub ColorFormat Macro()
ROWX = 3
LINE500:
Dim Cell As Range

Range(Range("A" & ROWX), Range("AA" & ROWX)).Select
With Selection.Interior
.ColorIndex = 15
.Pattern = xlSolid
End With

ROWX = ROWX + 2

For Each Cell In Range("A" & ROWX)
'If Cell.Value = "" Then GoTo LINE600
Next
If ROWX < 3000 Then GoTo LINE500



LINE600:
End Sub

This will change every other row to light gray, if you want to adjust the color change the color index value "15". Hope this helps!

Answer 9

Yes, go to Format/AutoFormat.... If you select List 1, it will do it for you. If you look at the options as you select, you can pick what things you want to apply the format you.

If it's not a huge sheet, you can CTL click every other line and then click Format Cells and highlight it gray..... but the AutoFormat feature is really meant for this.

Answer 10

On the toolbar:

1) Format
a) Cells
b) Choose what you want to format, numbers, lignment or whatever.

Good Luck!