A client reached out to me recently for help with their QuickBooks Desktop company. Over the years they’d set up numerous one-time use inventory items, to the extent that they now had over 7,000 items that they wanted to make inactive. As we’ll see, it’s not much trouble to make a few items inactive by hand in QuickBooks, but this approach falls apart when you have dozens or thousands of list items to affect. In this article I’ll show you how to easily inactivate QuickBooks list items with Excel.
You will export the list items to an Intuit Interchange File (IIF), which you’ll then open in Excel. You’ll then be able to toggle the field that manages the inactive status of a list item, and then import the resulting file back into QuickBooks to batch inactivate as many list items as you wish. Do note, however, that this technique works with the Desktop versions of QuickBooks only, and is not compatible with QuickBooks Online.
First Things First
Before you even contemplate importing any type of file into QuickBooks, you’ll first want to create a backup. Even if you have QuickBooks set to automatically back up, take the belt-and-suspenders approach and create another backup:
- Select File.
- Select Back Up Company.
- Select Create Local Backup.
- Click Finish.
- A prompt will inform you of the file name and location of the backup. Click OK.
Figure 1: Always perform a manual backup before attempting to import data into QuickBooks.
Before we cover importing and exporting, let’s complete your safety net by showing you how to restore your company in case something goes awry:
- Select File.
- Select Open or Restore Company.
- Select Restore a backup copy.
- Click Next.
- Accept the default value of Local backup and then click Next.
- The Open Backup Copy window should automatically remember the name and location of your most recent backup. If it doesn’t, browse to locate the file, and then in either case click Open.
- Click Next when the Open or Restore Company window appears again.
- The Save Company As window will show you the current name and location of your QuickBooks company. Change the file name if you wish, and then click Save.
- If you chose to save over your existing file, click Yes.
- If you chose to save over your existing file, type YES and then click OK.
- You’ll be notified when the restore process is complete. Click OK.
Figure 2: These steps enable you to restore a backup in case an IIF import goes awry.
Exporting Your List
At this point we can discuss exporting your list to an Intuit Interchange File, which is a tab-delimited text file. Simply put, this is a file that you can open in Notepad, Word, or even Microsoft Excel. Once you make any necessary edits, you’ll save the IIF file and then import it into QuickBooks. Getting to the IIF format in QuickBooks requires navigating several levels of menus:
- Select File.
- Select Utilities.
- Select Export.
- Select Lists to IIF Files.
- When the Export window appears, choose the list type you wish to affect. For purposes of this article I’ll check the Item List If you choose more than one list type, the lists will be stacked into a single IIF file, so I recommend only exporting one list type at a time.
- Click OK to export the list to an IIF file.
- When prompted, specify a name and location for the file, such as IIF on your Desktop.
- Click Save to export the list to an IIF file.
- A prompt will notify you that the data has been exported. Click OK.
Figure 3: Any list in QuickBooks can be exported to a tab-delimited IIF file.
At this point we’re ready to open the IIF file in Microsoft Excel:
- Within the Open window in Excel change the File Type filter to All Files (Step 4 in Figure 4) and then double-click on your IIF file (Step 5).
- Click Finish when the Text to Columns window appears (Step 6). In this context all of the default settings match our requirements.
Figure 4: Accept the default settings within the Text to Columns window.
Now let’s take a moment to get oriented in this file. The IIF file for the Item list contains much more information than you need, so you’ll want to tread carefully. Follow these steps to find the first inventory item:
- Press Ctrl-F to display the Find and Replace
- Type !INVITEM in the Find what field and press Enter.
- Press Escape to close the Find window and you are taken to the start of your item list.
Figure 5: Use the Find window to locate the start of your item list.
At this point you’ll want to arrange your worksheet so that you can freeze the worksheet panes:
- Scroll the worksheet so that the first cell containing !INVITEM appears in the upper left-hand corner of your screen.
- Select Column C or cell C21 in Figure 6.
- Select View, Freeze Panes, and then Freeze Panes.
Figure 6: The Freeze Panes command makes it much easier to navigate an IIF file in Excel.
At this point when you scroll to the right you should always see columns A and B on the screen. As you scroll to the right you’ll see column AC has a caption of HIDDEN. This is the field that contains the active or inactive status for an item. Y signifies that an item is inactive, and N translates to active. Change the settings for the items as needed, being careful to only enter Y or N in column AC.
My client wanted to make any inventory items with a quantity of zero inactive, so I carried out these steps:
- Select the Data tab in Excel’s Ribbon interface.
- Select the Filter
- Click the arrow in Column K of the IIF file for an inventory list, which contains the current quantity on hand.
- Uncheck the Select All
- Check the 0
- Click OK.
Figure 7: You can filter for items that meet specific criteria within an IIF file.
At this point you can select all visible cells in column AC, type the letter Y, and then press Ctrl-Enter to update all of the zero quantity items at once. Alternatively, you can manually update items one at a time if this broad-brush approach casts too wide a net.
Once you’ve updated your IIF file:
- Select File, and then Close.
- Click Save when prompted to save your changes.
- Click Yes when warned that you might lose features.
Figure 8: Use these steps to save changes you’ve made to the IIF file.
The final step is to import the revised IIF file back into QuickBooks:
- Select File.
- Select Utilities.
- Select Import.
- Select IIF Files.
- When the Import window appears, double-click the IIF file that you edited in Excel.
- A prompt will inform you when your data has been imported. Click OK.
Notice that no “are you sure?” prompts appear during the IIF import process, so be sure to back up your data before attempting this procedure.
Figure 9: No warning prompt will slow you down during an IIF import, so always back up first.