Start learning
My learning

The Complete Excel Course with AI Integration

Excel course for hotel managers

For hotel managers and heads of department who read occupancy, income and costs in Excel every day. The Complete Excel Course with AI Integration has no hotel data of its own; this page picks out the lessons the daily and monthly reports use: ratio formulas, SUMIFS, charts, PivotTables, the MarketPulse dashboard as a model for the daily business review and the FinanceGuard budget vs actual report.

See the plans Watch free lessons

Taught by Sagnik Bhattacharya and Virginia Thorn

The Complete Excel Course with AI Integration: Sagnik Bhattacharya and Virginia Thorn

What you will be able to do

At work, you will be able to:

  • Work out occupancy, average daily rate and RevPAR from rooms and room revenue with ratio formulas, absolute references and IFERROR.
  • Set the same period this year and last side by side with SUMIFS and date conditions, and in a PivotTable with dates grouped by month and year.
  • Compare each department's budget with its actual costs and income using variance, percent variance and colour-coded number formats.
  • Count complaints by category and month with COUNTIFS and PivotTables.
  • Chart occupancy against average daily rate on a combo chart with a secondary axis, with sparklines for each week.
  • Build a one-screen daily business review with KPI cards, PivotCharts, slicers and a timeline.

Projects

What you will build.

The course has 14 projects. These four matter most here.

  • Marketing dashboard in Excel with slicers at work (MarketPulse): KPI cards, a spend against revenue chart and a monthly ring, filtered to one channel by the Channel and Campaign slicers.

    Project 08

    MarketPulse

    One screen that shows which campaigns earn back their spend.

    The build of a daily business review: ratio KPIs guarded with IFERROR, KPI cards for occupancy, average daily rate and RevPAR, slicers and a timeline to move between days.

  • Budget vs actual dashboard in Excel (FinanceGuard): total budget, actuals and variance cards above a monthly line chart and a column chart by department.

    Project 09

    FinanceGuard

    The budget vs actual report finance teams send every month.

    Each department's budget against its actual costs and income, variances coloured, and slicers to move from rooms to food and beverage.

  • Excel PivotTables and a PivotChart of sales data (SalesPlusPerformance): net sales by product category as a column chart, with State, Region and City filters.

    Project 05

    SalesPlusPerformance

    A sales question answered in seconds, straight from the order data.

    Room nights and income by month, channel or room type from a bookings export, with dates grouped and Refresh All for the new day's data.

  • Nine Excel chart types from the Marketing Engagement Dashboard: combo, bar, funnel, Pareto, waterfall, histogram, box and whisker, sunburst and treemap.

    Project 04

    Marketing Engagement Dashboard

    Nine charts, each picked for the question it answers.

    Charts for a hotel's numbers: a combo of occupancy against average daily rate on two axes, line charts for the season, sparklines for each week.

Lessons

The lessons that matter, in course order.

The Complete Excel Course with AI Integration has 186 lessons. This page picks out 44 of them, 3 h 23 min of video. Each lesson keeps its number in the course.

Where to start

Lesson 10 Relative vs absolute references in Excel

Begin at Lesson 10 if you already enter figures and write simple formulas: the hotel ratios start with absolute references and percentages. New to Excel? Begin at Lesson 1. If SUMIFS and PivotTables are part of your day, go to Lesson 105 for the daily review dashboard, or to Lesson 115, where budget against actual begins.

44 of 186 lessons · 3 h 23 min

01 Microsoft Excel fundamentals: building a foundation from scratch 3 of 13 lessons · 10 min The arithmetic behind the hotel ratios: absolute references for the rooms available held in one cell, percentages for occupancy, and IFERROR for a day with no rooms sold. The lessons use a personal budget; occupancy, average daily rate and RevPAR are the same kind of division.
  1. 10 Start here Relative vs absolute references in Excel Dollar signs and mixed references 3 min
  2. 11 Calculating percentages in Excel Each expense as a percentage of income 2 min
  3. 12 Common Excel errors and how to fix them DIV0, NAME, VALUE errors and IFERROR 4 min
