How to learn Excel: from a table to a reliable report

Learning Excel can become an exercise in collecting bookmarks. A video explains a formula, another course promises dashboards, and someone shares a hundred keyboard shortcuts. A month later, you recognise more names but still struggle to produce a report independently. I organise learning around a task: some data, a question, a result and a way to check whether that result makes sense.

The plan below uses a small orders report. It moves from organising records through calculations and summaries to refreshing an import. I do not assign a fixed number of days to each stage. Your pace depends on your starting point and the opportunities you have to practise. A clear completion criterion is more useful than another tick beside a watched lesson.

Begin with a question and four records

I will use synthetic data throughout this exercise. Each row represents one order containing one product. Amount is the total value of that order in a single illustrative unit. There are no taxes, refunds or currency conversions. Those simplifications keep the mechanics visible and let you work out the answers without a computer.

Put the headers in A1:E1 and the records in rows 2:5. Ask three questions: what is the total amount, how much belongs to each region, and which orders are worth at least 30? The answers are 120 overall, 80 for North, 40 for South and two orders meeting the threshold.

These answers are your control totals. A large dataset may feel more professional, but it makes mistakes harder to notice while learning. With four rows, you can see immediately if sorting has separated an amount from its order. Increase the volume after you understand the operation. You want difficulty to come from a new idea, rather than from finding your place in a huge file.

Stage one: tables, data types and filters

Select the data and create a table using Insert, Table. In Excel for Windows, Ctrl+T also opens this operation. Check that the selection includes every column and that the headers are recognised. Give the table a useful name such as Orders. Microsoft’s structured reference guide explains how table and column names can be used in formulas.

Filter Region to North. You should see Z01 and Z03. Clear the filter, sort the whole table by Amount descending, and confirm that Z03 still contains three units and an amount of 60. This check matters more than attractive header colours. Sorting a detached column can destroy the relationship between fields while leaving a perfectly tidy worksheet.

Distinguish the stored value from its appearance. A currency format changes how a number is displayed; it does not establish its business meaning. Product codes with leading zeros may need to remain text. A date should be recognised as a date rather than an arbitrary string. Inspect several cells and the formula bar when working with an export.

I consider this stage complete when you can add a record, change a filter and sort the table without damaging the data. You should also be able to explain what one row means. If a file mixes orders with individual order lines, fluent clicking will not resolve that ambiguity. Write down the intended level of detail before trying to summarise it.

Stage two: formulas and references

Start with ordinary arithmetic and a few functions. There is no need to memorise the entire catalogue. For the amounts in this example, enter these formulas outside the table:

=SUM(E2:E5)
=AVERAGE(E2:E5)

The results are 120 and 30. The SUM documentation explains summing a range. The AVERAGE documentation also makes an important distinction: empty cells in a reference are ignored, whereas zero is included. A missing amount and a recorded zero therefore require different decisions.

Learn what happens when you copy a formula. If a calculation multiplies a row value by a parameter stored in H1, $H$1 keeps that address fixed during copying. A relative reference moves with the formula. Before filling a thousand rows, copy down one row and inspect the references. That small habit makes the behaviour visible before an error spreads.

Function names and argument separators depend on your configuration. English examples commonly use commas; Polish examples often use semicolons and translated function names. Recognising both names can be useful, but changing your entire interface is not a prerequisite for learning. Keep a short personal glossary of functions you actually use, rather than treating translation as another syllabus to memorise.

Try explaining a formula without reading its characters aloud. “I am adding the amounts for these four orders” shows more understanding than reciting an address. For an average, explain the denominator as well. If you cannot say which observations belong in the calculation, return to the data before learning a more complicated formula.

Stage three: conditions and lookup tables

Use IF to classify an order. In an additional column, enter this formula for the first record:

=IF(E2>=30,"larger","smaller")

Z01 receives “smaller”, while Z02 receives “larger”. The boundary is part of the rule: an amount of exactly 30 belongs to the larger category. Microsoft describes IF as choosing between two results based on a logical test. Change the threshold during practice and check a value exactly on the new boundary.

Retrieving information from a lookup table is a separate problem. An order’s product code might identify a name or price in a catalogue. Functions such as VLOOKUP and XLOOKUP help with this task. Before choosing one, check whether the catalogue contains one appropriate row per key. Two prices for one code require a selection rule, perhaps based on a date.

XLOOKUP defaults to an exact match and accepts separate lookup and return ranges. Microsoft states that it is unavailable in Excel 2016 and Excel 2019. If you are learning through a shared workbook, establish which versions its recipients use. A newer formula brings little benefit if another person cannot calculate it.

Do not replace every failed lookup with zero. A missing catalogue entry differs from a product with a valid zero price. Decide how the report should communicate missing data and who needs to resolve it. This is also a good time to learn a basic formula troubleshooting process, so that handling an error does not become hiding it.

