Shopping

Price per 100 g or per litre in Excel: compare pack sizes

Price per unit formula in Excel: divide price by quantity, scale to 100 g or one litre, and compare pack sizes in one column with the cheapest highlighted.

Editorial illustration for Price per 100 g or per litre in Excel: compare pack sizes

To compare pack sizes in Excel, divide the price by the quantity and multiply by 100 to get the price per 100 g, or per 100 ml for liquids. Put the result in its own column, sort or highlight the lowest, and the cheapest pack per unit is obvious. The formula is =C2/A2*100 when column A holds grams and column C the price.

What is the basic formula?

A price per unit is price divided by quantity. A 200 g pack at ₹320 costs ₹320 ÷ 200 = ₹1.60 a gram. That small number is hard to compare at a glance, so scale it to a round quantity: ₹1.60 × 100 = ₹160 per 100 g. In a sheet, this is =C2/A2*100.

What does a made-up comparison look like?

Say you buy coffee powder and see four pack sizes. The prices are invented.

Pack size in gramsPricePrice per 100 g
50₹95₹190
100₹170₹170
200₹320₹160
500₹760₹152

Each row is ₹price ÷ grams × 100. For example, ₹760 ÷ 500 × 100 = ₹152. The 500 g pack is ₹152 per 100 g, against ₹170 for the 100 g pack, so you pay ₹18 less per 100 g, a drop of about 10.6%.

How do you handle kg, ml and litres together?

Real shelves mix units. Use three input columns: A for quantity, B for the unit (g, kg, ml or L), C for price. Then in D2 enter =C2/IF(B2="kg", A2*1000, A2)*100 for solids to get price per 100 g. Or, for liquids, =C2/IF(B2="L", A2*1000, A2)*100 to get price per 100 ml. If you prefer price per litre, use *1000 at the end instead of *100 and keep the quantity in ml.

The IF part converts kilograms to grams, or litres to millilitres, before dividing. For example, a 1 L oil bottle at ₹140 gives ₹140 ÷ 1000 × 100 = ₹14 per 100 ml, while a 5 L can at ₹640 gives ₹640 ÷ 5000 × 100 = ₹12.80 per 100 ml.

How do you highlight the cheapest pack?

Select the price-per-unit column, for example D2 to D5. Add a conditional formatting rule using a formula: =D2=MIN($D$2:$D$5). Choose a green fill. In Excel, that is Home, Conditional Formatting, New Rule, Use a formula. The lowest cell will turn green, and when you change a price, the highlight moves.

If you want the sheet to say which pack wins, use =INDEX($A$2:$A$5, MATCH(MIN($D$2:$D$5), $D$2:$D$5, 0)), which returns the pack size beside the lowest unit price.

What else can trip the comparison?

  • Packs that give a free extra, such as 10 percent extra: enter the total quantity, including the extra.
  • Multipacks: enter the total weight of all units, not one unit.
  • Net weight versus gross weight: use the net quantity printed on the pack.
  • Different quality or brand: a cheaper unit price on a product you will not enjoy is not a saving.

Can you use the same sheet for other things?

Yes. The same layout works for detergent per wash, oil per litre, rice per kg, or batteries per piece. Only the unit changes. Keep one tab per product so you can add a new row each time you notice an offer.

After the shop, the bill total goes into your records. If you track your spending in SpendixAI, a custom category for pantry staples helps you see whether the bigger packs actually lowered your monthly grocery total, not just the unit price.

SPENDIXAI JOURNAL

Useful ideas start with a clearer picture of your own spending.

Explore your reports