Collaborate on Vendor Invoice Template for Organizations with Ease Using airSlate SignNow

Vendor invoice template for organizations

if you are running a business you must be dealing with lots of vendors on daily basis lots of vendors means lots of invoices or many invoices it's really easy task to maintain couple of invoices but what's if you are getting lots of invoices on daily basis how to track the invoices how to track the payment it's really a challenging task in today's video we will see how we can create vendor payment tracking software in Excel and believe me this software or tracking system is going to ease your job and you are not going to miss any payment hi my name is vij and you are watching office Mom let's start the video without any delays this is the vendor payment tracking software which I have created in Excel before creating the new format let's see how it works on the top we have got month and year and both the things will be managed through dropdowns below that we have got vendor invoice date invoice total in case you want to insert invoice number it can be done just insert a column over here insert and you can put your invoice number over here I'll undo this payment mode there's a drop down to select payment mode I have just kept card and cash payment date you'll have to put your payment dates over here paid or pending this is fully automated once all the entries are done you have to click on check let's do some entry over here payment mode I'll select card payment date I'll put a random date over here 10th Jan 24 when we come to right hand side we can see outstanding and over here also we have got outstanding I will be tracking the invoices every 15 days so this is from 1st genan to 15th Jan and this is from 16th Jan to 31st genan I'll come to left and we'll click on check means I have done the payment when we see on the left hand side we got strike through paid we paid 3968 to wender one when we come to right hand side we can see payment done 3968 and the outstanding in amount got less if I'll do more checks over here payment done will increase means amount of payment done will increase and amount of outstanding will decrease just see over here so for vendor one payment done is total and outstanding is zero so payment status is clear when we come down over here we have got the option to see that the payment was done in cash or card for vendor one I have done the payment in card so it's showing over here when I come to left hand side and go on top I will change the payment mode and we'll do it cash let's go down let's come to right earlier the amount was showing in card now it's showing in cash let's go on top I will come to right hand side over here all the pending invoices will be visible when we will do the payments the list will keep on decreasing I'll come to left and we'll do the payment to all the vendors let's go on top let's go to right so none of the payment is pending now when I'll come to left and we'll click on some pending and we'll check the pending list we can see all the details there will be only one sheet in this format I'll come to left this is for January when I'll click over here and we select February we can see all the details of February when I'll select March there's no invoice of March only reason we are not able to see any amount over here and when we come to right hand side still we have pending amount from the month of January so we will have to clear when we will go to right hand side thus no pending amount in case you want to create this format for 12 months you can let's start creating a new format now I'll insert a new sheet we'll rename the sheet as January we'll take column A Closer we'll click on B4 serial number vendor invoice date invoice amount or invoice total payment mode payment date paid slash pending and over here I'll type check because we will use checkbox over here enter in case you haven't got the option of checkbox just use yes or no over here we'll adjust the column size perfect we'll click on row number four and we'll align all the things in Center serial number 1 2 we'll select both and we'll drag it down we'll create till 15 perfect we'll click on serial number we'll press CR a or command a and we'll insert borders we'll click on serial number we'll press shift control right arrow key and we'll put some color over here we'll change the font color vendor invoice date I'll take some random date over here first Jan 24 we'll click on top we'll press control 1 we'll go to date and we'll select a desired date format and we'll click on okay we'll align all the dates in left perfect inv voice total we'll take some random figures and to take random figures I'll use Rand between Plus or equal to hand between bracket open bottom I'll take bottom figure as 1200 comma top figure as 4500 we'll close the bracket and we'll hit enter we'll align this in left we'll drag it down we'll copy this contrl C or command C and we'll paste here as values P special values payment mode I'll select from top to bottom we'll go to data data validation from here I'll select list source card comma cache and we'll do okay so the drop down of payment mode got created we'll select card over here and we'll select cash over here payment date for now I leave it blank paid or pending this will be formulated but before putting the formula let's let's put our checkbox we'll click on top and we select till bottom we'll go to insert in the center we get the option of checkbox we'll click over here and all the checkboxes got inserted we'll leave it selected we'll go to home conditional formatting highlight cell rule equal to and over here I'll type Pro we'll click on drop down customized format we'll go to font color I'll select the color as green in fonts we get the option of strike through I'll click over here and we'll do okay okay so whenever we will click on checkbox it got highlighted in green now I'll click on first vendor and we'll select till last vendor we select till payment date we'll go to conditional formatting new rule from here we will select classic in case you are using Windows you'll not be getting the option of classic instead you will be diverted to next step and that is use a formula to determine V to format and the formula will be plus or equal to we will click on first check boox we'll remove dollar from Center is equal to true we not take true in double inverted commas we'll click on drop down customized format font we'll click on drop down and we select font color as green we'll click on strike through and we'll do okay okay so we can see the strike through if if I'll uncheck the Box strike through got vanished if I'll click on multiple check boxes all the data is getting strike through I'll uncheck this now we will click on paid or pending and we'll insert our formula and the formula will be plus or equal to if bracket open we'll click on first checkbox is equal to True comma if this will be true then we want invoice total comma if this will not be true what we want in return we want pending so I'll put double inverted comma pending double inverted comma close bracket close and we will hit enter we got pending over here if I'll click on checkbox we can see some amount we'll align this in left now I'll click on amount we'll press control 1 we'll go to custom we'll remove General we'll put has space paid p a i d I'll take paid in double inverted commas and we'll do okay now we can see 4125 paid if I'll untick this it's showing pending I would like to show something over here if this box is unchecked we can see false in the formula bar and when this box is checked we can see true when we come to our formula I have inserted true over here so true means box is checked and false means box is is not checked we'll select the whole sheet we'll go to home conditional formatting highlight cell rule equal to and over here I'll type pending we'll click on drop down customize format fill from here I'll select red color and font color will be white and we'll do okay okay so the word pending got highlighted in red I'll drag this down when we will check this it will be showing paid perfect we will leave column J blank we'll take this closer we'll make row size bigger over here we will type vendor better I'll copy this contrl C or command C and we'll paste over here contrl V or command V this is Vendo this will be payment we'll press alt and we'll hit enter do slash clear enter we'll adjust the row size we'll align all the things in Center this will be payment done enter we'll adjust the column size this will be outstanding enter we'll adjust the column size we'll come to right hand side this will be for first 15 days I'll create borders over here we'll copy this control C or command C we'll leave column o blank and we'll paste over here control V or command V this will be for next 15 days in case you want to keep all your records in one place you can we'll adjust the column size we'll come come to left we'll click just under vendor and we'll insert our formula Plus or equal to Unique bracket open AR we'll go to left and we'll select all the vendors from top to bottom we'll close the bracket and we'll hit enter we'll click on vendor one we'll go to formula bar our last sell reference is 19 we'll delete 19 and we'll do it 200 so our range got increased we'll hit enter we'll align all the things in left perfect now we will click on top of vendor we leave row number three blank we'll take this closer we'll increase size of row number two we'll select K and L and we'll merge this together we'll increase the column size this will be invoice payment tracker enter we'll put some color over here we'll increase the font size we'll go to left we'll click on B we'll go to data data validation from here I'll select list Source I'll type name of months over here from January to December and we'll do okay so the drop down of months got created I'll select January we'll leave c blank and we'll click on D we'll go to data data validation we'll select list Source I'll insert my ears over here 224 comma 2025 comma 2026 in case in case you want to insert more ear you can I'll click on okay so the drop down of ear got created I'll select 2024 we got date format over here we'll press crol 1 we'll go to General and we'll do okay now we can see 224 we'll go to home and we'll align this in center now we will click next to invoice payment tracker and we'll insert our formula formula for first date will be plus or equal to date value bracket double inverted comma 1 double inverted comma close and we'll click on month and we'll click on here we'll close the bracket and we'll hit enter we'll click over here we'll align this in Center we'll select both the cells we'll press control1 we'll go to date and we'll select a desired date format and we'll do okay perfect now I'll click over here over here we are going to track the invoice for first 15 days so I'll insert the formula for 15th date and the formula will be plus or equal to date bracket open Year we will click over here this is my year comma month bracket open I'll click on month's name and one we'll close the bracket comma 15 we'll close the bracket and we'll hit enter so we got 15th Jan if I I'll change the month we'll do it February we can see first Feb to 15th Feb we'll do this January we'll select all and we'll change the color we'll create borders we'll come to right hand side we'll copy this contrl C or command C and we'll past over here control V or command V we'll increase the column size we'll delete both the formulas from here Plus or equal to we'll click on 15th + one enter we got 16th Jan for fora for last date of the month will be plus or equal to EO month means end of the month bracket Open start date first Jan is my start date comma months I'll put zero over here we'll close the bracket and we'll hit enter we got 31st Jan we'll come to right we'll click just under outstanding and we'll insert our formula and the formula will be plus or equal to some ifs bracket open some range will come to left and we select invoice total from Top we'll press F4 or function F4 and the range will be logged comma criteria range one so my criteria over here is vendors so I'll select vendors from Top we'll press F4 or function F4 and the range will be logged comma criteria 1 this is my criteria 1 comma criteria range two all the dates will be my criteria range two we'll press f 4 or function F4 and the range will be logged comma double inverted comma greater than or equal to double inverted comma close and we will click on first date we'll press F4 or function F4 and the cell will be loged comma criteria range three I'll select date once again as my criteria range three we'll press F4 or function F4 and the range will be logged comma double inverted comma smaller than or equal to double inverted comma close and we'll click on last date that is 15th Jan over here we'll press F4 or function F4 and the cell will be logged we'll close the bracket minus payment done and I'll hit enter we got the amount over here we'll align this in left we'll drag this down payment done formula will be plus or equal to sum ifs bracket open sum range last time our sum range was invoice total this time our sum range will be column number H we'll press F4 or function F4 and the range will be logged comma criteria range 1 vendor details will be our criteria range one we'll press F4 or function F4 and the range will be logged comma criteria 1 window will be criteria 1 comma criteria range two date will be our criteria range two we'll press F4 or function F4 and the range will be logged comma double inverted comma greater than or equal to double inverted comma close and we'll click on first date we'll press F4 or function F4 and the cell will be logged comma criteria range three we will select dates once again we'll press F4 or function F4 and the range will be logged comma double inverted comma smaller than or equal to double inverted comma close and we'll click on 15th Jan that is last day of this place we will press F4 or function F4 and the cell will be logged bracket close and we will hit enter we'll align this in left we'll drag this down perfect I'll select this we'll press control 1 we'll go to custom we'll remove General and we'll insert H semicolon has semicolon Dash and we'll do okay so all the zeros got vanished and we can see Dash over here what will happen if I'll untick all we can see all the outstanding amount and no payment has been done and whenever we will do any payment it will keep on adding perfect payment due or clear formula will be plus or equal to if bracket open we'll click on first outstanding amount is equal to Zer if this will be zero what we want in return we want clear so I'll put double inverted comma clear double inverted comma close comma if it will not be zero what we want in return we want du so I'll put double inverted comma du double inverted comma close bracket close enter so we can see clear we'll put one criteria over here of if we'll go to formula bar if bracket open we'll click on first vendor is equal to Zer comma if this will be zero in return I want blank so I'll put double inverted comma twice for blank comma we'll come to the end we'll close the bracket and we'll hit enter I'll drag this down we'll select the whole sheet we'll go to home conditional formatting highlight sell rule equal to and over here I'll type clear we'll click on drop down customize format fill from here I'll select green color font font will be white and we'll do okay okay we'll go to conditional formatting once again highlight sell rule equal to and this time I'll type du we'll click on drop down customized format fill we'll select red color for du and font will be white and we'll do okay okay we'll come to right hand side we'll click over here we'll copy our formula contrl C or command C and we'll paste over here crl V or command command V enter payment due or clear we'll select all and we'll align this in left payment due or clear I'll click over here and we'll copy the formula from the formula Bar contrl C or command C we'll press escape and we'll paste over here contrl V or command V we will have to change the cell reference we'll click over here we'll go to formula bar N5 I'll delete this and we'll click over here K5 I'll delete K5 and we'll click over here on Windows name formula is same what we have inserted over here and we'll hit enter we'll drag this down payment done we'll click over here we'll copy our formula contrl C or command C we'll press Escape we'll click over here contrl V or command B we'll align this in left we'll go to formula bar and we'll change the cell reference over here all the things will be same except our dates so first date was in M2 so I'll delete M from here this is R so I'll put R last date we'll take this down last date was in N so I'll delete n and we'll put s over here because in this case our last date is in column s and we'll hit enter we got zero we'll drag this down we'll come to outstanding over here we'll copy this formula contrl C or command C we'll press Escape we click over here we'll go to formula bar and we'll paste our formula contr Sol your command V enter we'll align this in left we'll come over here once again over here we will have to change one more reference K5 that is vendor details we'll delete K5 and we'll click over here enter we'll drag this down this is perfect now we'll click on outstanding we'll go to formula bar we'll delete K5 and we'll click over here we will have to change the dates it's M2 we'll delete M and and we'll put R this date N2 we'll delete n and we'll put s this date and we'll hit enter we'll drag this down we select both we'll press control1 we'll go to custom we'll remove General we'll insert has semicolon has semicolon Dash and we'll do okay perfect we'll go to view show and we'll remove the grid lines now we will come to right right hand side we'll leave column t blank we'll take this closer we'll come to left we'll click on vendor and we'll select till paid or pending contrl C or command C we'll come to right hand side and we'll paste over here contrl V or command V we'll come to right hand side we'll click on top and we'll select till end we'll go to home and we'll merge this together we'll put some color over here we'll change the font color this will be pending enter we'll insert borders we'll increase the font size we'll increase column size we'll adjust these also perfect now we will insert our formula and the formula will be plus or equal to filter bracket open AR we come to left we'll click on first window and we select till paid or pending we select till bottom over here our last cell reference is h19 we'll delete 19 and we'll put 200 and our range got increased comma we'll click on paid or pending we'll select till here last sell reference is h19 I'll delete 19 and will put 200 and my range got increased is equal to we'll come to right hand side and we'll click on top will close the bracket but before hitting enter we will insert the criteria of if error if error bracket open will come to the end comma double inverted comma twice for blank we'll close the bracket and will hit enter we got our range we'll insert borders over here date format got changed I'll click on top we'll press control 1 we'll go to date and we'll select a desired date format and we'll do okay we'll select all the things and we'll align in left payment mode and payment date is not required over here so I'll hide this in case you want to keep you can hide we will come to left so this part is done now we will create the format to check details of card and cash we'll come to left we'll click on vendor and we select till payment mode control C or command C we'll click over here control V or command V we select till end and we'll merge this we'll put some color this will be cach enter we'll change the font color we'll create borders we'll come down formula will be plus or equal to filter bracket open aray we will click on vendor and we select till payment mode we'll select till bottom we'll go to formula bar last sale reference is f19 I'll delete 19 and we'll put 20000 my reference will increase comma include I'll include payment modes from top to bottom we'll delete 19 and we'll put 200 this is the simple way to increase our sale reference is equal to we'll click on cash and we'll hit enter we got all the details if I'll do more entry of cach the list will keep on increasing I'll select this and we create borders now I'll copy this contrl C or command C we come to right hand side and we'll paste over here control V or command V we'll come down we'll click on window one we'll copy our formula contrl C or command C we'll press esape and we'll paste over here we'll delete this contrl V or command V enter over here also we can see cache we'll click on vend one we'll go to formula bar over here sale reference is k21 means cash I'll delete k21 and we'll click over here enter we'll do this card enter and I got all the details of card date format got changed we select this from top to bottom we'll press control or command and we'll select invoice date over here also we'll press control 1 we'll go to date and we'll select a desired date format and we'll do okay we'll align all the things in left means we will align all the rates in left and the format is ready now I'll provide link of the format in description box in case you want to download you can but the download option is only for channel members you can join the channel membership to download the file 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