Conditional formatting is a feature of Excel which allows you to apply a format such as colors, icons, and data bars to a cell or a range of cells based on certain criteria. There are many ways to use Conditional Formatting, and you can also copy that format to another cell.
Basic Conditional Formatting
If you want to highlight the cell based on a value of a specified cell as criteria, then you can use conditional formatting with a custom formula.
In the example below, you will set conditional formats so that a cell:
- Turns green if it contains a value higher than 100 and
- Turns red if it contains a value equal to 50.
- Turns blue if it contains a value less than 20.
Conditional format if a cell contains a value higher than 100
Select the range you want to format. In our case A2:A15 is selected. On the Ribbon’s Home tab, click Conditional Formatting, to format the values greater than a specific one select Highlight Cells Rules and then choose the option Greater Than.
To drop down the list for formats click Custom Format, click the Fill tab, and click on the green fill color that you want. To close the Format Cells window click Ok, the cells with values greater than 100 now are colored green as we choose the color format.
In the A column could be 100 as a value and using the format we used above the 100 value so it won’t be highlighted, including 100 in highlighted values we should use another format rule.
Conditional formatting if it contains a value less than 20.
To color the low values in blue fill, you can apply a second conditional formatting rule to the cells. Select the cells to be formatted. In this example, cells A2:A15 are selected then on the Ribbon’s Home tab, click Conditional Formatting.
To format the less than values, click highlight cell rules, then click less than. In the less than the window, delete the value that appears, and click on cell C2, where the low value is entered.
Click the drop-down list for formats, and click Custom Format. In the Format Cells window, click the Fill tab, and click on the green fill color that you want.
Click OK to close the Format Cells window, and click OK to close the Less Than window. The cells with values greater than 100 are now colored green, and cells less than 20 are blue.
Conditional formatting if it contains a value is equal with 50.
Select the cells to be formatted. In this example, cells A2:A15 are selected, From the Home tab, click the Conditional Formatting command. A drop-down menu will appear to click Highlight Cell Rules and then choose Equal to.
In the Equal to the window, you have to delete the value that appears in the box, and then select the cell in this example C3 cell or a specific value in our case 50.
After choosing our desired format press OK and A6 cell which equals 50 will be formatted.
Conditional format if cell contains text
To auto-fill a cell with color in Excel given that it has text in it you can achieve this by applying conditional formatting and selecting a Rule Type for your range of values.
Do the following steps:
Select the range of cells for your data, like A1:A15. Then in the Home tab, go to Conditional Formatting, click on “New Rules” select the rule type as “Use a Formula to determine which cells to format” write the following formula, in formula section. Remember to give reference of the only 1st cell of your selected range of cells.
=ISTEXT(A1)
Click on “Format” button, In “Fill” tab, select the color for the text values you want to format with, click OK, and again click OK on new formatting rule window
You will get all the text values in your selected range of cells to be filled with your desired color scheme.
To highlight entire rows of cells containing the specific text, value or just be blank with the Conditional Formatting command in Excel, you can do as following:
Select the purchase table without its column headings. Click the Home > Conditional Formatting > New Rule
In the coming New Formatting Rule dialog box click to select the “Use a formula to determine” which cells to format in the select a rule type box:
In the format values where this formula is the true box, enter =$A1=”Apple” click the Format button.
In the formula of =$A1=”Apple,” $A1 is the cell you will check if contains the specific text or value, and “Apple” is the specific text, you can change them as you need. And this formula can only find out cells only containing the specific text or value.
If you want to highlight rows if cells begin with the specific text, you need to enter =LEFT($A1,5)=”Apple”; or to highlight rows if cells end with specific text, enter =RIGHT($A1,5)=”Apple.”
Now the Format Cells dialog box is opening. Go to the Fill tab, specify a background color, and click the OK button.
Click OK button to close the New Formatting Rule dialog box.
Then all rows containing the specific content cells in the selected range are highlighted if a $ sign is written before A(column) in the formula.
Copying Conditional Formatting to Another Cell
If you apply a conditional format to one or more cells and want to apply that format to other data on your worksheet, use Format Painter to copy the conditional formatting to that data.
Click on the cell that has the conditional formatting you want to copy. Click Home > Format Painter.
To paste the conditional formatting, drag the paintbrush across the cells or ranges of cells you want to format.
There are multiple ways you can specify the conditional format in a cell. You can even apply conditional format on one cell based on other cells criteria. The conditional format can also be applied to the whole row or column. For a customized solution to your specific problem, you can try our Excel expert help service using this link. It will save you hours of searching online and our expert will solve your problem in a 20-minute session. The first question is free.
Leave a Comment