Build a Quotation Template in Excel

A quotation template in Excel lets you type a product code and a quantity, and have the item name, price, discount, tax and total fill in by themselves. You build it once with VLOOKUP and ROUND, then reuse it for every quote. This guide walks through each step for freelancers, small businesses and anyone who has to price work in a spreadsheet, including in a practical job test.

What the quotation template will calculate#

The finished quote does five jobs for you:

1. Looks up each item's description and unit price from a price list.
2. Works out each line total after any discount, rounded to cents.
3. Adds the lines into a subtotal.
4. Adds tax and shows the grand total.
5. Shows the date the quote is valid until.

You only type the customer, the codes, the quantities and any discounts. Everything else is a formula.

Step 1: Make a price list on its own sheet#

Keep prices on a separate sheet, so you change a price in one place and every new quote uses it. Rename a sheet to Prices (double-click the sheet tab) and enter:

Codes must be unique, because VLOOKUP stops at the first match. Codes are safer than item names, which are easy to mistype.

Step 2: Set up the quote header and valid-until date#

On a second sheet called Quote, add the details a customer expects at the top:

Excel stores dates as numbers, so =B4+30 returns the date 30 days later: 2 December 2026. If the result shows as a number like 46358, format the cell as Short Date on the Home tab. Type the quote date yourself rather than using TODAY(), because TODAY() changes every day and would change the date on old quotes when you reopen them.

Step 3: Pull item names and prices with VLOOKUP#

The item table starts at row 11 with the headers Code, Item, Unit price, Qty, Discount and Line total in columns A to F. You type the code in column A. In B12, look up the item name:

=IF($A12="","",VLOOKUP($A12,Prices!$A$2:$C$6,2,FALSE))

In C12, look up the unit price, which is column 3 of the price list:

=IF($A12="","",VLOOKUP($A12,Prices!$A$2:$C$6,3,FALSE))

Here is what each part does:

• IF($A12="","",...) leaves the cell empty when no code has been typed, so unused lines don't show #N/A.
• Prices!$A$2:$C$6 points at the price list on the other sheet. The $ signs lock it so it doesn't move when you fill down.
• FALSE asks for an exact match. Without it, VLOOKUP may return the price of the wrong item.

Fill both formulas down to row 21 to give the quote ten lines (Ctrl+D on Windows, ⌘D on a Mac). The VLOOKUP function guide explains every argument.

Step 4: Calculate line totals and discounts with ROUND#

Each line total is the price times the quantity, minus the discount. Type the discount as a percentage, such as 5%, in column E. In F12:

=IF($A12="","",ROUND(C12*D12*(1-E12),2))

(1-E12) turns a 5% discount into 0.95 of the price. ROUND with 2 keeps the result to cents. Here is a sample quote:

The install kit line is 42.75 × 3 × 0.95 = 121.8375. Without ROUND, the cell would show 121.84 but keep the extra digits, and the totals below could end up one cent different from what the customer adds up by hand. The ROUND function guide covers ROUNDUP and ROUNDDOWN too.

Step 5: Add the subtotal, tax and total#

Below the lines, three short formulas finish the quote. With a tax rate of 20% in E23:

SUM skips the empty text in unused lines, so blank rows don't cause errors. Keep the tax rate in its own cell so you can change it without editing the formula. If your quotes have no discounts or tax, simply leave those cells at zero.

Step 6: Make the template safe to reuse#

A template is only useful if it stays clean. A few finishing touches help:

• Protect the formulas. Select the cells you type into (codes, quantities, discounts, customer details) and unlock them under Format Cells > Protection. Then turn on Review > Protect Sheet. Now nobody can overwrite a formula by accident.
• Add a drop-down for codes. Use Data > Data Validation > List pointing at the Prices codes, so only valid codes can be typed.
• Set the print area. Select the quote and choose Page Layout > Print Area > Set Print Area, then fit it to one page.
• Save as a template. Choose File > Save As and pick Excel Template (.xltx). Opening it creates a new, unsaved copy each time.

A quote and an invoice share almost the same layout. When the customer accepts, you can turn the quote into an invoice built with formulas by adding a due date. The same lookup-and-round pattern appears in a payroll calculator, and pricing work like this is a common task in Excel skills for engineers.

Practise it#

Practise VLOOKUP in a live spreadsheet: you type the formula and it checks it instantly. Then practise rounding to get line totals exact, and try the exercise on this page, which prices one quote line with a discount.