Excel Skills for Finance and Accounting Jobs

The Excel skills for finance jobs come down to accuracy: references that don't break when copied, rounding that changes the value rather than the display, totals by account and period with SUMIFS, pivot tables, and checks that prove a report adds up. This guide is for anyone applying to accounting, finance assistant, accounts payable or FP&A roles who wants to know what to practise and why.

Why Excel skills for finance jobs are about accuracy#

Finance work is judged on whether the numbers are right, so the skills that matter most are the ones that prevent silent errors. A formula that gives a wrong answer with no error message is far worse than one that shows #N/A.

Typical finance tasks in Excel include:

• Applying a tax, FX or growth rate to a column of amounts.
• Totalling spend by account, cost centre, month or quarter.
• Comparing actuals with budget or last year.
• Reconciling two lists, such as a bank export and a ledger.
• Building a summary for a manager from a long transaction list.

Absolute references: lock your rates and totals#

An absolute reference like $G$1 stays fixed when you copy a formula, so every row uses the same rate. Put the rate in its own cell, never inside the formula. With net amounts in column B and a tax rate of 8.25% in G1:

=B2*$G$1

Fill it down with Ctrl+D (Mac: ⌘D), and every row multiplies by G1. Without the $, the second row would point at G2, which is empty, and return 0. Press F4 while editing a reference to cycle through $G$1, G$1, $G1 and G1 (Mac: ⌘T). The guide to Excel cell references and how to copy a formula without changing its references go deeper.

ROUND vs formatting: why your totals don't match#

Formatting a cell to show two decimals hides the rest, but Excel still calculates with the full value. That's why a column of displayed amounts can add up to a different total than the one Excel shows.

Example: three items each cost 12.99 with 8.25% tax. Each tax amount is 1.071675, shown as 1.07. The SUM of the three is 3.215025, shown as 3.22. But 1.07 + 1.07 + 1.07 is 3.21. Someone checking by hand finds a one-cent gap.

To fix it, round each line:

=ROUND(B2*$G$1,2)

Now each row really is 1.07 and the total is 3.21. ROUND(number, num_digits) rounds half away from zero, so 2.5 becomes 3 and -2.5 becomes -3. Use ROUNDUP or ROUNDDOWN when a rule says always up or always down. The ROUND function guide has more examples.

SUMIFS: totals by account, period or both#

SUMIFS adds numbers that meet one or more conditions, and the sum range always comes first. With quarters in A, a category in B and amounts in C:

=SUMIFS(C2:C7,A2:A7,"Q1",B2:B7,"Travel")

This returns 1510 (1200 + 310). For dates, use operators joined with &, such as =SUMIFS(C2:C7,D2:D7,">="&DATE(2025,1,1),D2:D7,"<"&DATE(2025,4,1)) for the first quarter of 2025 when dates are in column D. See the SUMIFS function guide.

Pivot tables for fast summaries#

A pivot table summarizes a long table by dragging fields, with no formulas. Put Category in Rows and Amount in Values, and you get a total per category with a grand total. Add Quarter to Columns and you have a category by quarter grid.

Two things to remember in finance work:

• A pivot doesn't update by itself. After the data changes, refresh it (Data > Refresh All).
• Use formulas for fixed reports. For one number in a fixed cell, such as on a monthly summary, SUMIFS is always live. Pivots are better for exploring.

Percent change, % of total and budget variance#

These three calculations come up in almost every finance report:

Always divide percent change by the old value. Format the result with Ctrl+Shift+% (Mac: Control+Shift+%) rather than multiplying by 100.

Lookups for reconciling two lists#

Reconciling means proving two lists agree, such as invoices in your ledger and payments in a bank export. A lookup or a count tells you which items are missing from the other list. With your invoice numbers in column A and the bank's references in F2:F50:

=IF(COUNTIF($F$2:$F$50,A2)=0,"Missing","OK")

COUNTIF counts how many times the invoice number appears in the bank list. Zero means it was never paid, so the row is flagged Missing. To bring back the paid amount instead, use =XLOOKUP(A2,$F$2:$F$50,$G$2:$G$50,"Not paid") in Excel 2021 or Microsoft 365, or =IFERROR(VLOOKUP(A2,$F$2:$G$50,2,FALSE),"Not paid") in older versions.

Checks that prove your numbers add up#

A good finance analyst checks their own work before anyone asks. Simple checks catch most mistakes:

• Summary equals source: =SUM(H2:H4)=SUM(C2:C7) shows TRUE when a summary by category adds back to the data.
• Text that looks like a number: =COUNT(C2:C7) should equal =COUNTA(C2:C7). If COUNT is lower, some amounts are stored as text and SUM is skipping them.
• Spot-check one row by hand with a calculator before sending a report.

The same habits help in neighbouring roles: see the Excel skills data analysts need, the skills for sales and operations roles and the skills HR teams use.

Practise it#

Lock a tax rate and fill formulas both ways in relative vs absolute references, fix decimals properly in ROUND, ROUNDUP and ROUNDDOWN, then build and rearrange your first summary in Pivot tables 1. The exercise below combines the first two: a rounded tax amount with a locked rate.