Ravindra BagaleCourses & study guides मराठी Track your progress

Appliance Electricity Cost Calculator in Excel: Watts to Units (kWh) and Monthly Cost

By Last updated:

The Bagale home in Pune got a bigger electricity bill in summer, and everyone blamed someone else. Was it the AC, the water heater, or the fans running all day? Every appliance label shows its watts. Multiply by hours of use and Excel turns that into units and rupees, and the answer is clear: the AC alone is more than half the bill.

What you'll learn on this page

  • Watts, quantity, hours per day and days per month
  • Units (kWh) = watts × quantity × hours × days ÷ 1000
  • Monthly cost at your average rate per unit
  • Each appliance's share of the bill
  • The biggest user with INDEX and MATCH
  • A what-if saving from cutting AC hours

Download the template: appliance-electricity-cost-calculator-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

The Bagale home in a summer month (made-up wattages and hours; average ₹8 per unit):

Appliance Watts × qty Hours/day Days Units Cost Share
Air conditioner 1,500 × 1 6 30 270.0 ₹2,160.00 53.8%
Fridge (compressor hours) 150 × 1 10 30 45.0 ₹360.00 9.0%
Ceiling fans 75 × 4 10 30 90.0 ₹720.00 17.9%
LED lights 10 × 8 6 30 14.4 ₹115.20 2.9%
TV 100 × 1 5 30 15.0 ₹120.00 3.0%
Washing machine 500 × 1 1 12 6.0 ₹48.00 1.2%
Water heater 2,000 × 1 0.5 30 30.0 ₹240.00 6.0%
Water pump 750 × 1 0.5 30 11.3 ₹90.00 2.2%
Laptops 65 × 2 6 26 20.3 ₹162.24 4.0%

Total: 501.9 units, ₹4,015.44 a month (about ₹48,185 a year). One hour of AC costs ₹12. Cutting AC use by 1 hour a day saves ₹360 a month, ₹4,320 a year.

Inputs (yellow) Watts and quantity Hours and days Rate ₹8 per unit Formulas Units = W × qty × h × d ÷ 1000 Cost = units × rate Share = units ÷ total Results 501.9 units, ₹4,015 AC is 53.8% ₹360 saved per hour cut

Figure 1. Watts × quantity × hours per day × days ÷ 1000 gives units (kWh); units × rate gives the cost; each appliance's share of the total shows the biggest user; a what-if cell shows the saving from fewer AC hours.

The appliance-electricity-cost-calculator-template.xlsx template sheet with example data, yellow input cells and green formula cells

Figure 2. The template sheet, recalculated with its example data. Download it above and replace the yellow cells with your own numbers.

2. Build it step by step

Step 1. Rate and appliances

  1. In B3 type your average rate per unit. 1 unit on the bill = 1 kWh (1,000 watts for 1 hour).
  2. In A5:H5 type Appliance, Watts, How many, Hours per day, Days per month, Units per month, Cost per month, Share of bill.

Step 2. Units per month

  1. In F6 type =B6*C6*D6*E6/1000: 1,500 × 1 × 6 × 30 ÷ 1,000 = 270 units.

Step 3. Cost and share

  1. In G6 type =F6*$B$3: ₹2,160.
  2. In F15 type =SUM(F6:F14), then in H6 type =F6/$F$15 as %. Copy F6:H6 down.

Step 4. Biggest user

  1. In K6 type =INDEX(A6:A14,MATCH(MAX(F6:F14),F6:F14,0)): Air conditioner (INDEX and MATCH).
  2. In K7 type =MAX(H6:H14): 53.8%. In K8 type =G15*12 for the year.

Step 5. What if

  1. In K10 type the AC hours to cut per day (1). In K11 type =B6*C6*K10*E6/1000*B3: ₹360 a month.
  2. In K13 type =B6/1000*B3: one hour of AC costs ₹12.

Step 6. Colours and chart

  1. Add data bars on F6:F14 (data bars) and a formula rule =$H6>=0.25 with red bold text (formula rules).
  2. Select A6:A14 and F6:F14 > Insert > Pie (pie charts).

Ravindra Bagale's Tip

Set the AC one or two degrees higher and use the fan with it. Fewer compressor hours means fewer units.

3. Formulas used

Cell Formula What it does
F6 =B6*C6*D6*E6/1000 Units per month
G6 =F6*$B$3 Cost per month
H6 =F6/$F$15 Share of bill
F15 =SUM(F6:F14) Total units
G15 =SUM(G6:G14) Total cost
K6 =INDEX(A6:A14,MATCH(MAX(F6:F14),F6:F14,0)) Biggest user
K8 =G15*12 Cost per year
K11 =B6*C6*K10*E6/1000*B3 Saving per month
K13 =B6/1000*B3 Cost of 1 hour of AC

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. Forgetting ÷ 1000. Watts × hours gives watt-hours. Divide by 1,000 for units (kWh).
  2. Fridge at 24 hours. The compressor switches on and off. Use the hours it actually runs, or the fridge looks far too costly.
  3. Using the label maximum for an inverter AC. Inverter ACs often draw less than the label once the room is cool. Treat the result as an upper estimate.
  4. A flat rate for a slab bill. Real bills use slabs and fixed charges. Use your bill total ÷ units as the average rate.
  5. Forgetting standby. Set-top boxes and chargers left on add a little every day.

Ravindra Bagale's Tip

Read the meter on two days a week apart. Units used ÷ 7 is your real daily use, to check this sheet.

5. More real examples

Example 1: Winter vs summer. Copy the sheet: no AC, more water heater. Compare the two totals.

Example 2: Buying a new appliance. Compare a 5-star and a 3-star model by their yearly units on the label × your rate.

Example 3: Small office. Add computers, printer and lights to see the monthly running cost.

Quick revision cards

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.

Revision card 1 for Appliance Electricity Cost Calculator in Excel: Units per month: =B6*C6*D6*E6/1000 gives 270.0; Cost per month: =F6*$B$3 gives ₹2,160.00; Share of bill: =F6/$F$15 gives 53.8%.
Card 1 of 2: Appliance Electricity Cost Calculator in Excel · Download PNG
Revision card 2 for Appliance Electricity Cost Calculator in Excel: Total units: =SUM(F6:F14) gives 501.9; Total cost: =SUM(G6:G14) gives ₹4,015.44. Includes an example data table.
Card 2 of 2: Appliance Electricity Cost Calculator in Excel · Download PNG

More cards: Ravi Sir's Cards.

6. Frequently asked questions

How do I convert watts to units in Excel?

Units (kWh) = watts × hours ÷ 1,000. For a month: watts × quantity × hours per day × days ÷ 1,000.

What is 1 unit of electricity?

1 kWh: a 1,000-watt appliance running for one hour.

How much does a 1.5 kW AC cost per hour?

1.5 units per hour × your rate. At ₹8 a unit, ₹12 an hour.

Why is my bill different from this sheet?

Hours are estimates, and real bills add slabs, fixed charges and taxes. Use your bill to adjust the rate.

Which appliance uses the most?

Usually the ones with high watts and long hours: AC, water heater, geyser, pumps.

7. Quick quiz

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

Loading practice tasks...

  1. Total invoices (Easy): Add up Invoices in D2:D11. (sheet "Data")
  2. Average amount (Easy): Average of E2:E11, rounded to 2 decimals. (sheet "Data")
  3. Rows in Admin (Easy): How many rows have Department "Admin"? (sheet "Data")
  4. Largest invoices (Easy): The largest value in D2:D11. (sheet "Data")
  5. Days taken (Easy): C2 minus B2. (sheet "Dates")
  6. Start year (Easy): Year of B3. (sheet "Dates")
  7. Due month (Easy): Month number of C3. (sheet "Dates")
  8. Earliest start (Easy): Earliest date in B2:B9 (as a date number). (sheet "Dates")
  9. Invoices for Admin (Medium): Total of D2:D11 where B is "Admin". (sheet "Data")
  10. Admin in Berlin (Medium): Total of D2:D11 where B is "Admin" and C is "Berlin". (sheet "Data")
  11. Working days (Medium): Working days from B2 to C2. (sheet "Dates")
  12. Weekday of due (Medium): Weekday number of C4 (Sunday = 1). (sheet "Dates")

See all practice tasks in the Task Hub →