Create a Medicine Bill Format in Excel for Financial Services with Ease

Medicine bill format in excel for Financial Services

In today's video we will see how we can create  Billing format for Hospitals in Excel. We will   be creating billing format as well as invoice for  the patients. This billing format will be fully   automated and we will be including various factors  like Room charges, Doctor visit fee and so on.   Hi my name is Vijay and you are watching Office  MoNK. Let's start the video without any delays. This is a hospital billing system  which I have created in advance.   Here we have got details like Patient ID, Patient  name, Age, Gender, Advance. If someone paid the   advance, in that case we will have to mention over  here. In which ward patient is getting shifted.   Ward charges, admit date, discharge date,   total days the patient stayed in the ward,  Doctor Who visited the patient in the ward   so name of the doctor is over here. Visit charges,  how many visits of that particular doctor happened   and total charges of that particular  doctor. When we come in right hand side,   name of second doctor, visit charges, number  of visits total charges of the second doctor,   amount by the patient and total charges of the  patient when we come in the left hand side over   here we have got the option to generate receipt  we will have to insert patient ID over here   and we will have to click on generate receipt  and the receipt will be updated or generated   you can give the command Ctrl p and from here  you can take the printout or if you want you   can save this file in PDF also to create this  format we will need certain things like word   name along with word charges doctor's name their  department and their fees let's start creating a   new sheet now I'll open a new Excel file I have  opened a new Excel file in which I have got few   details like word name along with their charges  doctor's name along with department and fees   first thing what we will have to do we will click  over here and we'll type doctor's name Dr Albert   bracket open we will type name of the department  we will close the bracket and will hit enter   now we will press Ctrl e and all the names  will be updated along with the Department   we will copy this Ctrl C or command C we  will right click the mouse paste special   values and we will delete this I'll delete none  from here means the none which is in bracket and   will hit enter we will insert a new sheet and  this will be our billing sheet billing we will   click over here on V3 serial number patient  ID patient name age gender advance word word   charges admit date or admission date discharge  date total days doctor name bracket Force Doctor   Bracket close visit charges number of visits we  will select this and will adjust the column size   total charges bracket first doctor now we will  copy this Ctrl C or command C and we will paste   it over here we will come in right side we  will select this and will adjust the columns   this will be our doctor name second doctor  over here also we will do it second doctor   payable amount total charges or total amount we  will come in left side we'll click over here and   we'll take in mid and Center serial  number we will take for 10 patients patient ID we will have to insert it manually I'll  drag this patient name we will have to insert it   manually I'll take patient's name and age from  my example file and we'll paste it over here I'll take this in Center gender we will  create drop down for this we will go in data   data validation list in Source  we will type it manually mail not specified we will do okay and we will drag this till  end from here we can select the gender Advance I'll take a random figure 5000  I'll take 5000 for all the patients   I'll take this in Center Ward we will  create drop down for this we will go in data   data validation list Source we  will go in our setting sheet   and we'll select this or we can select from here  also okay and drop down for what got created   we will drag this till end we'll  adjust the column size we'll change it word charges we will insert our formula over  here and the formula will be plus or equal to   vlookup bracket open lookup value we  will take this as lookup value means   what name as lookup value comma table  array we will go in our setting sheet   and we'll select this comma What charges are in  column number 2 1 2 so we will type 2 over here   comma 0 we will close the bracket and we'll hit  enter so we have got our watt charges over here   before dragging the formula we will insert if  error just behind vlookup if error bracket open   we will come in the end comma double inverted  comma twice bracket close and we will hit enter   now we will drag the still end admit date and  discharge date we will have to insert it manually   I'll select this we'll right click the mouse  we'll go to format cells date and we'll select our   preferred date format okay now I'll insert a date  over here fifth of April I'll take this in Center   I'll drag this till end discharge date I'll  insert a random date over here and we'll drag   this till end total days formula for total  days will be plus or equal to discharge date   minus admit date Plus 1. we are doing plus one  over here because the day when customer gets   admitted in the hospital and the day when he get  discharged total days gets counted I'll hit enter   and we'll drag this I'll click over here  and will insert criteria of if if bracket   open discharge date is equal to 0 comma double  inverted comma twice comma I'll come in the end   we'll close the bracket and we'll hit enter  now I'll drag this till end I'll select this   and will insert a color we'll change the font  color will increase the font size we'll select   the whole sheet and we'll change the font style  we'll go in View and we'll remove the grid lines   now I'll select this and will insert borders  I'll take this in left side I'll click over here   and will merge those cells and  we'll type name of the hospital we'll select this we'll change the background  color will change the font color will increase   the size and will change the font style now  I'll click over here will most this and we'll   change the font color we'll change color  of this over here I'll type Billing System   we'll click over here and we'll change the  font style we'll increase the font size so this   thing is done we'll select this and we'll double  click the mouse and the columns will be adjusted   doctor's name we will create drop down list for  this we will go in data data validation list   source we will go in our setting sheet we'll take  this right side and we'll select the doctor's   name we will select from Top ok we will drag this  till end so drop down list for doctors got created   we will copy this Ctrl C or command C and we  will paste it over here in second doctor's name   visit charges we will insert our formula over here  and the formula will be plus or equal to vlookup   bracket open lookup value we will select doctor's  name as lookup value comma table array we will   go in our setting sheet and we'll select this  face is in column number three so we will put   3 over here comma 0 we will close the bracket  and we'll hit enter we will take this in mid   before dragging the formula we will insert  if error criteria just behind vlookup if   error bracket open we will come in the end comma  double inverted comma twice bracket close and we   will hit enter we will drag this till end number  of visits we will have to insert it manually total charges for first doctor it will be plus or  equal to visit charges into number of visits enter   we will drag this till end we will insert  criteria of if error just behind the formula   if error bracket open we will come in  the end comma double inverted comma   twice bracket close and we will hit enter  now we will drag this till end now we will   insert formula for visit charges or better  we will do one thing we will come over here   we will click just after I and will press function  F4 and the cells will be logged enter we will drag   this we'll copy our formula from here and we'll  paste it over here in second doctor's visit charge   and we will drag till end number of  visits we will have to insert it manually   we will drag this till end we will delete this total charges for second doctor  we will copy our formula Ctrl   C or command C and we will paste it  over here we will drag this till end   we will come in right hand side table  amount the formula will be plus or equal to   bracket open it will come in left hand side word  charges into total days we will close the bracket   Plus total charges of first doctor Miss  total visit charges of first doctor plus   total visit charges of second doctor  we will come in the beginning of our   formula and will insert criteria of if error  if error bracket open we will come in the end   comma double inverted comma twice  bracket close and we will hit enter   so we have got total payable amount over here we  will drag this till end will come in right hand   side we'll take this in Center total charges  it will be plus or equal to table amount Plus   advance and we will hit enter we will insert if  error just behind our formula if error bracket   open we will come in the end comma double inverted  comma twice bracket close and we will hit enter   now we will drag this till end so this format  is ready now we will go in insert illustrations   shapes and we will select a desired shape I'll  click over here and we'll drag the shape we'll   remove the outline means no outline will double  click on the shape and we'll type print receipt   we'll click outside the shape will go in  home we'll take this in center and in mid   and will increase the font size we'll change  the font style will make the row size bigger   will adjust it will take this down we  will click over here we will put a color   and we'll change the font color we'll change  the font style will increase the font size we   will have to insert patient ID over here to  generate the bills or to print the receipt p101 I'll take this in center and in mid will  increase the font size now we will insert a new   sheet and we'll rename it as receipt we will go  in Billing we'll copy from patient ID till end Ctrl C or command C we will come in receipt we'll  click over here we'll right click the mouse paste   special values transpose okay we will double  click over here and we'll adjust the column size   we will insert our formulas over here and later  we will format it we will click just in front   of patient ID and will change the color we'll  change the font color we will have to insert it   manually rest of the things will be formulated  I'll click over here just in front of patient   ID and will insert my formula but before  that I'll insert a patient ID over here p101 patient name now we will insert our formula over  here and the formula will be plus or equal to   vlookup bracket open we will select patient ID  as lookup value we'll press function F4 and the   cells will be logged comma table array we will go  in our billing sheet and we select from patient   ID till end will press function F4 and the sales  will be logged comma we will come in left side   patient's name is in column number 2 1 2 so we  will do 2 over here comma 0 we will close the   bracket and we'll hit enter so we have got our  patient's name over here I'll drag this till end   I'll come in Billing sheet all the things  are in a sequence means column numbers are   changing over here like 2 3 4 and so on so we  will come over here in receipt we'll click on   edge and we'll change the column number we'll do  it three this will be our 4 this will be our five this will be our six this will be our  seven this will be our 8 this will be our 9   we will select this and we'll change the date  format format cells date and we'll select a   desired date format okay so this was column  number nine this will be column number 10   column number 11 will adjust this column  number 12. column number 13 column number 14   column number 15 column number 16  column number 17 column number 18   column number 19 and it will be column  number 20. enter we will take all the   things in left hand side we'll select the  whole sheet and we'll change the font style   we'll take this in center and in mid we'll take  this in left now we will adjust it patient ID is   over here patient name we will cut this Ctrl  X or command X and we will paste over here   age we will cut this and we'll paste over  here gender we will cut this and we will   paste it over here Advance we will cut this and  we'll paste it over here word same control X or   command X and we'll paste over here we'll click  over here and you will manage the column size   we'll cut this Ctrl X for command X we will paste  over here Ctrl X command X we will paste over here   we will cut this and we'll paste over  here same with this we will cut this   we'll paste it over here visit charges  Ctrl X or command X we will paste over here   number of visits we will cut this and  we'll paste over here total charges   control X or command X will paste over here  for second doctor we will do the same thing table amount we test over here total charges  we will paste over here we'll double click over   here and we'll adjust the column size we'll take  this in left and we'll take this in left we'll go   in view and we'll remove the grid lines we will  select this will go in home and will give outline   we'll select this and will give borders same  with this we will select this will give borders   and borders over here we'll select this  and will merge it we'll give it a color   will change the font color and we'll type  name of the hospital Divine monk hospitals   receipt will change the font style will make  it bigger and will increase the font size   we will remove color from here we'll make it white  and font color as blue we will make the font bold   we'll click over here and we'll type receipt date  we'll hit enter now we will insert line over here   we will go in borders bottom double border  we'll select this will click over here signature   we'll take this in left we'll copy this and we'll  paste it over here we will click over here we'll   select till end will merge it and we'll type  our slogan stay safe stay fit stay healthy   we'll select this and we change the color will  change the font color we'll change the font style   will make it bigger over here I'll type  address and we'll put my address over here will come down and we'll insert phone number  we'll insert borders we'll take this in left side   and we'll press Ctrl p so all the things  are looking proper we will have to adjust   row sizes I'll cancel it will make this smaller  we'll select this and will increase the row size   we'll select this and we'll increase the row size  and over here also we will increase the row size   for this also we will press Ctrl p and we'll check  again now it's looking better in case you want to   take the print out connect your printer and take  the print out or if you want you can save it as   PDF I'll save it as PDF file save as PDF file  number two I'll do okay means I'll save the file   now we will check how our receipt is looking so  this is the receipt which we have created right   now I'll come in my Excel file we had created  this receipt for patient number one zero one   we will create for patient number one zero two  also and we'll check how it looks t one zero two   so we have changed the patient ID now we  will click over here on the button we'll   right click the mouse will go in hyperlink  or link this document and we will select   print receipt and we'll do okay so both the  sheets got connected now I'll click over here   and we'll check the receipt  of patient number one zero two   it haven't got updated because we haven't  connected our sheets I'll click over here   Plus or equal to will go in Billing and will  click over here and we'll hit enter now both   the sheets got connected I'll come in Billing  and we'll do it patient number one zero three   and we'll hit enter we'll go in receipt now it  got updated very properly name of the patient is   Jeremy will come in Billing name of patient  number three is Jeremy age is 26. over here   all the things got updated very properly so  our format is ready now I'll provide link   of this format in my description box  in case you want to download you can 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