Excel skills
The formulas and habits finance teams use every day: SUMIFS, lookups, IF, pivot tables and checks.
New to this topic?
Excel is the tool you’ll use every day in almost any finance job. Most of the work comes down to a few skills: adding up data that meets conditions, looking up values from another list, flagging items that need attention, and summarising large lists quickly. Learning these formulas saves hours of copying and adding by hand.
Example. Instead of scrolling through 2,000 invoices and adding the unpaid ones, one SUMIFS formula gives you the total in a second, and it updates when the data changes.
Key words
- Cell reference
- The address of one cell in a spreadsheet: the column letter followed by the row number.Example: D2 means column D, row 2.
- Range
- A block of cells in a spreadsheet, written as the first cell and the last cell with a colon between them.Example: D2:D50 means every cell in column D from row 2 to row 50.
- Absolute reference
- A cell reference with $ signs, such as $H$1. When you copy the formula to another cell, this reference stays pointing at the same cell.Example: =E2*$H$1 copied down one row becomes =E3*$H$1. E2 changed to E3, but $H$1 stayed the same.
- Criteria
- In a spreadsheet formula, the condition a row must meet to be included.Example: In =SUMIFS(D:D,E:E,"Unpaid"), the criteria is "Unpaid".
- Pivot table
- A spreadsheet tool that groups a list of data by category and shows totals, without writing formulas.Example: Turning 5,000 invoices into a small table of total sales by region and by status.
Learn
Almost every finance job, from audit and tax to finance teams, is done in Excel. You don’t need to be an expert on day one, but you should be able to total, look up and flag data with formulas, and check your own work.
The functions you’ll use most
| Function | What it does | Finance example |
|---|---|---|
SUM, ROUND | Adds a range; rounds to a set number of decimal places | =ROUND(SUM(E2:E50),2) totals invoices to the penny |
IF | Returns one result if a test is true, another if not | =IF(E2>5000,"Review","OK") flags large invoices |
SUMIFS | Adds values that meet one or more conditions | =SUMIFS(E:E,C:C,"North",F:F,"Unpaid") gives unpaid North sales |
COUNTIFS | Counts rows that meet conditions | =COUNTIFS(F:F,"Unpaid") gives the number of unpaid invoices |
AVERAGEIFS | Averages values that meet conditions | Average invoice value for one region |
XLOOKUP | Finds a value in one column and returns the matching value from another | =XLOOKUP("INV-1006",A:A,E:E) gives that invoice’s amount |
INDEX + MATCH | The older lookup that works in every version of Excel | =INDEX(E:E,MATCH("INV-1006",A:A,0)) |
IFERROR | Shows something tidy instead of an error | =IFERROR(XLOOKUP(…),"Not found") |
EOMONTH | Gives the last day of a month | =EOMONTH(B2,0) gives the month end for a date |
Absolute references
When you copy a formula down, its cell references move with it. Put a $ in front of the column or row to stop it moving. =E2*$H$1 copied down becomes =E3*$H$1, so every row uses the VAT rate in H1. Press F4 to add the $ signs.
Pivot tables
- Click anywhere in your data and choose Insert → PivotTable.
- Drag a field into Rows (for example Region), and one into Columns if you want (Status).
- Drag the numbers into Values (Sum of Amount).
- Right-click and choose Refresh when the data changes.
Habits reviewers look for
- No hardcoded numbers inside formulas. Put the VAT rate or the threshold in its own labelled cell and refer to it.
- Cross-cast. Check that row totals and column totals agree.
- Tie out to the source, such as the trial balance or bank statement, and note where each number came from.
- Keep formulas the same all the way down a column. One edited cell in the middle is a common hidden error.
Shortcuts worth learning
| Shortcut | What it does |
|---|---|
| Ctrl + Arrow key | Jump to the end of the data |
| Ctrl + Shift + Arrow key | Select to the end of the data |
| Alt + = | AutoSum |
| F4 | Add or change $ signs in a reference |
| Ctrl + Shift + L | Turn filters on or off |
| Ctrl + T | Turn a range into a table |
Watch it explained
Press play to watch the animation, or step through it at your own pace with the arrows.
Videos from YouTube tutors
These videos are made by independent tutors on YouTube, not by Trial Balance. Some use US terms or older exam names (for example F7 for FR), but the principles are the same.
Worked example
An invoice listing, with some formulas you might write next to it:
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Invoice | Customer | Region | Amount £ | Status |
| 2 | INV-1001 | Brewline | North | 4,200 | Paid |
| 3 | INV-1002 | Parkside | South | 7,800 | Unpaid |
| 4 | INV-1003 | Brewline | North | 6,100 | Unpaid |
| 5 | INV-1004 | Greenleaf | East | 2,350 | Paid |
| 6 | INV-1005 | Parkside | South | 3,900 | Unpaid |
| Formula | Result | What it tells you |
|---|---|---|
=SUMIFS(D2:D6,C2:C6,"North") | 10,300 | Total sales in the North |
=COUNTIFS(E2:E6,"Unpaid") | 3 | Number of unpaid invoices |
=SUMIFS(D2:D6,B2:B6,"Parkside",E2:E6,"Unpaid") | 11,700 | What Parkside still owes |
=XLOOKUP("INV-1003",A2:A6,D2:D6) | 6,100 | The amount of one invoice |
=IF(AND(D3>5000,E3="Unpaid"),"Review","OK") | Review | Flags large unpaid invoices |
Practice questions
Type or choose your answers, then press Check answer. Questions with a New numbers button can be repeated with different figures.