Free lesson · Excel for small business · about 35 minutes

A quote that does its own maths

You will build a quote table with totals, discount and VAT using only formulas — no manual calculation at all.

Materials language: Bulgarian. The software menus are in English, and the lessons show where everything is.

You need

The exercise is designed for the software's free plan.

The columns
In row 1 type: A — Service, B — Quantity, C — Unit price, D — Amount. Fill in 4 service rows (rows 2–5). Select A1:D5 and press Ctrl+T to turn it into a “Table”.
Amount per row
In D2 enter the formula. The table copies it down by itself.
```
=ROUND([@Quantity]*[@[Unit price]],2)
```
Discount, VAT and total
Below the table: F2 “Before discount”, F3 “Discount”, F4 “Before VAT”, F5 “VAT 20%”, F6 “Total”. The discount is 5% when the sum is over 500 €.
```
G2: =SUM(Table1[Amount])
G3: =IF(G2>500,-ROUND(G2*5%,2),0)
G4: =G2+G3
G5: =ROUND(G4*20%,2)
G6: =G4+G5
```
Money format
Select C2:D5 and G2:G6 → Format Cells → Currency, 2 decimals, € symbol.

Enter: Cleaning — 2 × 180 €; Windows — 10 × 4 €; Carpets — 3 × 25 €; Transport — 1 × 30 €.

Then change the cleaning quantity to 1 and watch the discount disappear.

Before discount 505.00 €; discount −25.25 €; before VAT 479.75 €; VAT 95.95 €; total 575.70 €
With quantity 1: 325.00 €, no discount, total 390.00 €

Liked the lesson? See the whole course.