search

Excel for Accountants: The Functions and Skills That Matter

9/1/2026

Almost every accountant says they are good at Excel. Most are good at the part of Excel they use every day, and that is a different thing. The gap shows up in the workbook nobody else can follow, the reconciliation that takes three hours because the data had to be fixed by hand, and the lookup that silently returned the wrong row.

This post is a skills checklist for Excel and accounting work, organized by the tasks accountants actually do rather than by menu. For each area it covers the functions and techniques that matter, the mistakes that cause real errors, and what "good" looks like. Use it to find your own gaps. If you want structured training, the course options are on our Excel training for accountants page.

One note on versions before we start. Several of the most useful newer functions, including XLOOKUP and the dynamic array functions, exist only in Excel for Microsoft 365 and in Excel 2021 and later. Microsoft states plainly that XLOOKUP is not available in Excel 2016 or 2019. If you share workbooks with clients on older versions, you need to know which functions they can open.

1. Lookups and References

Lookups are the backbone of accounting work in Excel: pulling a GL account description, mapping trial balance accounts to financial statement lines, bringing last year's balance next to this year's.

What to know:

  • Defaults to exact match, searches in either direction, and lets you set a value to return when nothing is found. For most new work it replaces VLOOKUP.
  • INDEX and MATCH. Still worth knowing, because it works in every version and handles two-way lookups (row and column) cleanly.
  • VLOOKUP, understood properly. You will inherit thousands of these. The fourth argument matters: leaving it blank or TRUE means approximate match, which on unsorted data returns a wrong value with no error. That single argument is responsible for a large share of spreadsheet errors in accounting files.
  • Absolute and relative references. Knowing when to use $A$1, $A1 and A$1, and why a copied formula broke.

The test of competence: you can explain what your lookup does when the value is missing, duplicated or formatted differently (a number stored as text, a trailing space). A lookup that returns #N/A is safe. A lookup that returns the wrong row is not.

2. Tables and Structured References

Converting a data range into an Excel Table (Ctrl+T) is the single habit that prevents the most errors in growing workbooks.

Why it matters for accountants:

  • Formulas and PivotTables based on a Table expand automatically when new rows are added. A SUMIFS on A2:A500 silently ignores row 501.
  • Structured references such as =SUM(Sales[Amount]) read like a sentence and are far easier to review than =SUM(D2:D500).
  • Calculated columns fill consistently, so one row cannot quietly carry a different formula from the rest.

The test of competence: your monthly data lands in a Table, and nothing downstream needs its ranges edited when next month's data is pasted in.

3. Conditional Summing and Counting

This is where most accounting analysis happens: totals by account, by vendor, by department, by period.

What to know:

  • SUMIFS, COUNTIFS and AVERAGEIFS with multiple criteria, including date ranges (">="&StartDate) and wildcards.
  • SUMPRODUCT for weighted calculations and conditions that SUMIFS cannot express.
  • Dynamic array functions, in versions that have them: FILTER to pull matching rows, UNIQUE to list distinct values, SORT to order them. UNIQUE plus SUMIFS builds a summary that updates itself.

The common error: criteria that do not match because the data is dirty. A SUMIFS on vendor name will not pick up "Acme Supply " with a trailing space, and a lookup for account 4000 will not find "4000" stored as text. The totals look complete and are not. Clean the data first, which brings us to the next skill.

4. Data Cleaning

Accountants spend a surprising share of their Excel time fixing data exported from other systems: bank downloads, ERP reports, payroll registers, client spreadsheets.

What to know:

  • TRIM, CLEAN and SUBSTITUTE for spaces and stray characters. TRIM does not remove the non-breaking space that many web and system exports contain; SUBSTITUTE with CHAR(160) does.
  • VALUE, DATEVALUE and Text to Columns for numbers and dates that arrived as text.
  • LEFT, RIGHT, MID, FIND and TEXT for splitting account strings and rebuilding keys.
  • Removing duplicates carefully, after deciding what "duplicate" means for that dataset.
  • Power Query (Get & Transform) for any cleanup you repeat every month. You record the steps once, and the next month's file is cleaned by clicking Refresh. For recurring imports, this is the biggest time saver in Excel that most accountants do not use.

The test of competence: monthly imports are cleaned by a repeatable process, not by hand, and you can show what was changed.

5. Reconciliation Techniques

Reconciliation is the accounting task Excel is most often used for, and the one where technique matters most.

