Python Excel: Effortlessly Insert Formulas for Advanced Data Manipulation!

hey guys in this video we are going to learn how to insert formula into Excel to keep it simple I have got a simple Excel file where I have got column A and column B and I have a column C in this one I have applied a formula equals to some a to B2 now if you go to the next row it says A3 B3 now if you go to the next row it says a for V4 So based on the location of the cell the formula number is changing if you see A4 B4 if I go down below it will say A6 B6 now how this formula I can write using python let's see that first of all I need few libraries which is the library that I'm going to use here is open by Excel I'll use Open PI Excel and then I'm going to say import load workbook so this is the load workbook function I am going to use for this specific task now the next thing is we have to Define where our file is in a variable so I'm using file path equals to and I'll go to the specific location where it is there shift right click and then copy as path and I'm going to paste it with a r string okay so this is how you'll be writing the file path now once you have written the file path I'll create a simple variable called a equals to and using this function load workbook function I am going to load the file path into the variable a so that means the a variable will become a temporary variable which will hold the entire Excel file done now your Excel file might contain multiple different sheets so you have to Define which sheet to work so I need to work here in the sheet a right so I'll create another variable called B and using the a variable within the square bracket I am going to Simply say sheet 1. clear now B is holding this sheet one this much is clear now the main part here is to learn how can I iterate through this row so we will I will delete all of this okay I'll delete all of this I need to iterate through all of this rows and finally write a formula here right so for that I need to learn how to iterate through for that I will use a simple variable called R which represents row you can use any variable you want and then I'm going to say in then I'm going to iterate through this B sheet right the data that is there in the B so I'll say B dot there is something called iter rows okay using this function I am going to enter through the B sheet data that means the sheet1 data okay b b is a variable put a colon and then let's see what is getting printed okay only when you see what is getting printed you will have understanding what is the meaning of this particular code now what is happening I am iterating through the B that means I'm iterating through the sheet 1. so what exactly it is showing hey when you enter like this the first time when it iterates it says A1 then beside that there is a B1 then beside that is C1 that means it is iterating like this so first row first row like this A1 B2 B1 and C1 then we'll enter to this one A2 B2 and C2 getting it is the second row this is the first row right the columns will remain constant so the row number is changing if you look at the output see a two three four like that the column name is same only the row number is changing getting it C3 C4 like that now what would happen okay what would happen if I am going to write something like this I am saying 0. dot value look at this look at the output first okay don't try to um you know mug up anything just try to see how it is working then you will automatically understand so when I'm writing r r represents what here R represents what rows okay R is representing rows okay now when I'm writing Square zero that means this is the column so if you see the First Column is represented by the index number zero row starts from one two three columns are indexed from 0 1 2 like that so here it is saying when I'm saying row 0 means it is pointing out to this particular c row 0 value so that is means it is telling row 0 means you are telling column A when I am saying rho b means Row 1 means column B if I write a Row 1 0 1 2 2 if I write it will give this some column so look at this the output is given the entire column A clear now if I write experiment all this small small things then it will be clear then we will be able to write what we need now look at it what happened column B has come the entire column B got printed now if I'm going to write R 2 that means it is going to represent the sum column now some column is uh all blank right I still see the formulas let me delete the formulas and save it okay I did not save it that's why so let me run it again so this is a representation of the column so let's go back let's say 0 0 means what is coming the column A is coming now what would happen there is another function there's another method uh called uh dot row okay so what would happen by writing dot row look at it it is going to print the row numbers that means your sheet has got six rows if you see the first time it ran one two three four five six now if I compare with the Excel one two three four five six getting it so remember this is very important point here what we have learned is this one when I am writing R 0 means I am representing the particular column and Dot romance it is telling me currently which row I am in so when the loop starts it is in the row one then the loop start then it goes to row two then it goes to row three then it goes to row five like that it goes okay this is one point okay very important point R zero dot row means it is only giving the row numbers what it gives it gives row numbers clear okay there's another point because if you look at I don't need this Row one okay I need to work with from row two so how do I restrict that I can restrict it by simply writing Min row there is something called here in the parenthesis I'm going to write min underscore row is equals to 2. I want to start from 2. now if you see it was printing from 1 if I run it now it will print from 2. do you see two three four five six so this was clear okay so we don't actually need this just remember this R 0 means row number okay R I will use this particular thing several times so remember R square bracket 0 means this is the column dot ROM is it just gives the number it doesn't matter what you write if you write one also it will give the same output one is the column but which row you are in so dot ROM is which row you are in it gives the same number so I will just keep it zero okay so this one is we are going to use several times so remember R zero dot row means row number clear let's proceed Let's cross it up now I need to Target my formula where but in this in this particular area right to a particular cell I have to Target to a particular cell so I am writing b b means what the sheet 1 right B variable contains everything and here I am writing cell and within the parenthesis there are a couple of things that we have to use a cell but which cell so every cell will have a row it will have a column isn't it it will have a row and column so same thing I am going to say rows within the parenthesis say row is a parameter and which row the row value is nothing but just now we learned r 0 dot row which is nothing but a number instead of writing 2 I am writing this so when the iteration will happen it will have this number two three four five like that so I'm saying rho is equal to whatever the iteration is happening for the r variable you give that value to rho done now I am saying column parameter column so column is constant column is are what the sum column right one two three column is or some column so I am saying column number three column number three and row will have this value done now what is the value you like to pass now every cell may have a value so here our objective is quite simple I have to pass a formula here so what is the formula let's write it it's a string right so I'll use let me minimize this and let me write the formula our formula is something like this sum A2 okay colon B2 but here the trick is this two this numbers has to keep changing now we got what is the secret r dot zero dot row will be keep changing right so instead of writing a fixed value I am going to write this formula here now remember when this kind of variables you are using I'll be using something called format okay so when you're writing a big string and there are multiple variables that you have to use inside this thing simply say ffms format and here instead of 2 I'll use curly braces and instead of B2 I'll remove that and use curly braces inside the curly braces I am going to just paste it paste this and paste this so this is the numbers will keep changing as the loop is running so it will become A2 then you become A3 B3 A4 B4 like that will keep changing so that is the secret okay so once this is done okay it will Target that those specific cells and then it is going to write so after you have done it remember to save anytime you will be dealing with workbook you have to save it so a DOT save and I'm going to provide the file path variable here done so let's save it and before you start running ensure no formula is there okay let me close it it's a very simple code okay very simple code easy to understand let's run it so process finished so let me go here Let me refresh so it is not appearing like this in the uh preview let me open the Excel file now if you look at the Excel file contains all the formula and my output has come so pretty simple code easy to learn easy to understand so try this out on your computer and let me know if you are successful if you are able to do it please let me know yes you have done it just comment yes I have done it it'll be nice to see your comments thank you guys for watching in case you have not subscribed please do subscribe and do like the contents that you're watching on my channel thank you take care bye

Channel: Automate with Rakesh

Views: 797

Keywords:

Id: 63jcqg-24zo

Length: 11min 59sec (719 seconds)

Published: Tue Aug 08 2023