MOS Study Guide for Microsoft Excel Exam MO-200

Microsoft Exam MO-200 Course Outline

The exam MO-200 will evaluate the candidates on the basis of below-mentioned domains

1. Manage worksheets and workbooks (10-15%)

Import data into workbooks (Microsoft Documentation: Get data from Excel workbook files)

• Import data from .txt files (Microsoft Reference: Import or export text (.txt ) files)
• Import data from .csv files (Microsoft Reference: Import or export text (.csv) files)

Navigate within workbooks (Microsoft Reference: Two tips for faster worksheet navigation)

• Search for data within a workbook (Microsoft Reference: Find or replace text and numbers on a worksheet)
• Navigate to named cells, ranges, or workbook elements (Microsoft Reference: Find named ranges)
• Insert and remove hyperlinks (Microsoft Reference: Remove or turn off hyperlinks)

Format worksheets and workbooks (Microsoft Reference: Ways to format a worksheet)

• Modify page setup (Microsoft Documentation: Set page setup attributes for more than one sheet in Excel)
• Adjust row height and column width (Microsoft Reference: Change the column width and row height)
• Customize headers and footers (Microsoft Reference: Insert a header or footer)

Customize options and views (Microsoft Reference: Create, change, or customize a view)

• Customize the Quick Access toolbar (Microsoft Reference: Customize the Quick Access Toolbar)
• Display and modify workbook content in different views (Microsoft Reference: Create, apply, or delete a custom view)
• Freeze worksheet rows and columns (Microsoft Reference: Freeze the first two columns)
• Change window views (Microsoft Reference: View display settings in Windows 10)
• Modify basic workbook properties (Microsoft Reference: View or change the properties for an Office file)
• Display formulas (Microsoft Reference: Display or hide formulas)

Configure content for collaboration (Microsoft Documentation: Collaborate with guests on a document)

• Set a print area (Microsoft Reference: Set or clear a print area on a worksheet)
• Save workbooks in alternative file formats (Microsoft Reference: Save a workbook in another file format)
• Configure print settings (Microsoft Documentation: Use Group Policy settings to control printers in Active Directory)
• Inspect workbooks for issues (Microsoft Reference: Remove hidden data and personal information by inspecting documents, presentations, or workbooks)

2. Manage data cells and ranges (20-25%)

Manipulate data in worksheets

• Paste data by using special paste options (Microsoft Reference: Paste options)
• Fill cells by using Auto Fill
• Insert and delete multiple columns or rows (Microsoft Reference: Insert or delete rows, and columns)
• Insert and delete cells (Microsoft Reference: Insert or delete rows, and columns)

Format cells and ranges (Microsoft Reference: Highlight patterns and trends with conditional formatting)

• Merge and unmerge cells (Microsoft Reference: Merge and unmerge cells)
• Modify cell alignment, orientation, and indentation (Microsoft Reference: Align or rotate text in a cell)
• Format cells by using Format Painter (Microsoft Reference: Use the Format Painter)
• Wrap text within cells (Microsoft Reference: Wrap text in a cell)
• Apply number formats (Microsoft Reference: Create and apply a custom number format)
• Apply cell formats from the Format Cells dialog box (Microsoft Documentation: Applying Borders)
• Apply cell styles (Microsoft Reference: Apply, create, or remove a cell style)
• Clear cell formatting (Microsoft Reference: Clear cells of contents or formats)

Define and reference named ranges

• Define a named range (Microsoft Reference: Define and use names in formulas)
• Name a table

Summarize data visually (Microsoft Documentation: Summarize Query Results (Visual Database Tools))

• Insert Sparklines (Microsoft Reference: Create sparklines)
• Apply built-in conditional formatting (Microsoft Reference: Use conditional formatting to highlight information)
• Remove conditional formatting

3. Manage tables and table data (15-20%)

Create and format tables

• Create Excel tables from cell ranges (Microsoft Reference: Create and format tables)
• Apply table styles (Microsoft Reference: Apply a table style)
• Convert tables to cell ranges (Microsoft Reference: Convert to Range)

Modify tables (Microsoft Reference: Create or modify tables or indexes by using a data-definition query)

• Add or remove table rows and columns (Microsoft Reference: Add a column)
• Configure table style options (Microsoft Reference: Format an Excel table)
• Insert and configure total rows (Microsoft Reference: Total the data in an Excel table)

Filter and sort table data (Microsoft Reference: Sort data in a table)

• Filter records (Microsoft Documentation: Sorting, Searching, and Filtering)
• Sort data by multiple columns (Microsoft Reference: Sort data in a table)

4. Perform operations by using formulas and functions (20-25%)

Insert references (Microsoft Reference: Add citations to your document)

• Insert relative, absolute, and mixed references (Microsoft Reference: Switch between relative, absolute, and mixed references)
• Reference named ranges and named tables in formulas (Microsoft Reference: Define and use names in formulas)

Calculate and transform data (Microsoft Reference: Get & Transform in Excel)

• Perform calculations by using the AVERAGE (), MAX (), MIN (), and SUM () functions (Microsoft Documentation: Average, Max, Min, StdevP, Sum, and VarP functions in Power Apps)
• Count cells by using the COUNT (), COUNTA (), and COUNTBLANK () functions
• Perform conditional operations by using the IF () function (Microsoft Reference: IF function)

Format and modify text (Microsoft Reference: Customize or create new styles)

• Format text by using RIGHT (), LEFT (), and MID () functions
• Formatting text by using UPPER (), LOWER (), and LEN () functions (Microsoft Reference: Text functions (reference))
• Format text by using the CONCAT () and TEXTJOIN () functions (Microsoft Reference: TEXTJOIN function)

5. Manage charts (20-25%)

Create charts (Microsoft Reference: Create a chart from start to finish)

• Create charts (Microsoft Reference: Create a chart with recommended charts)
• Create chart sheets

Modify charts (Microsoft Reference: Change the chart type of an existing chart)

• Add data series to charts (Microsoft Reference: Add a data series to your chart)
• Switch between rows and columns in source data (Microsoft Reference: Change how rows and columns of data are plotted in a chart)
• Add and modify chart elements (Microsoft Reference: Format elements of a chart)

Format charts (Microsoft Reference: Present data in a chart)

• Apply chart layouts (Microsoft Reference: Change the layout or style of a chart)
• Apply chart styles
• Add alternative text to charts for accessibility (Microsoft Reference: Add alternative text to a shape, picture, chart, SmartArt graphic, or other objects)