Step by step instructions to shading each other line in an Excel spreadsheet - PowerPoint PPT Presentation

how to color every other line in an excel spreadsheet l.
Skip this Video
Loading SlideShow in 5 Seconds..
Step by step instructions to shading each other line in an Excel spreadsheet PowerPoint Presentation
Step by step instructions to shading each other line in an Excel spreadsheet

play fullscreen
1 / 9
Download
Download Presentation

Step by step instructions to shading each other line in an Excel spreadsheet

Presentation Transcript

  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!