Hotel Bill Format in Excel (Create with Easy Steps)

Hotel Bill format is an invoice format that 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. While you can create the bill manually in a bill book with the hotel’s name and logo, automated programs can easily streamline the process to entering the information and printing the result.

You can also create a hotel bill format table in Microsoft Excel. It’s a free and relatively straightforward way to get professionally-looking hotel bills made for your services. Here’s a step-by-step guide to creating a Hotel Bill Format in Excel.

Download Practice Workbook

Hotel Bill Invoice.xlsx

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 to put it into your file.

Resize and move the logo wherever you need it to be.

Type your Hotel Name in a cell.

Change the font formatting (font style, size, color etc.) from the Font ribbon of the Home tab according to your needs.

Here’s an example for Harlow Solid Italic as the font style and 16 as the font size.

Insert information you want to include in the bill (such as the address, phone number, email address) below your hotel name.
Select suitable font format from the Font ribbon of the Home tab.

Here’s a showcase of the Constantia font style in size 10.

2. Add Billing Information Format

In the second step, you need to add the format for Billing Information. This is where you’ll add the information about the person or organization who will pay the bill. Some of the fields you’ll need can include Name, Address, city, Phone number, and Email address. You can customize this according to your needs.

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 number of the particular bill.

4. Make the Format for Bill Calculation

Formatting the bill calculation is the most important step of preparing a hotel bill format in Excel. You can create a bill that automatically calculates the total costs based on the listed items and prices. Here’s how:

Insert the information headers in different cells of a row below the billing information. Here, relevant information for billing will be inserted in cells below each header.

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 an invoice-like format for inserting information for billing.

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

Now it’s time to automate the table to calculate the bill totals:

Select the Check In and Check Out column and go to Home > Number > Short Date. The dates will be used to calculate No. of Day of your hotel bill format.

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 nights of stay.

Press ENTER and drag the cell to the end of the bordered columns.

Type the following formula in the Line Total 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’s bottom-right corner down to cover all other cells in that column.

5. Hide 0 Values from the Bill Format

The previous step puts 0 for values where you don’t really need them, so you should hide them to make the bill more professional. Here’s how you can do that:

Go to File, then Options.

Go to Advanced tab, 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

6. Add Other Bills and Get the Total Bill

Add the Subtotal, Taxes, Others and Total cells below the Line Total of your hotel bill format.

In the cell for Subtotal calculation (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 the Total calculation (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

Finally, you can make a text box to keep any notes relevant to the bill or stay:

Select the cells which you want to keep for notes.
Go to Home > Borders and select Outside Borders.

Go to Home > Merge & Center and select Merge Cells. This way, you get one big cell that you can fill with text.

8. Add the Acknowledgment Part of the Hotel Bill Format

The Acknowledgment section is reserved for signatures and stamps. 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.

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

9. Final Formatting of the Hotel Bill Format

Go over the formatting and make sure all values inside the table are using the correct style and format and the rows are the same height. You can also format the cells for that use currency (price/day and total values) as accounting numbers (pressing the “$” button in Home > Number should do the trick if you use USD).

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. Here’s how your bill should look at this point.

10. Test the Hotel Bill Format

Now, to check if the hotel bill table is working properly, insert some information. If you insert the data for Check In, Check out and Price/Day, the subtotal and the total bill should be calculated automatically.

Conclusion

The hotel bill format shown in the example above is only the start. You can play around with formatting and additional calculation options. Some bills require specific information, so make sure to include these fields in your table. You can also use data filtering from other tables to automatically populate the table, which you can learn in other Exceldemy articles and courses.