Excel Skills for Banking Jobs: What Banks Expect

The Excel skills for banking jobs are less about clever tricks and more about getting numbers right: percentages and rates, locked references, totals by branch and status with SUMIFS, rounding money correctly, loan payments, and lists that reconcile. This guide is for anyone applying to branch, operations, credit, compliance or analyst roles who wants to know what to practise first, with a worked example for each skill.

Which Excel skills do banks expect?#

The core skills are the same across most banking roles; what changes is how deep each one goes. As a rough guide:

If you're not sure where you stand, the Excel level test gives you a starting point in 12 questions. The wider guide to Excel skills for finance jobs covers the accounting side.

Percentages, percent change and basis points#

Banking runs on rates, so percentages come first. Percent change always divides by the old value:

=(C2-B2)/B2

If deposits grew from 1,240,000 in B2 to 1,302,000 in C2, the result is 0.05, or 5% once formatted as a percentage (Windows: Ctrl+Shift+%; Mac: Control+Shift+%).

Interest rates are often compared in basis points. One basis point is 0.01%, so 100 basis points make 1%. With an old rate of 5.00% in B2 and a new rate of 5.25% in C2:

=(C2-B2)*10000

returns 25, a rise of 25 basis points. Don't confuse this with percent change: the same move is a 5% increase in the rate itself. The percent change lesson drills the formula until it's automatic.

Absolute references: lock your rates#

Put each rate in its own cell and lock it with $ so it doesn't move when you copy a formula. With balances in column B and an annual rate in F1:

=B2*$F$1

Fill it down (Windows: Ctrl+D; Mac: ⌘D) and every row uses F1. Without the $, the next row would point at F2, which is empty, and quietly return 0. That kind of silent error is exactly what a bank can't afford. Press F4 while editing a reference to add the $ signs (Mac: ⌘T). The absolute references lesson has you fix the classic mistakes.

SUMIFS and COUNTIFS for portfolio summaries#

SUMIFS adds the numbers that meet one or more conditions, and COUNTIFS counts the rows. Together they answer most "how much" and "how many" questions about a loan or deposit list:

With this table in A1:C7:

=SUMIFS(C2:C7,A2:A7,"North",B2:B7,"Approved")
=COUNTIFS(B2:B7,"Approved")

The first returns 165,000 (120,000 + 45,000), and the second counts 4 approved loans. Remember that SUMIFS puts the sum range first. The SUMIFS function guide and the COUNTIFS and SUMIFS lesson cover dates, "greater than" conditions and cell-based criteria.

ROUND for interest and fees#

Formatting a cell to two decimals only changes what you see; Excel still calculates with every decimal. In banking, where each line must match to the cent, round the value itself:

=ROUND(B2*$F$1/12,2)

For a balance of 12,500 at 4.75% a year, the monthly interest is 49.479…, and ROUND makes it 49.48. If you only format the cells, a column of interest amounts can add up to a total that is a cent or two off from the sum of the displayed values. Use ROUNDUP or ROUNDDOWN only when a policy says to always round one way. See the ROUND function guide.

Loan payments with PMT#

PMT returns the regular payment for a loan with a fixed rate. The rate and the number of periods must use the same unit, so monthly payments need a monthly rate:

=PMT(6%/12,360,-250000)

That's a 250,000 loan at 6% a year over 30 years (360 months), and the payment is 1,498.88. The minus sign on the loan amount makes the payment positive; without it, Excel shows a negative number because money flows out. The guides to the PMT function and to building a loan amortization schedule take it further.

Lookups and reconciliation#

Reconciling means proving two lists agree, such as a payments file against the ledger. A count shows what's missing. With your references in column A and the other system's in F2:F200:

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

To bring back a matching amount instead, use =XLOOKUP(A2,$F$2:$F$200,$G$2:$G$200,"Not found") in Excel 2021 or Microsoft 365. Operations teams also lean on pivot tables to summarize volumes by day or product. Audit-style checks are similar; the Excel skills for auditors and Excel skills for accounts payable guides show more reconciliation patterns.

How to prepare for a banking Excel test#

Some banks use online Excel assessments, and others give a short spreadsheet task during the interview. Either way, the questions tend to look like the examples above: total a filtered list, calculate growth, fix a broken reference. Three steps help:

1. Practise each function until you can type it without looking up the argument order.
2. Do at least one timed test so the pressure isn't new on the day. The finance Excel mock test mixes rates, SUMIFS and rounding.
3. Check your answers the way a reviewer would: do the totals add back to the data?

For analyst roles, read what a financial analyst Excel test covers.

Practise it#

Start with the three skills that show up in almost every banking task. Practise percent change in a live spreadsheet, then practise COUNTIFS and SUMIFS and practise absolute references. You type each formula and it's checked instantly. The exercise on this page is a quick first test: one SUMIFS on a loan list.