Excel For Accounting and Finance: An Accountants Complete Guide

Matthew Dziak

Excel For Accounting and Finance Professionals

In a mature finance function, the role of accounting operates much like electricity powering your home. You need electricity to run your homes’ appliances, electronics, air conditioning and other functions, but you don’t necessarily think about it throughout the day. But when the electric power goes out in your home, it’s your primary concern and consumes your thoughts. 

Regardless of what you attempt to plug in, if the electricity isn’t available, the device offers no value. Accounting, and its role in the finance function is no different. It’s table stakes for accounting to have the company’s metrics accurate, reliable and readily available. 

If the math doesn’t work, eventually, nobody will. This is why for years, the primary tool to manage the company’s metrics was Excel for Accounting and Finance. Excel is a great tool for processing data and generating concise metrics, but as a standalone, it fails to deliver on many critical aspects of accounting, especially on data aggregation and management efficiency. 

It should come as no surprise that 58% of finance leaders claim eliminating repetitive day-to-day transaction accounting is one of their top three priorities, according to EY’s Digital Disruption Survey.

There’s no dispute that accountants should manage the transactions and report on the company metrics, but you can automate other accounting processes like accounts payable, payroll, billing and other transactional general ledger maintenance. Keep in mind, Excel is very helpful, but it should not be used as your sole tool to close the books and create reporting packages. 

The Accounting Backstory is Not Back Office

Despite what some business owners and executives may believe, accounting is not a back-office function. Instead, it is a necessary component of successful companies to: 

  • Tracks what has happened
  • Accumulates the facts and metrics
  • Reconciles company data and metrics to ensure accuracy
  • Measures the health of the business

The outputs of the accounting or controller function support the financial planning and analysis function, which builds off of the accounting metrics of what has happened (descriptive analytics) to produce forward-looking forecasts and scenario planning for what might happen (predictive analytics) and how to hit the targets (prescriptive analytics). 

For more details on descriptive, predictive and prescriptive analytics and its role in accounting and finance, be sure to check out our blog post on the topic here

Functions to Accelerate Your Excel Spreadsheets 

Excel can be a powerful tool for accounting and having familiarity with commonly used Excel functions for accounting will make your life easier and your work more impactful. Some of the accounting tasks you can deliver with Excel are: 

  • Three statement financial models
  • Balance sheet account reconciliations
  • P&L account analysis & trends review
  • Amortization accruals 
  • Cash flow forecasts
  • Recording for month-end close purposes
  • Expense calculations — and then some!
QUICK EXCEL TIP: For each unique item in a row, all of the attributes related to that row should be on the same line so that you can easily summarize and pivot the data for analysis. Doing this will save you many hours each month for closing and reporting.

To fulfill these deliverables, you’ll need an intermediate understanding of  Excel Functions and tools to use for accounting. Three primary functions are:

  1. Pivot Tables are every accountant's best friend to summarize data. Pivot tables allow you to filter by specific criteria and values and return results by your desired column and row classification. 
  2. Hlookup, Vlookup and Xlookup formulas of relational data to join information from multiple reports into one. Index and Match formulas are also valuable to retrieve specific values and a given cell’s position in a range.
  3. Sumif and Sumifs formulas are necessary to summarize data by specific attributes. This is extremely useful to create financial statements from summary data such as balance sheets, P&L and income statements.

These functions are necessary to generate reports and data visualizations to reveal specific trends and actual outcomes. You can then conduct deep analysis of these results, draw conclusions and provide actionable insights with great efficiency to drive optimal decision making for the business. 

3 Benefits of Excel For Accounting and Finance

