How to Calculate VAT from Gross Amount in Excel (2 Examples)

Method 1 – Calculate the Included VAT of 20% from the Gross Amount

STEPS:

• Select the cell where you want to put the formula to calculate VAT from the gross amount. We selected cell E5.
• Enter the formula below into that selected cell:

• Press Enter to see the result in that selected cell.

• The result will appear in the selected cell, and the formula will appear in the formula bar.
• To copy the formula over the range, drag the Fill Handle down. Or, double-click on the plus (+) sign to AutoFill the range.

• The VAT from the gross amount is calculated in the resulting cell.

Read More: How to Calculate 15% VAT in Excel

Method 2 – Compute 5% of VAT Included from Gross Amount in Excel

STEPS:

• Choose the cell where you want to see the VAT calculation from the gross amount. In this case, we choose cell E5.
• Enter the formula below into that cell:

• Press Enter to complete the process.

• You can analyze the data in the selected cell and the formula in the formula bar.
• Drag the Fill Handle down to duplicate the formula over the range. Or, to AutoFill the range, double-click on the plus (+) symbol.

• Column E shows the result of the VAT from the gross amount.

Calculate Net Amount Without VAT of 20% from Gross in Excel

STEPS:

• To compute the net amount without VAT, choose the cell into which the formula will be typed. We used cell E5.
• Enter the formula below into the selected cell:

• Press the Enter key to finish the procedure.

• The result, as well as the formula in the formula bar, will now appear in the selected cell.
• To copy the formula across the range, drag the Fill Handle down. Alternatively, double-click the plus (+) sign to AutoFill the range.

• Column E shows the net amount for each product.

Calculate the Gross Amount with VAT of 20% in Excel

STEPS:

• To calculate the gross amount with VAT, select the cell where the formula will be entered. We chose cell E5 as our selection.
• Enter in the formula below:

• Press Enter.

• The result will now be displayed in the selected cell, and the formula will be in the formula bar.
• Drag the Fill Handle down to duplicate the formula across the range. Alternatively, to AutoFill the range, double-click the plus (+) symbol.

• You can see the gross amount result in column E.

Download the Practice Workbook

You can download the workbook to practice.

Related Articles

• How to Remove VAT Using Excel Formula

<< Go Back to Vat Formula In Excel | Excel Formulas for Finance | Excel for Finance | Learn Excel