How to Calculate Electricity Consumption (kWh) Using Excel?

Hi @ekxsss venom

Here is a practical example of how to set up your spreadsheet:

The basic formula is Electricity consumption (kWh) = Current meter reading − Previous meter reading

Create a worksheet with the following columns:

In the Consumption column (E2), enter: =D2-C2. Copy the formula down to calculate the consumption for each reading.

Automatically retrieve previous meter readings

If you have multiple meters, you can use the LOOKUP function to retrieve the most recent previous reading for the same meter.

For example, starting from C4, enter: =IFERROR(LOOKUP(2,1/($B$2:B3=B4),$D$2:D3),""). Copy the formula down. This formula searches for the most recent previous entry with the same Meter ID and retrieves its Current Reading. It prevents readings from different meters from being mixed.

If your data is formatted as an Excel Table (Ctrl + T), formulas will automatically copy to new rows whenever you add meter readings, allowing consumption calculations to update automatically.

PivotTables can help summarize electricity consumption across multiple meters and periods.

To create one:

• Select your Excel Table.
• Go to Insert > PivotTable.
• Drag Date into the Rows area.
• Drag Meter into the Columns area.
• Drag Consumption into the Values area.

If possible, please share a sample of your spreadsheet and let me know where you're getting stuck. That will help me provide more accurate suggestions tailored to your setup.

You can also use AI tools such as Microsoft Copilot to help build formulas, explain how they work, troubleshoot errors, or suggest improvements to your spreadsheet.

For sake of example, assume you enter the date of the meter reading starting in A2 and proceeding down and the reading itself is in the corresponding row of column B.

You can calculate the consumption between any two meter readings by subtracting the earlier one from the later one,such as =B3-B2.

If you want to calculate daily consumption, you need to enter the meter reading daily also. If you enter readings less frequently, you can only calculate average daily consumption. You can do this with the formula =(B3-B2)/(A3-A2).

For monthly consumption, you need to enter data on the same day of each month. If you enter data daily, then a formula like =B31-B2.

If you have multiple meters, then one approach would be to use a different column for each meter. With a second meter in column C, the total consumption would simply be the sum of the two individual consumptions, such as =(B3-B2)+(C3-C2).

To automatically process new data, you can create a table that induces your existing data and one additional row with formulas but no readings. When you have new data to add, insert a row above this last row. The formulas will be copied to the new row and when you enter the readings the calculations will be applied to this new data.