Excel Functions Tutorials
- Excel Tools
- Excel Ribbons and Tabs
- Quick Access Toolbar in Excel (QAT)
- Features of MS Excel
- "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
- Auditing Tools 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 Dates
- 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
- Power Query in Excel
- 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
- Formatting Text in Excel
- Form Controls 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
- Icon Sets 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
- New Line in Excel Cell
- Null in Excel
- One Variable Data Table in Excel
- OneDrive Excel
- Page Setup in Excel
- Protect Workbook in Excel
- Unprotect Excel Workbook
- 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
- Ribbon 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 (18+)
- Logical Functions in Excel (17+)
- TEXT Functions in Excel (31+)
- Lookup Reference in Excel (46+)
- Maths Functions in Excel (53+)
- Date and Time Function in Excel (24+)
- Statistical Function in Excel (54+)
- Information Functions in Excel (5+)
- Excel Charts (66+)
- Excel Tips (202+)
- VBA (226+)
- Power Bi (45+)
Conditional Formatting in Excel is a technique where we format cells in a worksheet based on certain conditions, conditional formatting option is available in the styles section of the Home tab, there are various conditions provided by excel to use formatting on cells or a user can create a new rule for conditional formatting using a formula.
Table of Contents
- Conditional Formatting with Formulas
- How to apply Conditional formatting using GUI of excel
- How to use the Conditional Formatting Rule?
Conditional Formatting in Excel (with Formulas)
Conditional formatting is a feature in excel spreadsheet that allows you to apply specific formatting to cells/range that meet the specified criteria/condition. Mostly formatting rule is used as color-based formatting to highlight or distinguish among data and information stored in an excel sheet.
- Conditional formatting formula provides the functionality to highlight cells with a certain colour, depending on the cell’s value or specific condition.
- It is a fantastic way to quickly visualize data in an excel sheet. By conditional formatting rules in Excel, you can highlight data with provided criteria.
- It allows you to visually analyze your data, based on a large number of condition types,
- Greater than, Less than, Between
- Above / Below Average
- Top / Bottom 10
- Top / Bottom 10%
- Duplicates / Unique
- Dates – Dynamic or a fix date range
- Text containing
How to apply Conditional formatting in excel.
It will highlight the Cell A1 with red colour if the value in A1 cell will be 1.
- Start Excel, and then open a new Excel worksheet.
- In the Excel worksheet, select any cell A1.
- Click Conditional Formatting in Excel Styles group, and then click New Rule.
- Click Use a formula to determine which cells to format under Select a Rule Type.
- Click inside the Format values where this formula is true Then, select the cell that you want to use for the conditional formatting in excel.
- Modify the value in step 6 to be =$A$1=1.
- Click Format.
- In the Format Cells dialogue box, click the Fill
- Click the color “red,” and then click OK.
- In the New Formatting Rule dialogue box, click OK.
- In cell A1, type 1, and then press ENTER.
- Verify that the color of the cell B1 changed to red.
- Close the Excel worksheet.
How to use the Conditional Formatting Excel Rule?
Let understand the working with simple examples.
Conditional Formatting Example #1
Highlight the data that is greater than 30.
Choose Highlight Cells Rules > Greater Than
Enter the number.
Below is the result:
Highlight the data with duplicate values.
Choose Highlight Cells Rules > Duplicate Values
Click on Duplicate and OK
The result will be:
Conditional Formatting Example #3
Apart from simple rules like greater than and duplicates, there are others like “A Date Occurring…”. This condition is based on dates. Many conditions will have sub-conditions. This is one such example.
Here, we will select all the dates and apply the conditional format.
Choose Highlight Cells Rules > A Data Occurring
Within this dialogue box, there are many date – related options available.
We will use ‘This Week’ as we want to highlight all the dates that fall in the current week. Likewise, you can use any other setting, as needed.
So the result will be:
Conditional Formatting Example #4
Here we have a given data set of date and count and we have to apply the multiple formatting rule.
First, we highlight the cell value that is between 31 and 43.
Select color yellow fill with Dark yellow text.
The Result will be:
And then apply another formatting on cells who have less than 0 value with color of Light Red Fill with Dark Red Text.
And the Final Result will look like this:
First, we will use Data bars.
Go to Data Bars > Select Blue Data Bar
Now, we will use Color Scales.
Go to Color Scales > Select any Color you want to.
Now, we will use Icon Sets.
Go to Icon Sets > Select any icon you want to.
Finally, the table will look like this
Conditional Formatting can be used in VBA
We can apply the formatting rule using VBA here is the example as follows:
.FormatConditions.Add Type:=xlExpression, _
.FormatConditions(1).Interior.ColorIndex = 46
You can download this Conditional Formatting template here – Conditional Formatting Excel Template
This has been a guide to Conditional Formatting in Excel. Here we discuss how to use Conditional Formatting formula along with excel example and downloadable excel templates. You may also look at these useful functions in excel –
- How to Apply Conditional Formatting for Blank Cells?
- How to Add Formula Text in Excel?
- Conditional Formatting in Pivot Table
- Conditional Formatting
- Excel Dynamic Tables
- Remove Duplicates in Excel
- How to Highlight Every Row in Excel?
- Convert Numbers to Text in Excel
- Paste Special in Excel
- MS Excel Training Course Online
- Column Chart in excel