Accountants should master lookups (XLOOKUP or INDEX/MATCH), conditional sums (SUMIFS, COUNTIFS), PivotTables, date and text functions, Power Query for cleaning and combining data, financial functions (NPV, IRR, PMT), reconciliation techniques, clear model structure, and error checks. These skills save hours and reduce mistakes in reporting and analysis.
Core formulas
| Task | Functions |
|---|---|
| Look up account details | XLOOKUP, INDEX and MATCH |
| Sum by criteria | SUMIFS, COUNTIFS, AVERAGEIFS |
| Handle errors | IFERROR, IFNA |
| Dates and periods | EOMONTH, EDATE, YEAR, MONTH |
| Clean text | TRIM, CLEAN, TEXT, LEFT, RIGHT |
| Financial | NPV, IRR, XNPV, XIRR, PMT |
PivotTables
Summarise general ledger data by account, period or cost centre in seconds. Learn grouping, calculated fields and refreshing.
Reconciliations
- Use lookups to match transactions between bank statements and ledgers.
- Highlight unmatched items with conditional formatting.
- Use Power Query to combine and clean exports.
Power Query
Import, clean and merge data from multiple files repeatably. Ideal for monthly reporting where the same steps repeat.
Model structure
- Separate inputs, calculations and outputs.
- Colour-code inputs.
- Avoid hard-coded numbers in formulas.
- Document assumptions.
- Use consistent formulas across rows.
Error checking
- Totals that should match (check sums).
- Balance checks, such as assets equal liabilities plus equity.
- Trace precedents and dependents.
- Review for broken links.
AI in Excel
AI features can suggest formulas and explain data. Use them to speed up, but always verify results, especially in financial reporting.
Practice
Recreate a trial balance summary, bank reconciliation and monthly management report from sample data. Our data analytics course and finance tutoring build these skills with real examples.
Frequently asked questions
Is Excel still important for accountants with modern software?
Yes. Accounting systems export data that accountants analyse and report in Excel.
Should accountants learn Power BI?
It is increasingly valuable for management reporting and dashboards.
What is the most useful Excel skill for accountants?
Lookups and SUMIFS combined with PivotTables cover a large share of everyday work.