Stage four: summaries with conditions

Once an ordinary sum is clear, add a condition. To calculate North’s amount, use:

=SUMIFS(E2:E5,B2:B5,"North")

The result is 80: 20 from Z01 and 60 from Z03. The sum range and the region range cover corresponding rows. The SUMIFS reference describes the argument order and the additional range and criterion pairs used to introduce more conditions.

Learn COUNTIFS and AVERAGEIFS through the same questions. North has two orders, five units and an amount of 80. These are three different measures for the same group. The first counts records, the second adds quantities, and the third adds amounts. A report can be numerically correct while answering a different question from the one its reader intended.

As an exercise, add a condition for Folder. Only one North order remains, with an amount of 60. Then change the product on one record and predict the new result before recalculation. Small changes of this kind make the selection rule tangible. Copying a finished formula perfectly is less useful if you cannot explain why a particular record was included.

Stage five: a PivotTable and a chart

Create a PivotTable from the orders table. Put Region in Rows and Amount in Values. Inspect the calculation selected for that value field: you need a sum rather than a count. The results should match your control totals of 80 and 40. Microsoft’s PivotTable instructions cover creation, field placement and refreshing.

Place Product below Region and observe the change in detail. You are changing the layout of a summary instead of writing a new formula for every combination. Add another order to the source table and refresh the PivotTable. Do not assume that the visible report already includes the last source change. Checking the refresh is part of producing the result.

For comparing two regions, I would choose a simple bar chart. Label the unit and the period covered. Show the information that prompted the chart, rather than demonstrating every effect in the menu. If the data covers only part of a month, make that visible before a reader compares it with a complete earlier month.

You have reached a useful milestone when you can obtain the same summary through two methods and explain a disagreement. Check the source range, active filters, aggregation and refresh state before adding visual decoration. Independent totals give you a practical way to investigate the report, especially after a source file changes or someone edits the workbook.

Stage six: a workbook another person can use

When someone else enters data, predictable values become important. Create a short source list containing North and South, then use Data Validation with the List setting. Microsoft’s drop-down instructions explain choosing a source and setting an alert for invalid entries. A list reduces some typing mistakes; it does not replace reviewing imported data.

Separate input fields, calculations and final output. Include a short instruction explaining what to complete, where the source data lives and what needs refreshing. Do not assume that a yellow fill universally means “edit here”. Explain its purpose in words, particularly if other colours already indicate order statuses or warnings elsewhere in the file.

I use conditional formatting for a specific action, such as finding records with missing product codes. Every column does not need a different rainbow. A rule should help someone correct an entry, inspect an exceeded threshold or read an explanation. When everything demands attention, the reader has no clear place to begin. Design around the task they need to finish.

Give a copy of the exercise to someone unfamiliar with your intentions, or reopen it after a few days without consulting your notes. Try producing the report using only the instructions inside the workbook. Places where you have to guess are the next improvements to make. This exercise teaches handover, which matters even when the future recipient is simply you.

Stage seven: repeatable imports with Power Query

If you receive a similar CSV every week, learn to import it through Power Query. Establish the delimiter, encoding and column types, remove unnecessary fields and load the result. Microsoft’s introduction describes connecting, transforming, combining and loading data, with refresh available for repeating the process.

For practice, prepare two small exports with the same structure but different records. Define the import steps for the first, then check whether the second follows the same route. Compare row counts and total amounts separately. A changed column name or date format should become visible before you present the output. Repetition alone does not establish that an import remains correct.

Features and connectors differ across platforms and versions. Record your environment instead of promising identical buttons on Windows, Mac and the web. Also record where the source file is expected to be found. A process tied to a forgotten download folder will be difficult to hand over, even if every individual transformation is easy to understand.

Choose automation after describing the process

Learn VBA when you have a concrete operation to automate. I do not treat it as a compulsory final exam for Excel. Tables, formulas and Power Query solve many problems. Office Scripts may be relevant in another environment. Microsoft’s comparison explains the desktop focus of VBA and the cross-platform approach of Office Scripts.

First describe the process in ordinary sentences: where the data comes from, which steps you perform, what you deliver and how you notice a failure. Then choose the tool. Automating an unclear process makes it easier to repeat the same mistake quickly. Pay particular attention to records that might disappear without producing a visible warning in the final report.

Choose one deliverable for each learning session: a working filter, a correct price lookup or a refreshed import. Keep the workbook before and after your change. Add one sentence about the problem you solved and another about a limitation. This collection becomes a useful reference because it connects an operation to a reason for using it.

Finally, build a report from fresh data without opening the previous solution. Documentation is allowed, as it is in ordinary work. Check totals, introduce a missing product, change a region and ask the workbook for a new summary. If you can explain the outcome and adjust it when assumptions change, you have clear evidence of progress. Choose the next subject from the part where you still need help.