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.
Read More: How to Create Fuel Cost Calculator Using Excel Formula
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.
- 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)
Read More: How to Create Shipping Cost Calculator in Excel
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.
Read More: How to Construct Cost Inflation Index Calculator in Excel
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.
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.
