Manage Worksheets and Workbooks
25-30%of exam
Manage Data Cells and Ranges
25-30%of exam
Paste optionsAuto FillCell formattingNamed rangesSparklinesConditional formatting
Manage Tables and Table Data
10-15%of exam
Create tablesTable stylesTable structureTotal RowFilteringMultilevel sorting
Formulas and Functions
15-20%of exam
Manage Charts
15-20%of exam
Create chartsChart sheetsData seriesChart elementsLayouts and stylesAlternative 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
Choose the Data Command
- 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
- 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 Format and Views
- 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
- 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
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
- 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
- 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
- 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
Create → Style → Modify → Filter
Confirm headersName tableCheck Total Row
Range vs Table
Range
- Ordinary cells
- Manual expansion
- Cell references typical
Table
- Named structured object
- Automatic expansion
- Built-in filters
Plain cells vs managed records
Table Operations
- 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
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
$ before column locks column
$A1: columnA$1: row$A$1: both
Relative vs Absolute References
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
- 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
- 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
COUNT Numbers | COUNTA Values | COUNTBLANK Blank-like
COUNT: numbers/datesCOUNTA: includes errors and ""COUNTBLANK: blanks and ""
COUNT vs COUNTA
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
- 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
- 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
LEFT Starts | RIGHT Ends | MID Inside
LEFT: beginningRIGHT: endingMID: position
COUNTA vs COUNTBLANK
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
- 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
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
- 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
- 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
- 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
Select → Insert → Modify → Format
Select sourceInsert chartModify elementsFormat appearance
Chart Object vs Chart Sheet
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
- 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
- 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
Take courses on UdemySame family resources
Explore More Microsoft Certifications
Continue into nearby exams from the same family. Each card keeps practice questions, study guides, flashcards, videos, and articles in one place.
More From This Family
Videos and articles for deeper review.
VideoFREE DP-600 Exam Guide 2026: Pass Microsoft Fabric Analytics Engineer (Direct Lake, DAX, Semantic Models)VideoFREE MD-102 Exam Guide 2026: Pass Microsoft Endpoint Administrator (Intune, Autopilot, Windows 11)VideoFREE MS-102 Exam Guide 2026: Pass Microsoft 365 Administrator (Entra, Defender XDR, Purview)VideoIs PL-300 Worth It in 2026? Power BI Salary & Career RealityArticlePL-200 Exam Guide 2026: Skills, Retirement, and Renewal12 min readArticlePL-400 Exam Guide 2026: Skills, Format, and Renewal12 min readArticlePL-900 Exam Guide 2026: Current Outline and Free Prep19 min readArticleMS-900 Retired 2026: FREE Guide + AB-900 Pivot17 min read
Comments vs Notes
WOComments
Notes
Discussion vs annotation