Showing results for 
Search instead for 
Did you mean: 

Import or export MS Excel files

Learn how to import and export QuickBooks data with MS Excel files.

You can import and export various list and transaction types to and from QuickBooks Desktop using different file formats.

Import Excel files

You can import Customer, Supplier, Items, and Chart of Accounts from an Excel spreadsheet. There are several options available when importing Excel files from QuickBooks.

QuickBooks opens a formatted Excel spreadsheet where you can enter your information. When you save and close the file, you can review results and view your data in QuickBooks.To import Customers, Suppliers, and Products you sell

  1. Go to File menu > Utilities > Import > Excel Files.
  2. If you get the Add/Edit Multiple List Entries window, click No.
  3. Follow the wizard in importing files.
    1. Select the type of data.
    2. QuickBooks opens a formatted excel spreadsheet. Once you have entered your information, saved the file and closed it, you will be given the option to Add My Data Now.
    3. Review results and view Data in QuickBooks.
    4. Click Close when done.

Note: If you need to import additional customer, supplier or item data from excel using this option, you can always go back to this window.

To import Chart of Accounts

Note: Before importing, we recommend that you create a backup of you company file.

  1. Go to the Lists menu, then click Chart of Accounts.
  2. Click the Account drop-down at the bottom, then choose Import from Excel.
  3. Click Browse to select the Excel file you wan to import.
  4. Select the file, then click Open.
  5. Select the Excel Sheet where the data you want to import is on.
  6. Map your accounts.

    Mapping your accounts tell QuickBooks how to import the data from your Excel sheet. The column headings in QuickBooks may be different from your Excel, so you’d need to match them.

    1. Type in a mapping name.
    2. Select Account as Import type.
    3. Match the information under QuickBooks with the column headings in your Excel sheet.
    4. Click Save.
  7. Click Import. If it's your first time to import, click Yes to confirm the process.

This option allows you to add and edit multiple customers, suppliers and items. This is for list information (name, description, etc.) and cannot be used to import transactions (invoices, bills, etc…).

  1. From the Lists menu, select Add/Edit Multiple List Entries.
  2. Click the List dropdown and choose the list you want to work with.
  3. Filter the list to see a particular record.
  4. (Optional) Click the Customise Columns button to choose which columns you want to see in the Add/Edit Multiple List Entries window. This way, you can easily work on the columns you need. Click the Default button to return to the preset columns for the list.
    1. To add: from the left pane, select a column and click Add.
    2. To remove: from the right pane, select a column and click Remove.
    3. To rearrange the column order: from the right pane, select the column and click Move Up or Move Down.
    4. Click Ok.NOTE: Columns like customer or supplier name are required and cannot be removed. You can only change their order.
  5. Add or edit the entries by clicking the appropriate fields. Note that you can copy (Ctrl+C) data from Excel and paste (Ctrl+V) it into this window. You can also Copy down and Duplicate rows (highlight any field and click Copy Down and Duplicate Row)
  6. Click Save Changes.

After saving your changes, you will receive a warning telling you the number of records saved. QuickBooks will also tell you if there are errors that need to be fixed.

To fix the errors:

  1. Click or hover your mouse in any field to see what the error is.
  2. Update the record to fix the errors.
  3. Click Save Changes and repeat the process until all errors are fixed. Note that If you don’t want to fix an error, you can save the changes you’ve made so far and click Close without making further changes. QuickBooks will not save the changes that contain errors.

Export Excel files

You can export Customer, Supplier, Payroll lists and transactions, as well as Items list into an Excel spreadsheet. If you need to change a report’s appearance or contents in ways that are not available in QuickBooks, you can also export reports and customise it in Excel without affecting your QuickBooks data.

  1. Open the Customer/Supplier/Payroll centre.
  2. Click the Excel drop-down and choose:
    • Export Customer/Supplier/Employee list if you want to export customer/supplier/employee data such as name, balances and contact information.
    • Export Transactions if you want to export transactions (either by name or transaction type).
  3. In the Export window, choose whether to create a new worksheet or update an existing worksheet.
  4. Click the Export button. QuickBooks opens MS Excel and you can edit the information as needed.
  1. Go to Lists menu > Item List.
  2. On the Excel dropdown, choose Export all Items.
  3. In the Export window, choose whether to create a new worksheet or update an existing worksheet.
  4. Click the Export button. QuickBooks opens MS Excel. You can edit the information as needed.

Information that QuickBooks exports to Excel include:

  • Data in the report, as it is displayed on screen
  • Formula for subtotals, totals, and other calculations
  • Row labels that describe the contents of each row
  • Headers and footers from the QuickBooks report
  1. Open the report you want to export.
  2. Click the Excel drop-down at the top of the report, then select Create New Worksheet or Update Existing Worksheet
  3. In the Send Report to Excel window, choose what you'd like to do with the report.
  4. Click the Advanced button to see other available options for the exported report then click OK.To ensure the exported report will show Header information

    If you are using a newer version of MS Excel and the exported report does not show header information, go to the Printing options section and make sure the On Printed report and screen radio button is selected.

    User-added image

  5. Click the Export button. QuickBooks opens MS Excel. You can edit your data as needed.

Was this helpful?

You must sign in to vote, reply, or post