Microsoft Excel is arguably the most consequential piece of software ever created for business. Financial models, data analysis, project tracking, operational reporting, inventory management — Excel handles all of it, and in many organisations it handles it better than specialised tools that cost thousands of dollars per year. Knowing how to use Excel properly is one of the most universally valuable computer skills in professional environments. You’ll find the complete rundown in our Complete Guide to Software and Apps.
This guide covers Excel from foundational mechanics through intermediate features — the ones that separate capable users from the people who build spreadsheets in minutes that others can’t figure out in hours.
Workbooks, worksheets, and the interface
An Excel file is called a workbook. A workbook contains one or more worksheets — the individual grid-based pages visible as tabs at the bottom of the screen. Each worksheet has up to 1,048,576 rows and 16,384 columns. Cells are addressed by column letter and row number: A1 is the first cell, B3 is the second column and third row. This addressing system is the foundation of everything — every formula, chart, and data reference uses cell addresses.
The ribbon across the top organises commands into tabs: Home (formatting, basic editing), Insert (charts, PivotTables), Page Layout (print settings), Formulas (formula library, named ranges), Data (sorting, filtering, data connections), Review, and View. Know which tab holds which command rather than hunting through all of them.
Two interface features most users miss:
- Name Box (the field showing the current cell address, left of the formula bar): click it and type any cell address to jump there instantly. Type a name after pressing F2 to create a named range — formulas can then reference that range by name rather than address.
- Quick Access Toolbar (above the ribbon): customise it to hold your most-used commands (Save, Undo, Redo, AutoSum, Format as Table) — accessible with a single click regardless of which ribbon tab is active.
Formulas, functions, and calculations
Formulas begin with =. Every formula calculates a result based on values, cell references, operators, or function calls. The essential function toolkit:
| Function | Syntax | Use |
| SUM | =SUM(A1:A10) | Add a range of numbers |
| AVERAGE | =AVERAGE(B2:B20) | Calculate the mean |
| COUNT / COUNTA | =COUNT(C1:C50) | Count numbers / count non-empty cells |
| IF | =IF(D2>100,”Over”,”OK”) | Conditional logic — returns one value or another based on a test |
| VLOOKUP | =VLOOKUP(E2,A:B,2,FALSE) | Look up a value and return data from a related column |
| XLOOKUP | =XLOOKUP(E2,A:A,B:B) | Modern VLOOKUP replacement — more flexible, no column number required |
| SUMIF / COUNTIF | =SUMIF(A:A,”West”,B:B) | Conditional aggregation — sum or count only rows matching a condition |
| INDEX + MATCH | =INDEX(B:B,MATCH(E2,A:A,0)) | Flexible two-way lookups; more robust than VLOOKUP |
| IFERROR | =IFERROR(formula,”N/A”) | Handle formula errors gracefully instead of showing #N/A or #REF! |
| XLOOKUP / TEXT | =TEXT(A1,”DD/MM/YYYY”) | Format values as text for display purposes |
Understanding reference types — press F4 while the cursor is on a cell reference in the formula bar to cycle through modes:
- Relative (A1): changes when the formula is copied to another cell — the reference adjusts relative to the new position
- Absolute ($A$1): stays fixed regardless of where the formula is copied — the reference never changes
- Mixed ($A1 or A$1): locks only the column or only the row
Getting reference types wrong is the most common cause of formulas that work in one cell but produce wrong results when copied down. A formula like =B2*$C$1 multiplies each row’s value by the fixed rate in C1 — correct because $C$1 stays fixed while B2 adjusts per row.
Step-by-step data analysis
Step 1 — Structure data correctly as a table. Data should be in tabular format: one header row at the top, one record per row, no blank rows or columns within the range, no merged cells. Select any cell in the range → Insert → Table (Ctrl+T) → confirm range and check “My table has headers” → OK. The data becomes a structured Excel table with automatic banded rows, filter dropdowns on every header, and column-name references in formulas.
Step 2 — Sort and filter. Click any header dropdown → sort ascending/descending, or apply a filter. Multiple columns can be sorted simultaneously: Data → Sort → Add Level → specify sort order for each column in sequence. Filters on multiple columns are independent and combined — each column’s filter further narrows the visible rows. Clear all filters: Data → Clear.
Step 3 — Create a PivotTable. Select any cell in the data → Insert → PivotTable → place in a new worksheet → OK. The PivotTable field list appears — drag fields into the four areas:
- Rows: the grouping dimension (Product, Region, Month)
- Columns: secondary grouping for cross-tabulation
- Values: the metric to aggregate (Sales, Count, Average)
- Filters: apply a slicer to narrow the entire table
Dragging “Product” to Rows and “Sales” to Values instantly creates total sales per product. Dragging “Region” to Columns breaks it down by both product and region. PivotTables summarise millions of rows in seconds without any formulas — the single highest-leverage Excel skill for data analysis.
Step 4 — Add a PivotChart. Click anywhere in the PivotTable → Insert → PivotChart → choose chart type → OK. The chart links to the PivotTable — it updates automatically when the data changes, and the PivotTable’s field dropdowns filter the chart simultaneously. This combination of PivotTable + PivotChart creates a dynamic dashboard that any stakeholder can interact with by changing filters.
Power features for advanced users
Conditional Formatting: highlight cells based on their values automatically. Home → Conditional Formatting → choose from data bars, colour scales, icon sets, or custom rules. Common uses: heat maps of performance data, highlighting cells above/below a threshold, flagging overdue dates in red. Conditional formatting updates dynamically as data changes.
Data Validation: restrict what can be entered in a cell. Data → Data Validation → set criteria (number range, date range, list of allowed values). Adding a dropdown list from a validation list is one of the most useful Excel techniques for shared workbooks — it prevents typos and ensures data consistency without requiring training.
XLOOKUP is the modern replacement for VLOOKUP and should be used in any new workbook where Excel 365 is available. It looks left (VLOOKUP can only look right), handles missing values gracefully, and doesn’t require a column number: =XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found]).
Flash Fill (Ctrl+E): recognises a pattern from examples and fills the remaining cells automatically. Type the first name from a “Full Name” column in a “First Name” column → press Ctrl+E → Excel fills the entire column. Dramatically faster than writing text formulas for structured data extraction.
Power Query: Data → Get Data → From File/Database/Web. Power Query imports, transforms, and cleans data from external sources — CSV files, databases, web tables — and refreshes with one click when the source data updates. The step-by-step transformation process is recorded, so the same cleaning operations run automatically every time. This is the feature that replaces manual copy-paste-cleanup workflows for data that needs to be imported and processed regularly.
Formatting, printing, and sharing
Format Cells (Ctrl+1) provides complete control over number format, alignment, font, borders, and fill. Format decisions that matter most for readability: consistent number formatting (currency with two decimal places, percentages with one, large numbers with comma separators), borders reserved for headers and section separators rather than applied everywhere, and fill colour used sparingly as a highlight for summary rows.
Freeze panes keep header rows and column labels visible while scrolling. Select B2 to freeze both row 1 and column A simultaneously → View → Freeze Panes → Freeze Panes. Always freeze the header row in any dataset that requires scrolling — navigating a wide spreadsheet without visible column headers is significantly slower and more error-prone.
Excel files saved to OneDrive or SharePoint enable real-time co-authoring — multiple users editing the same file simultaneously, each user’s cursor shown in a different colour. Share: File → Share → enter email addresses. Use comments (Review → New Comment) for asynchronous discussions about specific cells. Our guide on using Google Sheets covers the browser-native alternative for teams that want collaboration without a Microsoft 365 subscription. If this sounds familiar, How to Use Notion is worth a look.
Keyboard shortcuts that matter
- Ctrl+Shift+L: toggle filter on/off for selected data range
- Ctrl+T: format data range as a structured table
- Ctrl+E: Flash Fill — recognise and apply a pattern
- Ctrl+1: open Format Cells dialog
- Ctrl+Shift+$: apply currency format to selected cells
- Ctrl+Shift+%: apply percentage format
- Alt+Enter: line break within a cell (useful for addresses and multi-line notes)
- F4: cycle through reference types (absolute/relative) in formula bar
- Ctrl+`: toggle between showing values and showing formulas in all cells — useful for auditing a spreadsheet
- Ctrl+]: select all cells referenced by the active cell’s formula (trace dependents)
- Ctrl+[: select all cells that the active cell’s formula references (trace precedents)
Common Excel mistakes that cause real problems
- Storing numbers as text. A column of numbers that aren’t recognised as numbers won’t sum correctly — SUM returns 0 because none of the cells are numeric. This happens most often with data imported from other systems. Signs: numbers align left instead of right; SUM returns 0. Fix: select the column → Data → Text to Columns → Finish (converting the format in place).
- Using VLOOKUP when data could be reordered. VLOOKUP requires the lookup column to be the leftmost column in the range, and breaks if columns are inserted or reordered. XLOOKUP has neither constraint — use it in new workbooks.
- Merged cells in data ranges. Merged cells break sorting, filtering, PivotTables, and almost every data tool. Use “Center Across Selection” (Format Cells → Alignment → Horizontal → Center Across Selection) instead — the visual result is identical to merging but without the data compatibility problems.
- Hardcoding values that should be references. If a tax rate appears in five different formulas as 0.2, changing the rate requires finding and updating five formulas. Put the rate in a named cell (e.g., B1 named “TaxRate”) and reference it: all formulas update instantly when the rate changes.
- Not saving regularly, or not using AutoRecover. File → Options → Save → check AutoRecover and set the interval. Losing 2 hours of work to a crash is avoidable; losing 5 minutes is acceptable. The default 10-minute interval is too long — set it to 3–5 minutes.
Excel rewards the time spent learning it. The users who get the most from it are the ones who understand reference types (so formulas copy correctly), know PivotTables (so analysis takes minutes not hours), and use data validation and structured tables (so their workbooks stay usable by others). The learning investment pays returns on every spreadsheet built from that point forward — and in most professional environments, that’s a lot of spreadsheets. Our guide on How to Use WordPress covers an adjacent issue.
