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

Introduction to Electricity Cost Calculator in Excel

The energy cost calculator is a straightforward tool for determining the cost of energy used by homes and businesses. By using the results of this calculation, you can establish a budget. Additionally, this power cost calculator assists in creating family or workplace budgets and managing expenses. The equation for calculating the cost is as follows:

Equation to create an electricity cost calculator in Excel

Where:

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

In this article, we’ll explore how to use SUM, IF functions, and Excel formulas for this calculation. We’ll also manually verify the calculator. Let’s proceed step by step.

Step 1 – Basic Information of the Electricity Cost Calculator in Excel

Add the following headers in column B: User, User Address, and Pay Per Unit.
Populate these headers with the correct values.

Basic Information of Electricity Cost Calculator in Excel

Add additional headers: Home Appliance, Quantity, Power Rating, Total Watts, Hours/Day, Daily Cost, Monthly Cost, Yearly Cost, Total Electricity Cost, and Tax (3%).
Fill in the appropriate values under Quantity, Power Rating, and Hours/Day.

Creating Dataset of Basic Information of Electricity Cost Calculator in Excel

Step 2 – Using Excel Formulas to Calculate Daily, Monthly & Yearly Power Bills

Calculate the total Watts used for each appliance:

In cell E9, enter the formula:

=C9*D9

- Here, we multiply Power Rating by Quantity to get the total Watts.
- Press Enter.

Calculating total Watts to create electricity cost calculator excel

- Autofill this formula for the rest of the column.

Total watts acquired

Calculate the daily cost:
- In cell G9, enter the formula:

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

- Here, we divide the Pay per Unit 0.80 by 1000 and multiply it by 450 & 8.
- We multiply by 1 as we wish to get the daily cost.
- Press Enter.

Using Excel formula to Find Daily Cost to create electricity cost calculator excel

- Autofill this formula.

Daily Costs Acquired

Calculate the monthly cost:
- In cell H9, enter the formula:

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

- Press Enter.

Monthly Cost to electricity cost calculator excel

Autofill this formula.

Output of Monthly Cost

Calculate the yearly cost:
- In cell I9, enter the formula:

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

- Press Enter.

Use Excel formula to estimate Yearly cost

- Autofill this formula.

Output of yearly cost

Note

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

Step 3 – Estimating Total Electricity Cost with the SUM Function

To estimate the total daily cost, use the SUM function in cell G17:

=SUM(G9:G16)

- Press Enter.

Use of SUM function to total electricity cost for the Excel Calculator

Similarly, calculate the total monthly cost in cell H17:

=SUM(H9:H16)

- Press Enter.

Use of SUM function to total electricity cost for the Excel Calculator

For the yearly cost, use the SUM function in cell I17:

=SUM(I9:I16)

- Press Enter.

Use of SUM function to total electricity cost for the Excel Calculator

Step 4 – Adding Tax Using the IF Function

Calculate Tax for Daily Cost (Cell G18):

In cell G18, enter the following formula:

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

- Explanation:
  - If the value in G17 (daily cost) is greater than 33, add a 3% tax to it.
  - Otherwise, keep the original daily cost.
Press Enter.