What to know:

  • Matching keys. Building a key from several fields (date, amount, reference) so that two lists can be compared reliably.
  • Two-sided lookups. Checking each side against the other, so that items missing from either list are found. A one-sided lookup finds only half the differences.
  • COUNTIFS to detect duplicates on either side before matching, since duplicates break one-to-one matching.
  • Excel stores numbers to about 15 significant digits in floating-point form, so two numbers that display identically can differ by a tiny amount. Use ROUND in comparisons (=ROUND(A2-B2,2)=0) rather than testing raw equality.
  • Conditional formatting to highlight unmatched or mismatched items, so exceptions are visible rather than buried.
  • An unexplained difference that is always visible. A good reconciliation shows the remaining difference in a cell that should be zero, and makes it obvious when it is not.

The test of competence: your reconciliation surfaces differences rather than hiding them, and a reviewer can see each reconciling item and its support. Our year-end payroll reconciliation checklist shows the kind of reconciliation where these techniques pay off.

6. Conditional Logic That Stays Readable

What to know:

  • IF, IFS, AND, OR and when nesting has gone too far.
  • IFERROR, used sparingly. Wrapping a lookup in IFERROR(…,0) turns a missing account into a zero, which hides exactly the error you need to see. Prefer XLOOKUP's not-found argument, with a visible message.
  • LET, in newer versions, to name intermediate results inside a formula so that long calculations become readable.
  • Lookup tables instead of nested IFs for rates, brackets and thresholds. A tax or commission table in cells is easier to update and review than ten nested IFs.

The test of competence: someone else can read your longest formula and say what it does.

7. Summarizing With PivotTables

PivotTables summarize thousands of rows in seconds: GL detail by account and month, AP by vendor, revenue by customer.

What to know: building a PivotTable from a Table, grouping dates by month and quarter, showing values as a percentage or difference, filtering with slicers, and refreshing after the data changes. Equally important is knowing when a PivotTable is the wrong tool: when the output feeds a formatted report that has to look the same every month, a formula-driven layout is often easier to control.

Typical accounting uses include AR aging buckets, vendor totals for year-end information returns and department variances. The Tackle Excel Pivot Tables webinar is the focused training option.

8. Dates and Periods

Accounting runs on periods, and Excel stores dates as serial numbers, which is both useful and a source of errors.

What to know:

  • EOMONTH for period-ends and month-based schedules
  • EDATE for adding months, useful in prepaid and lease schedules
  • DATEDIF or simple subtraction for days outstanding in an aging
  • YEAR, MONTH and TEXT to create period keys for grouping
  • NETWORKDAYS for business-day calculations

The common error: dates imported as text, which sort alphabetically and fail every date comparison. Check with ISNUMBER before trusting a date column.

9. Financial Functions You Will Actually Use

Not the whole financial function library, just the ones that appear in accounting work:

  • PMT, IPMT and PPMT for loan schedules and splitting payments into interest and principal
  • NPV and XNPV, IRR and XIRR, with XNPV and XIRR preferred when cash flows are irregular
  • SLN and DB to check depreciation figures, while remembering that tax depreciation follows its own rules and conventions
  • ROUND, ROUNDUP and ROUNDDOWN, applied deliberately so schedules foot

These matter in practice when you build a schedule a client or lender will rely on. Our posts on financial ratio analysis and working capital analysis are good examples of work that depends on reliable spreadsheet calculations.

10. Building Workbooks a Reviewer Can Trust

This is the skill that separates an accountant who uses Excel from one whose work can be relied on. A workbook only one person can understand is a single point of failure.

Structure:

  • Separate inputs, calculations and outputs, ideally on different sheets, with inputs clearly marked
  • No hard-coded numbers inside formulas. A rate typed into a formula is invisible and gets missed when it changes. Put it in a labeled input cell.
  • One formula per column, copied consistently, so that an odd formula stands out
  • Flow left to right and top to bottom, so the logic can be followed

Controls:

  • Check cells that confirm totals tie to the source (the trial balance, the bank statement) and show the difference
  • Footing and cross-footing checks on any schedule
  • Source and date notes for every imported dataset
  • A cover or notes sheet explaining purpose, sources, preparer and reviewer

Review tools: know how to use Trace Precedents and Trace Dependents, Show Formulas (Ctrl+), Evaluate Formula, and Go To Special to find constants and formulas. A reviewer who cannot use these is reviewing only the visible numbers.

The test of competence: a colleague can pick up your workbook next year, roll it forward, and trust the result without calling you.

11. Presenting the Numbers

