Using Excel to Clean Up Your QuickBooks Customer Lists

From the Oct. 2009 Issue

[Editor's note: A printable PDF version of this column with larger images is available here. Users can also click on the individual images below to bring up a larger view in a new window.]

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.


EXPORTING LISTS IN IIF FORMAT

To export your lists to an IIF file, follow these steps:

    1. Select Utilities from the File menu, then select Export, and then Lists to IIF Files.
    2. Click the boxes next to Customer List and Vendor List and then click OK (see Figure 2).
    3. In the Export window, browse to locate the desired folder on your computer.
    4. Enter Customers.IIF in the File name field (you may need to replace the contents in the field).
    5. Click Save (see Figure 3).
    6. Click OK when the QuickBooks Information dialog box displays (see Figure 4).


CLEANING THE LIST IN EXCEL

After you’ve exported the IIF file, open it in Microsoft Excel:

    1. Launch the Windows Explorer and browse to the My Documents folder.
    2. Right-click on Customers.IIF file and select Open With and then select “Microsoft Office Excel,” or if it’s not one of the choices, then select “Choose Program.”
    3. The next screen lets you have Windows use a “web service” to find the appropriate program to open your file, or to have you manually select the program yourself. Since the web service won’t work for IIF files, choose “Select the program from a list” (Figure 7) and click OK.
    4. Scroll through the list of your programs and select Microsoft Office Excel, and click “Always use the selected program to open this kind of file” (Figure 8), and then click OK. As you can see from the Excel screen below, the IIF file has several rows and columns that contain the various fields of the IIF file. Note that row 21 in this table is a “header” row that shows you the name of each of the columns.


From here on, the process of cleaning the list will be greatly aided by your Excel skills. A very useful feature of Excel for this type of thing is the “Filters.” Since we’re going to work on all the data in the rows below row 21, it really makes the task easier if we create a “filter” on row 21.

    1. Select row 21 in the IIF table above and then select Filter from the Sort and Filter icon in the ribbon. The Filter command makes each of the columns in the “Header Row” into special filter fields. Filtered data displays only the rows that meet criteria that you specify and hides rows that you do not want displayed. After you filter data, you can copy, find, edit, format, chart and print the subset of filtered data without rearranging or moving it. You can also filter by more than one column. Filters are additive, which means that each additional filter is based on the current filter and further reduces the subset of data.
    2. For our example, we’ll set a filter on the “Phone” column to select only those customers who have a phone that begins with Area Code 408, and contains the prefix 555. Notice that by clicking on the down arrow in each column, you see a menu with all the options for setting filters on that column. You could click in the boxes to the left of each of the data elements shown, or you can create a “custom filter” to have Excel calculate which records to show.
    3. After setting the filter on the Phone column to the criteria shown in Figure 13, the list now will only display a subset of the records. With this filtered list, it’s easy to do mass updating. For example, if all of those phone numbers have a new area code, you could use the Find/Replace command in Excel to change all of them with one command.
    4. In Excel, select Replace from the Find & Select icon.
    5. For example, if the area code changes from 408 to 346, enter these numbers into the replace command and click Replace All. Now all of the phone numbers for the filtered list have been changed. At this point, you can continue filtering the list in different ways, making similar modifications until you get all the changes made. When you’re finished, save the file in the same format as it was (i.e., do not save it as an Excel file).
    6. Click the save icon, or press Ctrl+S. On the warning screen below, click Yes to continue saving the IIF file in the text (Tab delimited) format.
    7. After the file is saved, close it from Excel. Excel will ask you if you want to save it again, but this time just click No. You already saved it in the format you want in the previous step.


      Now you’re ready to import the modified IIF back into QuickBooks.


IMPORTING IIF FILES

To import an IIF file, follow these steps:

    1. From the File menu, select Utilities, then Import, and then IIF Files.
    2. In the Import window, browse to locate the desired folder and select the Customers.IIF file. Click Open (see Figure 22).
    3. QuickBooks displays a dialog box that says, “Your data has been imported.” Click OK.

Now you’ve completed the process of exporting the list, making all the needed changes in Excel, and then importing the list back into QuickBooks. All your changes can be viewed in the QuickBooks list.


WARNINGS

  • Note that, in general, the IIF file format is not recommended. However, for this specific example of doing list exports, cleanup and imports, the IIF format works great.
  • Also, you cannot use this method to modify the “Name” field on any of the lists. The name field is the “primary key” for each of the lists, and it uses that field to match up the records you import and then update the fields with the new data in the IIF file. So if you modify the name field in the list, there will be new records added to the list when you import the IIF file.
  • You cannot use this method to delete names from any list. You can add new ones, and modify existing ones only.

I trust this gives you some ideas for new engagements to help your clients keep their systems clean and efficient. The slow season is a great time to approach your clients and propose a “clean up” engagement. It’s a great billable service to help your consulting business, and the clients will benefit from more efficient data entry (e.g., all the invoices will populate with complete, accurate information), plus you’ll be able to reduce the likelihood of big problems at tax time.

Note: QuickBooks saves exported lists with an .IIF extension. The folder in which you save the .IIF file is not important, though it is best to choose a folder that you can find easily (e.g., My Documents or the Windows Desktop).

 

Loading