🔧

Excel Ampersand Uses

Jun 9, 2025

Overview

This lecture explores advanced uses of the ampersand (&) operator in Excel, showing how it can create flexible formulas, dynamic reports, and custom control panels beyond simple text joining.

Combining Text in Excel

  • The ampersand (&) joins text or cell values, replacing the need for the CONCAT function.
  • To add a separator (like a space), include " " between ampersands (e.g., =A1 & " " & B1).
  • The ampersand provides a faster, simpler alternative to TEXTJOIN for basic concatenation.

Creating Dynamic Titles and Headers

  • Use ampersand to build dynamic titles that update when referenced cell values change (e.g., ="Analysis of " & C4 & " " & D4).
  • Dynamic headers and chart titles can be set by linking titles to formula cells.
  • Updating company names or years automatically refreshes all connected headers and charts.

Making Flexible Lookup Formulas

  • Combine multiple criteria in XLOOKUP by concatenating values (e.g., =XLOOKUP(A2&B2, Range1&Range2, ResultRange)).
  • Allows lookups to dynamically adjust based on several input criteria, overcoming standard XLOOKUP limitations.

Handling Approximate and Wildcard Matches

  • Use ampersand and wildcards (e.g., A2 & "*") for approximate lookups in XLOOKUP or MATCH.
  • To match values with extra spaces or prefixes, add "*" before and/or after the lookup value ("*" & A2 & "*").
  • Set the match mode to 2 in XLOOKUP for wildcard support.

Building a Custom Control Panel

  • Construct sheet references dynamically using ampersands and the INDIRECT function (e.g., INDIRECT("'" & A1 & "'!C3:C18")).
  • Wrap with SUM to aggregate values across dynamically chosen worksheets.
  • Works with newly added sheets if naming conventions are consistent.

Key Terms & Definitions

  • Ampersand (&) — Operator used to join (concatenate) values or text in Excel formulas.
  • Dynamic Title — A cell or chart header that updates automatically based on referenced data.
  • Indirect Function — Returns a cell or range reference specified by a text string.
  • Wildcard (*) — Symbol used in lookups to match any sequence of characters.

Action Items / Next Steps

  • Practice building dynamic titles and lookups using the ampersand in Excel.
  • Review usage of INDIRECT for creating flexible dashboards.
  • Explore how wildcard matching can improve data retrieval in lookup functions.
  • Optional: Download the practice Excel file linked in the video description.

Full transcript