A tool that Microsoft released in 1985, and still holds value today speaks to the power of Excel. Sme of the rather distinct benefits of using Excel for accounting include: 

  1. Intuitive - Excel is very intuitive and easy for many to use and gain some mastery of in a short amount of time. It’s widely used in business school curriculum and has been a staple tool for accounting since the late 1980s. 
  2. Flexible - Excel is flexible, with a blank canvas for you to consume your data and automate calculations for many outcomes with a single formula and a few clicks. This allows you to quickly and easily identify certain metrics from a given data set and extrapolate as needed. 
  3. Add-ins - Accounting and finance users of Excel can leverage Add-ins and other tools to automatically extract and transform data for use in automated reporting packages for greater efficiency and productivity.

4 Downsides of Excel For Accounting and Finance

Although Excel can be a reliable and powerful tool for accounting and finance professionals, it certainly has its fair share of limitations. The downsides of only relying on Excel include: 

  1. Although Excel can hold up to 1,048,576 rows and 16,384 columns (according to Microsoft), it is not intended to be used as a database or data warehouse. As spreadsheets grow in size, and more tabs are added, performance issues arise. The dreaded ‘running slow’ message or lag requiring minutes to compute a calculation is not an optimal use of one’s time. 
  2. Excel spreadsheets can grow to be like the Kudzu vine, also known as the, “vine that ate the South”. In the same way that Kudzu grows at a rapid rate, eventually engulfing entire forests, cars and anything in its path, so too can spreadsheets for accounting and finance processes. The addition of too many tabs makes it difficult to manage the data and if you ever hard-code something, you leave yourself susceptible to errors that can take hours or parsing to identify and fix. Version control nightmares also come into play, as you try to lock certain cells or workbooks and ask budget owners to input their metrics into specified areas which lead to confusion. 
  3. Exporting and importing data manually or attempting to use certain tools to pipe data from your other sources of truth doesn’t lead to clean data. Your data in Excel isn’t automatically structured and organized for use, creating repetitive, time-consuming tasks for accounting users. The data modeling and structure from your systems are not preserved for consumption without additional tools.
  4. Spreadsheet formulas and references are also susceptible to manual error. For example, simply pasting a set of data in the incorrect column, or referencing a sheet of data in a formula to the wrong data set due to a typo, will lead to an entire output being incorrect.

Think Outside of the Cell

As we’ve pointed out, Excel is a reliable resource for accounting, but it shouldn’t be the only tool accounting and finance teams rely on. Depending on your Company structure, as well as your own technical skills, some accountants also assume analyst tasks such as reporting, modeling, forecasting and scenario planning. 

When it comes to these outputs, Excel can work for some, but to get the most out of it, requires additional tools like Finance Excel Add-ins to maximize its potential. 

That said, data storage and transactional general ledger management are just a few of the limitations of Excel, after all, it was never intended to be a system of record for accountants. On the other hand, ERP software like Netsuite and Quickbooks Online provides a general ledger to easily register and track past transactions to facilitate reporting. 

For more detailed and robust capabilities, FP&A software offers direct ERP and other system integrations and automations to consolidate all of your financial and operational into a single system for efficient planning, forecasting and analysis at a much deeper level with transaction drill-down details, all while always maintaining version control. 

Successful finance and accounting team guide

It’s a Great Time to be in Accounting and Finance

As the Great Resignation has many shuffling towards greater opportunities, the desire for accountants remains in high demand, and rightfully so. On top of that, based on estimates by the AICPA, 75 percent of today’s CPAs will retire in the next 15 years. It’s never been a better time to be an accountant or controller. 

Not only is there tremendous demand for accounting talent, but the needs of companies and their expectations from contributors are also compounding. To meet expectations, it’s no longer enough to simply manage company metrics, you must do so efficiently and accurately for the company to remain agile and competitive. Tools and automation can help elevate your contributions and overall satisfaction as a high-performer in your finance function. 

If you are an accounting or finance professional seeking a solution to maximize the potential of Excel, consider FutureView Foundation. Foundation’s direct ERP integration and Excel Add-in allows you to consume automatically structured and reconciled financial data, with an ongoing synchronization to your ERP, so you can refresh and work with the latest dynamic data on demand. Create a report once, and instantly refresh the latest data with a couple of clicks.