Collaborate on Billing Excel Sheet for Banking with Ease Using airSlate SignNow

Billing excel sheet for Banking

[Music] this is a daily collection report which I have created in advance before creating the new format let's see how it works on the top we have got months and year and both the things will be managed through drop downs I'll click over here we'll click on the drop down and we'll select January from here we can see from and to from 1st genan to 31st genan now I'll click over here and we'll select and as soon as I selected the year all the details got visible currently I have got all the details or all the payments or collections of January January 24 when I'll click on January and we select February all the details got changed means dates and days got changed even the amount or collection amount got changed now we can see the figures of February when I'll do it March there's no collection for the month of March only reason no amount is visible I'll do it January and when I'll do it 2025 we have got the data of 2025 when I come to right hand side over here we can see total dues and the data is till 31st of Jan means till end of the month I'll come to left there will be total three sheets in this format first will be setting sheet where we will have our months year and company names second will be collection entry sheet in collection entry sheet all the details of collection will be there like customers name invoice number invoice date and so on let's start creating a new format now I have opened a new sheet we'll rename the sheet this will be settings we'll select the whole sheet and we'll take all the things in Center we'll take column number a closer we'll click on B2 this will be months and this will be year and this will be company name enter we'll adjust the column size we'll take column number D closer we'll s row number two and we'll align all the things in mid we'll put some color over here we'll change the font color we'll click on here we'll go to home we'll click on format painter and we'll click over here now I'll select the whole row and we'll change the font style months we'll increase the column size over here I'll type January enter we'll drag this till December here 2024 enter we'll align this in Center we will drag this till end field series we'll select both and will create borders company name I'll put company 1 enter we'll drag this down we'll drag this till 20 we'll create borders we'll go to view and we'll remove the grid lines we'll put border over here now we will insert another sheet this will be collection entries we'll select the whole sheet and we'll take all the things in mid we'll take column number a CL closer we'll click on B2 this will be serial number helper customer name enter we'll come to setting sheet and we'll do this customer name enter this will be customer one enter we'll drag this till last now I'll go to collection entries we'll adjust the column size we'll select the whole row and we'll take all the things in Center we'll change the font style we'll adjust the column size this will be invoice number we'll drag this this will be invoice date month year due date over due days invoice amount we'll adjust the column size we'll come to right hand side paid we'll open the bracket yes slash no we close the bracket enter we'll adjust the column size paid date and this will be paid amount enter we'll adjust the column size we'll click on paid amount we'll press shift control left Arrow key we'll put some color over here we'll change the font color we will increase the row size paid amount will be fully formulated so I'll put red color over here we'll come to left year due date and overdue days will be fully formulated so I'll put red color over here also we'll come to left we'll click on serial number we'll press shift control right arrow key and we'll insert borders serial number I'll put serial numbers we'll adjust the column size we'll increase size of helper customer name customer one or I'll take details from here I'll take from first customer till fifth customer control C or command C and we'll paste over here we'll copy this we'll paste over here we'll drag this down we'll copy this paste because whatever we will be selling it will be just random so any customer can take anything on any date we'll click on serial number we'll press shift control down arrow key and right arrow key and we'll create borders invoice number I'll take some random invoice numbers we'll drag this down invoice date it can be random we'll take this in Center we'll click on top we'll press control one we'll go to date and we'll select a desire date format okay we'll drag this down month month will be also Dynamic so I'll put red color over here formula will be plus or equal to text bracket open we'll click on invoice date comma double ined comma m m m m means M four times double inverted comma close bracket close and I'll hit enter so we got January we'll drag this down here formula will be plus or equal to text bracket open we'll click on invoice date comma double inverted comma y y y y means y four times double inverted comma close bracket close and I'll hit enter we got our year we'll drag this down due date so due date will be calculated on basis of invoice date it will be plus or equal to we'll click on invoice date now it totally depends on you how many Grace periods you are going to give I'll take 30 plus 30 enter so we got 31st Jan as due date we'll take this in Center we'll drag this down we'll come to right over due days it will be calculated on daily basis so we will use today over here Plus or equal to today bracket Open Bracket close minus we will click on due date enter we'll take this in Center we'll click on top we'll press control1 we'll go to number we'll remove decimal places or we can go to General also and and we'll do okay so we got 165 days we'll drag it down it will keep on changing on daily basis we will insert one more criteria over here so I'll highlight this right now once we will put the criteria I'll remove the Highlight we will come to right invoice amount invoice amount figures can be anything so I'll just take random figures to take random figures I'll take Rand between the r between bracket open bottom I'll take bottom figure as 500 comma top figure as 1200 bracket close and I'll hit enter so I got a random figure we'll drag it down we'll copy this contrl C or command C and we'll paste here as values P special values we press skape paid yes or no I'll select from top to bottom we'll go to data data validation from here I'll select list Source I'll do yes comma no and I'll do okay we leave it selected we'll go to home conditional formatting highight sale rule equal to and over here I type yes we'll click on drop down customize format fill from here I'll select green color and font will be white and we'll do okay okay we'll go to conditional formatting once again highlight sale rule equal to and this time I'll type no we'll click on drop down customized format fill from here I'll select red color and font will be white and we'll do okay okay so our drop downs got created as well as got highlighted I select yes we'll select yes for all for a couple of people I'll select no perect paid date payment date can be anything so I'll take some random figures so I have taken some random dates now we will click just under paid amount so if this will be yes over here we will get the amount and if this will be no it will be blank so our formula will be plus or equal to if bracket open this is equal to double inverted comma yes double ined comma close comma if this will be yes we want invoice amount over here comma if this will not be S we want du to feature so we'll put double ined comma du double inverted comma close bracket close and we will hit enter we take this in Center or in left we'll drag it down if this is no we can see do over here we'll select the whole sheet we'll go to conditional formatting highlight sell rule equal to and over here we will type du we'll click on drop down customize format fill from the drop down we will select red color and font will be white and we'll do okay okay so do got highlighted in red now we will come over here over here we will insert criteria of if if bracket open paid date is greater than zero comma if paid date will be greater than zero what we want we 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 now I'll drag this down we'll remove the color no F over here here all the things are blank so I'll do one thing if the amount is not paid I'll remove paid date from here also I'll remove paid date so if the amount will not be paid overdue days will be showing over here we'll go to view and we'll remove the grid lines now we will come to left helper we are using helper only for Vier purpose over here so I'll put Plus or equal to customer name and we'll come to right and we'll click on paid date and we'll hit enter we'll come to left so both the text got combined and our helper column is ready we'll drag it down this sheet is ready we will insert another sheet and this will be collection report we select the whole sheet we'll go to home and we'll align all the things in mid we'll take column number a closer we'll click on B9 this will be serial number customer name invoice number we adjust the column size invoice date status invoice amount we'll adjust the column size total paid balance we'll click on row number nine and we select 10 also and we'll change the font style we'll align all the things in Center we'll click on B2 and we'll select till balance and we'll merge this together we'll make row size two bigger we'll put some color over here we'll change the font color we'll change the font style we increase the font size this will be daily collection report enter we'll leave row number three blank we'll take this closer we'll select B and C and we'll merge this together this will be month enter we'll put some color over here we'll change the font color we'll select the whole row and we'll change the font style we'll drag this till I we'll delete this we'll change color over here because this will be for drop down of months we'll change the font color this will be year we'll change color over here also we'll make row number four bigger we'll select the whole row and we'll increase the font size we select from month till end and we'll create outside border months for months and year we will create drop down so we'll go to data data validation from here we will select list Source we will go to our setting sheet and we'll select months from January to December and we'll do okay so our drop down for months got created we will select January we'll click just in front of here we'll go to data data validation from here we will select list Source we'll go to setting sheet and we'll select ears from top to bottom and we'll do okay so the drop down for ears got created we'll select 2024 we'll take row number five closer now we'll click on Basics and we'll select till F and we'll merge this together we'll put some color over here we'll change the font color we'll select the whole row and we'll change the font style this will be total we'll create borders till balance for total we will put red color over here and the font will be white we'll click on serial number and we'll select cell just above serial number and we'll merge this we'll do this for all the heads we'll take row number seven closer we click on serial number we'll press shift control right arrow key and we'll change the color we'll change the font color we'll create borders serial number I'll insert some serial numbers over here we'll drag this down we'll change the font style customer name customer name can be repeated n number of times in collection entries so we will do one thing we will use unique over here formula will be plus Plus or equal to Unique bracket open we'll go to collection entry sheet we'll click on first customer name and we'll select till end this data May really be vast and it can't be adjusted till d18 so we will do one thing we'll come to formula bar and just after d18 we will insert 18800 means we will do it 1,800 instead of 18 means I have added two zeros we'll close the bracket and we'll hit ENT enter we'll align this in left now if the customer name gets added over here it will be automatically added in our daily collection report sheet I'll add my name over here we'll do office M enter let's go to collection report and my name got featured in the end we'll delete this in case you don't have option of unique you'll have to put the names over here manually invoice number the formula will be plus or equal to V look up bracket open look up value customer name will be our lookup value we'll press function F4 three times comma table aray we'll go to collection entry sheet and we'll select from d and we'll select till end we'll press function F4 and the range will be logged comma we'll come to left we want invoice number so selection of V lookup started from customer name so this will be one and invoice number will be column number two so I'll put two over here comma 0 for exact match we'll close the bracket but before hitting enter I'll insert the criteria of if error if error bracket open will come to end comma double inverted comma twice for blank we'll close the bracket and will hit enter so I got my invoice number we'll drag this down invoice date I'll drag this we got invoice number over here so we will have to change the column index number we'll do this three and we'll hit enter so we got our invoice date we'll click on top we'll press control1 we'll go to date and we'll select a desired date format and we'll do okay now we will drag this down status so status will be collected from balance if the balance will be zero status will be cleared and if balance will not be zero status will be due so I'll insert my formula formula will be plus or equal to if bracket open we'll click on balance is equal to zero comma if the balance will be zero we want cleared so I'll put double inverted comma cleared double inverted comma close comma if it will not be zero we want D over here so I'll put double inverted comma d double inverted comma close bracket close and I'll hit enter so we can see clear right now if I'll put any figure over here it got changed to do I'll delete this I'll drag this down so we can see clear till end so we will put one more criteria of if we'll go to formula bar if bracket open we'll click on invoice amount if invoice amount is equal to0 means if the invoice amount will be zero what we want in return we want blank so I'll put double inverted comma or I'll put double inverted comma Dash or minus double inverted comma close comma we'll come to the end we'll close the bracket and we hit enter so we can see Dash I'll drag this down invoice amount we want invoice amount to be dynamic whenever we will change the month or year invoice amount should be showing ingly so we will use Su ifs over here so the formula will be plus or equal to Su ifs bracket open Su range we will go to our collection entry sheet we'll come to right hand side and we'll select invoice amount from Top we'll press function F4 and the whole range will be logged comma criteria range one will come to left so our first criteria will be customer's name so we will click on top we will press function F4 and the range will be logged comma criteria 1 we'll go to collection report and we'll click on first customer comma criteria range two we'll go to collection entries criteria range two will be our months so we will click on top we'll press function F4 and the range will be logged comma criteria two we'll go to collection report and the criteria will be month over here so whenever we will change the month all the calculations will be done automatically in Formula bar we can see D4 and E4 both the sales are merged over here so it's showing D4 and E4 so we will delete E4 from here and we'll keep D4 we'll press function F4 and the cell will be logged comma criteria range three so criteria range three will be years so we'll go to collection entry sheet and we'll select ear from Top we'll press function F4 and the range will be logged comma criteria 3 we go to collection report and we'll click on ear over here also we will delete last cell number we'll press function F4 we'll close the bracket and we'll hit enter so we got our invoice amount we'll drag this down now if the month will change we'll do it February all the figures got vanished because there no invoice or invoice amount against month of February we'll go to collection report and we'll do this January we'll do one thing we'll select all these and we'll press control1 we'll go to custom we'll remove General and we'll put has semicolon has semicolon Dash and we'll do okay so instead of zero we can see Dash now total paid and balance we can't insert formula of total paid right now but we can insert the formula of balance so formula for balance will be plus or equal to invoice amount minus total paid and we'll hit enter we'll drag this down total paid we will highlight this because we are going to insert formula over here later on the basic reason I'm putting color so that it will remind me that formula is still pending now I'll select all and we'll create borders we'll adjust the column size we'll click on top of invoice amount and we'll put our formula it will be plus or equal to some bracket open we'll select from top to bottom we'll close the bracket and we'll hit enter so we got the amount we'll take this in Center we'll select all three we'll press control 1 we'll go to custom we'll remove General and we'll put has semicolon has semicolon Dash and we will do okay now we will drag this so whenever it will be zero Dash will be visible now we will come to right hand side now we will click on K4 and we'll select L also and we'll merge this together this will be from enter we'll select m and n and we'll merge this together we'll select opq and we'll merge this together now we will select all and we'll create borders we'll create outside border we'll put some color over here and we'll change the font color we'll unmo this we'll mge p and Q this will be two enter from the formula for start date will be plus or equal to date value bracket open double inverted comma 1 double inverted comma close and we will come to left and we'll click on first month and we'll click on year we'll close the bracket and we'll hit enter we'll come to right we got some ROM figure we'll click over here we'll press control or command and we'll select this also we'll press control 1 we'll go to date and we'll select a desired date format and we'll do okay so the random figure got changed to date two we'll take this in Center two formula will be plus or equal to EO month means end of the month bracket open we'll click on first date comma zero we'll close the bracket and we'll hit enter so we got last date of the month now we will click just after balance we come down and we'll insert the formula for first date formula will be plus or equal to we'll click over here means we will just connect this from here enter now we will select this we'll press control 1 we'll go to date and we'll select this date format means just date and month okay now we will insert formula for second date and the formula will be plus or equal to if bracket open we click on first date is smaller then we'll click on last date we'll press function F4 and the cell will be logged comma first date + 1 comma double inverted comma twice for blank we'll close the bracket and we'll hit enter in Formula I have inserted double inverted comma twice in end the basic reason behind this the months which will have days less than 31 it will be showing blank over there we'll click just on top of foral Date and we'll insert the formula for days and the formula will be Plus or equal to text bracket open we'll click on first date comma double inverted comma d d d means d three times double ined comma close bracket close and we will hit enter we'll take this in Center we'll select both the rows and we'll change the font style we'll drag this we'll put some color over here over here also for dates we will put some color we'll change the font color we'll create borders over over here also we will create borders we select both CES and we will adjust the column size we select from top to bottom and we'll insert borders now we will select second day and date and we'll drag till 31st we'll come to left we'll click on top of balance and we'll drag this till end because this is total we'll come to left we'll click on S we'll select till U we'll M this together we'll put the red color font will be white and we'll drag this we create outside border this will be total dues enter now we'll come to left we'll come to extreme left we'll select this till end and we'll put blue color we'll put outside border perfect we'll go to view and we'll remove the grid lines total paid now we will insert our formula I'll remove the color no fill Plus or equal to sub tootal bracket open we will not use sum over here we will use sub tootal because we want total of all the months separately in subtotal we get many options I'll just go to number nine that is sum comma we'll click just under first date and we'll select till last date we'll close the bracket and we'll hit enter we come to left we will drag this down now we will select column number F and we'll highlight due and cleared we'll go to home conditional formatting highlight sale rule equal to and over here I'll type cleared we click on drop down customize format fill and I'll select green color and font will be white okay okay we'll go to condition formatting once again highlight s rule equal to and this time I'll Tye du we click on drop down customize format fill we'll select red color for du and font will be white and we'll do okay okay so whenever it will be due it will be highlighted in red and whenever it will be cleared it will be highlighted in green now I'll come to right hand side total dues it will be plus or equal to I'll come to left and we click on total of balance enter total balance will be our total dues we'll click over here we'll press control one we'll go to currency decimal places it will be zero symbol we will select Rupee symbol or dollar symbol whatever we want and we'll do okay we'll come to left now we will insert our formulas over here so it will be plus or equal to V lookup bracket open look up value there will be total two V lookup values over here we'll come to left First Look up value will be customer's name we'll press function F4 three times and second Lookout value will be our date we'll press function F4 two times comma table R we will go to collection entry sheet and we select from helper we'll select till end we'll press function F4 and the whole range will be logged comma column index number so we want paid amount we'll come to left and we'll count our columns we have selected our range of V looker from helper so helper will be number one customer name 2 3 4 5 6 7 8 99 will come to right 10 11 12 so I'll put 12 over here comma 0 for exact match we'll close the bracket but before hitting enter I'll insert the criteria of if error if error bracket open we come to end comma double noted comma twice for blank we'll close the bracket and we'll hit enter now I'll drag this down and we'll drag this till end we will come to left we can't see any figure over here because all the payments are done in the month of February so let's go to collection sheet and select the month as February and we got our figures we'll do one thing we'll select from top to bottom we'll press shift control right arrow key we'll go to conditional formatting we'll select highlight cell rule not equal to for not equal to we will go to more rules and from here we will select not equal to not equal to blank for blank we will put double inverted comma twice we'll click on drop down customized format fill from here we will select green color and font will be white so whenever any payment amount will be there it will be highlighted automatically in green and we'll do okay so all our figures got highlighted in green let's leave it selected and come to right hand side over here we can see D also got highlighted in green so we'll go to conditional formatting once again highlight sell rule equal to and this time we will type du we click on drop down customized format fill from here we will select red color and font will be white and we'll do okay okay so whenever it will be due it will automatically be highlighted in red we'll come to left and our format is ready now I'll provide link of this file 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 for download and edit access I'm sure you 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