Essential Excel Functions & Features You Need to Know

Whether you’re building reports, cleaning data or creating dashboards, mastering these built-in Excel tools will save you hours. Read on for syntax, examples and best-practice tips.

1. Text Manipulation

• =UPPER(text) Converts all letters to uppercase.
  =UPPER(A2) → “HELLO”
• =LOWER(text) Converts all letters to lowercase.
  =LOWER(B2) → “world”
• =LEFT(text, num_chars) Returns the first num_chars characters.
  =LEFT(D2,2) → “Ex”
• =RIGHT(text, num_chars) Returns the last num_chars characters.
  =RIGHT(E2,3) → “ort”
• =MID(text, start_num, num_chars) Extracts num_chars from the middle of a string.
  =MID(C2,3,4) → “alyz”

2. Formatting & Rounding

• =TEXT(value, format_text) Apply custom date/number formats.
  =TEXT(F2, "dd-mmm-yyyy") → “01-Jan-2025”
• =ROUND(number, num_digits) Round to a specified number of decimals.
  =ROUND(G2,1) → 3.1

3. Aggregation & Basic Formulas

• =SUBTOTAL(function_num, range) Aggregate (sum, avg, count…) while ignoring hidden rows.
  =SUBTOTAL(9, H2:H50) → Sum of H2:H50
• Basic arithmetic & built-ins
  =A1+B1, =SUM(A1:A5), =AVERAGE(B1:B5)

4. Best Practices & Tips

1. Standardize text first.
2. Keep your layout clean. Freeze header rows (View → Freeze Panes) so you never lose context.
3. Automate subtotals. Use SUBTOTAL combined with Group/Ungroup outlines to build expandable summaries.
4. Format on the fly. Use TEXT when you need human-readable dates in dashboards but keep raw dates on hidden columns for calculations.
5. Use Go To Special. (Home → Find & Select → Go To Special…) to rapidly select blanks, formulas or constants for bulk edits.
6. Insert : Home → Insert or Right-click → Insert…Adds cells, rows, columns, or sheets (e.g., Insert Sheet Rows).
7. Freeze Panes : View → Freeze Panes → choose Locks rows/columns while scrolling (e.g., Freeze top row).
8. Go To Special : Home → Find & Select → Go To Special…Selects specific cell types (e.g., blanks, formulas).
9. Find & Replace : Ctrl+H or Home → Find & Select → Replace…Searches and replaces text or values (e.g., replace ‘10%’ with ‘12%’).
10. Group / Ungroup : Data → Group / Ungroup Creates or removes outlines to collapse/expand rows/columns.