Notes S-02/Excel 2: Lookups and tables


ImportantTODO
  • Check/test the vlookup limitation demo (xlookup)
  • PR: Importance of HLOOKUP/approximate matching? - update materials accordingly
Time Topic/activity Materials
0–15 min Review Homeworks
(if necessary: complete special IF functions)		 Session 1 solutions
15–55 min Introduce VLOOKUP and HLOOKUP Slides 29–31
Slide 32: Exercise Bar sales
55–85 min Explain XLOOKUP and contrast it with VLOOKUP Slide 33
Slide 34: Exercise Client lookup; Clients.xlsx
85–90 min Introduce the lookup homework Slides 35–36: Christmas gift and Course grading
Break
0–15 min Introduce and create Excel Tables Slides 37–39;
15–30 min Sorting, filtering, table design, and styles Slides 40–42
30–50 min Demonstrate dynamic tables, charts, and row operations Slides 43–44
50–65 min Add a Total Row and explain SUBTOTAL Slides 45–46
65–75 min Introduce structured references Slides 47–50
75–90 min Begin the Excel Tables exercise Slides 51–52: Exercise Excel tables; Sales_Table.xlsx

Note: PR: Session 2 typically ends at 46. GW: try to cover everything until p.52

Homework review: DAX moving averages

Homework solution ../materials/Excel_in_class_exercises.xlsx – Sheet: Moving Average

Start by briefly explaining the task (again):

  • Discuss the spreadsheet structure before discussing individual formulas.
  • Check the starting periods for the 10-period and 20-period averages.
  • Emphasize that a longer moving average is smoother and reacts more slowly.

Homework review: Installment loan

Homework solution ../materials/Excel_in_class_exercises.xlsx – Sheet: Installment

Explain the payment schema introduced in the previous session.

  • Q: Why should the fixed repayment amount not be copied through all 20 periods?
  • A: The final repayment must not exceed the remaining debt.

Show how changes in Loan amount, Interest rate and Redemption are automatically reflected in the payments. This could be used in a What-if or scenario analysis.

Homework solution ../materials/Excel_in_class_exercises.xlsx – Sheet: Annuity

  • Explain the PMT(interest rate, term, loan amount) function (payment).
  • Show that the annuity and payments per period change when modifying the numbers of periods.

Add charts for the payments of installment loans and annuity loans. Briefly contrast the declining payments of a fixed-principal loan with the constant payments of an annuity loan.

ImportantSlides

If Special IF functions were not covered in the previous session: complete slide 28.

Slides 30–31 — VLOOKUP and HLOOKUP

Start with the practical example:

In-class demo ../materials/Excel_in_class_exercises.xlsx – Sheet: Bar_Sales

  • Scroll down (hide the price list initially) to start with a simple approach: filling in raw data
  • Highlight that this is not how a restaurant works. It does not make sense that every sale can have a different price for the same product (the unit price should not be in the sales table).
  • Common problem: some data (unit prices) should not be entered in the sales table, but taken from the menu.
  • Scroll up to the price list and explain.
  • Instead of copying data from the price list, we would like to use a formula: VLOOKUP

Whiteboard:

=VLOOKUP(lookup_value, table_array, column_index_num, range_lookup)
  • Explain parameters (range_lookup: FALSE for exact match; for TRUE/approximate matches, the first column must be sorted in ascending order)
  • Write VLOOKUP formula
  • Ask about the need for absolute and relative references
  • Calculate total price
  • Copy formulas down

Continue on the slides (the general VLOOKUP formula).

Explain the lookup process step by step:

  1. Search for the lookup value in the first column of the selected range.
  2. Identify the corresponding row.
  3. Return the value from the specified column of that row.

Key points:

  • The lookup column must be the leftmost column in the selected range.
  • The return column is identified by a number rather than by its name.
  • Use FALSE for an exact match.
  • Approximate matching with TRUE should only be used deliberately and normally requires sorted lookup data.
  • Lock the lookup range before copying a formula down.

HLOOKUP follows the same principle but searches in the first row instead of the first column. Mention it briefly; focus the practical work on vertical lookups.

Slide 33 — XLOOKUP

ImportantLimitation of VLOOKUP

TODO: table is probably the Bar sales. - switch the price list (add an ID as the first column for plausibility)

Funny illustration: With VLOOKUP, Microsoft built a formula that can only look to the right. This is why they needed to build the XLOOKUP 😊

“VLOOKUP can only return values from columns to the right of the lookup column”

Rearrange the columns in the lookup table (TODO: CLARIFY WHICH ONE) and show that the formula may fail or return the wrong column. This motivates the introduction of XLOOKUP.

Demonstration:

  • First solve a lookup with VLOOKUP.
  • Move the return column to the left of the lookup column.
  • Show why VLOOKUP no longer works.
  • Replace it with XLOOKUP.

Example:

=XLOOKUP(D11,$B$3:$B$7,$C$3:$C$7)

General form:

=XLOOKUP(lookup_value, lookup_array, return_array)

Contrast it with VLOOKUP:

VLOOKUP XLOOKUP
Searches only in the first column of the selected range Lookup and return arrays are selected separately
Cannot directly return a value to the left Can return values from either side
Uses a numerical column index Uses an explicit return range
Approximate match is the default Exact match is the default
May break when columns are inserted or reordered More robust to structural changes

Slide 34 — Client lookup

In-class exercise ../materials/Clients.xlsx

We want to change the client number (type in different values) and then the following fields should update automatically (based on the list above):

  • Name
  • Address
  • City
  • State and ZIP code

Teaching sequence:

  • Identify the client-number column as the lookup array.
  • Create one XLOOKUP formula for each requested field.
  • Show that the name can be returned even though it is located to the left of the client number.
  • Change the client number to test all formulas.

Slide 35 — Christmas gift (Homework)

Homework ../materials/Excel_in_class_exercises.xlsx – Sheet: Gift

Students should retrieve two values based on customer status:

  • Christmas gift
  • Packaging

Formulas are needed for the ChristmasGift and Packaging columns (each retrieving the data from a different table).

Encourage students to build both a VLOOKUPand XLOOKUP solution.

Slide 36 — Course grading (Homework)

Homework ../materials/Excel_in_class_exercises.xlsx – Sheet: Grading

This one is similar to the client lookup. We want to enter different StudentIDs and then the lookup functions should retrieve the different test scores and do the calculations of final grades.

ImportantError in Excel sheet

The calculation of the homework contribution is not yet updated in Excel_Exercises_in_class_session_1_solutions.xlsx (to use the AVERAGE()/15 x 10). A more recent version of Excel is needed for that.

☕ Break — 10 minutes

Slides 38–39 — Introduction to Excel Tables

In-class demo ../materials/introduction.xlsx – Sheet: Bike_sales

Create the table together with the students. Copy and show how the range of cells behaves differently compared to the excel table

Teaching sequence:

  • Start with the ordinary range in the Bike_sales sheet
  • Select the complete data range.
  • Insert an Excel Table.
  • Confirm that the range contains headers.
  • Rename the table from its default name to Bikesales.

Explain the main differences between an ordinary range and an Excel Table:

  • Named object
  • Automatic headers
  • Dynamic expansion
  • Integrated filtering and sorting
  • Structured references
  • Automatically propagated formulas
Important

A table name identifies the table independently of its position in the worksheet.

Slide 40 — Sorting and filtering

Demonstrate:

  • Sorting by bike name.
  • Sorting numerical columns.
  • Filtering the table to selected bike models.
  • Removing the filter and restoring all rows.

Emphasize that filtering hides records but does not delete them. This distinction becomes important for SUBTOTAL.

Slides 41–42 — Table design and styles

With a cell inside the table selected, open Table Design.

Demonstrate:

  • Renaming the table.
  • Header Row.
  • Total Row.
  • Banded Rows and Banded Columns.
  • First Column and Last Column.
  • Filter Button.
  • Selecting a predefined table style.

Avoid spending too much time on visual formatting. The main purpose is to show that the table is a managed Excel object.

Slide 43 — Dynamic data entry and charts

Demonstrate that:

  • A new row entered immediately below the table becomes part of the table.
  • Table formatting is applied automatically.
  • Formulas in calculated columns are copied automatically.
  • A chart based on the table expands when new data are added.

Add the Gravel-Star row and observe whether the chart changes.

Slide 44 — Inserting and deleting rows and columns

Distinguish between:

  • Inserting a worksheet row.
  • Inserting a table row.
  • Deleting worksheet cells.
  • Deleting a complete table row or column.

Students should understand that table operations affect the table structure, whereas worksheet operations may shift unrelated cells.

Slide 45 — Calculating totals

Activate Table Design → Total Row.

Demonstrate how the drop-down menu can be used to calculate:

  • Sum
  • Average
  • Count
  • Maximum
  • Minimum

Show the generated formula:

=SUBTOTAL(109,[Cost])

Key observation:

  • Excel does not use a normal SUM formula.
  • It generates a SUBTOTAL formula that responds to filtering.

Slide 46 — SUBTOTAL function numbers

Note: need to select/click function or insert the Function numbers! But do not memorize them - a mapping table will be provided in the exam.

General form:

=SUBTOTAL(function_num,reference)

The first argument encodes the aggregation function:

Function Includes manually hidden rows Excludes manually hidden rows
AVERAGE 1 101
COUNT 2 102
… … …
ImportantImportant distinction

Filtered-out rows are excluded by SUBTOTAL.

For manually hidden rows:

  • Function numbers 1–11 include them.
  • Function numbers 101–111 exclude them.

Demonstrate the difference by:

  1. Calculating a total.
  2. Filtering the table.
  3. Observing the recalculated total.
  4. Manually hiding a row.
  5. Comparing function numbers 9 and 109.

Slides 47–50 — Structured references

Note: This part is slightly more challenging. It will help you in the Python/Pandas sessions.

Principles:

  • Accessing data through variables
  • Selecting specific columns

In Python/Pandas, this will be the same. There are no cells with row and column numbers. They are replaced by named variables.

Note: structured references work regardless of where the table is located (name references).

Basic structured references

Contrast:

=SUM(E3:E7)

with:

=SUM(Bikesales[Cost])

Explain that the structured reference:

  • Uses table and column names.
  • Is easier to interpret.
  • Expands automatically.
  • Is independent of the table’s location on the worksheet.

Components

A structured reference can contain:

  • Table name
  • Item specifier
  • Column specifier

Item specifiers:

Specifier Meaning
[#All] Entire table
[#Data] Data rows
[#Headers] Header row
[#Totals] Total row

Develop the formulas to illustrate name-based access to tables/cells:

Examples:

Bikesales[Cost]
Bikesales[[#Totals],[Turnover]]
Bikesales[[#Totals],[Turnover]:[Cost]]

Note: in =ROWS(Bikesales[[#All],[Sales]]), the [#All] also counts header and totals as rows.

Current-row references

Working with data tables raises the question: how can we use data within the table to calculate something?

Extend the example with current row formulas:

Inside the table, @ refers to the current row:

=[@Turnover]-[@Cost]
=[@Cost]/[@Sales]
=[@Turnover]*1.19

After entering the formula once, Excel should copy it to the complete calculated column.

Slides 51–52 — Excel Tables exercise

In-class exercise/Homework../materials/Sales_Table.xlsx

This extended exercise can be completed in class, assigned as homework, or continued in Session 3.

Suggested checkpoints:

Table exercise

  1. Open Sales_Table.xlsx and convert the data into an Excel Table.

  2. Name the table Sales.

  3. Sort the table by region.

  4. Filter the table to show only 2020.

  5. Restore all years.

  6. Delete the records for Brazil.

  7. Enable the Total Row and calculate (use drop-down at the end of the table):

    • Quantity: sum
    • Cost: sum
    • Turnover: average
    • Year: maximum

  8. Outside the table: Count the number of records:

    =ROWS(Sales[Product])

  9. Add the column Profit:

    =[@Turnover]-[@Cost]

  10. Add the column TOpU for turnover per unit:

    =[@Turnover]*1000/[@Quantity]

  11. Add the column QShare and format it as a percentage:

    =[@Quantity]/SUM([Quantity])

  12. In the Total Row, calculate (use drop-down at the end of the table):

    • Sum of Profit
    • Average of TOpU
    • Sum of QShare

  13. Outside the table: Recalculate total profit:

    =SUM(Sales[Profit])

  14. Outside the table: Calculate total turnover in two ways:

    =SUM(Sales[Turnover])
    =Sales[[#Totals];[Profit]]+Sales[[#Totals];[Cost]]

  15. Outside the table: Add all values from Year to QShare in the Total Row:

    =SUM(Sales[[#Totals];[Year]:[QShare]])

  16. Outside the table: Extract the unique regions in cell L8:

    =UNIQUE(Sales[Region])

  17. Outside the table: Calculate turnover by region in cell M8 and fill the formula down:

    =SUMIF(Sales[Region];L8;Sales[Turnover])

  18. Outside the table: Create a chart showing total turnover by region.

Solution:

Summary and announcements

Homework:

  • Christmas gift
  • Course grading
  • Complete the Sales Table exercise if it was not finished in class.

Next session:

  • Finish structured references and the table exercise, if necessary.
  • Introduce PivotTables.
TipNotes for improvement

Take notes on timing, common errors, unclear instructions, and suitable stopping points on a separate paper and add them to feedback.qmd after the session.