Excel for finance and accounting: the skills you actually use

Finance runs on Excel. The ledger lives in an accounting system, but the budget, the forecast, the month-end accruals, the bank reconciliation and the board pack are almost always built in a workbook. If you work in finance or accounting, or want to, Excel is not one skill among many. It is the tool you will use for most of the day.

This guide covers the Excel skills finance and accounting teams actually rely on. Not every function that has "financial" in its category, but the handful of techniques that make up most real work: summing a ledger, comparing budget and actual, rounding properly, working with periods, a little time value of money, and building sheets that other people can check and trust.

How finance work in Excel is different

Most Excel tutorials treat a spreadsheet as a calculator. Finance treats it as evidence. A number in a finance workbook will be reviewed, questioned, audited and reused next month. That changes what "good" looks like:

• Traceable. Anyone should be able to click a number and see where it came from. That rules out typed numbers inside formulas and long chains of references across ten sheets.
• Consistent. The same calculation is written the same way on every row and every month, so one check covers them all.
• Checked. Totals tie back to the source, and the sheet tells you when they do not.
• Repeatable. Next month you paste in new data and the workbook works again, without rebuilding.

Keep those four words in mind. Most of the techniques below exist to serve one of them.

Structure: inputs, calculations, outputs

The single most useful habit in finance Excel is to separate a workbook into three parts:

1. Inputs: data exports, assumptions and rates. Each one typed in one place, never repeated.
2. Calculations: formulas that read the inputs. No typed numbers.
3. Outputs: the reports and summaries people read, which only reference calculations.

Many teams color input cells differently, commonly blue text for typed values and black for formulas, so a reviewer can see at a glance which numbers are assumptions. Whatever the convention in your team, follow it. A model where inputs and formulas look identical is much harder to review.

The rule underneath is simple: never type a number into a formula if it could change. A VAT rate, an exchange rate or an interest rate belongs in its own cell, referenced with $, as in =B2*(1+$E$1). When the rate changes, you change one cell, and every formula follows.

Summing a ledger with SUMIFS

A large part of finance work is turning a long list of transactions into totals: per account, per department, per month. That is SUMIFS. Its first argument is the column to add up, followed by pairs of a range to check and a condition:

In practice the conditions come from cells rather than being typed in, so one formula can fill a whole summary: account codes down the side, departments across the top. =SUMIFS($C$2:$C$500,$A$2:$A$500,$E2,$B$2:$B$500,F$1) is a complete management-accounts layout in one formula. The mixed references $E2 and F$1 are what let it fill in both directions; the cross-table exercise drills exactly that pattern, and SUMIFS with the conditions in cells is the step before it.

SUMIFS also handles date ranges, which is how you get monthly totals from a transaction list. With the period start in E1 and the period end in F1, the condition pair is ">="&E1 and "<="&F1 on the date column. Our pivot table guide shows how the same totals look as a pivot table, and why many teams use both.

Budget versus actual

Every finance team compares what happened with what was planned. The variance is the difference; the variance percentage is the difference relative to the budget:

Three details matter in variance reports:

• Sign conventions. Decide whether a positive variance means "over budget" or "better than budget", and be consistent. For costs, spending more is bad; for revenue, more is good. Many reports flip the sign for cost lines so that positive always means favorable. Label the column so nobody has to guess.
• Division by zero. A line with no budget gives #DIV/0!. Wrap the formula: =IFERROR((C2-B2)/B2,0) or, if a zero budget should be visible, return text such as "n/a". The IFERROR on a division exercise covers this.
• Materiality. Not every variance needs an explanation. A common approach is to flag only lines that are over both an absolute amount and a percentage: =IF(AND(ABS(C2-B2)>5000,ABS((C2-B2)/B2)>0.1),"Explain",""). That gives reviewers a short list instead of a wall of numbers.

Rounding to the cent

Excel stores numbers with about 15 significant digits and shows whatever the cell format says. That gap causes one of the most familiar problems in finance: a column of amounts that visibly adds up to 100.01 but whose total says 100.00, or the other way around.

The fix is to round the stored value, not just the display, at the point where cents matter:

Rules of thumb:

