You can find lots of techniques to achieve the alternate shading effect in Excel. Most of them involve some sort of conditional formatting trick.
About a year ago, someone from one of my workshops taught me a trick to quickly apply alternate shading without a lot of hassle.
I’m a pretty lazy guy, so I naturally got excited.
Step 1: Color the first two rows of data.
Step 2: Grab the fill handle and drag to the end of the dataset. You can also double-click the fill handle.
Step 3: Click the ‘Auto Fill Options’ icon in the lower right-hand corner of your dataset and select ‘Fill Formatting Only’
There you have it.
Again, this is something I would use to quickly apply alternate shading without taking the time to go through a more sophisticated technique.
I've got to conserve my calories for something more useful…like sleeping.
Really cool.
We often look for sophisticated solutions when simple solutions are possible. Simple is cool.
Nice. I just tried it in Excel 2000 (don't ask why I'm still stuck with 2k) and couldn't get your methods to fill formats only to work. BUT. I dragged the fill-handle down with the right mouse button and got all the fill options, including "Fill Formats", "Fill Values", and "Fill ". Thanks for the tip!
Format | AutoFormat | List 1 is another way – if you like Gray and White.
Ditto what Brian says for 2003. You can you "right-drag" down, otherwise the menu won't appear.
BTW, please keep up the great tips… this is definitely my favorite excel blog.
Cool…I can use this to color all those white backgrounds black, so you can't see what I've written about you in every 2nd row.
In Excel 2003 using the Format Painter also seems to work.
A real lazy ass assigns a macro to a keyboard shortcut…
Sub Shady()
Dim Rng As Range
For Each Rng In Selection.Rows
With Rng
If .Row Mod 2 = 0 Then
.Interior.Color = 15395562
Else
.Interior.Color = 14540253
End If
End With
Next r
End Sub
nice…
Or select the range, format as table and then convert back to a range which leaves the nice (if you use 2007) formatting behind…
Copy -> Paste special, Formats also works, which I find easier than attempting to stop my fill over-extending wildly down the sheet.
The only downside to this technique is that the number of rows you want to format must be divisible by the number of rows copied, otherwise it just does nothing.