Automated Invoice in Google Sheets for Teams that Enhances Collaboration

Automated invoice in google sheets for teams

hello friends and welcome back to my channel so if you're running a small shop or if you're running a small thing in which you require a bills but you don't want to pay a hefty charge for the person who is making a software this google sheet will help you to learn the things much more better so i have made a small invoice for you for example if i'm just selecting a soap the price will be automatically coming over here i have to just enter the quantity i will get the total price suppose i'm entering clause obviously i'm entering the quantity i will get the total price and even i will get the grand total and yes even you can just click over here and you can print it and give it to your patient and after your things are over you can even click over here it will get it here so if you want to learn this please watch this video from beginning to end properly okay so what we will do over here we will divide this into two parts first sheet we will just open uh sheets.google.com and we will enter with our email id and password simple like that now obviously we will what we will do we will just divide into two parts we will write down the products whichever products you are having you can just list down the product it is a one-time process only you have to list down the product and there is a price also you have to write down the price like this okay so here are the products and here are the price now comes the main part that is sheet number four obviously what you have to do you have to enter your company's name or not so suppose suppose your company name is abc company okay so you have to enter your company name obviously you have to write your client's name so if you want you can enter your client name also you want to write the date so you can write down the date so equal to today so today will give you the today's date if it is required so you can see that it will be 7th of may 2021 so like that you can just give it obviously you have to write down the medicine name or the product name whatever you want to write down you can write down over here you can even customize this there is no problem okay you will write down the quantity whatever things are required in the uh bill you can write down the quantity you can write down the price per unit okay so i'm just writing price per unit and the total okay so these are the very very very basic things that i have told you okay so if you want obviously you can even like this make a match so you can see that it looking little bit go okay if you want you can increase the size also by over here the font size little bit i want to increase okay i want to make it little bit proper so it is completely your choice how much you want to make it proper it is your choice only and you can even color this also whatever you want or better you just go to data and there is an option of alternating colors also uh in the format i'm sorry you can just go to alternating colors so you can see that okay the colors are been alternated okay so you will automatically see so i will select all this and just make it like this okay so it is looking little bit proper now obviously you will write down the client names over here and if you need extra space you can just drag it like this okay so this is done so this is a very basic things you can even make it you can color it you can whatever you want to do you can do it okay that is completely your choice but i am here to just share you with the formulas also so suppose if you want to make a black color you can make a black color if it is disappeared okay you can make a very light color suppose this like this it is completely gorgeous now here we have to write down the products okay so what we will do to write down the product so for that what we'll go we'll go for data and in that we will go for data validation you can see over here data validation if you have not checked my previous video please see in the description box i have given the link you can check in the previous video how to use data validation so i will just enter the range okay so i will go to my previous sheet and whatever range are there suppose i am having chocolate 2 like this i will just select over here and i will just select it one more time so you can see that it is a range okay if you want you can just uh remove this file so all the values of a it will be selected and you can just press ok okay and you can just save it okay so you can see over here now you will in the sheet for there is a small arrow and from here you can select the items if you want to drag this up to the end what you can do you can just click over here and drag it to everywhere you can see there is an arrow and suppose if you just type c it will just give you chocolate okay directly so it is done like this if you want to type m so it will just give you a mask here and you can see that it is very simple and basic thing so i'm just erasing all these things quantity obviously you're going to write down by yourself now the main thing over here is how we can go through for the price so for the price we have a very good formula that is known as vlookup so you just type on vlookup very simple formula those who have not seen my previous video please share the in the data validation i have given a very detailed thing how we can use vlookup formula so we look up from for example i want a quantity price of mass so what i will do i will just click over here the value of a4 here whatever i will select i want the price over here so from where to where you have to select so i will go to my previous sheet and over here i will select all these things okay from here i want my formula to look up for the price okay once it is done comma then from where you have to see the price from the first column with the second column so obviously i will select the second column and i will select false okay so i'll just do that and i will just enter it let us see it will show not okay it didn't find out but why because i have not selected any of the values so now suppose if i select a chocolate you will see that 20 rupees mask is of 200 rupees and 95 is of 90 rupees now you can see that the price is coming up okay so suppose if nothing is there okay for an example there is nothing and it every time it shows any it will look very much bad so what i'm doing i'm just adding over here if error okay so suppose if there is an error in the formula it can just not show the value i don't want to show any value if there is an error in the formula so you can see now nothing has been shown but suppose if i select a chocolate you can see that automatically the price has been shown so this is the way the first thing that you have to do you have to set the price is okay so i'm just dragging this formula but before dragging this formula you have to fix your range because you don't have to change your range i will just select this okay i will just select a2 to b5 and i will just press f4 if you press f4 the range has been selected okay so now obviously i will just copy down the formula and now everywhere the formula is approx for example here if i select a chocolate you will see that now there is a price of 20 rupees so very simple very basic so i hope you understand up to here now obviously i want the total also okay i want the total also so is equal to what i will do the quantity multiplied by the price okay very simple it is this into this but before see if i don't enter a quantity it will show very much bad in the things so what i am doing i am using a little formula if suppose if it is blank is blank means if there is a blank okay so i'm just entering is blank okay is blank this one okay this one i'm just telling okay suppose i'm sorry so if i just erase this i will just select this blank this one b4 if it is blank then you have to enter nothing that means nothing or to enter otherwise okay comma otherwise what you have to do the quantity b4 into okay into mean star into this c4 okay and then you have to close it so it is very simple right now nothing has been shown let us check what is that so suppose if i select a chocolate which is of 20 rupees if i enter a quantity a person wants 12 chocolate or tomato you will see that okay the price has been 240. uh i think i forgot one thing that we need even the grand total if i'm not wrong we need a grand total also so i will write down over here grand total okay of the person what is the grand total of the person and yes is equal to very easy some sum some means addition addition of what addition of all these things okay whatever he selects over here i have to select and this it will show the grand total okay i will make it little bit proper this also i will just increase the font it is required okay the grand total should be little bit big and obviously i will just put a matching also okay so it is looking a little bit good so our obviously all the things are ready and clear now just let us test run whether everything is running properly or not and then i will show you how to clear it also very simple it is so suppose if i just select a chocolate if i select mask and if i enter the quantity 12 rupees so okay sorry i have not dragged the formula so i will just check the formula okay so now you can see i just have to select and i have to subtract the formula of the mask is this suppose if i select uh for example n95 mask if i buy n 10 and 95 mass so it is 900 and you can see the grand total has been also shown i can click on print also you can see i can just select on print and then i can just print it very much easily i can give the name also okay so i will just put it hundred percentage so you can even see the grand total also okay so you can see the grant code also and you can print it also very much easily okay so you can see and you can print it and you can give it to your customer now problem is that every time if i just keep on deleting this quantity and it will be little bit difficult for me okay suppose i have to select all the things i have to press delete each and every time so better i will show you a trick how we can just put a clear button over here and whenever it is required you can just click on clear button and everything will be cleared very much easily so for that what you have to do you have to go to the tools and there is an option of macro now macro means what it clears the things that you are doing okay so for example i'm just showing over here for a reference because suppose if i select a chocolate and the quantity is 12 so you know that the price is 240. now what i will do i will go to the tools and i will just click over here and here record macro so right now the macro has been recording so what i will do i will select i want to delete the product name and the quantity each and every time so i will select all the things and i will press delete button okay so you can see over here the values have been removed so i will just save this and give the name as whatever you want to just give the name so reset suppose i'm giving the name reset you can give any name whatever you want and just save it okay so it is saving new macro and i will show you how it will work so suppose if i just select a chocolate if i just select 12 quantity 240 rupees bill has been generated it is printed now i don't want this braille again so what i will go i will go to the tools i will go to the macros and there is option of reset so if i just click on reset you will see that everything will be deleted okay but i want a button over here i don't want to just go to tools and every time so what you will do over here you go to insert and you will go for drawing so it is completely your choice okay whether you want to go for drawing and you can do it for any time wherever you want okay this google sheet is very much useful it is not at all so i will go for some shape or maybe some call out whatever you want okay shapes will be good square shape i will just click on square shape and here i will write down reset okay or any name whatever you want out this is not at all required reset save and close so you will see that in a small time there will be a button i will just drag this button over here and over here i will click on this and assign a script the script which we have already written so i i have already given the name of the script as reset i will just press it okay so now this will be a button for you i will just make it little bit small this button so it will look good also okay so i'm just making it small let us see whether it is working or not so suppose if i then chocolate mask okay 12 rupees 2400 suppose client name i will write down as so abc okay date is given today only there is not a problem suppose mask gloves 13 rupees you can see that 195 rupees so grand total has been done now i have already printed this and given to the customer now i want to just do i will click over here automatically you will see that in one minute it will get resetted also so this is the way you can create a build very simple very basic there is no need to worry too much okay you can just pause and look for the formulas also properly and you can customize this also whatever way you want you can make it much more cleaner also you can do whatever you want you can make a customized things okay so and yes obviously you can even keep a transaction id also which can be done so if you want to look at the same kind of videos please like share and subscribe my channel and keep on tuning thank you and have a nice day