MO-210 Microsoft Excel Associate Cheat Sheet

Manage Worksheets and Workbooks

25-30% of exam
Import data Workbook navigation Page setup Workbook views Print settings Collaboration

Manage Data Cells and Ranges

25-30% of exam
Paste options Auto Fill Cell formatting Named ranges Sparklines Conditional formatting

Manage Tables and Table Data

10-15% of exam
Create tables Table styles Table structure Total Row Filtering Multilevel sorting

Formulas and Functions

15-20% of exam
Cell references Structured references Calculations Counting Dynamic arrays Text functions

Manage Charts

15-20% of exam
Create charts Chart sheets Data series Chart elements Layouts and styles Alternative text

Quick Facts

Exam MO-210
Credential Excel Associate
Product Microsoft 365 Apps
Level Intermediate
Assessment time 50 minutes
Delivery Proctored
Components May be interactive
Scheduling Through Certiport
Experience guidance About 150 hours
Renewal frequency 60 months
Price Varies by region

Comments vs Notes WO

Comments

Threaded conversations
Supports replies
Collaboration focused

Notes

Legacy annotations
No threaded replies
Cell reminder focused
Discussion vs annotation

Choose the Data Command WO

Text file named → Use From Text/CSV
Online table named → Use From Web
Exact text sought → Use Find
Named range sought → Use Go To
Print subset requested → Set Print Area
Hidden content suspected → Inspect Workbook

Import Data WO

Text file Use From Text/CSV
Online source Use From Web
Delimiter Controls column splitting
File origin Controls character encoding
Data preview Verify rows before loading
Navigator Select detected online table
Load Imports to default destination
Load To Chooses destination and type
Transform Data Opens Power Query Editor

Workbook Navigation WO

Find Searches workbook content
Find All Lists every match
Go To Jumps to named destination
Name Box Navigates to address or name
Named cell Meaningful address alias
Hyperlink Opens location or resource
Remove Hyperlink Removes link behavior
Workbook scope Searches every worksheet

Workbook Format and Views WO

Page orientation Portrait or landscape
Margins Control page whitespace
Paper size Matches output medium
Scaling Fits content to pages
Row height Controls vertical cell size
Column width Controls horizontal cell size
Header/footer Adds repeating page content
Quick Access Toolbar Stores frequent commands
Normal view Standard editing grid
Page Layout view Shows printed page layout
Page Break Preview Shows printable page divisions
Freeze Panes Keeps headings visible
New Window Duplicates workbook view
Arrange All Tiles open windows
Workbook properties Stores title and author
Show Formulas Displays formulas, not results

Distribution and Collaboration WO

Print area Limits printed cells
Print titles Repeats headings each page
Print selection Prints selected cells only
Print active sheets Prints selected worksheets
Export PDF Creates fixed-layout output
Save As Creates alternate file format
Inspect Workbook Finds hidden or private content
Document Inspector Reports removable workbook issues
Comment Supports threaded discussion
Note Stores legacy annotation

Clear Contents vs Formats CE

Clear Contents

Removes values
Keeps formatting
Leaves cells present

Clear Formats

Keeps values
Removes appearance
Leaves cells present
Erase data vs erase styling

Manipulate Worksheet Data CE

Paste Values Pastes results, not formulas
Paste Formulas Pastes formulas only
Paste Formats Pastes appearance only
Transpose Swaps rows and columns
Auto Fill Extends values or patterns
Fill handle Drags copied pattern
Insert cells Shifts neighboring cells
Delete cells Closes removed-cell gap
Insert rows Adds complete worksheet rows
Delete columns Removes complete worksheet columns
RANDBETWEEN(bottom,top) Inclusive integer; recalculates
SEQUENCE(rows,[columns],[start],[step]) Spills a configurable sequence

Format Cells and Ranges CE

Merge Cells Keeps only upper-left content
Unmerge Cells Restores individual cells
Horizontal alignment Positions content left-to-right
Vertical alignment Positions content top-to-bottom
Orientation Rotates cell content
Indent Offsets cell content
Format Painter Copies formatting
Wrap Text Displays content across lines
Number format Changes displayed value style
Format Cells Opens detailed formatting controls
Cell style Applies reusable format bundle
Clear Formats Removes formatting, keeps content
Grouped sheets Formats worksheets together

Names and Visual Summaries CE

Define Name Creates named reference
Name Manager Edits and deletes names
Workbook name Available across workbook
Worksheet name Available within one sheet
Named reference Use directly in formulas
Sparkline In-cell miniature chart
Line Sparkline Shows trend direction
Column Sparkline Compares compact values
Win/Loss Sparkline Shows sign, not magnitude
Data bars Visualize relative magnitude
Color scales Map values to colors
Icon sets Classify values visually
Clear Rules Removes conditional formatting

