Microsoft Office Specialist: Excel Exam Preparation: Lessons 2026

Lesson 1: Manage worksheets and workbooks

About the MO-210 exam

Passing exam MO-210: Microsoft Excel (Microsoft 365 Apps) earns the Microsoft Office Specialist: Excel Associate (Microsoft 365 Apps) certification. The exam is hands-on: Certiport describes MOS exams as performance-based, "live-in-the-application" testing. You do real tasks in Excel instead of only choosing answers.

• Time: 50 minutes. This is the timed part only; check-in and the tutorial take extra seat time.
• Length: Certiport lists 33–37 questions for the Excel exam.
• Score: reported on a 1–1000 scale; Certiport says the passing value is always equated to 700.
• Retakes: wait 24 hours after a first fail, then 2 days (48 hours) before each later retake.
• Experience: Microsoft suggests about 150 hours of instruction and hands-on practice.

Import data

To bring in a text or CSV file, use Data > From Text/CSV. A preview opens where you check the delimiter (such as a comma), then choose Load (or Transform Data to clean it first). To import a table from a website, use Data > From Web and enter the web address.

Navigate a workbook

• The Name Box (left of the formula bar) lists named ranges. Pick one to select it. Go To (F5 or Ctrl+G) does the same.
• Find (Ctrl+F) searches cell contents, not names.
• Insert > Link (Ctrl+K) adds a hyperlink to a web page, a file or a place in the workbook. Right-click a link cell and choose Remove Hyperlink to keep the text but remove the link.

Views and windows

Freeze Panes (View tab) locks the rows above and the columns to the left of the cell you select. Select C3 and you lock rows 1–2 and columns A–B. Use Freeze Top Row for headings and Unfreeze Panes to undo it. Freezing only affects the screen.

