How to Automate Your Quoting Process in Excel (Step-by-Step)

Let’s be honest — most businesses start quoting in Excel. And for good reason. It’s already on every computer, everyone sort of knows how to use it, and you can get something functional built in an afternoon.

This guide will walk you through building a genuinely useful quoting spreadsheet. No fluff, just the formulas and techniques that actually work. We’ll also talk about where this approach breaks down, because it will eventually — and you should know when that’s coming.

Step 1: Set Up a Product/Pricing Table

Before you write a single formula, you need a clean pricing reference. Create a new sheet called Products with these columns:

• Column A: Product code (e.g., WDG-001)
• Column B: Product name
• Column C: Unit price
• Column D: Unit (each, metre, sqm, hour)

Keep this table tight. No blank rows, no merged cells, no formatting tricks. This is your single source of truth for pricing.

Now select your entire product table (including headers), go to Formulas > Define Name, and call it ProductTable. Named ranges make every formula you write from here on dramatically easier to read and maintain.

Step 2: Build the Quote Sheet with VLOOKUP

On your main Quote sheet, set up columns for product code, description, quantity, unit price, and line total. In the product code column, you’ll type codes manually for now — we’ll fix that in the next step.

For the description cell (let’s say B10), use:

=VLOOKUP(A10, ProductTable, 2, FALSE)

For the unit price (D10):

=VLOOKUP(A10, ProductTable, 3, FALSE)

And the line total (E10) is simply:

=C10 * D10

The FALSE parameter is critical — it forces an exact match. Without it, Excel will do an approximate match, and you’ll get wrong prices with zero warning. That’s the kind of silent error that costs real money.

Step 3: Add Data Validation Dropdowns

Typing product codes manually is error-prone. Instead, use data validation to create a dropdown list.

1. Select the cells in your product code column
2. Go to Data > Validation
3. Under “Allow,” choose List
4. For the source, enter =INDIRECT("ProductTable[Product Code]") or simply reference your product code column range

Now your team picks from a dropdown instead of typing codes from memory. This one change eliminates the most common quoting error — wrong product codes pulling wrong prices.

Step 4: Add Markup, Discounts, and Tax Logic

Most businesses don’t just sell at list price. You’ll need cells for:

• Markup percentage — a single cell (say G3) where you enter your margin
• Discount — another cell (G4) for customer-specific discounts
• Tax rate — cell G5, defaulting to your standard rate

Your subtotal formula sums the line totals. Then:

=Subtotal * (1 + G3) * (1 - G4)

And the final total:

=AdjustedSubtotal * (1 + G5)

Name these cells too — MarkupRate, DiscountRate, TaxRate. Your formulas will read like English instead of cell-reference soup.

Step 5: Lock It Down with Sheet Protection

This is where most people skip a step and regret it later. Protect your formula cells:

1. Select the cells where users need to type (quantity, product code, customer name, etc.)
2. Right-click > Format Cells > Protection > Uncheck “Locked”
3. Then go to Review > Protect Sheet

Now your team can use the quote sheet without accidentally overwriting a VLOOKUP with the number 42.

When Excel Starts Fighting You

Here’s the part where I’m going to be straight with you. The system you just built works. It’ll handle quoting for a small team doing straightforward pricing. But there are real limits, and you’ll hit them faster than you think.

Version control disappears. The moment two people are editing different copies of the same quote, you’ve got a problem. Which version has the latest pricing? Who approved what? There’s no audit trail in a spreadsheet.

Complex pricing breaks VLOOKUP. If your pricing depends on quantity tiers, customer agreements, job-specific conditions, or bundled packages, you’ll end up with nested IFs inside VLOOKUPs inside INDEX-MATCHes. It becomes fragile. One wrong edit and the whole thing silently miscalculates.

It doesn’t connect to anything. Your CRM doesn’t know a quote was sent. Your accounting software doesn’t know it was accepted. Your project management tool doesn’t know the job scope. Everything requires manual re-entry.

Make the Most of What You’ve Built

If you’re not at the breaking point yet, here are a few more things to squeeze out of your Excel quoting setup:

• Conditional formatting to highlight quotes over a certain value or margins below a threshold
• A dashboard sheet that summarises open quotes, win rates, and average deal size using COUNTIFS and SUMIFS
• A template system — save your blank quote as an Excel template (.xltx) so every new quote starts clean

Excel is a brilliant tool. It just wasn’t designed to be a quoting platform, a CRM, and a reporting engine all at once. Use it well for as long as it works, and know what to look for when it’s time to move on.