Excel Functions Tutorials
- Excel Tools
- Excel Ribbons and Tabs
- Quick Access Toolbar in Excel (QAT)
- "Save As" Shortcut in Excel
- Accounting Number Format in Excel
- Add-Ins in excel
- Add Filter in Excel
- Advanced Filter in Excel
- Auto Filter In Excel
- Auto Format Excel
- AutoFill in Excel
- Analysis ToolPak in Excel
- ANOVA in Excel
- Border in Excel
- Checkbox in Excel
- Check Mark in Excel (? Tick Symbol)
- Combo Box in Excel and VBA
- Conditional Formatting in Excel
- Conditional Formatting with Formulas
- Conditional Formatting for Blank Cells
- Conditional Formatting Based on Another Cell Value
- Conditional Formatting in Pivot Table
- Consolidate Data in Excel
- Comma Style in Excel
- CSV vs Excel
- Data Bars in Excel
- Data Table in Excel
- Data Validation Excel
- Data Model in Excel
- Developer Tab in Excel
- Descriptive Statistics in Excel
- Dynamic Named Range in Excel
- Drawing in Excel
- Excel Fill Handle
- Excel Fill Down
- Error Bars in Excel
- Excel Forms for Data Entry
- Excel Tables
- Excel Power View
- Exponential Smoothing in Excel
- Filters in Excel
- Flash Fill in Excel
- Freeze Panes in Excel
- Freeze Columns in Excel
- Freeze Cells in Excel
- Format Painter in Excel
- Shortcut for Format Painter in Excel
- F-Test in Excel
- Goal Seek in Excel
- Gridlines in Excel
- Heat Map in Excel
- 3D Maps in Excel
- Header and Footer in Excel
- Insert Button in Excel
- Insert / Draw Line in Excel
- Insert Function in Excel
- List Box in Excel VBA
- Lock Cells in Excel
- Macros in Excel
- Enable Macros in Excel
- Merge and Center in Excel
- Merge Cells in Excel
- Merge Tables in Excel
- Name Box in Excel
- Name Range in Excel
- Null in Excel
- One Variable Data Table in Excel
- OneDrive Excel
- Protect Workbook in Excel
- Pivot Table in Excel
- Pivot Table Examples
- Pivot Table Filter
- Pivot Table Slicer
- Paste Special in Excel (With Top 10 Shortcuts)
- Quick Analysis Tools in Excel
- Radio Button in Excel
- Recording Macros in Excel
- Regression Analysis in Excel
- Scenario Manager in Excel
- Scroll Bars in Excel
- Scroll Lock in Excel
- Slicers in Excel
- Solver in Excel
- Sort by Color in Excel
- Sort by Number in Excel
- Sort Data in Excel
- Sparklines in Excel
- Spell Check in Excel
- Split Panes in Excel (Horizontally, Vertically, Cross Split)
- Status Bar in Excel
- Text to Columns in Excel
- Timeline in Excel
- Toolbar on Excel
- Track Changes in Excel
- Trend Line in Excel
- Two-Variable Data Table in Excel
- Watch Window in Excel
- Wrap Text in Excel
- XML in Excel
- Financial Functions in Excel (17+)
- Logical Functions in Excel (15+)
- TEXT Functions in Excel (29+)
- Lookup Reference in Excel (44+)
- Maths Functions in Excel (52+)
- Date and Time Function in Excel (22+)
- Statistical Function in Excel (50+)
- Information Functions in Excel (5+)
- Excel Charts (48+)
- Excel Tips (178+)
- VBA (162+)
As we do conditional formatting in excel we also have conditional formatting in VBA, and for this we use format.condtions property, this property uses a type an operator and formulas, to use Format. Conditions property we also use the add method with it for the instructions in it.
Conditional Formatting with Formulas in Excel
By default excel has already provided us with many types of conditional formatting available to do with our data but if any user wants the formatting done on a specific based criteria or formula we can do so, in the home tab of conditional formatting section when we click on new rule we will find an option for conditional formatting with formulas where we can write formulas which will define the cells to format.
Conditional formatting with Formula is nothing but changing the format of the cells based on the condition or criteria given by the user. You might have used conditional formatting to highlight the top value in the range, highlight duplicate values as well.
A good thing is we have formulas in conditional formatting in excel. All the formulas should be logical the result will be either TRUE or FALSE. If the logical test pass then we will get conditional formatting and if the excel logical test fails then we will get nothing.
Overview of the Conditional Formatting Formula Window
Let me introduce you to the conditional formatting with formula window.
In the below section of the article, we will see a wide variety of examples for applying conditional formatting with formulas.
How to Use Excel Conditional Formatting with Formulas?
Following are the examples of Excel Conditional Formatting with Formulas.
#1 – Highlight Cells which has Values Less than 500
Assume below are the sales numbers you are working with and below is the sales price per unit.
Step 1: Go to Conditional Formatting and click on New Rule.
Step 2: Now select “Use a formula to determine which cells to format”.
Step 3: Now under “Format values where a formula is true” apply the formula as A2<500 and then click on Format to apply the excel formatting.
Step 4: Select the format as per your wish.
Step 5: Now you can see the preview of the format in the preview box.
Step 6: Now click on OK to complete the formatting. Now it has highlighted all the cells which have a number <500.
#2 – Highlight One Cells Based on Another Cell
We can highlight the one cell based on another cell value. Assume we have Product Name and Product Price data in the first two columns.
From the above table, we need to highlight the products if the sale price is >220.
Step 1: Select the product range and then, Go to conditional formatting and click on New Rule.
Step 2: In the formula apply the formula as B2 > 220.
Step 3: Click on Format key and apply the format as per your wish.
Step 5: Click on OK, we have formatting ready.
#3 – Highlight All the Empty Cells in the Range
Assume below is the data you have.
In the above data, you have to highlight all the cell which are blank or empty. We need to use the ISBLANK formula in the Excel conditional formatting.
Apply below formula in the formulas section.
Select the formatting as per your wish.
Click on ok it will highlight all the empty cells in the selected range.
#4 – Use AND Function to Highlight Cells
Consider below data for this formula.
If the region is Central and sales value is >500 we need to highlight the complete row.
Step 1: Select the entire data first.
Step 2: Here we need to use AND excel function, because we need to test two conditions here and both the conditions should be TRUE. Apply below formula.
Step 3: Click on Format.
Step 4: Go to FILL and select the required color and effects.
Step 5: Click on OK. We will see the rows highlighted if both the conditions are TRUE.
#5- Use OR Function to Highlight Cells
Consider the below data for the example.
In the above data range, I want to highlight the city names Bangalore and Mysore.
Step 1: Select the data first and go to Conditional Formatting then, click on New Rule.
Step 2: Since we need to highlight either of the two value cells, apply the OR excel function.
Step 3: Click on format and select the required format.
Step 4: Once the formatting is applied click on OK to complete the task. A formula would highlight all the cells which have values Bangalore & Mysore.
#6 – Use COUNTIF Function to Highlight Cells
Assume you are working with the customer database. You need to identify all the clients whose names appearing more than 5 times in the list. Below is the data.
Select the data and apply COUNTIF formula in the formula section.
Click on the format and apply the required formatting.
Now formula will highlight all the name if the count if more than 5.
Amber is the only name which appears more than 5 times in the list.
#7 – Highlight Every Alternative Row
We can also have the formula to highlight every alternative row of the data. Assume below is the data you are working on.
Select the data and apply the below formula.
=MOD(ROW (), 2)
Apply to format as per your wish and click on OK, we would have every alternative row highlighted by the formula.
#8 – Highlight Every Alternative Column
Like how we have highlighted the every alternative row similarly we can highlight every alternate column. Consider the same data for the example.
Apply below formula.
Copy above formula and apply in formula section of the conditional formatting.
Click on OK, it will highlight the alternate row.
If you wish to leave out first column and highlight from the second column you need to use below formula.
It would highlight the alternative column starting from the second column.
Things to Remember
- Conditional formatting accepts only logical formulas whose results are only either TRUE or FALSE.
- Preview of conditional formatting is just the indication of how formatting looks.
- Never use absolute reference as in the formula. When you are applying the formula and if you select the cell directly it would make it as an absolute reference, you need to make to a relative reference in excel.
This has been a guide to Excel Conditional Formatting with Formulas. Here we discuss how to use Conditional formatting using formulas like COUNTIF, AND, OR, MOD etc along with practical examples and downloadable excel template. You may learn more about excel from the following articles –