Unlock Efficiency with the Google Sheets Invoice Tracker Template for Life Sciences

Google sheets invoice tracker template for Life Sciences

Hello everybody welcome back and in today's video we are going to learn how to create a invoice and payment tracker template in Google Sheets and uh here you can see already developed invoice and payment tracker template and if you notice here here you can see the different column headings are given and if you see here there are some formula also used in the number of days I have used formula in the difference column I have used formula similarly in this stage taken for payment I have used formula and in the payment status I have used formula and this payment status column is very Dynamic column based on the data we feel here or based on the invoice and payment track we record here this payment status colum will automatically update for example if you notice here you can see different kind of values are given the clear partially clear overdue and is given here and all these values will automatically change ing to the values we record here for example if I make this payment reip which is less than the invoice amount for example 20,000 rupees in this case you can see it is partially cleared because the invoice amount was 25,321 and the received amount is 20,000 only so it is given as partially clear because we don't have received the full amount for this invoice that's why it is automatically displaying partially clear here so this template is dynamic template because the formulas will make this template a dynamic by automatically changing the values so in today's video we are going to learn how to design this invoice and payment tracker template in Google sheet and the benefit of creating s template in Google Google sheet is you can share this sheet to multiple users and when the users update values to this seat then you can see or you can access this file from anywhere so the benefit of using the Google sheet is we can share the file to multiple users for example if you are boss in a company and if you want to uh share this file to your employees to update the file then you can share it to the employee and when your employee upgrades value here then you can access the file or you can view the file from anywhere so this is the major benefit of creating such template template in Google sheet and in this video we'll learn from the beginning how to design this invoice and payment tracker template from step byst step process and before starting uh making this from beginning first of all I will show you what are the feature included here in the template and what are included in the column headings here if if you notice here the invoice number is given on the First Column here we need to record the invoice number similarly the second column contains of dat of invoice the third column contains customer name and in this invoice amount we need to write the amount of invoice similarly this number of days we calculate by using formula the next one is invoice due date and this is this will also be calculated by using formula the next one is payment receip and here we need to record the received payment the next one is difference and this difference sale we need to use formula to calculate the difference value the next one is debit or credit amount for adjustment here we have to record the debit or credit amount of the customer to adjust this difference amount similarly the date of Reed we have to put manually when we receive payment from the customer then we have to record the date of payment re here in this column then the dayses taken for payment we need to use formula and here in the payment status column we need to use formula to display whether the payment is partially cleared fully cleared or overdue or due so this is all about this uh column headings and if you notice here in the row number three the total is given here and here the sof total formula is used because when we want to view the record of a particular customer here by using filter then in that case the sum of filter data will be given here and based on these values this total outstanding amount will calculate let's go to the new seat here and create this invoice and payment tracker template in Google Sheets from step by step process so here I'm clicking on this add seet button to create a new seet here now here you can see a new seet is created and here in this blank sheet we are going to calculate the we are going to create the invoice and payment tracker template so here I'm going to this uh invoice and payment tracker then directly copying this title from here contrl C then going to this seat number five and I'm pasting this Title Here contrl B then I'm also copying the column headings from here I'm s cting this then contrl C then go to the new seat and here I am pressing contrl B to copy the titles now here I am decreasing the volum width of this titles by simply decreasing the column width in this way now I am increasing the column wide for the customer name because this needs a little bit wider column here in this way then I'm selecting some rows here for example I am making this till row number 17 and I'm going to the Border option and selecting all border from here so after doing this then here I'm going to create some random invoice numbers for example 101 102 103 104 the date of date of invoice is suppose Jan jary 1st 2022 and then January 5th 2022 then I'm writing January 7th 2022 then January 15th 2022 for example then here I'm giving some customers name which is customer a customer CD then again customer AB then customer DF now here in this invoice amount I'm giving some random invoice numbers just for example in this way and here in this number of of days we need to use a formula to calculate number of days which is calculated by subtracting this in date of invoice from today's date so here equal to then type today formula Open Bracket close bracket minus then the date of invoice so this will give the number of days which is 43 days if you subtract this uh first of January 2022 from today's date which is 13th of February 2022 then the number of days will be calculated here which is 43 and now you can simply drag this formula till the last row here and when dragging this simple formula here if you see when there is no data then it will it is calculating the result here which does not look good here in this template so if you want to remove these values from here if the invoice number or if this data is blank here then in that case you can use this formula technique so you you can go to this formula and combine if formula here to remove these unnecessary results here so go to the beginning of the formula and combine a formula here which is if then Open Bracket type if then Open Bracket then here in The Logical expression you can check whether the invoice number is blank or not so click on invoice number then equal to then type double quotation which represents blank in spreadsheet formula now give a comma and here in this value if true if the sale is blank here then we need to display blank here that's why here in the value if true type double quotation again now give a comma and here in this value if fals this today minus the date of invoice formula will work now go to the end of the formula close a bracket and hit enter now here you can see the result is calculated now you can simply drag this formula till the last row go here then drag this formula to the last row which will remove all these unnecessary values from here if this sale is blank then it will display blank here and if the cell is not blank then it will display value here for example if I put another invoice number here in this case it will start showing the value here and when you put the date here then it will calculate the number of days here if I make this January 16th 2022 then here you can see it is calculated as 28 so in this way this formula works here and we will apply this formula technique for all the other column which contains formula to avoid unnecessary result here in the blank rows so let's go to the invoice due date and here we will calculate the value using this formula suppose that the credit limit is 30 days so in this case the invoice due date is calculated by using the formula here equal to then combine if formula to avoid unnecessary result in the blank rows if this invoice number is blank equal to blank then give a comma and in value if true type double quotation to represent blank then in the value if false the actual formula will be written which is this data of invoice plus 30 this will give the invoice due date now close bracket and press enter now simply drag this formula till the last row here in this way then it will calculate the invoice due date which is calculated by adding 30 days in the date of invoice so here in this payment receiv we need to update value manually when the customer pays payment for these invoices then we will record the payment reip here similarly in the difference column we need to use a formula which is equal to if bracket open if the invoice number equal to blank then it will have to display blank here then give a comma and here in the value it falls we will calculate the difference amount which is calculated by substracting the payment receipt from invoice amount click on invoice amount minus payment receipt plus then we need to add the debit or credit amount which is given on I5 sale here so here directly type I5 because we cannot select this sale because of this formula here that's why directly type I5 here now close bracket press enter now the difference is calculated now drag this formula till the last row in this way similarly here in the debit credit amount we need to put the value manually because if the customer has some credit amount we have to put it in minus figure and if the customer have some debit amount then we have to put it here in plus figure the next one is date of receip and here we have to record the data manually when the customer pays payment then we have to put the payment date here which is date of receip then here in this days taken for payment we have to use formula it calculates in how many days the customer paid us the payment which is calculated by subtracting the date of invoice from the date of receip so here in this case we can use this formula equal to if then Open Bracket and here in this case we will check whether this sale is blank or not so click on this sale then equal to blank give a comma then in the value of two display blank if the date of receip column is blank then we will display the value here is blank then give a comma and then in the value if false we will write the actual formula to calculate R taken for payment which is calculated by subtracting the date of invoice from date of receip so click on this date of receip minus the date of invoice click on this then close bracket and press enter now here you can see the sale is given as blank because we don't have any date of receip here that's why it is given as blank when we F the data prip here then it will calculate the value here for example here I'm showing you if this customer AB paid the payment in here which is suppose 10,000 rupees in this case here we need to write the date of receipt for example this customer paid payment on February 1st 2022 here in this case it is calculated as 31 because the customer paid us payment for this invoice number 101 after 31 days of the invoice date so in this way the days taken for payment is calculated here and here in this case the difference amount is 254 rupees and if this customer has some credit amount of 254 then you can write it here in minus figure minus 254 then it will become zero so in this way we can write if there is any credit or debit amount of the customer to adjust this difference amount if there is no any credit debit amount then we have to make it blank in this case this invoice is known as partially cleared because we have still 254 rupees remaining for this invoice so here in the payment status we will display the status of payment ing to the figure we have updated here as you see in this previously buildt uh payment tracker template here you can see partially clear clear overd du is given so next we have to calculate these values here by using formula and here in this template the most important formula we are going to use is the payment status formula which is little bit complex in comparison to this uh formula we previously used here so you have to be very careful while developing formula here in the payment status qu so let's start learning how to use the formula here in this payment status now go to the payment status column here then press equal to and here we need to test for multiple Logics and uh display values so here you have to be very careful while developing the formula equal to then we start with ifs formula then Open Bracket we need to use ifs or ifs formula here and here in this first condition we are going to display whether the invoice is clear or not and uh here we have to use this formula click on the difference amount and when the difference amount is greater than or equal to zero in this case the invoice is supposed to be clear so here we need to write this logic here then give a comma and here in this value one we need to type clear between inverted comma type inverted comma or double quotation then type clear then inverted comma close then give a comma so we tested the first logic here in case the difference amount is greater than or equal to zero then this invoice is supposed to be clear so here we write the logic and the value then give a comma and here in the second condition we need to check whether the invoice is partially cleared and for the invoice be partially clear this different difference amount should be less than zero should be greater uh this uh this uh this difference amount should be greater than zero so here we need to use this formula here we need I'm again deleting this formula because here we need to write less than for the invoice to be clear so here I'm again writing the formula equal to IFS Open Bracket the first condition is if this difference amount is less than or equal to zero then give a comma then the value one will be clear so type double quotation then clear then double quotation close now give a comma and for this invoice be partially clear we need to check for two different Logics and to check two different logic here we have to use the and formula so type A and D and then Open Bracket and here in this logical expression we need to click on this difference amount then we need to we need to type the we need to type the greater than sign then greater than then type zero this is a first logical expression for the and formula then give a comma then the second logical expression we need to test here is if this is greater than zero then if this value is less than this invoice amount after satisfying both of these two Logics then here in this case this invoice is this invoice is known as partially cleared so type comma here then type double quotation then write partially do p dot clear in this way now type double quotation again then give a comma and here in this another logical expression here after uh writing these two Logics here we need to close a formula so go here and close this and formula here then go to the end of the formula and here we need to check for another condition which will identify whether the invoice is overdue or not so here we need to check this logic click on the number of days then greater than 30 if this number of days is greater than 30 then this invoice is supposed to be overdue so here give a comma and type overdue between the inverted comma overdue then inverted comma close now give a comma and here we we need to test another logic which is if this number of days is less than or equal to 30 in this case this invoice is supposed to be due so here we need to write inverted comma then D du comma close now bracket close now this ifs formula will calculate values here in this payment status column and this is a dynamic formula now you can press enter and here you can see partially clear is given because the invoice amount was 10,254 and we received 10,000 rupees only that's why this is known as partially clear if we receive 11,000 rupees here then in this case it will show as clear because the invoice amount was 10,252 and we received ex's amount here that that's why it is given as clear similarly if we have received 10,000 rupees here it will show as partially clear and if we have some credit amount for this customer which is suppose that 254 rupees then we will enter it in minus figure in the credit amount now hit enter in this case it will give as clear so in this way this works and for example if you delete this uh from here and then if you also delete the payment reip from here then it will show is overdue because the the number of days for this particular invoice is already 43 days that's why it is given as overdue so in this way this value will automatically update here ing to the values we feel here now you can simply drag this formula till the last but before dragging we need to again combine the e formula here so that if these values is blank then it will show blank here so go to the formula here then go to the beginning of the formula then combine if if formula here bracket open then click on the invoice number equals blank give a comma and here in the value if true type blank again give a comma and here in this value if false this entire ifs formula will work now go to the end of the formula close bracket now hit enter now you can simply drag this formula till the last row by going here in the plus button and dragging this using your mouse now here you can see the values are automatically calculated here three invoices is already overdue and one is due because this invoice number of days is 29 only that's why it is given as due and if the number of days is greater than 30 then it is showing us overdue so in this way this system works here now after developing this formula here now our duty is to here I'm zooming the Windows size to 110% now our duty is to automatically color highlight the rows ing to the value given here for for example here you can see the automatic color highlight is given and to do this we need to use conditional formatting so select this entire data range from here in this way then go to format option then go to conditional formatting and uh here we need to we need to go to the formula option here go to custom formula is option then here we need to use the formula our value is given on the L column so here we need to type equal to then dollar sign then L five because our data starts from row number five here that's right type L5 then equal to then here we need to write inverted comma then clear then inverted comma close if the value in L5 cell is clear here then we need to highlight the row with blue color so choose blue color from here then click done now if the value given here in the L column is clear then the entire row will be highlighted with blue color then we need to apply another conditional formatting here so again go to format then conditional formatting and here apply another rule click on another rule then here again choose the formula is option custom formula is option then in the value in this formula type equals then dollar sign then L5 dollar sign then L5 then equal to then inverted comma then here type partially dot CER inverted comma close if the sale value is partially clear then the formatting of the row is yellow color highlight so choose yellow color from here then click done similarly add another rule here click on another rule then go to custom formula is option then go to the value or formula field here equal to Dollar then L5 equals then in ined comma then overdue inverted comma close in this case the Highlight for the sale will be go to the formatting option here then choose this red color highlight for overdue invoices then click on done now here you can see the conditional formatting is applied and in case of the in case if the bill is due then we don't need to use any highlight here so now using conditional formula is completed now you can close this now here you can see the color is automatically applied if the sale value is overdue then it is highlighted with red color and suppose that for example if you received payment for this uh invoice number 101 then here you have to record the payment reip amount here I am writing amount also payment received then amount and here if you enter the payment received amount for example if you receive 5,000 rupees only for this invoice then it is given as partially clear because we received partial payment only here similarly if you make this exactly same with the invoice amount which is 10,254 in this case you can see it is now given as clear because this invoice is fully cleared similarly if you make this 9,000 rupees and if you put the credit amount of minus 1254 in this case also the invoice is given as clear so in this way this payment status colum dynamically calculates value and automatically applies the color highlight ing to the value given here so in this way the system is fully automated here and if I delete this debit or credit amount from here if you delete this debit and crit credit amount from here and then make this 10,254 then you can see it is given as clear now similarly for the invoice number 102 suppose that if you receive 15,000 rupees only here then it will so is partially clear because the invoice amount was 15478 and we received partial payment here that's why it is given as partially clear so in this way the system works here and if you notice here it is given as overdue because the number of days is greater than 37 that's why it is given as overdue if you reduce the date of invoice here for example if you increase the date of invoice here to uh January 15 2022 then it is now given as due because the number of days is 29 days only so in this way the color coding is automatically changing ing to the values we change here for example if you receive payment for this invoice number 103 suppose if you receive 30,000 rupees here in this case it will give as partially clear if you fully receive the payment of this invoice for example 32147 in this case it will give a clear so in this way we develop this invoice and payment tracker template in Google Sheets now here if you go to the previously build payment tracker you can see some values here and to display these values here we can use the sub total formula so here you can use sub total formula equal to sub tootal bracket open then in the function code we need to type nine for the Su function now give a comma and here in this range you can select this data range from the top row to bottom row in this way now close bracket and hit enter now here you can see some of the invoices you can make this you can give some color from here in this way then if you want to give the text color here you can choose it from here so this is the sub total of this data now you can simply copy this contrl C then you can paste it in the payment received amount control V and here in the difference also you can use this same formula and here you can calculate the total outstanding and this total outstanding is given by this formula equal to the invoice amount minus pay pment re amount plus the uh difference amount so this will calculate the total outstanding here which is in this case the total out the total outstanding is 11,450 rupees if you want to view the record for a particular customer you can always go to the data filter option by cting The Heading then going to this filter option when you click on the filter option then here you can see the customer name filter is given if you want to view the record of customer AB only then you can clear the filter from here then choose customer ab and click okay now the data for customer AB only is given here in in case of customer AB we have already received all the payment so the outstanding amount is zero if you change the customer name from here suppose that if you choose customer CD then click okay here you can see the total outstanding is calculated 956 rupees because 956 rupees here we need to calculate the total out outstanding by using a different formula here I am selecting here I'm selecting all the customer first select all then click okay and here I am using the outstanding formula equal to the outstanding will be calculated by this invoice amount minus the payment receip amount plus then here when you click on the difference amount not the we we need to click on the debit or credit amount not in this difference so we have to click on this debit or credit amount to calculate outstanding here and here I'm copying this contrl C and pasting the formula here for debit and credit amount and here we don't need this difference amount so I am deleting this form here and then changing the color highlight to White now this will give the total outstanding for the customer we choose from here for example if I choose by going to the drop down if I choose customer CD from here now you can see this is the total outstanding for this customer which is 478 rupees when we subtract the payment receip from this amount of invoice then we get the total out outstanding so in this way we use formula here in this template now here I am selecting all the customer from here again select all then click okay now here you can see the total outstanding is calculated and if you add a new record here for example invoice number 105 the date of invoice is suppose January 20 2020 22 in this way the number of days is 24 customer I am choosing customer PQ from here invoice amount is suppose 93250 rupees now in this case it is given as due because the date number of days is less than 30 so in this way we can update value here in this invoice and payment tracker for example invoice number is 106 the date of invoice is suppose January 25222 customer name is suppose customer DF again and then invoice amount is suppose 80,000 rupees now here you can see the values are Auto changing this invoice is given as due because it is less than 30 days so in this way we can record values here now here I'm clearing the filter from here then suppose that if you receive payment for this uh invoice number 106 and if you receive the payment of 50,000 Rupees here you can enter it here and then it will show partially clear if you record the date of receip here for example if the payment is received on February 13 2022 then here you can see the days taken for payment is autoc calculated here we haven't put the days date of receip that's why it is not calculated if you put the date of receip here suppose January 25222 then you can see the DAT taken for payment is autoc calculated similarly here I'm putting January 27 2022 here I putting January 30 202 2 then here you can see the values are here I am putting January 30 2022 and here you can see all the values are automatically calculated so in this way we can develop a invoice and payment tracker in Google Sheets and uh this much for today's video thank you very much for watching if you want to learn more in Microsoft Excel then you can go to my channel and browse the video section and if you are new in this Channel Please Subscribe my channel so that you will never miss similar kind of videos in future thank you very much for watching this video we will again meet on next video