Work skills

How to learn Excel from scratch

Excel is learned fastest on your own data — a household budget, a task list, an export from work. At an hour a day, six weeks take you from your first formulas to pivot tables and charts. Below is the order of topics, the exercises and ways to tell that a topic has really sunk in.

Updated
In this article

Prepare a dataset of your own#

Practice files from courses are forgotten; your own are not. Build a table you actually need: three months of spending, a client list, a training schedule, a sales export from a system at work. A hundred or two hundred rows and five or six columns are enough: date, category, amount, note. Every exercise in the plan uses this file.

One rule will save you months: one row is one record, one column is one attribute, no merged cells and no blank rows inside the data. Most of the trouble beginners have with formulas and pivot tables comes from breaking exactly this rule.

Week 1: entering data, formats and navigation#

Learn to move around a sheet quickly with the keyboard, select ranges, freeze the header row, and use sort and filter. Get to grips with number formats: number, date, percentage, currency. Excel stores a date as a number, and if a column of dates is stored as text, neither sorting nor filtering by period will work. The check for this stage: in one minute you can find all the spending in one category for one month.

Weeks 2–3: formulas and references#

A formula starts with an equals sign. Begin with arithmetic and the functions SUM, AVERAGE, MIN, MAX and COUNT. Then comes the most important topic for a beginner: references. A relative reference shifts when you copy the formula, an absolute one (with dollar signs, such as $B$1) stays put, and a mixed one locks only the row or only the column. Practise on a multiplication table: one formula copied across the whole square should give the right result everywhere.

Next, logic and conditions: IF, SUMIF, COUNTIF. Work out the spending in each category and flag the months where spending went over a limit.

Week 4: lookups and linking tables#

Lookup functions join two tables on a shared field: VLOOKUP exists in every version, and newer versions add the more flexible XLOOKUP. Make a second sheet as a reference table, for example "category — limit", and pull the limit into every row of spending. Learn to read the errors: #N/A means the value was not found, #DIV/0! is division by zero, #REF! means a cell the formula pointed to was deleted.

Week 5: pivot tables#

A pivot table answers "how much for each…" without a single formula. Build a summary of your data by month and category, then change the grouping, add a filter and show values as a percentage of the total. If the data is a clean table with no gaps, a pivot table takes half a minute; if not, go back to the rule at the top of this page.

Week 6: charts and a final report#

Pick one chart per question: change over time — a line chart; comparing categories — a column chart; share of a whole — a pie chart, but only with three to five slices. The result of the course is one report sheet: a pivot table, two charts and a short conclusion in words. If a week later you can update it just by adding new rows of data, the skill is in place.

How to check yourself#

Before you look at a formula's result, estimate it in your head. A gap between the estimate and the result is the main sign of a mistake in a reference or a range. Once a week, rebuild one table from the week before from scratch, without looking at the old file — the same idea as active recall, applied to a spreadsheet.

Step-by-step plan

  1. Week 1 — data and formatsYour own dataset, one row per record; sort, filter, date and number formats.
  2. Weeks 2–3 — formulas and referencesSUM, AVERAGE, IF, SUMIF; relative, absolute and mixed references.
  3. Week 4 — lookupsVLOOKUP or XLOOKUP with a reference table; reading the common formula errors.
  4. Week 5 — pivot tablesA summary by month and category, grouping, filters, percentages of the total.
  5. Week 6 — the reportA sheet with a pivot table, two charts and a conclusion that updates with new data.

Start learning this in your own space

The plan goes into your repository: tick off stages, keep notes — the change history shows how far you have come.

Start the plan

Check yourself

1.Cells A1, A2 and A3 hold 4, 6 and 10. What does the formula =SUM(A1:A3)/2 return?

2.Cell A1 holds 7. What does the formula =IF(A1>5, A1*2, A1) return?

3.Which reference changes neither its row nor its column when you copy the formula?

Sources

Was this helpful?

More articles

Work skills How to learn copywriting from scratch Copywriting is the skill of writing a text that people read to the end and then take the action you need. You can learn it on your own if you write every day, edit your drafts by clear rules and build a portfolio from real tasks. Below is an eight-week plan. Work skills How to learn marketing from scratch Marketing is not advertising. It is a chain of decisions about whom to offer what, through which channel, and how to tell whether it paid off. It is easiest to learn on your own with one practice project that goes through every stage. Below is a four-month plan and ways to check yourself with numbers rather than impressions. Work skills How to learn bookkeeping from scratch Bookkeeping is a discipline with strict logic, not a set of buttons in a program. Once you understand the balance sheet and double-entry, everything else — payroll, taxes, financial statements — fits onto a frame you already have. Below is a three-month plan for an adult starting from a blank page. It is built on double-entry principles that work the same in every country; local tax rules are a separate layer on top. Math and natural sciences How to learn statistics from scratch Analysts, researchers, doctors, marketers and anyone who reads the news with numbers in it need statistics. The easiest way to learn it from zero is in three layers — describe the data, understand probability, draw conclusions from a sample — and to calculate on real data at every layer. Here is the order of topics, how to practise and the traps that catch even experienced people. Math and natural sciences How to learn physics from scratch For many people school physics stayed a pile of formulas to plug numbers into before a test. As an adult you have it easier: you can take your time and ask, every time, where a formula comes from and what it describes in the real world. The plan below goes from mechanics to electricity and optics — in the order in which each topic rests on the previous one. Math and natural sciences How to learn chemistry from scratch Chemistry looks frightening because of all the formulas, but it rests on a handful of ideas — what an atom is made of, why atoms bond, and how to count substances in moles. Take them in order and the rest — organic chemistry, solutions, electrochemistry — builds on a solid base. Below is a three-to-four-month plan for an adult starting from an almost blank page.

More solutions

Create your own online course If you have something to explain and want to build a course rather than a sales page, you can start for free. A course on Skillok is a challenge in a repository: stages in order, each with its own check, and the materials themselves are ordinary files with an edit history. Programmer portfolio An employer looks at a portfolio for a couple of minutes. In that time they need to see what you can do, how you write code and whether you finish what you start. A few finished projects with clear READMEs and an honest history of the work show that best. Company knowledge base A company knowledge base rarely dies for lack of tools. It dies because nobody owns the pages and they quietly go out of date. Here is how to set up a base that has owners, a history and a clear way to change it.