Using Excel to Clean Up Your QuickBooks Customer Lists
Column: The QuickBooks Advisor
Oct. 01, 2009
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
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
- 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.
- Export the customer list to an IIF file — Follow the
steps below to export your customer list (or any other list you need to
- 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.
- 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:
- Select Utilities from the File menu, then select Export, and then Lists
to IIF Files.
- Click the boxes next to Customer List and Vendor List and then click
OK (see Figure 2).
- In the Export window, browse to locate the desired folder on your computer.
- Enter Customers.IIF in the File name field (you may need to replace the
contents in the field).
- Click Save (see Figure 3).
- Click OK when the QuickBooks Information dialog box displays (see Figure
CLEANING THE LIST IN EXCEL
After you’ve exported the IIF file, open it in Microsoft Excel:
- Launch the Windows Explorer and browse to the My Documents folder.
- 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.”
- 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.
- 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.
- 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.
- 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.
- 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.
- In Excel, select Replace from the Find & Select icon.
- 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).
- 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.
- 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:
- From the File menu, select Utilities, then Import, and then IIF Files.
- In the Import window, browse to locate the desired folder and select
the Customers.IIF file. Click Open (see Figure 22).
- 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.
- 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