Sunday, November 23, 2008

Advanced excel (1)

Having googling for answers on some special or overlooked tool in excel...

Finally, I got my answers on them. But if I dun record it, I will forget it. Life is so busy >_<>

Trick 1: Color fill Multiple Rows Based on Condition
source: www.ozgrid.com
  1. Select all cells on the worksheet
  2. Select: Format|Conditional Formating....
  3. Set: "Formula is" and for formula use: =IF($F1="Yes", TRUE, False)
  4. Set your pattern
Trick 2: Fill alternate rows with a color
  1. Select the range
  2. Select: Format|Conditional Formating
  3. Set: "Formula is" and for formula use: = MOD(ROW(), 2)=0
  4. Set your pattern
  • Remarks: explanation on the sytax: MOD Function and ROW()
MOD(,): Remainder
ROW(): row number

i.e. Remainder of "ROW( ) being divided 2" is equal to 0. If true, apply the pattern, else no action will be taken.

Next coming Sort by color

Trick 3: Sort by color (coming soon ... ^^)



No comments: