how to color every other line in an excel spreadsheet l.
Download
Skip this Video
Loading SlideShow in 5 Seconds..
How to color every other line in an Excel spreadsheet PowerPoint Presentation
Download Presentation
How to color every other line in an Excel spreadsheet

Loading in 2 Seconds...

play fullscreen
1 / 9

How to color every other line in an Excel spreadsheet - PowerPoint PPT Presentation


  • 477 Views
  • Uploaded on

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

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about 'How to color every other line in an Excel spreadsheet' - jacob


Download Now An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
the request
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.”

short answer
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
step by step for excel 2004
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
step 2 set the shade of gray
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
step 3 choose a column
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)
step 4 enter formula
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
step 5 conditional format
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
step 6 finishing touches
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!