Mathew K Analytics

Lesson 1 · Excel: Zero to Expert

Learn Excel from Scratch: Full Beginner Course (Microsoft 365) | Excel 101

Sheet: Welcome Workbook: excelbasics.xlsx. Download it from this page to see the finished result, or start a new blank workbook and build it step by step…

⬇ Download workbook (.xlsx)

What you'll learn

Files for this lesson

Open the workbook in Excel (Microsoft 365 or Excel 2021) and follow along with the video.

📝 Step-by-step guide

Excel for Absolute Beginners: Your First Hour of Excel#

Sheet: Welcome

Workbook: excel_basics.xlsx. Download it from this page to see the finished result, or start a new blank workbook and build it step by step with the video. Each section below lists exactly what to click and type.

What is Excel?#

A tour of the Excel window#

  • Click C5
  • Click F12
  • Select B2:D6
  • Click A1

While I was dragging, the selected block was shaded, with B2 left white because that's the active cell, where typing would go. We call a block of cells a range, and we write it as the first cell, a colon, and the last cell. So that block was B2 colon D6.

  • Press Ctrl+Down
  • Press Ctrl+Right
  • Press Ctrl+Home

Did you see the Name Box? The last row is row one million, forty eight thousand, five hundred and seventy six, and the last column is X F D. That's more than seventeen billion cells on one sheet. You will never run out of room.

  • Select C:C
  • Select 3:3
  • Select B:D
  • Click A1
  • Press Right, Right, Right
  • Press Down, Down
  • Press Tab
  • Press Pagedown
  • Press Ctrl+Home
  • In A1 type Hello Excel!
  • Click A1

Workbooks, sheets and getting help#

Entering text, numbers and dates#

Sheet: Entering

  • In A1 type My first table

  • Starting in A3, type these rows (one value per cell, moving right):

    | Name | Age | Start date | |---|---|---| | Sarah | 34 | 14-Feb-2025 | | Tom | 28 | 1-Jul-2024 | | Priya | 41 | 9-Mar-2023 |

Now look closely at how Excel lined things up. The names sit on the left side of their cells, and the ages and dates sit on the right. That's not an accident. Excel puts text on the left and numbers on the right. Dates are really numbers underneath, so they go right too. This is a handy check. If you type a number and it sits on the left, Excel has read it as text, perhaps because of a stray space or letter, and it won't calculate with it.

  • In B4 type 35
  • Click A5
  • Press F2
  • Type my
  • Double-click B6 to edit it
  • Press End
  • Press Backspace
  • Type 2
  • In A8 type Oops, wrong cell
  • Press Esc
  • Clear A6:C6 (Delete key)
  • Press Ctrl+Z

Priya is back, with her age of forty two and her start date, exactly as before.

  • Starting in E3, type these rows (one value per cell, moving right):

    | Price | Discount | Stock | |---|---|---| | $25 | 15% | 1,200 |

All three sit on the right, so they're real numbers. Excel kept the dollar sign as a currency format, the percent sign as a percentage format, and the comma as a thousands separator. Underneath, the cells simply hold twenty five, zero point one five, and twelve hundred. That's what Excel will use in calculations.

  • In A10 type This is a long sentence that will not fit in one cell
  • In B10 type Hi
  • Click A10
  • Clear B10 (Delete key)

Excel as a calculator#

Sheet: Calculator

  • In A1 type Excel as a calculator

  • Starting in A3, type these rows (one value per cell, moving right):

    | Add | =2+3 | |---|---| | Subtract | =10-4 | | Multiply | =6*7 | | Divide | =20/8 | | Power | =2^3 |

Look at column B. Five, six, forty two, two point five, and eight. All calculated instantly. And while those answers are selected, glance at the status bar at the bottom. Excel shows their average, the count of cells, and the sum, which is sixty three point five.

  • Click B5
  • In D3 type 2+3

Excel just shows the text, two plus three, sitting on the left. With no equals sign, Excel has no idea you wanted a calculation. If a formula ever doesn't calculate, check for the equals sign first.

Order of operations and brackets#

  • Starting in D3, type these rows (one value per cell, moving right):

    | Without brackets | =2+3*4 | |---|---| | With brackets | =(2+3)*4 | | Power first | =10-2^2 | | Subtract first | =(10-2)^2 |