04 Essential formulas and functions (IF, SUMIF and more) 4 of 13 lessons · 25 min SUMIF and SUMIFS for room nights and income by date, department or channel, COUNTIF and COUNTIFS for complaints by category, and date arithmetic to set the same period this year and last side by side.
  1. 36 SUMIF and COUNTIF in Excel Conditional sum and count with one criterion 8 min
  2. 37 SUMIFS and COUNTIFS in Excel Sum and count with multiple criteria 7 min
  3. 38 The Excel function library Insert Function dialog and Formula AutoComplete 5 min
  4. 39 Date and time functions in Excel TODAY, NOW, MONTH and date arithmetic 6 min
06 Data visualisation: charts and graphs 7 of 9 lessons · 34 min Charts for the daily and monthly figures: column and line charts for occupancy over a season, a combo chart with occupancy on a secondary axis against average daily rate, clean formatting and sparklines that show each week in a cell.
  1. 48 Choosing the right Excel chart Column, line, pie, combo and modern chart types 4 min
  2. 49 Column charts in Excel Insert, format and label a column chart 5 min
  3. 50 Line charts in Excel Show trends over time with a line chart 4 min
  4. 51 Pie charts in Excel Show proportions and parts of a whole 4 min
  5. 52 Combo charts in Excel Column and line chart with a secondary axis 4 min
  6. 53 Formatting charts in Excel Titles, data labels, colours and chart styles 7 min
  7. 54 Sparklines in Excel Line, column and win-loss mini charts in cells 5 min
07 PivotTables and PivotCharts: data analysis made easy 10 lessons · 33 min PivotTables for room nights, income and complaints by month, department or channel: dates grouped, slicers, PivotCharts and a refresh when the next export arrives. The lessons use sales orders; a bookings export summarises the same way.
  1. 57 PivotTables 101 in Excel What a PivotTable does and the sales dataset 5 min
  2. 58 Preparing data for PivotTables Clean data with TRIM, PROPER, find and replace 5 min
  3. 59 Create your first PivotTable in Excel Insert a PivotTable from a sales table 1 min
  4. 60 PivotTable fields in Excel Rows, values, columns and Value Field Settings 2 min
  5. 61 PivotTable summaries in Excel Sales by product category and grouping by date 4 min
  6. 62 Slicers in Excel Filter PivotTables with clickable buttons 3 min
  7. 63 PivotCharts in Excel Create interactive charts from a PivotTable 5 min
  8. 64 PivotTable design Tab Report layouts, PivotTable styles and number formats 5 min
  9. 65 Refresh a PivotTable in Excel Update pivots when source data changes 2 min
  10. 66 Excel Ninja Challenge PivotTable sales by region, category and monthly trend 1 min
11 MarketPulse campaign intelligence dashboard 10 lessons · 50 min MarketPulse as a daily business review: clean the data, plan the measures, ratio KPIs with IFERROR, PivotTables, KPI cards, charts, slicers and a timeline on one screen. The project tracks marketing campaigns; occupancy, average daily rate and RevPAR take the same build.
  1. 105 MarketPulse marketing dashboard in Excel Project overview and campaign data 4 min
  2. 106 Clean and check marketing data in Excel Remove duplicates, Copilot KPI plan 4 min
  3. 107 Plan a marketing dashboard with ChatGPT KPIs and the PivotTable strategy 4 min
  4. 108 Marketing PivotTables in Excel Build the MarketPulse pivot back end 6 min
  5. 109 CTR, conversion rate and ROI in Excel Marketing KPI formulas with IFERROR 4 min
  6. 110 PivotTables with Copilot Generate and verify marketing pivots in Excel 6 min
  7. 111 Marketing charts and a timeline in Excel MarketPulse dashboard PivotCharts 4 min
  8. 112 KPI cards in Excel Marketing KPI cards for the MarketPulse dashboard 7 min
  9. 113 Slicers for an Excel dashboard Interactive filtering in MarketPulse 4 min
  10. 114 Polish an Excel marketing dashboard Final formatting and insights in MarketPulse 9 min
12 Project: FinanceGuard budget variance analyser (budget vs actual Excel dashboard) 10 lessons · 52 min FinanceGuard for budget against actual by department: budget and actual tables, variance and percent variance, colours for an overspend, PivotTables, KPI cards, a combo chart and slicers. The project uses a company budget; rooms, food and beverage and other departments slot into the same columns.
  1. 115 FinanceGuard budget vs actual dashboard Excel project overview 3 min
  2. 116 Excel tables for budget and actual data Organise the FinanceGuard datasets 4 min
  3. 117 Budget vs actual model in Excel SORT, UNIQUE, VSTACK, LET, TEXTSPLIT, SUMIFS 8 min
  4. 118 Budget variance in Excel Calculate variance and percent variance 4 min
  5. 119 Custom number formats in Excel Show budget variances with colours and signs 2 min
  6. 120 PivotTables for budget vs actual The FinanceGuard pivot workspace 8 min
  7. 121 KPI cards in Excel Summary cards for a budget variance dashboard 4 min
  8. 122 Budget vs actual charts in Excel Combo chart with secondary axis for variance 5 min
  9. 123 Slicers and PivotCharts in Excel Interactive FinanceGuard budget dashboard 4 min
  10. 124 Finish the FinanceGuard dashboard in Excel Final polish and variance insights 10 min

Instructors

Who teaches it.

Virginia Thorn smiling at the camera, dark hair tied back, wearing a navy top.

Instructor, Excel with AI

Virginia Thorn

Emmy Award winner. Teaches Excel with AI at Coding Liquids.

Questions

Asked before starting.

Is there an Excel course for hotel managers?

Yes. The Complete Excel Course with AI Integration from Coding Liquids teaches the Excel behind a hotel's daily and monthly numbers: ratio formulas for occupancy, average daily rate and RevPAR, SUMIFS and PivotTables to set this year against last, the FinanceGuard budget vs actual report by department, combo charts and a one-screen review built like the MarketPulse dashboard. The lessons use sales, marketing and budget data. This page lists 44 lessons, 3 hours 23 minutes in all.

Can I calculate occupancy, ADR and RevPAR in Excel?

Yes, as plain divisions: occupancy is rooms sold over rooms available, average daily rate is room revenue over rooms sold, and RevPAR is room revenue over rooms available. The Complete Excel Course with AI Integration never names these measures, but it teaches what they need: percentages, an absolute reference for a fixed room count and IFERROR for a day with nothing sold. The MarketPulse lessons build ratio KPIs the same way, like CTR and conversion rate.

Can I build a daily business review dashboard?

Yes, by following a project built for another trade. MarketPulse in The Complete Excel Course with AI Integration turns campaign data into a dashboard with PivotTables, KPI cards, PivotCharts, slicers and a timeline on one screen. Swap the campaigns for days and the click-through rates for occupancy and average daily rate, and the same steps give a daily review a manager can filter by date or department.

How do I compare departments against budget?

Department by department. In The Complete Excel Course with AI Integration the FinanceGuard project keeps the budget in one table and the actual figures in another, so rooms, food and beverage or spa can each be set against plan; the gap shows as an amount and a percentage, an overspend turns a different colour, and slicers on the finished dashboard pick one department at a time. The project's own data is a company budget.

Does it teach revenue management or demand forecasting?

No. The Complete Excel Course with AI Integration teaches Excel, not hotel management: revenue management systems, demand forecasting, rate strategy and property management systems are not part of it, and no lesson uses hotel data. It helps with the reports those systems export into: the daily ratios, this year against last, the departmental budget and the review a manager reads each morning.

Excel with AI

See the whole course.

The Complete Excel Course with AI Integration: 16 hours of video in 186 lessons, built around 14 projects, with lifetime access, a Certificate of Completion and a 14-day money-back guarantee.

See the plans Watch free lessons

The same course

For another job, level or topic.

By job

Finance and accounting
HR and office administration
Operations and supply chain
Data and reporting
Sales, marketing and customer service
Business and management
Engineering, construction and manufacturing
Retail, property and hospitality
Healthcare and life sciences
Education and careers
Public sector, non-profit and legal
Technology

By level and topic