Appearance
Understanding Excel Features — A Quick-Reference Guide to What Excel Can Do
Applies to: Microsoft Excel (Microsoft 365, Excel for Windows, Excel for Web)
Article Type: Informational
Last Updated: 2026-05-18
Summary
Excel is one of the most powerful tools in Microsoft 365, but most users only scratch the surface — typing data into cells and doing basic math. This article is a quick-reference guide to Excel's most useful features, organized by skill level, so you can find the right tool for the task. Whether you need to look up data, build a dashboard, or automate repetitive formatting, this guide points you to the right feature.
Prerequisites
- Microsoft Excel (Microsoft 365 desktop app or Excel for the web).
- No advanced skills required — this article explains what each feature does and when to use it.
Instructions
1. Essential Features (Everyone Should Know These)
| Feature | What It Does | When to Use It | How to Access It |
|---|---|---|---|
| AutoSum | Instantly calculate SUM, AVERAGE, COUNT, MIN, or MAX for a range | Quickly total a column or row | Select a cell below/right of data → Home → AutoSum (or Alt + =) |
| Sort and Filter | Arrange data alphabetically, numerically, or by colour; filter to show only matching rows | Finding specific records in a large dataset | Select your data → Data → Sort or Filter (or Ctrl + Shift + L) |
| Find and Replace | Search for text/values and optionally replace them across the sheet or workbook | Correcting typos, updating codes, or standardizing data | Ctrl + H |
| Freeze Panes | Lock header rows or columns so they stay visible while scrolling | Working with large spreadsheets where headers scroll out of view | View → Freeze Panes |
| Flash Fill | Auto-detect a pattern from examples you type and fill the rest of the column | Extracting first names from full names, reformatting phone numbers, splitting data | Type the pattern in the first 1–2 cells → Ctrl + E |
| Tables | Convert a range into a structured Table with auto-expanding formulas, filters, and formatting | Any dataset you plan to sort, filter, or reference in formulas | Select data → Ctrl + T |
| Conditional Formatting | Automatically highlight cells based on rules (e.g., values above a threshold, duplicates, data bars) | Spotting trends, outliers, or duplicates at a glance | Home → Conditional Formatting |
| Dropdown Lists | Create a cell with a predefined list of choices | Standardizing data entry (e.g., Status: Open/In Progress/Closed) | Data → Data Validation → List |
| Sheet Protection | Lock cells to prevent accidental edits while allowing input in specific cells | Sharing a template where some cells are editable and others are locked | Review → Protect Sheet |
2. Intermediate Features (Level Up Your Productivity)
| Feature | What It Does | When to Use It | How to Access It |
|---|---|---|---|
| XLOOKUP | Look up a value in one column and return a corresponding value from another column | Pulling data from reference tables (e.g., employee ID → department) | =XLOOKUP(lookup_value, lookup_array, return_array) |
| PivotTables | Summarize large datasets into interactive reports with drag-and-drop fields | Analyzing sales by region, counting tickets by category, summarizing any large dataset | Select data → Insert → PivotTable |
| Charts | Create visual representations of data (bar, line, pie, scatter, etc.) | Presenting trends, comparisons, or distributions visually | Select data → Insert → Chart |
| Named Ranges | Assign a name to a cell or range so formulas are easier to read | Making complex formulas readable (e.g., =TaxRate*Income instead of =B1*C5) | Formulas → Name Manager → New |
| Data Validation | Restrict what can be entered in a cell (numbers only, dates within a range, list of choices) | Preventing invalid data entry in shared workbooks | Data → Data Validation |
| TEXT, DATE, and IF functions | Transform text, manipulate dates, and add logic to calculations | Reformatting data, calculating deadlines, creating conditional outputs | Type the formula in any cell |
| Analyze Data (Ideas) | AI-powered analysis that suggests insights, trends, and charts from your data | Quick exploration of unfamiliar data — "show me something interesting" | Home → Analyze Data |
3. Advanced Features (Power User Territory)
| Feature | What It Does | When to Use It | How to Access It |
|---|---|---|---|
| Power Query | Import, clean, and transform data from external sources (CSV, databases, web) | Automating data cleanup that you do repeatedly | Data → Get Data |
| Dynamic Arrays (UNIQUE, SORT, FILTER, SEQUENCE) | Formulas that return multiple results that spill into adjacent cells | Extracting unique values, creating filtered views, generating sequences without helper columns | Type the formula — results spill automatically |
| LAMBDA and LET | Create custom functions (LAMBDA) and define variables within a formula (LET) | Building reusable custom calculations, simplifying complex nested formulas | =LAMBDA(x, x*2)(5) or =LET(total, SUM(A:A), total*1.1) |
| Macros (VBA) | Record or code automated sequences of actions | Automating repetitive multi-step tasks (e.g., formatting, copying, exporting) | View → Macros or Alt + F11 (VBA Editor) |
| Copilot in Excel | AI assistant that analyzes data, creates formulas, and builds charts from natural language prompts | Quick analysis without writing formulas — "What are the top 5 products by revenue?" | Home → Copilot (requires Copilot license) |
4. Formula Quick Reference — Most Used Functions
| Function | Purpose | Example |
|---|---|---|
=SUM(range) | Add up values | =SUM(B2:B100) |
=AVERAGE(range) | Calculate average | =AVERAGE(C2:C50) |
=COUNT(range) | Count cells with numbers | =COUNT(A:A) |
=COUNTA(range) | Count non-empty cells | =COUNTA(A:A) |
=IF(test, true, false) | Conditional logic | =IF(B2>100, "Over Budget", "OK") |
=XLOOKUP(value, lookup, return) | Look up a value | =XLOOKUP(A2, IDs, Names) |
=VLOOKUP(value, range, col, FALSE) | Legacy lookup (use XLOOKUP when possible) | =VLOOKUP(A2, Sheet2!A:C, 3, FALSE) |
=CONCATENATE() or =TEXTJOIN() | Combine text from multiple cells | =TEXTJOIN(" ", TRUE, A2, B2) |
=TODAY() | Current date | =TODAY() |
=TRIM(text) | Remove extra spaces | =TRIM(A2) |
=IFERROR(formula, fallback) | Catch errors gracefully | =IFERROR(XLOOKUP(...), "Not Found") |
=SUMIF(range, criteria, sum_range) | Sum based on a condition | =SUMIF(A:A, "North", B:B) |
=COUNTIF(range, criteria) | Count based on a condition | =COUNTIF(C:C, ">100") |
5. Keyboard Shortcuts Every Excel User Should Know
| Shortcut | Action |
|---|---|
| Ctrl + T | Convert range to Table |
| Ctrl + Shift + L | Toggle filters on/off |
| Ctrl + E | Flash Fill |
| Ctrl + ; | Insert today's date |
| Ctrl + Shift + ; | Insert current time |
| Ctrl + ` (backtick) | Toggle formula view (show all formulas in cells) |
| Alt + = | AutoSum |
| Ctrl + Shift + $ | Apply currency format |
| Ctrl + Shift + % | Apply percentage format |
| F4 | Toggle absolute/relative reference ($) in a formula |
| Ctrl + D | Fill down (copy cell above) |
| Ctrl + R | Fill right (copy cell to the left) |
| Ctrl + Space | Select entire column |
| Shift + Space | Select entire row |
6. Decision Guide — "Which Excel Feature Should I Use?"
| I Need To... | Use This Feature |
|---|---|
| Total a column of numbers | AutoSum or =SUM() |
| Look up a value from another sheet | XLOOKUP |
| Summarize a large dataset into a report | PivotTable |
| Highlight values that meet a condition | Conditional Formatting |
| Prevent users from entering invalid data | Data Validation / Dropdown Lists |
| Reformat or split text in a column | Flash Fill |
| Lock cells so they can't be edited | Sheet Protection |
| Keep headers visible while scrolling | Freeze Panes |
| Automatically apply formatting to new rows | Convert to Table (Ctrl + T) |
| Import and clean data from a CSV or database | Power Query |
| Get AI-generated insights from my data | Analyze Data or Copilot |
| Create a chart or visualization | Insert → Chart |
| Automate a repetitive task | Macros (VBA) or Power Automate |
Troubleshooting
INFO
Most Excel issues stem from incorrect cell references, locked cells, or data type mismatches. The troubleshooting articles linked below cover formula errors, display issues, and performance problems in detail.
| Symptom / Error | Potential Cause | Solution |
|---|---|---|
| #REF! error | A formula references a cell or range that was deleted | Undo the deletion (Ctrl + Z) or update the formula to reference the correct range. |
| #VALUE! error | Formula expects a number but received text | Check that the cells in your formula contain the expected data type. Use =VALUE() to convert text to numbers. |
| #N/A error in XLOOKUP or VLOOKUP | Lookup value not found | Verify the lookup value exists in the lookup range. Check for extra spaces with =TRIM(). |
| ##### displayed in a cell | Column is too narrow to display the value | Double-click the column border to auto-fit, or manually widen the column. |
| Formulas showing as text | Cell formatted as Text | Change the cell format to General (Home → Number Format dropdown), then press Enter in the formula bar. |
| Excel is slow or freezing | Large file, volatile functions, or too many conditional formatting rules | See How to Fix Excel Crashing or Freezing for detailed steps. |
| Cannot edit cells — "Protected Sheet" | Sheet protection is enabled | Go to Review → Unprotect Sheet and enter the password (or ask the sheet owner). |