• Round where the business rounds. Invoices are rounded per line, so round per line. Allocations are usually rounded per result. Do not round intermediate steps unless the process does.
• ROUND, ROUNDUP and ROUNDDOWN do different things. ROUND goes to the nearest; ROUNDUP always away from zero, as in boxes needed; ROUNDDOWN toward zero.
• Allocations that must add up exactly need the remainder pushed onto one line, usually the largest: allocate all lines with ROUND, then set the last one to the total minus the sum of the others.
• Formatting is not rounding. A cell formatted to two decimals still holds all of them. If a number feeds another calculation, what you see is not what Excel uses.

Periods and dates

Finance thinks in periods: months, quarters and financial years. Excel stores dates as serial numbers, one per day, which makes period arithmetic easy once you know a few functions.

• EOMONTH gives the last day of a month, a number of months away. =EOMONTH(A2,0) is the month end of the date in A2; =EOMONTH(A2,-1) is the previous month end. It is the basis of most accrual and reporting schedules. Practice it in last day of the month.
• EDATE adds whole months: =EDATE(A2,12) is the same day next year, useful for contract renewals and prepayment schedules. See contract end date with EDATE.
• The first day of next month is =EOMONTH(A2,0)+1, a trick that comes up constantly.
• NETWORKDAYS counts working days between two dates, with an optional list of holidays, for payment terms and close calendars. See count working days.
• YEAR and MONTH split a date into parts for SUMIFS conditions or a period column.

When an export from another system brings dates in as text, none of this works. A quick test: a real date aligns right and changes when you apply a different date format. Text does neither. Data, Text to Columns with a date format usually converts a whole column in one step.

Lookups and mapping

Exports from accounting systems rarely have everything you need on one row. The transaction list has an account number but not the account name; the account has a code but not its line in the management report. Lookups fill the gap, using a mapping table that you maintain on its own sheet.

XLOOKUP is the clearest tool for this if your Excel has it; VLOOKUP and INDEX MATCH work everywhere. The key points for finance work:

• Look up by code, never by name. Account names get renamed; codes do not.
• Decide what a missing code should show. In a reconciliation, a missing mapping should be loud, not hidden. Use XLOOKUP's not-found argument to return "UNMAPPED", and count those with COUNTIF so nothing slips through.
• Check the mapping table for duplicates. Every lookup returns the first match, silently.

Our lookup guide covers the differences between the three functions in detail.

A little time value of money

Not every finance role needs valuation, but a few functions come up often enough to know.

PMT gives the payment on a loan or lease with a fixed rate and term:

The loan amount is entered as a negative number because it is money coming in, and the payments go out; with -B3 the result comes out positive. The rate and the number of periods must use the same unit: a yearly rate over months must be divided by 12.

NPV discounts a series of future cash flows to today's value:

Excel's NPV assumes the first cash flow comes one period from now. If there is an initial investment today, add it outside the function: =NPV(B1,B2:B5)-B7, where B7 is the cost. IRR gives the rate at which the NPV is zero, and XNPV and XIRR do the same with actual dates rather than regular periods, which is what most real cash flows need.

Weighted averages with SUMPRODUCT

An ordinary average treats every row the same. Finance often needs a weighted one: the average price paid across purchases of different sizes, the average interest rate across loans of different amounts, the average margin across products with different sales.

SUMPRODUCT multiplies two ranges row by row and adds up the results. Divide by the sum of the weights and you have a weighted average: =SUMPRODUCT(B2:B10,C2:C10)/SUM(C2:C10), where B holds the prices and C the quantities. A plain AVERAGE of the prices would give a small purchase at a high price as much weight as a large one at a low price, which is rarely what anyone means.

The same function does conditional sums of products without a helper column, such as revenue for one region from separate price and quantity columns: =SUMPRODUCT((A2:A10="North")*B2:B10*C2:C10). The comparison in parentheses returns TRUE or FALSE for each row, and multiplying turns those into 1 and 0, so only the North rows count.

Number formats finance expects

Finance readers expect numbers to look a certain way, and a report that ignores the conventions looks careless even when every number is right.

