Linker IT Software
Google
Web www.oraxcel.com
menubar-top-links menubar-top-rechts
Home Help Search Login
Welcome, Guest. Please Login.
SQL*XL: Database to Excel bridge litLIB: Excel power functions pack ExcelLock: Locking and securing your valuable Excel spreadsheets encOffice: Protect your Excel file easy and safe encOffice: Protect your Excel file easy and safe
Pages: 1
Excel: Conditional Formatting (Read 3245 times)
Gerrit-Jan Linker
YaBB Administrator
*****




Posts: 75
Excel: Conditional Formatting
06.04.06 at 21:49:38
 
One of the gems of Excel is conditional formatting. I use it a lot to highlight cells if it needs some attention. You could e.g. color a cell red to allert the user and you can color it green to explicitly mark it ok.
 
A basic scenario
Basic conditional formatting is done by looking at the cell value and specifying some formatting if a particular condition is met. A very simpel scenario is that if the value in a cell is less than zero to color it red and when the value is positive to color it green.
 
Type the following values in a spreadsheet:
A1: 5
A2: -5
 
Now select both A1 and A2.  
Choose Format, Conditional Format
A dialog box pops up called Conditional Formatting
Set Condition1 as Cell Value Is greater than 0
Press the format button and choose patterns
Select a green shade
 
Then press the Add button.
Condition2 appears.
Set Condition2 as Cell Value Is less than 0
Press the format button and choose patterns
Select a red shade
 
Press Ok to close the window
You should see that A1 with value 5 is colored green and that A2 with value -5 is colored red.
 
A more complex scenario
A more useful scenario is when you want to base the formatting of a cell on the outcome of a formula. You can reference other cells too. Suppose you want to color a whole row green when the value in column A is positive and that you want to color the whole row red when the value in column A is negative.  
 
Start with a new worksheet, type again in A1: 5 and in A2: -5
Now select cell A1.
Choose Format and conditional formatting
Set Condition1 to: Formula Is =$A1>0
Please note that I start the formula with an = sign. Then note that I have used a $ before the column A. This will ensure that when I copy the formatting lateron that the reference to column A is not changed. I do want to change the row numbers so the conditional format for row 2 will look at A2.
 
Now press the format button and specify a green pattern
 
Now set a condition for the negative values.
Press the Add button to get a second condition
Set Condition2 to: Formula Is =$A1<0
Use the format button to select a red pattern.
Close the dialog.
 
Now we are going to copy the formatting of A1 to all the other cells in the worksheet.
Select cell A1
Choose Edit, Copy
Now click on the space between column A and row 1 to select the whole spreadsheet.
Choose Edit, Paste Special, Formats
 
Now you should see that the full row 1 is colored green and the row 2 is colored red.
Type some values in A3, A4 and so on to test the formatting settings.
Back to top
 
« Last Edit: 06.04.06 at 21:51:04 by Gerrit-Jan Linker »  

Gerrit-Jan Linker
Linker IT Software
Email WWW Gerrit-Jan Linker   IP Logged
Pages: 1