Price List With Discounts and Tax in Excel

A price list in Excel is a table of products where a formula works out each column: the discounted price, the tax, and the final price the customer pays. You keep the tax rate in one locked cell, round every amount to cents with ROUND, and add a small tier table for quantity discounts. This project is a common task in sales, retail and finance roles, and every formula in it comes up in Excel tests.

What you're building#

The price list has one row per product. You type only the product, the list price and the discount; formulas do the rest.

The tax rate lives in I1, with the label "Tax rate" in H1. Here it is 8%; type 8% or 0.08.

Step 1: The discounted price formula#

A discount takes a percentage off, so the customer pays 1 - discount of the price. A 10% discount means paying 90%, or 0.9 of the price.

In D2:

=ROUND(B2*(1-C2),2)

For the desk lamp, 24.50 × 0.9 = 22.05. Fill down to D6 with Ctrl+D on Windows or ⌘D on a Mac. Every reference here should move with the row, so no $ signs yet.

If you want the discount in money, use =B2-D2, which gives 2.45 for the lamp. Percentages trip up a lot of people; our guide to the percent change formula explains how Excel stores 10% as 0.1.

Step 2: Add tax with a locked rate cell#

Tax is the sale price times the tax rate. Because every row uses the same rate, lock its cell with $.

In E2 and F2:

=ROUND(D2*$I$1,2)
=D2+E2

$I$1 stays fixed when you fill down, while D2 moves to D3, D4 and so on. Without the $, row 3 would look at I2, which is empty, and show zero tax. Press F4 on Windows or ⌘T on a Mac to add the dollar signs. Our guide to absolute references and the $ sign explains this in detail.

Change I1 to 10% and every tax and final price updates at once. That is the point of an input cell.

Step 3: Why round each amount to cents#

ROUND changes the stored value, not just how it looks. Formatting a cell to two decimals only hides the extra digits, so a total can be off by a cent from what customers actually pay.

The notebook shows why. 12.75 × 0.95 = 12.1125. Formatted, it looks like 12.11, but Excel would still calculate tax on 12.1125. With =ROUND(B6*(1-C6),2), the stored value is exactly 12.11.

The syntax is =ROUND(number, num_digits): 2 for cents, 0 for whole units. The ROUND function guide and ROUND vs ROUNDUP vs ROUNDDOWN cover the other rounding functions. Some businesses round once on the final price instead of each step. Whichever rule you use, use it everywhere.

Final price in one formula#

If you don't need the sale price and tax columns, one formula does it all:

=ROUND(B2*(1-C2)*(1+$I$1),2)

Multiplying by (1 + tax rate) adds the tax. The results match column F here, but rounding once can occasionally differ by a cent from rounding at each step.

Step 4: Quantity discounts with a tier table#

Quantity breaks give a bigger discount for bigger orders. Put the tiers in a small table, sorted from the smallest quantity:

Each row means "from this quantity up". For an order of 24 units in N1:

=XLOOKUP(N1,L1:L3,M1:M3,,-1)
=VLOOKUP(N1,L1:M3,2,TRUE)

Both return 5%. XLOOKUP's match mode -1 means "exact match, or the next smaller value"; VLOOKUP with TRUE does the same but needs the first column sorted. XLOOKUP needs Excel 2021, Excel 2024 or Microsoft 365. VLOOKUP approximate match for tiers and bands explains the logic.

Step 5: Work backwards from a tax-inclusive price#

To find the pre-tax price, divide by 1 + tax rate. Subtracting 8% from the final price gives the wrong answer, because the tax was 8% of the smaller number.

=ROUND(F3/(1+$I$1),2)

For the office chair, 136.78 ÷ 1.08 = 126.65, back to the sale price.

To show how far a sale price is below the list price, use the percent change formula, =(D2-B2)/B2. It returns -10% for the lamp.

How to extend the price list#

Once the basic columns work, these additions are common in real price lists:

1. Cost and margin: add a Cost column and calculate margin with =(D2-cost)/D2.
2. A quote or invoice: add a Quantity column and a line total =F2*qty, then a SUM at the bottom. The invoice with formulas project builds this out.
3. Look up prices by code: give each product a code and use XLOOKUP to pull prices into an order sheet.
4. Several tax rates: put each product's tax category in a column and look up its rate from a small table.
5. Sales reporting: feed prices into a simple sales report, or plan spending with the same input-cell idea in a monthly budget.

Discount and tax questions are a classic in finance and sales tests. See Excel skills for finance and accounting jobs, Excel skills for sales and operations roles, and our list of Excel interview questions.

Practise it#

Write the one-formula final price in the exercise on this page. Then practise locking a rate cell in the relative vs absolute references lesson, where you add tax and discounts to a product list, and the percentages and percent change lesson.