Customer Data Entry Shortcuts

 

If you frequently add or edit more than one customer a time, filling in the New Customer dialog box isn’t only mind-numbingly tedious, it also wastes time you should spend on more important tasks like selling, managing cash flow, or finding out who has the incriminating pictures from the Christmas party.

Chances are you store customer info in programs other than QuickBooks, such as a customer relationship management (CRM) program that tracks customer interactions or a word-processing program where you create mailing labels.

If your other programs can create Excel-compatible files or delimited text files, you can avoid data entry grunt work by transferring data to or from QuickBooks. (Delimited text files are nothing more than files that separate each piece of data with a comma, space, tab, or other character.) In both types of files, the same kind of info appears in the same position in each line or row, so QuickBooks (as well as other programs) can pull the information into the right places.   

In many cases, you’ll want to add (or edit) more than one record, but less than a gazillion. QuickBooks 2010 introduced the perfect tool for that: the Add/Edit Multiple List Entries command. When you’re creating customers, vendors, or items, you can use the Add/Edit Multiple List Entries dialog box to paste data from Excel into QuickBooks. Or, to edit existing records, you can filter or search the list to show just the customers (or vendors or items) you want to update and then paste Excel data, type in values, or use shortcut commands to copy values between records.

When you want to transfer a ton of customer information between QuickBooks and other programs, importing and exporting is the way to go. By mapping QuickBooks fields to the fields in the other program, you can quickly transfer hundreds, even thousands, of records in no time. This section covers it all: working with multiple entries, importing, and exporting.

Adding and Editing Multiple Customer Records

The Add/Edit Multiple List Entries command lets you add or update values in the customer, vendor, and item lists by pasting information from an Excel spreadsheet directly into a table in the Add/Edit Multiple List Entries dialog box.

Other commands, for copying or duplicating values between records, make short work of changes like updating the new billing address for a customer who sends you job after job. Typing values into cells works, too, if you notice a typo in one of the records in the list. And you can customize the table in the dialog box to show only the customers you want to edit and the fields you want to modify.

This command goes by different names depending on where you find it in Quick- Books. Choose it in any of these locations to open the Add/Edit Multiple List Entries dialog box to the customer list:

• On the Lists menu, choose Add/Edit Multiple List Entries.

• In the Customer Center toolbar, click New Customer & Job, and then choose Add Multiple Customer:Jobs.

• In the Customer Center toolbar, click Excel, and then choose “Paste from Excel”.

Selecting a list to work with

QuickBooks automatically selects Customers in the Add/Edit Multiple List Entries dialog box’s List drop-down menu (unless you open the dialog box from the Vendor Center or the Item List window). You can switch lists by choosing Customers, Vendors, or Items from this menu.

To make it easier to edit existing list entries, you can display only the entries you want to edit. Here are some ways to control which customers appear in the table:

• Filter the entries. The View drop-down list includes several choices for filtering the customer list. Choose Active Customers if you want to make changes to only active customers in your company file. Inactive Customers displays customers that you’ve changed to inactive status.

To filter the customer list to your exact specifications, choose Custom Filter and fill in the dialog box shown in Figure 4-5. For example, if you want to divide your government customers into local, state, and federal customers, you can filter the list to show only records with Government in their customer fields. Note that QuickBooks only displays list entries that exactly match what you filter for. For example, if you type (555) to look for the 555 area code, records that don’t have parentheses around the area code won’t show up.

 Because QuickBooks doesn’t save the changes you make in the Add/Edit Multiple List Entries dialog box until you click Save Changes, you can select Unsaved Customers to see all the entries you’ve edited but not yet saved. Choosing “Customers with errors” displays entries that contain invalid values, like a customer type or tax code that doesn’t exist in your company file. In fact, if you click Save Changes when there are records with errors, the dialog box automatically filters the list to the “Customer with errors” view, so you can see what you need to correct before you can save your changes.

 

In the Custom Filter dialog box, you can type a word, value, or phrase to look for, and specify the fields you want QuickBooks to search. For example, type Government in the For field, and search common fields (all the fields listed in the “in” drop-down list) to find customers with the Government customer type.

• Find entries. Typing a word, value, or phrase in the Find box is similar to applying a custom filter to the list, except that QuickBooks searches all fields. For example, if you type 555 in the Find box and then click the Search button (which looks like a magnifying glass), QuickBooks will display records that contain 555 anywhere in the record, whether it’s in the company name, telephone number, address, or account number field.  

