Microsoft Excel Data Analysis
Overview
In this course we assume the knowledge from our Microsoft Excel Essentials course. This course covers data analysis using Tables and Pivot Tables. It will use functions used frequently when analysing data such as IFS, Lookups and the M365 #SPILL functions of Sort and Filter. This course will also give you an introduction to Excel feature called Power Query.
Course Objectives
In this course, participants will learn how to efficiently convert data ranges into tables, facilitating organized data management. They will also gain the skills to perform in-depth data analysis by harnessing the power of Pivot Tables. Additionally, students will explore advanced functions using LOOKUPS and IFS as well as the new M365 #SPILL formula.
Content
Formatting
- Tips and Tricks
- Custom Formatting Options
- Splitting Data Options
Calculations
- Revision of Cell Ref and Basic Daily Functions
- Auditing Tools
- CleanUp Functions such as TRIM, UPPER & LOWER
Dynamic Ranges
- Revsion of Tables
- Table Style Options incl. Total Row
- Use of Table Names in Functions
- Referring To Tables In Other Sheets
Functions
- Countif
- Countifs
- Sumif
- Sumifs
Advanced Functions
- IF Statements
- Nested IF and IFS (365)
- VLOOKUP & HLOOKUP Functions
- XLOOKUP Function
Working with Lists
- UNIQUE, SORT and FILTER #SPILL formulas
- Advanced Conditional Formatting
- Subtotal Command & Aggregate Function
Data Tools
- Data Validation
- Lists (dropdowns)
Pivot Tables
- Creating and Formatting Pivot Tables
- Calculations in a Pivot Tables
- Slicers and Timelines
- Working with Dates
Charts
- Creating different types of Charts
- Primary and Secondary Axis
Power Query
- GET & TRANSFORM
- Getting started with the Power Query Editor interface
- Split Columns
- Format & TRIM
- Unpivot Columns
- Append multiple sheets
You will learn how to
- Custom format cells
- Create conditional formatting rules to help present your data
- Create and use named cells and ranges
- Use text functions to increase your productivity when working on imported data
- Create and use Tables in Excel
- Create and edit IF and nested IF (IFS) functions
- Create and edit LOOKUP functions
- Protect your data and your workbooks
- Work with Pivot tables to analyse your lists
- Use of Power Query
- … and much more
Audience
Essential to have a good understanding of Excel before attending this course. Please ensure you are familiar with all the topics on our Excel Essentials Course.
If you are using Excel everyday to a good level and would like to be better and know more about formula and data manipulation, this course if perfect for you.
Duration
2 Day or 4 x 3 Hour Sessions
Contact Us
We’ll reply quickly.
Enquiry
I understand that my personal data is being processed in accordance with the privacy notice and accept the terms and conditions of use.