Excel for accountants: functions and techniques

The Excel skills accountants actually use

Excel for accountants comes down to a short list: structured tables, lookups with XLOOKUP or INDEX-MATCH, SUMIFS for summarising by account and date, pivot tables, Power Query for importing and cleaning data, data validation, reconciliations and check cells. It matters because most finance work outside the ledger, from month-end schedules to audit requests, still passes through a spreadsheet.

This guide works through each technique with a small EUR example you can rebuild in a few minutes, then covers the controls that make a workbook trustworthy and the signs that a process has outgrown spreadsheets. Function names and version availability follow Microsoft's own documentation. Newer functions such as XLOOKUP and FILTER are in Excel for Microsoft 365, Excel 2021 and Excel 2024, and IFS needs Excel 2019 or later; if a colleague uses an older version, INDEX-MATCH works everywhere.

Start with clean data: one table, one row per transaction

Every technique below works better on a proper table: one header row, one row per transaction, one type of data per column, no merged cells, no blank rows and no subtotals inside the data. Select the range and press Ctrl+T to turn it into an Excel table, then give it a name such as GL or Invoices. Tables expand automatically when you add rows, so formulas and pivot tables that refer to them keep working next month.

Formulas can then use structured references, such as GL[Amount] instead of C2:C5000, which read like English and do not break when rows are inserted. Store dates as real dates, not text, and store amounts as numbers without currency symbols typed in; a text value that looks like a number is the most common reason a SUMIFS returns less than expected.

Lookups: XLOOKUP and INDEX-MATCH

Lookups pull a value from one table into another: a customer name onto an invoice list, an account type onto a trial balance, a budget onto actuals. Worked example: an Invoices table has columns Invoice, CustomerID, Date and Amount, and a Customers table has CustomerID, Name and CreditLimit. To bring the customer name into Invoices, add a column with =XLOOKUP([@CustomerID], Customers[CustomerID], Customers[Name], "Not found").

XLOOKUP searches the lookup array and returns the matching item from the return array. Its default match mode is exact, and the fourth argument supplies the text to show when there is no match, so a missing customer is flagged as Not found rather than a #N/A error. The same result in any version of Excel is =INDEX(Customers[Name], MATCH([@CustomerID], Customers[CustomerID], 0)), where the 0 forces an exact match.

Avoid VLOOKUP for new work. It needs the key in the first column, uses a column number that silently points to the wrong column when someone inserts a column, and defaults to an approximate match if the last argument is left out, which can return a plausible but wrong value.

SUMIFS: summarising by account, period and branch

SUMIFS adds the values that meet several conditions at once, which makes it the workhorse for trial balance summaries, management accounts and budget comparisons. The syntax is SUMIFS(sum_range, criteria_range1, criteria1, and further pairs), and Microsoft allows up to 127 range and criteria pairs.

Worked example: a GL table holds four postings to account 4000 Sales: 1,000 on 5 January, 2,500 on 14 January, 750 on 28 January and 900 on 3 February. With the period start 1 January 2026 in B1 and the period end 31 January 2026 in C1, the January total is =SUMIFS(GL[Amount], GL[Account], 4000, GL[Date], ">="&$B$1, GL[Date], "<="&$C$1). The result is 4,250, because 1,000 plus 2,500 plus 750 is 4,250 and the February posting of 900 is outside the dates.

Point the criteria at cells rather than typing them into the formula, so changing B1 and C1 rolls the whole report to the next month. COUNTIFS uses the same pattern to count items, which is useful for matching, as shown in the reconciliation section below.

Worked example: an aged receivables report

Ageing combines a date calculation, a banding formula and SUMIFS or a pivot table. Put the report date, 31 March 2026, in cell H1. In the Invoices table add a Days column, =$H$1-[@Date], and a Band column, =IFS([@Days]<=30, "0-30", [@Days]<=60, "31-60", [@Days]<=90, "61-90", TRUE, "Over 90").

Four open invoices give these results. The band totals are 1,200, 3,500, 2,000 and 800, which add up to 7,500, the same as the four invoices. That total must then agree to the trade receivables control account in the general ledger; an ageing that does not tie to the ledger is not finished.

• INV-101, dated 15 January 2026, EUR 2,000: 75 days, band 61-90.
• INV-102, dated 20 February 2026, EUR 3,500: 39 days, band 31-60.
• INV-103, dated 10 March 2026, EUR 1,200: 21 days, band 0-30.
• INV-104, dated 5 December 2025, EUR 800: 116 days, band Over 90.
• Band total formula, for example: =SUMIFS(Invoices[Amount], Invoices[Band], "31-60") returns 3,500.

Pivot tables and Power Query

A pivot table summarises a table by any field without formulas. For the ageing above, select the Invoices table, choose Insert, PivotTable, and drag Name to Rows, Band to Columns and Amount to Values. Add Date to Rows and group it by month to see which months the open balances come from, or put a cost centre field in Filters to review one department. Pivot tables do not update themselves: press Refresh after the source data changes, and always check the grand total against the ledger.

