Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

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.

Overview of electricity cost calculator excel

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


Download Practice Workbook

Download this workbook and practice while going through the 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:

Equation to create an electricity cost calculator in Excel

Where:

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

Step-by-Step Procedures to Create Electricity Cost Calculator in Excel

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.

Basic Information of Electricity Cost Calculator in Excel

  • 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.

Creating Dataset of Basic Information of Electricity Cost Calculator in Excel

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.

Calculating total Watts to create electricity cost calculator excel

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

Total watts acquired

  • 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.

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

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

Daily Costs Acquired

  • To calculate the monthly costs of every appliance, input the formula in H9 and tap Enter.
=(($C$6/1000)*F9*E9)*30

Monthly Cost to electricity cost calculator excel

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

Output of Monthly Cost

  • Lastly, add the following formula in I9 to get the yearly costs.
=(($C$6/1000)*E9*F9)*365
  • Hit the Enter button again.

Use Excel formula to estimate Yearly cost

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

Output of yearly cost

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

Read More: How to Calculate Cost per Unit in Excel (With Easy Steps)


Similar Readings


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)

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

  • 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.

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

  • Finally, write the below formula in I17 and press the Enter key one more time.
=SUM(I9:I16)

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

Read More: How to Create Shipping Cost Calculator in Excel (with Easy Steps)


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.

Use of IF function to add tax

  • 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)

Use of IF function to add tax to electricity cost for the Excel Calculator

  • 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.

Use of IF function to add tax to electricity cost for the Excel Calculator

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.

Analyze data of the Electricity Cost Calculator in Excel

Read More: How to Calculate Production Cost in Excel (3 Effective Ways)


Conclusion

In conclusion, we have discussed some easy steps to create an electricity cost calculator in Excel. Not to mention, our ExcelDemy website shows various simple problem-solving methods like this. Please, leave any further queries or recommendations in the comment box below.


Related Articles

Yousuf Khan

Yousuf Khan

Hello! This is MD Yousuf Khan. I am a graduate & post-graduate in Information Technology from Jahangirnagar University, Bangladesh. Currently, I am writing articles for ExcelDemy. I am an independent, self-motivated person with enthusiasm to learn new things, and always try to do my best in any work assigned to me.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo