Excel Account Reconciliation Best Practices

Overview

If you’re an accountant charged with preparing or reviewing account reconciliations, chances are you’re using Excel a lot.  Even with a variety of purpose-built account reconciliation tools available, Excel is still heavily used throughout the process, and Excel files themselves are often the end result.

Excel can be a really effective tool for performing account reconciliations if you take the right approach and build in the right controls.  There are many features within Excel that help with this, but often go unused.  In this article, we’ll cover ten best practices you should consider using with an Excel-driven account reconciliation process.  Below, you can get a prepaid expense example file that has several of these best practices included.

Tip 1 – Keep files organized

As an auditor, Controller, and consultant, I’ve seen a lot of crazy account recs that were really tough to follow.   Keeping them organized makes it easier for everyone, including yourself!  Here are some specific ways you can keep your recs organized:

• Use a consistent folder structure – by period, by company, by account number/group, by risk level – really it’s up to you, but be consistent!
• Use consistent file names – include the account number, company ID, and other account segments, if applicable.  Include the period (i.e. 2024-06).  When these are used consistently, they are easier to find, reference, and automate around.
• Use consistent worksheets: 1) a leadsheet summarizing the accounts reconciled, 2) a sheet describing the policies, purpose, and procedures, 3) a raw data source, and 4) supporting schedules with calculations, roll-forwards, etc. (could be multiple sheets).

Helpful macro: Create Subfolders – this can create hundreds of folders in one bulk step!  Even better, you can reuse it quickly with formulas linked to inputs like dates.

Tip 2 – Use consistent data sources

You’d think this tip is obvious, but sometimes people use different data sources to support their reconciliations.  That usually results in a lot of extra manual work.  When the data source is consistent, you can automate a lot of manual work (see the other tips below!).  It’s also quicker to navigate and review because of familiarity.

Ideally, you should be able to get all the source data for your rec in one file, rather than separate files for each location, cost center, company, etc. when you’re reconciling multiple accounts together.  If you find yourself having to run multiple reports to get lots of source data, there are generally ways to automate it with query connections, robotic process automation (RPA), or by optimizing the source report.

Helpful macro: Create Mapped File – this can restructure a data source into a required format (i.e. specific columns in a specific order), and can add formulas or fixed values to fill in any additional data you might need.

Tip 3 – Use formulas wherever you can

Despite having hundreds of formula functions available in Excel, most people seem to only use the most basic ones and result to copying and pasting data all over instead.  Formulas allow you to quickly update your recs with minimal effort after refreshing your source data.  This saves a lot of time, and greatly reduces the risk of errors.

Here are some functions that can really help streamline your Excel-driven account rec process:

• SUMIFS – Add up various amounts based on conditions like dates, locations, statuses, categories, etc.
• XLOOKUP – A newer lookup function – this allows you to apply lookups to your source data.
• MID – A text-manipulation function that allows you to extract text from descriptions, labels, etc.
• IFS – A logic function that allows you to identify when certain conditions are met.
• INDIRECT – An advanced function that lets you build dynamic references – extremely helpful for referencing across sheets and columns.

Excel has released several dynamic array functions (DAFs) over the past few years, and these add even more automation potential by allowing you to spill values across multiple cells.  UNIQUE, FILTER, SORT, and SEQUENCE are some of the DAFs worth using most frequently.  Learn more about DAFs in this article.

Helpful macro: Formula Picker – this contains several shortcuts for working with formulas, including entering or searching for your favorite formulas.

Tip 4 – Automate the repetitive work

Formula functions are a great way to automate your work, but there’s still the need to compile the source data, enhance it, and sometimes perform calculations with it beyond what formula functions can easily do.  This is where advanced tools like Power Query and macros can save you a ton of time.

Power Query (technically called “Get Data” on the Excel Data ribbon tab) is extremely useful for getting data efficiently and enhancing it.  Data can be brought in from websites, database connections, flat files, other Excel files, PDF files, and even pictures.  From there, you can filter, sort, add calculated fields, delete fields, unpivot fields, merge with other data sources, and much more.  70-80% of the manual steps in your rec, journal, and reporting processes can often be automated with Power Query!

Macros are another great way to automate repetitive processes, especially if you need to manipulate the workbook (i.e. add or replace sheets) or perform advanced calculations that formula functions and Power Query might otherwise struggle with.

As you go through your processes, write down every single step you do – down to the keystroke – and determine the best way to automate it.  If you’re unsure how you can automate it, that’s where we’d love to help!

Helpful macro: Modify Fields – this can add, remove, rename, or reformat fields in your data source – great for those several repetitive steps you do every time you download a data source.

Tip 5 – Don’t use hard-coded numbers

It’s almost never a good practice to include hard-coded numbers in account recs or other accounting files.  Eventually, someone will have a question, and you won’t have any idea where the number came from.  This includes both hard-coded numbers that are the resulting value, as well as hard-coded numbers typed within a formula (i.e. =SUM(A2:A49)+12345) – those are even worse!  Obviously you’ll have some hard-coded values in your source data, and that’s okay, assuming you haven’t changed the values and that source data can be recreated.

It’s a best practice to clearly separate input values from formulas and lookup values.  Data points like dates, locations, accounts, etc. are often best to keep on your leadsheet or a sheet specifically for inputs and assumptions.  I like to highlight these yellow to indicate an area for inputs.

Helpful macro: Conditional Formatting Picker – this can highlight hard-coded amounts or formulas containing them in one step!  It also has several other helpful conditional formatting options.

Tip 6 – Include an audit trail

If you’re a recovering auditor like myself, the “every workpaper should stand alone” consideration is burned deep into your brain.  Audits are becoming more demanding, so make sure you consider the audit trail of whatever goes through your account recs.  The good news is it’s getting easier to support a solid audit trail since so many financial apps are now web-based – you can simply include a URL to the underlying transaction or supporting documentation in your data source or somewhere else in your workbook.

In addition to supporting the underlying transactions in your source data, make sure you include some documentation with how any calculations work within your workbook.  The more complex the calculations, the more documentation you should probably consider adding.

Helpful macro: Show Cell References – this helps you navigate workbooks with many cell references, jumping back and forth between the cells and auditing the formulas.

Tip 7 – Add comments where necessary

Comments and documentation are important, especially when your account rec, underlying transactions, and calculations are complex or uncommon.  A solid set of policies, purpose, and procedures is a good start.  Adding cell comments or notes is even better because it adds documentation right where the calculation is happening, in an uncluttered way.

Cell comments and notes are also helpful for reviewer comments so they are right there with the work being reviewed.  I’ve found it’s very helpful to have a consolidated list of review comments in one sheet where you can track who owns it and the resolution.  The macro below provides a great option for this!

Here’s a breakdown of these two options in Excel:

• Cell notes (formerly called comments) – these are fairly basic – they live with the cell and will show up when you hover over the cell (indicated with a red triangle).  They can be resized, formatted to some extent, and the visibility can be toggled on and off.
• Cell comments (the newer version) – these are like comments you tend to find in social media – they support threading, mentions, notifications, and can be “resolved” when they are done.  If you have legacy workbooks full of “notes”, they can be converted to threaded comments!

Helpful macro: Comment Picker – this provides several shortcuts for working with cell comments, including managing a central sheet where comments can be tracked.

Tip 8 – Include error checks throughout

As your data grows and your workbook structure changes, it’s easy to have formula errors, especially for completeness.  Error checks ensure that everything is properly accounted for and referenced back and forth.  Adding error check calculations only works if they are reviewed, so making them as easy to find and review as possible is quite helpful.

I take two approaches that make error checks easy on reviewers:

• I use conditional formatting where red means there’s an error, and green means it’s okay (kind of like at a traffic light).
• In workbooks with a lot of sheets, I’ll include the error checks within each of the sheets, plus a centralized sheet full of error checks, where they can all be reviewed in one place.  Just remember to update that sheet when new error checks are added!

Helpful macro: Conditional Formatting Picker – this can highlight items in green or red when the value is outside of thresholds you can set (the default being zero).  It also has several other helpful conditional formatting options to find errors quickly.

Tip 9 – Lock down your completed files

Many of us start our recs by copying and updating files from the previous period.  Inevitably, someone will make updates to the wrong file, delete a file, or move a file without realizing it.  Assuming you do realize it, it can create several hours of rework.  I once had a folder full of 200 rec files disappear, and while there were backups for some items, it was still a weekend full of rework for my team!

The best approach here is to use a workflow-driven reconciliation solution like BlackLine or FloQast.  These can lock down the contents of the files and the files themselves once they are reviewed.  Outside of tools like those, you can copy completed files to special “completed” folders that have more restrictive access and/or make them read-only once completed.

Helpful macro: Toggle File Read Only Recommended – this lets you quickly toggle a file to be read-only, which helps prevent unwanted changes.

Tip 10 – Start the process before the month ends

One of the biggest opportunities accountants have for efficiency and quality in their rec process is to perform recs continuously, and certainly not after close is done.  If you complete your recs after the books are closed and you find an issue, you’re either doing a lot of rework or pushing adjustments to the next period!

Continuous doesn’t mean in real-time – it can mean every day or two, once a week, twice a month, or even a few days before the month ends, then update quickly at month end.  There are several benefits to this approach:

• You’re shifting work from month-end (an already busy time!) to the month itself when generally there is more bandwidth.
• You’re addressing errors sooner – when you or others will be more likely to know how to correct them.
• Other tangential processes will be ready sooner (i.e. journal entries).
• The quality of your books will improve, because you will have completed reconciliations before publishing financial results.

Not all accounts need to be reconciled continuously or even every month.  Take a risk-based approach and balance the effort to prep the rec (i.e. gather data, enrich, run calculations) with the actual work itself (review, categorize, etc.).  There’s almost always at least some admin work you can perform in advance of the month ending.

Helpful macro: Calculate Amortization Lines – this helps automate the process of calculating amortization for prepaids, deferred revenue, and other accounts with several calculation options.  it’s especially helpful for companies with non-standard calendars (i.e. 4-5-4) because it can accommodate any kind of calendar.

Summary

The more accounts you are ultimately responsible for reconciling, the more important it is to have a consistent, efficient, and controlled process for accountant reconciliations.  When Excel is your primary tool for reconciling, it’s worth adding some documentation and control for the benefit of the entire team.  By following the best practices above, you’ll help ensure errors are minimized and work is done in a timely manner.  For even more efficiency, the macros above were purpose-built for processes just like account reconciliations.  Give them a try and free up valuable time for your team to help support your business.

What tips and tricks have you used to support a high-quality and efficient account reconciliation process?  Let us know in the comments below!