![custom formatting excel nonzero numbers custom formatting excel nonzero numbers](https://8ylfd337evj32wz3l2begfi1-wpengine.netdna-ssl.com/wp-content/uploads/2018/06/image.png)
There are any number of other formulas that could also be used. The AND function makes sure that only when both criteria are satisfied will the formula return True and the format be applied. The formula used in step 5 checks to make sure that the value is 0 and that the cell is not blank.
![custom formatting excel nonzero numbers custom formatting excel nonzero numbers](https://i.stack.imgur.com/FuLKA.png)
The formatting is applied to the range of cells you selected in step 1.
![custom formatting excel nonzero numbers custom formatting excel nonzero numbers](https://d295c5dn8dhwru.cloudfront.net/wp-content/uploads/2019/06/10101050/717.png)
Click OK to close the New Formatting Rule dialog box.
![custom formatting excel nonzero numbers custom formatting excel nonzero numbers](https://i.stack.imgur.com/Veu7J.gif)
Excel displays the Format Cells dialog box. In the formula box enter the formula =AND(A2=0,A2"").In the Select a Rule Type area at the top of the dialog box, choose Use a Formula To Determine Which Cells to Format.The formatting is applied to the range of cells you selected in step 1.Īnother solution is to combine your two conditions into a single condition. Click OK to close the Conditional Formatting Rules Manager dialog box.Click the down arrow to move the rule you just created to the second position in the list of rules.Excel again displays the Conditional Formatting Rules Manager, and the rule you just defined is the first one in the list. Click OK to close the Format Cells dialog box.Use the controls in the dialog box to modify the formatting, as desired.In the value box for Condition 2, enter 0.Using the second drop-down list for the rule, choose Equal To.Using the first drop-down list for the rule, choose Cell Value.In the Select a Rule Type area at the top of the dialog box, choose Format Only Cells That Contain.Excel again displays the New Formatting Rule dialog box. Make sure the Stop If True check box is selected for the rule.(Note that you didn't specify any formatting for this rule that's fine.) Excel closes the New Formatting Rule dialog box and again displays the Conditional Formatting Rules Manager dialog box, this time with your new rule visible. Using the first drop-down list for the rule, choose Blanks.Excel displays the New Formatting Rule dialog box. Excel displays the Conditional Formatting Rules Manager dialog box. Excel displays a palette of options related to conditional formatting. With the Home tab of the ribbon displayed, click the Conditional Formatting option in the Styles group.(For this example, I'll assume that you've selected the range A2:A99.) Select the range you want conditionally formatted.This works because if the first condition is satisfied (the cell is blank), the second condition is never tested. The condition that checks for blanks doesn't need to adjust any formatting, but the one that checks for zero values can. The first condition checks for the blanks, and the second checks for zero values. One solution is to apply a conditional format that uses two conditions. There are several different solutions to this predicament. The problem is, if you just add a conditional format that highlights the cells to see if they are zero, then the condition will also highlight any cells that are blank, since they contain a "zero" value, as well. You might want to use a conditional format on the imported information to highlight any zero values. The information contains numeric values, but it can also contain blanks. Let's say that you routinely import information from another program into Excel.