Conditional Formatting

Highlight Cells Rules  

 

To highlight cells that are greater than a value, execute the following steps.  
1. Select the range A1:A10. 

2. On the Home tab, click Conditional Formatting, Highlight Cells Rules, Greater Than...  

3. Enter the value 80 and select a formatting style

4. Click OK.

Result. Excel highlights the cells that are greater than 80. 

5. Change the value of cell A1 to 81.

Result. Excel changes the format of cell A1 automatically. 

Note: you can also highlight cells that are less than a value, between a low and high value, etc.  

Clear Rules 

To clear a conditional formatting rule, execute the following steps.

1. Select the range A1:A10. 

2. On the Home tab, click Conditional Formatting, Clear Rules, Clear Rules from Selected Cells.

Top/Bottom Rules 

To highlight cells that are above the average of the cells, execute the following steps.

1. Select the range A1:A10. 

  

2. On the Home tab, click Conditional Formatting, Top/Bottom Rules, Above Average...  

 

3. Select a formatting style. 

4. Click OK.

Result. Excel calculates the average (42.5) and formats the cells that are above this average. 

Note: you can also highlight the top 10 items, the top 10 %, etc. The sky is the limit!

 

Happy Learning!!

Excel formulas and functions - Tips for Troubleshooting

Finding & fixing errors in excel formulas & functions can be tedious & time-consuming.  Here are some practical tips to help you cut to the chase.

Error values: Not a nuisance but are clues

Excel displays an error value, if it cannot calculate a formula. These values are important clues & knowing what they mean can help quickly spot errors. The following error values are your initial steps, & often the only steps, to debug the error:

1. #VALUE!: You've expressed the wrong data type or operator. The most common cause for this error value is a reference to text by a numeric operation. 
2. #REF!: You've referenced a cell incorrectly. Most likely, you've deleted a cell. For instance, the expression =A1+B1 won't return an error if you delete the value in B1. But if you delete column B, it will. 
3. #NUM!: This error value indicates a problem with a number in the formula. Either the argument is invalid or the result is too large or small. 
4. #NULL!: You specified an intersection between two ranges that don't intersect, such as A1:C3 & D4:F6. If the ranges do intersect, you've probably used a space character (the union operator) instead of a comma to separate the two ranges (A1:C3 D4:F6 instead of A1:C3,D4:F6). 
5. #NAME?: This error occurs when you refer to a range incorrectly or forget to enclose text in quotation marks. When Excel encounters text in a formula, it tries to interpret the text as a cell reference, a range name, or a function name. When it can't recognize the text as any of those things, it displays this error value. Check your function names, cell references, & range names (make sure the range name actually exists if you've spelled it correctly). If all ranges are correct, make sure you've delimited text using quotation marks. 
6. #DIV/0: Excel can't divide by 0 & your formula refers to a cell that contains or evaluates to 0 or is blank. This is one of the easiest to find & fix.

Happy Learning!!