Cleaning Up Your QuickBooks Data

Part 3:  Cleaning Up Item and Service Lists           

If you’ve been using QuickBooks software for some time, you may have accumulated List Items that are no longer useful.  Tidying up your Item and Service Lists can be done a number of ways.  I will lead you through the simple methods and then touch on the more involved methods.

First, as with any list, if an item has not been used at all then it can be deleted. This is true of the following item types:  Service, Other Charge, Payment, Non-Inventory, Group, or Discount.  If an Inventory or Assembly item has not been used AND has zero quantity on hand, then it can be deleted as well.  To do this, in the Item and Service List, highlight the item you wish to delete and either right-click and choose “Delete Item” or pull down the drop-down “Edit” menu and choose “Deleted Item”.

As with other lists, if an item is no longer needed, but has been used in a transaction in the past, then it cannot be deleted and instead must be deactivated.  (QuickBooks will tell you this when you try to delete an item that has transaction history.)  To deactivate an item, in the Items & Services List, highlight the item you wish to deactivate and either right-click and choose “Make Item Inactive” or pull down the drop-down “Edit” menu and choose “Make Item Inactive”.  Inactive Items can be seen by choosing to “Show Inactive Items” on the pull-up “Item” menu in the lower left corner of the Items & Services List screen.

Merge Lists

Items and Services can be merged.  Again, as with other lists in QuickBooks, if you have duplicate items you can merge the two items together and the transaction history for both items will become one list under the remaining item.  Certain prerequisites apply:  the two items must both be the same type (Service, Inventory, Other Charge, etc.), neither must be a sub-item of another (release the check mark for “sub-item of” before merging) OR both must be sub-items of the same item.  Inventory Items can be merged, even with quantities on hand.

Remember that when you merge two items (A & B) in order to save B, A must have the same item number as B and A’s information (cost/accounts/sell price) will be lost.  B, as the receiving item, will dominate and retain its information.  Once they are merged, however, the item information (cost/accounts/sell price) can be updated as necessary.  To merge two items, highlight the item A and double-click to edit the item.  Change the Item Name/Number to match that of B exactly then Save & Close.  QuickBooks will ask if you want to merge the item and you can say “Yes”.

Plan and Organize Your List

A couple of things I like to do to keep my item list streamlined is to use sub-items, and to think through the coding of Item Name/Numbers.  Your goal should be to make looking up an item as easy as possible so as you set up (or clean up) your list, think about the way that you and your staff think about your list of items and services.  For instance, in a promotional products company, you may want to group your items by type such as T-Shirts, Pens, Mugs, Novelties, etc. so that when you are creating a Sales Order for a client, you can type “Pens” and be in the list of pen products.  I would set up a Non-Inventory Item for “Pens” that I would never use, and as I buy and sell pens for customers, I would add them as Non-Inventory Items that are sub-accounts of “Pens”.

Item and Service Lists often need some clean-up as far as the General Ledger Accounts to which they are assigned (Cost of Goods Sold, Income and Inventory).  An easy way to see if your items are assigned correctly to your Chart of Accounts, is to go to your Item and Service List and choose the “View” drop-down menu and choose “Customize Columns”.  A pop-up window will show up with a list of possible fields on the left and those that are currently displayed on the right.  Delete unwanted fields and make sure the following fields are chosen:  Name, Description, Type, Account, COGS Account, and Asset Account.  Click on “OK” once the list on the right side is correct.  Scroll through the list and find any items that are incorrectly assigned to a General Ledger Account.  You can double-click on an item, fix its assignation and then click “OK” to correct your list.  Note:  QuickBooks will ask if you want to reassign past transactions to this new account or if you just want to assign purchases/sales of this item to this new account going forward.  (Really, really helpful is you are cleaning up messy financial reports!)

Update Groups of Items at Once

