Conditional Formatting in Excel

Conditional formatting applies formatting to cells depending on the value of the cells. This can be used to highlight values that are lower or higher than average e.g. when calculating profit or loss. Users can apply suitable formatting depending on the rules set for a range of cells. This makes viewing values in a spreadsheet easier. Conditional formatting is often used when creating budgets so users can view profit or loss at a glance.

1. Open a workbook containing a budget where profit and loss for each month or week is calculated

2. Highlight the range of cells that contain the profit or loss within the budget

3. On the Home tab in the Styles group, select Conditional Formatting

4. Select New Rule

5. Select Format Only Cells that Contain

6. Set the Rule Description as Cell Values less than 0

7. Click on the Format button

8. Choose a Red font colour

9. Click OK

10. Click OK again

11. Formatting has been applied to any cells with a value less than €0. This will highlight all of the weeks/months where there has been a loss

12. Again, highlight the range of cells that contain the profit or loss within the budget

13. On the Home tab in the Styles group, select Conditional Formatting

14. Select New Rule

15. Select Format Only Cells that Contain

16. Set the Rule Description as Cell Values greater than 0

17. Click on the Format button

18. Choose a Green font colour

19. Click OK

20. Click OK again

21. Formatting has been applied to any cells with a value greater than €0. This will highlight all of the weeks/months where there has been a profit

22. On the Home tab in the Styles group, select Conditional Formatting

23. Select Manage Rules

24. Select the Green font rule

25. Click on the Delete Rule button

26. Click OK

27. The cells will no longer be formatted to appear green when a profit is made. Only cells that have a value of less than 0 (a loss) will be highlighted in red

28. Save the worksheet

To learn more about advanced Excel, check out the ICDL Advanced Excel eBook available now from Amazon & Apple Books: