The Colon Trick 99% of Excel Users Don’t Know About

Kenji Explains • 1 day ago

Original source

What This Video Is About

This video discusses the use of colon tricks in Excel to improve productivity and calculation accuracy. It covers three main tricks: calculating running totals, adding up multiple sheets with a single formula, and using formulas to dynamically determine range ends.

Major Ideas Discussed

  • The colon trick can be used with the SUM function to calculate running totals by locking the first cell reference and selecting the range from the first cell to the current cell.
  • The colon trick can be used to add up a whole stack of sheets with a single formula by specifying the range of sheets.
  • The XLOOKUP function can be used with the colon trick to let a formula decide where a range ends, such as finding the previous order amount for a client.
  • Additional examples of using the colon trick include finding the number of orders for a client, the number of unique customers, and the previous order date.
  • Formulas can be combined using the colon trick to achieve more complex calculations, such as finding revenue between a date range.

What to Watch Next

Viewers can explore more advanced Excel functions and techniques, such as using INDEX-MATCH, PivotTables, and Power Query to further improve their data analysis skills.