Conditional Formatting in Excel is a powerful tool that allows users to highlight cells based on specified conditions. It enables formatting based on cell values or formulas, making it versatile for various data analysis needs. Despite being often overlooked; it offers significant capabilities for improving data visualization and analysis within Excel spreadsheets.
In this guide, we will dive into the art of Conditional Formatting to its fullest potential, from basic to advanced conditional formatting tactics.
Watch my YouTube video on this topic for a clear understanding of this blog post. Click here to go to youtube video.
Key insights:
1. We will highlight cells that contains N/A.
2. We will highlight empty cells.
3. We will highlight row if it contains N/A or Empty values in its all-specified columns.
4. We will highlight rows where the specified cell’s value remains unchanged. For example: Finding rows where the first 3 min price remains unchanged.
We have taken Stock Market Data to run the test cases.
Highlighting cells with N/A values
In Conditional formatting, there needs to be condition. Here condition is if value of a cell is N/A then it satisfies the condition. In short, formatting a cell depending on its value.
Here are steps to highlight the cells with “N/A”:
- Select the dataset in which you want to highlight N/A cells.
- Go to Home -> Conditional Formatting –> Highlight Cells Rules > Equal To
- Then insert the value, in this case “N/A” and press ok button.
- Output: Highlighted cells with N/A values.
Highlighting empty Cells
Here condition is if the cell is empty then it satisfies the condition.
Here are steps to highlight the cells that are empty:
- Select the dataset in which you want to highlight blank cells.
- Go to Home > Conditional Formatting > Manage Rules > New Rule
- Use a formula to determine which cells to format > Enter formula “=LEN(A1) = 0”
- Format as you want.
Output : where the empty cells highlighted with orange color.
Highlighting Rows that contains all N/A and Empty value for all of its specified Cells
Here condition is if the symbol/row contains only N/A and Empty columns in its all cells then it satisfies the condition.
- Here the new formula will be > `=AND(($C1:$L1=”’’)+($C1:$L1=”N/A”))`
Output : where the empty cells highlighted with orange color.
Highlight rows where the specified cell’s value remains unchanged.
Here condition is if the symbol/row contains similar values for first three min columns then it satisfies the condition.
- Here the new formula `=AND(($C1=:$D1,$D1=$E1,$C1<>”N/A’’)`
Output : The Symbols where there is no transaction occurs at the first three minute.
We can customize the formula by ourselves to meet specific business logic. For example, we can easily find in which time specific share price remain higher and which times it remains lower and so on. Here is another output where I have list of symbols those prices doesn’t change over five minutes after opening.
