Buy Me a Coffee? Your donation will help me to make more tutorial videos!
Oct 19, 2018 - For example, if you selected the rule “date is after,” enter a date that the date in the cell. After you add a conditional highlighting rule to a cell, you can apply that rule to other cells. In the Format sidebar, click the Cell tab.
Conditional formatting is a fantastic way to quickly visualize data in a spreadsheet. With conditional formatting, you can do things like highlight dates in the next 30 days, flag data entry problems, highlight rows that contain top customers, show duplicates, and more. Excel ships with a large number of 'presets' that make it easy to create new rules without formulas. However, you can also create rules with your own custom formulas.
By using your own formula, you take over the condition that triggers a rule, and can apply exactly the logic you need. Formulas give you maximum power and flexibility. For example, using the 'Equal to' preset, it's easy to highlight cells equal to 'apple'. But what if you want to highlight cells equal to 'apple' or 'kiwi' or 'lime'?
Sure, you can create a rule for each value, but that's a lot of trouble. Instead, you can simply use one rule based on a formula with the: Here's the result of the rule applied to the range B4:F8 in this spreadsheet: Here's the exact formula used. = (A1 ) = (A1 ) =A1 100 = (A1 100,B1.
= ($C5 = 3 ) The dollar signs ($) lock the reference to columns C and D, and the is used to make sure both conditions are TRUE. In rows where the AND function returns TRUE, the conditional formatting is applied: Highlight top values (dynamic example) Although Excel has presets for 'top values', this example shows how to do the same thing with a formula, and how formulas can be more flexible. By using a formula, we can make the worksheet interactive — when the value in F2 is updated, the rule instantly responds and highlights new values.
The formula used for this rule is. = ( ($F$2,B2 )) For more details and a full explanation, see:. Article:. Article:.
Video: Troubleshooting If you can't get your conditional formatting rules to fire correctly, there's most likely a problem with your formula. First, make sure you started the formula with an equals sign (=). If you forget this step, Excel will silently convert your entire formula to text, rendering it useless.
To fix, just remove the double quotes Excel added at either side and make sure the formula begins with equals (=). If your formula is entered correctly, but is not triggering the rule, you may have do dig a little deeper. Normally, you can use the F9 key to check results in a formula, or use the Evaluate feature to step through a formula. Unfortunately, you can't use these tools with conditional formatting formulas, but you can use a technique called 'dummy formulas'. Dummy Formulas Dummy formulas are a way to test your conditional formatting formulas directly on the worksheet, so you can see what they're actually doing. This can be a big time-saver when you're struggling to get cell references working correctly.
In a nutshell, you enter the same formula across a range of cells that matches the shape of your data. This let's you see the values returned by each formula, and it's great way to visualize and understand how formula-based conditional formatting works. For a detailed explanation,.
Video: Limitations There are some limitations that come with formula-based conditional formatting. First, You can't apply icons, color scales, or data bars with a custom formula. You are limited to standard cell formatting, including number formats, font, fill color, and border options. 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 restrictions. If you see this error, and have a valid formula, you may be able to move the logic of the formula into a cell in the worksheet, then refer to that cell in the formula instead. If you are trying to use an array constant, try created a named range instead.
More CF formula resources.