Excel for Finance: The Functions and Tools Finance Teams Actually Use

Excel for finance is less about knowing hundreds of functions and more about using two dozen of them in a way your manager, your auditor and your future self can trust. Analysts, FP&A teams, controllers and treasury staff all live in spreadsheets, but the work they do there follows a few recurring patterns: valuing cash flows, testing scenarios, pulling numbers from exports, and presenting results that hold up under review.

This guide covers the functions and tools finance professionals actually use, with a worked NPV and IRR example you can rebuild in five minutes, and the modeling habits that separate a reliable workbook from a fragile one.

In this guide

• Why finance still runs on Excel
• The Excel functions finance teams use most
• Step by step: NPV and IRR for a project
• Test assumptions with Goal Seek, scenarios and data tables
• Pull and summarize data with lookups and PivotTables
• Clean accounting exports with Power Query
• Build a financial model others can trust
• How to build these skills
• FAQ

Why finance still runs on Excel

Most companies have an ERP, a planning tool and a BI platform. Finance teams still open Excel every day, because it is where the questions that the systems were not built for get answered: what happens to cash if a client pays 30 days later, is this investment worth it at a 9% cost of capital, why is marketing 12% over budget this quarter.

Excel is flexible enough to model almost anything, and that flexibility is also its main risk. A single hard-coded number or a broken range can push a wrong figure all the way to a board pack. So finance work in Excel rests on two pillars: knowing the right tools, and using them in a structure that makes errors visible.

The Excel functions finance teams use most

You don’t need the whole function library. The table below covers what shows up in the large majority of finance workbooks.

Need	Functions and tools	Typical use
Valuing cash flows	NPV, XNPV, IRR, XIRR	Investment appraisal, project and acquisition analysis
Loans and annuities	PMT, IPMT, PPMT, RATE, NPER, FV, PV	Debt schedules, leases, savings plans
Conditional totals	SUMIFS, COUNTIFS, AVERAGEIFS	Spend by cost center, revenue by region and month
Lookups	XLOOKUP, INDEX/MATCH	Mapping accounts, pulling rates, joining two exports
Logic and checks	IF, IFS, IFERROR, AND, OR, ROUND	Flags, error handling, control totals
Dates	EOMONTH, EDATE, YEARFRAC, NETWORKDAYS	Monthly timelines, payment terms, accruals
What-if analysis	Goal Seek, Scenario Manager, data tables	Break-even, sensitivity, best and worst cases
Reporting and data	PivotTables, Power Query, Power Pivot	Monthly reporting, ERP extracts, multi-table analysis

Two details catch people out. NPV assumes every cash flow lands at the end of a period, so the initial investment made today must be added outside the function. And the X versions, XNPV and XIRR, take actual dates, which makes them the better choice when cash flows are irregular.

Step by step: calculate NPV and IRR for a project

Say your company is considering a $10,000 equipment purchase that should generate $3,000, $4,000, $4,000 and $3,000 of extra cash over the next four years. The cost of capital is 8%. Is it worth it?

1. Put the inputs in their own cells. Type the discount rate in B1 (8%) and the initial investment in B2 (-10000). Negative means cash going out.
2. List the cash flows. Enter 3000, 4000, 4000 and 3000 in B4:B7, with Year 1 to Year 4 labels in column A.
3. Calculate NPV. In B9, type =NPV(B1,B4:B7)+B2. The function discounts the four future flows, then you add the investment made today. The result is about $1,587.55.
4. Calculate IRR. IRR needs the full series including the outflow, so put the -10000 in a cell directly above the cash flows (B3) and type =IRR(B3:B7). The result is about 14.9%.
5. Read the result. A positive NPV at 8%, and an IRR well above 8%, both say the project creates value at that cost of capital.

The most common mistake is =NPV(B1,B2:B7), which discounts today’s investment by a full year and overstates the result. If your cash flows fall on specific dates instead of neat yearly intervals, list the dates in another column and use =XNPV(B1,values,dates) and =XIRR(values,dates).

The same input-driven logic works for debt. =PMT(6%/12,360,250000) returns the monthly payment on a $250,000 loan at 6% over 30 years: about -$1,498.88, negative because it is money paid out.

Test assumptions with Goal Seek, scenarios and data tables

A model is only useful if you can ask it questions. Excel has three built-in what-if tools, all under Data > What-If Analysis:

• Goal Seek works backward from a target. In the example above, set B9 (NPV) to 0 by changing B1 (rate), and Excel finds the break-even rate, which is the IRR. Use it for break-even volumes, required prices or maximum loan amounts.
• Scenario Manager stores named sets of inputs, such as Base, Upside and Downside, and produces a summary sheet comparing the outputs side by side.
• Data tables show how an output changes across a range of one or two inputs. A two-variable table with discount rates across the top and investment amounts down the side gives you an instant sensitivity grid for NPV.

Data tables recalculate constantly and can slow large workbooks down. If that happens, set calculation to Automatic except for data tables in the Formulas tab and press F9 when you need fresh numbers.

