Excel Satish

❤1👍1

1.84K viewsSatish Gupta, 14:22

Must-Know Excel Formulas for Internal Auditors

✅ VLOOKUP / XLOOKUP – Find missing or incorrect data
👉 Example: You’re auditing supplier invoices and need to check if every invoice in the accounts payable report exists in the purchase order report. Instead of manual checking, use XLOOKUP to instantly match invoices and flag missing ones.

✅ IF & IFERROR – Error-proof your audit checks
👉 Example: While checking expense claims, you can use:
=IF(B2>10000, "High Risk", "OK")
to flag claims above a threshold for further review.

✅ COUNTIF – Find duplicate or suspicious transactions
👉 Example: Identify duplicate vendor payments by using:
=COUNTIF(A:A, A2)
If the count is more than 1, you have a duplicate!

✅ SUMIF / AVERAGEIF – Summarize risk areas
👉 Example: Find the total value of high-risk transactions by summing only those that exceed a specific amount:
=SUMIF(B:B, ">10000", C:C)

✅ TEXT, LEFT, MID, RIGHT – Extract hidden insights from data
👉 Example: If invoice numbers start with a branch code, you can extract it using:
=LEFT(A2, 3)

✅ Identifying Missing or Delayed Payments using XLOOKUP
Scenario: You're auditing customer payments and need to check if all invoices have corresponding payments.
Formula:

=XLOOKUP(A2, Payments!B:B, Payments!C:C, "Missing Payment")

✅ Detecting Duplicate Invoices using COUNTIF
Scenario: You suspect duplicate invoices in the sales ledger, which could indicate fraudulent transactions.
Formula:

=COUNTIF(A:A, A2)

✅ Aging Analysis for Overdue Receivables using IF & TODAY()
Scenario: You need to identify overdue invoices and categorize them into aging buckets (0-30, 31-60, 61-90 days).
Formula:

=IF(TODAY()-B2<=30, "0-30 Days", IF(TODAY()-B2<=60, "31-60 Days", IF(TODAY()-B2<=90, "61-90 Days", "90+ Days")))

✅ Customer Credit Utilization using SUMIF
Scenario: You need to check if a customer has exceeded their credit limit.
Formula:

=SUMIF(A:A, "Customer_Name", B:B)

✅ Trial Balance Verification using SUMIF
Scenario: You need to check if all debits and credits match in the trial balance.
Formula:

=SUMIF(B:B, "Debit", C:C)-SUMIF(B:B, "Credit", C:C)

✅ Bank Reconciliation using Conditional Formatting
Scenario: You want to highlight bank transactions that are missing in books.
Steps:

1. Use XLOOKUP to compare bank statements and books.

2. Apply Conditional Formatting to highlight missing records.

Formula:

=XLOOKUP(A2, Bank_Statement!A:A, Bank_Statement!B:B, "Not Found")

✅ Variance Analysis using ABS & IFERROR
Scenario: You need to compare budgeted vs. actual expenses and highlight significant variances.
Formula:

=IFERROR((B2-C2)/B2, 0).

✅ Fixed Asset Depreciation Calculation using SLN
Scenario: You need to calculate straight-line depreciation for fixed assets.
Formula:

=SLN(Cost, Salvage, Life)

#ExcelAutomation #ExcelFunctions #ExcelTips #LearnExcel #SpreadsheetSkills #MicrosoftExcel #ExcelProductivity #ExcelTricks #Excel #Productivity

❤1

1.82K viewsSatish Gupta, 10:31