Excel Accounting: Formulas, Tips and Advice Accountants Need to Know

As an accountant, you most likely learned your craft using spreadsheets, namely Microsoft’s Excel program. Maybe you have a love/hate relationship with the software. When it does what you need it to, you’re in love – but when it doesn’t, you’re screaming at the universe, “Why now?” because one incorrect or transposed entry can have damaging consequences. While it may be difficult to get your clients to upgrade to the cloud, especially if you have clients that are wary of changing their routines, it’s a good idea to show them the many benefits of cloud accounting.

Excel Accounting Most Common Mistakes (and How to Fix Them)

As with any manual accounting tasks, there are risks of errors when you use Excel. The automatic nature of cloud accounting negates many of these mistakes. Even if you happen to input an incorrect figure, you see the mistake immediately rather than two weeks down the road after it’s messed up several of your accounts. Helping your clients transition to the cloud saves you a lot of time spent cleaning up these potential mistakes.

Some of the most common mistakes are:

  • ##### – This error typically occurs if the cell with the referenced numbers is too narrow to display the entire figure. You can eliminate this by widening the column the number is located in.
  • #Name? – This error typically occurs when you’ve input a formula incorrectly, such as misspelling the COUNTIF formula. Correct this by examining your formulas to see which one may have been incorrectly configured or spelled.
  • #Value! – This error means you’ve entered a formula incorrectly or the cells your formula referenced are not configured properly. Because this is far-reaching, it can be difficult to spot exactly where your error is. Going over your functions, text, spacing, and other aspects of your inputs can help you deduce where you might have gone wrong.
  • #REF! – This error occurs when the formula you’ve inputted is incorrectly referencing a cell or a set of cells, such as a row of cells you’ve since deleted. One way you can correct this is by not using explicit cell references in your formulas.

Why You Still Need to Excel at Excel

While most accounting firms and their clients have transitioned to cloud accounting software by now, it’s sometimes hard to avoid falling back on Excel. After all, it’s been in use since 1987. Even though throughout the years there have been several competitors to surface, such as Google Sheets, Excel still holds the top spot for millions of businesses all over the globe – though that number has steadily been declining in recent years, thanks to the assistance of cloud accounting software.

It’s no surprise why Excel is such a popular product. When you were first learning how to be an accountant, you probably were taught how to use Excel as a means of maintaining entries. Because of your familiarity with the program, you most likely carried Excel over into your accounting practice. Since you’ve been using this software program for so long, you probably think of yourself as an expert–and you might be–but you can always improve. In fact, knowing just a few simple formulas can make your work a breeze.

Common Formulas in Excel

The most common formulas you’re most likely to use in Excel for accounting include:

  • SUM – A function that adds together cells you reference in the formula
  • COUNT – This function counts all cells in a given range that have a numerical value
  • AVERAGE – This function provides the average of all the numbers in the cells you reference in the formula

For intermediate users, the most common aspects of Excel that you should understand include:

  • Pivot tables
  • Record macros
  • IF scenarios

If you’re a seasoned pro in Excel, it’s likely you know how to create a Dashboard, how to use the Modelling feature, and how to write Macros.

Resources for Learning (or Brushing Up on) Excel

Regardless of where you’re at in your practice, it’s always a good idea to stay updated on tips and tricks to use in Excel, as well as new features. The Microsoft website is one of the best places to find information regarding how their office suite products work. You can also find useful information on the Robert Half site, which illustrates the best tips for Excel users for 2019.

How to Convince Your Clients They Don’t Need Excel

When you’re calling on prospective clients, or even when you’re attempting to upsell your current ones, it’s a good idea to persuade your clients that they don’t need Excel. You can convince them by showing them the benefits of using cloud accounting software. With accounting in the cloud, there’s no need to remember how to input a function, or where the function should be placed, because all the math happens in the background of your cloud application.

Your clients’ first rebuttal to this argument is likely to be the fact that Excel is nearly a no-cost application. While being almost free has its advantages, many more advantages are available to you and your clients when you’re able to integrate everyone into one streamlined program. Some of these benefits include multi-user access and collaboration, instant reporting, technical support, and the ability to backup sensitive data.

How to Transition From Excel to QuickBooks Accounting Software

A few necessary steps ensure a smooth transition from Excel to cloud accounting:

  1. Familiarize yourself with your new software
  2. Choose the start date for using the new software
  3. Prepare your clients’ data
  4. Enter the data into your new software

As with anything new in your practice, there’s a learning curve. Watch some videos of your new software in action so you know what to expect during the transition. If the software you want to use offers a free trial, take advantage of that time to learn how to use it. And there’s no rule that says you have to transition completely or even all at once. When you’re confident in your abilities with the new software, make the transition. If you can line up this transition with the date you’ve chosen to begin using your new software, even better.

The date for total migration should be chosen strategically. If you can transition in line with the start of your fiscal year, perfect. If that’s not possible, the best option is a date that doesn’t interrupt your calendar and requires the least amount of changes to your current processes.

The actual transition should be rather seamless if you are simply changing formats of your current software provider. For instance, if you currently use QuickBooks Desktop and you want to migrate to QuickBooks Online Accountant, your data is copied from one to the other with virtually no action on your part. If you’re transitioning from manual accounting or Excel to a new software, you must make sure that the way your data is configured meshes with the way your new software configures data, such as naming conventions or how the rows and columns are arranged. Finally, enter your data from Excel into the new software. If you’re switching to QuickBooks, you can have a seamless transition by easily importing into QuickBooks.

What if you have a lot of clients using Excel that all decide to transition to cloud accounting? How do you migrate their data into QuickBooks without compromising the integrity of the original data? You can learn how to export your data from Excel to QuickBooks by following the simple tutorial on the QuickBooks Intuit website, but the gist of it is as follows:

  1. Go to the File menu in QuickBooks
  2. Click Utilities
  3. Click Import
  4. Click Excel Files
  5. Click No if the Add or Edit Multiple Entries window opens
  6. Follow the QuickBooks setup wizard

There has never been a better time to migrate your important client data to QuickBooks. QuickBooks Online Accountant offers powerful tools for accounting professionals. Sign up for free.

Learn from your peers

Want to learn more about staying organized as an accountant. Register now for the Get Connected Roadshow by QuickBooks. In the virtual event, you’ll learn about QuickBooks Online Accountant tips and tricks from a QuickBooks Certified ProAdvisor.

Register now

Related Articles

Looking for something else?

Get QuickBooks

Smart features made for your business. We've got you covered.

Firm of the Future

Expert advice and resources for today’s accounting professionals.

QuickBooks Support

Get help with QuickBooks. Find articles, video tutorials, and more.