Aung Shine
Sometimes we need to format cells according to our requirements in our Excel datasheet. But, formatting with the Format Cells feature takes some time which is quite inconvenient. In this article, we will show you the simple ways to Format Cell based on Formula in Excel.
To illustrate, I’m going to use a sample dataset as an example. For instance, the following dataset represents the Salesman, Product, and Net Sales of a company.
Download Practice Workbook
To practice by yourself, download the following workbook.
13 Examples to Format Cell Based on Formula in Excel
1. Format Cell Based on Another Cell with Formula in Excel
We can use different formulas to format cells in an Excel datasheet. But first, we have to know where we should type the formulas. In our first method, we’ll just Compare the Net Sales. Therefore, follow the steps below to know where you should create the formula and then, format the cells.
STEPS:
- First, select the range D5:D10.
- Next, under the Home tab, select New Rule from the Conditional Formatting drop-down list.
- As a result, a dialog box will pop out. Here, select Use a formula to determine which cells to format in Rule Type.
- Then, in Format values where this formula is true box, type the formula:
=$D5>$D$5- After that, press Format.
- Consequently, the Format Cells dialog box will pop out. There, under the Fill tab, select a color.
- Subsequently, press OK.
- At last, you’ll see the highlighted cells which are greater than D5.
Read More: How to Use Excel Cell Format Formula(4 Effective Methods)
2. Apply Formula to Format Rows Based on a Text Criteria
We can apply a formula based on text criteria to format the entire row. In the below dataset, we’ll look for product AC. And then, format the rows where the product is present. So, follow the below process to perform the task.
STEPS:
- First of all, select the range of cells.
- Next, go to Home > Conditional Formatting > New Rule.
- A window will pop out. Here, choose the Rule Type: Use a formula to determine which cells to format.
- Then, in the field: Format values where this formula is true, type the formula:
=$C5="AC"- After that, select Format.
- Another dialog box will pop out. There, under the Fill tab, select any color.
- Subsequently, press OK.
- Lastly, you’ll see the desired changes.
Read More: How to Format Text Using Excel VBA (12 Methods)
3. Formatting Rows with Formula Based on a Number of Criteria
In this method, we’ll format the entire row based on number criteria. We’ll format the rows where the net sales exceed $10,000. Hence, learn the process to do the operation.
STEPS:
- Firstly, select the range in your dataset.
- Then, go to Home > Conditional Formatting > New Rule.
- A window will pop out. Here, select the Rule Type: Use a formula to determine which cells to format.
- Subsequently, in the field: Format values where this formula is true, type the formula:
=$D5>10000- After that, press Format.
- Next, select any color to fill the rows.
- Afterward, press OK.
- Lastly, it’ll return the desired rows in the specified color.
Read More: How to Custom Format Cells in Excel(17 Examples)
4. Format Odd Number Cells in Excel Based on Formula
Sometimes, we need to find the odd numbers in a range and format them. Using the ISODD function makes this process a lot easier. Therefore, follow the below steps to learn the method.
STEPS:
- In the beginning, select the range D5:D10.
- Now, go to Home > Conditional Formatting > New Rule.
- A dialog box will pop out. Here, choose the Rule Type: Use a formula to determine which cells to format.
- In the field: Format values where this formula is true, type the formula:
=ISODD(D5)- Press Format.
- As a result, the Format Cells dialog box will pop out. There, select a color under the Fill tab.
- Subsequently, press OK.
- In the end, you’ll see the odd numbers in the selected color.
Read More: How to Use Format Painter in Excel
5. Use Excel AND Function to Format Cells
We can use AND function when we need to format cells based on multiple criteria. In the following dataset, we’ll highlight the rows which contain the product Cable and have net sales below $10,000. So, follow and learn the steps.
STEPS:
- First, select the range B5:D10.
- Under the Home tab, select New Rule from the Conditional Formatting drop-down list.
- As a result, a dialog box will pop out. Here, select Use a formula to determine which cells to format in Rule Type.
- Then, in Format values where this formula is true box, type the formula:
=AND($C5="Cable", $D5<10000)- After that, press Format.
- Consequently, the Format Cells dialog box will pop out. There, under the Fill tab, select a color.
- And then, press OK.
- At last, it’ll return the formatted rows.
6. Format Cells with OR Function in Excel
In our previous method, both of the conditions needed to be satisfied. But, in this example, we’ll format the rows for any of the conditions being true. For this reason, we’ll use the Excel OR function. Now, learn the steps below to do the operation.
STEPS:
- Select the range of cells at first.
- After that, go to Home > Conditional Formatting > New Rule.
- A window will pop out. Here, choose the Rule Type: Use a formula to determine which cells to format.
- Next, in the field: Format values where this formula is true, type the formula:
=OR($C5="Cable", $D5<10000)- Then, select Format.
- As a result, another dialog box will pop out and select any color from the Fill tab.
- Subsequently, press OK.
- Lastly, it’ll return the expected outcome.
7. Apply Formula to Format Blank Cells
A lot of times we have blank cells in our dataset. Highlighting the blank cells with a single formula helps us to edit them and thus saves our time. We’ll use the ISBLANK function in Excel to find the empty cell and subsequently format them. So, follow along with the procedure to Format Cell based on Formula in Excel.
STEPS:
- Firstly, select the range B5:D10.
- Then, under the Home tab, select New Rule from the Conditional Formatting drop-down list.
- As a result, a dialog box will pop out. Here, select Use a formula to determine which cells to format in Rule Type.
- Next, in Format values where this formula is true box, type the formula:
=ISBLANK(B5)- After that, press Format.
- Here, the Format Cells dialog box will pop out. There, under the Fill tab, select a color.
- And then, press OK.
- Finally, it’ll highlight the blank cells.
Similar Readings
- Use Format Painter Shortcut in Excel (5 Ways)
- How to Change Time Format in Excel (4 Ways)
- Copy Formatting in Excel (3 Processes)
- How to Copy Cell Format in Excel (4 Methods)
- Formula to Copy Cell Value and Format in Excel (5 Uses)
8. Format Non-Blank Cells Based on Formula in Excel
Additionally, we can also highlight the Non–Blank cells. For that purpose, we’ll just use the NOT function before the ISBLANK function. The NOT function simply converts TRUE to FALSE and FALSE to TRUE. Therefore, learn the steps below to know how to format the non-blank cells.
STEPS:
- First, select the range in your dataset.
- Go to Home > Conditional Formatting > New Rule.
- A window will pop out. Here, select the Rule Type: Use a formula to determine which cells to format.
- Subsequently, in the field: Format values where this formula is true, type the formula:
=NOT(ISBLANK(B5))- After that, press Format.
- Next, select any color to fill the cells.
- Afterward, press OK.
- Lastly, you’ll see the required changes.
9. Excel SEARCH Function to Format Cells
Moreover, we can use the SEARCH function to find a particular text and format them afterward. In this dataset, we’ll search for the product Cable and then, format the entire row.
STEPS:
- In the beginning, select the range B5:D10.
- Now, go to Home > Conditional Formatting > New Rule.
- A dialog box will pop out. Here, choose the Rule Type: Use a formula to determine which cells to format.
- Next, in the field: Format values where this formula is true, type the formula:
=SEARCH("Cable",$C5)>0- Then, press Format.
- As a result, the Format Cells dialog box will pop out. There, select a color under the Fill tab.
- Subsequently, press OK.
- In the end, you’ll see the highlighted rows which contain Cable.
10. Format Duplicate Cells Based on Formula in Excel
In this method, we’ll apply the COUNTIF function to find the duplicate cell values. Subsequently, we’ll format them. Now, learn the steps below to perform the task.
STEPS:
- First, select the range B5:D10.
- Now, under the Home tab, select New Rule from the Conditional Formatting drop-down list.
- As a result, a dialog box will pop out. Here, select Use a formula to determine which cells to format in Rule Type.
- Next, in Format values where this formula is true box, type the formula:
=COUNTIF($C$5:$C$10,$C5)>1- After that, press Format.
- Here, the Format Cells dialog box will pop out. There, under the Fill tab, select a color.
- Press OK.
- At last, it’ll return the rows with duplicate cells.
11. Format Cells with Excel AVERAGE Function
We can use the AVERAGE function in Excel to compare the Net Sales of each salesman to the average of the total. In this example, we’ll highlight the rows which have net sales greater than the average. Hence, follow the procedure to Format Cells based on Formula in Excel.
STEPS:
- Firstly, select the range of cells.
- Then, go to Home > Conditional Formatting > New Rule.
- A window will pop out. Here, choose the Rule Type: Use a formula to determine which cells to format.
- Next, in the field: Format values where this formula is true, type the formula:
=$D5>AVERAGE($D$5:$D$10)- Select Format after that.
- As a result, another dialog box will pop out and select any color from the Fill tab.
- Subsequently, press OK.
- Finally, you’ll get the desired output.
12. Format Cells with Top 3 Values Based on Formula
The LARGE function in Excel returns the highest values. Here, we’ll use this function to format the rows with 3 top net sales amounts.
STEPS:
- In the beginning, select the range B5:D10.
- Now, go to Home > Conditional Formatting > New Rule.
- A dialog box will pop out. Here, choose the Rule Type: Use a formula to determine which cells to format.
- Next, in the field: Format values where this formula is true, type the formula:
=$D5>=LARGE($D$5:$D$10,3)- Then, press Format.
- As a result, the Format Cells dialog box will pop out. There, select a color under the Fill tab.
- After that, press OK.
- In the end, it’ll return the expected output.
13. Format Entire Row with Formula When Any Cell is Blank
In our last example, we’ll show how to format an entire row when there is a blank cell. We’ll use the COUNTBLANK function to do the operation.
STEPS:
- First, select the range B5:D10.
- Then, under the Home tab, select New Rule from the Conditional Formatting drop-down list.
- As a result, a dialog box will pop out. Here, select Use a formula to determine which cells to format in Rule Type.
- Next, in Format values where this formula is true box, type the formula:
=COUNTBLANK($B5:$D5)- Press Format now.
- The Format Cells dialog box will pop out. There, under the Fill tab, select a color.
- And then, press OK.
- Eventually, it’ll return the dataset highlighting the rows which have blank cells.
Conclusion
Henceforth, you will be able to Format Cell based on Formula in Excel with the above-described methods. Keep using them and let us know if you have any more ways to do the task. Don’t forget to drop comments, suggestions, or queries if you have any in the comment section below.
Related Articles
- Uses of CELL Color A1 in Excel (3 Examples)
- VBA to Format Cell in Excel (12 Ways)
- Dealing with Time Format in Excel (5 Suitable Ways)
- How to Repeat Formula Pattern in Excel (Easiest 8 ways)
- How to Use Format Painter in Excel for Multiple Cells (6 Quick Ways)
- How to Copy Formatting in Excel to Another Sheet (4 Ways)
