Excel: from first cell to confident analysis
Learn Excel tables, formulas, lookups, and PivotTables with guided examples and a small sales-analysis project. No spreadsheet experience needed.
Learn it here, one small step at a time.
4 guided lessons · About 2h 30m including practice · No subscription
- Organize a clean, reliable worksheet
- Write formulas and choose safe references
- Summarize a small dataset with a PivotTable
Build a tidy workbook
A spreadsheet is easiest to understand when each row describes one thing and each column describes one detail. Start with a clear header row, one type of value per column, and no blank rows inside the data. In Excel, select the data and choose Format as Table (or press Ctrl+T). A table grows when you add rows and gives formulas meaningful names.
For the mini-project, use this sales data. Keep the original rows intact; later lessons will turn them into useful answers.
| Date | Item | Region | Quantity | Unit price |
|---|---|---|---|---|
| 2026-05-02 | Notebook | North | 3 | $4 |
| 2026-05-03 | Pen | South | 5 | $2 |
| 2026-05-04 | Notebook | South | 2 | $4 |
Create a table called `Sales` from the three sample rows. Add headers exactly as shown, then sort by Date. Notice that sorting the whole table keeps each sale together.
Reveal a suggested answer
Each column stores one kind of information, so Excel can filter, calculate, and summarize it reliably.
Optional further reading
Core lesson and practice are on this page. For more detail, visit the official free reference ↗.
Make formulas do the arithmetic
Every Excel formula begins with =. For example, =D2*E2 multiplies quantity by unit price. A relative reference such as D2 adjusts when you copy the formula down; an absolute reference such as $H$1 stays fixed. Structured references make table formulas easier to read: =[@Quantity]*[@[Unit price]] calculates the current row.
Check one row by hand before filling a formula down. For the first notebook sale, three items at $4 each should total $12.
| Goal | Example formula | Plain-language meaning |
|---|---|---|
| Line total | =[@Quantity]*[@[Unit price]] | Items sold × price for this row |
| All sales | =SUM(Sales[Line total]) | Add every line total |
| North sales | =SUMIF(Sales[Region],"North",Sales[Line total]) | Add totals for North only |
Add a `Line total` column to `Sales`. Write the formula once, let the table fill it down, and compare the first result with your hand calculation.
Reveal a suggested answer
It changes to `D3`, so the next row uses its own quantity. Use `$D$2` only when you need a reference to stay fixed.
Optional further reading
Core lesson and practice are on this page. For more detail, visit the official free reference ↗.
Find and label the right values
When your work grows, a lookup can bring a matching value from another table. XLOOKUP searches for a key and returns its matching result: =XLOOKUP([@Item],Prices[Item],Prices[Unit price],"Not found"). The item names must match. Use IF to label a result, such as =IF([@[Line total]]>=10,"Large","Small").
Formulas can be correct yet answer the wrong question. Test one known item, decide what should happen if it is missing, and make that fallback visible instead of hiding an error.
| Item | Unit price |
|---|---|
| Notebook | $4 |
| Pen | $2 |
| Folder | $5 |
Create a `Prices` table with the values above. Use XLOOKUP to fill Unit price for each sale. Temporarily change `Pen` to `Marker` and confirm your formula displays `Not found`.
Reveal a suggested answer
It makes missing matches understandable and easier to fix than an unexplained error or an incorrect price.
Optional further reading
Core lesson and practice are on this page. For more detail, visit the official free reference ↗.
Summarize the project with a PivotTable
A PivotTable groups rows without changing the source data. Select a cell in Sales, choose Insert → PivotTable, put Region in Rows and Line total in Values, and confirm Values uses Sum. Add Item under Rows to see the next level of detail. A slicer can make a report easier to filter.
After adding or changing source rows, refresh the PivotTable. Compare its grand total with =SUM(Sales[Line total]). A matching total is a simple but useful check.
| PivotTable area | Field | Question it answers |
|---|---|---|
| Rows | Region | Which areas are included? |
| Columns | Item | How do products compare? |
| Values | Sum of Line total | How much was sold? |
| Filters | Date | Which period is shown? |
Build a PivotTable showing total sales by Region. Add Item as a second row field, refresh it, and write one sentence describing the largest total.
Reveal a suggested answer
No. It summarizes the source. Keep the source table and refresh the PivotTable when those records change.
Optional further reading
Core lesson and practice are on this page. For more detail, visit the official free reference ↗.
Recommended next steps
These paths build on ideas from this course. You can start any of them whenever you are ready.
Your final knowledge check.
Score 80% or more to pass. Sign in to save your best result.