Start learning
My learning

The Complete Excel Course with AI Integration

Excel course for travel consultants

Travel consultants and agents use Excel to cost trips, chase balances and send rooming lists. The Complete Excel Course with AI Integration contains no travel data; this guide selects costing arithmetic, Excel Tables for rooming lists, SUMIFS and date formulas for payments, text functions for names, PivotTables for destination sales, rate lookups and TaskFlow for booking checklists.

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:

  • Build an itinerary costing by retrieving supplier rates with XLOOKUP, multiplying nights, rates and rooms, and keeping the mark-up in a single cell.
  • Use SUMIFS to track each booking's deposits and balance, then FILTER and SORT to show which balances fall due soonest.
  • Manage a group rooming list in an Excel Table, format names to the hotel's requirements and select room types from a drop-down.
  • Compare commission received from suppliers with the expected earnings on each booking using XLOOKUP, IFERROR and a column for the difference.
  • Summarise sales across destinations, suppliers and months using PivotTables, with slicers to filter the report.
  • Use date arithmetic and conditional formatting to flag traveller documents expiring within a chosen margin after the return date.

Projects

What you will build.

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

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

    Turn a bookings export into destination, supplier and monthly summaries, filter by consultant with slicers and use Refresh All when further sales arrive.

  • XLOOKUP formula in Excel (PeopleInsight): each employee row looks up its location from another sheet.

    Project 06

    PeopleInsight HR workbook

    Employee data joined across sheets with one formula.

    Apply XLOOKUP to supplier rates for a hotel and room type, use a two-way lookup for seasonal rates and handle rates still to be loaded with IFERROR.

  • Task tracker in Excel (TaskFlow): start and due dates, status, priority and a progress bar for each task, with working days left, days past due and unfinished high-priority tasks highlighted.

    Project 07

    TaskFlow

    One sheet that shows what is done, what is late and what is urgent.

    Organise a booking's outstanding work, from issuing tickets and sending documents to chasing balances, with coloured due dates and a calculation of working days remaining.

  • Project 02

    Dunder Michael Paper Company employee database

    Apply the database skills to a group rooming list: organise travellers in a single Table by room, split and tidy their names, remove duplicates and print a clean hotel copy.

Lessons

The lessons that matter, in course order.

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

Where to start

Lesson 8 Basic maths in Excel

If you can enter data in a sheet but have yet to use formulas, start with Lesson 8: multiplying nights by rates is the beginning of a trip costing. Start at Lesson 1 if Excel is new to you. Already comfortable with SUM and percentages? Begin the rooming list at Lesson 19, or start rate lookups at Lesson 67. The whole of Managing data in Excel sits on this path and is free by email with its practice files: ask for it on the course page.

Get the free section by email

60 of 186 lessons · 5 h 8 min

01 Microsoft Excel fundamentals: building a foundation from scratch 4 of 13 lessons · 15 min Adapt the personal budget formulas to itinerary costing: multiply nights by rate and rooms, total the trip with SUM and calculate percentages. An absolute reference keeps a mark-up or service fee linked to a single cell.
  1. 8 Start here Basic maths in Excel Add, subtract, multiply and divide with formulas 3 min
  2. 9 SUM and AutoSum in Excel Total income, expenses and savings with MAX 6 min
  3. 10 Relative vs absolute references in Excel Dollar signs and mixed references 3 min
  4. 11 Calculating percentages in Excel Each expense as a percentage of income 2 min
03 Managing data in Excel: sort, filter and tables (Dunder Michael Paper employee database) 9 lessons · 1 h 1 min Use the employee database lessons to prepare a group rooming list in an Excel Table. Sort on room and surname, filter for hotel or room type, clean text with TRIM, remove duplicates and separate names with Flash Fill or Text to Columns. Freeze the header and print a tidy copy for the hotel.
  1. 19 Excel tables Create and format a table, structured references and total row 8 min
  2. 20 Sorting data in Excel Single and multi-level sort on an employee database 6 min
  3. 21 Filtering data in Excel AutoFilter, text and number filters on employee data 10 min
  4. 22 Cleaning data in Excel TRIM, Paste Special values and Find and Replace 13 min
  5. 23 Remove duplicates in Excel Find and delete duplicate rows in an employee list 6 min
  6. 24 Flash Fill in Excel Split first and last names automatically 3 min
  7. 25 Text to Columns in Excel Split data by delimiter into separate columns 6 min
  8. 26 Freeze panes in Excel Lock header rows and columns while scrolling 3 min
  9. 27 Printing in Excel Print area, page orientation, scaling and page layout 6 min
