You can do this with conditional formatting. Conditional formatting applies one or more rules to any cells you want. An example of a rule might be If the value is greater than 5000, color the cell yellow. By applying this rule to the cells in a worksheet, you’ll be able to see at a glance which cells are more than 5000. There are also rules that can mark the top 10 items, all cells that are below the average, cells that are within a certain date range, and many more.

For example, suppose you want to flag the sales in the following table that fall below 200,000.

Select the range first and then click on Conditional Formatting from the Home tab.  Click on Highlight Cells Rules, and from the popout menu, select Less Than…

From the dialog box that appears, enter the LESS THAN value on the left and choose one of the available formats on the right.  If you’d like to create your own, select Custom Format…

You can set the numerical format, the font style and color, the cell border and the fill.  In this example, the font color and fill color have been changed.

Notice that the Sales that are less than 200,000 in that range are now highlighted.

This is dynamic formatting, meaning that if the values in that list drop below 200,000, their formatting will pick up the rule.



Submit a Comment

Your email address will not be published. Required fields are marked *