You can only have a maximum of 64 conditions in 2007. It is possible to refer to other worksheets when you are using cell references in your conditions.ġ) Create a named range for the cell (or range of cells) you want to useĢ) Use a cell reference on the active worksheet that refers to the other worksheet. Then highlight the other range(s) to be added. In order to make multiple selections, press Shift and F8 after making the first selection. The range selection to be conditionally formatting can be non-contiguous. If you define > 300 = blue and 200 300 = blue The precise formatting will depend on the order in which you define them. Lets assume you want all values over 300 to appear in blue and any values between 200 and 400 to appear in green.įor this you would need to create two conditional format statements. If Excel encounters cells that meet more than one conditional formatting condition it only applies the first one. This is for more complicated conditions, for example: This is for simple conditions, for example: You can create a condition based either on the actual value in the cell or a formula that uses that cell to determine a condition. Use the first drop-down list to select the type of condition you would like to use. The condition can either be based on the value in that particular cell or based on a formula. You can enter a maximum of three conditions per cell This can be found by selecting (Format > Conditional Formatting). Left(sText,1) etc will not be recognised and the conditions will not work - This is a very common mistake. Here are just some of the uses which conditional formatting can be used for.ġ) Identifying all negative numbers in a range of cells.Ģ) Identifying all cells that contain a value in a specific range.ģ) Identifying all the cells that contain an error.Ĥ) Quickly shading alternate rows or columns.Ĭells must be numbers and not text for the conditions to work !!

This formatting is dynamic and is done automatically as the value changes. What can you use Conditional Formatting for ? This is very useful for quickly identifying cells that meet certain criteria.įor example conditional formatting could be used to quickly identify all the cells which contain a value greater than 10. If the condition is False, then no formatting is applied. You can define conditions which when true will mean formatting is applied to the cells. Conditional Formatting is the automatic formatting of cells based on the values that the cells contain.

Copy conditional formatting excel 2016