04 Essential formulas and functions (IF, SUMIF and more) 13 lessons · 1 h 17 min Apply booking criteria to calculations: total destination and monthly sales with SUMIF and SUMIFS, count bookings per consultant with COUNTIFS and flag a balance due with IF. AND and OR handle paired conditions, while date arithmetic works back from departure to the balance due date.
  1. 28 Logical functions in Excel TRUE and FALSE comparisons for beginners 5 min
  2. 29 The IF function in Excel Pass or fail results with a single condition 3 min
  3. 30 IF function practice in Excel Categorise sales data with IF formulas 6 min
  4. 31 Nested IF and IFS in Excel Customer value tiers with nested IF 6 min
  5. 32 Nested IF and IFS in Excel Sales performance labels with the IFS function 5 min
  6. 33 AND and OR with IF in Excel Flag high-value deals with multiple conditions 8 min
  7. 34 SUM, COUNT and COUNTA in Excel SUM with FILTER and counting numbers with COUNT 7 min
  8. 35 SUM, COUNT and COUNTA in Excel COUNTA, unique counts and completeness checks 4 min
  9. 36 SUMIF and COUNTIF in Excel Conditional sum and count with one criterion 8 min
  10. 37 SUMIFS and COUNTIFS in Excel Sum and count with multiple criteria 7 min
  11. 38 The Excel function library Insert Function dialog and Formula AutoComplete 5 min
  12. 39 Date and time functions in Excel TODAY, NOW, MONTH and date arithmetic 6 min
  13. 40 Excel Ninja Challenge COUNTIF, SUMIF, IF, COUNTIFS, SUMIFS and IFS practice 8 min
05 Text functions and data validation (enhancing data quality) 5 of 7 lessons · 25 min Prepare names in the formats hotels and airlines request. Join surname and first name with CONCAT, adjust capitals with UPPER and PROPER, remove stray characters with SUBSTITUTE and split text using Text to Columns. Add drop-down choices for room type and board basis.
  1. 41 CONCAT, the & operator and TEXTJOIN in Excel Combine text from multiple cells 6 min
  2. 42 TRIM, UPPER, LOWER and PROPER in Excel Clean messy text and fix capitalisation 6 min
  3. 43 FIND, SEARCH and SUBSTITUTE in Excel Locate and replace text with formulas 5 min
  4. 44 Text to Columns wizard in Excel Split a column by a comma delimiter 4 min
  5. 45 Data validation in Excel Create drop-down lists for consistent data entry 4 min
07 PivotTables and PivotCharts: data analysis made easy 10 lessons · 33 min The sales-order lessons provide a model for analysing a bookings export. Build PivotTables showing sales by destination, supplier and month, group dates, filter by consultant with slicers and display PivotCharts. Refresh the reports after adding bookings.
  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
08 Advanced Excel functions: lookups, text and arrays 10 of 12 lessons · 49 min Retrieve rates for hotels, room types or seasons with VLOOKUP, XLOOKUP including a two-way lookup, INDEX and MATCH, and IFERROR. Match supplier statements to booking references, calculate days until departure with TODAY and working days until a deadline with NETWORKDAYS, then use FILTER and SORT to bring the soonest-due balances to the top.
  1. 68 VLOOKUP in Excel Find employee data across sheets with exact match 4 min
  2. 69 XLOOKUP in Excel Exact match, if not found and two-way lookup 6 min
  3. 70 IFERROR in Excel Handle lookup errors in VLOOKUP and XLOOKUP formulas 3 min
  4. 71 INDEX and MATCH in Excel A flexible alternative to VLOOKUP 6 min
  5. 72 CONCAT and TEXTJOIN in Excel Combine employee names and text with a delimiter 3 min
  6. 73 LEFT, RIGHT and MID in Excel Extract parts of a text code like DAL-SAL-0001 7 min
  7. 74 TODAY, EOMONTH and NETWORKDAYS in Excel Date calculations for HR data 5 min
  8. 75 UNIQUE function in Excel Extract a distinct list with dynamic arrays 6 min
  9. 76 FILTER function in Excel Dynamic array filtering with AND and OR criteria 5 min
  10. 77 SORT and SORTBY in Excel Sort data dynamically with formulas 4 min
09 Project: task automation with data validation and forms 9 lessons · 49 min Adapt TaskFlow's task columns to the steps of a booking: tickets, documents and payments. Choose status and priority from drop-downs, highlight overdue work, show a timeline and working days remaining, and protect the sheet.
  1. 79 TaskFlow project tracker in Excel Overview of the task tracker dashboard 4 min
  2. 80 Designing a task tracker in Excel TaskFlow layout, columns and headers 5 min
  3. 81 Data validation in Excel Status and priority drop-down lists in TaskFlow 8 min
  4. 82 Conditional formatting in Excel Highlight overdue and completed tasks 8 min
  5. 83 Gantt chart in Excel Build a project timeline with conditional formatting 6 min
  6. 84 Data bars and icon sets in Excel Visual priority and progress in TaskFlow 6 min
  7. 85 NETWORKDAYS and TODAY in Excel Days remaining and task deadlines in TaskFlow 5 min
  8. 86 Protect a sheet and lock cells in Excel Secure the TaskFlow tracker workbook 4 min
  9. 87 Testing an Excel task tracker Stress test TaskFlow with sample data 3 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.

Which Excel course can travel consultants use for their work?

The Complete Excel Course with AI Integration from Coding Liquids teaches skills for the spreadsheets travel consultants maintain. These include XLOOKUP for supplier rates in itinerary costings, SUMIFS and dates for deposits and balances, Excel Tables for group rooming lists, commission checks against supplier statements and PivotTables for destination and monthly sales. Its examples use employee, sales and HR data; the steps also apply to bookings. The selection on this page comprises 60 lessons, taking 5 hours 8 minutes in total.

Can a travel consultant manage a group rooming list in Excel?

Yes. This path includes the whole of Managing data in Excel, available free by email, where The Complete Excel Course with AI Integration teaches Excel Tables, sorting on several levels and filters: enough to sort a rooming list by room then surname and filter it by hotel. For a list assembled from several emails, use TRIM and Remove Duplicates, split names with Flash Fill, freeze the headers and prepare a tidy printout. Later lessons on the path cover text functions and a room-type drop-down.

How can travel consultants keep track of deposits and balances due?

Start with a bookings table and apply the formulas taught in The Complete Excel Course with AI Integration. Calculate the balance due date a fixed number of days before departure using date arithmetic, total each client's payments with SUMIFS and use IF to flag a balance when its due date approaches. FILTER and SORT put the earliest balances first; the TaskFlow lessons show conditional formatting that colours dates once they have passed.

Can a travel consultant use Excel to reconcile supplier commission?

Yes. The Complete Excel Course with AI Integration teaches the component skills, although it has no commission reconciliation lesson. Calculate expected earnings by multiplying each booking by its commission rate, then use XLOOKUP with the booking reference to retrieve the amount on the supplier statement. IFERROR can flag a booking absent from that statement. Show discrepancies in a difference column with conditional formatting, and total the shortfall for each supplier with SUMIFS.

Will this course teach travel consultants booking systems, fares or visa rules?

The Complete Excel Course with AI Integration covers Excel skills, with no teaching on booking systems such as a GDS, fare construction, visa and entry rules or supplier agreements. None of its data sets contains bookings. Its lessons apply to the accompanying spreadsheets: itinerary costings, rooming lists, payment schedules, commission checks and sales reports.

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