+ Reply to Thread
Results 1 to 11 of 11

How to properly set up PIVOT Table for hotel data set?

  1. #1
    Registered User
    Join Date
    12-18-2018
    Location
    Utrecht
    MS-Off Ver
    2016
    Posts
    14

    How to properly set up PIVOT Table for hotel data set?

    Hi guys,

    I received a massive hotel data set (booking sources, cancellations, cancellation dates, arrival dates, departure dates, rate sum etc. included) with booking data from 2012-2019.
    Now, I would like to know how you make a pivot table where I can see for which arrival date how many people booked through which booking source (booking.com, expedia, agoda, etc.).
    What should be in my 'Filters', 'Rows', 'Colums', and 'Values?'
    I want to find out for example: on the 8th of april 2017, ........ people arrived with a booking made through booking.com.
    How should I set up my pivot table to find this out?

    Many thanks!

  2. #2
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: How to properly set up PIVOT Table for hotel data set?

    The important thing when using Pivot Tables is to ensure that each column is a unique TYPE of thing, not a unique value for a type of thing. It's the layiut of the underlying data that's important

    If you upload a representative example workbook no doubt we can advise further.
    Richard Buttrey

    RIP - d. 06/10/2022

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  3. #3
    Registered User
    Join Date
    12-18-2018
    Location
    Utrecht
    MS-Off Ver
    2016
    Posts
    14

    Re: How to properly set up PIVOT Table for hotel data set?

    Thank you for your reply!

    Please find below an example of what my workbook looks like. I would like to know how many 'Internet' bookings in total there were made on the 1st of april 2018, via a Pivot table. Same goes for 'TI' bookings.
    Hope this helps a tad bit, thanks in advance!Example of data set.jpg

  4. #4
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: How to properly set up PIVOT Table for hotel data set?

    Hi,

    Pictures are rarely much use. You have a workbook. Please upload it

  5. #5
    Registered User
    Join Date
    12-18-2018
    Location
    Utrecht
    MS-Off Ver
    2016
    Posts
    14

    Re: How to properly set up PIVOT Table for hotel data set?

    Example data set workbook.xlsx

    Thanks in advance.

  6. #6
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,811

    Re: How to properly set up PIVOT Table for hotel data set?

    attached is a Pivot Table with Sources across as columns and arrivals by date as both rows and counts.
    Attached Files Attached Files
    Alan עַם יִשְׂרָאֵל חַי


    Change an Ugly Report with Power Query
    Database Normalization
    Complete Guide to Power Query
    Man's Mind Stretched to New Dimensions Never Returns to Its Original Form

  7. #7
    Registered User
    Join Date
    12-18-2018
    Location
    Utrecht
    MS-Off Ver
    2016
    Posts
    14

    Re: How to properly set up PIVOT Table for hotel data set?

    You guys are absolute legends. Thank you very much - now I need to do this on a way bigger scale so would you please be able to specify the steps I need to take in order to get the same kind of Pivot table, but then on a bigger scale?
    Thanks in advance, I appreciate all help!

  8. #8
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: How to properly set up PIVOT Table for hotel data set?

    To generalise this I'd first set up a dynamic range name which will automatocally adjust to the size of your data.

    So create a name say "Data" and defne it as

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Now click in the PT, go to the Pivot Table Tools menu and click the Change Data Source item. Where yiuo currently see A1:D42 enter instead the word Data. Then OK.

    I'd be onclined to move the PT to a sheet of its own

    If the data changes you'll need to right click in the PT and choose Refresh. You can aitomate this with a VBA sheet activate macro in the sheet that contains the PT.
    Please Login or Register  to view this content.
    so that every time you go

  9. #9
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,811

    Re: How to properly set up PIVOT Table for hotel data set?

    I'd be onclined to move the PT to a sheet of its own
    I concur with Richard on this, but for the example, I felt it was ok to show on the same page.

  10. #10
    Registered User
    Join Date
    12-18-2018
    Location
    Utrecht
    MS-Off Ver
    2016
    Posts
    14

    Re: How to properly set up PIVOT Table for hotel data set?

    Thank you very much - I'll give it a shot, and am certain that with your provided help, I'll be able to do it!
    Thanks very much guys!

  11. #11
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: How to properly set up PIVOT Table for hotel data set?

    Our pleasure and thanks for the rep.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Pivot table does not sort grouped dates properly
    By Chomeske in forum Excel General
    Replies: 6
    Last Post: 11-05-2018, 04:41 AM
  2. Pivot table does not sort text properly
    By brunoalmeida in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 01-11-2017, 10:44 PM
  3. Sort Data by Date in Pivot Table
    By arpanpanchal in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 12-16-2014, 06:18 PM
  4. Date Sort in Pivot Table - Not working properly
    By claudiocustodio in forum Excel Charting & Pivots
    Replies: 4
    Last Post: 07-12-2013, 04:30 PM
  5. sort pivot table by date
    By reillc01 in forum Excel General
    Replies: 1
    Last Post: 10-15-2010, 04:42 PM
  6. Pivot Table sort by date
    By dblatt in forum Excel General
    Replies: 2
    Last Post: 09-30-2010, 10:29 PM
  7. Pivot Table Sort by date
    By Bush Kangaroo in forum Excel General
    Replies: 1
    Last Post: 03-09-2005, 08:34 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1