Imagine a situation, wherein you have to enter a whole lot of transactions into QuickBooks. No doubt, it is a time taking and labor intensive process. Apart from it being a tiring process, manual data entry is highly prone to errors as well. The best way would be to import Excel file into QuickBooks Desktop. There are different ways of doing it. To make the import process easier for you, look at the listed steps in this blog ahead.
Wondering how to import transactions into QuickBooks desktop from CSV? Don’t worry! We will advise you to give us a call on our Toll-Free Number +1-(855)-955-1942 and get in touch with our experts.
Different Ways To Import Data From Excel To QuickBooks
Users can import Customer, Vendor, Items, and Chart of Accounts into QuickBooks through Excel spreadsheet. There are 3 options available that will help you import Excel file into QuickBooks desktop:
- Standard Import
- Advanced Import
- Add/Edit Multiple Lists
1: Standard Import
QuickBooks launches a structured Excel spreadsheet into which you may enter your data. You can check the findings and view your data in QuickBooks after you save and close the file. Customers, vendors, and products you sell can all be imported.
- Tap on File menu, select Utilities> Import> Excel Files.
- Click NO, if you get the Add/Edit Multiple List Entries window.
- Further, you may follow the wizard in importing files:
- Firstly, select the type of data.
- A formatted Excel spreadsheet will open, there, enter your information, save it and close the file.
- Further, an option to Add My Data Now will appear on your screen.
- Go through the results and view Data in QuickBooks.
- Once done, click on Close.
NOTE: You can always go back to this window, In case, you want to import additional customer, vendor or item data from excel using this option.
To import Chart of Accounts
NOTE: Create a backup of your company file Before you import data to QuickBooks from excel.
- Select Chart of Accounts from Lists Menu.
- Click on the Account drop-down, then choose Import from Excel.
- To select the Excel file that you want to import, Click on Browse.
- Further, select the file, and click Open.
- Then, choose the Excel Sheet where the information you want to import is stored.
- Now, Map your accounts as it tells QuickBooks how to import the data from your Excel sheet. The column headings in QuickBooks aren’t similar to Excel, so you have to match them.
- Enter the mapping name and then, choose Account as Import type.
- Go through the info under QuickBooks with the column headings in your Excel sheet.
- Click on Save.
- Lastly, click on Import.
NOTE: If you are trying to import excel file into QuickBooks desktop for the first time, click on Yes to confirm the process.
2: Advanced Import
If you aren’t having an Excel or CSV file yet, and are trying to Import transactions into QuickBooks from excel, then you must proceed with the below-given steps:
Step 1: Inventory Preferences must be ON
- Firstly, sign-in to your company file as Admin and you must be in Single-user mode.
NOTE: An option to switch to multi-user mode appears when you go to the File menu. If it isn’t, then, you must switch to single-user mode.
- From the QuickBooks Edit menu, Click on Preferences followed by the Items & Inventory option.
- Further, Tap on Company Preferences tab.
- Lastly, make sure to mark the Inventory and Purchase Orders checkbox, then, click OK.
Step 2: Set Up Accounts in your Data File
- Go to Chart of Accounts from Lists Menu.
- Right-click anywhere in the Chart of Accounts and select New.
- Further, Choose the Account Type. For this, you have to create 3 account types:
- Income Account to track sales.
- Cost Of Goods Sold (COGS) Account.*
- Inventory Asset Account.*
Step 3: Create your Data with the following for each item:
- Item Name: Name you want on your item list
- Item Type: Include the type of item, i.e. inventory part, service, etc. Item name must be spelled out just as you see it in QuickBooks.
- Description of the Item: Written description of the item that will appear in the Item List, Sales Orders, Sales Receipts, and Invoices.
- Income Account: Name of the Income Account in which sales of the item will be recorded.
- Inventory Asset Account: Name of the Asset Account in which the inventory value will be recorded.
- Name of the COGS Account: Name of the COGS Account in which purchases of the item will be recorded.
- On Hand Quantity: The Quantity On Hand of the Item (Inventory Items only).
- Cost of Item: The Cost of the Item.
- Sales Price: Sales Price of the Item.
- Total Value: The Total Value of the Item (New Inventory Items only).
- As Of Date: When the Item was last purchased (New Inventory Items only).
- Is Passed Thru: Be sure that you put a Y in this column so you can track both the cost and price of the item.
IMPORTANT: Use the same name, spelling, and capitalization as it appears in the Chart of Accounts.
Step 4: Make a Spreadsheet
Try following the tables attached below for creating the spreadsheet:
Step 5: Prepare to Import
You must be aware of the location of the Excel spreadsheet file, as QuickBooks syncs with the spreadsheet while you upload transactions or import excel file into QuickBooks desktop:
- Open the QuickBooks application and then your company file.
- Make sure to back up the QuickBooks company file (Don’t overwrite any previous backup).
Step 6: Import the Spreadsheet
- Tap on the File menu, select Utilities> Import> Excel Files.
- Then, select No on the Add/Edit Multiple List Entries.
- Further, select Advanced Import option and setup a mapping by following the below-given steps:
- Click on Browse to choose the Excel file.
- Then, select the correct sheet in the Excel workbook.
- Make sure to mark This data file has a header rows checkbox to identify the available headers from your file.
- Select Add New from the Choose a mapping dropdown.
- Then, follow the below-given steps on the Mapping window:
- Type a name On the mapping name field to easily identify the mapping (such as- Customer, Vendors, etc…).
- From the Import type dropdown, you need to select the data that you want to import.
- Match the QuickBooks and Import Data columns, then Save.
- To verify the mapping, click on Preview.
- Lastly, click on Import to complete the import process.
NOTE: The QuickBooks column shows which customer fields are available in QuickBooks, whereas the Import Data column shows which row headers are available in your Excel file. If you don’t have any data from your file that will match the fields in QuickBooks, you must leave it blank.
- The following options will appear on your screen, if you come across the Duplicate Record Found error:
- Try to keep the existing data and discard the import data.
- Further, make sure to replace the existing data with import data, by ignoring blank fields.
- Replace the existing data with import data, including blank fields.
3: Add/Modify Multiple Lists
You can easily add and edit multiple customers, vendors, and items. However, you can use it for list information (name, description, etc.), but not for the import transactions (invoices, bills, etc.).
- Tap on Add/Edit Multiple List Entries, From the Lists menu.
- Then, click on the List dropdown and choose the list that you want to work with.
- Moreover, if you want to see a specific record, then you can filter the list.
- Further, you may add or edit the entries by clicking on appropriate fields. You can copy (Ctrl + C) data from Excel and paste (Ctrl + V) it into this window. Moreover, you can also Copy down and Duplicate rows.
- At last, click on Save.
Once you have saved the changes, you will get a prompt displaying the number of records saved. Along with that, QuickBooks will also display the errors that need to be fixed.
Instant Assistance to Upload Transactions to QuickBooks Desktop!
We hope the steps mentioned in this blog would have helped you import excel file into QuickBooks desktop. However, if you need any further assistance, we would suggest you to get in touch with our experts by giving us a call on QuickBooks Data Service Solutions Helpline Number +1-(855)-955-1942.