WPS Office

Free All-in-One Office Suite with PDF Editor

correct-icon

Edit Word, Excel, and PPT for FREE.

correct-icon

Read, edit, and convert PDFs with the powerful PDF toolkit.

correct-icon

Microsoft-like interface, easy to use.

Free download

Windows • MacOS • Linux • iOS • Android

banner

How to use colors in excel formulas to highlight information

August 1, 2022
2.8K Views

Can you use colors in excel formulas? Yes, using colors in worksheets increase usability and visuality in several ways. Additionally, it can also be used to specify important information under a given condition so people could then identify certain information in seconds. You can also use conditional formatting and IF statement to highlight or specify the conditions.

If you are wondering whether can you use colors in excel formulas, here is your guide to it. So, keep reading.

Note: There is only one way to access conditional formatting as below

Use colors under certain conditions

1. Open the document in which you want to apply colors in formulas. Here is an example of test grades of students in terms of percentage. Many students passed while few failed. In this normal data sheet, it is very difficult to find who passed and who failed. So the conditional formatting can come in handy.

2. Before applying colors, you must use the IF statement. To do this, click on C3.

3. Now type equals sign and if. Then press Enter, the IF statement is activated

4. First click on cell B3 and then type

5. Now type comma, and then in inverted commas “Fail”

6. Again type comma, and then in inverted commas “Pass”. These conditions tell if the value is less than 40, type fail, otherwise pass.

7. Tab Enter key. The Pass word is typed because the student scored more than 40% grades.

8. Now click on the cell C3 again, click the right corner of the cell, drag vertically till B28. The function is applied to all students from A to Z.

9. In order to apply conditional formatting, click again on cell C3 and go to Conditional Formatting under Home tab. A drop-down menu appears.

10. Go to Highlight Cell Rules, a drop-down menu appears.

11. Click Text That Contains option.

12. A dialogue box “Text That Contains” appears. Now click the arrow at the right of blank field.

13. Now click C3 and press Enter. Notice the value is specified.

14. Now click on the down arrow. A drop box appears to select colors.

15. Choose “Light Red Fill” for this example.

16. Hit Ok. The text in cell C3 is filled with light red color.

17. Click on C3 cell, put mouse at bottom right corner first click and start dragging to end. All the text containing word Pass is highlighted.

18. In order to find which student failed, repeat the whole procedure but type Fail in “Text That Contains” dialogue box and then select “Green Fill with Dark Green Text” as the color.

19. When you press Enter nothing happens to C3 because it has word Pass.

20. Click and drag cursor from C3 to C28. All cells having Fail written will get highlighted in different colors.

So here is your answer of can you use colors in excel formulas. Need to edit Word/Excel/PPT file free of charge? download WPS Office edit files like without any cost. Download now! to get enjoyable working experience.

15 years of office industry experience, tech lover and copywriter. Follow me for product reviews, comparisons, and recommendations for new apps and software.