How to Add/Edit Multiple List Entries in QuickBooks
The Add/Edit Multiple List Entries feature is a utility you can use to add entries to lists in to your QuickBooks company file. You can also use this utility to add data to existing entries, and to change existing data. This article, an edited excerpt from our Running QuickBooks 2012 Premier Editions book, presents a summary of some of the useful tools available in this utility. Other tools and processes are available.
The following QuickBooks lists are supported in the Add/Edit Multiple List Entries utility (we hope this utility will be expanded to include more lists and items in future editions of QuickBooks).
- Service Items (not a QuickBooks list—part of the Items list)
- Inventory Items (not a QuickBooks list—part of the Items list)
- Non-Inventory Parts (not a QuickBooks list—part of the Items list)
The power of this utility is that you can add or change multiple list entries quickly, instead of making changes one record at a time. For example, if you need to change the Terms for a group of QuickBooks customers or vendors, this tool makes it easy.
To open the Add/Edit Multiple List Entries window, choose Lists | Add/Edit Multiple List Entries. By default, the utility's window opens with the QuickBooks Customers list displayed. The spreadsheet-like window makes it easy to move among columns and rows.
NOTE: The Add/Edit Multiple List Entries command is also available on the menu you see when you select New Customer (in the Customer Center), New Vendor (in the Vendor Center), or click the Item button at the bottom of the Item List window. It's worded a little different on each menu, but it takes you to the Add/Edit Multiple List Entries window.
You can add, remove, and change the order of columns in the window. If you add and remove columns to match the type of QuickBooks data you're entering, the window is easier to work with. For example, if you're entering customer data and you want to create customer types, add the Types column to the window. If you don't send faxes, remove the Fax Number column. If your customer/vendor addresses don't exceed 4 lines, remove the column for the 5th line of the address block.
Click a column heading to sort the list by that column's data. The data is sorted in ascending order; reverse the order by clicking the column heading again. This is handy for ascertaining which list entries are missing data. For example, if you click the Terms column, any QuickBooks customers or vendors who haven't had their terms configured (the cell is blank) appear at the top of the list.
You can use the Find field on the Add/Edit Multiple List Entries window to search for data within the entries of the list currently displayed. This feature works exactly like the Find feature in the QuickBooks Customer/Vendor Centers and in List windows.
You can filter the display in the Add/Edit Multiple List Entries window to make it easier to get to certain records (which is very handy if you're editing entries). You can also design a custom filter to narrow the search to specific fields, instead of searching all fields.
How to Add Entries to Existing QuickBooks Lists
When you need to add new entries to existing lists, the Add/Edit Multiple List Entries window is fast, efficient, and accurate.
As you're entering data for a new listing, when you get to a column that's linked to another list (for instance, Terms or Type), the Add/Edit Multiple List Entries window provides a drop- down list of the existing entries. You can select the appropriate data or click <Add New> to enter the data you need and automatically add it to the list.
If you don't use an entry in the drop-down list (or add a new entry), when you press the Tab key to move on, QuickBooks displays an error message telling you that the data you entered is not in the list. You can click Set Up in the error message dialog to add a new entry to match your data. If you click Cancel and keep going, the cell in which you entered the wrong data displays your text in red, indicating an error. Errors are not saved when you save your changes.
How to Edit List Entries
The Add/Edit Multiple List Entries utility makes it easier to edit data in multiple existing entries. You don't have to open each listing, move to the correct tab or field, make your changes, save them, and then open the next listing to repeat that exercise.
Four tools exist to make your editing faster and more accurate:
- You can copy and paste among cells.
- You can copy down to fill all the rows below the cell you're in with the data in that cell.
- You always know which entry you're working on because the window displays the name in the field labeled Currently Editing. (In Excel, if the column you're using has forced you to scroll to the right, you have to lock the spreadsheet display at the appropriate column to see the Name column.)
- You can select legitimate entries for data that's from another list (e.g. Terms or Sales Tax Item) to avoid errors.
As an example of the power of Copy Down, suppose your list has twenty QuickBooks customers that lack Terms data and you want to add terms of Net30 to their records.
- Customize the columns to include the Terms field.
- Click the column heading for Terms to sort the list by the data in the Terms column. All the QuickBooks customers lacking Terms are at the top of the list.
- Click the column heading for Terms again to reverse the sort order. All the customers lacking Terms are at the bottom of the list.
- In the first row that is blank in the Terms column select the terms you want to assign to these customers (in this case Net30).
Right-click in that cell and choose Copy Down to add these terms to every QuickBooks customer.
You can use the Add/Edit Multiple List Entries utility to add or change data in QuickBooks custom fields, which is a real advantage. The names of your custom fields appear in the Available Columns pane of the Customize Columns dialog, so you can add them to the window.
Unlike Terms, Tax Items, and other fields that are parts of other lists, you can't control the way data is entered when you're adding or editing data for custom fields. You have to have a "company rule" established with your users for the way data is entered into these fields. When you create customized reports that are based on data from QuickBooks custom fields, you have to be very specific in your text to be sure you see all the list entries.
For example, if you have a custom field named "ContractRenewalMonth" and you want to create a report for all QuickBooks customers who need to renew their contract in November, you enter November in the Filter tab of the Customize Report window. However, if some of the customers have Nov instead of November entered in the custom field, they won't show up in the report.
The Add/Edit Multiple List Entries utility won't issue an error message that says "Hey, sometimes you have Nov and sometimes you have November, please pick one ". Enforcing these important data entry rules is your responsibility.
In addition to the powerful tools described here, you'll find many additional tools and processes available in the QuickBooks Add/Edit Multiple List Entries utility.