There are no items in your cart
Add More
Add More
| Item Details | Price | ||
|---|---|---|---|
Finance Skills & Tools
By CMA Rohan Sharma · · 10 min read · Last reviewed: 2026-06-18
Finance work has not moved away from Excel — it has grown more dependent on Excel being used well. Even when a company runs SAP, Oracle, TallyPrime, or Power BI, finance teams routinely export data to Excel for reconciliation, analysis, and presentation. The finance professional who can take that export and quickly clean, summarise, analyse, and present it with the right functions creates value that slower, formula-limited colleagues cannot match.
This blog covers the Excel functions that matter most in actual finance roles — with a finance-specific example for every function, not just a syntax description. The functions are grouped by what they do: lookup, aggregation, logic, date and text, financial calculations, and reporting tools. At the end is the learning order that makes building these skills efficient rather than overwhelming.
Every Excel function is just a tool. What makes it valuable is knowing which finance problem it solves — and being able to build the formula, explain it in an interview, and apply it correctly in your first job.
Top Excel functions for finance: Lookup — XLOOKUP (modern), VLOOKUP, INDEX-MATCH (legacy files). Aggregation — SUMIFS, COUNTIFS, AVERAGEIFS. Logic — IF, IFS, AND, OR, IFERROR. Date and text — TEXT, EOMONTH, DATEDIF, TRIM, CONCATENATE. Financial — NPV, IRR, PMT, RATE. Reporting tools — Pivot Tables, Power Query, conditional formatting, structured Tables. Learning order: references → SUMIFS → XLOOKUP → IF/IFERROR → Tables → Pivot Tables → Charts → Power Query → financial functions. Official syntax reference: support.microsoft.com/excel-functions.
Excel is the common language of finance. Regardless of the ERP system, accounting software, or BI tool a company uses, Excel almost always enters the workflow at the analysis, review, or presentation stage. Cost centre analysis from SAP gets pasted into Excel for pivot tables. GSTR-2B from the tax portal gets exported to Excel for reconciliation. Monthly MIS from TallyPrime gets formatted in Excel for management. Power BI connects to Excel as a data source.
The Microsoft functions reference at support.microsoft.com lists hundreds of Excel functions. Finance professionals do not need all of them — but the 25–30 covered in this blog recur across virtually every finance role at every level. Mastering them with finance examples, not just syntax, is the practical goal.
Lookup functions retrieve a value from one table based on a key in another. In finance, this means fetching budget amounts by cost centre, pulling vendor names from a master file, mapping GL codes to account descriptions, or linking product codes to category classifications.
| Function | Syntax (simplified) | Finance Example | When to Use |
|---|---|---|---|
| XLOOKUP | =XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found]) | =XLOOKUP(A2, BudgetTable[CostCentre], BudgetTable[Budget], 0) — fetches the budget for each cost centre in your expense report | Modern Excel (Microsoft 365 and Excel 2021+). Use as your default lookup. Microsoft describes XLOOKUP as the successor to VLOOKUP and HLOOKUP. |
| VLOOKUP | =VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]) | =VLOOKUP(A2, VendorMaster, 2, FALSE) — fetches vendor name from column 2 of the vendor master using vendor code as key | Legacy files, older Excel versions. The lookup column must be leftmost. Use FALSE for exact match in finance (always). |
| INDEX-MATCH | =INDEX(return_range, MATCH(lookup_value, lookup_range, 0)) | =INDEX(GL[Description], MATCH(B2, GL[Code], 0)) — fetches GL description for a code, where description column is to the LEFT of the code column | When the return column is to the LEFT of the lookup column (VLOOKUP cannot do this). Also more robust in large files. |
Interview tip: If asked "XLOOKUP or VLOOKUP?" — say: "XLOOKUP is more flexible and modern, but I know all three because legacy company files often use VLOOKUP and INDEX-MATCH, and working with existing spreadsheets requires understanding the formula that is already there."
Aggregation functions are the core of finance reporting. Every MIS, budget vs actual, cost centre analysis, and vendor summary depends on being able to sum, count, or average values that meet multiple conditions simultaneously.
| Function | What It Does | Finance Example |
|---|---|---|
| SUMIFS | Sums values where multiple conditions are all met simultaneously | =SUMIFS(Expenses[Amount], Expenses[CostCentre], "Marketing", Expenses[Month], "Apr-26") — total marketing expenses in April. The most-used function in finance MIS and budget analysis. |
| COUNTIFS | Counts rows where multiple conditions are all met | =COUNTIFS(AR[Customer], A2, AR[DaysPastDue], ">60") — count of invoices for a customer overdue beyond 60 days. Useful in receivables management and exception reports. |
| AVERAGEIFS | Averages values where multiple conditions are all met | =AVERAGEIFS(Sales[Margin%], Sales[Region], "North", Sales[Month], "Q1") — average gross margin % for North region in Q1. Useful in commercial finance and profitability analysis. |
| SUMPRODUCT | Multiplies arrays element-by-element and sums the results; can also serve as a flexible aggregation function | =SUMPRODUCT((Region="North")*(Month="Apr")*Amount) — alternative to SUMIFS, useful when conditions involve arrays or when multiple criteria cannot be expressed in standard SUMIFS syntax |
For a step-by-step guide to using SUMIFS in an actual MIS report, read our blog on how to build an MIS report in Excel.
Logic functions add decision-making capability to spreadsheets — classifying variances, flagging exceptions, handling errors, and applying conditional rules to data.
| Function | What It Does | Finance Example |
|---|---|---|
| IF | Returns one value if a condition is true, another if false | =IF(C2>D2, "Overspend", "Within Budget") — flags each cost line as overspend or within budget for a manager's exception view |
| IFS | Tests multiple conditions in sequence and returns the first match — cleaner than nested IF | =IFS(E2<-20%, "Critical", E2<-10%, "Watch", E2<0, "Minor", TRUE, "OK") — classifies budget variance into four tiers without nested IF nightmare |
| AND / OR | AND: all conditions must be true. OR: at least one must be true. Used inside IF to create compound conditions. | =IF(AND(Region="South", Variance%<-15%), "Escalate", "") — flags rows where both the region is South AND the variance exceeds 15% for escalation |
| IFERROR | Returns a specified value if a formula produces any error — keeps reports clean | =IFERROR(C2/D2, "") — calculates variance % but shows a blank instead of #DIV/0! when the budget cell is zero or empty. Every division formula in a finance report should be wrapped in IFERROR. |
| IFNA | Specifically handles #N/A errors from lookup functions | =IFNA(XLOOKUP(A2, MasterList, Names), "Not Found") — shows "Not Found" instead of #N/A when a vendor code is not in the master list |
Date and text functions solve the most common data quality problems in finance reporting — inconsistent date formats, untidy text values, and period labelling for charts and tables.
| Function | What It Does | Finance Example |
|---|---|---|
| TEXT | Converts a value to text in a specified format | =TEXT(A2, "MMM-YY") — converts a date like 15/04/2026 to "Apr-26" for chart axis labels and column headers in MIS. Essential for monthly reporting layouts. |
| EOMONTH | Returns the last day of a month, offset by specified number of months | =EOMONTH(A2, 0) — returns the last day of the month containing date A2. Used for period-end calculations, due date tracking, and ageing bucket calculations. |
| DATEDIF | Calculates the difference between two dates in days, months, or years | =DATEDIF(InvoiceDate, TODAY(), "D") — number of days since invoice date; used to build ageing analysis in receivables and payables reports |
| TODAY / NOW | Returns today's date (TODAY) or current date and time (NOW) — always live | =TODAY()-InvoiceDate — days outstanding for each invoice; recalculates automatically every time the file is opened |
| TRIM | Removes leading, trailing, and extra internal spaces from text | =TRIM(A2) — cleans " North " (from an ERP export) to "North" so SUMIFS conditions match correctly. Essential for data cleaning before analysis. |
| UPPER / LOWER / PROPER | Converts text to all uppercase, all lowercase, or title case | =UPPER(A2) — standardises vendor names to uppercase before VLOOKUP or SUMIFS to avoid case-sensitive mismatches |
| LEFT / RIGHT / MID | Extracts a specified number of characters from left, right, or middle of a text string | =LEFT(A2, 4) — extracts the first 4 characters from a GL code to get the account group prefix; useful for classifying accounts in bulk |
Financial functions are specifically designed for time-value-of-money calculations — loan analysis, investment evaluation, project appraisal, and valuation. They are particularly important for FP&A, corporate finance, business finance, and financial modelling roles.
| Function | What It Calculates | Finance Example | Official Reference |
|---|---|---|---|
| NPV | Net Present Value of an investment — the present value of future cash flows minus the initial investment, discounted at a specified rate | =NPV(10%, B2:B6) + A1 — where A1 is the initial investment (negative) and B2:B6 are projected annual cash inflows; calculates whether the investment creates positive value at a 10% discount rate | Microsoft NPV function reference: support.microsoft.com/npv-function |
| IRR | Internal Rate of Return — the discount rate at which NPV equals zero; the effective return rate of an investment | =IRR(A1:A6) — where A1 is the initial outflow (negative) and A2:A6 are projected inflows; calculates the project's effective return rate to compare against cost of capital | Verify current syntax from Microsoft Support |
| PMT | Periodic payment for a loan or investment with constant payments and constant interest rate | =PMT(8%/12, 36, -500000) — monthly EMI for a Rs. 5 lakh loan at 8% annual interest over 36 months. Useful for loan analysis, lease evaluation, and EMI schedule preparation. | Verify current syntax from Microsoft Support |
| RATE | Interest rate per period for a loan or investment | =RATE(36, -EMI, LoanAmount)*12 — calculates the annual interest rate implied by the monthly payment; useful for verifying loan terms or comparing financing options | Verify current syntax from Microsoft Support |
These financial functions assume constant periodic cash flows. Real-world finance situations often have irregular cash flows — for those scenarios, use XNPV and XIRR which accept actual dates alongside cash flows. Always verify current function behaviour from Microsoft Support (support.microsoft.com) as formula logic can have version-specific nuances.
Finance Freshers — Excel Functions Are Tested in Almost Every Finance and MIS Interview
Finance interviewers ask you to explain SUMIFS, walk through a VLOOKUP vs XLOOKUP difference, or describe a Pivot Table you built. This course prepares you to demonstrate your Excel and finance analytical skills clearly so every interview converts into a real offer.
Explore the Course →Pivot Tables are the fastest path from raw transaction data to a management summary. Microsoft describes Pivot Tables as tools used to summarise, analyse, explore, and present data. For finance professionals, a Pivot Table converts 10,000 rows of expense transactions into a cost-centre-by-month summary in under two minutes — with totals, subtotals, and filtering all built in. Finance freshers who cannot build and explain a Pivot Table are immediately disadvantaged in MIS, FP&A, and reporting role interviews.
Key Pivot Table skills for finance: grouping dates by month and quarter, adding calculated fields (like variance = actual – budget), applying Top 10 filters for customer or vendor analysis, using slicers for interactive filtering, and verifying that the Pivot Grand Total matches the raw data SUM (the essential accuracy check before sharing any report).
Power Query is Excel's built-in data transformation engine. Microsoft provides full Power Query documentation at learn.microsoft.com/power-query. For finance, Power Query's most valuable capabilities are: combining multiple monthly report files automatically (no manual copy-paste), cleaning inconsistent data from ERP exports (removing blank rows, standardising column formats, fixing date types), and reshaping data from wide to long format for Pivot Table analysis. Once the Power Query steps are built, every future month is a single click refresh. For a full beginner guide that covers Power Query in the context of finance dashboards, read our blog on Power BI for finance professionals.
Conditional formatting applies colour, icons, or data bars to cells based on their value — making exceptions, trends, and outliers immediately visible without manual highlighting. Finance use cases: red/amber/green variance flagging (negative variance = red automatically), data bars showing relative spend across cost centres, icon sets for traffic light KPI views, and colour scales for heatmaps of monthly performance data. Conditional formatting should be applied after the data is verified correct — not as a substitute for accuracy.
The most common mistake in Excel learning is either starting too advanced (jumping to financial functions or Power Query before SUMIFS is solid) or learning functions without a finance context (memorising syntax without understanding which problem each function solves). The correct order:
| Stage | Skills to Build | Why This Order |
|---|---|---|
| Stage 1 | Absolute references ($A$1 vs A1), relative references, structured Tables (Ctrl+T), basic SUM/AVERAGE/COUNT | Every other formula depends on correct reference management. Without this foundation, SUMIFS and XLOOKUP formulas break silently when copied across rows or columns. |
| Stage 2 | SUMIFS, COUNTIFS, AVERAGEIFS — with multiple conditions | These are the most-used functions in finance reporting. Building MIS and budget reports requires these before anything else. |
| Stage 3 | XLOOKUP (primary), VLOOKUP and INDEX-MATCH (secondary) | Data mapping — linking transaction data to master files, budgets, and classification tables — requires lookup functions at every stage of finance report building. |
| Stage 4 | IF, IFS, IFERROR, IFNA — with finance scenarios | Conditional logic and clean error handling make reports professional and accurate. Every division formula needs IFERROR. Exception flagging needs IF. |
| Stage 5 | TEXT, EOMONTH, DATEDIF, TODAY, TRIM, UPPER/LOWER — for data cleaning and report labelling | Real data is messy. Date formatting and text cleaning are daily tasks in every finance role that receives ERP or accounting system exports. |
| Stage 6 | Pivot Tables — with slicers, calculated fields, date grouping | Summarisation. Once SUMIFS is solid, Pivot Tables become faster for ad-hoc analysis and management reporting. |
| Stage 7 | Basic charts — bar, line, column, waterfall — with finance data | MIS and management reports require visual communication. Chart-building with dynamic chart titles and clean formatting is a distinct skill. |
| Stage 8 | Power Query — combine files, clean data, reshape | After the analysis toolkit is solid, Power Query automates the data preparation layer that precedes every analysis step. |
| Stage 9 | NPV, IRR, PMT, RATE, XNPV, XIRR — for financial modelling roles | Add financial functions when targeting FP&A, corporate finance, or financial modelling roles. Not required for all entry-level finance positions. |
For the full financial modelling context where Stage 9 functions become central, read our blog on financial modelling basics for finance graduates. For the data analytics workflow these Excel skills feed into, read our blog on data analytics for finance freshers.
CMA Students — Excel and Analytical Skills Are Tested in ICMAI Campus Placement Interviews
Corporate recruiters at ICMAI campus placement hire for MIS, costing, FP&A, and business finance roles where Excel proficiency creates a visible advantage. This course prepares you for placement from Day 1.
Explore the Course →XLOOKUP is more flexible and modern — can look left or right, no column index number needed, returns blank instead of N/A by default. Microsoft describes it as the successor to VLOOKUP and HLOOKUP. However, VLOOKUP and INDEX-MATCH still appear in legacy files, so knowing all three is practically useful. Verify current availability from support.microsoft.com.
SUMIFS, XLOOKUP, IF, IFERROR, and Pivot Tables are the most frequently used across finance reporting, MIS, and analysis roles. SUMIFS is the core aggregation function for every budget vs actual and MIS report. IFERROR should wrap every division formula. Pivot Tables provide the fastest summarisation from raw data to management view.
Important for FP&A, corporate finance, and financial modelling roles. Not required for all entry-level accounts, tax, or MIS roles. Build SUMIFS, XLOOKUP, IF, IFERROR, and Pivot Tables first. Add NPV, IRR, and PMT when targeting financial modelling or investment analysis positions.
Yes — Power Query is one of the highest-value Excel skills for finance because it automates data cleaning and monthly file consolidation. If you rebuild the same report from scratch each month, Power Query converts it to a one-click refresh. Full documentation: learn.microsoft.com/power-query.
In order: absolute/relative references → SUMIFS/COUNTIFS → XLOOKUP → IF/IFERROR → structured Tables → Pivot Tables → basic charts → Power Query → financial functions (NPV, IRR, PMT). Each stage builds on the previous. Learning lookup functions before references are solid leads to formulas that break silently.
Excel functions are not worth memorising in isolation. What creates interview and job value is being able to say: "I used SUMIFS to build the cost centre expense analysis — it pulled the monthly totals by department and category from 8,000 transaction rows in under a second" — and then actually being able to demonstrate it. The function name means nothing; the application in a real finance context is everything.
Follow the learning order in Section 8. Build each stage with actual finance data — even sample data you create yourself. Do not move to the next stage until you can explain the current one with a specific finance example out loud. When all nine stages are solid, you will have Excel capability that covers 95% of what finance roles require at entry and junior level — and you will be able to demonstrate it concretely in every interview.
— CMA Rohan Sharma, Career Success Launchpad
FCMA with 7+ years of post-qualification experience. Personally mentored 2,000+ CMA students and supported 1,000+ placements at PSUs, MNCs, and top finance companies across India. Published author of Rock Your Interview (Amazon & Flipkart). Winner of WIRC ICMAI Social Media Influencer Award 2025.
Tell us your current Excel level and target finance role — we will help you build the right Excel learning plan.
Fill in your details and Rohan Bhaiya will personally guide you.
Launch your Graphy