How to prepare and import your own Excel or CSV accounts spreadsheet

If you have a standard vendor export, use the Xero export guide or QuickBooks export guide. The complete import guide explains filing lines, separate reports and common import problems.

1. Prepare your own spreadsheet

Start with your prepared account totals. This import does not turn a list of bank transactions, invoices or receipts into accounts. Put one account on each row and use names you understand, such as Sales, Rent or Business bank account.

Keep profit and loss and balance sheet figures on separate sheets or in separate files. A profit and loss report covers income and costs over the full accounting period. A balance sheet contains assets, liabilities and equity at the period end. If you already have a prepared trial balance with Debit and Credit columns, use that instead.

You can keep your existing column headings and layout. Choose the matching columns when importing. Import is most useful when you already have many account balances to group into the filing headings. With only a few totals, entering them directly in Financial data may be quicker.

Try a realistic Excel accounts example

Download the Excel accounts example. It contains 49 fictional accounts across two sheets, with income, costs, assets, liabilities and equity. The profit and loss includes 15 monthly columns and a YTD total for 1 April 2025 to 30 June 2026. The balance sheet includes the closing balances and a separate comparative column.

The example includes headings, subtotals, a percentage row, sales returns, a rent refund and accumulated depreciation. These show why you should check the selected amount column and mapping rather than adding every visible number. It is prepared account data, not a list of bank transactions.

Fictional example. Use it to practise in a test draft. Replace every account, amount and date before using a copy for your company. The invented account codes are identifiers, not Xero or QuickBooks codes. You do not need to add codes or types to your own spreadsheet.

Prefer CSV? Download the same calculated figures as a profit and loss CSV example and a balance sheet CSV example. Each CSV contains one report. The Excel version keeps both sheets and its formulas.

The minimum layout for a small spreadsheet

Put Account name in cell A1 and Amount in B1. Enter the accounts below them. For this layout, enter both income and costs as positive numbers. Choose Income and costs are positive when importing it.

Fictional example. These are example figures, not figures to use in your filing. Replace every example amount and account with your own prepared figures.

This three-row illustration explains the minimum layout. You can add more account rows. In this simple layout, list only the income and cost accounts. Keep your net profit calculation outside the rows you import.

A separate balance sheet spreadsheet

Use the same two headings for your prepared balance sheet. In the fictional example below, assets, liabilities and equity are positive amounts. Choose Each line uses its natural sign when importing this layout. Keep an actual negative balance negative, and review where it belongs.

This is a second minimum-layout illustration, separate from the downloadable workbook. Replace all example data with your own balances. Choose the appropriate loan category during mapping. Do not invent a retained earnings amount to make the balance sheet balance.

What you can leave out

Account code and Account type are optional. Leave both settings as Not included if your spreadsheet only has names and amounts. You do not need to create nominal codes or learn accounting software categories.

Use numbers in the amount cells, with pence if needed. Avoid merged cells, percentage columns and extra subtotal rows in a file you prepare yourself. You can keep an existing report layout, but check that subtotals are not included alongside their detail. If you already have monthly or comparative columns, select the total covering the filing period rather than adding all the columns together.

2. Save your Excel or CSV file

1. In Excel, open File, then Save As or Save a Copy.
2. Choose Excel Workbook (.xlsx) to keep the workbook and its sheets. You can import this file directly. There is no need to convert it to CSV.
3. If you prefer CSV, select the sheet you want to export, choose a CSV format and save it. CSV exports only the active sheet. Save the profit and loss and balance sheet separately if they are on different sheets.
4. If an amount uses a formula, make sure the cell shows its calculated number. If the importer cannot read it, make a copy and paste the calculated values before saving that copy.

The importer accepts CSV, TXT, XLS and XLSX, up to 1 MB, 2,000 rows and 200 columns per sheet. You can also copy the heading row and account rows from Excel and use Paste rows.

3. Import and choose columns

1. Open your filing, go to Financial data and select Import file. Choose or drop your Excel or CSV file.
2. On the review screen, select Change to open the report settings if they are not already visible.
3. Under Exported from, choose My spreadsheet or other software.
4. Choose Profit and loss, Balance sheet or Trial balance under Report type. For a workbook with multiple sheets, choose the relevant Workbook sheet.
5. Columns opens automatically. Use the matching settings below. Check that the preview shows the descriptions and amounts you expect.

Workbook sheet and Report type

Profit and loss first, then Balance sheet