Table Workflow TA

Create → Style → Modify → Filter
Confirm headers Name table Check Total Row

Range vs Table TA

Range

Ordinary cells
Manual expansion
Cell references typical

Table

Named structured object
Automatic expansion
Built-in filters
Plain cells vs managed records

Table Operations TA

Create Table Converts range to table
My table has headers Preserves header row
Table name Identifies table in formulas
Table style Applies coordinated formatting
Header Row Shows table field names
Banded Rows Alternates row formatting
First Column Emphasizes first field
Convert to Range Removes table behavior
Table row Expands table vertically
Table column Expands table horizontally
Total Row Adds table summaries
Total dropdown Selects summary function
Filter Shows matching records
Multi-column sort Applies ordered sort levels

Filter vs Sort TA

Filter

Hides unmatched records
Keeps row order
Can be cleared

Sort

Reorders records
Uses ordered levels
Does not hide
Show subset vs reorder records

Dollar Sign Locks FO

$ before column locks column
$A1: column A$1: row $A$1: both

Relative vs Absolute References FO

Relative

Written A1
Moves when copied
Default reference type

Absolute

Written $A$1
Does not shift when copied
Locks shared constant
Moving target vs fixed target

Choose the Reference FO

Everything should move → Use A1
Both stay fixed when copied → Use $A$1
Column stays fixed → Use $A1
Row stays fixed → Use A$1
Table field requested → Use structured reference
Lock uncertain → Cycle with F4

Formula References FO

Relative reference A1 shifts when copied
Absolute reference $A$1 stays fixed when copied
Mixed column lock $A1 locks column
Mixed row lock A$1 locks row
F4 Cycles locks while editing formula
Structured reference Uses table field names
Table column Table1[Amount]
Current row [@Amount]
Formula fill Adjusts unlocked references

Count Family FO

COUNT Numbers | COUNTA Values | COUNTBLANK Blank-like
COUNT: numbers/dates COUNTA: includes errors and "" COUNTBLANK: blanks and ""

COUNT vs COUNTA FO

COUNT

Counts numbers
Ignores text
Ignores blanks

COUNTA

Counts values and errors
Includes text and numbers
Counts formula empty text
Numeric cells vs populated cells

Choose the Function FO

Add values → Use SUM
Find mean → Use AVERAGE
Count numbers → Use COUNT
Count nonblanks → Use COUNTA
Return conditionally → Use IF
Sort with formula → Use SORT
Remove duplicates dynamically → Use UNIQUE
Join with separators → Use TEXTJOIN

Calculation Functions FO

SUM(range) Adds numeric values
AVERAGE(range) Returns arithmetic mean
MAX(range) Returns largest value
MIN(range) Returns smallest value
COUNT(range) Counts numeric cells
COUNTA(range) Counts values, errors, and empty text
COUNTBLANK(range) Counts blanks and formula empty text
IF(test,true,false) Returns conditional result
SORT(array) Returns sorted spill array
UNIQUE(array) Returns distinct spill values
Spill range Dynamic array output area
#SPILL! Array cannot occupy output range

Text Extraction FO

LEFT Starts | RIGHT Ends | MID Inside
LEFT: beginning RIGHT: ending MID: position

COUNTA vs COUNTBLANK FO

COUNTA

Counts any stored value
Counts errors
Counts formula result ""

COUNTBLANK

Counts truly empty cells
Also counts formula result ""
Does not count zero
Formula empty text counts in both

Numeric Function Edges FO

SUM references Ignores text, logicals, and blanks
AVERAGE references Ignores text and blanks; includes zero
AVERAGE with no numbers Returns #DIV/0!
MAX/MIN references Ignore text, logicals, and blanks
MAX/MIN with no numbers Return zero
COUNT references Counts numbers and dates only
Direct logical or numeric text Included when typed as arguments
Referenced errors Propagate in SUM, AVERAGE, MAX, MIN
COUNT and referenced errors COUNT ignores error cells
COUNTA and empty text Formula returning "" is counted
COUNTBLANK and empty text Formula returning "" is also counted
COUNTBLANK and zero Zero is not blank
IF logical-test error Error propagates to result
IF omitted branch May return zero or FALSE

CONCAT vs TEXTJOIN FO

CONCAT

Joins supplied text
No delimiter argument
No blank-control argument

TEXTJOIN

Adds chosen delimiter
Can ignore blanks
Joins ranges
Plain join vs delimited join

Random and Dynamic Array Edges FO

