MS Excel Exam Guide: Analyzing and Visualizing Data

Explore the Northeast mortgage lenders company background, five east coast locations, and condo loan types, and learn to analyze year-over-year trends from data sets using Power Query and Power Pivot.

Explore manual lookups in Excel with vlookup and tables to retrieve unit types for mortgages, using exact matches, locked ranges, and efficient formula filling.

Create and use Excel tables to simplify VLOOKUP by referencing table names, enabling automatic column population and clean data lookups, while understanding table limitations for multi-tab data.

Spot the limitations of manual lookups and breakable columns in Excel, and see how Power Query and Power Pivot enable transforming and merging data across sources.

Discover how to use Power Query to query data across multiple files, create reusable connections, and transform data with queries and connections for scalable analysis.

Learn to transform and clean mortgage data with Power Query, splitting addresses into city, state, and zip, trimming and replacing values, and building steps for year over year analysis.

Apply advanced transformations in Power Query by using append and merge to combine Q1 2020 and Q2 2020 data, creating a master dataset for lookups via left outer joins.

Use power query to pivot and unpivot a wide sales dataset into a tall format, then group by salesperson to find each maximum monthly sale.

Use power query to import csvs and multiple files, then transform, append, and merge data for a repeatable analysis workflow.

Explore the Excel data model, Power Pivot, and Power Query workflow to relate multiple tables, handle large datasets, and perform deeper analysis with the data model.

Explore how to build and manage relationships in a Power Pivot data model, creating one-to-many links to analyze mortgages, loans, units, and employees via pivot tables.

Learn how DAX measures in a data model drive pivot table analysis, and how to create explicit measures versus implicit ones for flexible cost insights.

Explore common DAX functions and concepts like explicit measures, calculated columns, and safe division using Power Pivot data models, relationships, and the some X function in real pivot table scenarios.

Learn to work with dates in Excel by using a calendar table and Power Pivot to analyze time trends, relate dates, and compute days to sale.

Explore advanced dax functions using the calculate function to set context with filters, compare total cost and overall sales, and analyze data by offer date through relationships and the calendar.

Explore advanced DAX techniques in Excel, using calculate to build percent of total margins, sales last year, year-over-year growth, and running totals, with safe divide and isblank guards.

Create key performance indicators (KPIs) in Excel using Power Pivot and pivot tables by defining a measure, a target, and a visual status.

Explore organizing data with hierarchies in pivot tables. Create geo and date hierarchies from state to city to zip to address for drill-down insights.

Optimize your Excel data model by reducing data volume, dropping unnecessary rows and columns, breaking address into state, city, and zip, and using explicit measures over materialized fields.

Master pivot tables in Excel by building reports from a data model, configuring fields, rows, columns, and values, using slicers and timelines for the Excel certification exam.

Present data with pivot charts for storytelling, using visual formats and conditional formatting, then complement with pivot tables and interactive slices across chart types.

Publish Excel workbooks to the Power BI service, export data models, and pin charts to dashboards with the Power BI publisher for Excel, enabling others to interact with shared data.