QuickBooks Lists in Desktop: Your Multiple List Editing Cheat Sheet

There may be times when using QuickBooks that you find you’ll need to update some piece of data across many different items, customers, or vendors. Or maybe you’re doing some “spring cleaning” and want to inactivate some list elements you no longer use. In this post, we’ll go over two methods that can help you update large swaths of list information without updating each element by hand, saving you time and effort.

But first: Be careful! Any imports or sweeping list changes can, if done improperly, cause huge problems for your company file. It’s even more important to be careful with lists because fixing issues isn’t always as easy as just deleting a bad transaction.

Before attempting any of the steps discussed below, make a fresh backup of your company file to come back to if things go wrong. Remember, with great power there must also come great responsibility.

What Can I Do to Bulk Edit Multiple List Entries in QuickBooks Desktop?

1. Use QuickBook “Add/Edit Multiple List Entries” to Provide a Mass Update.

The first method for mass updating lists is to use the “Add/Edit Multiple” window. This will display your lists in an editable grid format and will allow some basic filtering.

You can edit customers, vendors, and the more common item types with this tool. Items are further broken down into separate lists of service, inventory, non-inventory, and assemblies.

Sales tax items and groups, Other Charges, Subtotals, Discounts, Groups, Payments, and Payroll items cannot be used with this tool.

Access this by going to the “Lists” menu in the top bar and choosing “Add/Edit Multiple List Entries” at the bottom.

How to Add/Edit Multiple List Entries – Step by Step:

After yore taken to the next window, yoll take these steps to edit your entries:

1. Selecting the list you’d like to work with, and any filter you’d like to apply to it. Default filters include active, inactive, list elements with errors (those with edits on this screen which would prevent them from saving), and unsaved list elements (those you have added or edited in this screen, but not yet saved). You may also filter by searching a variety of data fields within any or all of the above.
2. Before you begin your mass edit, customize the columns shown on this screen  to ensure the data field you wish to edit is available to this tool; and it is one of the currently displayed columns.
3. Use the button ‘Customize Column in the upper right, to get started.

 Your Column Options

Here, you’ll see available choices:

• A list of ‘Available Column on the left.
• A list of currently ‘Chosen Columns,’ on the right.
• The “Add” button moving a field selected in the Available Column on the left, to the ‘Chosen Column on the right. It should end up in the main window.
• The “Remove” button, similarly,  will put it with a field selected in the ‘Chosen Columns,’ back in the ‘Available Column and no longer display it.
• The “Move Up” and “Move Down” buttons apply only to the chosen columns on the right.
• The “Available Columns” on the left are always in a default order.

The order from top to bottom that the chosen columns are listed in this window is the order that the columns will appear from left to right on the main “Add/Edit Multiple” screen. This can be used to reorganize columns for easier viewing of key fields, or to match the order of columns in an Excel sheet that you wish to copy from and paste into this tool.

The “Default” button will reset the list of “Chosen Columns” to a default set, which you can then add or remove from as you like.

 Click the “OK” button at the bottom to apply your chosen columns.

 If you’d like to look for something specific in these results, the “Find” box in the upper right will search all available fields, and can be filtered back off with the red X.

There are a few ways to make quick use of this:

1. If you need to change the data to something different for each list element you’re editing, you may be best off entering a filter to narrow down the list and then change each item. This still saves time over manually editing each item, as you can quickly change the info, move on to the next element, and save the whole list at the end.
2. If you have the data in Excel format in the exact same order as it’s shown on this screen, you can also copy a cell range from Excel and paste it in here. You can change the sort order by clicking the “column header” you’d like to sort by. Click the same header again to reverse the sort order. We’ll go into more detail on what else you can do with Excel further down.
3. One place “Add/Edit Multiple” really shines is if you need to change, add, or remove the same data for every or nearly every element in a list. In this case you can quickly copy one value down the list for all elements. You may see a popup about this the first time you use the tool.

To do this (after optionally sorting or filtering the list to reduce the manual cleanup at the end):

1. Enter the value to place in that field for all elements at the top of the list.
2. Right-click and select “Copy Down,” and QuickBooks will fill this down the list for all elements below that one.
3. If a few of them should be different, you can then hunt those down; and change them before saving. Even if you must do that for a quarter of the list, this method has still saved you a lot of time on other the remaining three-fourths.

If you, instead, wanted to clear the data out of a field, “Copy Down” can help as well. It works just as we went over above, only you’re using it with a blank cell at the top. The blank copies down just like an actual value and allows you to clear that data out for all the selected elements.

Not all fields will be available to update with “Add/Edit Multiple”, but when they are it can be fantastically efficient.

2. Advanced Import via Excel

The other simple, in-product method of updating large quantities of list data is via the “Advanced Import” function. This shares many of the benefits of the “Add/Edit Multiple” window, and while it can be a bit more complicated, the trade-off is that it has a few extra tricks. Not only is this more useful for updating the same field with different information across list elements, but it will allow you to update a few fields that the “Add/Edit Multiple” can’t, such as a customer shipping address.

Also, unlike “Add/Edit Multiple,” accounts can be updated using the “Advanced Import.” This can be great if, for example, you’d like to start using account numbers when you haven’t before. Rather than editing the accounts one by one, you can export your chart of accounts to Excel, input the account numbers there, and import them back in to fill that field.

How To Use Advanced Import

To start using “Advanced Import,” you’ll need to make sure you have permissions.

The QuickBooks Admin can always import, but other users may require additional permissions depending on their role.

Access the tool by going to the File menu, choosing Utilities, then Import, finish with Excel Files.