• Customize the columns that appear in the table. To paste data from Excel in a jiffy, you can customize the table’s columns to match your Excel spreadsheet. (If you’re an Excel whiz, you may prefer to rearrange the columns in your spreadsheet before pasting data.) Click Customize Columns to open the Customize Columns dialog box. The tools for customizing columns are straightforward. To add a column, select the field you want in the Available Columns list and then click Add. To remove a column, select it in the Chosen Columns list and then click Remove.

• Sort the list entries. To sort the entries in the table, click the column heading for the field you want to sort by, and QuickBooks sorts the records in ascending order (from A to Z or from low to high numbers). Click again to sort in descending order.

In addition to adding and removing columns, you can change the position of a column by selecting it in the Chosen Columns list and then clicking Move Up or Move Down. If you completely mangle the columns, click Default to restore the preset columns.

Adding or editing list entries

Whether you want to add new entries or edit existing ones, you can paste data from Excel, type in values, or use commands like Copy Down to copy values between records. (When you want to add a new record, you have to click the first empty row at the bottom of the list before you can enter any data.) Here are the various ways to enter values in records:

• Type values in cells. This method is straightforward: Click a cell and make your changes. Click within text to select the text up to where you clicked. Click a second time to position the cursor at that location. Click the right end of a cell to select all the text in it. Drag over text to select part of an entry.

  

• Copy and paste values.

 If you’re a fan of copying and pasting (and who isn’t?), you can copy data from an Excel spreadsheet, (a single cell, a range of cells, one or more rows, or one or more columns) and paste it into the table. The only requirement is that the rows and columns in the table and in the spreadsheet have to contain the same information in the same order.

You can rearrange the rows and columns either in the dialog box or in the spreadsheet, whichever you prefer. When you paste Excel data into existing records in the Add/Edit Multiple List Entries dialog box, QuickBooks overwrites the existing values in the cells. To paste Excel data into new records, be sure to select the first empty row in the Add/Edit Multiple List Entries dialog box before pasting the data.

• Duplicate a row. To create a new record that has many of the same values as an existing record, right-click the row you want to duplicate and then choose Duplicate Row from the shortcut menu. The new record appears in the row below the original and contains all the same values as the original record, except that the value in the first field begins with “DUP” to differentiate it from the original. Edit the cells in the row that have different values. Then, edit the Name cell to reflect the new customer or job’s name.

• Copy values down a column. You can quickly fill in several cells in a column using the Copy Down command. Because this command copies data into all cells below the cell you select, it’s important to filter the list to show only the records you want to change. Then, right-click the cell you want to copy down the column and choose Copy Down from the shortcut menu. Quick- Books copies the value in the selected cell to all the cells below it in the column, overwriting any existing data.

 For example, if you want to change the contact name for all the jobs for a particular customer, filter the list to show all the records for that customer (in the Find box, type the customer’s name, and then click the magnifying glass icon). Next, type the new contact into the first Contact cell. Then, right-click the cell and choose Copy Down.

 • Insert line. If you want to insert a blank line in the table (to create a new job for a customer, for example), right-click the record where you want the blank line, and then choose Insert Line from the shortcut menu (or press Ctrl+Ins).

• Delete line. If you created a record by mistake, you can get rid of it by right clicking anywhere in its row and then choosing Delete Line.

• Clear column. If you want to clear all the values in a column, right-click in the column and then choose Clear Column from the shortcut menu.     â•‰â•‰Customer Data Entry Shortcuts

Saving changes

After you’ve completed the additions and modifications you want, click Save Changes to save those list entries. QuickBooks saves all the entries that have no errors and tells you how many records it saved. If it finds any errors, like a value that doesn’t exist in the Terms list, it displays those entries in the table in the Add/ Edit Multiple List Entries dialog box and changes the incorrect values to red text. Click a cell to see a hint about the error. If the problem is a list entry that doesn’t exist, the “<list name> Not Found” dialog box opens, where “<list name>” is a list like Terms. Click Set Up to add the entry to the list. Fix the errors and then click Save Changes again.

Importing Customer Information

If you have hundreds of customer records to stuff into QuickBooks, even copying and pasting can be tedious. If you can produce a delimited text file or a spreadsheet of customer info in the other program, then you can match up your source data with QuickBooks fields and import all your customer records in one fell swoop. Delimited files and spreadsheets compartmentalize data by separating each piece of info with a comma or tab, or by cubby holing them into columns and rows in a spreadsheet file. An exported delimited file isn’t necessarily ready to import into QuickBooks, though. Headings in the delimited file or spreadsheet might identify the field names in the program that originally held the information, but QuickBooks has no way of knowing the correlation between those fields and its own. But don’t worry: You can help QuickBooks understand the data you’re importing. QuickBooks looks for keywords in the file you’re importing to figure out what to do, as shown in Figure 4-7. So, you’ll need to rename some headings to transform the file produced by the other program into an import file that QuickBooks can read. QuickBooks’ customer keywords and the fields they represent are listed in Table 4-1. Fortunately, Excel and other spreadsheet programs make it easy to edit headings. When your exported file looks something like the file in Figure 4-7, save it in Excel 2010 by clicking that program’s File tab and then choosing Save (if you’re using Excel 2007, click the Office button and then choose Save). ╉╉Customer Data Entry Shortcuts

 A file you import has to use field names that match QuickBooks’. For example, replace a Last_Name heading with LASTNAME, which is the keyword for the last name field in QuickBooks. The first column has to include the keywords QuickBooks uses to identify customer records. And the first cell in the first row of a customer import file has to contain the text “!CUST”.

 Exporting Customer Information

QuickBooks lets you do lots of cool things with customer info, but say you have a mail merge already set up in FileMaker Pro, or you want to transfer all of your customer records to Access to track product support. You have to export your customer data out of QuickBooks into a file that the other program can read and import.   

• Export your customer information directly to Excel if you’re not sure what info you need and you’d rather delete and rearrange columns in a spreadsheet program. QuickBooks exports every customer field. Then, you can edit the spreadsheet all you want and transfer the data to yet another program when you’re done. (The one downside to this approach is that the spreadsheet includes blank columns between each data-filled column.)

• Create a report when you want control over exactly which fields QuickBooks exports. By creating a customized version of the Customer Contact List report, for example, you can export the same set of records repeatedly, creating delimited files, spreadsheets, and so on. (Chapter 21 covers QuickBooks’ reports in detail.)

• Export a text file of your customer data if you need a delimited text file to load into another program. The delimited file lists each customer in its own row with each field separated by tabs. The following sections explain all your options.  

╉╉Customer Data Entry Shortcuts

Exporting to Excel

In QuickBooks, exporting the Customer List to Excel is a snap. To export all the customer data stored in QuickBooks to an Excel file, in the Customer Center toolbar, click Excel, and then choose Export Customer List to open the Export dialog box (Figure 4-8). The Excel menu also contains commands for exporting transactions and for importing and pasting spreadsheet data into QuickBooks.

The Export dialog box that appears is already set up to create a new spreadsheet. Click the Export button and you’ll be looking at the Customer List in Excel in mere seconds. If you’d rather give QuickBooks more guidance on creating the spreadsheet, click the Advanced tab and then adjust options like Autofit (which sets the column width so you can see all your data) before clicking Export.

Customized exports using the Contact List report

By modifying the Contact List report’s settings, you can export exactly the fields you want for specific customers. For example, storing email addresses in QuickBooks is perfect when you email invoices to customers, but you probably also want them in your email program so you can communicate with customers about the work you’re doing for them. Exporting the entire Customer List is overkill when all you want is the contact name and email address; that’s where exporting a report shines.

Out of the box, QuickBooks’ Customer Contact List report includes the Customer Name, Bill To address, Contact, Phone, FAX, and Balance fields. Here’s how you transform this report into an export tool:

1. Choose Reports➝Customers & Receivables➝Customer Contact List. The Customer Contact List report window opens.

2. In the report window’s toolbar, click Modify Report. The “Modify Report: Customer Contact List” dialog box that appears lets you customize the report to filter the data that you export.

3. Click the Display tab (if you’re not already on it) and, in the Columns section, choose the fields you want to export. The Customer, Contact, Phone, and Fax fields might be good ones to export. Then again, they might not. You can add or remove whichever fields you want by clicking a field’s in the Columns list to toggle that field on or off. If there’s a checkmark in front of the field’s name, the report will include a column for that field; if there’s no checkmark, it won’t.

4. To produce a report for only the customers you want, click the Filters tab. In the Filter list, choose Customer. In the Customer drop-down list that appears, choose “Multiple customers/jobs” to select the customers you want to export. QuickBooks displays the Select Customer:Job dialog box, with the Manual option selected; that’s what you want. In the list of customer names on the right side of the Select Customer:Job dialog box, click each customer you want to export, and then click OK. Then, in the Modify Report dialog box, click OK. You see the report with the modifications you’ve made.

5. In the Customer Contact List window’s toolbar, click Export. The Export Report dialog box opens. To create a new Excel workbook, simply click Export. Your computer launches Excel and displays the report in a workbook.

Exporting a text file

To create a delimited text file of the entire Customers & Jobs List (or any other QuickBooks list), choose File➝Utilities➝Export➝“Lists to IIF Files”. The first Export dialog box that appears includes checkboxes for each QuickBooks list. Turn on the checkboxes for the lists you want to export, and then click OK.