• Normal, Page Layout and Page Break Preview are in View > Workbook Views. Page Break Preview shows blue page-break lines you can drag.
• New Window, Arrange All and Split are in the View tab's Window group.
• Formulas > Show Formulas (Ctrl+`) shows formulas instead of results.
• Right-click a ribbon command and choose Add to Quick Access Toolbar to keep it handy.
• File > Info shows document properties such as Title, Tags and Author, which you can edit.

Page setup and printing

The Page Layout tab holds orientation, margins, size and scaling. Print Titles sets Rows to repeat at top, so a heading row prints on every page. Print Area > Set Print Area prints only the selected cells; it is saved with the workbook. If you select several ranges with Ctrl, each print area prints on its own page.

Add headers and footers with Insert > Header & Footer. Codes show live information: &[Page] is the page number, &[Pages] the total pages, &[Date] today's date, &[File] the file name and &[Tab] the sheet name. "Page &[Page] of &[Pages]" prints as "Page 2 of 5".

Share and distribute

• File > Save As or Export: .xlsx is the normal workbook; PDF keeps the layout and is hard to edit; CSV saves only the active sheet as plain text, with no formatting.
• Document Inspector: File > Info > Check for Issues > Inspect Document. It finds comments, document properties, personal information and other hidden content. Run it on a copy, because removed items may not come back, even with Undo.
• Threaded comments (Review > New Comment) have a Reply box for discussion. Notes (Review > Notes > New Note) are yellow sticky annotations with no Reply box.

Themenquiz 1: Manage worksheets and workbooks

Lesson 2: Manage data cells and ranges

Paste Special

Copy cells, then use Home > Paste > Paste Special.

Example: to raise prices by 5%, type 1.05 in a spare cell, copy it, select the prices and choose Paste Special > Multiply. A price of 20.00 becomes 21.00.

Fill and insert

• Drag the fill handle (the small square at the bottom-right of a cell) to use Auto Fill. Mon continues Tue, Wed…; Jan continues Feb, Mar….
• Select two numbers (such as 5 and 10) and drag to continue the step: 15, 20, 25….
• Right-click > Insert or Delete to add or remove whole rows, columns, or single cells. For cells, choose to shift the other cells down or right (insert) or up or left (delete).
• =RANDBETWEEN(bottom, top) returns a random whole number, and both limits can appear. It changes every time the sheet recalculates (for example, F9). To keep the numbers, copy them and paste as Values.
• =SEQUENCE(rows, [columns], [start], [step]) lists numbers. Missing optional arguments are 1. =SEQUENCE(3,1,100,10) gives 100, 110, 120 down a column.

Format cells and ranges

• Merge & Center joins cells. Only the upper-left value is kept. Use Unmerge Cells to split them again.
• Alignment tools: left, centre and right align, Orientation (angled or vertical text), Increase Indent and Wrap Text (long text on several lines in one cell).
• Format Painter: click once to copy formatting to one place; double-click to apply it to many places, then press Esc.
• Number formats (Currency, Accounting, Percentage, Date) are on the Home tab. Ctrl+1 opens the Format Cells dialog for more choices.
• Cell Styles (Home tab) apply ready-made formats such as Heading 1, Good or Total.
• Home > Clear: Clear Formats removes formatting only; Clear Contents (or Delete) removes values only; Clear All removes both.
• Group worksheets: Ctrl+click sheet tabs. [Group] appears in the title bar and formatting you apply changes every grouped sheet. Click another tab to ungroup.

Named ranges

A name makes formulas easier to read: =SUM(JanCosts) instead of =SUM(B3:B6). Create one by selecting cells and typing in the Name Box, with Formulas > Define Name, or with Formulas > Create from Selection, which names the cells below (or beside) each label in the top row (or left column). Name Manager lists, edits and deletes names. A name must start with a letter, underscore or backslash, cannot contain spaces (use Tax_Rate) and cannot look like a cell reference such as B12.

Summarize data visually

A sparkline is a tiny chart inside one cell. The types are Line, Column and Win/Loss. Use Insert > Sparklines, choose the data range and the location cell, then fill down for other rows. To remove one, select it and use Clear on the Sparkline tab.

Home > Conditional Formatting formats cells by their values: Highlight Cells Rules (Greater Than, Text that Contains, Duplicate Values), Top/Bottom Rules (Top 10 Items, where you can change 10 to any number, or Above Average), Data Bars, Color Scales and Icon Sets. To remove it, use Clear Rules > Clear Rules from Selected Cells or from Entire Sheet.

Themenquiz 2: Manage data cells and ranges

Lesson 3: Manage tables and table data

Create and format a table

Click in your data and press Ctrl+T (or Insert > Table). Keep My table has headers ticked when the first row holds column names; if you clear it, Excel adds headings such as Column1. Excel names the table Table1, Table2 and so on. Rename it in the Table Name box on the Table Design tab, for example Shifts.

Pick a look from Table Styles. Table Style Options turn parts on or off: Header Row, Total Row, Banded Rows (shading on every second row), First Column, Last Column, Banded Columns and Filter Button.

Change a table

• Type in the row just below the table (or the column just to the right) and the table grows to include it.
• Press Tab in the last cell of the last row to add a new row.
• Right-click a cell > Insert or Delete to add or remove table rows and columns, or drag the sizing handle in the bottom-right corner.
• Table Design > Convert to Range turns the table back into normal cells. The formatting stays, and structured references in formulas change to normal cell references.

Total row

Tick Total Row on the Table Design tab. A row appears at the bottom with a drop-down in each cell: Sum, Average, Count, Max, Min and more. These choices use the SUBTOTAL function, for example =SUBTOTAL(109,[Hours]) for a sum. SUBTOTAL with 109 ignores hidden rows, so when you filter the table, the total shows only the visible rows.

Sort and filter

To sort by more than one column, use Data > Sort, choose the first column and order, then click Add Level for the next one. Example: Site A to Z, then Hours largest to smallest. The first level is the main order; the second level sorts rows that share the same site.

Tables have filter buttons in the header row (or use Data > Filter). Choose values to show, or use Number Filters and Text Filters such as Greater Than or Contains. Rows that do not match are hidden, not deleted. Filters on two columns must both be true. Data > Clear removes all filter conditions at once.

Themenquiz 3: Manage tables and table data

Lesson 4: Perform operations by using formulas and functions

Cell references

A relative reference (A1) changes when you copy a formula: =B2*C2 copied one row down becomes =B3*C3. An absolute reference ($A$1) never changes, so use it for a single rate cell: =B4*$B$1. A mixed reference locks only the column ($A1) or only the row (A$1). Click a reference in the formula and press F4 to cycle: $A$1, A$1, $A1, A1.

In a table, formulas can use structured references. Shifts[Hours] means all data cells in the Hours column of the table Shifts (not the header or total row). [@Hours] means the Hours cell in the same row as the formula, so =[@Hours]*[@Rate] works out pay row by row.

Calculate data

Example: =IF(C2>=B2,"Met","Short") shows Met when Actual (C2) is at least the Target (B2). Use >= when an equal value should pass. Put text in double quotation marks.

SORT and UNIQUE

=SORT(array, [sort_index], [sort_order], [by_col]) returns a sorted copy of the data. sort_index is the column number to sort by (default 1). sort_order is 1 for ascending (default) or -1 for descending. =SORT(A2:B6,2,-1) sorts by the second column, highest first.

=UNIQUE(array, [by_col], [exactly_once]) lists each different value once. With exactly_once set to TRUE, it lists only values that appear exactly one time.

SORT and UNIQUE spill: you type the formula in one cell and the results fill the cells below. If a cell in the spill area is not empty, the formula shows #SPILL! until you clear that cell.

Format and change text

• LEFT(text, n) and RIGHT(text, n) take characters from the start or end. MID(text, start, n) takes n characters starting at position start: =MID("YYC-3307-S",5,4) is 3307.
• UPPER and LOWER change the case. LEN counts every character, including spaces: "Elbow Park" has 10.
• CONCAT joins text: =CONCAT(A2," ",B2). The & sign does the same: =A2&" "&B2.
• TEXTJOIN(delimiter, ignore_empty, text1, …) joins a range with a separator. With ignore_empty TRUE, empty cells are skipped, so you do not get double commas.

Themenquiz 4: Perform operations by using formulas and functions

Lesson 5: Manage charts

Create charts and chart sheets

Select the data, including the labels, and use Insert > Recommended Charts to preview chart types that suit it, or pick a type yourself. Column and bar charts compare amounts. Line charts show a trend over time. A pie chart shows the parts of one whole and uses only one data series.

A chart can sit on a worksheet or on a chart sheet, a separate sheet that holds only the chart. To move a chart there, select it and use Chart Design > Move Chart > New sheet, then type a name. Pressing F11 with data selected creates a chart on a new chart sheet in one step; Alt+F1 puts it on the current sheet.

Change the data

Excel puts the larger group on the horizontal axis. With 5 sites in rows and 12 months in columns, the months go across the axis and each site becomes a series. Chart Design > Switch Row/Column (Data group) swaps this. The worksheet data does not change.

To add a series, use Chart Design > Select Data > Add and choose the series name and values. You can also drag the coloured outline around the source data to include a new row or column.

Chart elements, layouts and styles

• The Chart Elements button (+) beside a selected chart turns on or off Chart Title, Axis Titles, Data Labels, Gridlines, Legend and more.
• The legend shows which colour is which series.
• Chart Styles (paintbrush) changes the look; Chart Filters (funnel) hides series or categories.
• Chart Design > Quick Layout applies a ready-made set of elements; Change Colors picks a colour set.
• Chart Design > Change Chart Type switches the type.

Alternative text

Alt text is read aloud by screen readers for people who cannot see the chart. Right-click the chart and choose the Alt Text command, then type a short description. Good alt text names the chart type and its key point, for example: "Line chart of monthly sales in 2026; sales rose from March and peaked in August."

Themenquiz 5: Manage charts