Can You Merge Excel Into QuickBooks?
By Avery Martin
Excel spreadsheets provide a quick way to add information in a simple and standardized format. When you need to import that information into QuickBooks, you can do so using two main options. The first method involves creating an integration between QuickBooks and Excel. The integration method allows you to quickly update reports in QuickBooks even if the QuickBooks software remains closed. The second option allows you to input data to the appropriate Vendor, Customer or Item center to update and merge your data.
Integrate QuickBooks with Excel
Open "QuickBooks." Select the "File" menu followed by "Open or Restore Company."
Select the "Open a Company File" radio button, and then select "Next."
Select the folder on your computer where you store your QuickBooks company file. Double-click the "Documents" folder followed by "Intuit" and the "QuickBooks" folder. Open the "Company Files" folder, choose the company file you want to create a backup for and click "Open."
Open "Microsoft Excel" while QuickBooks remains open. You don't need to do anything further, as QuickBooks automatically adds a tab to Excel that allows you to merge your Excel reports with QuickBooks.
Click the "QuickBooks" tab in Excel, and select "Update Report" to add the information from Excel to your QuickBooks software. After the initial integration, you don't need to open QuickBooks to perform this operation.
Merge Excel Files
Click the "File" menu followed by "Utilities" and "Import." Select "Excel Files" from the Import drop-down menu in QuickBooks.
Select "No" if prompted to open the Add/Edit Multiple Lists page. Click the "Advanced Import" button on the Add Your Excel Data to QuickBooks page.
Click the "Browse" button, select the Excel file containing the information you want to import and click "Open."
Choose the sheet you want to import from the "Choose a Sheet in This Excel Workbook" drop-down list.
Click the "Choose a Mapping" drop-down menu, and select "Add New" from the list of options.
Type a name for the Mapping in the Mapping Name field. Mapping tells QuickBooks how to arrange the data in your Excel document by specifying the columns and rows that correspond to fields within the QuickBooks Customer, Vendor, Item and Account centers.
Click the "Import Type" drop-down menu, and select "Customer," "Vendor," "Item" or "Account."
Click the "Import Data" column for each item listed in the QuickBooks column on the Mappings page, and select the column where that information displays in your Excel file.
For example, for the Job or Customer Name field in the QuickBooks column, click the "Import Data" column. Select "Column A" from the drop-down list if the information for the Job or Customer Name fields in QuickBooks display in Column A of your Excel file.
Select each row on the Mappings page, and match each option in the QuickBooks column with an option in the Import Data column. If you are missing some data in your Excel file, do not assign Excel columns to those rows.
Click the "Save" button to save your mappings.
Click the "Import" button to import and merge the information from your Excel file into QuickBooks. Select "Yes" to continue if you receive a warning that you can't undo this process.
Create a backup of your QuickBooks file before merging data so that you can revert to your old data if you encounter any problems with the merging process. Click the "File" menu, select "Create Local Backup" and follow the prompts in the Create Backup wizard.
Information in this article applies to QuickBooks Premiere and Enterprise 2013. It may vary slightly or significantly with other versions or products.
Avery Martin holds a Bachelor of Music in opera performance and a Bachelor of Arts in East Asian studies. As a professional writer, she has written for Education.com, Samsung and IBM. Martin contributed English translations for a collection of Japanese poems by Misuzu Kaneko. She has worked as an educator in Japan, and she runs a private voice studio out of her home. She writes about education, music and travel.