How to build a Quote Generator in Microsoft Excel

When our experts are approached to build a quote generator,they look at all aspects of the design and will adopt an approach to suit thevolume and complexity of quote being produced. The most common questions thatneed addressing to build a quoting tool are summarised below:

How do I calculate prices in an Excel quoting tool?

Put your current pricing table in your Excel file, ensuring each line contains a unique product name on the left, and a price. Ensure users can only select available products by giving them a data validation dropdown list. To retrieve the price for a product selected in cell A10, use a XLOOKUP formula of form ‘=XLOOKUP(A10,PricingTable[Item],PricingTable[Price])’ where you reference your pricing table by name, and the two columns containing the item names and prices by their headings. The formula will continue to display results if you subsequently change the column headings or extend the pricing table.

Most quotes include a quantity column allowing calculation of an extended price. If that’s in B10, and the price lookup is in C10, then the extended price in D10 could read ‘=B10*C10’. If up to 20 items can be added in rows 10-29 of the spreadsheet, the price can be subtotalled in D30 e.g. ‘=SUM(D10:D29)’ with applicable sales taxes or adjustments made below.

You will want to protect the formulas on the worksheet which poses a problem if users need to be able to enter custom products with tailored pricing. One option is to create a section at the bottom of the quote where line items can be priced manually. If this looks untidy or doesn’t allow items to appear in a logical order, our consultants can install macros in your file to lock/unlock cells and add/remove lookup formulas depending on the products entered.

The above also assumes you can use a single quantity column. If you are pricing up a variety of materials by length or area, you will require a unit of measure column and may need to enter separate dimensions to calculate areas. One neat method is to give users pop-up forms that let them specify the length as 3’ and width as 2’, before placing it into a single column as 3’x2’. The number of square feet (6) gets taken forwards into other calculations. A less tidy alternative can be to replace a QTY column with length/width/height columns and just use the first column for pure quantities.

The other common complication is if you want to incorporate pricing factors into each line item of your quote. If you want to apply a common uplift or discount based on a customer’s identity, this multiplier can be entered or looked up elsewhere in the file, and incorporated into the pricing calculation e.g. ‘= <standard price> * (1 + <customer % uplift/discount>)’. If you provide volume discounts on an item-by-item basis then the pricing table needs to define the volume thresholds (by total price or quantity) and prices applicable by item. This may not be possible in a single pricing table. At Excel4Business, we can use VBA macros to replicate the logic required to apply different pricing rules to different products.

How can I search available products in a quote tool?

If users need to select from hundreds of products, you can make their life easier by splitting products into categories. If 400 products split evenly between 20 categories, a user can first select from a list of 20 categories, and then from a list of 20 products. Even if they don’t split evenly, reducing the largest product category to a list of 50 products makes your data more navigable. From an aesthetic perspective, having a separate column for product category alongside product names can help your customers distinguish big ticket items from accessories required for installation.

This method of categorising products requires the dropdown list for the product name to update dynamically when a user changes the product category. When selecting the source data for a data validation list, you can enter a formula. This formula can reference the contents of the adjacent category cell. Depending on your version of Excel and the exact architecture, you can use either the FILTER or INDIRECT functions to dynamically update your lists.

However, our experts dislike this approach because all solutions share the underlying issue that users can change the product category after selecting a product. The only way to ensure selected products match the selected categories is to use code that verifies the selected product whenever the category is changed. That code can be easily extended to update the dropdown list in the product name column.

If you are using macros, it may be worth considering a fully fledged search function. That means users can choose whether to look for products by an item’s code or SKU, or by entering part of an item’s description. At Excel4Business, we can make pop-up boxes appear whenever users click on a cell using the Worksheet_SelectionChange event.

One further option with a coded solution is to let users add product packages to a quote. If a fully assembled product consists of ten different components, users can select a fully assembled product, Visual Basic can retrieve the bill of materials, and then produce a fully itemised quote. That can offer significant time savings in the estimating process and reduce the scope for user error.

Can clients generate their own quotes in Excel?

If you want clients to select products and enter quantities in a basic list then you can do that by sharing an online Excel spreadsheet. However, it is unlikely to integrate as well with other internal applications as a basic e-commerce site where users can have a far richer experience. A quote generator with extensive Visual Basic macros can guide a client through a more complex quoting process but will add friction to the sales process as users will need to enable content at their end.

A common use case for an Excel quote generator is to ease product selection and ensure all the correct options are considered. Once the end-user is familiar with the file and process, this can offer significant time savings that dwarf the inconvenience of enabling macros on first use. Getting a regular customer or reseller to generate their own quotes using your Excel template can then make sense.

If you plan to offer your quote tool to external users, you will need to ensure prices are kept up-to-date. The most common option is to include an expiry date in the file. The simplest option is to use conditional formatting to display red text at the top of each quote when the file has expired. This has the benefit that users can still compile a quote on the understanding that current prices may differ. The alternative is to build a hard lock into the file using code. This approach relies on users enabling macros so only works where the file relies on VBA to generate a quote in the first place.

