Discover the Best Electricity Bill Format in Excel for Education

Electricity bill format in excel for Education

Welcome to today's new video of Unofficial Office. So friends, in today's video I will explain to you step by step the process of calculating electricity bill. So before starting, if you are new on this channel, please subscribe to this channel. So let's start, friends, I have taken a dummy data here and here I have added some columns in this data like Customer ID, Consumer Name, Previous Reading, Current Reading, Unit at Consumed Rate, Unit and Finally Net Amount. We will calculate ing to the data of the remaining columns, so you will get all this data on the electricity bill itself. Here, I have filled four columns with a hypothetical data. We will calculate the remaining data with the help of formula. One by one, first of all we will calculate the units consumed, then to calculate the units consumed, we will apply a simple formula here, we will subtract the equals to current reading from the previous reading, so friends, it is very important to keep this in mind here. That is, our current reading will always be more than the previous reading, so after applying this formula, I will press enter and here our unit consumption will be calculated and apply the same formula in the complete column in the same way for the first row. For this, we will select this complete column, press control D and the values ​​for all will be calculated and will come here. The next step is to calculate the unit on our rate, so in this same sheet, I have added a new tab here. In the name of unit price, here I have calculated the unit at dummy cost, so instead you have to find the cost of your actual unit, you can find it on the website of your electricity provider or it will be available on your electricity bill also. So from there you can pick your unit cost, so here I have taken dummy data and on the basis of this we will do our calculations. On this calculation tab, we will apply the IF formula on the rate per unit, so basically you can see here. But our cost is fluctuating ing to the range of units, so to calculate the unit rate, we will use the if formula, then here we will put equals to, type if, open the bracket, after opening the bracket, the first thing we will do is If we put the condition here , then our unit which is consumed, if it is less than equal to 100, we will start the double quoted comma by putting a comma, then the value of our e3, if it is less than equal to 100, then our unit at the rate will be 2.5, double quoted. We will close the comma, we will apply the second condition, then here again we will write if, we will open the bracket, select cell e e3 and the second condition here will be that if e3 is less than and equal to 250, then our joe rate will be the unit. That will be ₹ We will close the double inverted commas, we will again put commas and put the third condition, here we will open the bracket by putting if, then our third condition will be if e3 e is less and equal to 500 then our per unit cost will be 7.50 and last Our condition will be 'if' and we will open the bracket by applying 'if' if e3 is greater than 500 then our unit cost will be 9. Here we have applied 'if' four times. We have applied four conditions so we will close this bracket four times in the last and press enter. If we do this, here we will get the unit cost calculated here, then to apply the same formula in all the other cells, we will select the complete column, press control D and the same formula will be applied in all the other cells also, so here But you can see that our unit consumed is less than 100 and its unit cost is . 5. Once the calculation has been done, we can see that if our unit is less than 100, then its cost is 2.5, so this means that our formula has been completely applied correctly. Now the last step of this process is net. To calculate the amount, we will apply a simple formula here, we will multiply equals to unit consumed, we will calculate the rate by units and press enter, then here our net amount will remain calculated, select the complete column, press control D and We will apply the same formula in all, to make it a formula, we will press control and format cell dialog box will open, in this we will come to the currency, we will round the decimal places and select its symbol, from here OK and something like this Our electricity bill will be calculated and then friends, these were some simple steps which you can follow. You can calculate your electricity bill very easily. I hope you have learned something new from this video. If you liked this video, then like this video. Do subscribe to the channel. I have uploaded this spreadsheet in the description box of this video. So from there you can work around by downloading this sheet, that's all for today, see you in the next video, thank you.