Without brackets we get fourteen, but with brackets we get twenty. Same numbers, completely different answers. Ten minus two squared is ten minus four, which is six. But ten minus two, all squared, is eight squared, sixty four. When in doubt, add brackets. They never hurt, and they make your formulas easier to read.

  • Starting in A10, type these rows (one value per cell, moving right):

    | Price with GST | =120*1.1 | |---|---| | Three coffees | =3*4.5 | | Pizza split four ways | =(18+22+15)/4 |

The price with GST is one hundred and thirty two dollars, the coffees cost thirteen fifty, and each friend pays thirteen dollars seventy five.

  • Starting in A14, type these rows (one value per cell, moving right):

    | Tip of 15% | =80*15% | |---|---| | Price after 20% off | =80-80*20% |

The tip is twelve dollars, and the jacket now costs sixty four dollars.

  • Click B3
  • Press F2
  • Press Backspace
  • Type 4

The formula is now equals two plus four, and the answer is six.

Cell references#

Sheet: References

  • In A1 type Shopping list

  • Starting in A3, type these rows (one value per cell, moving right):

    | Item | Price | Qty | Total | |---|---|---|---|

  • Starting in the next empty row, type these rows (one value per cell, moving right):

    | Bread | 4.5 | 2 | |---|---|---| | Milk | 3.2 | 3 | | Eggs | 6.8 | 1 | | Apples | 0.9 | 6 |

  • In D4 type the formula =B4*C4

Bread comes to nine dollars, milk nine sixty, eggs six eighty, and apples five forty. The status bar shows the sum of the selected totals: thirty point eight.

  • Starting in A8, type these rows (one value per cell, moving right):

    | Total | | | =D4+D5+D6+D7 | |---|---|---|---|

  • In C5 type 5

The milk total jumped from nine sixty to sixteen dollars, and the grand total updated from thirty point eight to thirty seven point two, all by itself. That's the heart of Excel. You build the formulas once, and then you can change any input, and every answer that depends on it follows along.

  • Starting in F3, type these rows (one value per cell, moving right):

    | Budget | 50 | |---|---|

  • Starting in F4, type these rows (one value per cell, moving right):

    | Left to spend | =G3-D8 | |---|---|

Fifty minus thirty seven point two leaves twelve dollars eighty to spend. And this cell depends on D8, which depends on all the rows above it. Change any price or quantity, and the whole chain updates.

  • Press Ctrl+Backtick
  • Press Ctrl+Backtick

The fill handle and relative references#

Sheet: Fill

  • In A1 type Weekly pay

  • Starting in A3, type these rows (one value per cell, moving right):

    | Name | Hours | Rate | Pay | |---|---|---|---|

  • Starting in the next empty row, type these rows (one value per cell, moving right):

    | Ava | 38 | 32 | |---|---|---| | Ben | 25 | 28.5 | | Chloe | 40 | 35 | | Dev | 12 | 26 | | Ella | 30 | 30 |

  • In D4 type the formula =B4*C4
  • Fill D4:D8 down (drag the fill handle, or select it and press Ctrl+D)

One drag, and every person has their pay. Ava gets one thousand two hundred and sixteen dollars, Ben seven hundred and twelve fifty, Chloe one thousand four hundred, Dev three hundred and twelve, and Ella nine hundred. The status bar shows the total wage bill for the week: four thousand five hundred and forty point five.

  • Click D6
  • Press Ctrl+Backtick
  • Press Ctrl+Backtick
  • In F3 type Day
  • In F4 type Mon
  • Fill F4:F10 down (drag the fill handle, or select it and press Ctrl+D)

Excel recognised Monday and filled in the rest of the week for us: Tuesday, Wednesday, Thursday, Friday, Saturday and Sunday. It does the same with month names like January, and with text that ends in a number, like Week one, Week two, Week three. A real time saver.

  • In H3 type Roster
  • In H4 type Week 1
  • Fill H4:H8 down (drag the fill handle, or select it and press Ctrl+D)

Week one, Week two, Week three, Week four and Week five. Excel spotted the number at the end and counted up.

  • Starting in A9, type these rows (one value per cell, moving right):

    | Total | =SUM(B4:B8) | |---|---|

  • Fill B9:D9 right (Ctrl+R)

