Sign in with Google

How to Use Excel: The Core Skills That Matter

Excel has hundreds of features and about twelve that carry almost all real work.

beginner10 min read
creative-toolsexcelspreadsheetsdata-skillsbeginners
Excel has something like five hundred functions and a ribbon with a dozen tabs on it. About twelve things carry almost all of the real work; the rest are specialist tools you look up the day you need them. This guide covers those twelve, in the order they build on each other, using one small example you can type yourself. Learn only what is here and you will be more useful in a spreadsheet than most people who have used one for years.
iWhich Excel you need
Two things get called Excel: the subscription version, Microsoft 365, which updates continuously, and the one-time-purchase perpetual release, currently Excel 2024. Everything here works in both, plus Excel for the web, free with a Microsoft account. One caveat: XLOOKUP exists in Microsoft 365, Excel 2024, and Excel 2021, but not in Excel 2016 or 2019 — this guide shows VLOOKUP for those.
A sheet is lettered columns, numbered rows, and cells at the intersections. A1 is column A, row 1. That address is the whole point: it lets you refer to a number without retyping it. Every cell holds one of two things. A value is data you typed — a number, text, a date. A formula is an instruction producing a result, and it always begins with an equals sign. The formula bar shows what is really in a cell, which is how you tell a calculated 47 from a typed one.
Nearly every broken spreadsheet traces back to breaking one of these. They are not style preferences.
  1. 1Start every formula with an equals sign. Typing SUM(A1:A5) gives you the text SUM(A1:A5).
  2. 2Reference cells, never retype numbers. Write =B2*0.2, not =450*0.2. The moment B2 changes, a hard-coded 450 is a silent lie.
  3. 3Let the sheet do the arithmetic. Calculating a subtotal on your phone and typing the answer in throws away the thing you came here for.
  4. 4Put constants in their own labelled cell and point at them. One cell holding your tax rate beats 0.0825 scattered through forty formulas.
Set up a small example: column A holds a category, column B an amount, rows 2 through 13. Now the functions worth knowing by heart.
text
=SUM(B2:B13)                  add a range
=AVERAGE(B2:B13)              mean of the numbers
=COUNT(B2:B13)                how many cells hold numbers
=COUNTA(A2:A13)               how many cells are non-empty (text included)

=IF(B2>500, "over", "under")  test, then one answer or the other
=SUMIF(A2:A13, "Rent", B2:B13)   total only the Rent rows
=COUNTIF(A2:A13, "Rent")         count only the Rent rows

=XLOOKUP(D2, A2:A13, B2:B13, "not found")
=VLOOKUP(D2, A2:B13, 2, FALSE)   older Excel equivalent

Type these into empty cells beside your data

COUNT versus COUNTA catches people out: COUNT only sees numbers, so counting a column of names returns zero. SUMIF and COUNTIF are where a spreadsheet starts answering questions instead of adding things up. XLOOKUP replaces VLOOKUP: give it the value to find, the column to search, and the column to return, plus a plain-English fallback for misses. VLOOKUP needs a column number counted by hand and breaks when someone inserts a column — use it only if your Excel is too old.
This is the commonest cause of formulas that break when copied, and it takes five minutes to understand permanently. By default, references are relative: they describe a direction, not a fixed address. Copy =B2*2 down one row and it becomes =B3*2, because it means "the cell to my left", not "B2". Usually that is what you want.
text
        A         B          C
1   Item      Price      With tax
2   Mug       12.00      =B2*(1+E1)     -> correct
3   Shirt     24.00      =B3*(1+E2)     -> WRONG, E2 is empty
4   Hat       18.00      =B4*(1+E3)     -> WRONG

Fix it by locking the reference:

2   Mug       12.00      =B2*(1+$E$1)
3   Shirt     24.00      =B3*(1+$E$1)   copied down, still E1
4   Hat       18.00      =B4*(1+$E$1)

E1 holds the tax rate, 0.0825

A dollar sign freezes what follows it. $E$1 locks both column and row, which is what a single constant needs. $E1 locks the column only, so it can still slide down rows; E$1 locks the row only. That pair matters in a grid that looks sideways and downward at once. Rather than typing dollar signs, put the cursor on a reference in the formula bar and press F4 on Windows or Cmd+T on macOS to cycle the combinations.
✓How to spot this bug
If a column of formulas is right on the first row and wrong or zero underneath, you have a relative reference that should be absolute. Click the second cell and read its formula — the drift is obvious.
Ignore fonts and cell borders. Four formatting things affect whether the sheet works at all.
  1. 1Number formats. Format the cell as currency or percentage rather than typing the symbol in. A typed symbol turns the entry into text and every formula downstream stops seeing a number. Ctrl+1 or Cmd+1 opens Format Cells.
  2. 2Dates as real dates. A genuine date right-aligns; text left-aligns, and that is your quick test. Text dates cannot be sorted or subtracted. Fix a column with Data then Text to Columns then Finish.
  3. 3Freeze panes. View then Freeze Panes then Freeze Top Row keeps headers visible when you scroll.
  4. 4Conditional formatting. Home then Conditional Formatting colours cells by rule. Use it to find outliers, not to decorate.