One option to consider is whether your quoting tool could remain up-to-date by connecting to and querying an SQL pricing database over the internet. If your prices change daily in response to currency or commodity fluctuations, it is worth considering. If the price list only changes a couple of times a year, it is better to give the file an expiry date as you may wish to change other aspects of your quotes such as the length of quote validity, or your terms and conditions.

At Excel4Business, our consultants can advise on whether it’s worth investing in the process of building a quote tool for client use, and the best method for transmitting changes to clients.

Can generated quotes contain unique references?

Yes, if the quote creation process requires users to click a button or open a file to formally create a quote, that trigger represents an opportunity to add a unique quote reference. This can be an incremental number, it can be a timestamp, it can also link to a client or salesperson’s identity. It can be added as a reference at the top of a quote, and in a file name.

The most important consideration is whether a quote reference should change when a quote is revised. If the reference needs to change every time the quote is changed then you will need to save and catalogue a new Excel file for each amendment. If you want to create 10 close variants of every quote, you will have to dedicate a lot of time to your folder structure and you may be better served by specialist CRM software. Our experts find an Excel solution works best when revisions are the exception not the rule, and can be identified in the file name through a date or timestamp. Then quotes can be assigned a single reference.

The next question is when references should be assigned. The ideal solution would be to build a quote and assign a reference as the final step in the process as that prevents numbers being skipped due to quotes being discarded. If multiple people are using copies of the same quote generator at the same time, this can pose a problem because both files may try to use the same quote reference. One solution is for quote references to include client IDs and dates/timestamps. This works if different salespeople are responsible for different clients.

There are plenty of options such as retrieving the next quote reference from a separate database but the most popular is to assign references at the start of the quoting process. If that means some quote references end up discarded then it rarely causes issues. The other benefit to assigning references at the start of the process is that salespeople can open up a template and add some basic details to customise their quote in a separate file immediately. This lets users save their quote as they go whilst removing the risk of them saving over the blank quote template.

All the above assumes the use of Visual Basic code, or Office Scripts, as you only want to assign a quote reference on request.

How can I store quote details produced in Excel in my CRM?

If it was appropriate to generate quotes in Excel, there is nothing wrong with transferring quote details to your CRM software manually. Excel is best used for generating custom quotes that are heavy on detail where bespoke rules can be integrated into the process. These are cases where the time required to prepare quotes far exceeds the time required to enter a quote reference, issue date, and value into a CRM.

An Excel quoting tool can speed up the manual process. It can open a web browser containing your CRM and, depending on the CRM and the data being received by the Excel file, it may even be able to direct you to a relevant page. It can also copy useful details to the clipboard. For instance, the Excel file will know where it or any generated PDF copies of the quote have been saved. That means it can copy a link to where the original quote lives on your server, ready to paste into a relevant field of your CRM.

If you need the time savings from sending quote details live from Excel to your CRM, there generally will be a solution. That’s because CRM software tends to allow for the upload of data in text formats such as CSV files. Excel can generate those files automatically, then trigger the CRM upload using a tool like Power Automate.

As an Excel consultancy, we would traditionally have been wary of working with unfamiliar CRMs as we are unable to promise a satisfactory outcome for a fixed price. However, with AI support in sifting through documentation, it is now a lot faster to investigate different methods of writing to CRMs so we may be able to run some proof-of-concept tests as part of developing your quote tool.

How can I auto-mail the final quote?

An Excel quote can be saved as a PDF and attached to an e-mail using Visual Basic. This process can be triggered by clicking a button in an Excel spreadsheet. The PDF can be sent directly to a named recipient through your mail client. If you use the traditional desktop version of Microsoft Outlook, your macros can access all the features of Outlook, giving you the option to view and edit your quote message before sending.

A quote PDF will typically be printed from the worksheet of your Excel file that contains the quote. You should configure the quote page for printing such that it prints to one page wide and so any headers repeat across all pages of the document. The code required to print a PDF then takes the form Sheet1.ExportAsFixedFormat Type:=xlTypePDF, Filename:="your folder/subfolder/quote.pdf". The filename can be adapted to include a quote reference and a folder chosen by the user.

Alternatives to sending a PDF include mailing an Excel file and embedding the quote in the body of an e-mail. If you wish to send an Excel file, you may want to copy the quote sheet to a new workbook, remove any formulas and save that separately so as not to send any internal pricing information to the prospective customer. If you wish to embed the quote in the body of an e-mail and wish to include a table, your e-mail will have to be written in HTML as opposed to plain text.

If you plan to send the e-mail through Outlook, our experts can write code that ensures the mail client is open, selects the account from which to send the message, fills in the different fields (such as to/cc), and attaches the quote. Note our macro can merge quote details into the body of the e-mail, so a template message of ‘Hi <<FirstName>>, please find attached quote <<reference>> that expires on the <<expirydate>>’ can become ‘Hi John, please find attached quote JOHN-001 that expires on the 31st December’ in the final e-mail.

At the end of the process, we can choose between displaying and sending the final quote. As a quote generator typically only prepares a single e-mail, it’s generally worth letting a user hit Send manually as they can personalise the e-mail and double-check everything looks right.

By Ed Bolton, founder of Excel4Business Ltd. Last reviewed November 2026.