Import one sheet at a time into the same draft. CSV files have no Workbook sheet selector.

Heading row

Row 6: Account code · Account description · Account type

Rows above it contain the report title and example period.

Account name

Account description (2)

Column B has the account names. Column A has codes, not names.

Account code and Account type

Account code (1) and Account type (3)

These columns exist in this example. Leave them Not included when absent from your own file.

Amount layout

One amount column

Choose one full-period column. Do not add monthly amounts and YTD together.

Profit and loss amount

YTD (19)

Column S covers all 15 months. Choose YTD under Amount column if prompted. The individual months are not additional import amounts.

Balance sheet amount

30 Jun 2026 (4)

Column D contains the closing balances. The 31 Mar 2025 column is a comparison, not an amount to add or import into this filing’s closing balances.

How signs are shown

Income and costs are positive (profit and loss) or Each line uses its natural sign (balance sheet)

Keep the negative returns, refund, accumulated depreciation and dividends paid as they appear. Check their combined filing line totals.

For practice, the workbook’s accounting period is 1 April 2025 to 30 June 2026. When importing your own report, select a total covering your own filing dates. Importing a column does not change the draft’s dates. This example does not include a charitable donation or capital allowance. If your own report updates an amount needing allocating between two returns, follow the Allocate actions after applying.

If you are using the small two-column illustrations instead, use these simpler settings:

Heading row

Row 1: Account name · Amount

The row containing the column headings.

Account name

Account name (1)

Column A contains Sales, Rent or the balance sheet account descriptions.

Account code and Account type

Not included

These examples do not have either optional column.

Amount layout

One amount column

There is one Amount column, rather than separate Debit and Credit columns.

Amount

Amount (2)

Column B contains the prepared figures.

How signs are shown

Income and costs are positive (profit and loss) or Each line uses its natural sign (balance sheet)

Use the convention described beside the example. If your own profit and loss shows negative costs, choose Income is positive, costs are negative instead.

For an existing trial balance with separate Debit and Credit columns, choose Debit and credit columns under Amount layout and select each named column. Keep the balances as they appear in your prepared trial balance. You do not need to turn them into the single-amount examples above.

Select Review mapping if the file step is still open. If you are already on Review, continue with the mapped accounts below.

4. Review the mapping

1. Check where every account will go. Amber rows need your choice. Select the proposed line to read its description and examples or choose a different line.
2. Select Apply change to assign and confirm a line. Assign saves the choice in amber for later confirmation. Select Confirm when the proposal agrees with your prepared accounts. Use Not used for a row that should not contribute.
3. If you see Review changes, open it and choose Keep current, Replace or Clear for existing filing figures.
4. Select Use these figures to fill the financial fields. Choosing the file or a column does not apply the figures to your filing.
5. If you imported profit and loss first, repeat the process with the balance sheet sheet or file in the same draft. Review any changes before applying the second report.

You can search accounts and select several rows for a bulk move. Select Use this total when an existing report contains no detail, or Split between two lines when one account contains two categories. Enter amounts that add back to the account total. Read the filing line descriptions and complete mapping guide if you need help choosing a line.

For a long accounting period, apply the full-period figures first. The notice lists amounts still needing allocating between the two Corporation Tax returns. Select Allocate beside each amount to open its existing controls. Your dates, figures and allocation stay under your control.

5. Check the result

For the downloadable example, Turnover is £141,600, Cost of raw materials and consumables is £42,600, Staff costs are £30,600, Other charges are £24,750, Depreciation is £2,400 and interest paid is £300. Profit before tax is £40,950. The report’s subtotals and percentage row should not appear as additional accounts.

After importing its balance sheet, total assets are £53,970, liabilities are £14,520 and equity is £39,450, giving a £0 difference. Fixed assets are £9,000 after accumulated depreciation. Prepayments are £750 on their own line. Retained earnings are £38,450, from £7,500 brought forward plus the example profit of £40,950 less £10,000 dividends paid. Confirm the dividends paid proposal as a reduction of retained earnings for this fictional example.

The small two-column illustrations have different figures. Their profit before tax is £9,900 and balance sheet equity is £3,500. For your own filing, compare the imported totals with your prepared accounts rather than with either example.

Review the profit before tax, net assets and balance-sheet difference before applying. Then check the Financial data fields, generated accounts and tax computation. Fix the source report or mapping if the figures do not match your prepared accounts.

Official sources

• Microsoft: Import or export text and CSV files
• Microsoft: File formats supported in Excel