How to color every other line in an Excel spreadsheet

1. How to color every other line in an Excel spreadsheet By Jim Gordon 2/15/2007

2. The Request “I'm wondering if there's a way to color every other line with a slight grey color, using Normal view, in such a way that even if you Sort the data on those lines, the grey banding still remains where they are. I want to do this to produce a more readable document. I've manually done this this alternative color banding, but whenever I re-sort the data, the colors get all messed up.”

3. Short Answer • Visit http://cpearson.com/excel/banding.htm • The following presentation is based upon Chip Pearson’s instructions • Chip Pearson and Jim Gordon are Microsoft MVPs

4. Step-By-Step for Excel 2004 • Excel offers 56 default colors. The default gray is probably too dark • Step #1 - Create a nice shade of gray to use • Excel Menu > Preferences > Color • Select a color then click the Modify button

5. Step #2 - Set the shade of gray • In the Colors picker click the Sliders button • Change the pop-up to RGB Sliders • Set the all three sliders to 233 • Click OK • This change is global • Within this workbook • Changes everything using this color • Click OK to close preferences

6. Step #3 - Choose a Column • Pick an empty column. • It will be filled with a formula. • Select the cell in row 1 of the column you wish to use • Example: • This example uses the right-most column, IV • Select cell A1 • Hold the Control key Down and then press the Right Arrow key to move to cell IV1 (right-most top cell)

7. Step #4 - Enter Formula • Type or paste this formula into the cell • =MOD(ROW()-1,2)+1<=1 • Click the column header to highlight the entire column • On the Edit menu choose Fill > Down • The formula populates down the column • Every other row will be TRUE or FALSE

8. Step #5 - Conditional Format • Click the Select All Cells button (diamond) • From the Format menu choose Conditional Formatting • Change pop-up to Formula is and enter or paste the special formula we used =MOD(ROW()-1,2)+1<=1 • Click the Format button • Select the Patterns tab • Click the nice gray shade we made • Click OK • Click OK

9. Step #6 - Finishing Touches • While all cells are selected • Format > Cells > Border • Under Presets click Outline then Insidebuttons • Click OK • Click column header of the column containing the special formula cells • Format > Column > Hide • Edit > GoTo > A1(you have to type A1) > OK • Done!