Let’s say you have a worksheet with innumerable rows of data. It would be extremely difficult and lethargic to see patterns just from examining the raw information and viewing data separately, which can make it difficult not only to investigate a particular piece of information, but also increase the chances of error production.
A feature of MS-Excel called ‘Conditional Formatting’ provides another way to visualize data and make worksheets easier to understand and may also produce a way of sorting relevant and desired information.
Conditional formatting is a wonderful and easy way to immediately format data in an excel spreadsheet. With conditional formatting, you can do functions like highlight dates in the next 15 days, flag data entry functions, highlight rows that contain specific type of customers, show duplicates, and more.
Excel deals with innumerable presets that make it easy to create new conditions without formulas. However, these presets are very limited and you can also create rules with your own custom formulas by following certain semantics and logic of conditional formatting. By using your own formula, you take over the condition that triggers a Boolean value, which produce the result.
Conditional formatting enables you to apply desirable formatting features and get you a chance, to even sort the data using the formatted result—such as colors, icons, and data bars—to one or more cells based on the cell value, that is, the value contained in the cell. To do this, it would be required to create a Conditional Formatting Rule, also called as Conditional Formatting Condition. For example, a conditional formatting rule might be: If the money is less than 2000, color the cell pink. By applying this rule, you’d be able to quickly see which cells containing money less than 2000, and can further be used for formatting.
Creating a conditional formatting rule/condition
You can create a formula-based conditional formatting rule in four easy steps:
- The cells that need to be formatted should be selected.
- The desired conditional formatting rule should be created by using a specific formula.
- The formula created should return Boolean values as result, that is, TRUE or FALSE.
- Formatting option needs to be set, and click on OK, to save the conditional formatting condition.
In the below example, the function only returns TRUE (1) for odd numerics, according to the formula:
Formulas that apply conditional formatting must return Boolean values as results or number equivalents. Here are some sample examples of formula logic that can be applied for conditional formatting:
The above formulas all return TRUE or FALSE as their results and can satisfactorily be used for conditional formatting.
When conditional formatting is applied to a large quantity of cells, you need to enter cell references with respect to the first row and column in the selection (i.e. the upper left cell). The way of getting acquainted with the idea of understanding how conditional formatting formulas work is to imagine the same formula being applied to each cell in the selection, with cell references updated as usual and applied to a range. Imagine that you entered the formula in the upper left cell of the selection, and then copied the formula across the entire selection.
Below are instances of certain custom sample formulas you can use to apply conditional formatting. Some of these examples can be fabricated using Excel’s built-in presets for emphasizing certain cells using highlighter, but custom formulas can go far beyond presets, as you can see below.
Example 1: Highlight orders from Texas
For highlighting rows that represent orders from Texas (short handedly written as TX in the example), use a formula that locks the reference to column F:
Example 2: Highlighting dates in the next 30 days
For highlighting dates occurring in the next 30 days, a formula is needed that:
- Makes sure dates are in the future, and
- Makes sure dates are 30 days or less from today.
This can be done using the AND function together with the NOW function like this:
With a current date of August 18, 2016, the conditional formatting highlights dates as follows:
The NOW function returns the current date and time.
Highlighting Column Differences
Given two columns that contain similar information, you can apply conditional formatting to distinguish differences. The formula used to achieve the formatting is shown below:
Highlighting missing values
A typical example to highlight values in one list that are missing from another list, a formula using the keyword COUNTIF can be used, which can be used to produce a condition that returns a Boolean value.
This formula simply checks each value in List A against values in the named range “list” (D5:D10). When the count is zero, the formula returns TRUE value or 1 and triggers the rule, which then highlights values in List A that are missing from List B, with a green fill.
Highlighting properties with 3+ bedrooms under $350k
A typical example of conditional formatting condition to find properties in this list that have at least 3 bedrooms but at the same time, are less than $300,000, a formula can be fabricated using AND condition that can be used to produce the result.
The dollar signs ($) lock the reference to columns C and D, and the AND function is used to make sure both conditions are TRUE. If, and only if, both the conditions are satisfied, TRUE value is returned, and the cells satisfying these conditions get highlighted.
Highlighting top values
Formulas in Excel can be made more flexible and can be applied to conditional formatting conditions to produce desired results, as shown in the following example, wherein a formula is used to highlight the top 5 values.
The formula used for this rule is:
Where the complete cell information is contained in the named range B4:G11.
Simple Search Box
A simple search feature can also be performed using Excel Feature of Search Box by applying the desired formula of conditional formatting. In this example, a rule highlights cells in column B that contains text of cell F2:
The formula used is:
If the conditional formatting rule isn’t working properly, there has to be a problem with the conditional formatting condition or rule applied or some kind of syntactical or semantic error associated with it, which is not producing the desired result.
Always start the condition of conditional formatting with an ‘equal to’ sign (=). If you forget this step, Excel will automatically consider the entire formula as text, rendering it useless and producing no result at all. To fix this issue, replace the double quotes Excel generated automatically at either side with equal to (=) sign.
If the result is not generated yet, the problem needs to be looked in more carefully for any logical or type issue.
Dummy formulas are a way of testing in worksheet, certain conditional formulas, so you can investigate your performance using them as ‘test’. This can help save time when reference cells are not working as desired as you want to, by checking and applying the dummy formula on them.
Basically, for testing, you enter the same formula across a range of cells which lets you see the values returned by each formula, and provides a way of visualizing and observing how conditional formatting works.
There are few drawbacks that occur with formula-based conditional formatting. First, you can’t use icons, color scales, or data bars with any custom condition. You are narrowed down to use only standard cell formatting, including number formats, font style, color, and border option.
Second, you can’t use certain formula constructs like unions, intersections, or array constants for conditional formatting criteria. Generally, there are ways to work around these drawbacks. If you investigate this error, and have an applicable formula, you will be easily able to solve the issue.
How Much Have You Learnt ?
Consider yourself to be a teacher and you need to check the grades that are below 70, who have not perfomed satisfactorily. So, conditional formatting can be applied to highlight those cells containing marks less than 70 with a light red fill.
Also, try to use how the grades can be compared to each other. This can be done using Icon set in Conditional Formatting Tab.
Your spreadsheet should look like this: