Excel for Beginners Course: Lessons 2026

Lesson 1: Workbooks, worksheets, cells and ranges

An Excel file is called a workbook. Inside a workbook are one or more worksheets (also called sheets). Each sheet has a tab at the bottom of the window. A new blank workbook in current versions of Excel starts with 1 sheet, and you can add more at any time.

This course describes Excel for Microsoft 365 on Windows and Excel for the web. Most menus are the same on a Mac, but some keyboard shortcuts are different. Keyboard shortcuts in these lessons are the Windows ones.

Cells, columns and rows

A worksheet is a grid. Columns have letters (A, B, C…) across the top. Rows have numbers (1, 2, 3…) down the left side. One worksheet has 1,048,576 rows and 16,384 columns. The box where a column and a row meet is a cell.

Every cell has a name made of its column letter and then its row number. The cell in column C, row 4 is C4. The letter always comes first.

	A	B	C
1	Item	Category	Qty
2	Safety vests	PPE	42
3	Hard hats	PPE	15
4	Radios	Electronics	6

"Radios" is in cell A4 and its quantity, 6, is in cell C4.

Ranges

A range is a group of cells. Write the first cell, a colon (:) and the last cell. To count the cells in a block, multiply the number of columns by the number of rows.

Range	含义	Cells
B2:B6	Column B, rows 2 to 6	5
B15:E15	Row 15, columns B to E	4
A1:C4	Columns A to C, rows 1 to 4 (3 × 4)	12
5:10	Every cell in rows 5 to 10	whole rows
H:J	Every cell in columns H to J	whole columns

Creating workbooks and worksheets

New workbook: open Excel and select Blank workbook, or press Ctrl+N.
New sheet: select the New Sheet (+) icon next to the sheet tabs at the bottom, or press Shift+F11.
Rename a sheet: double-click its tab (or right-click it and choose Rename), type the name and press Enter.
Delete a sheet: right-click its tab and choose Delete.

Sheet names have rules. A name can be up to 31 characters and can't be blank. It can't contain these characters: / \ ? * : [ ]. You also can't use the name History, because Excel keeps it for its own use. So "Week 1-2" is fine, but "Week 1/2" is not.

Using cells on another sheet

A formula can use cells on a different sheet. Write the sheet name, an exclamation point (!) and then the cells. For example, Sales!B2:B9 means cells B2 to B9 on the sheet named Sales. If the sheet name has a space, put it in single quotation marks: 'March Hours'!A1.

专题测验1：Workbooks, worksheets, cells and ranges

Lesson 2: Entering, editing and formatting data

Typing and moving

Select a cell and type. Press Enter to confirm and move down one cell, or press Tab to confirm and move right one cell. Tab is handy for filling in a row.

To change part of a cell, enter Edit mode: double-click the cell, press F2, or click in the formula bar above the grid.
Press Esc to cancel your edits before you press Enter or Tab.
Press Alt+Enter while editing to start a new line inside the same cell.
To put the same entry in many cells, select them all, type once and press Ctrl+Enter. The cells don't have to be next to each other.
Press Ctrl+; (semicolon) to enter today's date. When you type a date yourself, separate the parts with a slash or a hyphen.

When a cell shows #####

If a number or date is too wide for its column, Excel shows ##### instead. The data is not lost. Widen the column: drag the right border of the column heading, or double-click it to fit the contents. A formula that returns a negative date or time can also show #####.

Number formats

A number format changes how a value looks. Choose one from the Number group on the Home tab, or press Ctrl+1 to open the Format Cells dialog box.

Format	What it does
General	The default. Large numbers of 12 or more digits may show in scientific notation (for example 1.23E+12).
数字	Shows numbers with the decimal places you choose.
Currency	Shows money with a currency symbol next to each number.
Accounting	Also for money, but lines up the currency symbols and decimal points in a column.
Date / Time	Shows date and time values in the style you pick.
Percentage	Multiplies the value by 100 and adds a % sign. 0.25 shows as 25%.
Text	Shows the entry exactly as typed, even numbers. Good for IDs like 00457, which keep their leading zeros.

The format does not change the value. Excel always calculates with the value stored in the cell. You can see that stored value in the formula bar. The Increase Decimal and Decrease Decimal buttons on the Home tab change how many decimals you see, not the value.

	A	B
1	Item	Price
2	Toner	19.756

If B2 is set to show 2 decimal places it displays 19.76, but =B2*2 returns 39.512, because Excel uses the stored value 19.756.

Wrapping text

Long notes can spill into the next cell. Select the cells and choose Wrap Text in the Alignment group on the Home tab. The text then fits the column width, and it rewraps if you change the width.

专题测验2：Entering, editing and formatting data

Lesson 3: Formulas and basic functions

A formula always starts with an equal sign (=). After it you can use numbers, cell references, operators and functions. Excel shows the result in the cell and the formula in the formula bar.

Operator	Meaning	示例
+	Add	=B2+C2
-	Subtract	=B2-C2
*	Multiply	=B2*C2
/	Divide	=B2/C2
^	Power	=2^3
&	Join text	=A2&" "&B2
= > < >= <= <>	Compare (gives TRUE or FALSE)	=B2>=40

Excel follows an order of operations. It works out percent (%) and powers (^) before multiplication and division, and multiplication and division before addition and subtraction. Use parentheses to change the order. =4+6*2 returns 16, but =(4+6)*2 returns 20.

SUM and AutoSum

=SUM(B2:B5) adds the numbers in B2 to B5. You can list up to 255 items, such as =SUM(B2:B5,D2:D5). SUM ignores text in the cells. If you add cells one by one with + (like =A2+B2+C2) and one of them holds text, Excel returns the #VALUE! error. SUM also adjusts when you insert rows inside its range.

AutoSum writes a SUM formula for you. Select the empty cell below (or beside) your numbers, choose AutoSum on the Home tab or the Formulas tab, and press Enter. The keyboard shortcut is Alt+=.

Other basic functions

Function	What it returns	Good to know
AVERAGE(range)	The mean of the numbers	Ignores empty cells and text, but cells with 0 are included
MIN(range) / MAX(range)	The smallest / largest number	Ignores empty cells and text
COUNT(range)	How many cells hold numbers (or dates)	Does not count text or empty cells
COUNTA(range)	How many cells are not empty	Counts text, numbers and errors
COUNTBLANK(range)	How many cells are empty	Cells with 0 are not counted as blank
COUNTIF(range, criteria)	How many cells match a rule	Put text and rules in quotes: "Absent", ">55". Not case sensitive

	A	B	C	D	E	F
1	Employee	Mon	Tue	Wed	Thu	Fri
2	Ana	8	7.5	8	0	6
3	Chen	7	7		7.5	8

=AVERAGE(B2:F2) returns 5.9 (29.5 ÷ 5, the 0 counts). =AVERAGE(B3:F3) returns 7.375 (29.5 ÷ 4, the empty cell is ignored). =COUNT(B3:F3) returns 4.

ROUND

=ROUND(number, num_digits) rounds a number. A positive num_digits sets the decimal places: =ROUND(3.14159,2) gives 3.14. Zero rounds to a whole number. A negative num_digits rounds to the left of the decimal point: =ROUND(1234.567,-2) gives 1200.

IF

=IF(logical_test, value_if_true, value_if_false) checks a condition and returns one of two results. Text in a formula must be inside double quotation marks. For example, =IF(B2>=32,"Full","Part") shows Full when B2 is 32 or more, and Part when it is less.

Excel shows #DIV/0! when a number is divided by zero or by an empty cell. =IF(C2,B2/C2,0) divides only when C2 is not zero or blank.

Practical exercise: count present and absent in an attendance sheet

Copy this attendance sheet into Excel. P means present and A means absent. Divya's Thursday was not recorded.

	A	B	C	D	E	F	G	H	I
1	Name	Mon	Tue	Wed	Thu	Fri	Present	Absent	% present
2	Amrit	P	P	A	P	P
3	Bella	P	A	A	P	P
4	Carlos	P	P	P	P	P
5	Divya	A	P	P		P
6	Erin	P	P	P	A	P
7	Farid	P	A	P	P	A

In G2, write a formula that counts Amrit's present days. Then fill it down to G7.
显示答案
=COUNTIF(B2:F2,"P") returns 4 for Amrit. Carlos, in row 4, gets 5.
In H2, count Amrit's absent days and fill down to H7.
显示答案
=COUNTIF(B2:F2,"A") returns 1. Bella, in row 3, gets 2.
Why do Divya's Present and Absent add up to 4, not 5?
显示答案
Her Thursday cell (E5) is empty, so COUNTIF counts it as neither P nor A. =COUNTBLANK(B2:F7) returns 1, showing one day is missing.
Write one formula that counts all absences for the whole team.
显示答案
=COUNTIF(B2:F7,"A") (or =SUM(H2:H7)) returns 7.
In I2, show Amrit's attendance as a percentage of the 5 days, and fill down.
显示答案
=G2/5, then apply the Percentage format. Amrit shows 80%; Divya shows 60%.
Which employees had the most absences?
显示答案
Bella and Farid, with 2 each. =MAX(H2:H7) returns 2.

Practical exercise: total the hours in a weekly shift schedule

Fill in the Total column and the Daily total row of this schedule with SUM.

	A	B	C	D	E	F	G	H	I
1	Name	Mon	Tue	Wed	Thu	Fri	Sat	Sun	Total
2	Priya	8	8	0	8	8	0	0
3	Tom	0	0	12	12	12	8	0
4	Sam	4	4	4	4	4	8	8
5	Lee	0	10	10	10	10	0	0
6	Daily total

In I2, total Priya's hours for the week. Then fill down to I5.
显示答案
=SUM(B2:H2). The totals are Priya 32, Tom 44, Sam 36, Lee 40.
In B6, total the hours for Monday. Then fill right to H6.
显示答案
=SUM(B2:B5). The daily totals from Monday to Sunday are 12, 22, 26, 34, 34, 16, 8.
In I6, find the total hours for the whole team. Check it two ways.
显示答案
=SUM(I2:I5) and =SUM(B6:H6) both return 152. If they don't match, a formula is wrong.
In J2, write a formula that shows "Over 40" when a person's total is more than 40, and nothing otherwise.
显示答案
=IF(I2>40,"Over 40",""). Only Tom shows "Over 40" (44 hours).

专题测验3：Formulas and basic functions

Lesson 4: Cell references and AutoFill

Relative references

A normal reference like B2 is relative. When you copy the formula, the reference moves the same distance. If D2 holds =B2*C2 and you copy it to D3, it becomes =B3*C3. That is why one formula can be filled down a whole list.

Absolute and mixed references

Sometimes every formula must point to the same cell, such as a tax rate or a commission rate. Put a dollar sign ($) before the column letter and the row number: $E$2. This is an absolute reference. It never changes when copied.

A mixed reference locks only one part. $A2 locks column A but lets the row change. B$1 locks row 1 but lets the column change. To switch types, select the reference in the formula bar and press F4.

Reference typed	Copied 2 rows down and 2 columns right	Type
$A$1	$A$1	Absolute
A$1	C$1	Mixed (row locked)
$A1	$A3	Mixed (column locked)
A1	C3	Relative

	A	B	C	E
1	Rep	Sales	Commission	Rate
2	Kai	12000		0.04
3	Lina	9500
4	Moe	15250

If C2 holds =B2*E2 and you copy it down, C3 becomes =B3*E3. E3 is empty, so the result is 0. Use =B2*$E$2 instead.

The fill handle and AutoFill

The fill handle sits at the lower-right corner of the selected cell. Drag it down, up or across to fill nearby cells. When you let go, an Auto Fill Options button appears so you can change how the cells were filled.