RANDBETWEEN bounds Both endpoints can be returned
RANDBETWEEN recalc New integer on worksheet calculation
Invalid random bounds Bottom above top returns #NUM!
SEQUENCE optional arguments Each defaults to one
SORT defaults First column, ascending, sorted by rows
UNIQUE default Returns each distinct row once
UNIQUE exactly_once TRUE keeps values occurring once only
Spill formula location Edit only the top-left anchor
Blocked spill Clear occupied cells; unmerge overlaps
Dynamic arrays in tables Spilled formulas belong outside tables
Closed linked workbook Dynamic array refresh returns #REF!

Text Functions FO

LEFT(text,count) Returns starting characters
RIGHT(text,count) Returns ending characters
MID(text,start,count) Returns middle characters
UPPER(text) Converts text uppercase
LOWER(text) Converts text lowercase
LEN(text) Counts text characters
CONCAT(values) Joins text values
TEXTJOIN(delimiter,ignore_empty,text) Joins with delimiter
Start number MID positions begin at one
Ignore empty TEXTJOIN can skip blanks

Text Function Edges FO

LEFT/RIGHT omitted count Defaults to one character
LEFT/RIGHT count zero Returns empty text
LEFT/RIGHT count negative Returns #VALUE!
LEFT/RIGHT count too large Returns the entire text
MID first position Start numbering begins at one
MID start past text Returns empty text
MID overrun Stops at the end of text
MID invalid arguments Low start or negative count: #VALUE!
LEN spaces Counts leading, internal, and trailing spaces
LEN empty text Returns zero
UPPER/LOWER Change letters; preserve nonletters
CONCAT Has no delimiter or ignore-empty controls
TEXTJOIN TRUE Skips empty cells in supplied ranges
Joined result over 32,767 CONCAT or TEXTJOIN returns #VALUE!
Referenced text error Propagates through these text functions

Chart Workflow CH

Select → Insert → Modify → Format
Select source Insert chart Modify elements Format appearance

Chart Object vs Chart Sheet CH

Chart object

Lives on worksheet
Moves over cells
Shares sheet space

Chart sheet

Dedicated chart tab
Fills sheet window
No worksheet cells
Embedded visual vs dedicated visual

Modify a Chart CH

Dataset missing → Add data series
Series orientation wrong → Switch Row/Column
Meaning unclear → Add chart elements
Arrangement requested → Apply Quick Layout
Visual preset requested → Apply Chart Style
Accessibility requested → Add alternative text

Chart Workflow CH

Source range Provides chart categories and values
Recommended Charts Suggests suitable chart types
Chart object Floats within worksheet
Chart sheet Occupies dedicated sheet
Select Data Edits series and categories
Add series Adds another plotted dataset
Switch Row/Column Swaps series orientation
Chart title States chart subject
Axis title Labels measurement axis
Legend Identifies data series
Data labels Show plotted values
Quick Layout Applies element arrangement
Chart style Applies coordinated appearance
Alternative text Describes chart accessibly

Common Traps

Loading without previewing

Wrong delimiter merges columns ≠ Verify preview before Load

Freezing the wrong panes

Selection controls frozen boundaries ≠ Select below and right

Clearing values accidentally

Clear Formats preserves values ≠ Delete key removes contents

Merging populated cells

Only upper-left content survives ≠ Move or preserve other values first

Grouping sheets unintentionally

Edits affect grouped sheets ≠ Ungroup after shared formatting

Forgetting table headers

Excel invents generic headers ≠ Confirm header checkbox

Typing totals below tables

Use actual Total Row ≠ Choose requested aggregate

Leaving constants relative

Copied formulas shift constants ≠ Lock with F4 while editing

Miscounting empty text

Both counters include formula "" ≠ Zero is not blank

Treating random values as fixed

RANDBETWEEN changes on recalculation ≠ Paste Values to freeze a result

Blocking dynamic arrays

Obstructions or tables can cause #SPILL! ≠ Clear, unmerge, or move the formula

Charting unwanted totals

Select intended source only ≠ Verify series before finishing

Skipping chart alternative text

Accessibility task remains incomplete ≠ Describe purpose concisely

Last Minute

1. Practice five official domains
2. Prioritize both 25-30% domains
3. Preview every data import
4. Know Find versus Go To
5. Rehearse page setup and printing
6. Separate comments from notes
7. Practice Paste Special choices
8. Ungroup worksheets after formatting
9. Define and reference names
10. Create tables with headers
11. Configure actual Total Rows
12. Cycle references using F4
13. Distinguish blanks, empty text, and zero
14. Memorize function defaults and error behavior
15. Clear or unmerge blocked spill ranges
16. Verify chart source series
17. Add requested chart elements
18. Include chart alternative text
19. Rehearse within 50 minutes

Microsoft Excel (Microsoft 365 Apps) (MO-210)

Practice Questions 233 questions Study Guide 38 topics Cheat Sheet 180 facts Flashcards 50 cards Free CE Podcast