Skip to main content
All CollectionsOrganisationsExcel organisations
Data format in Excel organisations spreadsheets
Data format in Excel organisations spreadsheets

An overview of how to format data for an excel organisation depending on where the account in question sits.

Updated this week

When filling the Excel template to create an Excel organisation, the data entered in the cells must follow a specific format.


Historical data format

The template used to import data to an Excel organisation is split into tabs for the Profit and Loss and the Balance Sheet.

Depending on where the account in question sits will determine what format the data should be added to the template as.

📎NOTE: It doesn't matter if the data is being uploaded in monthly, quarterly or annually periods, the format will always be the same.

Profit & Loss data format

Accounts in the Profit and Loss tab will need to be uploaded as transactional period movement.

  • Income

  • Cost of Sales

  • Expenses

  • Other Expenses

  • Other Income

All figures will need to be uploaded as positives.

EXAMPLE: If an Income account has a monthly transactional total of £100, the figure added to the spreadsheet will be 100. If it was a negative figure, such as for a refund, the figure to add would be -100.

If an Expense account has a monthly transactional total of £50, it will also be added as a positive, so the figure added to the spreadsheet will be 50. If it was a positive figure, the figure added to the spreadsheet would be -50.

Balance Sheet data format

Accounts in the Balance Sheet tab will need to be uploaded as cumulative period movements:

  • Bank

  • Fixed Assets

  • Current Assets

  • Non-Current Assets

  • Current Liabilities

  • Non-Current Liabilities

  • Equity

All figures will need to be uploaded as positives.

EXAMPLE: If a bank account had a balance of £500 at the end of January, the figure added to the spreadsheet would be 500. However, if the figure was a negative, the figure would be uploaded as -500.

If a liability account such as a loan had a balance of £300, it would also be added as a positive. So the figure added to the spreadsheet will be 300. If it was a positive liability, the figure added to the spreadsheet would be -300.

📌TIP: You can add switch rules in the chart of accounts to switch a negative asset to a liability.


Period headers format

You should not amend the period headers in row 5 of the Profit & Loss tab, or row 7 in the Balance Sheet tab. The periods are created automatically based on the information set in cells B2-B6 in the Profit and Loss tab.

For more information, visit our guide, Complete an Excel organisation import template.


Drop-downs format

Some data must be selected via drop-down lists and the data in the following cells or rows can’t be entered manually:

Profit & Loss tab

  • B3 - Start month

  • B4 - Period

  • B5 - Currency

Balance sheet tab

  • B1 - VAT scheme

  • B2 - VAT frequency

  • Column D - System accounts

Did this answer your question?