Copy a formula: select the formula cell and drag the fill handle. Relative references change for each row.
Repeat a value: type it in one cell only (for example 5) and drag. You get 5, 5, 5…
Build a series: type the first two values (1 and 2, or 2 and 4), select both, then drag. Excel continues the pattern (3, 4, 5… or 6, 8, 10…).
Keyboard: select the formula cell and the cells below and press Ctrl+D to fill down; press Ctrl+R to fill right.
Ribbon: Home > Fill, then choose Down, Right, Up or Left.

Practical exercise: build an invoice with GST

Build this office-supplies invoice. The GST rate is in D8 as 0.05 (5%). This is an example only: sales tax rates differ by province and territory. Some charge 5% GST only, such as Alberta, while others charge HST, such as Ontario at 13%. Always check the current rate that applies to your sale.

	A	B	C	D
1	Item	Qty	Unit price	Line total
2	Printer paper (case)	3	42.50
3	Toner cartridge	2	89.99
4	Stapler	4	12.75
5	Binder clips (box)	6	3.49
6
7			Subtotal
8			GST rate	0.05
9			GST
10			Total

In D2, calculate the line total for the printer paper. Then fill down to D5.
显示答案
=B2*C2. The line totals are $127.50, $179.98, $51.00, $20.94.
In D7, calculate the subtotal.
显示答案
=SUM(D2:D5) returns $379.42.
In D9, calculate the GST, rounded to the cent.
显示答案
=ROUND(D7*D8,2) returns $18.97. Without ROUND the result is 18.971, which is not a whole number of cents.
In D10, calculate the invoice total.
显示答案
=D7+D9 returns $398.39.
You add a GST column in E and put =D2*D8 in E2, then fill it down. Why are E3 to E5 wrong, and how do you fix it?
显示答案
D8 is a relative reference, so E3 becomes =D3*D9, which points at the wrong cell. Use =D2*$D$8 so every row uses the rate in D8.

专题测验4：Cell references and AutoFill

Lesson 5: Sorting, filtering and tables

Sorting

Click a cell in the column you want to sort, then go to the Data tab, Sort & Filter group. Choose the command that matches the data:

Data in the column	Commands
Text	Sort A to Z, Sort Z to A
Numbers	Sort Smallest to Largest, Sort Largest to Smallest
Dates and times	Sort Oldest to Newest, Sort Newest to Oldest

To sort by more than one column, open the Sort dialog box. Set the first column, then select Add Level for the next one. Levels higher in the list sort first. Tick My data has headers so your heading row stays at the top. You can also sort on Cell Color.

If you select only part of a list and sort, Excel may ask whether to Expand the selection or Continue with the current selection. Choose Expand the selection. Sorting one column alone can separate values from the rest of their row, which mixes up your data.

Filtering

Select a cell in your list and choose Data > Filter. An arrow appears in each heading. Select an arrow to choose what to show:

Untick (Select All), tick the values you want (for example PPE) and select OK.
Use Number Filters or Text Filters for rules, such as Between 10 and 70.
Rows that don't match are hidden for now, not deleted. After filtering, the arrow changes to a Filter icon. Select it to change or clear the filter and show every row again.

Excel tables

An Excel table makes a list easier to manage. Click a cell in the list, choose Home > Format as Table, pick a style, check that the range is right, and tick My table has headers if your first row holds column names. You can also use Insert > Table or press Ctrl+T.

The header row has filter arrows turned on.
Banded rows shade every other row so lines are easier to read.
Type a formula in one cell of an empty column and Excel fills it into the whole column. This is a calculated column.
Type in the row just below the table and the table grows to include it. To change the size, use Table Design > Resize Table.
The Table Design tab appears when you click inside the table. Tick Total Row to add totals at the bottom. Pick Sum, Average and more from each total cell's list.

The Total Row uses the SUBTOTAL function, which skips rows hidden by a filter. So if you filter a shift table to show only Night shifts, the total adds only the Night hours.

Freeze panes