Most people never do this, and it is the highest-leverage thirty seconds in Excel. Click anywhere in your data, press Ctrl+T on Windows or Control+T on macOS, confirm your table has headers, and name it in the Table Design tab — say Expenses.

What you get for that

  1. 1Structured references. Formulas become =SUM(Expenses[Amount]) instead of =SUM(B2:B13) — readable, and immune to rows moving.
  2. 2Auto-expansion. Type in the row beneath and the table absorbs it, extending every formula, format, and rule.
  3. 3Filter arrows on every header, so sorting and filtering need no setup. Sort by any column, or filter to one category.
  4. 4A Total Row you can toggle in Table Design, with a per-column dropdown for sum, average, or count.
  5. 5PivotTables and charts built on a Table pick up new rows on refresh. On a plain range, they do not.
!Filtered is not deleted
A filter hides rows; it does not remove them, and SUM still counts hidden rows. For a total of only what is visible, use the Table's Total Row or SUBTOTAL. Clear filters before sending a file to anyone.
This is the feature that turns a beginner into someone useful. A PivotTable summarises thousands of rows into an answer without a single formula, and rearranges that answer by dragging.
  1. 1Click inside your Table, then Insert then PivotTable, and accept a new worksheet.
  2. 2You get an empty outline and a field list with four drop areas: Rows, Columns, Values, Filters.
  3. 3Drag Category into Rows and Amount into Values. You now have a total per category. That is the whole trick.
  4. 4Values defaults to Sum for numbers and Count for text. Click the field in Values and open Value Field Settings to pick Average or Max instead.
  5. 5Drag Month into Columns for a category-by-month grid, or into Filters for a dropdown that limits the whole summary.
  6. 6After adding source rows, right-click the PivotTable and choose Refresh. PivotTables never update on their own — this is the step everyone forgets.
Select your summary, then Insert and pick Recommended Charts. Chart choice is mostly settled: bar or column for comparing categories, line for change over time, scatter for two numbers against each other. Pie charts only work with two or three slices, and almost nobody has three.
Then take things away. Delete the gridlines, the legend if there is one series, and the chart border, and never use a 3D effect — tilted bars are unreadable, which is the opposite of the job. Always start a bar chart's value axis at zero; truncating it exaggerates small differences and is the commonest way charts mislead. Label the axis with its unit, and title the chart with the finding, not the fields: "Rent is 41% of spending", not "Amount by Category".
text
Ctrl+Arrow      / Cmd+Arrow        jump to the edge of the data
Ctrl+Shift+Arrow                   select to the edge of the data
Ctrl+T          / Control+T        create a Table
Ctrl+1          / Cmd+1            Format Cells
Ctrl+;                             insert today's date as a value
Ctrl+D / Ctrl+R                    fill down / fill right
Alt+=           / Cmd+Shift+T      AutoSum
Ctrl+Shift+L    / Cmd+Shift+F      toggle filters
F4              / Cmd+T            cycle absolute references in a formula
F2              / Control+U         edit the selected cell in place

Windows, then macOS where it differs

The habits matter as much as the shortcuts. A spreadsheet that follows the rules below stays sortable, filterable, and pivotable; one that breaks them cannot be fixed later without retyping.

Rules for data that stays usable

  1. 1One table per sheet. Two tables side by side break sorting, filtering, and every PivotTable built on them.
  2. 2Headers in exactly one row, at the top, each unique. Two-line headers stop Excel recognising the range as data.
  3. 3Never merge cells inside data — merging breaks sorting, selection, and formulas. Centre across selection if you want the look.
  4. 4No blank rows or columns in the middle. A blank row is where Excel decides your data ends.
  5. 5One kind of thing per column, no units in the number cell. Typing 12 kg makes it text; put kg in the header.
  6. 6Keep raw data on its own sheet and analyse elsewhere, so a paste never overwrites your work.

Symptom, cause, fix

  1. 1The cell shows your formula as text. It is formatted as Text, or you omitted the equals sign. Set the format to General and retype.
  2. 2A column of formulas is right on the first row and wrong below. A relative reference should be absolute. Press F4 or Cmd+T on it.
  3. 3SUM returns a total that is too low. Some entries are text, usually from a typed currency symbol or trailing space. Numbers right-align, text left-aligns.
  4. 4A lookup returns #N/A though the value is there. Almost always a trailing space, or a number stored as text on one side. Wrap the lookup value in TRIM.
  5. 5Dates will not sort chronologically. They are text — run Data then Text to Columns then Finish.
  6. 6A #SPILL! error appears. A formula returning several results is blocked. Clear the cells beside it.
  7. 7The PivotTable ignores new rows. It has not been refreshed. Right-click and Refresh, and build on a Table next time.
Rebuild something you already track by hand — a budget, a reading log, match results — using a Table and a PivotTable. Real data with real messiness teaches more than a clean tutorial file. When a formula defeats you, How to Use ChatGPT Effectively covers asking so you get a working formula rather than a plausible one. If your tracking drifts toward notes rather than numbers, How to Use Notion is the better tool. When a spreadsheet stops being big enough, How to Choose Your First Programming Language is the honest next step. To point these skills at your own money, the Personal Finance roadmap gives them something real to work on.