Column B shows one hundred and forty five hours, and column D shows the total pay of four thousand five hundred and forty dollars fifty. The reference shifted across one column at a time, from B to C to D. But look at C9. It says one hundred and fifty one point five. That's the sum of everyone's hourly rates, which means nothing at all.

  • Clear C9 (Delete key)

Your first functions: SUM, AVERAGE, MIN, MAX and COUNT#

Sheet: Functions

  • In A1 type Maths test scores

  • Starting in A3, type these rows (one value per cell, moving right):

    | Student | Score | |---|---|

  • Starting in the next empty row, type these rows (one value per cell, moving right):

    | Amy | 78 | |---|---| | Ben | 92 | | Carla | 65 | | Dan | 88 | | Eve | 71 | | Finn | 84 |

  • Starting in D3, type these rows (one value per cell, moving right):

    | Total | =SUM(B4:B9) | |---|---| | Average | =AVERAGE(B4:B9) | | Lowest | =MIN(B4:B9) | | Highest | =MAX(B4:B9) | | How many | =COUNT(B4:B9) |

The class scored four hundred and seventy eight points in total. The average is seventy nine point six six six, and so on. The lowest score was sixty five, the highest ninety two, and six students sat the test.

  • Format E4: number format number

  • Starting in D9, type these rows (one value per cell, moving right):

    | Range | =E6-E5 | |---|---|

The average now reads seventy nine point six seven. Formatting only changes how a number looks. Underneath, Excel still keeps every decimal place. And the range of scores is ninety two minus sixty five, which is twenty seven points.

  • In A10 type Total
  • Select B10 and click AutoSum (Alt+=)

There it is: four hundred and seventy eight, the same as our hand written SUM. And the formula bar shows equals SUM of B4 to B9. The little arrow next to AutoSum also offers Average, Count, Max and Min, so you can build any of these with two clicks.

  • Select B4:B9

Now look at the status bar at the bottom of the window. Average seventy nine point six six, Count six, Sum four hundred and seventy eight. If you right click the status bar, you can switch on Minimum and Maximum as well. It's perfect for a quick check, without typing anything into the sheet.

  • In B6 type 75

Every function updated. The total is now four hundred and eighty eight, the average is eighty one point three three, and the lowest score is now Eve's seventy one. The highest and the count haven't changed.

  • Starting in D11, type these rows (one value per cell, moving right):

    | Names listed | =COUNTA(A4:A9) | |---|---| | Numbers in names | =COUNT(A4:A9) |

  • Format D3:D12: AutoFit columns

COUNTA finds six names, but COUNT finds zero, because there isn't a single number among the names. Use COUNT for numbers, and COUNTA when you want to count entries of any kind.

Tips for using functions#

Absolute references with the dollar sign#

Sheet: Commission

  • In A1 type Sales commission

  • Starting in A3, type these rows (one value per cell, moving right):

    | Rep | Sales | Commission | |---|---|---| | Hannah | 12000 | | | Josh | 8500 | | | Kim | 15250 | | | Leo | 6400 | | | Mia | 9800 | |

  • Starting in E3, type these rows (one value per cell, moving right):

    | Commission rate | 5% | |---|---|

  • AutoFit the width of column E (double-click its right edge)

  • In C4 type the formula =B4*F3
  • Fill C4:C8 down (drag the fill handle, or select it and press Ctrl+D)

Hmm. Hannah's six hundred dollars is right, but everyone else gets zero. That can't be right. Josh sold eight and a half thousand dollars.

  • Click C5
  • Click C6

Here's what happened. When we copied the formula down, both references shifted down one row each time. Moving the sales reference was exactly right. But the rate moved too, away from F3 and into empty cells, and Excel treats an empty cell as zero. So we need to tell Excel: let B4 move, but lock F3 in place. That's what an absolute reference does.

Locking a cell with F4#

  • In C4 type the formula =B4*F3
  • Press F4
  • Press Enter
  • Fill C4:C8 down (drag the fill handle, or select it and press Ctrl+D)

