Hotel Bill Format in Excel (Create with Easy Steps)

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.

insert picture

After that Insert Picture window will be opened.

➤ Select your hotel logo and click on Insert.

insert image

As a result, the logo will be inserted in your Excel file.

➤ Resize and move the logo according to your need.

resizing

After that,

➤ Type your Hotel Name in a cell.

hotel name

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.

formatting name

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.

hotel information


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.

Billing information


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.

invoice


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.

hotel bill format in excel

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.

border

As a result, you will get a format for inserting information for billing.

hotel bill format in excel

You can choose a color for the header to make it more attractive from Home > Theme Colors.

formatting

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.

hotel bill format in excel

After that,

➤ Type the following formula in the first cell of the No. of Day column (F20),

=E20-D20

The 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.

hotel bill format in excel

Now, to calculate the line total,

➤ Type the following formula in cell (H20),

=F20*G20

The 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.

hotel bill format in excel


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.

hotel bill format in excel

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.

hotel bill format in excel

Now, you will see the 0 in the formula cells are not showing anymore.

hotel bill format in excel


Similar Readings


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.

total row

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.

total

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.

total


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.

border

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.

notes


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.

hotel bill format in excel

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.

acknowledgement

At last,

➤ Type concluding words at the end of the hotel bill format.

hotel bill format in excel


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.

final formatting

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.

hotel bill format in excel


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.

check bill format


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.


Further Readings

Prantick

Prantick

Hi, I'm Prantick Bala, currently working and doing research on Microsoft Excel in Softeko.com. I have completed BSc in Naval Architecture and Marine Engineering From Bangladesh University of Engineering and Technology (BUET). Currently I am in the last semester of my MBA at Institute of Business Administration (IBA), Dhaka University (DU). I'm always eager to learn through experience and have a deep interest in solving problem to get optimum solution.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo