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.

 

Excel Tutorial - Conditional Formatting | Cemtech Training Center Puchong Selangor, Melaka

 

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.

 

Excel Tutorial - Conditional Formatting | Cemtech Training Center Puchong Selangor, Melaka

 

Format Invoices that will due in < 14days

3.  Click on the New Rule.

 

Excel Tutorial - Conditional Formatting | Cemtech Training Center Puchong Selangor, Melaka

 

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

 

Excel Tutorial - Conditional Formatting | Cemtech Training Center Puchong Selangor, Melaka

 

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.

Excel Tutorial - Conditional Formatting | Cemtech Training Center Puchong Selangor, Melaka

 

6.  Click OK again.

Excel Tutorial - Conditional Formatting | Cemtech Training Center Puchong Selangor, Melaka

 

7.  The first conditional formatting rule is created

 

Excel Tutorial - Conditional Formatting | Cemtech Training Center Puchong Selangor, Melaka

 

Format Invoices that Due Today

 

8.  Click on the New Rule button.

 

Excel Tutorial - Conditional Formatting | Cemtech Training Center Puchong Selangor, Melaka

 

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

 

Excel Tutorial - Conditional Formatting | Cemtech Training Center Puchong Selangor, Melaka

 

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

 

Excel Tutorial - Conditional Formatting | Cemtech Training Center Puchong Selangor, Melaka

 

Format Invoices that Overdue with Red Color

 

12.  Click on the New Rule button again.

 

Excel Tutorial - Conditional Formatting | Cemtech Training Center Puchong Selangor, Melaka

 

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().

 

Excel Tutorial - Conditional Formatting | Cemtech Training Center Puchong Selangor, Melaka

 

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.

 Excel Tutorial - Conditional Formatting | Cemtech Training Center Puchong Selangor, Melaka

 

15.  Click OK again.

 

Excel Tutorial - Conditional Formatting | Cemtech Training Center Puchong Selangor, Melaka

 

16.  The third conditional formatting rule is created

Excel Tutorial - Conditional Formatting | Cemtech Training Center Puchong Selangor, Melaka

 

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

 

Excel Tutorial - Conditional Formatting | Cemtech Training Center Puchong Selangor, Melaka

 

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 

Cempaka Technology Sdn Bhd

Pusat Latihan Komputer Cempaka
~Your HRDC Premiere Training Provider~
Unit 6, Level 4, SetiaWalk Mall (Block K), SetiaWalk, Persiaran Wawasan, Pusat Bandar Puchong, 47160 Puchong, Selangor, Malaysia.
Tel: 603-80684461, Fax: 603-80684240
1-28, Jalan PM4, Plaza Mahkota, 75000 Melaka, Malaysia
Tel: 606-2835955, Fax: 606-2845955