Cleaning Up QuickBooks Lists Using Excel

Lists are one of the most important building blocks of QuickBooks. Lists store information which 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. This 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 then import the list back into QuickBooks.

List Clean Up Step-by-Step

1. Back up 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. Our 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, and then select Export, and then Lists to IIF Files.

Figure 1 Export to IIF menu

2. Click the boxes next to Customer List and Vendor List and then click OK (see Figure 2).

Figure 2 Click to select which lists to export.

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).

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).

Figure 3 Name the export file.

5. Click Save (see Figure 3).

6. Click OK when the QuickBooks Information dialog box displays (see Figure 4).

Figure 4 Confirmation that the file has been exported

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.

Figure 5 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.”

Figure 6 Teaching Windows which program can open IIF files.

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, select “Select the program from a list” (Figure 7) and click OK.

Figure 7 Windows cannot recognize the IIF file by default. You need to “teach it”.

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.

Figure 8 Selecting Microsoft Excel as the program to open IIF files.

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.”

Figure 9 The IIF file opened in Excel

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.

5. Select row 21 in the IIF table above and then select Filter from the Sort and Filter icon in the ribbon.

Figure 10 After selecting row 21 in the IIF file, select Filter from the Sort & Filter icon in the Ribbon bar

The Filter command makes each of the columns in the “Header Row” into special

Figure 11 Row 21 now has been set as the filter header row

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.

6. 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.

Figure 12 Setting a custom filter on the rows in the table

Figure 13 The custom filter definition

7. 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.

Figure 14 The filtered list

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.

8. In Excel select Replace from the Find & Select icon.

Figure 15 Select Replace from the Find & Select icon on the Home ribbon.

9. For example, if the area code changes from 408 to 346, enter these numbers into the replace command and click Replace All.

Figure 16 Replace command to change 408- to 346-

Figure 17 Confirmation of the replacements.

Now all of the phone numbers for the filtered list have been changed.

Figure 18 The filtered list with all of the phone numbers 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).

10. 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.

Figure 19 Tab delimited file warning. Click Yes.

11. 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.

Figure 20 File close warning. Click No on this screen.

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.

Figure 21 Select Import from IIF files in QuickBooks.

2. In the Import window, browse to locate the desired folder and select the Customers.IIF file. Click Open (see Figure 22).

Figure 22 Select the file to import

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:

1. 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.
2. 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.
3. 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 system clean. 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.

2 Comments

• woody a says:
  September 09, 2009 13:36

  Good info Doug. Wait till you see 2010 though.

• Fran R says:
  September 09, 2009 14:13

  Doug, Great job and info... Excel 2003??not 2007?