Streamline Your Billing Sheet in Excel for Higher Education with airSlate SignNow

Billing sheet in excel for Higher Education

If you are working for a School, College,  University or any other Educational Institute   you must be maintaining Records of the Students.  Also you will be maintaining the records of the   Fees collected. In Today's video we will see  how to create a Fee Record Sheet, which will be   fully Automated. This Sheet will be connected  with links and you can jump over the Sheets,   just in a single click. Hi my name is  Vijay and you are watching OFFICE MONK.   Let's start the video without any delays. This  is a Student Fee record of various classes like   class 6th, 7th, 8th, 9th and 10th. Here are few  things like Roll Number, Name of the Student,   Monthly Fee, Name of all the months from April  till March, Paid Months, means fees of how many   months are already paid, Pending months, Fees  of how many months are still pending, Total paid   fee and total Balance fee is over here. On the  top, Session which can be managed by drop downs. Paid View and NM is not applicable will be  managed by drop downs from here we will have   to select from the drop downs whatever is  applicable once the fees will be paid it   will be showing in paid months if the fees will  not be paid it will be showing in pending months   once I'll delete all this you will notice that  paid months became 8 and pending months became 4.   in pending months you will notice that few months  are green and few months are red currently we are   in the month of November so ideally four  months are still pending so 4 months are   green if someone has not paid the fees before  November it's getting read over here few things   are more over here like class if I'll click  on 7th I'll jump to the record of class 7th   in the same way once I click it I'll jump to  record of class 8th then 9th and then 10th so   let's start making a new sheet we will start  from fourth so in the beginning serial number   name monthly fee here we will type name  of the month and we will drag till March on the top we will go in format and we will  adjust column width here we will type paid months   pending months total paid balance we will select  all four and we will adjust it we will select the   whole sheet and we will do formatting  we will increase the size of the cells   over here serial number  I'll create for 20 students name you can insert name over here monthly fee  I'll take a random figure I'll click over here   I'll do plus and I'll select this now I'll drag  this still down in case the fees changes you   will have to change in the first cell only and  the total fees will get changed in the months   we will create drop down for paid due and not  applicable for that we will have to go in data   data validation here we will select the list and  here we will type paid comma Tu comma n a and we   will do OK our drop down is created over here we  will drag this till March and for 20 students so   drop down is already created now we will select  all this and we will do conditional formatting   we will be going in home conditional formatting  highlight cell rule equal to and here we will   type fade we want paid to be in green so we'll  select green color okay again we will be going to   conditional formatting highlight cell rule equal  to this time we will type due and we want due in   red color so we will leave this we will do okay  again we will be going to conditional formatting   highlight cell rule equal to and this time we  will type n a means not applicable here we will   select yellow color and we will do ok so these  things are managed now whenever we will select   anything it will take the color and we can select  from drop down we will do formatting of all this we will remove the grid Lines by going in  view show and we will remove the grid lines   now we will select from here till here and we  will merge it we will merge these two and we   will merge these two also now we will insert the  table here we will type monthly School fee record Dash class 6th here we will type session and  here we will create drop down for the session   to create the drop down again we will have to  go in data data validation we will select list   in Source we will type sessions which you want  to create so I'll be creating till 2013. [Music]   now we will do OK and the drop down for  sessions is created we can select from   here we will select all this we will be  going in home we will select the color   we will change font color we will change  the font we will take impact because it's   bold we will increase the size now we will insert  formula for payments and the formula will be plus   or equal to countif bracket open we will select  from April to March comma double inverted comma   paid double inverted comma close bracket close and  we will do enter now we will drag this till down   pending months formula for the pending months  will be plus or equal to countif bracket open   we will select this comma double inverted comma Tu  double inverted comma close bracket close here we   will count blanks also because blank cells will  be due so we will have to do plus count blank   bracket open we will select from April to March  bracket close and we will do enter now we will   drag this till down in pending months we will  be counting two things due and blank cells now   we will be coming to Total paid and the formula  will be plus or equal to paid months into fees we   will do enter we will drag this till down balance  the formula for balance will be plus or equal to   pending months into monthly fee enter we will drag  the still down now we will fill this so that we   can calculate all the formulas very easily I will  adjust the size somewhere we will do due [Music] currently we are in November so I'll delete  this some parents pay fees in advance so I'll   drag this till March I'll drag this also till  March some students leave the school in the   mid of the session so we will do this  n a and we will drag the still end due   so this thing is created now and in the formulas  of paid month spending months total paid and   balance it's getting calculated very properly now  we will create the buttons I'll take this in end   I'll drag this and I'll make it little bit bigger  we are going to create sheet for class 6th 7th   8th 9th and 10th means we will have to insert  total five buttons we will be going in insert   then we will be going in shapes and we will take  a shape from here I'll click over here and our   shape is inserted now I will be going over here  in shape effect and I'll be going in bevel I'll   select this so that our shape looks like a button  now we will copy this and paste it five times   we will make this bigger and we will type class   over here 6th 7th 8th 9th 10th I'll select  all this I'll increase the size of the font   I'll make the font bigger I'll take this in  center and in mid we will select all this   and we will arrange this align distribute  horizontally and distribute vertically if we   want to give color to the edges of these buttons  we will have to select all this we will have to   go in save format and from here we will have  to select the edge color and wait so color of   the edges are here now we have to connect these  sheets with these buttons so we will select the   whole sheet and we will copy it and we will paste  in four different workbooks or worksheets [Music]   here we will rename post one was class 6 1 is extra so we will delete  this now we will be coming to   the first sheet of class 6  and we will color as white font color we will keep it black we will click on  class 6th we will right click the mouse we will go   in link or hyperlink you will get three options  over here web page or file this document email   address we have to click on this document now  we have to click on class 6th and type in cell   reference you will get A1 so you will have to do  okay again you will have to go in class 7th right   click the mouse link or hyperlink now this time  select Class 7 okay come in class 8 right click   the mouse link or hyperlink select class eighth  okay class 9th you will have to do the same thing   link or hyperlink select class 9th okay class 10  hyperlink select class 10th do OK we will change   color of this thing we will do it black you'll  have to press Ctrl to select this our font color   is changed now now first sheet got connected with  all the sheets but the remaining sheets are still   not connected so what we will have to do we  will have to select all this once again Ctrl   C or command C and we will have to paste it over  here once again in all the different worksheets so this thing is done this sheet is of  class 10th so we will be going over here   in class 6 we will press Ctrl and we  will do it blue means we will do the   color of the button as blue now we  will be going in class 10 we will   select this and we will do this white  we will have to do in all the classes now I will come in the first sheet and you will  notice that all the sheets got connected when   I'll click on Seventh I am on 7th class when  I'll click on eighth I am in 8th Class 9th   I'm on Ninth class 10th I am on 10th class  we can remove gridlines from all the sheets   so our monthly School fee record is created  you can create this for your school College   University or any other educational Institutes.  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