• Thousands separators and no decimals on management reports. Nobody reads the cents on a 4,350,000 revenue line. Use a number format, not ROUND, so the totals stay exact.
• Negative numbers in parentheses: (1,250) rather than -1,250. The format #,##0;(#,##0);- shows positives, negatives in parentheses and a dash for zero, which keeps a wide table easy to scan.
• Percentages with one decimal for variances and margins. Two decimals suggest a precision the numbers rarely have.
• Units in the header, such as "Revenue (thousands)", when the figures are divided by 1,000. Divide in the format (#,##0, shows thousands) or in a clearly labeled calculation, never silently.
• Dates in one format throughout the workbook, and preferably one that cannot be read two ways, such as Mar 31, 2026.

Set these as cell styles once and reuse them, so every sheet in the workbook looks the same.

Checks that catch errors

A finance workbook should tell you when something is wrong. Build check cells that return zero when everything ties out, and put them where you will see them.

• Totals tie to the source. The sum of your summary table should equal the sum of the export: =SUM(F2:H20)-SUM(C2:C500) should be 0. If someone adds an account code your mapping does not know, this check catches it.
• Debits equal credits. On a journal or trial balance, the sum of all lines should be 0.
• Rows add across, columns add down. In a cross-table, the grand total of the row totals should equal the grand total of the column totals.
• Unmapped and error counts. =COUNTIF(D2:D500,"UNMAPPED") should be 0, and a SUMPRODUCT with ISERROR over a column shows whether any formula returns an error.

Put all checks together at the top of the output sheet, with conditional formatting that turns the cell red when it is not zero. Reviewers learn to look there first.

Pivot tables for the ledger

When someone asks an open question, such as "why are travel costs up this quarter?", a pivot table on the transaction export is the fastest way to answer. Put account in Rows, month in Columns and amount in Values, then drill into the cell that looks wrong by double-clicking it: Excel opens a new sheet with the underlying transactions. That double-click is one of the most useful and least known features for finance work. The pivot table guide walks through building one.

Shortcuts finance people use all day

Finance work is keyboard work. These save the most time:

• F2 to edit a cell and see its references highlighted on the sheet.
• Ctrl+Shift+Arrow to select to the end of the data in a long export.
• Ctrl+D to fill down a formula to the last row.
• F4 to toggle the dollar signs on a reference.
• Ctrl+Shift+L to turn filters on and off.
• Ctrl+Alt+V for paste special, mainly to paste values over formulas when you need a frozen copy.

Mistakes that cost the most

From reviewing finance workbooks, these are the errors that most often reach a manager or an auditor:

• A typed number over a formula in the middle of a column, usually a quick fix that was never undone. Nothing marks it, so it survives for months. Excel's green error triangles ("inconsistent formula") and Go To Special, Constants, help find them.
• A range that stops short of the last row, so the newest transactions are left out. Use whole tables or ranges that clearly cover the data, and a tie-out check.
• Sorting one column instead of the whole table, which breaks the link between amounts and accounts.
• Hard-coded rates inside formulas, which are wrong the day the rate changes.
• Circular references, often from a total that includes itself. Excel warns once and then quietly shows 0.
• Links to other workbooks that break when files are moved or renamed. Keep a list of external links (Data, Edit Links) and remove the ones you do not need.

Getting ready for a finance Excel test

Finance and accounting roles are among the most likely to include an Excel test, often a task workbook rather than multiple choice. Expect a ledger or transaction export and tasks like these: total by account and month, a budget-versus-actual table with percentages, a lookup to map codes to names, rounding to two decimals, an IFERROR around a division, and sometimes a pivot table or a PMT question.

The exercises below follow that list. Do the SUMIFS and summary-table exercises until the mixed references are automatic, then take a timed test. Our job interview guide has a two-week plan if your test is coming up soon.

Summary

Finance work in Excel is about numbers that can be traced, repeated and checked. Separate inputs, calculations and outputs; never type a changeable number into a formula; sum ledgers with SUMIFS; compare budget and actual with variances that handle zero budgets; round where the business rounds; use EOMONTH and EDATE for periods; map codes with lookups that make missing values visible; and build check cells that return zero when everything ties out.

None of this is advanced Excel. It is ordinary Excel done carefully, and the exercises below are the fastest way to make it automatic.