Quote template Excel with working formulas

Quotation format in Excel with formula: the cell map

This quote template for Excel does the arithmetic for you. Each line multiplies quantity by rate, an Include column keeps optional items out of the total, and a summary block works out discount, sales tax, deposit and balance. Below is a cell by cell map of the formulas, how to set your tax rate, and how to add rows so the quotation format in Excel with formula keeps adding up. If you searched for a quotation template Excel file or an estimate template Excel version, it is the same workbook: change the title at the top and every formula keeps working.

The sheet splits into what you type and what it works out. You enter item names, quantities, units and rates in columns A to E, plus Y or N in column G. Column F and the summary cells beneath it are formulas. The references below come from the plumbing sample as it arrives, and they shift down when you add rows.

Rows 1 to 10 hold your business, the quote number, dates, the customer and the scope; A10 is merged across to G10 so the scope can wrap over two lines. Gridlines are switched off and printing is set to one page wide on US Letter.

How the Include column keeps optional items out of the total

Each group subtotal uses SUMIF, which adds an amount only when the cell beside it in column G says Y. That lets optional work sit on the quote at full price, visible to the customer, while the total ignores it until they agree. Changing a single letter from N to Y brings the line into every total below.

Read =SUMIF(G14:G15,”Y”,F14:F15) as: look down G14 to G15, and wherever a cell holds Y, add the matching amount from F14 to F15. Everything downstream (subtotal, discount, tax, total, deposit and balance) follows automatically.

Setting the tax rate, discount and deposit

Type plain numbers in the three percent cells: 8.25 for an 8.25% tax rate, 10 for a 10% discount, 25 for a 25% deposit. The formulas divide by 100 themselves. If you type 8.25% with the percent sign, Excel stores 0.0825 and the tax comes out a hundred times too small, which is easy to miss on a busy day.

• Sales tax rate %. The US file arrives at 0 because sales tax depends on your state and on what you sell. Enter your own rate, or leave 0 for work that is not taxable.
• Discount %. Taken off the subtotal before tax. Leave it at 0 if you never discount, and the Discount line shows $0.00.
• Deposit %. Calculated on the total including tax. At 0, the deposit reads $0.00 and the balance equals the total.
• Other countries. Rename the label to GST or VAT and type that rate. The formula ignores what the label says.

Adding rows without breaking the totals

Insert new lines inside a group, above its last item, and Excel stretches the SUMIF ranges to include them. Insert a row directly above the subtotal instead and the new amount lands outside the range: it shows on the page but never reaches the total. The line looks perfectly normal, which is why the mistake slips through.

1. Copy a full item rowRight-click the row number of an existing line in the group and choose Copy.
2. Insert it inside the groupRight-click the row number of the last item and choose Insert Copied Cells. The copy lands above it with the Amount formula, the Y or N dropdown and the border.
3. Type over the copyEnter the new item, detail, qty, unit and rate. Column F works itself out.
4. Check the rangeClick the group subtotal and read the formula bar. Both SUMIF ranges should run from the first item to the last.

For a whole new group, copy a complete block (heading, items and subtotal), insert it, then add its subtotal cell to the Subtotal formula, for example =F16+F22+F28. Formulas, Show Formulas displays every formula at once, and Trace Precedents draws arrows from a total back to the cells it uses.

Worked example: the plumbing quote with tax and a deposit

Here is the sample water heater job as the sheet calculates it, then with an example 8% sales tax and a 25% deposit typed into the percent cells. Both rates are examples only. The point is to see what each formula should return, so you can check your own sheet the same way.

Which parts of a job are taxable varies by state. If yours taxes materials but not labor, point the tax formula at the Materials subtotal instead, for example =ROUND(F16*F27/100,2).

Locking the formulas and saving a PDF from Excel

Protect the formula cells before you reuse the sheet, so a stray keystroke cannot overwrite a total. Then send the customer a PDF rather than the .xlsx: a PDF cannot be edited, opens on any phone, and does not expose your formulas or any cost notes you keep in spare columns.

• Select the input cells (items, quantities, units, rates, Include? and the three percent cells), press Ctrl+1, and untick Locked on the Protection tab.
• Choose Review, Protect Sheet. A blank password is enough if you only want to stop accidents.
• Page Layout, Size switches between Letter and A4. The sheet already fits one page wide.
• On Windows use File, Save As, PDF, or File, Export, Create PDF/XPS. On a Mac, File, Save As, then pick PDF as the file format.

Using it as an estimate template in Excel

Change the word Quote in F1 to Estimate and add a low and high figure under the total, and the same workbook becomes an estimate template in Excel. The customer then sees a likely price band instead of one fixed number, which suits repairs where the full extent only shows once the work is opened up.

Insert two rows below Balance on completion. Label E33 Low estimate and type =F29 in F33, then label E34 High estimate and type =ROUND(F29*1.15,2) in F34 for a 15% allowance. On the sample, before tax, that reads $1,795.00 to $2,064.25. The online estimate maker prints a range like this with no formulas at all, and the guide to writing an estimate covers the wording.