How to Import Trial Balance using Excel

This article will assist users  during the Setup process of a binder. 

If you have not yet created your workpaper binder, see Knowledge Article: How to Create a Binder

 

Linking a source can be performed during the binder Setup process or through the Settings & Tools menu once a binder has been created. 

 

Set up an Excel data source during the Binder set-up wizard

During the Setup process, select the Excel option and give your dataset a name, then select Add Source
 

Once connected, add columns to the workpapers. The current financial year will automatically be pre-filled for you.

 

Click + Add Column, which will add the comparative year dataset to your workpapers. 



Hint: You can add a calculation column, which can either calculate a variance, percent variance or percent of sales column to compare your current and prior year datasets as shown below.

Once configured, complete the remaining steps to set up your binder. 

Once the binder has been set up, you will be prompted to upload your Excel trial balance file. 


To ensure the right data is captured in your binder, ensure your data file includes the following columns:  

1. Acc No

2. Acc Name *

3. Acc Classification

4. Debit *

5. Credit

6. Quantity

7. Memo


*These columns are mandatory for importing your data. Where the data exists in only one column (i.e Debit and Credit in the same column, ensure Credit values are shown as – negatives)

Formatting Notes:

  • Active Workpapers does not mind the use of blank lines or header accounts and the like, however, it will assume that every line with a value in the debit or credit column is a valid account that needs to be imported, unless selected otherwise. 
  • In the Excel import process, it will automatically link the following text to classifications as shown below:
      

Classification
  

Valid Text Strings
  

Asset

"Asset", "Assets" or "A"

Liability

"Liability", "Liabilities" or "L"

Equity

"Equity", "Capital" or "E"

Revenue

"Revenue", "Income", or "R"

Cost of Goods Sold

"COGS", "Cost", "Cost of Goods Sold", "Direct Cost/s" or "C"

Expense

"Expense", "Expenses" or "X"

Other

All other text strings, including "O"

 

If you use an established account numbering system for the account number of each account, you can automate this process by selecting the numbering system used in the Classify Account Numbers dropdown menu. 



Finally, ensure the journal balances. For an imported trial balance, press Add and Close. For a Journal, select the relevant journal type and add a journal number, memo and date and click on Add and Close.


Re-importing current year date

To re-import current year data (overriding the existing import), navigate to the Index and select ‘Re-import’. Follow the prompts to upload your data file and re-import your updated data. 



Uploading prior-year or subsequent year data


To import prior or subsequent year data navigate to the Index and select ‘Re-import’. Follow the prompts to upload your data file.

When navigated to the Journals screen, from the dropdown select the column you wish to import this data into:



Ensure the journal balances. For an imported trial balance, press Add and Close. For a Journal, select the relevant journal type and add a journal number, memo and date and click on Add and Close.
 

Your prior year excel accounting data will now be loaded into the Index screen, ready to complete your workpapers.

 

Next articleHow to add a standard workpaper record to a binder.


 

Did you find it helpful? Yes No

Send feedback
Sorry we couldn't be helpful. Help us improve this article with your feedback.