Much better. Hannah gets six hundred dollars, Josh four hundred and twenty five, Kim seven hundred and sixty two fifty, Leo three hundred and twenty, and Mia four hundred and ninety.

  • Click C7

  • Starting in A9, type these rows (one value per cell, moving right):

    | Total | | =SUM(C4:C8) | |---|---|---|

  • Format B4:C9: number format currency

  • Format A3:C9: AutoFit columns

The total commission bill is two thousand five hundred and ninety seven dollars fifty.

  • In F3 type 6%

Every commission updated in a flash. Hannah now gets seven hundred and twenty, Josh five hundred and ten, Kim nine hundred and fifteen, Leo three hundred and eighty four, and Mia five hundred and eighty eight. The total is now three thousand one hundred and seventeen dollars.

Practice: relative or absolute?#

Formatting your sheet#

Sheet: Formatting

  • In A1 type Quarterly Sales Report

  • Starting in A3, type these rows (one value per cell, moving right):

    | Region | Sales | Target | Achieved | |---|---|---|---|

  • Starting in the next empty row, type these rows (one value per cell, moving right):

    | North | 48600 | 50000 | =B4/C4 | |---|---|---|---| | South | 61400 | 55000 | | | East | 39900 | 45000 | | | West | 52900 | 50000 | |

  • Fill D4:D7 down (drag the fill handle, or select it and press Ctrl+D)

It works, but it's not pretty. The achieved column shows long decimals like zero point nine seven two, and there are no dollar signs or commas. Let's fix all of that.

  • Format A1: bold, font size 16
  • Format A1: font colour Dark Blue
  • Format A1:D1: merge & centre
  • Format A3:D3: bold, font colour White, Background 1, fill Blue, Accent 1, align center
  • Format A3:D7: all border
  • Format B4:C7: number format currency
  • Format B4:C7: fewer decimals (2)
  • Format D4:D7: number format percent
  • Format D4:D7: more decimals (1)
  • Format D4:D7: align center

Now the numbers tell a story. North achieved ninety seven point two percent of its target. South hit one hundred and eleven point six percent, East eighty eight point seven percent, and West one hundred and five point eight percent. Remember, the cells still hold the same values as before. Zero point nine seven two is now simply displayed as ninety seven point two percent.

  • Format A3:D7: AutoFit columns
  • In A9 type Note: figures exclude GST and are rounded to the nearest dollar.
  • Format A9: wrap text
  • Press Ctrl+Z
  • Format A9: italic, font colour Gray, Accent 3
  • Click D5

The Format Cells dialog#

Copying formats with the Format Painter#

Formatting summary#

Inserting and deleting rows and columns#

Sheet: Rows

  • In A1 type Monthly bills

  • Starting in A3, type these rows (one value per cell, moving right):

    | Expense | Cost | |---|---|

  • Starting in the next empty row, type these rows (one value per cell, moving right):

    | Rent | 1500 | |---|---| | Food | 450 | | Petrol | 120 | | Internet | 80 | | Total | =SUM(B4:B7) |

The total is two thousand one hundred and fifty dollars.

  • Click A6

  • Press Shift+Space

  • Press Ctrl+Shift+Plus

  • Starting in A6, type these rows (one value per cell, moving right):

    | Phone | 60 | |---|---|

Look at the total, which has moved down to row nine. It's now two thousand two hundred and ten, because it includes the phone bill. Click it and you'll see the formula grew by itself, to SUM of B4 to B8. That's because we inserted the row inside the range. Excel stretched the formula for us.

  • Click A8
  • Press Shift+Space
  • Press Ctrl+Minus

The total is back in row eight, and it now reads two thousand one hundred and thirty dollars. The formula shrank to SUM of B4 to B7.

  • Click B3
  • Press Ctrl+Space
  • Press Ctrl+Shift+Plus
  • In B3 type Type
  • Select 4:7
  • Click C8

Column widths and row heights#

Working with worksheets#

  • Go to the Practice sheet

  • Rename the sheet to My notes

  • In A1 type Things I learned today

  • Starting in A3, type these rows (one value per cell, moving right):

    | Shopping total | =References!D8 | |---|---|

