QuickBooks: Inactivate QuickBooks List Items With Excel

Inactivate QuickBooks List Items With Excel

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. 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:

  1. Select File.
  2. Select Back Up Company.
  3. Select Create Local Backup.
  4. Click Finish.
  5. A prompt will inform you of the file name and location of the backup. ClickOK.

Create Backup File

Figure 1: Always perform a manual backup before attempting to import data into QuickBooks.


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:

  1. Select File.
  2. Select Utilities.
  3. Select Export.
  4. Select Lists to IIF Files.
  5. 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.
  6. Click OK to export the list to an IIF file.
  7. When prompted, specify a name and location for the file, such as IIF on your Desktop.
  8. Click Save to export the list to an IIF file.
  9. A prompt will notify you that the data has been exported. Click OK.

Figure 3

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:

  1. 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).
  2. Click Finish when the Text to Columns window appears (Step 6). In this context all of the default settings match our requirements.

open IIF file in Microsoft Excel

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:

  1. Press Ctrl-F to display the Find and Replace
  2. Type !INVITEM in the Find what field and press Enter.
  3. Press Escape to close the Find window and you are taken to the start of your item list.

locate start of 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:

  1. Scroll the worksheet so that the first cell containing !INVITEM appears in the upper left-hand corner of your screen.
  2. Select Column C or cell C21 in Figure 6.
  3. Select View, Freeze Panes, and then Freeze Panes.

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:

  1. Select the Data tab in Excel’s Ribbon interface.
  2. Select the Filter
  3. Click the arrow in Column K of the IIF file for an inventory list, which contains the current quantity on hand.
  4. Uncheck the Select All
  5. Check the 0
  6. Click OK.

Filter for items

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:

  1. Select File, and then Close.
  2. Click Save when prompted to save your changes.
  3. Click Yes when warned that you might lose features.

save changes to IIF file

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:

  1. Select File.
  2. Select Utilities.
  3. Select Import.
  4. Select IIF Files.
  5. When the Import window appears, double-click the IIF file that you edited in Excel.
  6. 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.

import IIF file back to QuickBooks

Figure 9: No warning prompt will slow you down during an IIF import, so always back up first.


Leave a Reply