Import Chart of Accounts from Excel to QuickBooks

How to import chart of accounts from Excel to QuickBooks? Well, this is an important aspect to learn and know of, in case you need to import the existing Chart of accounts from excel or the Microsoft sheet to the QuickBooks Desktop.

This is one of the most important tools to learn of, in case you are a freelancer or have been involved into the various aspects of QuickBooks implementation. This is especially relevant in case there are users who are already using some or the other ACCOUNTING SOFTWARE previously.

Hence, you might want to continue using the existing CHART OF ACCOUNTS  from the excel sheets over to the current new ACCOUNTING SOFTWARE, without having to do the entire hard work all over again. For, this we have two different methods to do the needful. 

Let us take a look into each of them individually:

How to Import The Existing Chart of Accounts Over to QuickBooks Software

This is one of the most important aspects to imply, as one can easily transfer numerous CHART OF ACCOUNTS from the excel sheet over to the software in a matter of minutes.

The above is an example of the existing Chart of accounts in an excel sheet of a user. The simple CHART OF ACCOUNT column comprises of ACCOUNT CODE, ACCOUNT NAME AND ACCOUNT NATURE. While the same task can be done manually, but it will take days to complete, however with the right tool and technique one can easily do so in a manner of a few seconds. The IMPORT technique is one such tool that can allow the user to be able to easily import the entire sheet into the software in a few clicks.

For this, it is firstly most important to understand the layout of the QB software, where your existing chart of accounts can be compatibly imported to. 

• Usually, the very first outlook of the QB layout for the chart of accounts looks something like this
  
• Go to the CHART OF ACCOUNTS form, which looks something like this:
  
• Here you will observe that there is a distinct box titled as ACCOUNT TYPE.
• Now you need to go back into your excel sheet and create a column for ACCOUNT TYPE. Do so by simply typing the required title along-side the existing table on the excel sheet.
• Likewise, you also need to create another title called the ACCOUNT NUMBER according to the box observed on the QB layout above.
• Next create the ACCOUNT NAME as well onto your excel sheet and leave the SUB ACCOUNT column as it is for the time being.
  
• Now, as for the OPTIONAL SECTION in the QB layout, the user has the choice to include it in the excel sheet if needed or leave it as it is if they desire to do so.
• The main topics or headings required for the import are generally four: Account type, account number, account name and the sub account.
• Now, in a separate section in the excel sheet you need to create the QB ACCOUNT TYPE that comprises of the two main columns: QuickBooks Account Type and the CAN BE USED FOR. This is in according to the titles of the same given in the QB CHART OF ACCOUNT form.
  
• Now go back to the previous page of the main category on the excel sheet where you have entered the Account Type and Name etc.
• In accordance to the category, you need to group your Account name under the Account type with the relevant title for example BANK.
  
