In the first two parts of this series, we explored the capabilities of conditional formatting with formulas in Excel, showcasing its utility in creating intricate formatting rules. In this installment, we delve into the realm of fraud detection, presenting two complex examples where conditional formatting with formulas proves invaluable. As before, the step-by-step guide will aid in comprehending and implementing these advanced techniques.
Recap:
Before delving into the new examples, let's recap the process of utilizing Excel's conditional formatting with a formula:
Select the Range:
Identify the range where the formatting will be applied.
Access Conditional Formatting:
From the Home Tab, select Conditional Formatting, and then choose New Rule.
Choose "Use a Formula":
Opt for the "Use a Formula" option to enable formula-based formatting.
Enter the Formula:
In the upper left-hand corner of the selected range, input a formula starting with the equals sign (=). This formula will be copied across and down the selected range, determining when the desired format is applied.
Now, let's explore the two examples presented in this article.
Example 1: Highlighting Duplicate Invoices
In the workbook "VishalApril2020.xlsx," the worksheet contains data on invoices specified by ID number, date, vendor, and amount paid. The goal is to highlight all information on duplicate invoices. For instance, if the same invoice appears more than once in the data, we want to draw attention to these duplicates.
Figure 1: Highlighting duplicate invoices
Formula:
excel
Copy code
=COUNTIFS($C$5:$C$396, $C5, $D$5:$D$396, $D5, $E$5:$E$396, $E5, $F$5:$F$396, $F5) > 1
Procedure:
Select the cell range C5:F396.
Choose Conditional Formatting from the Home tab and select New Rule, then Use a Formula.
Enter the provided formula, and choose the Fill Option to format cells with an orange fill. The formula evaluates to true if and only if the values in columns C-F of the current row occur in at least one other row.
Example 2: Highlighting Suspicious Invoices Based on Payment Date
In the worksheet "Payment dates," which contains information on 403 invoices, the goal is to highlight suspicious invoices based on the payment date. An invoice is considered suspicious if it was paid on or before the filing date or on a weekend.
Figure 2: Highlighting suspicious invoices based on day paid
Formulas:
excel
Copy code
=WEEKDAY(F5,16) (in cells G5:G407)
=$F5<$E5 (highlight yellow)
=$F5=$E5 (highlight orange)
=$G5<=2 (highlight blue)
Procedure:
Copy the formula =WEEKDAY(F5,16) from G5 to G407 to determine if the paid date falls on a Saturday (1) or Sunday (2).
Select cell range G5:G407, and apply the formats based on the provided formulas (Figure 3).
$F5<$E5 highlights the entire row in yellow if the invoice is paid before the invoice date.
$F5=$E5 highlights each row in orange if the invoice is paid on the same day it was filed.
$G5<=2 highlights each row in blue if the invoice was paid on a weekend.
Figure 3: Formulas to highlight suspicious invoices
Conditional formatting with formulas in Excel extends its utility beyond basic data presentation, offering powerful tools for fraud detection and data analysis. The examples presented in this series demonstrate the versatility of this feature. As we conclude our discussion on conditional formatting, stay tuned for our exploration of Excel Sparklines in the next installment – mini-charts that summarize data within a single cell.