Learn how to create, edit and archive Xero manual journals directly from Excel.
This article covers the following:
- Getting Started
- Columns Explained
- Grouping of lines
- Editing Transactions
- Scenarios When This Template Could be Used
It is recommended to familiarise yourself with the following articles:
Important notes to be aware of when using this template:
Each row in the template represents a transaction line. Only those marked with a Yes will be POSTed.
Since each row is a transaction the total Debit and total Credit amounts need to be equal.
In the POST TYPE column, select POST if you want to post directly to Xero. Select SAVE AS DRAFT if you wish to have a second look in Xero before committing.
You can upload a supporting working as an attachment to each row. To effect this, please select Yes under Upload Sheet column and make sure that the respective supporting sheet NAME is identical to the MY UNIQUE REFERENCE field in the template.
Mandatory fields must be filled in to successfully push a row to the accounting software - these are also marked with a white star in the template. The other columns are optional and can be used depending on what information needs to be posted/imported.
|Post?||Change to ‘Yes’ if you want to post the row details to your accounting software. Don’t forget to mark as ‘No’ for journals which are not required during this reporting period. There is NO NEED TO DELETE this entry … it may be required again during your next reporting period or for another organisation.|
|Upload Sheet?||You can upload supporting workings as an attachment to each row. To effect this, please select ‘Yes’ under the Upload Sheet? column, then create a new sheet and make sure to write the sheet name in the My Unique Refence column. THE NAME MUST BE IDENTICAL. In the newly created sheet, past in any documentation or workings you need. This sheet alone, will be added as an attachment to your Xero or Quickbooks organisation.|
|My Unique Reference||A unique reference has to be given to each journal. Besides uploading attachments, the My Unique Reference column can be used for grouping journals. This Journal reference controls how Journal lines within the spreadsheet are grouped together and can be used again (this is not to be confused with the separate System Journal reference automatically assigned by Xero).|
|Entry Date||This is the date that you would like your journal to be posted. This can be past, present or future.|
|Auto Reversal Date||You may want the journal you are posting to be reversed some time in the future, by entering a date here, the journal posted will be reversed on the given date. If this is not required, leave blank.|
|Tax Type||Choose if tax is Exclusive, Inclusive or No Tax. Choosing Exclusive will add the Tax % chosen to your total Cr and Dr amount to your accounting software. Inclusive will assume that your Dr and Cr amounts have already taken Tax calculations into consideration.|
|Post Type||Choose if you wish to post the journal or to save it in Xero as a draft.|
|Journal Narration||A description for each journal is required.|
|Account||Assign an account to this transaction (these are downloaded from the selected Xero organisation).|
|Tracking Code (both)||Tracking categories may be selected if they are configured in the connected Xero organisation.|
|Tax Rate||Choose the tax rate that needs to be applied to this transaction (these are downloaded from the selected Xero organisation).|
|Debit||Enter the debit value…|
|Credit||…and the credit value. The total debit lines and the credit lines for each My Unique Reference must be equal to 0.|
|Description||A description for each journal may be typed otherwise the journal narration will be used.|
Grouping of Lines
The template caters for creating transactions with multiple lines. When the following columns are identical DataDear will automatically collate them as a single transaction with multiple lines - there is no limit (unless imposed by the accounting software) on the number of lines that can be used:
|My Unique reference|
When grouping lines, it is recommended to put these under each other in the template.
Editing TransactionsThis template is a Post and Edit template, meaning it allows for transactions to be downloaded ( pulled from Xero) , edited and posted back to the accounting software.
If you are not familiar with this process please go through the article below.
How do I push & edit data into the accounting software?
Scenarios When This Template Could be Used
Year end Adjustments
When closing off the financial year and posting the year end adjustments, this template can be very helpful as it will expedite the process of closing off P/L accounts as well as posting any Audit adjustments.
Bank interest postings
Xero does not calculate Bank interest, this template can be setup once and amounts can be overwritten every month to post. Using this template will allow users to setup the journal entries once and post different amounts whenever bank interest needs to be posted.
Accruals and prepayments adjustment.
The Manual journals template can also be used to post Accruals and Prepayments adjustments on a month by month bases or as needed.