• As for the entry of the ACCOUNT NUMBER, you need to remember that in the QB it needs to be a maximum of 7-digits. Hence, when entering the Account number under the column, you need to ensure that it is well within the limits of 7-digits or else, the process of import will not be possible.
• In order to ensure that there are 7-digits in the existing account number so as to be able to ‘fit’ into the QB layout, you need to apply the formula for length on to the excel sheet:
• For this you need to use the formula of [len (A30] on to the excel sheet.
• On applying this formula the user will be able to find out or detect any such ACCOUNT NUMBER which exceeds the 7-digit limit.
• Now once you have checked that all the account code numbers are at place, you can simply click and drag the codes onto the ACCOUNT NUMBER COLUMN on the right hand side.
• Next, as for the ACCOUNT NAME, another tip you need to bear in mind is that it also needs to comprise of a set of characters which is limited to 31.
• Once again you need to apply the formula for the LENGTH, by clicking on to the section of ACCOUNT NAME:
  
• Thus, the text length of all the ACCOUNT NAMES will be displayed accordingly, thus allowing you to analyse which ones require correction and editing.
• Now, selecting and rectifying the right cells might take a long time, however this can be easily done in the excel sheet by applying the CONDITIONAL FORMATTING.
• Go to CONDITIONAL FORMATTING> HIGHLIGHT CELLS RULE> GREATER THAN > enter the value of 31 on the pop-up box that emerges.
• This will instantly highlight the cells with the characters that are greater then 31. This will simplify your task of checking out individually, instead you can just attend to the cells that have been highlighted.
• Shorten the cells with the names that have characters that exceed 31 characters.
• Now, as we know that there are PARENT OR HEAD ACCOUNTS WITH SUB ACCOUNTS IN THE QUICKBOOKS LAYOUT. Hence we need to link the subaccount with the parent account in the excel sheet as well.
• This is easily done in the excel sheet by following a simple formula [ = + Click on the Account name you want as a PARENT+ & + “ THE NAME OF THE SUB ACCOUNT YOU WANT + : + “ = G3]
• This way one can easily import the CHART OF ACCOUNTS over from the Excel sheet onto the software in a matter of minutes.
• Now, once all the changes have been made accordingly, SAVE the changes and close.
• Go to the QB form and close
  
•  The above is the main QuickBook main layout
• Go to the FILE menu at the top left side of the screen> UTILITIES> IMPORT> EXCEL FILES
  
• Next you will get a dialogue box, ‘ ADD/EDIT MULTIPLE LIST ENTRIES. You need to click on the NO option here.
• Next, click on ADVANCED IMPORT,to be transferred onto another  IMPORT window.
  
• You will recive a dialogue box that says ‘set up import’
• Here you need to browse to get the pathway to the required files
• Next select the ‘choose a sheet in this excel workbook, and select the sheet where you have stored your CHART OF ACCOUNTS on excel
• Next go to CHOOSE A MAPPING. This implies to creating the rightful layout that allows the QB to easily absorb the layout created in a simple manner.
• For this you need to ADD NEW> MAPPING NAME > IMPORT TYPE (select the right name from the drop down menu) we generally select ACCOUNT from the drop down menu.
• Next, you can click on the PREVIEW box located on the bottom left side of the screen, this will allow you to track if the Mapping of the Account has been done in the right and desired manner as required.
  
• This will take you to the preview screen as shown above. The software will check if the entire mapping has been done in the rightful manner as required. This will be indicated with the OK message as shown in the image example above.
• As you scroll down below the list, you will get to check if all the coding are OK as per requirements.
• Next let us take a look at the PREFERENCES at the QuickBooks.
  
• With the help of PREFERENCES, one can easily get to know if the account created by you has already been previously created.
• Under PREFERENCES one can also get to know if the ACCOUNT HEAD or PARENT ACCOUNT has been wrongly assigned.
• As you can see in the image example above, you will be asked about what you need to do once such an error has been detected by QB.
• Usually it is recommended that one must select the option:  PROMPT ME AND LET ME DECIDE.
• Likewise, just below the DUPLICATE HANDLING, you will notice ERROR HANDLING section. This also comprises of two options.
• For this, it is recommended by the experts that you select the second option which is the: DO NOT IMPORT ROWS WITH ERRORS. This will allow you a relief from having to re-open each individual ACCOUNTS HEAD singularly and check them over again.
• Now, once you have checked and selected the right options, you need to click on IMPORT located at the bottom of the screen.
• You will receive a WARNING dialogue box which states that:
  
• This is the dialogue box that will emerge. It states the caution about the fact that once the data has been imported to the QB it cannot be undone.
• Also most importantly, it is advised by the software that you need to create a BACKUP file of your data. This will offer you with the required knowledge as and if you loose or somehow suffer a corrupt company file after the IMPORT has been done.
• Once you have undertaken all the required precautions, click on YES; which will allow the import of all the required QB heads in the systematic manner.
• This will also show you the CORRECT RECORDS, and also those that have ERORS in them. It will further ask what you wish to do with the ERRORS.
  
• Now, recheck if all the records and data of the chart of accounts have been properly SAVED as required.
• You will discover that all the ACCOUNTS heads have been properly imported as required, along with the Sub heads as well. This entire process would have been quite lengthy, if you had to do this manually.
• Now, as you might have remembered, we also have to deal with the ERROR log, that had occurred previously. For this you need to SAVE THE ERROR LOG onto the desktop or wherever it is accessible.
  
• Here you can see in the example above, that the EEROR ACCOUNT HEAD and the error it comprises of is also stated accordingly.
• To rectify the ERROR, one can go back to the QUICKBOOKS accounts file
  
• These are the ERROR in the example above, that you can easily rectify manually as well.
• For this you simply have to go back to the ACCOUNTS FORM window and make the right kind of corrections.
  
• Once you have rectified the errors, you can go back to recheck over the QUICKBOOKS file to see if the changes have been reflected or not.

CONCLUSION

So, there you have is my tech wizards, these are the required steps on how to be able to import the data from Excel to the QB. Here one has to just understand the basics of the FORMULA application in order to get the entire process of IMPORT done with a simple click.

Set up Chart of Accounts in QuickBooks