Pull and summarize data with lookups and PivotTables

Most monthly reporting starts with a trial balance or transaction export that needs to be mapped and summarized. XLOOKUP maps each account number to a reporting line in one formula: =XLOOKUP(A2,Mapping[Account],Mapping[Line],"Unmapped"). The last argument returns a visible label instead of an error, so an account added to the chart of accounts last week doesn’t silently disappear from the report.

From there, a PivotTable turns thousands of rows into a budget vs actual view in a few clicks: reporting lines in rows, months in columns, actual and budget as values. Add a calculated field for the variance, or compute it next to the pivot with a simple formula, and apply conditional formatting to highlight lines more than 10% off budget. If PivotTables are still new to you, our PivotTable tutorial with practice exercises covers layout, grouping, slicers and calculated fields step by step.

Excel for Microsoft 365 also offers XLOOKUP’s companions, such as FILTER, UNIQUE and SORT, which return whole lists that update by themselves. They are covered in detail in our guide to what an advanced Excel course should teach.

Clean accounting and ERP exports with Power Query

If you spend the first morning of every month deleting header rows, splitting columns and fixing dates stored as text, Power Query is the tool that gives you that morning back. Found under Data > Get Data, it records each cleaning step once and replays it on next month’s file with a single Refresh.

• Remove report headers, blank rows and subtotal lines from a system export.
• Set the right data types so amounts are numbers and dates are dates.
• Combine twelve monthly files from a folder into one table.
• Merge the general ledger with the account mapping, without a single lookup formula.
• Unpivot a budget laid out with one column per month into a proper table that PivotTables can read.

The accounting side of this, with bank reconciliations and aging reports, is covered step by step in our companion article on Excel for accounting.

Build a financial model others can trust

Knowing the functions gets you a result. Structure is what makes the result reviewable. Most finance teams follow the same handful of rules, whatever their model size.

Rule	What it means in practice
Separate inputs, calculations and outputs	Assumptions on one sheet, workings on another, the summary on a third. Anyone can change a driver without touching a formula.
No hard-coded values in formulas	Write =B5*Inputs!C3, not =B5*1.08. Every number that might change lives in an input cell with a label.
Color-code cells	A common convention: blue font for inputs, black for formulas, green for links to other sheets.
One formula per row	The same formula runs across every period. Exceptions break the pattern and hide errors.
Build in checks	Balance sheet balances, cash ties out, sub-totals equal the total. Show a single check cell that turns red if anything fails.
Use tables and named inputs	Structured references like Sales[Amount] expand automatically and read like plain language.

A simple check formula such as =IF(ROUND(Assets-Liabilities-Equity,2)=0,"OK","ERROR"), combined with conditional formatting, catches most structural mistakes before anyone else sees them. Excel’s Trace Precedents and Evaluate Formula tools, in the Formulas tab, help you find where a wrong number comes from.

How to build these skills

Finance professionals rarely lack motivation. They lack a structured path, because most learned Excel on the job, one urgent request at a time. The result is often a strong command of a few functions and blind spots everywhere else, typically Power Query, data tables and model auditing.

1. Close the gaps in the fundamentals: absolute references, tables, SUMIFS, XLOOKUP.
2. Master analysis tools: PivotTables, what-if analysis, data validation, auditing.
3. Automate data preparation: Power Query and the Data Model with Power Pivot.
4. Prove it: the MOS Excel Expert exam (MO-211) is a recognized way to show employers your level. Our comparison of Excel certificates explains which one fits a finance career.

If you would rather follow one structured Excel learning path from beginner to expert than piece it together from tutorials, Gnosarium’s program covers these steps across four courses, with Power Query, Power Pivot and DAX in the expert course.

Excel for finance FAQ

What Excel skills do finance jobs require?

Most finance roles expect SUMIFS, XLOOKUP or INDEX/MATCH, PivotTables, financial functions such as NPV, IRR and PMT, what-if analysis and clean model structure. Analyst and FP&A roles increasingly add Power Query and Power Pivot to that list.

What is the difference between NPV and XNPV in Excel?

NPV assumes cash flows are evenly spaced and arrive at the end of each period. XNPV takes a date for each cash flow, so it handles irregular timing accurately. XIRR is the dated counterpart of IRR.

Is Excel enough for financial modeling?

For most budgets, forecasts, valuations and business cases, yes. Larger organizations add planning or BI tools on top, but Excel remains the place where models are prototyped, checked and explained.

Should finance professionals learn Power Query?

Yes. Anyone who rebuilds the same report from a system export every month will save hours with Power Query, and the cleaning steps become documented and repeatable instead of living in someone’s memory.

Which Excel certification is best for finance?

The Microsoft Office Specialist Excel Expert exam (MO-211) is the most relevant, since it tests advanced formulas, PivotTables, what-if analysis and macros. Pair it with Power Query and Power Pivot skills, which the exam doesn’t fully cover.