These are the simple methods for cleaning up your Item and Service List.  If you want to work with your list and update whole groups of items at once (pricing, g/l account, etc.) then you can use “Add/Edit Multiple Items”.  In the Item and Service List, pull-down the “Edit” drop-down menu and click on “Add/Edit Multiple Items”.  This screen allows you to edit your items by type: Service, Non-Inventory, Inventory, and Assembly.  You can also narrow the list of item by using a Custom Filter by Item No. or Preferred Vendor (say if your vendor’s pricing list arrives for a mass update).

In this screen, once again you can customize the columns to show you only those columns that need to be updated.  You can also “Copy Down” any value that needs to be applied to all of the following items.  So, for instance, if you have decided that a group of items should be sub-items of “Pens”, you can choose “Pens” from your item list in the “Sub item of” column and then right-click on “Pens” and choose “Copy Down”.  “Pens” will now be the master item for all the following items in your list.  Once you Save Changes, the editions will be applied to your Items & Services List.

There a lot of ways that you can map and import Microsoft Excel and comma-delimited lists into QuickBooks Items and Services, Customers and Vendors lists using the “Add/Edit Multiple Items” screen.  (Try using a sample company to experiment with this tool before using it on your real data.)

Return to the Dressler Santaus web site.


Cleaning Up Your QuickBooks Data

Part 2:  Cleaning Up Customer, Vendor and Other Name Lists   

At the close of the year you may be looking at your customer list as you prepare to send holiday cards.  While reviewing the list you may be thinking about those customers who have become inactive.  QuickBooks offers different options for cleaning up and consolidating your customer, vendor, and other name lists.

Delete Names that Have Never Been Used

First, if a customer, vendor or other name list item has never been used in a transaction, then you can delete the list item.  In the Customer Center, highlight the customer you wish to delete.  Click on the Edit drop-down menu and choose to “Delete Customer”.  QuickBooks will warn you that you cannot delete this customer if it has been used in a transaction.

De-activate Namesqb.customer lists.2

If you have used the vendor, customer or other name in a transaction, then your next option is to de-activate it. In the Customer Center, highlight the customer you wish to delete. Click on the Edit drop-down menu and choose to “Make Customer Inactive”.  Now the list item will no longer be on the customer list when you display the list for “Active Customers”.  To expedite the process of de-activating many clients on your list,  you can change the View to “All Customers” and simply mark an X next to the names you want to designate as inactive.


Merge Duplicates

The third option, merging names, is a great tool for cleaning up duplicate names.  In order to merge names, be aware of the following rules:  Customer A and Customer B need to be merged together.   Customer A is the one we want to keep, therefore we will change the “Customer” name/id of Customer B to match that of Customer A exactly (case sensitive, spaces and punctuation).  I usually make sure the recipient customer (Customer A) is the one with the most information (name, address, phone, email, etc.) because the “sending” customer’s (Customer B) information will be lost in the merger.  All of the transactions will be saved, however, so that you will find all the activity for both Customers A and B on the merged customer’s history.

Confines of the Others Names List

If you use the Other Names list, you will notice that there are limitations to this list.  First, there is no display of transaction history like within the vendor and customer centers.  Secondly, a Form 1099 cannot be generated for someone on the Other Names list.  To solve either issue, you can change a name from being on the Other Names list to being a vendor, customer or employee (the Change Type button when editing an Other Names item).

Often I find that clients have the same name on both the Other Names list and the Vendor list.  In order to combine these duplicates, first the Other Name item must be changed to a Vendor name.  Then you can merge the two vendor names as described above.

Many clients feel compelled to create a vendor name for each company they purchase from – even gas stations, fast food restaurants, and department stores.  Their vendor lists become very lengthy with non-essential vendor names.  I usually suggest that only one vendor is created – “Cash Purchase” or “Debit Purchase” – and used over and over again for each miscellaneous purchase, noting the company in the memo line instead.


Click here to return to the Dressler Santaus web site: