Grocery List in Excel with Prices: Auto Total, Category Drop-Down and Tick Items as You Shop

By Ravindra Bagale · Last updated: 17 October 2026

Download the template: grocery-list-with-prices-template.xlsx: example data with real formulas. Yellow cells are inputs you can type over; green cells hold formulas; blue rows are headers and totals. Replace the example data with your own and everything recalculates.

Made-up data: every name, amount, shop and date on this page is made up for practice. Cities are used only to make the examples feel real.

1. The example

Rani's list (made-up prices) has 15 items:

The list total is ₹6,531, so the budget check says Over by ₹531 before she even starts. By category, Dairy is the biggest at ₹2,060 (milk ₹1,680 + paneer ₹380), then Oil and ghee at ₹1,415.

Halfway through shopping she has marked 8 items Yes: ₹2,566 in the cart, ₹3,965 still to buy, 7 items left. To get within budget she cuts paneer (₹380) and one litre of oil (₹155): 6,531 − 535 = ₹5,996.

2. Build it step by step

Step 1. Budget and headers

1. In A3 type Budget (₹) and in B3 6000.
2. In A5:G5 type Item, Category, Qty, Unit, Price per unit (₹), Amount (₹), In cart?.

Step 2. Items and drop-downs

1. Type the items from A6 with category, quantity, unit and price (yellow cells).
2. Select B6:B300 > Data > Data Validation > List and type the categories, separated by commas (drop-down lists).
3. Select G6:G300 > the same with Yes,No.

Step 3. Amount and total

1. In F6 type =C6*E6 and copy down to row 20.
2. In F21 type =SUM(F6:F20): ₹6,531.

Step 4. Shopping status

1. In J6 type =F21 (list total).
2. In J7 type =SUMIF(G6:G20,"Yes",F6:F20): ₹2,566 in the cart (SUMIF).
3. In J8 type =J6-J7: ₹3,965 still to buy.
4. In J9 type =COUNTIF(G6:G20,"No"): 7 items left.
5. In J10 type =IF(J6>B3,"Over by ₹"&TEXT(J6-B3,"#,##0"),"Within budget").

Step 5. Per-category totals

1. Type the categories in I13:I19.
2. In J13 type =SUMIF($B$6:$B$20,I13,$F$6:$F$20) and copy down: Dairy ₹2,060.

Step 6. Grey out bought items

1. Select A6:G300 > Conditional Formatting > New Rule > Use a formula: =$G6="Yes" (formula rules).
2. Format: grey fill, grey font, Strikethrough.
3. Select J10 > a rule =$J$6>$B$3 with red fill.

3. Formulas used

Every formula above is written in the template, so you can click a cell and see it in the formula bar. All values on this page were recalculated in the file.

4. Common mistakes

1. Price per pack vs per kg. Keep the unit and the price on the same basis: 5 litres × ₹155 per litre, not × the price of a 5-litre can.
2. Typing "yes" with a space. SUMIF looks for exactly "Yes". The drop-down prevents typing errors.
3. Forgetting the $ in SUMIF. $B$6:$B$20 must stay fixed when you copy J13 down.
4. Number stored as text. A price pasted from a website with "₹" may be text; Amount then shows #VALUE!. Type the number only and format it as ₹.
5. New items below the Total row. Insert new rows above the Total so they are inside F6:F20.

5. More real examples

Example 1: Weekly vegetable list. Smaller list, budget ₹800, same formulas.

Example 2: Party shopping. Categories like snacks, drinks, decoration and return gifts; budget check before ordering.

Example 3: Compare two apps. Add a second price column for another app and =MIN(E6,H6) to see which is cheaper per item.

Quick revision cards

Englishमराठीहिंदी

Quick revision

Each card shows formulas from this page, each with an example and its answer from the template. Scan the QR code on a card to open this lesson. All names and numbers are made up for practice.

More cards: Ravi Sir's Cards.

6. Frequently asked questions

How do I make a grocery list in Excel with prices?

Columns for item, quantity and price, Amount = Qty × Price, and a SUM at the bottom. The template adds a budget check and an In cart column.

How do I tick items as I shop?

Use a Yes/No drop-down (or an in-cell checkbox in Microsoft 365, see the habit tracker). A formula rule greys out the row.

How do I total only the items in my cart?

=SUMIF(G6:G20,"Yes",F6:F20).

How do I reuse the list every month?

Keep it as a master list. At the start of the month change every In cart? to No and update the prices.

Can I see spending by category?

Yes: SUMIF on the Category column, as in J13:J19.

7. Quick quiz

Ten questions on this page. Sign in to save your score.