Thus today we will focus on the most advanced method of import excel files into QuickBooks Desktop and QuickBooks Online. If your payroll data is being maintained by you or any third party in excel then at some point you would want the same data to be imported into QuickBooks.
QuickBooks is a highly underrated program. The reason we say this is because people only take this as software to maintain one’s books of accounts. But believe us this is not all that you can do with it.
Create a Backup File Before Importing Excel Data Files into QuickBooks
- Open the QuickBooks program and then open the File menu
- Select the Backup option and then Click on the Next button
- Click on the ellipsis (…).
- Select a location where you want to save the backup file.
- Click on OK and then Click on Yes to confirm the backup.
How to Import Excel Files into QuickBooks Desktop
If you don’t know how to do that already then just follow along to do that. There are basically thee methods to import excel files into QuickBooks Desktop using Standard import, Advanced import. From an Excel spreadsheet, you can import the customer, vendor, items, and chart of accounts. The options for import Excel files into QuickBooks.
- Method I- Using Standard import option
- Method II- Using advanced import option
Payroll data import excel files into QuickBooks Desktop using standard method
Where you can enter your information QuickBooks opens a formatted Excel spreadsheet. You can review results and view your data in QuickBooks when you save and close the file.
Import Vendors, Customers, and Products
- Start by backing up your payroll data.
- It’s always a good idea to maintain a backup file as you are dealing with your financials.
- You can go to the File menu, then you have to select Utilities Import and then Excel files.
- Click No, in case you get the Add/Edit Multiple List Entries window.
- You have to follow the wizard in importing files.
- You have to select the type of data.
- A formatted Excel spreadsheet opened by QuickBooks. After entering the information, you have to save the file and then close it. Then the option will be displayed Add My Data Now.
- Then you have to review the results and view the Data in QuickBooks.
- When finished you have to select Close.
Import Chart of Accounts
- Start by going to the Lists menu, then you have to select Chart of Accounts.
- Then at the bottom you have to select the Account drop-down menu, then you have to choose Import from Excel.
- Now you have to select Browse to select the Excel file that you want to import.
- You have to choose the file and then select Open.
- After that, you have to select the Excel sheet where the data you want to import is on.
- Now you have to map your accounts.
Import Excel files into QuickBooks Desktop using advanced method
Thus today we will focus on the most advanced method of data importing excel files into QuickBooks. Using this method allows you to keep the formatting and readability intact.
For items
If you’re importing items and don’t yet have an Excel or CSV file, proceed as described below.
Step 1: Turn on inventory preferences
- Log in as Admin to your company file.
- Ensure that Single-user mode is selected.
- Pick Preferences from the QuickBooks Edit menu.
- On the left pane, choose Items & Inventory.
- Next, select the Company Preferences tab.
- Check the boxes next to Active Purchase Orders and Inventory, then click OK.
Step 2: Set up accounts in your data file
- Select Chart of Accounts under Lists.
- Select New by right-clicking anywhere on the Chart of Accounts.
- Decide on Account Type. You must create the following account types for this step:
- Income Account for sales tracking.
- COGS cost of goods sold.
- Account for Inventory Assets.
Step 3: Create your data with the following for each item
- Item Name: Name you to want to appear in your item list.
- When importing a spreadsheet with sub-items, the parent item must either already be present in the item list or appear before the sub-item in the list order.
- The Item Name would be formatted with the Parent and the Sub-Item separated by a colon if you were importing Sub-Items.
Example:
- Widgets
- Widgets: Widget001
- Widgets: Widget002
- Widgets: Widget003
- Item Type: Include the item’s type, such as whether it is a service, an inventory part, etc. The item name must be written exactly as it appears in QuickBooks.
- Description of the item: The item’s written description must match what is found in the item list, sales orders, sales receipts, and invoices.
- Income Account: Name of the income account in which the item’s sales will be reported.
- Inventory Asset Account: The account’s name will be used to record the value of the inventory.
- Name of the COGS Account: This is the name of the COGS Account that will be used to record the purchase of the good.
- On Hand Quantity: The quantity of an item that is currently in stock (only applicable to inventory items).
- Cost of Item: The item’s price.
- Sales Price: The item’s sales price.
- Total Value: The item’s overall value (only applies to new inventory items).
- Date: The most recent time the item was purchased (only new inventory items).
- Passed through: Make sure to enter a Y in this column so you can track both the cost and the item’s price.
Optional Information:
- Manufacturer’s Part Number or SKU: The item’s part or SKU number can also be imported.
- Reorder Point: When the number of items on hand hits this level, QuickBooks will ask you to place another order for more items.
Step 4: Create the spreadsheet
Using these tables as guides you have to create the spreadsheet:
Step 5: Prepare to import
Make sure you are aware of where the Excel spreadsheet file is located. Throughout the process, QuickBooks and the spreadsheet will sync.
- You have to start QuickBooks.
- Activate the company data file.
- Without overwriting any earlier backups, make a copy of the QuickBooks company file.
Step 6: Import the spreadsheet
- You have to select Utilities by going to the file menu, then you have to select Import, and then click on Excel files.
- Then you have to select No on the Add/Edit Multiple List Entries.
- Now you have to select Advanced Import
- Then you have to set up a mapping.
- You have to select Browse and then choose the Excel file
- Then you have to choose the right sheet in the Excel workbook.
- After that, you have to select the This data file has header rows checkbox to identify the available headers from your file.
- You have to select Add New on the Choose a mapping dropdown.
- On the Mappings window:
- You have to type a name to easily identify the mapping (Customer, Vendors, etc. on the mapping name field.
- Choose the data that you are importing from the Import type dropdown.
- Then you have to match the QuickBooks and Import Data columns, then you have to select Save.
- To verify the mapping you have to select Preview.
- To complete the import you have to select Import.
- In case you receive the Duplicate Record Found error, then you will be presented with these options.
- You have to keep the existing data and you have to discard the import data
- With import data you have to replace the existing data, you have to ignore blank fields.
- With import data, you have to replace the existing data, including blank fields.
If getting an issue while import excel files spreadsheet. Follow the below steps to fix the errors.
- You have to choose the appropriate option and then you have to select Apply or Apply to all.
- Indicating the number of successful imports and the number of errors you will receive a notification. In case you have errors, you can select Save for the error log and then review it to determine the important action to resolve the errors.
- As required re-import the list.
For customers, vendors, and other lists of data
Use this option if your Excel file or CSV data is already structured for QuickBooks and you are importing data for Customers, Vendors, Items, and other lists.
- You have to select Utilities and then Imports and then Excel Files from the File menu.
- Then you have to select No on the Add/Edit Multiple List Entries
- You have to then select Advanced Import
- Now you have to set up a mapping
- You have to select Browse and then you have to choose the Excel file
- Now you have to select the right sheet in the Excel workbook
- Then you have to select the This data file has header rows checkbox to identify the available headers from your file
- After that, you have to select Add new on the Choose a mapping dropdown.
- On the Mappings window:
- You have to type a name to easily identify the mapping (Customer, Vendors, etc. on the mapping name field.
- Choose the data that you are importing from the Import type dropdown.
- Then you have to match the QuickBooks and Import Data columns, then you have to select Save.
- To verify the mapping you have to select Preview
- To complete the import you have to select Import
- In case you receive the Duplicate Record Found error, then you will be presented with these options:
- You have to keep the existing data and you have to discard the import data
- With import data you have to replace the existing data, you have to ignore blank fields.
- With import data, you have to replace the existing data, including blank fields.
If getting an issue while importing the customers, vendors, and other lists data spreadsheet. Follow the below steps to fix the errors.
- You have to choose the appropriate option and then you have to select Apply or Apply to all.
- Indicating the number of successful imports and the number of errors you will receive a notification. In case you have errors, you can select Save for the error log and then review it to determine the important action to resolve the errors.
- As required re-import the list.
Method III- Add/Edit Multiple Lists
You can add and modify multiple customers, suppliers, and products with this option. This cannot be used to import transactions (invoices, bills, etc.); rather, it is for list information (name, description, etc.).
- Select Add/Edit Multiple List Entries from the Lists menu.
- Choose the list you wish to work with from the List menu.
- In order to see a particular record, filter the list.
- (Optional) To select the columns you want to view in the Add/Edit Multiple List Entries box, click the Customize Columns button. You may quickly work on the required columns in this manner. To return to the list’s default columns, choose the Default button.
- Select a column in the left pane and choose to Add it.
- Select a column in the right pane and choose to Remove it.
- Select the column from the right pane and choose to Move Up or Move Down to change the order of the columns and then select OK.
- By selecting the appropriate fields, you can add or update the entries. Please take note that you can copy (Ctrl+C) and paste (Ctrl+V) data from Excel into this window. Additionally, you can duplicate rows by selecting Copy Down and Duplicate Row after selecting any field.
- At last, you have to select Save Changes
You will get a warning after saving your changes that will let you know how many records were saved.
If getting an issue while modify multiple customers, suppliers, and products. Also, QuickBooks will inform you of any errors that need to be fixed. Follow the below steps to fix the errors.
- Any field can be clicked or hovered over to reveal the error.
- To correct the mistakes, update the record.
- Repeat the procedure until all errors are fixed and then select Save Changes. Keep in mind that you can pick Close without making any additional changes if you don’t want to correct an error and save the changes you have already made. If there are errors in the edits, QuickBooks won’t save them.
Import Excel Files into QuickBooks Online
Right before you proceed to payroll data Import excel files into QuickBooks Online make sure that you create a Backup file.
To know how to make a backup file, accurately perform the steps given below
- For the first and foremost step, open the File menu from the QuickBooks homepage.
- Then proceed to Select the backup option.
- After the subsequent step, hit the next click on the ellipsis button.
- Now, proceed to choose the location where you want to save the backup file.
- Next step, select the OK button.
- Thereafter, you need to click Yes, on the confirmation message.
- Finally, the backup file has been saved on the selected location.
Things to make sure of before you start the process of importing Payroll Data using Excel Files into QuickBooks Online
- The first is to, Create and send direct deposit paychecks in the same corporate file as direct deposit paychecks.
- If you send paychecks in a distinct corporate file, you may be experiencing data loss.
- Employee direct deposit paychecks must be prepared and sent separately from vendor direct deposit payments.
- After you’ve prepared the paychecks, Intuit recommends switching to single-user mode before sending. It makes it easier to manage the processes.
Tips to Payroll Data Import Excel Files into QuickBooks Online
Generally, The purpose of the payroll data report is to summarize the data in the Excel report selection. The data importing results will be available in the excel sheets.
- For the first step, click on the Reports menu, from the top menu bar.
- Next, proceed to select the Employees & payroll.
- After the subsequent step, to continue, choose the next click on the option that says Summarize Payroll Data in Excel.
- Now, for another way, proceed to open the report menu that you want to export.
- For the next step, choose the selected destination and type.
- Once the above step is done, to continue, you further need to perform the guidelines to enable the macros in Excel.
- Thereafter, proceed to enter the data range in the excel sheet.
- After the subsequent step, To remove it from the QB payroll reports, click the Clear button.
- Next, To add or exclude the report, go to the Optional Reports section.
- For the next step, Click the Clear button to clear the checkboxes.
- Finally, Then, tweak the settings to your desire.
QuickBooks Payroll Data Things Include
QuickBooks is highly capable of import Excel files into QuickBooks.
- Consolidating your data,
- Track all the sales and expenses you do,
- Create and manage all your invoices and send them via email from within QuickBooks,
- Your profit and loss, cash flow statements, and balance sheet reports can be made with ease.
- Tracking debtors and creditors is a breeze with QB.
- Tax computation with just a click of a button.
Hope you are able to fix your issue and you are able to import Excel files from QuickBooks Desktop and Online. If you are still facing any problems then contact QuickBooks payroll Consultant now. Our QuickBooks consultant will take your query on a priority basis and help you fix your issue in no time.
