# How to Use Electricity Bill Calculation Formula in Excel

Get FREE Advanced Excel Exercises with Solutions!

We all need to use electricity to conduct our day-to-day activities in our everyday life. So, the electric bill may become a very significant expense to be included in our monthly budget calculation. This article demonstrates how to use the electricity bill calculation formula in Excel in 2 suitable ways.

We shall use a sample dataset as an example to show. The information below shows the current meter reading, the past meter reading, and the consumed units of electricity of ten different households located inside a building.

Before starting the article, let’s have an overview of the final output you are going to get. ## General Formula to Calculate Electric Energy Usage and Total Bill

The electricity bill can be calculated manually with the help of a theoretical scientific formula. Which is:

Total monthly electricity bill (\$)= Total energy consumed by all the electrical appliances in one hour (kilowatt-hour) x Number of hours that the above-mentioned appliances were active during a period of one month (hours) x Per unit cost of electricity (\$).

Here,

Kilowatt = Unit of Power

Kilowatt-hour = Special unit of Energy to calculate the electric energy usage and subsequently the cost of it. (We can calculate an appliance’s KW-h by looking at its power rating and then multiplying it by 3600)

Let’s assume, a typical space heater has a power rating of 1 KW. So, the energy consumed by it in one hour is 1 x 3600 = 3600 KW-h. Assuming, it remains active for 1 hour a day on average, during one month period it remains active for a total of 1 x 30 = 30 hours. And if the current per unit cost of electricity is \$1, the electricity bill that would have to be paid for using this appliance during one month period would be 3600 x 30 = \$108000.

## 2 Cases to Perform Electricity Bill Calculation Formula in Excel

Let’s assume we have a dataset, namely “Monthly electricity usage of Johnson H. housing society (Building 1)”. You can use any dataset suitable for you. Here, we have used the Microsoft Excel 365 version; you may use any other version at your convenience.

### 1. Electricity Bill Calculation for Fixed Unit Price

In this section, we are going to show you how to calculate your monthly electricity bill when the unit price is fixed. Here, we only need to use the multiplication operator. No additional functions are needed.

Steps:

•  Firstly, go to cell H5 and type the following formula:
`=F5*G5` • Next, press ENTER to get the result. • Finally, place the cursor at the bottom right corner of cell H5, left-click on it and drag the cursor down to cell H14. You will get the calculated result for the rest of the flats too. ### 2. Electricity Bill Formula for Variable Unit Price (Slab)

At this point, you are going to learn to carry out the same task in the case of variable unit prices. It means the unit price would be different for different ranges of electricity usage or slabs. Also here, you will only need to use the multiplication operator and the function named IF.

We have provided a table below, in which different unit prices according to the different electricity usage slabs are mentioned. Steps:

•  First of all, move to cell G5 and insert the following formula:
`=IF(F5<=400,F5*0.1,IF(F5<=700,F5*0.2,IF(F5<=1000,F5*0.3,IF(F5<=1300,F5*0.4,IF(F5<=1600,F5*0.5,0)))))`

Formula Breakdown:

• IF(F5<=400,F5*0.1,” this part means if the value in F5 is less than or equal to 400, the function would take the value in F5, multiply It by 0.1 and then return the result in G5, if not, the function would proceed to the next part.
• IF(F5<=700,F5*0.2,” this part means if the value in F5 is less than or equal to 700, the function would take the value in F5, multiply It by 0.2 and then return the result in G5, if not, the function would proceed to the next part.
• IF(F5<=1000,F5*0.3,” this part means if the value in F5 is less than or equal to 1000, the function would take the value in F5, multiply It by 0.3 and then return the result in G5, if not, the function would proceed to the next part.
• IF(F5<=1300,F5*0.4,” this part means if the value in F5 is less than or equal to 1300, the function would take the value in F5, multiply It by 0.4 and then return the result in G5, if not, the function would proceed to the next part.
• IF(F5<=1600,F5*0.5,0)))))” this part means if the value in F5 is less than or equal to 1600, the function would take the value in F5, multiply It by 0.5 and then return the result in G5, otherwise the function would return the value “0” in G5. • Afterward, press ENTER to get the expected result. • Lastly, repeat the task of step 3 from method 1 to obtain the rest of the results. ## Things to Remember

• Nowadays, almost all users are charged with variable unit prices to reduce the extent of electrical energy usage by encouraging the users to use electricity as little as possible.
• Careful use of parenthesis is very important while you are using multiple nested IF functions.

## Conclusion

You may now know to use formulas for your electricity bill calculation in Excel using both fixed and variable unit prices. Use these anytime you need to, and let us know if you think of any other, superior methods. If you have any questions, feel free to leave a remark in the space below.

## Related Articles Md. Nafis Soumik

I am Md. Nafis Soumik. I am a Naval Architecture & Marine Engineering (NAME) graduate from Bangladesh University of Engineering & Technology (BUET). My hobby is to listen and create music along with backpacking. My career goal is to pursue higher education eventually. I always attempt to learn from many sources and try to come up with creative answers.

We will be happy to hear your thoughts Advanced Excel Exercises with Solutions PDF  