Analyzing and Visualizing Data with Excel

Build Modern Excel Reporting Skills with Power Query, Power Pivot and DAX

This practical training course gives you advanced skills for applying business intelligence techniques to Excel data analysis. You will learn how to extend traditional reports, build an Excel data model, import and transform data, create DAX measures and design interactive visualisations.

Whether you are an analyst, manager, data scientist or experienced Excel user, this course helps you move from formula-heavy reports to refreshable data models and dashboards, so you can spend less time maintaining reports and more time finding useful insights.

Analyzing and Visualizing Data with Excel is ideal for people who:

• Use Excel to analyse and visualise data
• Create PivotTables, PivotCharts or dashboards
• Work with intermediate to advanced formulas
• Need to combine data from multiple tables or sources
• Want to create refreshable, interactive reports

To attend Analyzing and Visualizing Data with Excel, you should:

• Be comfortable writing intermediate to advanced Excel formulas
• Be able to create a successful VLOOKUP
• Have used or created a PivotTable or PivotChart
• Want to work with data from multiple tables or sources
• Have knowledge equivalent to the named prerequisite Excel courses, or equivalent experience

After completing Analyzing and Visualizing Data with Excel, you will be able to:

• Extend a classic Excel dashboard
• Build and extend an Excel data model
• Import and transform data with Power Query
• Create calculated columns and measures with DAX
• Create interactive Excel visualisations and publish an Excel report to Power BI

Module 1: Review Classic Excel Analysis

• Review Excel Tables and VLOOKUP
• Use PivotTables, PivotCharts and slicers
• Discuss limitations of traditional reporting approaches

Module 2: Build the Excel Data Model

• Use the Excel data model
• Create relationships between data sources
• Use Power Pivot to format and analyse data

Module 3: Import and Transform Data with Power Query

• Import CSV files and folders
• Navigate Power Query settings and applied steps
• Split, fill, copy and unpivot data
• Refresh transformed data

Module 4: Import Data from Databases and Reports

• Import data from multiple sources
• Create relationships between tables
• Use a date table and explore hierarchies
• Prepare existing Excel reports for analysis

Module 5: Create DAX Measures

• Distinguish calculated columns from measures
• Create measures with DAX
• Use CALCULATE, DATEADD and DATESYTD
• Calculate comparisons, year-to-date totals and market share

Module 6: Create Interactive Excel Dashboards

• Create and format PivotCharts
• Use slicers to filter multiple charts
• Choose suitable chart types
• Explore Cube Functions as optional content

Module 7: Use Excel with Power BI

• Publish an Excel report to the Power BI service
• View the report online
• Recognise how Power BI can extend Excel reporting