Unit Price Comparison in Excel: Which Pack Size Is Cheaper per Kg or Litre?

By Ravindra Bagale · Last updated: 17 October 2026

Download the template: unit-price-comparison-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

Shelf prices in Pune (made-up values):

For tea and detergent the biggest pack is not the cheapest. With her monthly use (8 kg rice, 3 litres oil, 500 g tea, 2 kg detergent), the best packs cost ₹1,537 a month against ₹1,682 for the worst. That is ₹145 a month, ₹1,740 a year, just from reading prices properly.

2. Build it step by step

Step 1. Type every pack

1. In A4:F4 type Item, Pack, Size (g or ml), Price, Price per kg or litre, Best value?.
2. One row per pack you see: item, pack name, size in grams or ml (1 kg = 1000), and price (yellow).

Step 2. Price per kg or litre

1. In E5 type =D5/C5*1000: ₹78 ÷ 1000 g × 1000 = ₹78 per kg. Copy down.
2. Always type the size in the small unit (g or ml), so every row is compared the same way.

Step 3. Mark the best value

1. MINIFS(min_range, criteria_range, criteria) returns the smallest value that matches (MINIFS and MAXIFS).
2. In F5 type =IF(E5=MINIFS($E$5:$E$16,$A$5:$A$16,A5),"Best value","") and copy down.

Step 4. Monthly saving table

1. In A18:G18 type Item, I use per month, Best price per kg/l, Worst price per kg/l, Cost with best pack, Cost with worst pack, Saving per month.
2. In C19 type =MINIFS($E$5:$E$16,$A$5:$A$16,A19) and in D19 =MAXIFS($E$5:$E$16,$A$5:$A$16,A19).
3. In E19 type =B19/1000*C19, in F19 =B19/1000*D19 and in G19 =F19-E19.

Step 5. Totals

1. In G23 type =SUM(G19:G22): ₹145 a month. In G24 type =G23*12: ₹1,740 a year.

Step 6. Colours

1. Select A5:F16 > Conditional Formatting > New Rule > Use a formula: =$F5="Best value" green and bold (formula rules).
2. Select E5:E16 and add =E5=MAXIFS($E$5:$E$16,$A$5:$A$16,$A5) with red text for the costliest pack.

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. Mixing kg and g. Typing 1 for a 1 kg pack and 500 for a 500 g pack makes the kg pack look 1,000 times dearer. Use g or ml for all.
2. Assuming bigger is always cheaper. Here the 1 kg tea and the 4 kg detergent cost more per kg than a smaller pack.
3. Buying more than you can use. A big pack that expires or goes stale is not a saving. Check the use-by date.
4. Ignoring offers. A "buy 2 get 1" offer changes the price. Type the effective price for the quantity you get.
5. Comparing different qualities. Compare the same product in different sizes, not two different grades of rice.

5. More real examples

Example 1: Pieces instead of weight. For eggs or soap bars, type the count as the size and multiply by 1 instead of 1000 to get price per piece.

Example 2: Two shops. Add a Shop column; MINIFS with two conditions finds the cheapest shop for each item.

Example 3: Wholesale buying with Rani. Split a 10 kg rice pack between two homes: price per kg stays ₹71 and each pays for their half.

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 calculate price per kg in Excel?

Price ÷ size in grams × 1000: =D5/C5*1000.

How do I find the cheapest option per item?

Use =MINIFS(price_per_kg, item_column, item) and compare each row with it.

Is the biggest pack always the best value?

No. In this example the 500 g tea and the 2 kg detergent beat the biggest packs.

Which Excel versions have MINIFS?

MINIFS and MAXIFS are in Excel 2019, Excel 2021 and Microsoft 365. In older versions use a filter and MIN.

How much can I save?

It depends on what you buy. Here the best packs save ₹145 a month, ₹1,740 a year.

7. Quick quiz

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