OmniDocWhat are the commonly used Excel functions in financial accounting: classified by scenario
This article organizes the Excel functions commonly used in financial accounting and lists them according to four scenarios: summation, search and matching, date processing, and financial calculation. It explains which ones to learn first in each category, how to write them, and what are the common mistakes.
Key points of this article
- Functions used in daily finance can be classified into four categories: summation, search and matching, date processing, and financial calculations. Learn them based on scenarios first, which is more labor-intensive than memorizing them one by one.
- When summarizing account details, SUMIFS sums based on multiple conditions, which is less likely to miss rows than manual filtering and then summing.
- Fill in the fourth parameter of VLOOKUP with FALSE to avoid mismatches caused by approximate matching. In particular, encoding search should be written like this
- When calculating repayment amount and depreciation amount, first unify the unit of interest rate and number of periods, and then check the positive and negative signs of cash flow
- When the table is hidden in a scan or PDF, it is first converted into editable data, and then the function is applied, so that the result can be verified
When making accounts, reconciling accounts, and preparing reports, many people will ask: What are the commonly used types of financial functions in Excel? There are hundreds of functions, but only a small part are actually used in financial work. Instead of memorizing them one by one, it is better to classify them according to "what problem I want to solve".
This article organizes commonly used functions in financial accounting according to four types of scenarios: summation, search and matching, date processing, and financial calculations. Each category explains what problems it solves, how to write several commonly used functions, and where errors are likely to occur. It is suitable for accountants who have just taken over reporting, cashiers who need to check accounts, and also suitable for newcomers who want to change manual tables into formulas.
The functions in this article are all common Excel writing methods and do not depend on specific software. If the table data is originally in PDF or scanned documents, it can be converted into editable data first and then processed with functions.
What are the commonly used Excel functions in finance? One sentence conclusion
Select functions according to scenarios. First master the two or three most used in each category, and then you can cover most daily work. The table below can serve as a starting point.
| your situation | Recommended practices | reason |
|---|---|---|
| To summarize amounts by account or month | Use SUM for totals and SUMIFS for multi-condition summary | Formulas can be reused, and the results automatically change after the data is updated. |
| To get the name or balance corresponding to the code from the chart of accounts | Use VLOOKUP or INDEX plus MATCH, and write exact match in match mode | Avoid line errors caused by approximate matching |
| Calculate the expiration date of the contract and the end of the reporting period | Use EDATE to calculate the date, and use EOMONTH to get the end of the month. | Calculate months and leap years without using your hands |
| Calculate monthly loan payment and annual depreciation of assets | Use financial functions such as PMT, SLN, and DB | The parameters are fixed and the results can be compared with the contract or system. |
| Data comes from scans or PDF tables | Convert it to an editable table first, and then write formulas | The original table cannot be calculated directly and must be checked item by item after conversion. |
What are the four types of functions?
1. Summarization: SUM and SUMIFS
SUM is responsible for adding up the numbers in a column or a range. For example, =SUM(B2:B50) gets the total of a batch of vouchers. It's simple, but it can only sum by position and cannot judge conditions.
SUMIFS is a conditional sum. For example, if you want to calculate the amount of "administrative expenses" incurred in a certain month, you can write it as =SUMIFS(金额列, 科目列, "管理费用", 月份列, "3 月"). There can be multiple conditions, which is suitable for summary of account details and department expense statistics.
A common misconception is that SUMIFS automatically recognizes numbers in text. If there are numbers with spaces, units or saved as text in the amount column, the result will be smaller and the format needs to be unified first.
2. Find matches: VLOOKUP, INDEX and MATCH
The search function is used to retrieve information from one table to another, such as finding the account name based on the account code, and finding the account period based on the customer number.
The writing method of VLOOKUP is: search value, search range, return column, matching method. For example =VLOOKUP(A2, 科目表!A:B, 2, FALSE). The final FALSE indicates an exact match. If omitted or written as TRUE, the function may return a seemingly normal but actually incorrect result when encountering data with inconsistent encoding.
The lookup value must be placed in the first column of the lookup range, which is a hard limit of VLOOKUP. If you need to search to the left, it is more reliable to use INDEX plus MATCH.
3. Date processing: EDATE and EOMONTH
In financial forms, it is often necessary to calculate the due date, aging, and month-end date.
- EDATE: Calculate the number of months backward or forward, for example,
=EDATE(起始日期, 12)will get the date one year later, which is suitable for calculating the expiration of the contract. - EOMONTH: Get the last day of a certain month,
=EOMONTH(日期, 0)returns the end of the month, suitable for getting the number at the end of the report period. - DATEDIF: Calculate the year, month, and day difference between two dates, often used for aging segmentation.
What is confusing is that the number of days in the month is not fixed. If you use "days divided by 30" to estimate the number of months, the results will be biased. Use EDATE or DATEDIF to be more reliable.
4. Financial calculation: PMT, SLN, DB, NPV, IRR
Financial functions deal specifically with time value of money and asset depreciation.
- PMT: Calculate the amount of each period for equal repayments, such as
=PMT(年利率/12, 期数, -贷款额). The interest rate should be consistent with the unit of period, and the annual interest rate should be divided by 12 when repaying it monthly. - SLN: Straight-line depreciation,
=SLN(原值, 残值, 使用年限), the depreciation amount is the same every year. - DB: Fixed declining balance method. Depreciation is more in the first few years and less in the next few years. It needs to be selected according to the system or contract method.
- NPV and IRR: used to evaluate project cash flow. The cash flows of each period of NPV correspond to the number of periods, usually starting from period 1; the initial investment is generally recorded in period 0, that is, added separately in front, and recorded as a negative number.
A common misconception is to ignore the plus and minus signs. In PMT, the amount received for the loan is positive and the repayment outlay is negative, which would be exactly the opposite if the sign was reversed.
📊 Core comparison
| category | Commonly used functions | Main purpose | What's easy to make mistakes |
|---|---|---|---|
| summation | SUM, SUMIFS | Total, summary by condition | Numbers are saved as text, and the condition area lengths are inconsistent. |
| Find matches | VLOOKUP, INDEX, MATCH | Get the information corresponding to the encoding | Missing an exact match, the search value is not in the first column |
| Date processing | EDATE, EOMONTH, DATEDIF | Due date, month end, aging | Estimate the number of months using a fixed number of days |
| financial calculations | PMT, SLN, DB, NPV, IRR | Monthly payment, depreciation, project evaluation | The interest rate is inconsistent with the period unit, and the positive and negative signs are reversed. |
How to use it in practice
If the account balance sheet and current bills in hand are PDF or scanned copies, you can follow the steps below:
- Upload PDF to omnidoc.top, choose to output only translation or bilingual comparison, download the result, and use it to read financial information in English or foreign languages.
- If you need to turn tables in PDF into computable data, you can use the PDF editor of the OmniDoc desktop client to first perform OCR on the scanned document and then export it to Excel.
- After exporting, check the amount, date and code line by line to confirm that the numbers are not misaligned or missing.
- Write formulas in the table according to the above four scenarios, such as using SUMIFS to summarize and VLOOKUP to match account names.
- Use a 12-month installment table to check the PMT result and confirm that it is consistent with the contract amount.
Note: Both OCR and machine translation may have recognition or wording errors. Key contents such as amount, account name, date, etc. must be checked item by item with the original voucher before use.
🎯 Which method is suitable for you?
- Only do simple totals: first become proficient in SUM and SUMIFS, which can already meet most of the reconciliation needs.
- Frequently connect multiple tables: focus on practicing VLOOKUP or INDEX plus MATCH, and develop the habit of writing exact matches.
- Responsible for fixed assets or loans: learn SLN, DB, PMT, and check parameters against the system or contract.
❓ Frequently Asked Questions (FAQ)
1. What is the difference between SUMIFS and PivotTable?
SUMIFS is a formula written in a cell. The result is updated as the source data changes. It is suitable for fixed-format reports. Pivot tables are suitable for temporary drag-and-drop analysis, but they need to be refreshed. Both can be used together.
2. What should I do if VLOOKUP returns #N/A?
In most cases, the format of the search value and the source data are inconsistent, for example, one is a number, the other is text, or there are spaces before and after. Unify the format first, and then check whether exact matching is used.
3. Should I use SLN or DB for depreciation?
It depends on the company’s accounting policies or tax regulations. SLN is used for the straight-line method, and DB is considered only for the accelerated depreciation or fixed-declining balance method, whichever is applicable.
4. Why is the monthly payment calculated by PMT different from that calculated by the bank?
Common reasons are that the interest rate is not converted into a monthly rate, or the period is in years instead of months. In addition, the repayment methods, handling fees and penalty interest of some loans will also affect the final figure, which needs to be based on the contract and bank statements.
📝 Summary
Excel functions commonly used in financial accounting can be learned in four categories: summation, search and matching, date processing, and financial calculations. Master two or three core functions in each category first. When writing formulas, pay attention to exact matching, unified units, and positive and negative signs. Key figures should be checked with the original vouchers.
