📌 Basic Accounting Formulas
1️⃣ SUM → Adds up values (Total Revenue, Expenses, etc.)
excel
CopyEdit
=SUM(A1:A10)
2️⃣ AVERAGE → Calculates the mean value (Average Sales, Expenses, etc.)
excel
CopyEdit
=AVERAGE(B2:B12)
3️⃣ ROUND → Rounds numbers to a specific decimal place (Used in tax calculations)
excel
CopyEdit
=ROUND(A1,2) → (Rounds to 2 decimal places)
📌 Financial Analysis & Reporting Formulas
4️⃣ IF → Conditional logic (Check if an amount is over a limit, etc.)
excel
CopyEdit
=IF(A2>1000, "High", "Low")
5️⃣ IFERROR → Hides errors in calculations
excel
CopyEdit
=IFERROR(A2/B2, "Error")
6️⃣ VLOOKUP → Finds data from another table (e.g., Fetch account name using account
number)
excel
CopyEdit
=VLOOKUP(101, A2:C10, 2, FALSE)
7️⃣ HLOOKUP → Works like VLOOKUP but searches in rows instead of columns
excel
CopyEdit
=HLOOKUP("Sales", A1:D5, 2, FALSE)
8️⃣ INDEX + MATCH → More advanced version of VLOOKUP (Works in both directions)
excel
CopyEdit
=INDEX(B2:B10, MATCH(101, A2:A10, 0))
📌 Bank Reconciliation & Transactions
9️⃣ TEXT → Format numbers, dates, and currencies
excel
CopyEdit
=TEXT(A1, "$#,##0.00")
🔟 LEFT, RIGHT, MID → Extract text from account numbers or invoice IDs
excel
CopyEdit
=LEFT(A2, 4) → Extracts first 4 characters
=RIGHT(A2, 3) → Extracts last 3 characters
=MID(A2, 2, 3) → Extracts 3 characters from the 2nd position
1️⃣1️⃣ CONCATENATE / TEXTJOIN → Combine multiple values (e.g., "Customer -
Invoice")
excel
CopyEdit
=TEXTJOIN("-", TRUE, A2, B2)
📌 Accounting Adjustments & Data Cleaning
1️⃣2️⃣ TRIM → Removes extra spaces in text (Fixes errors in imported data)
excel
CopyEdit
=TRIM(A2)
1️⃣3️⃣ LEN → Counts characters in a text (Check if an invoice number is correct)
excel
CopyEdit
=LEN(A2)
1️⃣4️⃣ PROPER, UPPER, LOWER → Change text case
excel
CopyEdit
=UPPER(A2) → Converts text to UPPERCASE
=LOWER(A2) → Converts text to lowercase
=PROPER(A2) → Capitalizes the first letter of each word
📌 Date & Time Formulas (For Payment & Due Dates)
1️⃣5️⃣ TODAY & NOW → Current date & time
excel
CopyEdit
=TODAY() → (Returns today’s date)
=NOW() → (Returns current date & time)
1️⃣6️⃣ EOMONTH → Finds the last day of the month (For payment deadlines)
excel
CopyEdit
=EOMONTH(A2, 1) → (Returns next month's last date)
1️⃣7️⃣ DATEDIF → Calculates the difference between two dates (Used for overdue invoices)
excel
CopyEdit
=DATEDIF(A2, B2, "D") → (Days difference)
=DATEDIF(A2, B2, "M") → (Months difference)
=DATEDIF(A2, B2, "Y") → (Years difference)
📌 Data Analysis & Pivot Tables
1️⃣8️⃣ COUNTIF → Counts values based on conditions (How many invoices are above
$500?)
excel
CopyEdit
=COUNTIF(A2:A100, ">500")
1️⃣9️⃣ SUMIF → Sums values based on a condition (Total sales for a specific product)
excel
CopyEdit
=SUMIF(A2:A100, "Product A", B2:B100)
2️⃣0️⃣ SUBTOTAL → Dynamic calculations for filtered data
excel
CopyEdit
=SUBTOTAL(9, A2:A100) → (Sums only visible cells)
🚀 Bonus: Pivot Tables for Reporting
📌 Pivot Tables help summarize revenues, expenses, and accounts receivable/payable.
📌 Use Excel Power Query to clean large financial datasets easily.
