Create a Proforma Invoice in Excel for Banking Effortlessly

Proforma invoice in excel for Banking

In Today's video we will see how we can  create a Fully Automated invoice in Excel.   Either you are a freelancer or Business owner,  this invoice template will save your lots of   time. In this tutorial you will see how we  can generate a Professional invoice in Excel.   Hi my name is Vijay and you are watching Office  Monk. Let's start the video without any delays.   This is an invoice which I have already  created in advance. I'll show you how it   works. We have to insert few details over  here like invoice number invoice date,   reverse charge it will be managed by drop down  yes or no State we will have to insert it manually   state code we will have to mention name over  here of the company name of the client address   GST number I'll insert a random figure over here  here we have to provide product details which will   be managed by drop down I'll select a product  from here desktop and HSN code got picked up   automatically quantity we have to insert manually  even the rates will be picked up automatically   discount we will have to provide manually I'll  take a random figure we will come down here we   have to provide Bank details Bank name branch ifsc  code terms and conditions if any you will have to   provide it here I'll do it none I'll select few  more products over here I'll provide quantity   and discount percentage if applicable so all the  entries got done I'll come up and I'll click over   here on save a box will pop up where we will get  an alert Microsoft Excel will permanently delete   this sheet do you want to continue delete our  Excel file got blank and our PDF file got saved   in our invoice folder I'll show you this is my  invoice folder I'll open it and this is the tax   invoice or invoice which we have created right  now invoice number one two three we will rename   it invoice number one two three to create this  file we will record a macros and we will use our   VBA code over here we will open a new file we  will need few details like product description   HSN code and rate we will come over here this  format is totally blank we are not going to   create format because formatting path takes lots  of time we will just insert formulas over here   and will record our macros invoice number  om101 invoice date I'll take today's date   reverse charge we will create drop down  for this we will click over here we will   go in data data validation we will select  list in Source we will type yes comma no   we will do okay and our drop down got created  we will select yes from here State Maharashtra   state code I'll type company name over  here Jacob and Company name Jacob address I'll take a random figure now we will insert  formulas over here formula for serial number   will be plus or equal to rows bracket open we are  going to start our account from cell number A10 so   we will come over here we will select this A10 we  will press function F4 and the cell will be logged   colon we will select 18 once again we will close  the bracket and we will do enter so we are able   to see serial number over here we will insert a  criteria we will click over here just behind or   before rows if bracket open this cell is equal to  0 comma double inverted comma twice comma and we   will close the bracket in the end and we will  do enter so our formula got inserted over here   and we are not able to see serial numbers once  the entry will be done over here serial number   will be visible now we will create drop down  for products we will select all this we will   go in data data validation we will select list we  will click on source and we'll go in setting sheet   we will select this and we will do ok so product  list got created we will select a product HSN code   we will insert formula for it and the formula  will be plus or equal to vlookup bracket open   we have to provide lookup value so this will be  our lookup value comma table array we will go in   setting sheet and we will select this HSN number  is in column number 2 1 2 so we will type 2 over   here comma 0 for exact match bracket close and we  will do enter so formula for HSN code got inserted   we will insert a criteria just before vlookup  if error bracket open we will come in the end   comma double inverted comma twice bracket flows  and we will do enter we will drag this till end   quantity we will have to insert it manually  I'll take a random figure rate we will insert   the formula and the formula will be plus or equal  to vlookup bracket open lookup value we will take   product description comma table array we will go  over here we will select the whole table comma   rates are in column number 3 so we will type  3 over here comma 0 we will close the bracket   and we will do enter we will drag this till end we  can see one error over here so to avoid this error   we will insert a criteria we will click over  here just behind vlookup if bracket open this   is equal to 0 comma double inverted comma twice  comma we will come in the end we will close the   bracket and we will do enter we will drag this  till end we will go in HSN code and we will lock   our sales over here we will click over here just  after B3 we will press function F4 we will click   after D12 and we will press function F4 once  again we will hit enter now we will drag this   till end we will insert dollar over here also we  will click after B3 and we'll press function F4 we   will click after D12 and will press function F4  we will do enter and we will drag this till end   quantity we will insert manually so the amount  will be plus or equal to rate into quantity enter   we will drag this till end we can see some error  over here so we will insert a criteria if bracket   open this cell means Product cell is equal to 0  comma double inverted comma twice comma we will   come in the end and we will close the bracket  and will hit enter we will drag this till end   discount we will have to insert it manually total  value so the formula for total value will be plus   or equal to amount into percentage enter so we  got discount value over here we will come in the   beginning we will click on the amount minus we  will insert bracket we will close the bracket   and we will do enter so this is our taxable  value after discount we will drag this till end   again we can see some error over here so we  will insert a criteria if bracket open this   is equal to 0 comma double inverted comma  twice comma we will come in the end and we   will close the bracket and we'll hit enter  we'll drag this till end so all the formulas   got inserted over here total amount before  tax it will be plus sum we will select this   will close the bracket and we'll hit enter  we have to add cgst and sgst percentage so   I'll take a random figure nine percent for both  formula will be plus this into percentage enter   over here formula will be same plus this into  this in case GST percentage changes you can   change the value from here 12 enter and the  total value got changed so grand total will   be plus this plus this plus this enter so this is  our grand total total invoice amount in words to   get the amount in words we will have to insert  a VBA code this is our VBA code I'll copy this we will come in our Excel file we'll go in  developer tab we will go in Visual Basic we   will go on top insert module and we will paste  our VBA code over here now we will go in our   Excel file and we'll insert a formula over here  Plus or equal to spell number we will select this   and we will select this amount means grand total  we will close the bracket and we will hit enter   we will click over here we will go in home and  will wrap the text so our amount is visible in   words over here we will come down we will click  over here on signatory authorized signatory we   will go in insert and we'll insert our signature  over here illustrations picture picture from file   this is my signature I'll insert it I'll resize  it and will reposition as per our requirement   Bank details bank account number Bank name  branch ifsc code terms and conditions none   none none none now we will insert a shape over  here we will go in insert illustrations shapes   and I'll selector shape over here I'll double  click on the shape and will insert text save   I'll go in home we'll take this in center and  in mid we'll increase the font size I'll take   this little bit on left side now we will record  our macros to record the macros we will have to   go in view or developer tab over here we will get  option of record macro we will click over here and   we'll rename it save okay now we'll click over  here on Tab name we will right click the mouse   move or copy move to end create a copy okay so  our copy got created we'll click over here on the   shape and we'll delete this we'll select the whole  sheet Ctrl C or command C we will right click the   mouse paste special values we will press escape  and we'll click outside the invoice now we will go   on top file save as and we will select our folder  wherever we want to save the invoice in PDF file   we will select PDF from here here we get option of  workbook and Sheet we will click on sheet because   we don't want to save the whole workbook we will  save it now we'll click over here we will right   click the mouse and will delete this invoice this  is the copy which we have created right now delete   we will select this and we'll delete  this we will select this and will delete   you select this from top to bottom delete delete  you will have to delete from top to bottom because   whatever command you will be giving to macros  it will be recording that only delete we will   come down we will select this and we'll delete  we'll select this also and we'll delete we'll go   on top it'll click over here and we will stop our  macros so our macro got recorded we will come in   our folder where we have saved our invoice in PDF  format we will rename it rename so invoice number   was om 101 every time you will save the file you  will have to rename it in case you will not rename   the file new files which will be generated will  Auto replace the old file now we will go in our   Excel we will click on the shape we will right  click the mouse assign macro and we have created   this macro over here right now we will select this  and we'll click on OK so our macro got assigned we   will do some entries invoice number om009 invoice  date reverse charges we will select from drop   down yes State Kerala state code I'll take a  random figure of two office Monk and Company   name Vijay address Kerala gstin I'll put a random  figure product description we will have to select   from drop down desktop earphones Mouse TV  fridge quantity we will insert it manually   discount percentage you will take a random figure  in case discount will not be applicable leave it   planned we will come down our signature is  already over here Bank details Branch name   Kerala ifsc code terms and conditions none  in case you want to leave it blank you can we   will go on top and we'll click on Save here we  got the alert Microsoft Excel will permanently   delete this sheet you want to continue delete  so the whole sheet got blank we will go in our   invoice folder and we'll check the file this is  the PDF file which got created right now invoice   number is om009 so we will rename this file we  will right click on the file rename om009 every   time the PDF file will be saved you will have  to rename it otherwise new files will replace   it so our invoice format is ready now I'll provide  link of this file as well as VBA code file in my   description box in case you want to download it  you can. I'm sure you must have loved the video.   Please don't forget to Like, Share and Subscribe.  See you soon with a new topic. Till then Bye, Bye