Using Excel to Clean Up Your QuickBooks Customer Lists

From the Oct. 2009 Issue

Lists are one of the most important building blocks of QuickBooks, storing information that is used again and again to fill out forms. For example, when you set up a customer in the customer list, the customer’s name, address, tax code, tax item, price level, etc. is used to automatically fill out an invoice, sales receipt or customer letter. Similarly, when you set up an Item in the Item list, QuickBooks uses the Item’s description, price and associated account information to fill in details on the invoice and record the appropriate debits and credits in the general ledger. Properly maintained lists help speed up data entry and maintain accuracy and consistency in the data file.

The problem is, as time passes, lists tend to get “dirty” in one way or another. For example, the customer list tends to have incomplete or inconsistent data. Perhaps some email addresses are missing or customer types are not set correctly, or the sales tax codes or items are incorrect on several customer records. These types of problems seem somewhat innocuous on the surface, but as lists get dirty data entry tends to get inefficient and inaccurate, which leads to problems with reports, tax returns, and the overall quality of the QuickBooks data.

Consider the difficulties of filing a sales tax return from QuickBooks data. If you discover that the sales tax reports in QuickBooks are either wrong or do not give you enough data to properly fill out the sales tax return, you’ll have a big project in front of you. Most likely, you’ll have to modify every invoice and sales receipt for the whole quarter in order to make the reports give you the accurate and complete information for the tax return. The good news is that proper list maintenance can help you avoid this type of headache at tax time.

To avoid these problems, and provide a great service to your clients during the “slow” time of year, here is a simple, billable service you can provide for all of your clients. To clean a customer list (or any of the lists) in QuickBooks, use the following steps to export the list to an IIF file; then, manipulate the list in Excel and import the list back into QuickBooks.

LIST CLEAN UP STEP-BY-STEP

1. Backup your file first -- You should always backup a data file before working on the data. It’s a good “best practice” for all your consulting engagements because it allows you to completely undo all of the changes you plan to make. If for whatever reason you do need to restore the file to the point before you worked on it, you should use the “Restore” command in QuickBooks.
2. Export the customer list to an IIF file -- Follow the steps below to export your customer list (or any other list you need to clean).
3. Open IIF in Excel and use the flexibility of Excel filtering to select, sort and modify the list as needed. The example below will show how to change the area codes for a group of phone numbers.
4. Import the list back into QuickBooks. This “overwrites” the list in the QuickBooks file with any changes you’ve made to the list. Note that you cannot delete list entries using this method, only add or change existing list items.