In a long list the headings scroll out of view. On the View tab, choose Freeze Panes, then:

Freeze Top Row to keep row 1 visible.
Freeze First Column to keep column A visible.
Freeze Panes to lock rows and columns together. First select the cell below the rows and to the right of the columns you want to keep. Select C3 to keep rows 1–2 and columns A–B.
To undo it, choose Unfreeze Panes on the View tab.

Practical exercise: sort and filter an inventory list

Turn this supply-room stock list into a table and add a Value column.

	A	B	C	D	E
1	Item	Category	Qty	Unit cost	Value
2	Safety vests	PPE	42	12.50
3	Hard hats	PPE	15	24.00
4	Flashlights	Tools	8	18.75
5	Radios	Electronics	6	149.00
6	Batteries (AA)	Electronics	120	0.85
7	Gloves	PPE	64	4.20
8	Traffic cones	Site	30	16.40

Format A1:E8 as a table with headers. In E2, type a formula for the stock value of the first item. What happens?
显示答案
Type =C2*D2 (Qty × Unit cost). Excel fills the formula into the whole Value column. Safety vests are worth $525.00.
Turn on the Total Row and set the Value total to Sum. What is the total stock value?
显示答案
$2,791.80.
Filter Category to show only PPE. What does the Value total show now?
显示答案
$1,153.80. The Total Row uses SUBTOTAL, so it adds only the visible rows (Safety vests, Hard hats and Gloves).
Clear the filter, then sort Value Largest to Smallest. Which item is first?
显示答案
Radios, worth $894.00.

专题测验5：Sorting, filtering and tables

Lesson 6: Charts, printing and sharing

Making a chart

Select the data, including the headings.
On the Insert tab, choose Recommended Charts.
Click a chart to preview it, or choose All Charts to see every type.
Select the chart you want and select OK.

Near the upper-right corner of a selected chart are three buttons: Chart Elements (add axis titles or data labels), Chart Styles (change the look) and Chart Filters (change which data shows). The Chart Design and Format tabs have more options.

Chart type	Use it when
Column	Comparing categories. Categories run along the horizontal axis and values up the vertical axis.
Bar	Comparing categories that have long labels. Categories run down the vertical axis.
Line	Showing a trend over time at equal intervals, such as sales by month.
Pie	Showing parts of a whole: one data series, no negative values and no more than seven categories.

	A	B
1	Month	Sales
2	Jan	12400
3	Feb	11850
4	Mar	13920
5	Apr	15100
6	May	14480
7	Jun	16230

Six months of sales. A column chart of A1:B7 shows 6 columns (row 1 is the headings). A line chart shows the trend.

Printing

Choose File > Print to see a preview before you print. These settings on the Page Layout tab help a sheet print well:

Print Area: select the cells, then Print Area > Set Print Area. Only those cells print. The print area is saved with the workbook. Use Clear Print Area to print the whole sheet again.
Print Titles: on the Sheet tab of the dialog, enter $1:$1 in Rows to repeat at top so the headings in row 1 print on every page.
Orientation: if a sheet has many columns, switch from portrait to landscape.
Scale to Fit: set Width to 1 page and Height to Automatic. All columns fit across one page, but rows may continue onto more pages.

In File > Print you can also pick a scaling option such as Fit Sheet on One Page, or No Scaling to turn it off. Be careful: Excel shrinks the data to fit, so a big sheet may print too small to read.

Sharing and saving

To share a workbook from Excel, select Share, set the permissions (allow editing, or view only), add people, type a message if you like and select Send. To work on a file at the same time as others (co-authoring), save it to OneDrive first. Select your initials in the upper-right corner to see who else is working in the file.

AutoSave is available to Microsoft 365 subscribers. It saves your file every few seconds and is on by default for files stored on OneDrive, OneDrive for work or school, or SharePoint Online. On Windows the AutoSave switch is in the top-left corner. If someone makes changes you don't want, open Version History from the file name at the top of the window and restore an earlier version.

专题测验6：Charts, printing and sharing