Excel for accounting students
Accounting runs on Excel. This tutorial teaches the functions you will actually use, one per unit, on the kind of ledger extract you will be handed at work.
Twenty-eight units, in the order a tutorial workbook teaches them. The first four are the basics: SUM, AVERAGE, CONCATENATE and PROPER. After those come locked references, lookups, conditions, subtotals, splitting a code apart, weighted averages, and what a pivot table is really doing.
The last units are the ones that decide whether the numbers can be trusted: checking an import before you report off it, saying what happens when a code is not on file, and dates, which is where ageing a ledger, testing a cut-off and working out a due date live.
Every unit ends with questions that do not name a function, so you finish each one by working out which function the question needs. Then there are sixty-four challenges, where nothing is named at all and most answers need more than one function.
Continue with SUM| A | B | C | D | |
|---|---|---|---|---|
| 1 | Invoice | Supplier | Status | Days overdue |
| 2 | INV-8801 | Brightwell Paper | Overdue | 12 |
| 3 | INV-8802 | Calder Utilities | Queried | 47 |
| 4 | INV-8803 | Meridian Freight | Overdue | 63 |
| 5 | INV-8804 | Ashgrove Catering | On terms | 0 |
| 6 | INV-8805 | Thornbury Print | Overdue | 38 |
| 7 | ||||
| 8 | Overdue rows | 3 |
The units
One function per unit, on a real extract. Work through them in order, because each unit assumes the one before it.
- 01
The SUM function
Every ledger total, every control account check and every management pack starts with adding a column up and checking it against something that should agree.
=SUM(first cell:last cell)6 tasks - 02
The AVERAGE function
A total says how big the month was. An average says what one line usually looks like, which is how you spot the line that is out of step.
=AVERAGE(first cell:last cell)6 tasks - 03
The CONCATENATE function
Exports split a name, a reference or a code across two columns. Remittance advices, chase lists and journal narratives need it back in one.
=CONCATENATE(first piece, second piece, ...) or =A2&" "&B26 tasks - 04
The PROPER function
Older systems store names in capitals, and those names end up on remittance advices, statements and the statutory accounts.
=PROPER(cell)6 tasks - 05
Locking a reference with $
Every share, every percentage of sales and every rate applied down a column divides by the same figure each time. Without the pin, row three divides by the wrong thing and the column stops adding to 100%.
=C2/SUM($C$2:$C$7)6 tasks - 06
The VLOOKUP function
Terms, credit limits, account names and approval limits live in one list and are needed in another. Fetching them is the most common thing an accountant asks Excel to do.
=VLOOKUP(value to find, block to search, which column across, FALSE)6 tasks - 07
The COUNTIF function
Filtering hides rows; it does not tell you how many there should be. Counting first tells you what the filter should show, so you can tell when it does not.
=COUNTIF(column to look down, "what a row has to say")6 tasks - 08
The SUMIF function
Subtotals by supplier, by department, by category and by account are what a set of management accounts is made of.
=SUMIF(column to test, "what it has to say", column to add up)6 tasks - 09
The IF function
Chase or hold, approve or query: rules like these get applied a hundred times a month, and writing them down once means nobody has to remember them.
=IF(test, answer when true, answer when false)6 tasks - 10
The LEFT, RIGHT and MID functions
Codes arrive glued together: entity, cost centre and account in one cell, or supplier and invoice in one line off the bank. Nothing can be grouped or matched until the pieces stand on their own.
=LEFT(cell, how many) =RIGHT(cell, how many) =MID(cell, start at, how many)6 tasks - 11
The SUMPRODUCT function
Rates are never the same for everyone. Fees by grade, mileage by vehicle, cost per head by department: all of them multiply row by row and then add, and writing that out cell by cell gives you a formula nobody can review.
=SUMPRODUCT(first column, second column)6 tasks - 12
The AND and OR functions
A rule that catches too much colours the whole sheet, and a sheet that is all red says nothing.
=AND(first test, second test) =OR(first test, second test)6 tasks - 13
The MAX and MIN functions
The largest posting is the one a reviewer opens first, and the smallest balance is the one not worth a payment run of its own.
=MAX(first cell:last cell) =MIN(first cell:last cell)6 tasks - 14
The SUMIFS function
One condition gives a subtotal; two gives a cross-tab, which is where questions like which department overspent on which category get answered. It is what a pivot table does, written down.
=SUMIFS(column to add up, first column to test, what it says, second column to test, what it says)6 tasks - 15
The COUNTIFS function
Almost nothing you are asked to count in accounting has only one condition. It is not how many invoices are overdue; it is how many are overdue and still unapproved.
=COUNTIFS(first column to test, first condition, second column to test, second condition)6 tasks - 16
The SUBTOTAL function
Filter a ledger to one supplier, put SUM under it, and the total is still the whole ledger, sitting under a screen that says otherwise.
=SUBTOTAL(job number, range)6 tasks - 17
The IFS function
Approval limits, ageing bands, discount tiers and materiality thresholds are all ladders with three or four rungs. Written as IF inside IF inside IF they become unreadable, and then wrong.
=IFS(first test, first answer, second test, second answer, ...)6 tasks - 18
The ROUND function
Narrowing a column does not round anything. It hides the digits, and the total still adds up the ones you cannot see, which is where an allocation that should come to 1,000.00 comes to 999.99.
=ROUND(number, decimal places)6 tasks - 19
The VALUE function
This is the most common thing wrong with an export. SUM does not fail on a column of text; it returns nothing, or leaves out the rows that came across badly, and the report goes out short.
=VALUE(the cell holding the figure)6 tasks - 20
The COUNTBLANK function
A gap in a column is invisible in a total. Ten invoices with three amounts missing still add up to the seven that are there, and nothing on the page says so.
=COUNTBLANK(the range to check)6 tasks - 21
The IFERROR function
A page of errors is a page nobody reads. But wrap it round a formula that is simply wrong and you have not fixed anything, you have hidden it. Use it only when you know what the failure means.
=IFERROR(the formula to try, what to show if it cannot answer)6 tasks - 22
The XLOOKUP function
A VLOOKUP breaks the day somebody inserts a column, because the number counting across now points one column short, with no error to catch it.
=XLOOKUP(value to find, column to search, column to read back, what to show if there is no match)6 tasks - 23
The INDEX and MATCH functions
A VLOOKUP can only read rightwards from the column it searched, and real reference tables are not built to suit it. The code is often in the middle, with the description you want to its left.
=INDEX(column to read from, MATCH(value to find, column to search, 0))6 tasks - 24
The DAYS function
Every ageing schedule, every days-sales-outstanding figure and every cut-off test rests on this. A ledger extract arrives with dates on it, not with a days column.
=DAYS(the later date, the earlier date)6 tasks - 25
The MONTH, YEAR and DATE functions
Cut-off is the classic year-end test: did this transaction land in the period it was dated in? You cannot answer that from a filename. You read the period off the date on each line.
=MONTH(the date) =YEAR(the date) =DATE(year, month, day)6 tasks - 26
The EOMONTH and EDATE functions
End of month following terms are everywhere in payables, and so is a prepayment released over twelve months. Both are date arithmetic, and February is where doing it by hand goes wrong.
=EOMONTH(a date, how many months to move)6 tasks - 27
The DATEDIF function
An ageing schedule is a statement about one moment in time. Built on TODAY it agrees to the ledger on the day it is made and disagrees with it every day after, including the day the auditor opens it.
=DATEDIF(earlier date, later date, "d" or "m" or "y")6 tasks - 28
Ranges with room to grow
A completeness check written over a generous block reports hundreds of gaps that are really rows nobody has typed yet, so nobody looks at it twice and the one real gap goes out in the numbers.
=TRIMRANGE(range) =B5:.B1000 =B5.:B1000 =B5.:.B10006 tasks
Twenty-eight units, six tasks each. Progress shows how far each skill has come.
Once a unit has taught you the function
A unit names what it is teaching. These two do not, which is the point of both of them.
Pick the tool yourself
A real extract and a question somebody would actually ask about it. Nothing says which function answers it, and past the first rung most of them need more than one. Five levels, ending at reconciliations and cut-off tests.
Start the ladder 59 functionsThe reference
What each function is for, what comes back, every argument named, and worked examples on a ledger extract. The results are computed by the same evaluator that marks your answers, so no example here can claim something the formula would not do.
Look something up