Effortlessly Manage Your Hospital Bill Format Excel for Support

Hospital bill format excel for Support

in today's video we will see how we can create a fully automated Hospital Billing System in Excel and with the help of this Billing System we will Auto generate the receipts hi my name is Vijay and you are watching office let's start the video without any delays this is the billing format of Hospital which I have already created in advance here we have got details like patient ID patient name age gender word charges admit date discharge date and so on few things I have included over here like doctor's name along with their charges second doctor's name along with their charges when we will come to the right hand side we have got the option of medicine along with discount treatment charges along with discount total discount and payable amount when we will click on the button receipt will be generated automatically here we have got a setting sheet in which we have got details like word name along with their charges doctor's name along with their fees and discount percentage let's start creating a new sheet now I have opened a new Excel file the very first sheet will be setting sheet I'll take all the details of setting sheet from my example file in settings sheet we will need details like word name or what details along with the charges on the top there will be option for none and the charges will be zero then we will need details like doctor's name along with their fees and discount percentage you can adjust discount percentage as per your requirement let's insert a new tab or a new worksheet we will start from B3 serial number patient ID patient name age gender advance word charges admit date discharge date total days doctor's name in the bracket we will take first doctor we will select the whole sheet will go to home and will wrap the text we will come over here visit charges in the bracket we will take first doctor number of visits we will copy this and we'll paste over here and this will be for second doctor we will insert a column over here this will be total charges first doctor we will copy this we will paste over here this will be total charges second doctor we will come to right hand side medicine charges operation or treatment charges what discount we will copy this and we'll paste over here this will be what discount percentage medicine discount we will copy this this will be medicine discount percentage operation or treatment discount we will copy this and we'll paste over here this will be operation or treatment discount percentage total discount total charges payable amount we will select the whole sheet and we take all the things in mid and in Center now we will select this and will adjust the column size we will take this in left we'll select this and we'll create borders we will create this sheet for 10 patients I'll insert serial number till 10. we'll select this and will create borders we'll insert a color over here we will change the font color we'll make it bold patient ID we will insert it manually p101 I'm just taking random numbers patient name I'll take from my example file age I'll take some random figures gender for gender we will create drop downs I'll select this will go to data data validation from here we will select list in Source we will type it manually mail comma female comma not specified and we will do okay so the drop down got created we will select this and we'll take this in left side Advance we will take some random figures I'll take four thousand I'll take 4000 for all the patients we'll keep it selected we'll right click the mouse we'll go in format cells custom I'll take this up we'll delete General from here double inverted comma rupees double inverted comma close space has and we'll do okay so RS or rupees sign got inserted just before the amount what for what we will create drop down we will go to data data validation from here we will select list source we will go to settings sheet and we'll select this means name of all the words we'll do okay so the drop down got created will increase the size we'll adjust this I'll take this in left word charges for word charges we will insert a formula and our formula will be of vlookup Plus or equal to vlookup bracket open lookup value word name will be our lookup value comma table array we will go to settings sheet we'll select this we'll press function F4 and the cells will be logged comma word charges are in column number two so we will put 2 over here comma 0 will close the bracket and we'll hit enter we will drag this till end you will notice some error over here because name of What charges are blank So to avoid this error we will insert if error criteria just behind vlookup if error bracket open we will come to the end comma double inverted comma twice bracket close and we will hit enter now we will drag this down admit date we will take some random dates we'll select this we'll right click the mouse format cells date I'll take this up we will select a desired date format okay I'll drag this down discharge date I'll take some random dates once again I'll drag this down total days the formula for total days will be Plus or equal to discharge date minus admit date Plus 1. we will take this in bracket and we'll hit enter we will drag this down I'll delete few discharges from here once discharged it will be blank we get some error over here so we will insert criteria of if we will go to the formula bar if bracket open discharge date is equal to 0 comma double inverted comma twice comma we will come to the end we'll close the bracket and we'll hit enter now we will drag this down doctor's name we will create drop down for this we will select this will go to data data validation from here we will select list Source we will go to the setting sheet we'll take this and write we select doctor's name from here we'll do okay we'll copy this and we'll paste this in second doctor's name we will make the column size bigger so the drop down list for doctors got created we will increase the column size we will adjust the row size visit charges first doctor our formula will be Plus or equal to vlookup bracket open lookup value doctor's name will be lookup value comma table array we will go in the setting sheet we'll select this we'll press function F4 comma fees is in column number three one two three so we will put 3 over here comma 0 we'll close the bracket and we'll hit enter before dragging this down we will insert the criteria of if error if error bracket open we will come to the end comma double inverted comma twice bracket close and we will hit enter we will drag this down as soon as we will select the doctor's name fees will be visible number of visits we will have to insert it manually I'll take some random figures I'll insert doctor's name over here also [Music] we'll select this and we'll take this in left side we'll increase the column size total charges for first doctor will be plus or equal to visit charges into number of visits enter we'll drag this till end doctor's name for second doctor we will drag this down visit charges for second doctor we will copy this controls your command C and will paste over here because table array is same number of visits we will insert it manually total charges for second doctor formula will be same so we will copy this controls your command C and we'll paste it over here medicine charges we will have to insert it manually again the operation or treatment charges we will have to insert it manually we will come over here in what discount we'll select this and we'll create our drop downs to create drop downs we will go in data data validation list source we will go to the settings sheet we'll take this in left side we'll select the discount percentage and we'll do okay so our discounts got created we will select this will go to home and we'll click on percentage icon or percentage sign now we will copy this controls your command C and we'll paste in medicine discount and will also paste in operation or treatment discount percentage what discount this is what discount amount we will do one thing we will select this you'll right click the mouse format cells will take this up we'll go to custom we'll delete General from here double inverted comma rupees double inverted comma close space has and we will do okay we'll copy the format and we'll paste over here in medicine discount and in operation or treatment discount because all these are amounts the formula for what discount will be plus or equal to we will take this in bracket will come to the left hand side word charges into tal days bracket close into we will come to the right hand side and we'll select discount percentage from here and we'll hit enter What charges is 0 in the very beginning so we are not getting any amount over here we will drag this down we'll insert criteria of if error just behind the formula if error bracket open we will come to the end comma double inverted comma twice bracket close and we will hit enter now we will drag this down we will select some discounts we will come in the left hand side and we'll put words name over here so that we can cross check our formulas we'll come to the right hand side medicine discount the formula for medicine discount will be plus or equal to medicine charges into medicine discount percentage and will hit enter we'll drag this down we'll insert some discount percentage operation or treatment discount the formula will be plus or equal to operation or treatment charges into discount percentage and will hit enter we'll drag this down in case there will be no discount just select zero percent total discount this will be amount we will select this we'll right click the mouse format cells we'll take this up custom we'll delete this double inverted comma rupees double inverted comma close space has and will do ok so total discount will be plus what discount amount plus medicine discount amount plus operation or treatment discount amount and we will hit enter now we will drag this down will come to the right hand side total charges the formula for total charges will be plus or equal to we will take this in bracket will come in left hand side word charges into total days will close the bracket plus we'll come to the right hand side we will take this in bracket total charge is the first doctor Plus total charges of second doctor plus medicine charges Plus operation or treatment charges Plus I think we have added all the things so it will be minus total discount amount will close the bracket and will hit enter we'll drag this down we'll remove the decimal places we'll select these we'll right click the mouse format cells we'll take this up we'll go to custom we'll delete General from here double inverted comma rupees double inverted comma close space has OK table amount so table amount will be total charges minus Advance Plus total charges minus we will come to the left hand side and we'll select Advanced from here and we'll hit enter we'll drag this down we'll go to view show and will remove the grid lines we will make this bigger over here we will type name of the hospital we'll go to home and will unwrap the text we'll change the font color will change the font style we'll make this bigger we'll cut this and better we will paste it over here over here we will create a button will go in insert illustrations shapes and we will select a desired shape over here we will take this in left side we change the color of the button we will remove the outlines means no outline we'll double click over here generate build or generate receipt Dash patient number or patient ID will go to home we'll take this in center and in mid we'll change the font style will make it bigger we'll increase the size of the box we right click the mouse format shape will go in the center shadow and will give it a shadow we'll go to text options we'll go in Center and will give text a shadow we'll close this will make the font bold we will adjust this we'll click next to the button and we'll change the color we'll change the font color over here we have to insert patient ID for which we want to create the receipt I'll insert a number over here p101 will increase the size will make it bold we'll rename the sheet this will be billing we'll insert one more tab over here this will be receipt we will go to billing sheet will copy from patient ID till end controls your command C we will come over here we'll click on V4 right click the mouse paste special we'll take this up values transpose and we'll do okay so we have got all the details over here now we will create the receipt but before converting this data in a receipt we will add our formulas we will click over here just in front of patient ID Plus or equal to we will go in Billing format and we'll click on first patient ID and we'll hit enter now we will insert the formula of vlookup or the function of vlookup Plus or equal to vlookup bracket open lookup value patient ID will be our lookup value will press function F4 and the cell will be logged comma table array we will go in Billing sheet and we'll select our table array or data from the top we will select this till end comma patient name is in column number 2 so we will put 2 over here but before putting 2 we will log the cell function F4 comma 2 comma 0 we will close the bracket and we will hit enter I'll come in the billing sheet in vlookup we are counting from here means patient ID is a lookup value I'll drag this across so our columns are till 29. we will go in receipt and we'll drag this formula till end we can see patient name all over so what we will have to do we will have to change the column numbers so here it's 2 next will be 3 then 4 then 5 6 7 8 9 10. and we will change this till 29th so we have inserted till 29. we will select the whole sheet and we'll wrap the text we'll make this bigger we will drag this row now we will select alternate rows we'll cut this in this Ctrl X or command X and we'll paste over here age we will leave this we will cut this one Ctrl X we'll paste in front of this Ctrl X or command X will paste over here admit date and discharge date we will select this we will right click the mouse format cells we'll take this up will go in date and we'll select a desired date format okay now we will cut this Ctrl X or command X and we'll paste it over here and in the same way we will cut all the alternate rows and we'll paste Ctrl X and we'll paste it over here we will come down we'll cut this and we'll paste we'll cut this from here and we'll paste it over here now we will go in page layout margins and will make the margin as narrow because we are going to create receipt which will be printable we will take this in left we will merge this will give it a border this will be payment receipt we'll select the whole page and we'll take all the things in mid we'll change the color of this will make the font size bigger now we will delete the blank rows we leave one blank row over here means over here we have got the doctor details so all the doctor details are over here for medicine charges What charges and discounts we will keep one row empty we'll select this by pressing Ctrl in case you are using MacBook you will have to press command we'll delete this we'll delete this also we'll select this and will create borders we'll create border for this also we'll put some color over here we'll change the font color we'll make it bold we will click on top and will take all the things in extreme left over here also we will take all the things in extreme left we will select this we'll right click the mouse format cells we'll take this up custom we'll delete this double inverted comma r s means rupees double inverted comma close has and we will do ok we will take this in Center we will take this in Center over here also we will insert rupees right click the mouse format cells custom remove General double inverted comma rupees double inverted comma close has ok over here we will type receipt date we'll leave one row empty this will be signature slash stamp we'll make this bigger will increase the font size will come down we'll select this and will merge we'll make this bigger we'll insert a color over here we'll change the font color we increase the font size stay safe stay fit stay healthy will come down here will be phone number or contact number and here will be address you can insert your address over here now we will select this and will give a thick border we'll select this means patient details and we change the font color we'll make it bold now we will see how our print will appear Ctrl p and we will preview the PDF file so this is how our payment receipt will appear we will create a button over here we will go to billing sheet and we'll copy this button Ctrl C or command c will go in receipt and we'll paste it over here we'll delete this and we'll type billing sheet we'll make this smaller we change the color now we will click on this button we will right click the mouse hyperlink we will go in this document and we'll select billing from here okay now whenever we will click on billing sheet we will be diverted to this sheet means the main sheet where all the calculations will be done we will delete this because this is really not required now we will click over here we will change the color we'll change the font color also now we will click on this button we'll right click the mouse hyperlink we will select receipt from here okay now we will go in receipt tab will delete patient ID from here Plus or equal to we'll go in Billing Tab and will select this and we'll hit enter we'll go in Billing sheet now we will create receipt for patient number 105 means James p105 we will click over here on generate receipt and the receipt of James got created we will create one receipt for another patient we'll go in Billing sheet this time we will create for patient number one one zero Alexander p110 we'll click over here on generate receipt and the receipt of Alexander got created we'll check the print preview Ctrl p PDF open in preview and this is a preview of how payment receipt so in this way you can create a fully automated Hospital Billing System I'll provide link of this file in description box in case you want to download youcam I am 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 thank you