Excel Tutorial - Conditional Formatting

 

This is tutorial Conditional Formatting for Excel.

 

Here is a sample on how you can use the Conditional Formatting feature in Excel to help in showing you the status of your invoices:

  • Invoices that due today
  • Invoices that will be overdue within 14 days
  • Invoices that already overdue.

1. Enter the following date to the worksheet in cell A3:E3.

 

 

2.  Highlight cell C4:C13, which you would like to apply the conditional Formatting. OnHome tab, in Styles panel, click on Conditional Formatting, and select Manage Rules.

 

 

Format Invoices that will due in < 14days

3.  Click on the New Rule.

 

 

4.  Select the second rule type. Choose less than from the drop down menu and set it to =TODAY() + 14.

 

 

5.  Click on the Format button, and choose any format that you want. For our case here choose Dark Blue for the font color, and light Blue for the fill color, then click OK.

 

6.  Click OK again.

 

7.  The first conditional formatting rule is created

 

 

Format Invoices that Due Today

 

8.  Click on the New Rule button.

 

 

9.  Select the second rule type. Choose equal to from the drop down menu and set it to =TODAY() .

 

 

10.  Click on the Format button, and choose any format that you want. For our case here choose Dark Green for the font color, and light green for the fill color, then click OK.

 

11.  The second conditional formatting rule is created

 

 

Format Invoices that Overdue with Red Color

 

12.  Click on the New Rule button again.

 

 

13.  Select second rule: “Format only cells that contain”. Under Edit the Rule Description, choose less than from the drop down menu and set it to =TODAY().

 

 

14.  Click on the Format button, and choose any format that you want. For our case here choose red for the font color, and pink for the fill color, then click OK.

 

 

15.  Click OK again.

 

 

16.  The third conditional formatting rule is created

 

17.  Click OK to view the result. Here is the result.

 

 

Things to take note:

The order of the rules is important. The rules to be evaluated first need to be place on top of other rules.

 

By Bee Bee Ong  | Google

 

Cempaka Technology Sdn Bhd

Pusat Latihan Komputer Cempaka
~Your HRDC Premiere Training Provider~
64-2, Jalan Puteri 2/2, Bandar Puteri Puchong, 47100 Puchong Selangor, Malaysia. Tel: 603-80684461
1-28, Jalan PM4, Plaza Mahkota, 75000 Melaka. Tel: 606-2835955
Instant SSL