Excel Expert work begins with a reliable workbook: links point to the right source, calculations update when expected, and people can edit the cells intended for them. From there, you need to shape data without changing its meaning, choose formulas that handle real exceptions, and present results in charts and PivotTables that answer a specific question. This guide develops those skills through small, fully resolved workbook examples. It covers the shared expert skill groups of MO-201 for Office 2019 and MO-211 for Microsoft 365 Apps. Where a feature belongs to only one outline, the lesson says so. The emphasis is on the result you should see in Excel and the choices that produce it.
Manage workbook options and settings
1. Reference another workbook
An external reference reads a cell from another workbook. Excel identifies the source workbook, worksheet, and cell in the formula, so each part matters when you check a result. With both files open, select the destination cell, type =, select the source cell, and confirm the formula. A reference such as ='[Rates.xlsx]FX'!$B$2 points to B2 on the FX sheet in Rates.xlsx; dollar signs keep that address fixed if you copy the formula. The value shown in the destination is a calculated link, not an independent rate. A saved link may also include the source file's path. If the source changes, the destination reflects it once the link is updated and formulas calculate. Both Excel Expert examinations assess referencing data in other workbooks.
Worked example: In Budget.xlsx, put 80 in A4. Rates.xlsx has 1.25 in FX!B2. Enter ='[Rates.xlsx]FX'!$B$2 in Budget!B4 and =A4*B4 in C4. The result is 80 × 1.25 = 100. Change the source rate to 1.30 and update the link; C4 becomes 104. Check the source identity, B4's rate, and C4's product.
Watch the distinction: Typing 1.25 into Budget!B4 produces the right initial product but no link. The value cannot follow a later change in Rates.xlsx.
2. Diagnose a broken external link
When a linked result looks old, inspect both its formula and its connection to the source file. A moved or renamed workbook can leave Excel holding a saved value because it cannot reach the original path. First establish which file and sheet the formula names. Then inspect the workbook's link or workbook links controls for its current source and update status. If the correct source was moved, redirect the link to that file; if the link is no longer needed, replace it deliberately with a value or an appropriate local formula. Recalculate and compare the displayed rate with the source cell before trusting any downstream total. Updating a link answers a different question from repairing a formula: the former finds the intended source, while the latter changes what cell is read.
Worked example: Report!A4 contains 80, B4 links to Rates.xlsx FX!B2, and C4 is =A4*B4. C4 still shows 100, reflecting a cached rate of 1.25, although the moved Rates.xlsx has 1.30. Redirect the source path, update, and recalculate. Check that B4 shows 1.30, C4 shows 80 × 1.30 = 104, and the link names the moved file.
Watch the distinction: Overwriting the report's rate with 1.30 hides the broken path. It fixes today's number while leaving future source changes disconnected.
3. Enable a trusted macro workbook
A workbook's file type determines whether it can store macros; a separate security decision determines whether its code may run. An .xlsm file can contain a recorded macro, but opening it or saving another file with that extension does not execute one. Before enabling code, verify the workbook came from the expected source and inspect the named macro's intended actions. In a controlled copy, enable content only when you trust both the file and the action, then select the intended workbook and run the macro by name. Check a visible result afterward. Macro settings can block code even when the file is valid, and a formatting macro can affect the wrong sheet if it relies on the active selection. Treat the workbook's contents and its execution permission as separate checks.
Worked example: An internal Report.xlsm contains a recorded macro named BoldHeader, intended to bold A1. Open a trusted copy, inspect the macro's action, enable its code under the applicable Excel setting, and run BoldHeader on the report sheet. Check that the macro appears by name, A1 becomes bold, and a second workbook's A1 remains unchanged. Saving an empty file as .xlsm would satisfy none of those action checks.
Watch the distinction: The .xlsm extension means code can be stored, not that it is safe or running. Do not enable an unfamiliar workbook merely to see its formatting.
4. Copy a macro between workbooks
A recorded macro is stored in a workbook or personal macro location, so availability depends on where its module lives. To make a simple macro travel with Report.xlsm, copy the relevant macro or module from the source workbook into Report's VBA project, then save Report in a macro capable format. Inspect the recorded steps for references to the original workbook, a fixed sheet, or the active sheet. Such references determine which cells change when the macro runs. A macro that formats an explicitly named source sheet can be present in Report yet still alter the source. Test the copy with Report active and a known target range, then close and reopen Report to confirm the macro remains available. Keep the edit limited to the recorded action and its target.
Worked example: Source.xlsm holds FormatHeader, which bolds A1:C1. Copy its module into Report.xlsm. If a recorded line selects Source.xlsm, change that target to Report before running it. With Report active, run FormatHeader. Check that Report!A1:C1 is bold, Source!A1:C1 did not change during the test, and the macro remains listed after Report is saved and reopened.
Watch the distinction: Seeing FormatHeader in Report's macro list proves it was copied, but not that it acts on Report. Recorded workbook and sheet references still need checking.
5. Recover a workbook version
Version recovery starts with comparing specific cells, not assuming the oldest or newest file is wholly correct. In a workbook stored where version history is available, open the relevant saved versions and identify which values changed, when, and why. A prior version may contain the wanted value for one month but omit a later legitimate correction elsewhere. If only one cell needs recovery, copy that verified value into a current working copy rather than replacing the entire workbook. If the whole version is sound, restoring it may be appropriate, but first compare formulas, inputs, and dependent totals. Save the repaired workbook and recalculate so the result reflects the recovered input. Version history is useful evidence of earlier states; it does not establish which business value was approved.
Worked example: The current forecast has January 120 and February 140; an earlier version has January 100 and February 140. Review notes confirm 120 was an accidental January overwrite and 140 is approved. In a current copy, restore January to 100 and retain February at 140. Check both monthly cells and any total: 100 + 140 = 240, rather than the erroneous 260.
Watch the distinction: Restoring an entire old workbook for one bad cell can erase valid work done afterward. Compare the versions at cell level before replacing anything.
6. Choose an editing restriction
Choose a restriction by the change you need to control. If users must enter quantities while formulas remain intact, set the quantity cells to unlocked and leave formula cells locked, then protect the worksheet. Locking is a cell property that takes effect when sheet protection is applied; it is not an independent barrier in an unprotected sheet. Check the protection options too, since permitted actions such as selecting cells or formatting may affect usability. Workbook structure protection serves a different purpose: it controls sheet changes such as deletion or movement. Neither worksheet nor structure protection encrypts the file. For confidentiality, use an appropriate file access or encryption control separately. Test the template as an ordinary editor by entering a valid input and attempting a formula edit.
Worked example: In Quote.xlsx, B2 holds quantity and C2 contains =B2*20. Unlock B2, leave C2 locked, and protect the sheet. Enter 3 in B2: C2 calculates 3 × 20 = 60. Then try to type 99 into C2. Check that quantity entry succeeds, the total remains formula driven at 60, and the direct formula overwrite is blocked.
Watch the distinction: Protecting every cell makes a quote template unusable for entry. Conversely, marking formulas locked without turning on sheet protection leaves them editable.
7. Prepare input cells for protection
Prepare a protected form by separating inputs from calculations before applying protection. Excel cells are typically marked locked initially, so explicitly unlock each intended input range and keep formula cells locked. Then turn on worksheet protection and test the flow from input to output. This sequence matters because a locked formula cell is still editable until the sheet is protected, while a protected sheet with locked inputs prevents ordinary data entry. Inspect copied cells and new rows: formatting or insertion can change which cells are available to users. A formula can still recalculate from an unlocked input even though the formula cell cannot be edited directly. Sheet protection controls routine edits within Excel; it is not file encryption and should not be treated as secrecy for the underlying data.
Worked example: On an order form, B2 starts at 2 and C2 contains =B2*20, showing 40. Unlock B2, keep C2 locked, and protect the sheet. Change B2 to 4; C2 recalculates to 80. Check that B2 accepts 4, C2 displays 4 × 20 = 80, and a direct attempt to replace C2's formula is refused.
Watch the distinction: A visible 80 does not prove protection works. Test a direct edit to C2; otherwise the formula may be exposed despite calculating correctly.
8. Protect a specific cell range
A protected sheet can have exceptions for a particular range when different people contribute to the same workbook. Define the editable range and its permitted users or range password in the Excel environment that supports this feature, then protect the sheet. The exception allows edits inside that range while the surrounding locked cells remain restricted. It does not automatically grant access to an entire column, nor does it replace broader file sharing permissions. Start by identifying exactly which cells the contributor needs, because a range extending into formulas or another team's inputs weakens the control. Test with the relevant access condition, not just as the person who configured protection. An authorized change should succeed within the exception; an unauthorized change outside it should be blocked.
Worked example: Forecast!B2:B4 contains planning inputs, with B2 initially 5. Forecast!C2:C4 contains restricted calculations. Permit the planning team to edit B2:B4 and protect the sheet. As a permitted editor, change B2 to 6. Check that B2 stores 6, C2:C4 remain unchanged except for legitimate recalculation, and an editor without permission cannot directly change C2.
Watch the distinction: Unlocking the entire sheet to accommodate one team exposes every locked calculation. Define the smallest required editable range and test both allowed and denied actions.
9. Protect workbook structure
Workbook structure protection controls changes to the collection of worksheets, including actions such as deleting, renaming, moving, or adding sheets. It does not decide whether a cell on an existing sheet can be edited. That distinction is useful in a template whose Input and Summary sheets must remain in place while users enter data. Apply structure protection to the workbook, then rely on worksheet protection and cell locking for any cell level restrictions. Test each intended boundary independently: enter a value into an unlocked input cell, and attempt a sheet level change. If the entry fails, inspect the sheet's cell settings; if deletion succeeds, inspect structure protection. A password used for structure protection is an editing control, not encryption of workbook contents.
Worked example: A workbook has Input and Summary sheets. Input!B2 is unlocked and ready for entry. Protect workbook structure, then enter 7 in Input!B2 and attempt to delete Summary. Check that B2 stores 7, Summary remains present, and the delete command is unavailable or refused. The successful input shows structure protection did not lock cells.
Watch the distinction: Protecting workbook structure alone does not secure formulas on Summary. Protect its sheet separately if direct edits to formula cells must be restricted.
10. Set calculation behavior
Calculation mode determines when Excel refreshes formulas after an input changes. Automatic calculation is suitable when users must see current results immediately. Manual calculation can make a large model easier to work with, but its displayed formula values may lag until recalculation is requested. The formula text can be correct while its cached result is old. Check the mode before diagnosing arithmetic or rewriting a formula, especially when a workbook came from someone else's model. Calculation behavior can affect other open workbooks in an Excel session, so record the intended setting when handing off a model. After changing modes, recalculate and compare a known test result. A stable workbook should make it clear whether its outputs are current rather than relying on the user to remember a hidden setting.
Worked example: Set A1 to 10 and B1 to =A1*2, giving 20. In automatic mode, change A1 to 12; B1 should become 24. In manual mode, B1 may still display 20 until recalculation. Check the recorded mode, B1's unchanged formula, and the final 12 × 2 = 24 after recalculating.
Watch the distinction: If B1 shows 20 after A1 changes, rewriting =A1*2 may change nothing. Check calculation mode and recalculate before treating the formula as faulty.
11. Check stale calculated results
A stale calculated result is a timing problem, whereas a wrong result after recalculation points toward the formula, its references, or its inputs. Use that distinction to narrow a fault efficiently. Read the formula first to confirm the intended operation, inspect the current precedent cells, and note the displayed result. Then recalculate and compare the new result with arithmetic done from those current inputs. If the value changes to the expected result, investigate manual calculation or an update path. If it remains wrong, trace the references and check for hidden assumptions such as a different cost cell. Avoid replacing a formula with a typed answer just because its display is stale. A typed number removes the model's ability to respond to later changes.
Worked example: Revenue in A2 was 100, cost in B2 is 70, and profit in C2 is =A2-B2, showing 30. After A2 becomes 120, C2 still shows 30 in manual mode. Recalculate; it becomes 120 − 70 = 50. Check that A2 is 120, C2 still contains =A2-B2, and its refreshed result is 50.
Watch the distinction: A correct formula can display an old cached value. Changing its references before recalculating may introduce a real error into an otherwise sound model.
12. Resolve workbook comments
A workbook comment can capture a question about an input, but the comment itself does not settle the value. Read the discussion, confirm the decision from the responsible reviewer, update the input, and inspect dependent formulas before resolving the comment. Keep the thread available as a record where the workbook's collaboration setup allows it; resolution signals that the question was addressed, not that the spreadsheet's arithmetic was independently checked. In Excel, the exact interface for comments and notes varies by product version, so identify the item that contains the review discussion rather than assuming every annotation is equivalent. Comment management is named in the Office 2019 Excel Expert guide scope. It should not be presented as a distinct MO-211 requirement.
Worked example: An Office 2019 workbook has quantity 5 in A2, disputed price 18 in B2, and =A2*B2 in C2, showing 90. A review comment asks whether price should be 20. After the reviewer confirms 20, update B2 and resolve the comment. Check B2=20, C2=5 × 20=100, and the thread's resolved state.
Watch the distinction: Resolving a comment before changing the disputed input leaves the workbook wrong. Closing the discussion is a workflow step, not a calculation.
Reference: [2]
13. Set an editing language
Editing and proofing language affects how Excel checks text; it does not translate cell contents. In a mixed language workbook, select the notes or relevant editing context and choose the language used for those words. Proofing tools can then apply the appropriate dictionary where installed. Check a known correctly spelled term as a diagnostic cue, while remembering that a missing language pack or a proper name may affect the result. This setting is distinct from display language, which changes interface labels, and from regional settings, which influence how typed numbers and dates are interpreted. Keep the workbook's data unchanged while configuring the text review environment. Language options appear in the MO-201 Office 2019 skills outline; do not treat this lesson as evidence of a separate MO-211 objective.
Worked example: A report has English headings in A1:A3 and French notes in B1:B3, including the correctly spelled word “bonjour” in B2. Set the notes' proofing language to French where available, then run spelling review. Check that B2 still contains “bonjour,” it is no longer flagged solely under an English dictionary, and the English headings remain English.
Watch the distinction: Changing proofing language does not translate “bonjour” into English or alter a numeric formula. It changes which language tools inspect the text.
Reference: [2]
14. Set a display language
Display language controls the words Excel uses in menus, dialogs, and other interface elements. It is separate from the values stored in cells and from the language chosen to proofread text. In a training environment, set the preferred display language through Excel's language options and restart the application if that setting requires it. Confirm the interface changed by checking a familiar menu label, then reopen a known workbook and verify its values and formulas. Do not use interface language as a substitute for checking regional entry conventions: a workbook may still contain dates or decimals that depend on locale. Formula names shown in the interface can vary by localization, but the central check here is that the existing calculation still represents the same operation and result. MO-201 includes display language configuration.
Worked example: Before the change, a workbook has A1=8 and B1 =A1*3, displaying 24. Set a different Excel display language in the training environment and restart if prompted. Reopen the workbook. Check that menu labels use the selected language, A1 still stores 8, and B1 still calculates 8 × 3 = 24.
Watch the distinction: A translated ribbon does not translate workbook text or reinterpret the value 8. Treat display, proofing, and regional input behavior as separate settings.
Reference: [2]
15. Check locale-sensitive entries
Locale affects how Excel parses typed values before formulas or sorts use them. A comma may be the decimal mark in one setup and a list separator in another; formula argument separators can also differ. Dates written with slashes are especially ambiguous when both day and month are at most 12. Verify whether an entry became a numeric date or remained text, and confirm the intended calendar day before grouping, sorting, or subtracting dates. Use unambiguous entry methods, such as constructing a date from explicit year, month, and day components, when exchanging workbooks across locales. A display format can make a date look clearer, but it does not repair a date parsed incorrectly at entry. MO-201 includes language specific features; this is a practical consequence of interpreting localized entries.
Worked example: In a locale using a decimal comma, enter numeric 1,5 in A1 and 4 in B1; =A1*B1 yields 6. A typed 03/04/2026 is ambiguous: confirm whether the intended date is 3 April or March 4 before using it. Check A1's numeric type, the product 6, and the date's stored day and month.
Watch the distinction: Formatting an already misread date differently cannot recover the intended day and month. Verify the stored date, then reenter it unambiguously if needed.
Reference: [2]
Manage and format data
16. Extract a pattern with Flash Fill
Flash Fill detects a pattern from examples you type beside source data and fills matching outputs. It is useful for a consistent text structure such as “surname, given name,” where you want the given name in another column. Supply an example that makes the intended extraction clear, run Flash Fill, and scan the full output, especially rows with missing commas, extra spaces, or multiword names. Unlike a formula, Flash Fill writes results as values; editing the source later does not automatically update the filled column. That makes it convenient for one time cleanup but risky if the source is still changing. A successful run is judged by the values produced, not merely by Excel accepting the command. Both version outlines include Flash Fill.
Worked example: Put “Lee, Ana,” “Patel, Ravi,” and “Ng, Mei” in A2:A4. Type Ana in B2, then use Flash Fill for B3:B4. The completed B2:B4 should read Ana, Ravi, Mei. Check all three outputs, verify that the surname was excluded, and inspect any later source row lacking a comma before accepting its filled result.
Watch the distinction: Flash Fill does not create a live link to column A. If “Ng, Mei” changes later, refresh or correct B4 rather than assuming it updates.
17. Build a linear fill series
A linear series adds the same step each time. Specify the starting value, step, direction, and stopping value so Excel generates the intended sequence instead of copying a single cell. The arithmetic check is simple: each successive value minus the previous one should equal the step. The stop value sets a limit; depending on the step, the last generated value may be below that limit if the next addition would pass it. This method suits scheduled IDs or evenly spaced targets, but use care with identifiers that must preserve leading zeros, since numeric series values are numbers. For dates, choose a suitable date unit rather than assuming adding one means the intended business interval. Both expert outlines include advanced Fill Series options.
Worked example: Enter 5 in A1. Use a linear Fill Series down column A with step 5 and stop 20. A1:A4 becomes 5, 10, 15, 20. Check the four populated cells, each difference of 5, and the final value of 20. Four copies of 5 would reveal that the fill command copied rather than generated a series.
Watch the distinction: Dragging one starting cell may copy 5 repeatedly. Inspect the resulting differences; a true linear series has the specified fixed step.
18. Build a growth series
A growth series multiplies each value by a fixed factor. With a factor of 1.10, each new value is 110% of the previous one, so the absolute increase grows over time. A linear series instead adds a fixed amount, producing equal differences. Decide which mechanism matches the question before choosing the Fill Series type. For a percentage growth rate, convert the rate to its multiplier: 10% growth means 1 + 0.10 = 1.10. Check consecutive ratios, allowing for displayed rounding. The values generated are projected arithmetic, not a guarantee of future business performance. If only the display is rounded, the underlying number may retain more precision, so inspect stored values when exact later calculations matter.
Worked example: Start with 100 in A1 and use a growth series with factor 1.10 for three further cells. A2=100×1.10=110, A3=110×1.10=121, and A4=121×1.10=133.1. Check four entries, consecutive ratios of 1.10, and the final 133.1. A linear step of 10 would instead end at 130.
Watch the distinction: Treating 1.10 as an amount to add produces 101.1 after the first step. A growth factor is multiplied by the previous value.
19. Generate a RANDARRAY sample
RANDARRAY produces a dynamic array that spills from one formula cell into adjacent cells. Its arguments specify row count, column count, minimum, maximum, and whether results are whole numbers. The destination spill area must be empty; an occupied cell can prevent the array from displaying. Random outputs can change when Excel recalculates, so they are suitable for temporary sampling or practice data but not stable records unless you freeze a chosen sample as values. Test the requested shape and bounds rather than expecting a particular set of random numbers. For Microsoft 365 Apps, the MO-211 study guide covers this version's skills; RANDARRAY is named in MO-211's outline and should not be treated as an MO-201 requirement.
Worked example: In Microsoft 365 Apps, clear A1:C2 and enter =RANDARRAY(2,3,1,6,TRUE) in A1. It spills across two rows and three columns, giving six integers, each from 1 through 6. Check the 2×3 shape, every value's allowed range, and that A2:C2 contains spill results. To keep one sample, copy A1:C2 and paste values.
Watch the distinction: A screenshot of one random result is not an expected answer. Validate size and bounds, and paste values if later recalculation must not change it.
Reference: [4]
20. Build a four-section number format
A custom number format can contain four semicolon separated sections for positive numbers, negative numbers, zero, and text, in that order. Formatting changes how a cell is displayed; it does not change the stored value used in arithmetic. Literal words belong in quotation marks, and @ represents the original text in the fourth section. This lets a report distinguish a real zero from a blank: a stored zero can show “none,” while an empty cell remains empty unless other behavior is applied. Check the format with one value from each section before using it broadly. For an identifier, a display format may add visible zeros while the stored value remains numeric, so decide separately whether the underlying characters must survive export. Custom formats appear in both Excel Expert outlines.
Worked example: Put 12, -12, 0, and pending in A1:A4. Apply the custom format 0 "units";(0 "units");"none";@. The cells display “12 units,” “(12 units),” “none,” and “pending.” Check all four displays, confirm that A1:A3 still store 12, -12, and 0, and verify SUM(A1:A3)=12−12+0=0.
Watch the distinction: Seeing “none” does not mean the cell is blank. Its stored zero participates in calculations, while the text “pending” does not add a number.
21. Preserve identifiers with formatting
A number format controls what you see in a cell; it does not change the value Excel stores. The custom format `00000` displays numeric 42 as 00042, but calculations and many exports still use 42. That distinction matters for identifiers because leading zeros can be part of the code itself. If a code must survive a text export, store it as text before entering or importing it. Preformatting the destination column as Text is useful, but it cannot restore zeros already stripped from an imported number. Check the source specification before choosing: a quantity belongs in a numeric cell, while a fixed-width account code usually belongs in a text cell. Inspect both the displayed cell and the formula bar when diagnosing a mismatch.
Worked example: In Microsoft 365 Apps, A2 contains numeric 42. Apply custom format `00000` to A2; it displays 00042, while the formula bar still shows 42. Set B2 to Text and enter `00042`. Export both cells to a text file and inspect the result. Check: A2 displays five digits, B2 stores five characters, and the exported code from B2 retains both leading zeros.
Watch the distinction: A five-digit display does not prove that five digits are stored. Formatting numeric 42 cannot guarantee that a later text export will preserve its leading zeros.
22. Create a validation list
A validation list gives an input cell a defined set of permitted entries. Select the intended range, choose List in Data Validation, supply the allowed values or a source range, and set a Stop error alert when an invalid typed entry must be refused. A source range is easier to maintain when the categories may change. The dropdown helps entry, but the rule is not a complete audit of the column: values entered before validation was added can remain, and some paste or import operations can bypass the intended entry check. Use Circle Invalid Data or inspect the existing values separately. Decide whether blanks are allowed, and check spelling and spaces in the category list because `Open` and `Open ` are different text entries. The MO-211 skills outline includes configuring data validation.
Worked example: In Microsoft 365 Apps, B1 is Status and B2:B5 are blank. Put Open, Hold, and Closed in E2:E4, then apply List validation to B2:B5 with `=$E$2:$E$4` as its source and a Stop alert. Enter Open in B2 and try Pending in B3. Check: the dropdown offers three categories, Open is accepted, and Pending is refused. Inspect any earlier entries separately.
Watch the distinction: Adding a list does not cleanse existing records. An old value such as Pending may still be present even though a new typed Pending entry is stopped.
23. Validate with a formula
Custom validation evaluates a formula for each cell in the applied range. Write the rule as though the top-left cell were active, then let relative references shift for later rows. For B2:B10, `=AND(ISNUMBER(B2),B2>=0,B2<=100)` requires a numeric quantity within the inclusive bounds; at B3, Excel evaluates the corresponding B3 reference. Fix a reference with `$` only when every row should consult the same cell, such as a shared limit in E1. Test both boundary values and wrong data types. Excel's Ignore blank setting deserves separate attention: depending on the desired rule, clear it or add an explicit blank check. Validation primarily governs entry, so review pasted and existing data too. These are practical workbook controls, not a substitute for checking the resulting records.
Worked example: In Microsoft 365 Apps, B2:B10 is an empty Quantity range. Select it with B2 active and enter custom validation `=AND(ISNUMBER(B2),B2>=0,B2<=100)` using comma argument separators; set a Stop alert and disallow blanks if required. Try 80, 120, and `many`. Check: 80 is accepted, 120 exceeds the upper bound, and text fails ISNUMBER. Confirm that B3 tests B3, not B2.
Watch the distinction: Locking the formula to `$B$2` would make every row test B2. A valid B2 could then let an invalid quantity elsewhere appear acceptable.
24. Group detail rows
Grouping creates an outline so detail rows can be collapsed and expanded. The rows remain in the worksheet, and formulas that refer to them still calculate. Place a subtotal outside the detail rows you group; otherwise collapsing may hide the subtotal along with the figures it summarizes. Select the detail rows, use Group, and check the outline symbols at the sheet edge. A collapsed outline is useful when reviewing many sections because it exposes summary lines without discarding transaction detail. It differs from filtering: a filter selects records by criteria, whereas an outline shows or hides a chosen block. It also differs from deleting rows, which removes source data and can alter totals. Before relying on the display, verify the subtotal formula and expand the group to reconcile its inputs.
Worked example: In Microsoft 365 Apps, B3:B5 contains 10, 15, and 20 units; B6 contains `=SUM(B3:B5)`. Select rows 3:5 and group them, then collapse and expand the outline. Check: the collapsed view still shows B6 as 45 units, expansion reveals all three inputs, and 10+15+20 agrees with the displayed subtotal. The source values have not been deleted.
Watch the distinction: A collapsed group can look as though records vanished. Expand the outline before assuming data was removed; hiding detail does not change a SUM over those rows.
25. Insert subtotals by category
The Subtotal command inserts a result at each change in a grouping field. Sort the complete record range by that field first so each category forms one contiguous block; otherwise the same category may receive several separate subtotals. Choose the field to group by, the aggregation such as Sum, and the numeric column to aggregate. Excel then creates an outline that lets you display totals or detail. Keep headers with their data while sorting, and check the grand total against the source amounts. The command works on a normal range; if the records are an Excel Table, convert the table to a range before using the legacy Subtotal command. A PivotTable is another way to summarize unsorted categories, but its output is a separate summary rather than inserted rows.
Worked example: In Microsoft 365 Apps, A1:B4 has Category and Sales, followed by A,10; B,5; A,20. Sort A2:B4 by Category, keeping each row together. Use Subtotal at each Category change, Sum of Sales. Check: the sorted detail is A,10; A,20; B,5, the A subtotal is 30, and the B subtotal is 5. The grand total is 30+5=35 sales units.
Watch the distinction: Subtotals follow changes in adjacent labels. Running the command on A, B, A produces separate A sections; it does not automatically gather matching labels across the range.
26. Use a table total row
An Excel Table's Total Row provides a dropdown for choosing an aggregation for each column. Sum answers how much occurred; Average answers the mean per numeric record; Count answers how many populated records are present. Pick the measure that matches the question before applying a number format. The Total Row commonly uses SUBTOTAL calculations, so its result can respond to table filtering. That makes it useful for reviewing a visible subset, but it also means a filtered total may differ from the unfiltered source total. Give the table clear headers and confirm that numeric entries are stored as numbers, since text that looks numeric can distort the interpretation of a count or average. Keep the Total Row distinct from ordinary data rows so a later calculation does not include the summary as another sale.
Worked example: In Microsoft 365 Apps, make A1:B4 a table with Item and Sales; B2:B4 contains 10, 20, and 30 dollars. Turn on Total Row and choose Sum for Sales. Check: it shows $60, since 10+20+30=60; changing the aggregation to Average shows $20, since 60/3=20. Confirm that the three source rows remain unchanged.
Watch the distinction: A currency format does not determine the aggregation. A displayed `$3` could be a formatted Count of three records, not total revenue.
27. Remove duplicate records
Remove Duplicates compares the columns you select and keeps one row for each distinct combination. Define what makes a record unique before opening the command. If a customer ID can appear in several regions, ID alone is an unsafe key; ID plus Region may describe the intended unique record. Select the whole dataset with its header so Excel removes complete rows and preserves field alignment. Preview or copy the source if the record definition is uncertain, then compare row counts and surviving combinations after removal. An exact duplicate is a row whose selected values match, even if other unselected columns differ. That last point can matter: excluding a transaction date, for example, could remove a valid repeat purchase. The operation changes the source range, so the chosen comparison columns are the central decision.
Worked example: In Microsoft 365 Apps, A1:B4 contains ID and Region, then 7/East, 7/East, and 7/West. Select A1:B4 and run Remove Duplicates with both columns checked and headers recognized. Check: one exact 7/East row is removed, one 7/East row remains, and 7/West remains. The record count falls from three to two.
Watch the distinction: Checking ID alone treats all three rows as duplicates. It would discard the valid West combination because Region was excluded from the uniqueness test.
28. Highlight a threshold
Conditional formatting tests cell values and applies a visual format when a condition is true. The comparison operator matters at the boundary. A rule of greater than 100 excludes exactly 100; greater than or equal to 100 includes it. Before creating the rule, identify the units and whether the threshold itself qualifies. Apply the rule to the full intended range, then test a value below, at, and above the boundary. The displayed format does not alter the underlying number or a SUM over that range. If the rule refers to a threshold cell, decide whether its reference must stay fixed when copied or applied across cells. Review the Applies To range in the Rules Manager if an expected cell does not change appearance. Further MOS practice questions can reinforce boundary checks.
Worked example: In Microsoft 365 Apps, A2:A4 contains 90, 100, and 110 dollars. Select A2:A4 and create a rule that fills cells red when their value is greater than 100. Check: A2 and A3 remain unfilled, A4 is red, and `=SUM(A2:A4)` still returns $300. If 100 must qualify, edit the comparison to greater than or equal to.
Watch the distinction: A threshold named 100 does not imply that 100 qualifies. Read the operator: `>100` starts above 100, while `>=100` includes it.
29. Format whole rows with a formula
A formula-based conditional formatting rule can color an entire record from a status stored in one column. Write the formula for the top-left cell of the Applies To range. In `=$C2="Late"`, `$C` keeps the test on the Status column as formatting moves across the row, while the relative row number changes for each record. If the range begins at A2, row 2 is the right starting reference; a rule written for a different first row can appear shifted. Use a text comparison that matches the actual status values and check the complete Applies To range. This approach is especially useful when several columns belong to one record and the reader needs to scan for exceptions. It changes appearance only; the source status remains in column C.
Worked example: In Microsoft 365 Apps, A2:D4 contains three records, with C2=Late, C3=Open, and C4=Late. Select A2:D4, add a formula rule `=$C2="Late"`, and choose a fill. Check: every cell in rows 2 and 4 receives the fill, row 3 does not, and changing C3 to Late also highlights row 3.
Watch the distinction: Using `=C2="Late"` lets the tested column shift from C as formatting moves across A:D. The result may highlight only part of a record.
30. Manage competing formatting rules
Several conditional formatting rules can apply to the same cell. Use the Rules Manager to inspect each rule's condition, Applies To range, and priority. When two rules assign competing formats, the higher-priority rule determines the overlapping format unless the rule design changes that interaction. Stop If True prevents lower-priority rules from being evaluated for a cell once the earlier rule is true. Arrange specific exceptions above broad conditions: a red rule for values above 115 should precede a general amber rule for values above 100. Test a value that matches both rules and one that matches only the broad rule. Also inspect rules copied from other ranges, since an unexpected Applies To address can make the correct condition seem faulty. Rule order is part of the workbook's logic, not merely its decoration.
Worked example: In Microsoft 365 Apps, A2=120 and A3=110. Apply a red rule for values greater than 115 above an amber rule for values greater than 100, both covering A2:A3; set Stop If True on the red rule. Check: 120 is red because it passes both thresholds but stops at the first rule, while 110 is amber because it fails the red rule.
Watch the distinction: Adding a stricter red rule does not guarantee the desired display. If a competing amber rule has priority, inspect the order and Stop If True setting.
Create advanced formulas and macros
31. Combine logical tests
Logical functions let a formula express a decision with several conditions. AND returns TRUE only when every test is true; OR returns TRUE when at least one is true; NOT reverses a logical result. IF then chooses the output for TRUE or FALSE. Translate the business rule into plain conditions before nesting functions, and test combinations that exercise both branches. For an order, stock of at least five and a customer who is not on hold can be written with AND and NOT. A cell containing `No` as text is different from a Boolean FALSE, so match the workbook's actual data design. Relative references such as A2 and B2 change when the formula is copied down, which lets each order use its own stock and hold status. The formulas below assume comma argument separators.
Worked example: In Microsoft 365 Apps, A1 is Stock and B1 is Hold; A2=8 and B2=No. Enter `=IF(AND(A2>=5,NOT(B2="Yes")),"Approve","Review")` in C2. Check: stock passes 8>=5, the hold test is false and NOT makes it true, so C2 says Approve. Change B2 to Yes; NOT becomes false and C2 changes to Review.
Watch the distinction: Replacing AND with OR could approve an order with enough stock even when the customer is on hold. Both stated conditions must pass.
32. Choose IFS or SWITCH
IFS checks logical tests in order and returns the result for the first TRUE test. Use it for ranked thresholds by placing the highest cutoff first; otherwise a broad lower cutoff may capture a score before a higher grade is considered. A final TRUE test can supply a default. SWITCH compares one expression with explicit values and returns the result for the first match, with an optional default at the end. It suits fixed codes such as H, O, and C because no threshold ordering is involved. Neither function changes the input; each maps it to an interpretation. Check values at cutoff boundaries and at an unmapped code. In the formulas here, comma separates arguments. Both functions appear in the published outlines for the two Excel Expert versions.
Worked example: In Microsoft 365 Apps, A2=82 and B2=H. Enter `=IFS(A2>=90,"A",A2>=75,"B",TRUE,"C")` in C2 and `=SWITCH(B2,"H","Hold","O","Open","Unknown")` in D2. Check: 82 misses 90 but meets 75, giving B; H maps to Hold; and an unmapped code such as X gives Unknown. The formulas use comma separators.
Watch the distinction: Putting `A2>=75` before `A2>=90` makes a score of 95 return B. IFS stops at its first TRUE condition.
33. Summarize one criterion
The one-criterion summary functions answer different questions about the same matching records. SUMIF adds their numeric values, COUNTIF counts matching cells in the criteria range, and AVERAGEIF divides the matching numeric total by the number of numeric values averaged. Align a separate sum or average range with the criteria range so each category is paired with the right amount. Exact text criteria such as `"East"` are useful for categories; confirm spelling and stray spaces when a result is unexpectedly low. A blank amount can affect the functions differently: a matching category may be counted by COUNTIF while its blank amount contributes nothing to SUMIF and is excluded from AVERAGEIF's numeric average. State which denominator the question needs before reporting a mean. All formulas here use comma argument separators.
Worked example: In Microsoft 365 Apps, A2:B4 contains East/10, West/20, and East/30 dollars. Calculate `=SUMIF(A2:A4,"East",B2:B4)`, `=COUNTIF(A2:A4,"East")`, and `=AVERAGEIF(A2:A4,"East",B2:B4)`. Check: East amounts add to $40, two rows match, and their mean is $40/2=$20. West's $20 is excluded from each East result.
Watch the distinction: COUNTIF's result of 2 is a record count, not a dollar amount. SUMIF and AVERAGEIF answer different questions using the matched amounts.
34. Summarize several criteria
Multi-criteria functions pair each condition with a range of the same size and shape. SUMIFS adds amounts where all criteria match; COUNTIFS counts those rows; MAXIFS and MINIFS find extreme numeric values within the same subset. Align the first and last rows of every range so Region, Status, and Amount describe the same records. When copying a summary formula, lock source ranges with absolute references while leaving criteria cells relative if each output row has different requested labels. For example, `$A$2:$A$4` stays fixed, while E2 can become E3. Test a subset with one match first, then several matches, to catch a shifted range. Unlike a filter, these functions calculate a result without changing which source rows are visible. The following formulas assume comma argument separators.
Worked example: In Microsoft 365 Apps, A2:C4 holds East/Open/10, East/Closed/20, and West/Open/30 dollars. Put East in E2 and Open in F2. Use `=SUMIFS($C$2:$C$4,$A$2:$A$4,E2,$B$2:$B$4,F2)` and matching COUNTIFS, MAXIFS, and MINIFS formulas. Check: only row 2 meets both criteria, so the sum is $10, count is 1, and both extremes are $10.
Watch the distinction: Ranges shifted by one row can return plausible but wrong totals. Every criteria range and the sum range must cover corresponding records.
35. Reuse a calculation with LET
LET assigns a name to an expression inside one formula, then uses that name in the final calculation. It helps when a lengthy intermediate result appears more than once: calculate it once, inspect its meaning, and reference it consistently. A simple profit floor can name revenue minus cost as `net`, then return the larger of that result and zero through IF. Names created by LET live inside that formula; they do not create worksheet cells or workbook-level named ranges. Choose names that describe the calculation and avoid names that resemble cell references. LET is listed in the MO-211 Microsoft 365 Apps guide scope through the MO-211 outline. Treat this as a Microsoft 365 Apps exercise; the supplied MO-201 Office 2019 outline does not list LET as a required skill. The formula uses comma argument separators.
Worked example: In Microsoft 365 Apps, B2 holds revenue of $120 and C2 holds cost of $70. Enter `=LET(net,B2-C2,IF(net>0,net,0))` in D2. Check: net is $120-$70=$50, the positive branch returns $50, and D2 displays $50. If cost changes to $130, net becomes -$10 and the displayed result becomes $0.
Watch the distinction: LET's `net` is a temporary name inside that formula. Typing `=net` into another cell will not retrieve the value unless a separate named range exists.
Reference: [4]
36. Control VLOOKUP match behavior
VLOOKUP searches the first column of a vertical table and returns a value from a numbered column to its right. For identifiers, specify FALSE for an exact match; an absent code should then produce an error that can be handled explicitly. TRUE or an omitted fourth argument requests approximate matching, which requires ascending lower-bound keys when the table represents bands. It returns the row for the greatest lower bound that does not exceed the lookup value. Unsorted bands can yield misleading results. HLOOKUP uses the same match idea across the first row of a horizontal table. Check orientation, return-column or return-row index, and match mode before blaming the source data. Both lookup functions are in the MO-201 Office 2019 exam scope, as well as the MO-211 outline. Formulas below use comma separators.
Worked example: In Microsoft 365 Apps, A2:B3 contains SKU P2 priced at $18 and SKU Q4 priced at $25. `=VLOOKUP("P2",A2:B3,2,FALSE)` returns $18. In D2:E4, enter sorted lower bounds 0/0%, 100/5%, and 200/10%. `=VLOOKUP(150,D2:E4,2,TRUE)` returns 5%, since 100 is the greatest bound no higher than 150. Check both lookup modes and the ascending band order.
Watch the distinction: Using approximate matching for a SKU can return a neighboring code's price. Use FALSE when only the exact identifier is acceptable.
37. Combine INDEX and MATCH
MATCH locates a lookup value's position within a one-dimensional range. INDEX retrieves the value at a specified position in another range. Together they can look up an ID in one column and return a name from a column to its left, which ordinary VLOOKUP cannot do without rearranging its table. Use `0` as MATCH's third argument for an exact identifier match. The lookup and return ranges must align row for row; the second item in each range must describe the same record. MATCH returns a relative position within its range, not the worksheet row number. Check duplicate keys because an exact MATCH returns the first matching position. Lock both ranges if the formula will be copied while the lookup cell changes. The example uses comma argument separators.
Worked example: In Microsoft 365 Apps, A2:A4 contains Ana, Bo, Cy, and B2:B4 contains IDs 8, 9, 10 in matching rows. Put 9 in D2 and enter `=INDEX(A2:A4,MATCH(D2,B2:B4,0))` in E2. Check: MATCH finds position 2 within B2:B4, INDEX returns the second name, Bo, and ID 10 would return Cy.
Watch the distinction: MATCH's result of 2 means the second position in B2:B4, not worksheet row 2. The paired INDEX range must start on the corresponding row.
38. Look up with XLOOKUP
XLOOKUP separates the search range from the return range, so the returned field can be on either side of the key. Its basic form takes a lookup value, a lookup array, and an equally sized return array. Add an if-not-found result when a missing identifier should display a clear message. The default match is exact, which suits codes; other match options should be chosen deliberately for banded data. Keep the arrays aligned so each ID maps to the correct name, and check duplicate IDs because the default search returns the first match. A text response such as `Not found` is useful for review, but do not include it in numeric arithmetic without handling it. XLOOKUP belongs to the MO-211 Microsoft 365 Apps outline; the supplied MO-201 Office 2019 outline does not list it. The formula uses comma separators.
Worked example: In Microsoft 365 Apps, A2:A4 contains Ana, Bo, Cy and B2:B4 contains 8, 9, 10. Put 9 in D2 and enter `=XLOOKUP(D2,B2:B4,A2:A4,"Not found")` in E2. Check: ID 9 aligns with Bo, so E2 shows Bo; changing D2 to 11 shows Not found; source names and IDs remain in their original columns.
Watch the distinction: The lookup and return arrays must correspond row by row. Searching B2:B4 while returning a shifted name range can produce a believable wrong name.
Reference: [4]
39. Filter and sort a spill result
FILTER returns only rows that satisfy a Boolean condition, and SORTBY orders that returned array using a matching sort key. Both produce spill results, so the output area must be empty enough for every returned row and column. To filter records for East and rank them by Amount, filter the two-column source, then sort by the filtered Amount column in descending order. The source order remains unchanged; the result is a separate, recalculating view. If no rows match, provide FILTER's optional empty-result response so the output is understandable. Check that the filter's include array covers the same number of rows as the source. FILTER and SORTBY are listed for MO-211 Microsoft 365 Apps, while the supplied MO-201 Office 2019 outline does not list them. The formula below uses comma argument separators.
Worked example: In Microsoft 365 Apps, A2:B4 holds East/10, West/30, and East/20 dollars. With D2:E3 empty, enter `=LET(x,FILTER(A2:B4,A2:A4="East"),SORTBY(x,INDEX(x,,2),-1))` in D2. Check: two East rows spill, the first is East/$20, and the second is East/$10. The original three source rows stay in their original order.
Watch the distinction: Sorting A2:B4 itself changes the source records' order. SORTBY on the filtered spill creates an ordered result while leaving the source in place.
Reference: [4]
40. Calculate working dates
Excel stores dates as numbers, which lets WORKDAY move through a calendar while skipping weekends and any supplied holiday dates. Its second argument counts working days after a start date; a value of three does not mean three calendar days. WEEKDAY reports a date's day-of-week position, with the numbering scheme controlled by its optional return-type argument. State that scheme when interpreting the number. TODAY supplies the current date and NOW supplies current date and time; both change when the workbook recalculates. Use them for a live status view, but store a fixed date when a historical record must remain stable. Confirm that imported dates are real Excel dates rather than text, and consider the workbook's locale before interpreting ambiguous typed dates. The example uses comma separators and a hypothetical calendar with no holidays.
Worked example: In Microsoft 365 Apps, A2 contains the hypothetical start date January 5, 2026, a Monday, stored as a date. Enter `=WORKDAY(A2,3)` in B2 and `=WEEKDAY(B2,2)` in C2, with no holidays. Check: Tuesday is day 1, Wednesday day 2, Thursday January 8 day 3; B2 shows January 8 and C2 shows 4 because return type 2 numbers Monday as 1.
Watch the distinction: Adding 3 directly to a date counts calendar days. WORKDAY counts working days and can also exclude supplied holidays, so the two methods can diverge.
41. Model loan payments and periods
PMT calculates the payment for a constant-rate loan with equal payments; NPER solves for the number of periods when the payment is known. Keep every input on the same time basis: monthly payments require a monthly rate and a count of months. For a nominal annual rate quoted with monthly compounding, divide the rate by 12; another rate convention may require a different conversion. Excel treats money received and money paid as opposite cash flows. A borrower can enter the loan proceeds as positive present value and read PMT's negative result as an outgoing payment. Give NPER a negative payment for the same reason. State whether payments occur at the beginning or end of each period, since timing changes an interest-bearing result. Format the result as money, but inspect the underlying sign and units before using it in a schedule.
Worked example: In Microsoft 365 Apps, enter 1200 in B2 as a hypothetical loan and 12 in B3 as monthly periods. In B4 enter =PMT(0,B3,B2); it returns -100 per month. In B5 enter =NPER(0,B4,B2); it returns 12 months. Checks: B4 is negative, 12 payments of 100 repay 1200, and B5 agrees with the entered term. For an interest-bearing case, convert the quoted annual rate before calculation.
Watch the distinction: A positive loan balance and positive payment describe money flowing in the same direction to Excel. Give the payment the opposite sign, then check that rate and period units match.
42. Consolidate matching ranges
Consolidate combines similarly structured ranges through a chosen function, commonly Sum. If source categories appear in different orders, match their labels by selecting both Top row and Left column in the Consolidate dialog. Without those labels, consolidation may align cells by position, producing plausible but wrong category totals. First make each source block rectangular, put the same field names and category spelling in the corresponding locations, and exclude existing totals that would be counted again. Add each source reference, choose Sum, and inspect the destination against the source records. Create links to source data if the result must respond to later source changes; otherwise treat the consolidation as a snapshot and rerun it after edits. A PivotTable or formula-based summary can be preferable when records will be appended often, because its refresh path is easier to manage.
Worked example: Sheet North has headers Item, Units and rows Pens 10, Books 20. Sheet South has the same headers and rows Books 5, Pens 7. Select an empty destination, choose Consolidate with Sum, add both ranges, and check Top row and Left column. The output is Pens 17 and Books 25. Checks: both labels appear once, 10+7=17, and 20+5=25 despite the reversed source order.
Watch the distinction: Matching the first row of one source to the first row of another would pair Pens with Books here. Enable label matching and verify spelling before trusting the totals.
43. Compare what-if methods
Goal Seek works backward from one formula result. Supply the formula cell, the desired result, and one input cell Excel may vary; the formula must actually depend on that input. It is useful when one target and one adjustable input define the problem. Scenario Manager answers a different question: it stores named sets of input values and recalculates the dependent formulas for each set. Use scenarios to compare alternatives, especially when several inputs change together. Neither tool creates a business assumption for you. Check whether the solved input is feasible, whether units are consistent, and whether the formula remains appropriate across the proposed range. If an input must be whole, confirm the returned Goal Seek value meets that restriction. A scenario summary can make alternatives easy to compare, but its displayed outputs still depend on the model's formulas.
Worked example: Set B2 to 20 units and B3 to =12*B2-200, with amounts in hypothetical currency units. Goal Seek B3 to 100 by changing B2: solving 12u-200=100 gives u=25. Save scenarios named Low and High with B2 values 20 and 30; B3 becomes 40 and 160. Checks: the target cell contains a formula, Goal Seek returns 25 units, and each scenario produces its expected profit.
Watch the distinction: Scenario Manager does not solve for the units needed to reach a target. Goal Seek does that with one changing cell; scenarios evaluate input sets you already chose.
44. Trace a faulty formula
A wrong displayed result can come from a faulty reference, an unexpected input, an error value, or stale calculation. Start with the result cell and trace its precedents to see which cells feed it; trace dependents when you need to know what else the result affects. Evaluate Formula reveals the order and intermediate values in a complex expression. Error Checking can flag certain formula problems, but it cannot know the business meaning of a valid-looking reference. The Watch Window keeps critical cells visible while you move between sheets and test a correction. Compare the intended formula with its actual references, then correct the smallest confirmed cause and recalculate. If tracing shows the right cells but the value remains old, inspect calculation mode before changing arithmetic. This sequence avoids masking a reference problem with a hard-coded answer.
Worked example: Enter A2=10, B2=5, B3=10, and C2 =A2+B3. C2 shows 20, although the intended sum is 15. Trace Precedents points to B3; Evaluate Formula shows 10+10. Replace C2 with =A2+B2 and watch it change to 15. Checks: arrows identify B2 after correction, the formula contains B2, and the Watch Window shows 15.
Watch the distinction: A result of 20 does not prove addition failed. The arithmetic is correct for the referenced cells; inspect the precedents before rewriting or replacing the formula.
45. Record and edit a simple macro
A recorded macro turns a repeatable sequence of Excel actions into a named procedure. Record only the actions you want repeated, then stop recording promptly so navigation and accidental selections do not become part of the procedure. Choose the storage location deliberately: a macro kept in the current workbook travels with that workbook when saved in a macro-capable format. Test it on a copy or harmless sample range, because running a macro can overwrite formatting or values and may not be undone through the usual Undo command. Review the recorded steps if the target range changes. A small edit to a recorded cell address is enough for this task; elaborate VBA design is unnecessary. Relative-reference recording can make actions follow the active cell, while absolute recording targets fixed addresses. Verify which behavior you captured before running it on another sheet.
Worked example: In Report.xlsm, set A1:C1 as headings and B2:B5 as four unformatted amounts. Record FormatReport while bolding A1:C1 and formatting B2:B4 as currency. Stop, inspect the recorded macro, and change its amount range to B2:B5. Run it on Report. Checks: the named macro is available, A1:C1 is bold, and B5 now has the same currency format as B2:B4.
Watch the distinction: Saving a workbook as .xlsm does not create or run the recorded steps. The macro must be stored, reviewed, and explicitly run from a trusted workbook.
Manage advanced charts and tables
46. Build a dual-axis combo chart
A combo chart can display measures with different units in one view. Put the volume measure in columns on the primary axis and the rate measure in a line on a secondary axis. This makes both series visible without treating a percentage as if it were currency or units. Label both vertical axes and check their scales; an unlabeled secondary axis can make two unrelated magnitudes look directly comparable. Choose a horizontal category axis that represents the same months for both series. Verify that the margin values are stored as percentages, not whole numbers later formatted to look like percentages. The line shows changes in margin rate, while the columns show changes in sales; one does not prove the other caused it. The MO-211 Excel Expert outline includes dual-axis charts among its chart skills.
Worked example: In A1:C4 enter Month, Sales, Margin; then Jan 100 20%, Feb 120 25%, and Mar 140 30%. Insert a combo chart with Sales as columns and Margin as a line on the secondary axis. Title the axes Sales (currency units) and Margin (%). Checks: three monthly columns appear, the line has three percentage points, and both series rise without sharing one numeric scale.
Watch the distinction: Placing 20%, 25%, and 30% on a currency axis may flatten the margin line. Use a labeled secondary percentage axis and check that the month categories align.
47. Interpret a Box & Whisker chart
A Box & Whisker chart summarizes the center and spread of numeric observations. The line inside a box marks the median; box edges represent quartiles, and whiskers and possible outlier points describe values farther from the center. When comparing groups, first check that the data use the same units and comparable observation periods. Then compare medians, box widths, whiskers, and any individually plotted points. A common median does not mean distributions are alike. Excel's quartile calculation and outlier display options can affect the exact appearance, particularly with very small samples, so inspect the chart settings before interpreting a point as an outlier. Use the underlying observations to explain a striking shape rather than claiming that the picture alone identifies a cause. A box plot conveys distribution more effectively than a simple average when values are uneven.
Worked example: Enter team A's observations as 8, 9, 10, 11, 12 and team B's as 8, 9, 10, 11, 30 in two columns. Insert a Box & Whisker chart. Each sorted list has median 10, while B extends to 30 instead of 12. Checks: both medians read 10, the upper extent differs, and the chart's outlier setting is inspected before labeling 30 an outlier.
Watch the distinction: Equal medians do not imply equal consistency. Team B has an extreme upper observation, so compare spread and plotted points as well as the center.
48. Show stages with a funnel chart
A funnel chart represents counts at successive stages of a process. Arrange stages in their actual order, from the starting population through later outcomes, and use counts measured on a compatible basis. Each stage should describe members who reached that stage; otherwise a narrowing shape may mislead. Calculate conversion by dividing the later-stage count by the immediately preceding count when you want stage-to-stage performance. Divide by the first-stage count for overall conversion from entry to that stage. The chart's width makes drop-offs easy to see, but exact percentages should be computed or labeled rather than estimated from area. A stage count can increase if the process allows additions between stages; investigate that definition rather than forcing the numbers into a neat funnel. Compare periods only when stage definitions and time windows match.
Worked example: Enter Stage and Count as Leads 100, Demos 40, Purchases 10. Insert a funnel chart in that order. Lead-to-demo conversion is 40/100=40%; demo-to-purchase conversion is 10/40=25%; lead-to-purchase conversion is 10/100=10%. Checks: counts descend 100, 40, 10, the chart preserves stage order, and the percentages use their stated denominators.
Watch the distinction: Dividing purchases by all leads gives overall conversion, not demo-to-purchase conversion. Choose the denominator that matches the stage relationship you are describing.
49. Choose histogram bins
A histogram groups numeric observations into intervals so you can see their distribution. Decide the bin boundaries before interpreting the bars, and make adjacent bins nonoverlapping with no gaps in the intended data range. For whole-number observations, bins labeled 0 through 4 and 5 through 9 are easy to audit. Excel may express histogram bins through width, count, overflow, or underflow settings; check the labels it actually displays and confirm how values on a boundary are assigned. A wider bin can hide clusters, while many narrow bins can make a small sample look erratic. The vertical axis shows frequency, meaning the number of observations in each interval, unless a different statistic was explicitly configured. A regular category chart with one bar per distinct number answers a different question because it does not group observations by numeric proximity.
Worked example: Enter five observations in A2:A6: 2, 3, 4, 7, 8. Configure a histogram whose displayed integer bins are 0–4 and 5–9. The first bin contains 2, 3, 4, so frequency 3; the second contains 7, 8, so frequency 2. Checks: all five values are counted once, the two bars show 3 and 2, and no value falls outside the bins.
Watch the distinction: Five separate category bars would show each entered number, not the frequency pattern across intervals. Check bin boundaries and total frequency before interpreting the histogram's shape.
50. Display hierarchy with a sunburst
A sunburst chart displays hierarchical categories as concentric rings. Arrange source columns from broad parent to narrower child, followed by a numeric measure. Every child must sit under the correct parent; otherwise the chart can suggest a relationship the data do not contain. Inner segments represent parent groups, and outer segments represent their child categories. Segment size reflects the selected numeric measure, so compare values only after confirming that all rows use the same unit and that totals have not been inserted as extra observations. A parent with one child still occupies its share of the inner ring. For small data, check parent totals by summing children before interpreting the geometry. If names repeat under different parents, preserve the full path in the data so identical child labels do not conceal distinct categories.
Worked example: Enter Parent, Child, Value rows Hardware, Pens, 10; Hardware, Paper, 20; Services, Support, 30. Insert a sunburst chart from these three source rows, without separate subtotal rows. Hardware totals 10+20=30 and Services totals 30, so each is half of the 60-unit whole. Checks: the inner ring has two parents, the outer ring has three children, and the child values sum to 60.
Watch the distinction: Adding Hardware=30 as another source row would count its value twice. Supply the child records and let the chart derive the parent share.
51. Explain changes with a waterfall
A waterfall chart explains how an opening amount changes through increases and decreases to reach a closing amount. Enter positive values for additions and negative values for reductions. Mark the opening and closing bars as totals so Excel anchors them to the baseline rather than treating them as further changes. Verify the arithmetic separately: closing amount equals opening amount plus every signed movement. Labels should name what changed, and the chart's colors should distinguish increases, decreases, and totals. A waterfall is useful for explaining a bridge between two balances; it cannot by itself show why a change happened. Avoid mixing values measured in different units or periods. If an intermediate subtotal is included, mark it as a total and check that the following bars start from that subtotal. An incorrectly classified total can make the chart appear coherent while overstating the ending value.
Worked example: Enter Stage, Value rows Opening revenue 100, New sales 30, Returns -20, Closing revenue 110. Insert a waterfall and mark Opening and Closing as totals. The bridge computes 100+30-20=110 currency units. Checks: the returns bar decreases the running amount, the closing bar rests on the baseline, and the final label is 110 rather than 220.
Watch the distinction: Leaving the closing 110 as an ordinary increase adds it to the running amount again. Mark that bar as a total and verify the arithmetic outside the chart.
52. Map regional values
A filled map assigns values to recognized geographic areas. Provide an unambiguous location field, a numeric measure, and enough geographic context to distinguish places with similar names. For US states, separate state and country fields or fully qualified labels help Excel identify the intended geography. Inspect any unresolved location before treating blank map regions as zero; a missing shape may indicate a recognition problem instead of missing sales. Confirm the chart's legend and color scale, because a value twice another value does not necessarily appear twice as dark. The map shows where amounts differ, but large geographic areas can attract attention regardless of their sales. Use labels or an accompanying table when exact comparisons matter. Map charts appear in the MO-201 Office 2019 guide scope; the supplied MO-211 outline does not name them.
Worked example: In an Office 2019 workbook, enter State, Country, Sales rows California, United States, 40 and Nevada, United States, 20, with sales in hypothetical currency units. Create a filled map using the location columns and Sales. Checks: both states resolve in the United States, the legend associates them with 40 and 20, and the source table confirms California's value is 40/20=2 times Nevada's.
Watch the distinction: A darker color is not a precise arithmetic scale. Read the mapped values and legend, and investigate an absent state before calling its sales zero.
Reference: [2]
53. Create a PivotTable summary
A PivotTable summarizes records by fields rather than by fixed cell positions. Start with one header row, consistent field names, and no blank or subtotal rows mixed into the source. Put a descriptive field such as Region in Rows and a numeric field such as Amount in Values. Check the value field setting: Excel may default to Count when the source column includes text or mixed values, even when Sum is intended. A PivotTable calculates category totals from its current source and filters; it does not alter the source records. Refresh after the source changes, and verify the source range includes appended rows. A structured table often makes that range easier to maintain. Reconcile the PivotTable grand total with the source amounts before using it in a report. This catches missing records, text-formatted numbers, and unintended filters.
Worked example: Enter headers Region and Amount, then East 10, West 20, East 30 in three rows. Insert a PivotTable from the full source, place Region in Rows, and set Amount in Values to Sum. It displays East 40, West 20, grand total 60. Checks: both regions appear, East equals 10+30, and the grand total equals 10+20+30.
Watch the distinction: A PivotTable showing East 2 is likely counting records rather than summing amounts. Inspect Value Field Settings and the numeric source data before changing the layout.
54. Change PivotTable field layout
Moving fields among Rows, Columns, Filters, and Values changes the question a PivotTable answers without editing its source. Rows list one dimension vertically, Columns split each row by another dimension, Filters restrict the whole view, and Values define the measure and aggregation. Decide the question first: a Region by Month grid needs Region in Rows, Month in Columns, and sales in Values as Sum. Check row totals, column totals, and the grand total against each other. If a month is missing, inspect active filters and source entries before assuming there were no sales. Moving Month to Filters would show one selected month at a time instead of a cross-tabulation. Multiple value fields can be useful, but label each measure clearly so a count is not confused with a sum. Refresh after changing the underlying records.
Worked example: Enter Region, Month, Sales rows East Jan 10; East Feb 20; West Jan 30; West Feb 40. Build a PivotTable with Region in Rows, Month in Columns, and Sum of Sales in Values. East totals 30, West 70, Jan 40, Feb 60, and grand total 100. Checks: four intersections show their source amounts, 30+70=100, and 40+60=100.
Watch the distinction: Putting Month in Filters cannot display January and February side by side. Move it to Columns when the intended comparison is across months.
55. Configure a value field
A PivotTable value field needs both an aggregation and a number format. Sum adds numeric entries, Count reports how many nonempty entries contribute, and Average divides the numeric total by the numeric observation count. Choose the aggregation from the business question rather than from the source column's name. Value Field Settings controls this calculation; formatting controls only how its result appears. Apply number formatting through the value field so it persists more reliably as the PivotTable changes. Check for blanks and text values before interpreting Count or Average, because the eligible observations may differ. An average of transactions is not the same as total revenue, and an average of pre-aggregated regional totals may not equal the average individual transaction. For the MO-211 Microsoft 365 Apps exam, configuring value field settings is an explicit outline skill.
Worked example: Enter Sales values 10, 20, and 30 in a numeric source column. Add Sales to a PivotTable's Values area and switch Value Field Settings among Sum, Count, and Average. The results are 60 currency units, 3 records, and 20 currency units per record. Checks: 10+20+30=60, three cells contribute, and 60/3=20. Format Sum and Average as currency, Count as an integer.
Watch the distinction: Applying a currency format to Count can display a monetary symbol beside 3, but the measure remains a record count. Change the aggregation when revenue is required.
Reference: [4]
56. Filter with a slicer
A slicer presents a PivotTable filter as selectable buttons, making the active choice visible. Create it from a categorical field, then select one or more items to restrict the connected PivotTable. The values area recalculates for the displayed subset, while the underlying source remains unchanged. Clear the slicer filter to restore the full view. If several PivotTables should respond together, inspect the slicer's report connections; a slicer connected to only one table can leave charts and totals out of sync. Verify the filtered total against the contributing source rows, especially when multiple slicer selections or other filters are active. A slicer can show an item with no current data depending on settings, so an empty result should prompt a filter and source check. Use a descriptive slicer caption so readers know which field is being filtered.
Worked example: Enter Region and Sales rows East 10, East 30, West 20. Build a PivotTable with Sum of Sales by Region, then add a Region slicer. Select East: the visible total is 10+30=40. Clear the selection: the total returns to 60. Checks: East is visibly selected, West is excluded from the filtered total, and clearing restores all three records.
Watch the distinction: A slicer selection does not delete West's source row. If the total stays 40 after clearing it, inspect other filters or refresh the PivotTable.
57. Group PivotTable dates
Grouping a PivotTable date field turns daily records into periods such as months, quarters, or years while preserving the original dates in the source. Use genuine Excel dates in a consistent date column, then group the PivotTable field by the periods needed for the question. Select Years along with Months when the data span multiple years; otherwise January from different years may appear together in an unhelpful way. Reconcile grouped totals with the daily records and check whether a filter has excluded dates. If grouping fails, inspect blanks, text that only looks like a date, and error values. Formatting a text string as a date does not turn it into a date serial. Refresh after source corrections. Grouping changes the summary view, so you can still return to individual daily records or drill into a period total.
Worked example: Enter Date and Sales rows 2026-01-05 10, 2026-01-20 15, and 2026-02-02 20, with dates stored as Excel dates. Create a PivotTable, put Date in Rows and Sum of Sales in Values, then group by Months and Years. January 2026 is 25, February 2026 is 20. Checks: both month labels appear, 10+15=25, and grand total is 45.
Watch the distinction: A cell displaying 2026-01-05 may still contain text. If date grouping fails, test the source type and convert the entries to actual dates before grouping.
58. Add a calculated field
A standard PivotTable calculated field defines a measure from fields in the source records. For an additive measure such as Profit = Sales - Cost, Excel can aggregate the underlying Sales and Cost amounts and return the same difference at a subtotal or grand total. Create the field using source field names, then place it in Values and format it as money. Check a few source rows and the grand total independently. Do not assume this behavior makes every calculated expression suitable: a ratio of summed fields can differ from the average of row-level ratios, so define the intended denominator before building a margin measure. Calculated fields are distinct from typing a formula next to the displayed PivotTable, which may break when the layout or filter changes. If the source changes, refresh the PivotTable before evaluating the calculated result.
Worked example: Create a source table with headers Sales and Cost and rows 100, 60 and 50, 20, in hypothetical currency units. Insert a PivotTable and add a calculated field named Profit with formula =Sales-Cost. The row contributions are 40 and 30, and the grand total is 70. Checks: the field appears in Values, 150-80=70, and the result remains 70 after a refresh.
Watch the distinction: Typing =100-60 beside a displayed PivotTable is not a calculated field and will not adapt to a changed summary. Define Profit from source fields.
59. Configure a PivotChart
A PivotChart visualizes a PivotTable's current summary and responds to its field layout and filters. Build a sound PivotTable first, confirm its aggregation, then insert a chart type suited to the measure. Columns work for comparing a small number of regional totals. Give the chart a title naming both measure and category, and format the value axis with the measure's units. Field buttons can help interactive analysis or be hidden for a cleaner presentation; either choice should leave the active filter understandable. Apply a style that keeps category labels legible and does not imply unsupported precision. A connected slicer can change both the table and chart. After filtering, verify that each plotted point matches its PivotTable value. If the chart does not update, check the slicer connection and whether the PivotTable itself refreshed.
Worked example: Enter Region, Sales rows East 10, East 30, West 20. Build a PivotTable with Sum of Sales by Region, then insert a clustered column PivotChart titled Sales by region. Format its value axis as currency. Initially the bars are East 40 and West 20. Select East in a connected Region slicer. Checks: one bar remains, its value is 40, and the PivotTable total is also 40.
Watch the distinction: A chart title saying Sales does not prove the value field uses Sum. Check the PivotTable aggregation and the plotted values before presenting the chart.
60. Drill into a PivotChart result
A PivotChart point represents an aggregate, so investigating an unexpected bar starts with its connected PivotTable. Locate the corresponding value cell and use Show Details to generate the contributing source records in a new sheet. Sum those records independently and inspect their category fields, dates, and filters. This distinguishes a genuine source change from a chart presentation issue. A missing record may indicate an incomplete source range or stale PivotTable; an extra record may contain a wrong category or an unexpected duplicate. The detail sheet is an inspection result, not a correction to the original source. Fix the source or PivotTable settings, refresh, and verify the chart again. For the MOS practice questions, rehearse this path from chart value to source evidence instead of relying only on the bar height.
Worked example: Enter Region, Amount rows East 10, East 30, West 20. Build a PivotTable and PivotChart showing East 40 and West 20. From the East value cell in the connected PivotTable, use Show Details. The detail sheet should contain East 10 and East 30; 10+30=40. Checks: exactly two East records appear, their sum matches the bar, and the West 20 record is absent.
Watch the distinction: A chart bar of 40 does not reveal which records produced it. Inspect the PivotTable detail before changing the chart or assuming the source is correct.
Official references and further reading
- MO-201: Microsoft Excel Expert (Office 2019) - Certifications | Microsoft Learn
- mos-current-mo201-skills-native
- Exam MO 211: Microsoft Excel Expert (Microsoft 365 Apps) - Certifications | Microsoft Learn
- mos-current-mo211-skills-native