Who wants to spend time recreating the same QuickBooks Chart of Accounts when one perfected version can be saved and re-used whenever it's needed? Learn how to export a QuickBooks Chart of Accounts into Microsoft Excel.
What's the Deal with Exporting?
Anyone who has worked with financial software can attest to the painstaking efforts it takes to create the perfect company file. Even with the canned Chart of Accounts provided in the QuickBooks software, modification is often necessary. Once modified, saving the Chart of Accounts for later use is important to help reduce duplication of efforts and standardize your practices. So how is exporting done? Let's get started.
Five Easy Steps to Export
- Open the QuickBooks company file that contains the Chart of Accounts you want to export.
- Select File > Utilities > Export > Lists to IIF Files from the menu.
- An export pop up window containing a checkbox list displays. Select "Chart of Accounts" from the list.
- The windows "save as" dialog box opens. Select the folder that you would like to save this file in from the "save as" drop down box located at the top of the window.
- Name the file. Be descriptive. For example, "Construction CoA."
Ten Steps to Convert to Microsoft Excel
The file that you exported needs to be converted into Excel format and prepared for later import back into QuickBooks. Follow these steps:
- Open Microsoft Excel.
- Use the "Text Import Wizard" to locate and open the IIF file. For Microsoft Office 2007 users, the wizard is located under the Data tab and is called "From Text." Click on the "From Text" button.
- The "Import Text Files" dialog box opens. Make sure that "All Files" is selected.
- Find the IIF file, click on it, and select "open." From the example above, the file name is "Construction CoA."
- The wizard walks you through three steps. The first step is to choose the file type. Choose Delimited and click next.
- The next screen in the wizard allows you to select the Delimiters. Select "tab" from the checklist and click the next button.
- The third screen asks you to choose the column data format. Choose general and click finish.
- A small box pops up asking where to put the data. Choose "existing worksheet" and select "Okay".
- From the file menu, choose "save as" to save the new file in Excel workbook format. When the save as dialog box opens, name the file, choose the folder to save it to, and click on save. Microsoft Excel should add the .xls extension to the file name automatically.
- Close Excel. Browse to your newly saved Excel file and open it.
Two Final Steps
Following are the final steps to completing the export and preparing the file for import back into QuickBooks:
- Delete all the rows above the !ACCNT row.
- Find the "ACCNTTYPE" column, usually column E. Notice that the account types in this column have been abbreviated. The names must be changed back to their full account name or QuickBooks will generate an error when you try to import the list.
- EXP should be renamed to EXPENSE
- COGS should be renamed to COST OF GOODS SOLD
- AR should be renamed to ACCOUNTS RECEIVABLE
That's it! The file is now ready to be imported back into QuickBooks.