Spreadsheet competence remains one of the most widely requested technical capabilities in modern administrative, analytical, and operational roles. Mastering core functions transforms raw data into actionable business insight.
1. Essential Arithmetic and Aggregation Functions
These foundational formulas form the bedrock of daily tracking sheets and financial summaries:
=SUM(A2:A50): Calculates the total of numeric entries within the specified range.=AVERAGE(B2:B50): Returns the mathematical mean of selected values.=COUNT(C2:C50): Counts the number of cells containing numerical data.=COUNTA(D2:D50): Counts non-empty cells, including text strings.
2. Conditional Logic: IF and COUNTIF
Conditional functions allow spreadsheets to respond dynamically to criteria:
=IF(E2>=1000, "Qualified", "Under Threshold")
Evaluates whether cell E2 meets the threshold and returns the appropriate string label.
=COUNTIF(StatusRange, "Completed")
Scans project status columns to count how many deliverables are currently marked as finished.
3. Data Lookup with XLOOKUP
Modern Excel replaced cumbersome lookup workflows with XLOOKUP:
=XLOOKUP(LookupValue, LookupArray, ReturnArray, [IfNotFound])
Unlike traditional VLOOKUP, XLOOKUP does not break when columns are inserted or reordered, and it handles missing values gracefully without requiring separate IFERROR wrappers.
4. Time-Saving Keyboard Shortcuts
| Shortcut (Windows) | Shortcut (Mac) | Action |
|---|---|---|
| Ctrl + Shift + L | Cmd + Shift + F | Toggle AutoFilter on active table |
| Ctrl + Space | Ctrl + Space | Select entire active column |
| Shift + Space | Shift + Space | Select entire active row |
| F4 | Cmd + T | Toggle absolute cell references ($A$1) |
