How to Create Electricity Cost Calculator in Excel (with Easy Steps)

Microsoft Excel is very useful to build a power energy cost calculator. We can use Excel formulas to create the calculator efficiently saving a lot of time and effort. Moreover, Excel thoroughly features a large number of built-in default formulas, making it simple for users to perform calculations. With this in mind, we will show you how to create an electricity cost calculator in Excel with 5 easy steps.

Before jumping into the steps in the section, take a look at the overview of this article.

Introduction to Electricity Cost Calculator in Excel

One of the simplest ways to determine the cost of the energy used by various homes and businesses is to use this energy cost calculator. Using the results of that calculation, one can always establish a budget. Furthermore, this power cost calculator also aids in establishing a family or workplace budget, aids in money management, etc. The equation to calculate the cost:

Where:

W = Power (Watts)
H = Time (Hours)
R = Rate (KWH)

How to Create Electricity Cost Calculator in Excel: with Easy Steps

In this article, we will see the uses of the SUM, IF functions, and Excel formulas for the calculation. Lastly, we will manually analyze the calculator to verify. So, let’s go through the steps one by one.

Step 1: Basic Information of Electricity Cost Calculator in Excel

The first step aims to include the basic information and populate them with proper parameters. Follow the below instructions.

Initially, add the headers User, User Address, and Pay Per Unit in column B.
Further, populate them with correct values.

Likewise, add up the Home Appliance, Quantity, Power Rating, Total Watts, Hours/Day, Daily Cost, Monthly Cost, Yearly Cost, Total Electricity Cost, and Tax (3%).
Also, put the proper values under the Quantity, Power Rating, and Hours/Day headers.

Step 2: Utilize Excel Formula to Calculate Daily, Monthly & Yearly Power Bills

The objective of the second step is to calculate the total Watts used for each appliance using Excel calculation.

Firstly, type the following formula in E9.

=C9*D9

Here, we multiply Power Rating by Quantity to get the total Watts.
Later, press the Enter or Tab keys.

Subsequently, we obtain the output as 450.
Next, use the AutoFill tool to fill the rest of the column as shown below.

Similarly, in G9, write the formula given below.

=(($C$6/1000)*F9*E9)*1

Here, we divide the Pay per Unit 0.80 by 1000 and multiply it by 450 & 8.
Lastly, we multiply by 1 as we wish to get the daily cost.
Pressing the Enter key will show you the output.

Drag the formula cell using the AutoFill icon.
Thus, we obtain the daily costs of each appliance.

To calculate the monthly costs of every appliance, input the formula in H9 and tap Enter.

=(($C$6/1000)*F9*E9)*30

Also, get the rest of the output using the AutoFill handle.

Lastly, add the following formula in I9 to get the yearly costs.

=(($C$6/1000)*E9*F9)*365

Hit the Enter button again.

Using the AutoFill tool, we get our desired yearly costs.

Note

Use the absolute cell reference in C6 as we do not wish to change the cell.

Step 3: Apply SUM Function to Estimate Total Electricity Cost

In the third step, we will use the SUM function to estimate the total electricity costs. The SUM function adds up the values of a given range and returns an Integer. See the below steps.

To begin with, type the SUM formula in G17 and press Enter.

=SUM(G9:G16)

Similarly, in H17, calculate the total cost using the same formula and tap Enter button.

=SUM(H9:H16)

Here, the SUM function adds all the values of the range H9:H16.

Finally, write the below formula in I17 and press the Enter key one more time.

=SUM(I9:I16)

Step 4: Use IF Function to Add Tax to Power Consumption Calculator in Excel

This step shows the use of the IF function to calculate the total cost with added tax. The IF function allows to us perform logical comparisons between 2 or more given conditions and returns True or False values accordingly. Move below to see the formula uses.

First, in cell G18, type the formula below.

=IF(G17>33,G17+G17*3%,G17)

Now, the IF function sets the logical conditions where if the value in G17 is greater than 33 then, it adds a 3% tax to it.
Afterward, tap the Enter or Tab keys.

Again, in H18, add the tax to the monthly cost with this formula below and then press Tab.

=IF(H17>1000,H17+H17*3%,H17)

Lastly, include tax to the yearly cost in I18 using the below formula.

=IF(I17>12000,I17+I17*3%,I17)

After that, tap the Enter button.

Step 5: Analyze to Properly Run Electricity Cost Calculator with Excel

In our last step, we will track daily consumption and input the total quantities to test the calculator.

Add the correct values under the Quantity header.
Hence, we get our desired electricity cost calculator with Excel.

Download Practice Workbook

Download this workbook and practice while going through the article.

Electricity Cost Calculator.xlsx

Conclusion

In conclusion, we have discussed some easy steps to create an electricity cost calculator in Excel. Please, leave any further queries or recommendations in the comment box below.

<< Go Back to Cost Calculator | Finance Template | Excel Templates

Get FREE Advanced Excel Exercises with Solutions!