The last step is making the output usable: clean number formats, consistent signs, clear headings, charts only where they help, and print settings that work. A dashboard summarizing key figures for a client or a partner can save a meeting. The High Impact Excel Dashboard Edition webinar covers that area.

12. Excel and AI

AI tools can now write formulas, explain inherited ones and suggest how to clean data. They are useful, and they make the review skills above more important, not less. A formula from an AI tool must be tested against cases where you know the answer, especially edge cases such as blanks, text and duplicates. Our post on ChatGPT prompts for accountants includes a spreadsheet-assistant pattern and how to check its output. Never paste client data into an unapproved tool to get help with a formula; a de-identified sample works just as well.

A Self-Assessment Checklist

Score yourself honestly on each item: comfortable, partly, or not yet.

  • I use XLOOKUP or INDEX/MATCH, and I know what my lookup does with missing values
  • I know what an omitted VLOOKUP fourth argument does
  • My recurring data lives in Tables, and my formulas reference them
  • I can build a multi-criteria SUMIFS with a date range
  • I can clean an exported file without retyping, and I use Power Query for monthly imports
  • My reconciliations compare both sides, round before comparing, and show the unexplained difference
  • I avoid IFERROR wrappers that turn errors into zeros
  • I can build and refresh a PivotTable from a Table and group by month
  • I use EOMONTH and EDATE for period schedules
  • My workbooks separate inputs, calculations and outputs, with check cells
  • I can trace precedents and find hard-coded numbers in someone else's file
  • I test any AI-written formula against known answers

Anything marked "not yet" is the place to start.

Where to Build the Skills

Most accountants get the fastest return from three areas: lookups, data cleaning, and reviewable workbook structure. They reduce errors and save hours every month, especially during busy season. The Essential Excel Skills course is our core Excel option, and the full range is on the Excel training for accountants page.

Make Excel a Strength You Can Prove

Excel for accountants is not about knowing every function. It is about producing work that is correct, repeatable and reviewable. Use the checklist above to find your gaps, then close them in order of the time they cost you. To start with the core, look at Essential Excel Skills.

Frequently Asked Questions

What Excel skills do accountants need most?

The most valuable are lookups (XLOOKUP or INDEX/MATCH), Tables, conditional summing with SUMIFS, data cleaning including Power Query, reconciliation techniques, PivotTables, date functions for period schedules, and building workbooks that a reviewer can follow. Advanced charting and macros matter less for most roles. The real dividing line is whether your workbook can be reviewed, reused next period and trusted by someone else, not how many functions you know.

What are the most important Excel formulas for accounting?

For day-to-day accounting work: XLOOKUP or INDEX/MATCH, SUMIFS and COUNTIFS, IF and IFS, ROUND, EOMONTH and EDATE, TRIM and SUBSTITUTE for cleaning, and PMT, IPMT and PPMT for loan schedules. In newer versions, FILTER, UNIQUE and LET are very useful. Knowing how each behaves with missing or badly formatted data matters as much as knowing the syntax, because that is where silent errors come from.

Is XLOOKUP better than VLOOKUP for accountants?

Generally yes, where it is available. XLOOKUP defaults to exact match, can look left or right, and lets you specify what to return when nothing is found, which avoids the common VLOOKUP error of an approximate match on unsorted data. The catch is compatibility: Microsoft notes that XLOOKUP is not available in Excel 2016 or 2019. If clients or colleagues use older versions, INDEX/MATCH is the safer choice for shared files.

How do accountants use Excel for reconciliations?

A sound Excel reconciliation builds a matching key from several fields, checks both lists against each other so items missing from either side are found, flags duplicates with COUNTIFS before matching, rounds before comparing because of floating-point precision, and highlights unmatched items. It ends with an unexplained-difference cell that should be zero. Each reconciling item should be listed with its support so a reviewer can follow it.

What is Power Query and should accountants learn it?

Power Query, labeled Get & Transform in Excel, is a tool for importing and cleaning data with recorded steps. Once you set up the cleanup for a monthly export (removing blank rows, splitting columns, fixing dates and text numbers), next month's file is processed by clicking Refresh. For accountants who handle recurring bank, ERP or payroll exports, it is one of the highest-return Excel skills to learn.

How can I make my Excel workbooks easier to review?

Separate inputs, calculations and outputs. Keep hard-coded numbers out of formulas and put assumptions in labeled input cells. Use one consistent formula per column, Tables with structured references, and check cells that tie totals to the source and show any difference. Add a notes sheet with purpose, sources and preparer. A reviewer should be able to follow the logic with Trace Precedents without calling you.