Power Query, called Get and Transform in Excel, imports and cleans data with recorded steps. A typical accounting use is combining monthly bank or sales exports that share a layout.

• Save each month's CSV export into one folder.
• In Excel choose Data, Get Data, From File, From Folder, and select the folder.
• Choose Combine and Transform to stack the files into one table.
• Set data types: dates as Date, amounts as Decimal Number, references as Text so zeros at the start are kept.
• Remove blank rows and unwanted columns, and split any combined description column.
• Choose Close and Load to create a refreshable table.
• Next month, drop the new file into the folder and press Refresh All; every step runs again.

Data validation: stop bad data at the door

Most spreadsheet errors are typed in. Data validation, under Data, Data Validation, restricts what a cell accepts. Use a List source that points to your chart of accounts table, so an account code can only be chosen from valid codes; a Date rule between the period start and end, so a journal cannot be dated in the wrong month; and a Decimal rule greater than zero for quantities and amounts.

Add an input message explaining what the cell expects and an error alert set to Stop for fields that must be right. Shade input cells in one colour and leave formula cells unshaded, so users know where to type. Validation does not check values pasted over it, so pair it with the check cells described below.

Reconciliations in Excel, worked through

A reconciliation proves that two records of the same balance agree, or explains exactly why they do not. For matching, add a Status column to the cash book table: =IF(COUNTIFS(Bank[Ref], [@Ref], Bank[Amount], [@Amount])=0, "Unmatched", "Matched"), and the mirror formula in the bank table. Filter each table on Unmatched to list the reconciling items.

Bank example at 31 March: the bank statement shows 18,400 and the cash book 17,250. Unmatched items are a deposit in transit of 900, unpresented cheques of 2,100 and bank charges of 50 not yet in the cash book. Adjusted bank balance: 18,400 plus 900 minus 2,100 equals 17,200. Adjusted cash book: 17,250 minus 50 equals 17,200. The difference is zero, and the 50 of charges is posted: Dr Bank charges 50 / Cr Bank 50.

Control account example: the receivables ageing totals 7,500 but the general ledger control account shows 7,650. The 150 difference is traced with SUMIFS by source to a manual journal posted straight to the control account with no customer attached. The fix is to reverse it and post the item through the customer's account, so the subledger and the ledger agree again.

Error checks that make a workbook trustworthy

A reviewer should be able to see in one place whether a workbook works. Build a Checks area at the top of the output sheet, with each check returning TRUE or zero when the workbook is right, and conditional formatting that turns a failing check red.

Excel stores numbers with 15 digits of precision in binary floating point, so a sum of many decimal amounts can differ from zero by a tiny fraction. Wrap check cells in ROUND, for example =ROUND(SUM(TB[Debit])-SUM(TB[Credit]), 2)=0, rather than testing for an exact zero.

• Trial balance: total debits minus total credits equals zero.
• Tie-outs: each report total equals the source total, such as ageing total equals the receivables control account.
• Row counts: COUNTA of the source equals the number of rows the report used, so nothing was filtered out by accident.
• No silent errors: use XLOOKUP's if-not-found argument or IFNA for expected gaps instead of wrapping everything in IFERROR, which also hides genuine mistakes.
• No hard-coded numbers inside formulas: put rates and dates in labelled input cells.
• Formula auditing: use Trace Precedents and Show Formulas to review how a key figure is built before the file leaves your hands.

When to move from spreadsheets to an ERP

Spreadsheets are excellent for analysis and poor as a system of record. A worksheet holds at most 1,048,576 rows, but the practical limits arrive much sooner: several people editing copies of the same file, no reliable record of who changed a number, formulas overwritten by pasted values, and a month-end that depends on one person's workbook.

The signs that a process belongs in an ERP are consistent. You re-key the same invoice into a spreadsheet and then into the ledger; reconciliations take days because data comes from exports that never quite agree; an auditor asks who approved an entry and the answer is an email; or the business adds a branch, a currency or a new tax regime and the workbook has to be rebuilt. At that point a system with one database, enforced double entry, period locks and an audit trail replaces the spreadsheet as the record, and Excel goes back to the job it does well: analysis of data exported from that system.

Using Excel alongside Skyline Nexus ERP

Skyline Nexus ERP is built to work with Excel rather than against it. Going in, the chart of accounts can be imported from CSV, XLSX or XLS using a downloadable template, and products, opening stock, contacts, sales, purchases, budgets, assets and employees each have their own import. Bank statements can be imported in CSV, TXT, XLSX or XLS format into Treasury's Bank Reconciliation and matched against the book using the Auto-match screen, which does the job of the COUNTIFS matching described above.

Coming out, the Trial Balance can be viewed as opening, movement and closing balances and exported to Excel or PDF, and the main statements offer Excel export. The Audit Pack produces one XLSX workbook with a sheet per report, including the chart of accounts, trial balance, balance sheet, profit and loss, journal lines, general ledger, sales, purchases and a VAT summary, ready for pivot tables and tie-outs. Journals entered in Skyline Nexus cannot be saved unless debits equal credits, so the first check in your workbook is already enforced at source.