Excel Data Analysis Course

Strong data analysis skills in Excel are among the most portable and in demand abilities in any office, because almost every team reports on numbers and Excel is already on every desk. Showing that you can clean messy data, build a PivotTable and present a clear chart marks you out for analyst, reporting and senior support roles, and it is the foundation that newer tools like Power BI build on.

With Logitrain you train live in real time with an experienced trainer, in person in Melbourne, Sydney and Brisbane, or live online anywhere in Australia. You keep the digital course material, every course is backed by our price beat guarantee, and if you want to go deeper afterwards you can step up to the Advanced Excel course.

There are no formal prerequisites for this course. To get the most from it, you should be comfortable opening a workbook, entering data and writing simple formulas such as SUM and basic cell references.

If you are new to Excel, start with the Microsoft Excel Intermediate course first, then return to this course to focus on analysis.

There is no certification exam associated with this course.

You receive the Excel Data Analysis course material in digital format, covering every topic with worked examples and sample datasets. It is yours to keep and use as a reference back at work.

• Clean, sort and structure raw data into reliable Excel tables
• Write analytical formulas and functions including SUMIFS, COUNTIFS, IF logic, INDEX and MATCH
• Use XLOOKUP and dynamic arrays such as FILTER, SORT and UNIQUE to summarise data
• Build and customise PivotTables to summarise large datasets quickly
• Add calculated fields, grouped items and custom calculations in PivotTables
• Create PivotCharts and interactive dashboards with slicers
• Import, transform and combine data from text, CSV and database sources with Power Query
• Build a Data Model and measures with Power Pivot
• Apply conditional formatting to highlight trends and outliers
• Filter and sort lists, including custom and advanced filters
• Run what-if analysis using scenarios, data tables, Goal Seek and Solver
• Calculate descriptive statistics, correlation and moving averages with the Analysis ToolPak
• Visualise results with charts, sparklines and trendlines
• Validate data entry and protect workbooks for safe sharing
• Automate repetitive analysis by recording and editing macros

This course is likely to add to the employment-related skills of the participants. The skills developed are likely to be used in the course of being an employee or working in a business.

• Aspiring and working data analysts
• Business analysts and reporting analysts
• Finance, accounting and payroll staff
• Marketing, sales and operations staff who report on numbers
• Administrators and office professionals
• Managers and team leaders who make decisions from data
• Career changers moving into data analytics
• Anyone preparing for a data analyst role or the Data Analyst Job Ready Program

Module 1: Getting started with data in Excel

• Excel data analysis options and data types
• Selecting, naming and managing ranges
• Custom number formats and named styles
• Custom templates and worksheet protection

Module 2: Organising worksheet data

• Creating and extending lists and Excel tables
• Sorting and custom sorts
• Consolidating data from multiple sheets
• Outlining and grouping data

Module 3: Evaluating worksheet data

• Conditional formatting to surface trends
• Summarising with subtotals
• Filtering lists with custom and advanced filters
• Creating scenarios
• Validating data entry

Module 4: Formulas and functions for analysis

• AutoSum and the Insert Function dialog
• Editing, evaluating and tracing formulas
• Conditional formulas and logical functions
• Lookup and reference functions including XLOOKUP, INDEX and MATCH
• Ranking and locating values within a list
• Dynamic arrays with FILTER, SORT and UNIQUE
• Building an amortisation table

Module 5: Working with external data and Power Query

• Importing delimited text and CSV files
• Splitting columns and cleaning imported data
• Importing from database tables
• Get and Transform with Power Query
• Refreshing and combining multiple data sources

Module 6: Charting and visualising data

• Chart basics and choosing the right chart type
• Custom chart types and combination charts
• Adding trendlines
• Sparklines and visual cues

Module 7: PivotTable reports

• PivotTable basics and field layout
• Building a PivotTable from a list
• Filtering, grouping and changing calculations
• Calculated fields and calculated items
• Retrieving values with GETPIVOTDATA

Module 8: PivotCharts and dashboards

• Creating PivotCharts
• Changing layout and chart type
• Filtering and grouping PivotChart fields
• Building an interactive dashboard with slicers

Module 9: The Data Model and Power Pivot

• Adding tables to the Data Model
• Creating relationships between tables
• Writing measures
• Summarising across multiple tables

Module 10: What-if and statistical analysis

• Goal Seek and Solver
• Data tables and scenario analysis
• Descriptive statistics with the Analysis ToolPak
• Correlation, covariance and moving averages

Module 11: Automating analysis with macros

• Recording and running macros
• Editing macros in the Visual Basic Editor
• Setting macro security
• Using an Excel function within a macro

Got a team to train? Logitrain can deliver the Excel Data Analysis Course in house at your premises anywhere in Australia, with the same experienced trainer, course material and hands-on datasets as the public courses. It suits teams who want everyone analysing data the same way, on dates that suit the business. Contact Logitrain to arrange in-house delivery.

What is the Excel Data Analysis Course?

It is a practical, instructor-led course that teaches you to clean, analyse and visualise data in Microsoft Excel using formulas, PivotTables, Power Query, Power Pivot, charts and dashboards, so you can answer business questions with confidence.

Is there an exam included in the Excel Data Analysis Course?

No. There is no certification exam for this course. You focus on hands-on skills, receive a Certificate of Attendance and keep your digital course material.

Do I need experience before starting the Excel Data Analysis Course?

There are no formal prerequisites. You should be comfortable entering data and writing simple formulas. If you are new to Excel, complete the Microsoft Excel Intermediate course first.

Which version of Excel does the course use?

Training uses the current version of Microsoft Excel, including modern tools such as XLOOKUP, dynamic arrays, Power Query and Power Pivot. The skills also apply to recent earlier versions.

How long is the Excel Data Analysis Course?

It runs for two days full-time, or two weeks part-time if you study in the evenings or on weekends.

Where can I attend the Excel Data Analysis Course?

You can attend in person in Melbourne, Sydney, Brisbane, Adelaide, Perth and Canberra, or live online anywhere in Australia, all delivered live in real time with a trainer.

Should I do the Intermediate Excel course or the Excel Data Analysis Course first?

If you are still building core skills, start with the Microsoft Excel Intermediate course. If you already work with formulas and tables and want to focus on analysis, this course is the right level.

Is this course or Power BI the better choice for reporting?

Start here if your data lives in Excel and you want stronger analysis and dashboards within it. Move on to the Power BI course when you need large, refreshable, interactive reports shared across an organisation.