Conditional formatting based on a formula part III

In part 1 and part 2 of this series, we gave several examples of conditional formatting with a formula, which allows you to use Excel formulas to create complex formatting rules. In this article, we will show you two complex examples of conditional formatting with a formula drawn from the realm of fraud detection.

Recall from the last two parts that to utilize Excel’s conditional formatting with a formula option you proceed as follows:

1. Select the range where the formatting will be applied.
2. After selecting Conditional Formatting from the Home Tab select New Rule.
3. Choose Use a Formula.
4.  With cell in upper left-hand corner of selected range type in a formula starting with =.
5. This formula will copy across and down selected range (watch your $ signs!) Whenever the copied formula is True, the desired format will be used.

In this article we will show you

• Given a list of invoices specified by ID number, date, vendor and amount paid, how can we highlight all information on duplicate invoices?
• An invoice is more likely to be fraudulent if the invoice was paid on or before the invoice date or paid on a weekend. How can we highlight these invoices?

Our work is in the workbook BeckerApril2020.xlsx.

Highlighting duplicate invoices

The worksheet BeckerApril2020.xlsx contains the invoice number, date, vendor and payment amount for 392 invoices. As shown in Figure 1, we want to highlight all invoices that occur at least twice in the data. For example, rows 13 and 53 are the same invoices. To create this format we proceed as follows:

Figure 1: Highlighting duplicate invoices

1. With the cursor in cell C5 Select the cell range C5:F396.
2. From the Home tab select Conditional Formatting and Choose New Rule Use a Formula.
3. Enter the formula  =COUNTIFS($C$5:$C$396,$C5,$D$5:$D$396,$D5,$E$5:$E$396,$E5,$F$5:$F$396,$F5)>1 and choose from the  Fill Option  on Format Cells orange fill. The formula we entered will be true if and only if columns C-F are the current row occur in at least one other row.

Highlighting suspicious invoices based on date paid

For 403 invoices, the worksheet “Paymentdates” contains the date an invoice was filed and the date it was paid. An invoice is considered suspicious if it was paid on or before the filing date or on a weekend. The following steps (see Figure 2) will highlight all the suspicious invoices.

Figure 2: Highlighting suspicious invoices based on day paid

1. Copying from G5 to G6:G407 the formula =WEEKDAY(F5,16) will enter a 1 if the paid date was a Saturday and a 2 if the paid date was a Sunday.
2. With the cursor in cell D5 select the cell range G5:G407  and enter the formats based on a Formula shown in Figure 3.

Figure 3: Formulas to highlight suspicious invoices

• The formula =$F5<$E5 will highlight in yellow the entire row if the invoice is paid before the invoice date.
• If the row has not been already highlighted in yellow, the formula =$F5=$E5 will highlight in orange each row in which the invoice is paid on the same day it was filed.
• If the row has not been already highlighted in yellow or orange, the formula =$G5<=2 will highlight in blue each row for which the invoice was paid on a weekend.

This concludes our discussion of conditional formatting with a formula. Stay tuned for our discussion on Excel Sparklines, which are mini-charts that summarize a row or column of data in a single cell.