It shows thirty seven point two, the grand total from our shopping list on the References sheet. If the shopping list changes, this cell updates too.

  • Go to the Welcome sheet
  • Go to the Formatting sheet
  • Press Ctrl+Pagedown
  • Press Ctrl+Pagedown
  • Press Ctrl+Pageup
  • Give the sheet tab a colour

  • Starting in A5, type these rows (one value per cell, moving right):

    | Favourite shortcut | Ctrl+Z | |---|---|

Common errors, made on purpose#

Sheet: Errors

  • Starting in A3, type these rows (one value per cell, moving right):

    | Total sales | Orders | Average order | |---|---|---| | 1200 | 0 | =A4/B4 |

  • Format A3:C4: AutoFit columns

Hash DIV slash zero, exclamation mark. That's the divide by zero error. You can't divide anything by zero, not in maths and not in Excel.

  • In B4 type 8

The error disappears, and the average order is one hundred and fifty dollars. In video two we'll learn IFERROR, which lets you show a friendly message instead of the error.

  • In A7 type Prices

Hash NAME question mark. This means Excel doesn't recognise a name in your formula. There's no function called SUMM. It almost always means a spelling mistake in a function name, or text that's missing its quotation marks.

  • In A11 type the formula =SUM(A8:A10)

Now it works. Four fifty plus three plus two fifty is ten. Tip: when you type a function name, watch the list that pops up. If your function doesn't appear in it, you've probably misspelled it.

  • Starting in C7, type these rows (one value per cell, moving right):

    | Quantity | Doubled | |---|---| | ten | =C8*2 |

Hash VALUE exclamation mark. Excel can't multiply a word by a number. The word ten means nothing to Excel, it's just text. Notice that it's sitting on the left of its cell, our clue that it's text.

  • In C8 type 10

Now the number sits on the right, and doubled shows twenty. The VALUE error often appears when data comes from another system with numbers stored as text, or with extra spaces.

  • In F3 type Due date
  • In F4 type 3-Nov-2026
  • Format F4: number format long date
  • Format F:F: column width 4

A row of hash signs. All this means is: the column is too narrow to show this number or date. Nothing is broken. The date is still safely in the cell.

  • AutoFit the width of column F:F (double-click its right edge)

And there's the full date again: Tuesday, the third of November, twenty twenty six.

Reading error messages#

Your first chart#

Sheet: Chart

  • In A1 type Website visitors

  • Starting in A3, type these rows (one value per cell, moving right):

    | Month | Visitors | |---|---|

  • Starting in the next empty row, type these rows (one value per cell, moving right):

    | Jan | 1200 | |---|---| | Feb | 1350 | | Mar | 1100 | | Apr | 1600 | | May | 1850 | | Jun | 2100 |

  • Select A3:B9 and insert a column chart titled “Website visitors by month”, no legend

There's our first chart. Months along the bottom, visitors up the side, and one column per month. You can see straight away that March was a dip, and then visitors grew steadily to a high of twenty one hundred in June.

  • In B7 type 2400

The April column shot up instantly, and it's now the tallest bar on the chart. Just like formulas, charts update whenever the data changes.

Choosing a chart type#

Saving your work#

AutoSave and file formats#

  • Press Ctrl+S

No dialog box, no fuss. Our work is safe.

Project: a personal monthly budget tracker#

Sheet: Budget

Planning the layout#

  • In A1 type Monthly Budget, November 2026
  • Format A1: bold, font size 16, font colour Dark Blue
  • Starting in A3, type these rows (one value per cell, moving right):

    | Income | Amount | |---|---| | Salary | 4800 | | Freelance work | 650 | | Total income | =SUM(B4:B5) |

Our total income for the month is five thousand four hundred and fifty dollars.

  • Starting in A8, type these rows (one value per cell, moving right):

    | Category | Budget | Actual | Difference | % of income | |---|---|---|---|---| | Rent | 1800 | 1800 | | | | Groceries | 600 | 685 | | | | Transport | 250 | 210 | | | | Utilities | 220 | 238 | | |

  • Starting in the next empty row, type these rows (one value per cell, moving right):

    | Phone & internet | 110 | 110 | |---|---|---| | Eating out | 200 | 290 | | Entertainment | 150 | 120 | | Savings | 800 | 800 | | Total | =SUM(B9:B16) | =SUM(C9:C16) |

