Excel Data Analysis & Reporting

Build Efficient Excel Reports and Gain Clearer Insights from Business Data

This hands-on training course gives you the core skills needed to prepare data for analysis and build efficient reports in Microsoft Excel. You will learn how to group data, split and combine columns, transform information with Power Query, work with the Excel Data Model, use lookup tools, and summarise data with Pivot Tables and Pivot Charts.

Whether you prepare regular reports, work with large datasets or want to extend your formula knowledge, this course builds practical analysis and reporting capability so you can spend less time cleansing data and more time working with useful insights.

Excel Data Analysis & Reporting is ideal for people who:

• Analyse business data in Excel
• Prepare regular workplace reports
• Work with large datasets
• Want to take formula knowledge to the next level
• Need more efficient ways to cleanse, combine and report on data

To attend Excel Data Analysis & Reporting, you should:

• Have knowledge equivalent to Excel Essential Skills and Excel Timesaving Features
• Understand formulas, including absolute references ($A$1)
• Understand common functions such as AVERAGE, MAX, COUNT and IF

After completing Excel Data Analysis & Reporting, you will be able to:

• Group and restructure data for easier analysis
• Clean, split and combine information efficiently
• Transform and connect data with Power Query and the Excel Data Model
• Retrieve and summarise information using lookup tools, Pivot Tables and Pivot Charts

Grouping and Structuring Data

• Group data by region, month or topic
• Hide and unhide columns to focus on relevant information

Splitting, Combining and Filling Data

• Split and combine information in columns
• Prepare inconsistent text for reporting
• Use Flash Fill and pattern recognition

Transforming Data with Power Query

• Clean data in Power Query Editor
• Split and combine columns
• Calculate new columns

Working with the Excel Data Model

• Connect multiple data sources
• Transform data for broader analysis and reporting
• Understand Excel as a relational data model
• Work with data from multiple sources

Looking Up and Retrieving Data

• Use VLOOKUP to retrieve information from another table
• Use XLOOKUP to search across data tables more flexibly

Pivot Tables

• Summarise cleansed data using Pivot Tables
• Group, filter and arrange data to answer reporting questions

Pivot Charts

• Use Pivot Charts to visualise Pivot Table summaries clearly and make report trends easier to understand
• Add Pivot Charts to dashboards and reports so key patterns can be presented visually.