Microsoft Excel: from basic formulas to clear analysis
Organise data, write reliable formulas, summarise results and present them in charts.
Last reviewed: November 2026
Core ideas
- Cell: one box in the grid, identified by its address such as B3.
- Formula: starts with an equals sign, for example
=B2*C2. - Function: a built-in formula, for example
=SUM(B2:B20). - Range: a block of cells, such as B2:B20.
Functions to learn first
| Function | What it does | Example |
|---|---|---|
| SUM | Adds numbers | =SUM(B2:B20) |
| AVERAGE | Calculates the average | =AVERAGE(B2:B20) |
| IF | Returns one result or another | =IF(C2>=50,"Pass","Fail") |
| COUNTIF | Counts cells that meet a condition | =COUNTIF(C2:C20,"Pass") |
| XLOOKUP | Finds a value and returns a match from another column (Microsoft 365 and recent versions) | =XLOOKUP(A2,Products!A:A,Products!B:B) |
Lookup functions compared
VLOOKUP searches the first column of a range and returns a value from a column you specify. XLOOKUP is newer and can search in any direction and return a default when nothing matches. INDEX and MATCH together work in older versions and are very flexible. If your version does not support XLOOKUP, use INDEX and MATCH.
Organise data with tables
Select your data and press Ctrl+T to create a table. Tables add filter buttons, make formulas easier to read and expand when you add rows.
Sort and filter
Select the whole table first, then use the Data tab to Sort or Filter. Selecting the whole table keeps each row together when sorting.
Create a chart
- Select the data, including the column headings.
- Go to Insert, then Recommended Charts.
- Use columns for comparisons, lines for trends, and pie charts only for simple shares of a total.
- Add a clear title and label the axes.
Summarise with PivotTables
A PivotTable groups and totals large amounts of data without writing formulas. Select your table, go to Insert, then PivotTable, and drag fields into Rows, Columns and Values.
$B$2 when a formula must always point to one cell, such as a tax rate.Common problems
A formula appears as text
Check that the cell is not formatted as Text, and that the formula starts with an equals sign.
I see #VALUE! or #DIV/0!
#VALUE! usually means a number is stored as text or a function received the wrong type of data. #DIV/0! means you are dividing by zero or by an empty cell. Find the cause before you hide the error with IFERROR.
The formula does not update
Go to Formulas, then Calculation Options, and make sure it is set to Automatic.
Frequently asked questions
Is XLOOKUP available in every version?
No. It is available in Microsoft 365 and in recent versions of Excel. Check your version under File, then Account.
Why does my data look right but the formula returns zero?
The numbers may be stored as text. Check the cell format and use Data, then Text to Columns to convert them.