Manage & Format Data

This module is the heaviest on the MO-211 exam — and for good reason. In any real-world data role, raw data is never clean, never consistently formatted, and never immediately ready for analysis. This module gives you the tools to transform messy, inconsistent datasets into reliable, presentation-ready information.

We begin with smart data entry techniques: using Flash Fill to instantly reformat data by example, the Fill Series engine for generating sequences and projections, and the RANDARRAY() function to produce dynamic numeric datasets. We then move into formatting and validation, where you will enforce data quality through custom number formats, data validation rules, and structured grouping — all while using subtotals to extract meaningful summaries without restructuring your data. The module closes with advanced conditional formatting, where formula-driven rules allow you to build dynamic, self-updating visual dashboards directly inside the grid.

✅ What You Must Be Able To Do

By the end of this module, you must be able to:

• Define, scope, edit, and delete named ranges via the Name Manager and use them inside formulas, lists, and conditional formatting.
• Fill cells using Flash Fill and convert text to Linked Data Types (Stocks, Geography, Currencies) with the dot-operator formula syntax.
• Generate sequences and projections using advanced Fill Series options (Linear, Growth, Date).
• Produce arrays of random numeric data using RANDARRAY() and manage its volatile nature.
• Create custom number formats with the Positive;Negative;Zero;Text structure, conditional brackets, and color codes.
• Configure data validation rules — including dependent dropdowns via INDIRECT — with input messages and error alerts.
• Group and ungroup rows and columns to build collapsible worksheet outlines.
• Use the SUBTOTAL() and AGGREGATE() functions, and the Subtotal menu feature, to calculate grouped totals that respect filtered/hidden rows.
• Remove duplicate records using both the Remove Duplicates tool and the UNIQUE() function.
• Create and manage custom and formula-based conditional formatting rules, including rule priority and Stop If True.
• Convert ranges to Excel Tables and use structured references (Sales[@Amount], Sales[#Totals], etc.).
• Build Advanced Filter criteria ranges (AND/OR logic) and choose in-place vs. copy-to-location.
• Pull and shape data with Power Query: Promote Headers, Filter, Split Column, Group By, Pivot/Unpivot, Merge, and Append.