Yes. In Microsoft Excel for Microsoft 365 on Windows, make a quote template with a product drop-down by storing product names on a separate worksheet, defining a name for that list, and applying Data Validation to the quote’s item cells. You can then use lookup formulas to fill in descriptions and prices and formulas to calculate line totals and the quote total.
What the drop-down should control
A drop-down is useful when people repeatedly choose from a known set of items, services, or pricing options. It limits spelling variations such as “Site survey” and “Site Survey,” which can otherwise make quotes harder to compare and analyze. It also makes a template quicker to fill out, especially when the same products appear on many quotes.
Start by deciding what belongs in the list. A small service business might offer a site survey, installation, and travel charge; a product seller might list stock items or service plans. Keep the selectable labels short and consistent. Put longer explanations in a separate description column rather than making each drop-down entry unwieldy.
A useful quote layout includes customer and quote details at the top, followed by columns for item, description, quantity, unit price, and line total. Leave space for a subtotal, any applicable tax or discount, a final total, and terms such as the quote’s expiry date. A quote is not automatically an invoice: label the document clearly and describe what is included, what is excluded, and when the price may change.
Build the workbook and product list
Create two worksheets, for example Quote and Lists. On Lists, put a header in A1 and product names in A2:A100. Add descriptions in column B and unit prices in column C. For example, the rows might contain Site survey / On-site assessment / 120, Installation / Standard installation / 250, and Travel / Travel charge / 35. Use actual prices and units appropriate to your business; these figures are only an illustration.
On Quote, reserve cells for the customer, quote number, date, and any rate or discount you intend to show. Put the line-item headings in row 9 and use rows 10 through 24 for up to 15 items. One practical column arrangement is A for item, B for description, C for quantity, D for unit price, and E for line total. Format prices as currency and quantities as numbers. Leave a few blank rows for smaller quotes rather than making the form look crowded.
Create a drop-down with Data Validation
Excel’s list validation works best here when the source is a defined name. On Windows, open Formulas > Name Manager > New. Name the range ProductList and set Refers to to =Lists!$A$2:$A$100. Choose a name with no spaces. The range includes room for future entries, but entries should be contiguous: avoid blank cells between product names.
Next, select the item cells on Quote, such as A10:A24. Choose Data > Data Validation. On the Settings tab, set Allow to List and enter =ProductList in Source. Keep In-cell dropdown selected, then confirm. Selecting one of those cells should show an arrow that opens the available choices. You can also set an input message or an error alert to guide people who try to type an item that is not on the list.
On Excel for Mac, use the Data tab’s Data Validation command; the dialog labels and placement can differ slightly by release. Excel for the web can work with many data-validation lists, but if a workbook’s defined-name source or validation settings are difficult to edit there, set them up in the desktop app. The list and named range are stored in the workbook, so recipients can use them when they open the file in compatible Excel versions.
Fill descriptions and prices automatically
With the product name selected, a lookup can return the matching description and price from Lists. These examples assume the list has product names in Lists!A2:A4, descriptions in Lists!B2:B4, and prices in Lists!C2:C4. In Quote!B10, enter the formula below, then copy it down the description column. If A10 contains Site survey, it returns On-site assessment; if A10 is blank, it returns a blank.
=IF(A10="","",XLOOKUP(A10,Lists!$A$2:$A$4,Lists!$B$2:$B$4,""))
For the unit price, use this in Quote!D10 and copy down. With Site survey in A10 and 120 in Lists!C2, the result is 120. XLOOKUP is available in Microsoft 365 and Excel 2021 or later; the formula returns a blank if the item cell is empty or no match is found.
=IF(A10="","",XLOOKUP(A10,Lists!$A$2:$A$4,Lists!$C$2:$C$4,""))
If your list has more products, extend each lookup range to cover them. The formula does not automatically make a fixed A2:A4 range larger when you add a new product. You can also use the same item list for all quote rows, so users select an item in A11, A12, and so on while the corresponding formulas use their own row numbers.
Calculate line totals and the quote total
Assuming Quote!C10 contains quantity and Quote!D10 contains unit price, enter this in E10 and copy it down through E24. If the quantity is 2 and the unit price is 250, the line total is 500. The IF condition keeps unused rows visually blank rather than displaying a zero.
=IF(OR(A10="",C10=""),"",C10*D10)
Assuming the line totals are in E10:E24, place this formula in the subtotal cell, such as E25. If the quote includes one site survey at 120 and two installations at 250 each, the subtotal is 120 + 500 = 620. SUM ignores the blank strings returned by the line formulas.
=SUM(E10:E24)
If you choose to show an illustrative tax rate in B4, format B4 as a percentage and enter the rate as a percentage value. Assuming the subtotal is E25, the rate is B4, and the final total goes in E27, this formula calculates the total including that rate. At a sample rate of 8%, a subtotal of 620 produces 669.60. The applicable tax rules and rate depend on the real transaction; verify them with an appropriate tax professional rather than treating this example as tax advice.
=E25*(1+B4)
Make the template practical to reuse
Save a clean master workbook before creating customer quotes. For each new quote, use Save As to make a separate copy, then update customer details and line items. This reduces the chance of overwriting the original template or accidentally carrying one customer’s information into another customer’s quote.
- Add a quote number, issue date, expiry date, customer contact details, and the currency used.
- State whether prices include or exclude tax, and identify delivery, travel, installation, or other charges clearly.
- Use a consistent number of decimal places and a visible final total; distinguish unit price from line total.
- Protect formula cells if users should edit only customer details, quantities, and selected items. Test the sheet after protecting it so the intended input cells remain editable.
- Check the printed page or export a PDF before sending. On Windows, use File > Print to review the print preview; page orientation, scaling, and print area affect how the quote appears.
Excel does not require macros for this basic setup. Avoid adding automation unless it solves a real need: a no-macro workbook is generally easier to share with people who have different security settings. If you use a password or protection feature to prevent accidental edits, treat it as an editing safeguard, not as a substitute for securely sharing confidential information.
Common drop-down and formula problems
If the arrow does not appear, check that the correct cells are selected and that In-cell dropdown is enabled in Data Validation. Also check whether the sheet is protected in a way that blocks editing. If Excel rejects the source, confirm that the defined name exists, is spelled exactly ProductList, and refers to a valid range. A direct reference to another worksheet may not work in the validation Source box; the defined name avoids that common issue.
If a selected product produces a blank description or price, compare the selected text with the source list. Lookups need a matching product name, and extra spaces or inconsistent labels can prevent a match. Make sure the lookup ranges include the new product and that the description and price are in the expected columns. If a line total is blank, check that the item and quantity cells are filled and that the quantity is numeric.
A fixed named range such as A2:A100 is straightforward but has a limit. If the list grows beyond row 100, update the name in Name Manager or choose a larger range in advance. Do not include unrelated notes or headings in that source range, or they may appear as selectable items.
When a spreadsheet template is enough
An Excel quote template is a good fit when quotes are relatively simple, the product list is manageable, and one person or a small team can maintain the prices and terms. It offers flexibility: you can adjust the layout, print a quote, or export it as a PDF without setting up a dedicated quoting system. It also makes it easy to keep a record of calculations in the workbook.
A spreadsheet becomes harder to govern when many staff members edit different copies, prices change frequently, quotes need approval, or customers expect online acceptance and automatic reminders. In those cases, a shared workbook with clear ownership or purpose-built quoting software may reduce duplicate versions and manual follow-up. The right choice depends on the workflow, collaboration needs, and cost—not simply on how many rows fit in Excel.
Frequently Asked Questions
Can a quote template drop-down show product prices automatically?
Yes. Use a lookup formula such as XLOOKUP to return each selected item’s price from a product list. Keep the product name, description, and price in aligned rows, and update the lookup ranges when the list changes.
How do I add a new product to the drop-down?
Enter it in the next available row of the source list, then make sure the defined name covers that row. With the example range A2:A100, entries can be added within those rows; if the list outgrows the range, expand ProductList in Name Manager.
Can I use a drop-down list from another worksheet?
Yes. Define a name that refers to the cells on the Lists worksheet, then use that name as the Data Validation list source. For example, the validation source can be =ProductList.
Can I send the quote as a PDF?
Yes. Review the print layout first, then use Excel’s PDF option in the Save As or Export workflow, or choose a PDF printer available on your computer. Check that the full quote, including terms and totals, fits on the intended pages.
Can I use the same template for invoices?
You can adapt the layout, but a quote and an invoice serve different purposes and may require different details. If you reuse the workbook, change its labels and fields deliberately and verify the requirements that apply to your business and location.
Related
- Excel Number Format for Lakhs With Commas
- Excel Number Format for Millions With One Decimal Place
- Excel Number Format K: Show Values in Thousands
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- How to Sum a Column in Google Sheets: Formulas and TipsTo sum a column in Google Sheets, enter =SUM(B2:B12) in an empty cell, replacing B2:B12 with the range…
- Insert Key in Excel: What It Does and How to Use It SafelyTo use the Insert key in Excel for Microsoft 365 on Windows, open File > Options > Advanced,…
- Excel Data Validation Restrictions ExplainedExcel data validation restrictions control what users can enter in selected cells: choose the cells, then go to…
- How to Add a Checkbox in Excel With Words Beside ItTo add a clickable checkbox with words beside it in Microsoft Excel for Microsoft 365 on Windows, select…
- Excel Row Always on Top? Keep It Visible While ScrollingYes—if you mean keeping a heading row visible while you scroll, choose View > Freeze Panes > Freeze…
- How to Unhide All Rows in an Excel Spreadsheet QuicklyTo unhide every row in an Excel worksheet, select the entire sheet using the Select All button at…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.