Importing from Microsoft Excel

The bridging software captures the details from a worksheet confirming to an 'Excel (Standard Format)' as defined by below. During the import process, you will be prompted to locate your Excel workbook. Then the workbook will be opened and each worksheet inspected in turn. The bridging software will attempt to find the VAT Return details from Cells 'B1 to 'B13'.

The following section describes the detailed technical requirements for importing from a Microsoft Excel workbook, using the NJT standard format.

One of the main principles of Making Tax Digital is that any transfer of data must be undertaken digitally. To facilitate businesses that use Excel based Accounting systems, a function is provided to allow preprared VAT returns to be imported, via an Excel workbook, that contains a worksheet conforming to a 'Standard Format' as defined by NJT. The pre-requisite being that businesses are able populate the worksheet as per the following example.

Example: 'My VAT Return.xlsx'

1. Example Workbook Download:

[Click Here] to download the example Excel (Standard Format) workbook.

2. Workbook and Worksheet Definition:

• Workbook: The workbook can be saved as an Excel '.XLS', '.XLSX', '.XLSM' or '.XLSB' format. The name of the workbook can be any name you wish, for example: 'My VAT Return.xlsx'
• Worksheet: You can choose any name for the worksheet that contains the VAT Return details. For example 'MTD-VAT' or 'MTD Qtr1'.
  Note: It is normal to expect that other worksheets already exist within the workbook, that contain your transactions and formulas that contain you Box totals.
• Workbooks Containing Multiple VAT Returns: To cater for workbooks that contain details of multiple VAT Returns, for example, an annual workbook containing the 4 quarterly VAT Returns or the 12 Monthly VAT Returns, you can add multiple individual worksheets named for example 'MTD-VAT1', 'MTD-VAT2 or 'MTD Qtr1' etc. The software will inspected each of these worksheets and attempt to match the Period Start and End Dates to the Obligated VAT Return.

3. 'MTD-VAT' Worksheet Cells Definition:

• Cell B1: contains the Taxpayer or Trading Name
• Cell B2: contains the VAT Registration Number
• Cell B3: contains the Period Start Date. Format: DD/MM/YYYY
• Cell B4: contains the Period End Date. Format: DD/MM/YYYY
• Cells B5 to B13: contain the values for Boxes 1 to 9. (Box 1 to 5 values should contain no more than 2 decimal places, Box 6 to 9 values should contain no decimal places, i.e. whole numbers only).

4. Example of a single VAT Return workbook:

Assuming you already use an Excel workbook to calculate your VAT Return. This may include manual adjustments etc. This workbook would already contain other worksheets that hold your adjustments details. It is assumed that you already have formula based cells that contain your 'Nine Box' totals for the VAT Return.

Steps for making the workbook compatible with the MTD software

1. Add a new worksheet within the existing workbook and set the worksheet name to 'MTD-VAT'.
2. If you wish, populate the cells A1 to A13 as defined in the example screenshot. Note: This is for your information only, and is not used by the MTD software.
3. Either type your 'Taxpayer or Trading Name' in Cell B1 or enter a formula linking it to your 'Business Name' already present in a cell elsewhere in the workbook.
4. Either type your 'VAT Registration No.' in Cell B2 or enter a formula linking it to your 'VAT No.' already present in a cell elsewhere in the workbook.
5. Either type your 'From Date.' in Cell B3 or enter a formula linking it to your 'From Date' already present in a cell elsewhere in the workbook.
6. Either type your 'To Date.' in Cell B4 or enter a formula linking it to your 'To Date' already present in a cell elsewhere in the workbook.
7. In cells B5 to B13, enter a formula linking the cells to the existing totals already created elsewhere in the workbook.

You may wish to create an Excel template or an empty workbook containing all the layouts and formulas. Alternatively, after you have completed and saved the current VAT Return workbook, you may choose to 'Save As' the workbook, giving it a different name in preparation for your next VAT Return. The objective being that you only create the formulas once.

5. Example on how to create a 'Local Link' to another Worksheet within the same Workbook:

Scenario: Let's say you have an existing workbook that contains a worksheet called 'Trans'. Worksheet 'Trans' contains your transactions including the calculated totals for 'Boxes 1 to 9'. The 'Box 1' total value is stored within the cell 'E20'. 'Box 2' total in the cell 'F20' etc.
Also you have added the New worksheet called 'MTD-VAT' and you now wish to create a formula in cell 'B5' that points to the 'Box 1' total in worksheet 'Trans'.

1. Click in the cell 'B5' within the 'MTD-VAT' worksheet.
2. Type "=" (an equals sign without the quotes).
3. Click on the 'Trans' worksheet, then click in the cell 'E20'.
4. Then press the 'Enter' key.
5. This will create a formula in cell 'B5' that links to the cell 'E20' within worksheet 'Trans'
   The contents of cell 'B5' would now be =Trans!E20.
6. You can then repeat the above steps for the remaining cells.

6. Example on how to create an 'External Link' to a Worksheet within a separate Workbook:

Scenario: Let's say you have two workbooks called 'Inputs.xlsx' & 'Outputs.xlsx', that each contain a worksheet called 'InputTrans' & 'OutputTrans' respectively. Worksheet 'OutputTrans' within workbook 'Outputs.xlsx' contains your sales transactions, including the calculated total for 'Box 1'. The 'Box 1' total value is stored within the cell 'E20'.
Also you have created a new workbook called 'My VAT Return.xlsx' and added the New worksheet called 'MTD-VAT'. You now wish to create a formula in cell 'B5' that points to the 'Box 1' total in worksheet 'OutputTrans'.

1. Open Workbooks 'Outputs.xlsx', 'Inputs.xlsx', then open 'My VAT Return.xlsx'.
2. Within the workbook 'My VAT Return.xlsx', click in the cell 'B5' within the 'MTD-VAT' worksheet.
3. Type "=" (an equals sign without the quotes).
4. Click on the workbook 'Outputs,xlsx', then click on the 'OutputTrans' worksheet.
5. Click in the cell 'E20' then press the 'Enter' key.
6. This will create a formula in cell 'B5' that links to the cell 'E20' within external worksheet 'OutputTrans'
   The contents of cell 'B5' would now be ='[Outputs.xlsx]OutputTrans'!$E$20.
7. You can then repeat the above steps for the remaining cells, creating external links to the workbooks 'Inputs' & 'Outputs' respectively.

Note: If you subsequently open the workbook 'My VAT Return.xlsx' without first opening the linked workbooks, you will be prompted to update the links, in which case you would open the linked workbooks then click on the 'Update' button.
If you do not wish to change the links you can click on the "Don't Update" button. For information, the links will be expanded to include the full folder path.
For example ='C:\My Folder\[Outputs.xlsx]OutputTrans'!$E$20.