Select Page

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

  • This field is for validation purposes and should be left unchanged.

I understand that my personal data is being processed in accordance with the privacy notice and accept the terms and conditions of use.

0
YOUR CART
  • No products in the cart.