Add, change, or clear conditional formats. If your conditional formatting needs are more complex, you can use a logical formula to specify the formatting criteria. For example, you may want to compare values to a result returned by a function or evaluate data in cells outside the selected range, which can be in another worksheet in the same workbook. Select the number, font, border, or fill format you want to apply when the cell value meets the condition, and then click OK. You can choose more than one format. The formats you select are shown in the Preview box. Example 1: Use two conditional formats with criteria that uses AND and OR tests In the example shown here, the first rule formats two cells green if both conditions are true. If the result of that test is not True, the second rule formats two cells red if either of the conditions are False. A home buyer has budgeted up to $7. If both the down payment and the monthly payments fit these requirements, cells B4 and B5 are formatted green. If either the down payment or the monthly payment meet the buyer's budget, B4 and B5 are formatted red. Change some values, such as the APR, the loan term, the down payment, and the purchase amount to see what happens with the conditionally formatted cells. Formula for first rule (applies green color)==AND(IF($B$4< =7. IF(ABS($B$5)< =1. Formula for second rule (applies red color)=OR(IF($B$4> =7. IF(ABS($B$5)> =1. Example 2: Shade every other row by using the MOD and ROW functions A conditional format applied to every cell in this worksheet shades every other row in the range of cells with a blue cell color. You can select all cells in a worksheet by clicking the square above row 1 and to the left of column A. The MOD function returns a remainder after a number (the first argument) is divided by divisor (the second argument). The ROW function returns the current row number. Enables generic SGML to be served, received, and processed on the Web in the way that is now possible with HTML. XML has been designed for ease of implementation and. When you divide the current row number by 2, you always get either a 0 remainder for an even number or a 1 remainder for an odd number.Because 0 is FALSE and 1 is TRUE, every odd numbered row is formatted.The rule uses this formula: =MOD(ROW(),2)=1.Note: You can enter cell references in a formula by selecting cells directly on a worksheet or other worksheets.Selecting cells on the worksheet inserts absolute cell references. Windows 7 X86 X64 Preactivated . If you want Excel to adjust the references for each cell in the selected range, use relative cell references.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |