Home / Courses / Pro Excel / Resources

Pro Excel, Resources

To put your course knowledge into practice: a one-page cheat sheet, a glossary of terms and ready-made formula templates. Save, print, copy.

One-page cheat sheet

osonstudio.uz

Pro Excel, formulas and shortcuts

1Core calculation functions

  • SUM, total: =SUM(B2:B10)
  • AVERAGE, mean
  • MIN / MAX, smallest / largest
  • COUNT, count of numbers
  • COUNTA, non-empty cells

2Logical functions

  • IF, condition: =IF(A1>=60;"Pass";"Fail")
  • AND / OR, multiple conditions
  • IFS, multiple conditions
  • IFERROR, hide an error

3Conditional calculation

  • COUNTIF, count by condition
  • SUMIF, sum by condition
  • AVERAGEIF, average by condition
  • COUNTIFS, count by multiple conditions

4Lookup functions

  • VLOOKUP, find a value in a table
  • XLOOKUP, modern lookup
  • INDEX + MATCH, flexible lookup
  • For an exact match, end with 0 / FALSE

5Text functions

  • LEFT / RIGHT / MID, extract a part
  • LEN, number of characters
  • CONCAT / &, join
  • TRIM, remove extra spaces
  • UPPER / LOWER / PROPER, case

6Date functions

  • TODAY, today's date: =TODAY()
  • YEAR / MONTH / DAY, part of a date
  • Age: =YEAR(TODAY())-YEAR(A1)

7Reference types

  • A1, relative (changes when copied)
  • $A$1, absolute (stays fixed)
  • $A1 / A$1, mixed
  • F4, switch reference type

8Useful shortcuts

  • Alt + =, AutoSum
  • Ctrl + Shift + L, filter
  • Ctrl + ;, today's date
  • Ctrl + Z, undo; Ctrl + S, save
  • Ctrl + Shift + ↓, select column
© Oson Studio, Pro Excel course · osonstudio.uz/excel-kurs

Copy the ready-made formulas and replace the [bracketed] placeholders with your own cells. A semicolon (;) is used as the argument separator, for a local version of Excel.