We planned to spend four thousand one hundred and thirty dollars, and we actually spent four thousand two hundred and fifty three.

  • In D9 type the formula =B9-C9
  • Fill D9:D17 down (drag the fill handle, or select it and press Ctrl+D)

Rent, phone and savings are exactly on budget, at zero. Groceries is minus eighty five, utilities minus eighteen, and eating out minus ninety. Those are the categories where we overspent. Transport is forty dollars under budget, and entertainment thirty under. Overall, the total difference is minus one hundred and twenty three. We spent one hundred and twenty three dollars more than we planned.

Percent of income with an absolute reference#

  • In E9 type the formula =C9/B6
  • Press F4
  • Press Enter
  • Fill E9:E17 down (drag the fill handle, or select it and press Ctrl+D)
  • Starting in A19, type these rows (one value per cell, moving right):

    | Money left over | =B6-C17 | |---|---|

Five thousand four hundred and fifty, minus four thousand two hundred and fifty three, leaves one thousand one hundred and ninety seven dollars. And that's on top of the eight hundred we saved.

  • Starting in A20, type these rows (one value per cell, moving right):

    | Daily spending | =C17/30 | |---|---| | Biggest expense | =MAX(C9:C16) |

We're spending about one hundred and forty one dollars seventy seven a day, including rent and savings. And the biggest single expense is eighteen hundred dollars, our rent.

  • Starting in A22, type these rows (one value per cell, moving right):

    | Savings rate | =C16/B6 | |---|---|

  • Format B22: number format percent, more decimals (1)

Our savings rate is fourteen point seven percent. Many money experts suggest aiming for somewhere between ten and twenty percent, so this month we are right on track.

Using the tracker every month#

Formatting the budget#

  • Format B4:B6: number format currency
  • Format B9:D17: number format currency
  • Format B19:B21: number format currency
  • Format E9:E17: number format percent
  • Format E9:E17: more decimals (1)

Now we can read the shares. Rent takes thirty three point zero percent of our income, groceries twelve point six, transport three point nine, utilities four point four, phone and internet two point zero, eating out five point three, entertainment two point two, and savings fourteen point seven percent. Altogether, our spending, including savings, uses seventy eight point zero percent of our income.

  • Format A3:B3: bold, font colour White, Background 1, fill Blue, Accent 1
  • Format A8:E8: bold, font colour White, Background 1, fill Blue, Accent 1, align center
  • Format A6:B6: bold, thick bottom border
  • Format A17:E17: bold, fill Gray, Accent 3
  • Format A19:B19: bold, font colour White, Background 1, fill Green, Accent 6
  • Format A8:E17: all border
  • Format A:A: column width 18
  • Format B8:E17: AutoFit columns

Highlighting overspending#

  • Conditional formatting on D9:D16: highlight cells less than 0

Three cells light up red: groceries at minus eighty five, utilities at minus eighteen, and eating out at minus ninety. In one glance, you can see exactly where your money leaked.

Charting actual spending#

  • Select A8:A16,C8:C16 and insert a column chart titled “Actual spending, November”, no legend

Rent towers over everything at eighteen hundred dollars, followed by savings at eight hundred and groceries at six eighty five. The smaller categories, like phone and internet, and entertainment, are just little columns. This is a very honest picture. Most people are surprised by how much the big fixed costs dominate their spending.

What the budget tells us#

Freezing the headings#

  • Click A9 and choose View → Freeze Panes
  • Click B19

Testing the budget#

  • In C14 type 180

So much changed from that one number. The eating out difference is now plus twenty, so the red highlight disappeared. Its share of income dropped to three point three percent, and the chart's eating out column got shorter. Total actual spending fell to four thousand one hundred and forty three dollars, and the total difference is now just minus thirteen. We're only thirteen dollars over our overall plan, because of groceries and utilities. Total spending is now seventy six point zero percent of income, and the money left over rose to one thousand three hundred and seven dollars. Daily spending dropped to one hundred and thirty eight dollars ten.

  • Press Ctrl+S

Recap and what's next#

Shortcuts to remember#

Coming up in video two#

Found this useful?

All lessons, notebooks and datasets here are free. If they helped you, a coffee keeps new lessons coming.