If want to see how your rooms are performing, this tutorial will explain how to generate a PivotTable on Excel for individual rooms that contains the following information for the dates you selected in your report:
- Total revenue.
- Average revenue per booking.
- Average daily rate per booking.
- Average amount of nights.
- Average adult occupancy per booking.
Create the PivotTable
Generate the Booking Summary report. Our tutorial 'How to generate a report' will guide you if you need help with this.
- Click on any one cell in the report that has text in.
- Click on the Insert tab.
- Choose PivotTable.
- Click on 'OK' on the pop-up window.
Edit the table contents
- Choose 'Room Name' to add to the report and drag to the rows section in the PivotTable fields.
- Drag the 'Revenue' category from field name to the values field. This will default to being 'Sum of Revenue'.
- Drag the 'Revenue' category from field name to the values field for a second time to create a 'Sum of Revenue2' field.
This is what you should see:
- Click on 'Value Field Settings' on the drop-down menu to the 'Sum of Revenue2' in values block.
- Click on 'Average' in the pop-up.
- Click 'OK'.
You should now see 'Average of Revenue2'. You can follow the same steps for 'Avg rate', 'Nights' and 'Adults' to get the Average categories in the 'Values' field.
Once done, highlight the fields with numbers on the PivotTable, and format the cells to a number format. Video guidance on how to format numbers in cells on Microsoft Excel
View your results
You should see a report that looks like this when you have followed the steps. This will show you the information per booking per room to provide a visual on how your rooms are performing.
Click here for more guidance on PivotTables on Excel.