The Advanced Import window is split into two tabs, “Set up Import” and “Preferences. Set up Import is where you’ll begin working, but I’d like to start by going over Preferences first, as there are some very important options to go over before attempting an Advanced Import.

Setting Advance Import Preferences

1. You can have QuickBooks ask you what to do about the duplicate, choosing one of the following options for each duplicate individually.
2. The next option will ignore any duplicate on the import sheet, in favor of keeping existing data intact. This is good for purely updating lists.
3. The third option is to replace any fields in QuickBooks with data from the import sheet, where present. This is generally the option you will want for list updates.
4. The final option will also replace existing data with data from the import sheet. However, if the import sheet has no data for a given field, that field will be blank after the import. This may be a good choice, if you need to blank out a given field.

The first two options are not very useful for updating data, as one will prompt you for every existing element (which they all will be), and the other won’t use your updated data. Generally, you’ll want the third option, though the fourth has its uses. Be careful and check this before any import.

“Error Handling” has only two options:

1. If a row in the import sheet would result in an error, it will be imported but any fields that contain errors would be left blank.
2. If a row contains an error, that row is not imported at all.

Deciding which of these options works better for you may depend on the size and complexity of your import data. You’ll have to choose whether you would rather fix any potential errors in QuickBooks post-import, or if you’d prefer to fix them up in Excel and try importing those rows again.

Setting Up Advance Import – Picking Your File, and Mappings

With those important warnings out of the way, let’s look at the Set up Import tab.  At the top, you’ll select the Excel source file you’re using for import.

• This needs to be a .XLS, .XLSX, or .CSV file.
• If it’s in another format, you’ll need to re-save it as one of those three.

1. Click “Browse.”
2. Locate the Excel Source File
3. Click on it, and the button “Open.”
   1. Or, double-click on the file.
4. After selecting your workbook, choose which sheet your import fields will draw from. You must select a sheet even if there is only one sheet in the workbook. The checkbox below that allows you to tell Advanced Import whether the Excel file has header rows, meaning titles at the top of the columns.

The lower half of this screen is where you’ll select or create mappings, where you tell QuickBooks which columns in your import data will flow into which data fields for the chosen list elements.From the drop-down, you can choose to use any mapping you have saved previously.

Creating the Mapping For Advance Import

If you haven’t saved any, or need to map things differently, choose “Add New.” If you need to make a change to an existing mapping, you can choose “Edit.”

In the Mappings window, give this mapping a name to identify it, then select the type of list you’ll be importing.

Mapping Your Import Columns

Once you have chosen the mapping type, the lower part of this screen will populate with two columns. On the left are all the available fields to import to for this list. On the right are drop-down boxes allowing you to select columns from your import data.

1. Choose the column from your import sheet that should fill each matching field in QuickBooks.

• You do not have to map all fields, only those you wish to add or update data for, or those that QuickBooks requires to identify the list element.
• These would include the name, and in the case of items or accounts, the type.
• If a field that is required has not been mapped, QuickBooks will warn you when you try to save the mapping, and you will have to map the required field to a source column to proceed.
• Remember that with the last “Duplicate Handling” option in Preferences, columns mapped but containing no data for a given row in the import sheet will be blank in QuickBooks after import.
• It might be safest to only map required columns and any columns you wish to clear if using that option.

2. After mapping the fields you’ll be importing, choose Save to save your mapping.

3. Back on the Import window, you can begin the import immediately by choosing Import, but a safer option even if you’ve made a backup is to choose Preview first.

Here you’ll see a sample of how QuickBooks expects each Excel row to translate to information imported into the list elements’ data fields. This doesn’t always catch every error prior to import, but if you do see errors listed here, you’ll probably want to fix those up before you try importing. Down below is another place where you can change the error handling option.

If everything looks good, go ahead and begin importing.

Note: In case you haven’t already, QuickBooks will prompt you to make a backup before the import begins. Please do this, I cannot stress this enough. As an import cannot be undone, this may be your only saving grace if the import goes unexpectedly.

After Importing your List Data

Once the import is complete, you’ll get a pop-up letting you know it’s done, and if any of the imported rows had errors or warnings, you’ll be notified and prompted to save the error log. The error log will list each row that had problems, with columns listing what the error was and whether the row was imported, partially imported, or not imported.

From here you can go into the records that were partially imported and correct any information you needed to add or update, and for rows that were not imported you can correct your data and try importing just those rows again.

If too many rows partially imported incorrectly, you may wish to restore the backup and try again after editing your data.

This pretty much covers the basics of updating large amounts of list data via these methods. Before we close, I do want to point out one important caveat to this. You may have noticed that when changing one of the accounts used on an item, QuickBooks will prompt whether you would like to make this change retroactive, or to have only new transactions reflect his change. Using either of these methods to update item accounts in mass will not give this prompt, and thus any account changes which otherwise offer the option will only take place going forward. If you’d like to retroactively update transactions with new item accounts, you’d need to update the items individually, selecting the option to update past transactions.

As we’ve tried to emphasize in this post, these tools are to be used very cautiously, and can have unintended side effects if not used precisely. You may even want to test or practice on isolated backup copies before trying it in the live file.

If you can get comfortable with their use, however, these tools can save a lot of time and effort in updating your lists. That gives you more time for other projects, and it’s just one more way that learning a few QuickBooks tricks can empower your business success.

If you do happen to run into quirks and issues with updating your lists, dot hesitate to Contact Us!

 

ANNOUNCEMENT:

Looking for more QuickBooks guidance? Wre hosting a Two-Day Seminar/Webinar on QuickBooks Desktop – on the 3rd and 4th of October 2019. It will take place at our Learning Center, and online! Space IS Limited; get your spot while spots remain.