Spreadsheets

Electricity bill tracker sheet with units used and cost per unit

Log electricity meter readings, units, bill amount and effective cost per unit in Excel, with a 12-month chart to spot summer spikes and appliance effects.

Editorial illustration for Electricity bill tracker sheet with units used and cost per unit

An electricity bill tracker needs five columns: month, previous reading, current reading, units used and bill amount. A sixth column divides the bill by units to give the effective cost per unit, and a 12-month chart turns the list into a picture where summer peaks and the effect of a new appliance are easy to see.

How do I lay out the sheet?

Use row 1 for headers: A Month, B Previous reading, C Current reading, D Units, E Bill amount, F Cost per unit, G Notes, H Flag. Enter the first month's previous reading from the bill, then in B3 write =C2 so each month starts where the last ended. The current reading comes from the bill or from the meter.

  • D2 =C2-B2 gives units used
  • F2 =IFERROR(E2/D2,"") gives the effective cost per unit
  • H2 =IF(D2>AVERAGE($D$2:$D$13)*1.3,"High","") flags a month 30 percent above the year's average
  • D14 =SUM(D2:D13) and E14 =SUM(E2:E13) give year totals
  • F14 =E14/D14 gives the year's blended cost per unit

Use the Notes column for the things that explain a number: guests in May, AC on from April, inverter replaced.

Why is my cost per unit higher than the tariff?

The bill is usually more than units times a single rate. Fixed charges, tiered or time-based rates and other line items on the bill all feed the total, and they differ by state and distribution company. Dividing the bill amount by units gives a single honest figure, the effective cost, whatever mix of charges sits behind it. It is the number to compare month to month.

A made-up illustration: a month with 210 units and a bill of ₹1,470 works out at 1,470 / 210 = ₹7.00 a unit. A hot month with 380 units and a bill of ₹3,230 is 3,230 / 380 = ₹8.50 a unit. Usage rose by 170 units, the bill by ₹1,760, and the effective rate went up too. If your own bill behaves this way, read the tariff section of your bill to see why, or ask your electricity provider.

How do I chart twelve months?

  1. Select A1:A13, then hold Ctrl and select D1:D13 (Month and Units).
  2. Choose Insert > Recommended Charts, or pick a Column chart.
  3. Add a second series for Cost per unit by right-clicking the chart, choosing Select Data and adding F2:F13, then set that series to a line on the secondary axis.
  4. Title the chart with the year.

Columns for units and a line for rate show whether a higher bill is due to using more or to paying more per unit. Keep the month list in order, April to March if you track by financial year.

How do I compare before and after buying an appliance?

Compare the same months in two years, because weather matters more than almost anything else. Put last year's units in column I and use =D2-I2 in column J for the change. A new AC may show up as a higher summer figure, while a newer, efficient fridge or fan may show as a lower base in cooler months. Write the purchase date in Notes so the story is clear later.

To estimate what one appliance costs, take its rated watts from the label, multiply by hours used a day and days in the month, and divide by 1,000 to get units. A 1,000 watt heater used 2 hours a day for 30 days is 1,000 x 2 x 30 / 1,000 = 60 units. Multiply by your effective cost per unit for a rough rupee figure.

Where does the payment fit?

Electricity bills are usually paid by UPI, card or net banking, so the debit sits on your statement. If you track spending in SpendixAI, you can import the statement and keep electricity under its own category, so the totals in the sheet and the totals in your reports can be checked against each other.

Monthly routine

  • Enter the reading and bill amount the day the bill arrives.
  • Glance at the High flag and the Notes column.
  • Once a year, copy the sheet to a new tab and keep last year's for comparison.
SPENDIXAI JOURNAL

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

Explore your reports