Prantick Bala
Hotel Bill format is an invoice format which you can use to create hotel bills for clients in order to document information about hotel services as well as charges incurred by the customer while staying in the hotel. This bill can be created manually in a bill book with the hotel’s name and logo. But it is not that much convenient and by using any automated programs you can easily create the hotel bill by just entering the information. You can also create a hotel bill format by using Microsoft Excel. The advantages are that you will be able to prepare bills just by entering the information in the Hotel bill format in Excel and it is free of any additional cost. In this article, I’ll show you how you can prepare Hotel Bill Format in Excel by a step to step guideline.
Download Practice Workbook
10 Steps to Make a Hotel Bill Format in Excel
1. Add Hotel Logo and Information in the Format
First, you have to add your hotel logo and information in your hotel bill format. To insert the hotel logo,
➤ Go to Insert > Illustrations > Pictures > Insert Picture From > This Device.
After that Insert Picture window will be opened.
➤ Select your hotel logo and click on Insert.
As a result, the logo will be inserted in your Excel file.
➤ Resize and move the logo according to your need.
After that,
➤ Type your Hotel Name in a cell.
Now,
➤ Change the font formatting (font style, size, color etc.) from the Font ribbon of the Home tab according to your needs.
For this demonstration, I’ve chosen Harlow Solid Italic as the font style and 16 as the font size.
After Inserting the Hotel name,
➤ Insert information ( which you want to include in the bill such as Hotel address, Phone number, Email address, etc.) about your hotel below your hotel name.
➤ Select suitable font format from the Font ribbon of the Home tab.
In this hotel bill format, I’ve selected Constantia as font style and 10 as font size.
2. Add Billing Information Format
In the second step, you need to add the format for Billing Information. Here the information about the person or organization who will pay the bill has to be added. In this format, I’ve added the format for Name, Address, city, ID, Phone number, and Email address of the bill payer. You can customize this according to your needs. After adding the format,
➤ Select a suitable font format from the Font ribbon of the Home tab for this section as shown in the previous step.
3. Add Date and Invoice No. in the Bill Format
In this step, you need to add the billing Date and Invoice number in the right top corner of your hotel bill format. Billing Date is the date on which the bill will be given to the customer and the Invoice number is the serial of the particular bill. Don’t forget to choose a suitable font format for this part of your hotel bill format.
4. Make the Format for Bill Calculation
Making the format for bill calculation is the most important step of preparing hotel bill format. In this section, I’ll show you how you can create a format where the total bill will be automatically calculated after inserting the necessary information.
First,
➤ Insert the following headers in different cells of a row below the billing information.
Here, relevant information for billing will be inserted in cells below each header.
After that,
➤ Select the cells of the header row and a number of empty cells below this row. (The number of empty rows you want to keep in your format for inserting information)
➤ Go to Home > Borders and Select All Borders.
As a result, you will get a format for inserting information for billing.
You can choose a color for the header to make it more attractive from Home > Theme Colors.
In this time we will automate the format to calculate hotel bills.
➤ Select the Check In and Check Out column and go to Home > Number > Short Date.
Now, you can input date in this column in date format which will be used to calculate No. of Day of your hotel bill format.
After that,
➤ Type the following formula in the first cell of the No. of Day column (F20),
=E20-D20The formula will calculate the difference between the Check Out and Check In and will give the number of days of stay.
➤ Press ENTER and drag the cell to the end of the format.
Now, to calculate the line total,
➤ Type the following formula in cell (H20),
=F20*G20The formula will calculate the bill of each entry by multiplying No. of Day with Price/Day.
➤ Press ENTER and drag the cell to the end of the format.
5. Hide 0 Values from the Bill Format
In the previous step, we have seen a number of 0 values have appeared after applying the formula. In this step, I’ll show you how you can hide these 0.
➤ Go to, File > Options.
It will open the Excel Options window.
➤ Go to Advanced, uncheck the box show a zero in cells that have zero value, and click on OK.
Now, you will see the 0 in the formula cells are not showing anymore.
Similar Readings
- Labour Contractor Bill Format in Excel (Download Free Template)
- Tally Bill Format in Excel (Create with 7 Easy Steps)
- Transport Bill Format in Excel (Create in 4 Simple Steps)
- How to Create a Cash Bill Format in Excel (A step-by-step Guideline)
6. Add Other Bills and Get the Total Bill
In this step,
➤ Add the Subtotal, Taxes, Others and Total cells below the Line Total of your hotel bill format.
In the cell for Subtotal calculation (cell H28),
➤ Type the following formula and press ENTER,
=SUM(H20:H27)The formula will add all the line total of the hotel bill.
In the cell for Total calculation (cell H31),
➤ Type the following formula and press ENTER,
=SUM(H28:H30)The formula will give the total hotel bill.
7. Add a Note Sections in the Bill Format
Now, I’ll show you how you can add a section for keeping notes in the hotel bill format.
First,
➤ Select the cells which you want to keep for notes.
Then,
➤ Go to Home > Borders and select Outside Borders.
After that,
➤ Go to Home > Merge & Center and select Merge Cells.
As a result, the Notes section of the hotel bill format will be prepared.
8. Add the Acknowledgement Part of the Hotel Bill Format
Now, you will need to add the acknowledge part of the hotel bill format. In this part, you have to add the Guest’s signature and Cashier’s signature. You can also add any reminder you want to give to the guests. From the image below you will get the idea of how you can add this part.
If you use more than one cell for any input in the format,
➤ Go to Home > Merge & Center and select Merge Cells to merge those cells.
At last,
➤ Type concluding words at the end of the hotel bill format.
9. Final Formatting of the Hotel Bill Format
Now, the hotel bill format is almost prepared for using.
➤ Give a final check to the formatting and change any of those if you need to from the Font and Alignment ribbons of the Home tab.
To make the hotel bill format more attractive you can add an outside border or apply different colors and font styles from the Font and Alignment ribbons of the Home tab. Your final hotel bill format will look like the next image.
10. Check the Hotel Bill Format
Now, to check if the hotel bill format is working properly, insert some information, in the format. If you insert the data of Check In, Check out and Price/Day, the subtotal and the total bill will be calculated automatically.
Conclusion
I hope now you will be able to make a hotel bill format in Excel. You can download the format which I have prepared to write this article from the Download Practice Workbook section and use it in your business. This hotel bill format is in the basic form. If you need a more customized hotel bill format, you can leave a comment and let me know. I can prepare the format as per your specifications. Please leave a comment if you have any kind of confusion. Finally thanks for your time, I hope this article was helpful to you.
