As an experienced Excel user, you may often use tools such as Pivot Tables, VLookups and Conditional Formatting. However, there are many other features in Excel which provide efficient and effective methods to cleanse, analyze and visualize large datasets. On this course you will learn about Advanced Filter, Custom Sort, Power Query, Advanced Function writing and What-If Analysis, all invaluable tools to help you work more extensively in Excel.

Duration: 1 day

Who is it for: This is an Advanced training course, designed for current users who are comfortable with all the key tools such as pivot tables and VLookups, but want to explore what else Excel has to offer which will help manage worksheets and perform calculations in large datasets

Layout: 1 Day – Live classroom or virtual classroom

Objectives

You will learn how to write more complex functions– using named ranges, structured referencing and nesting. You will use a range of Text Functions, Logical Functions and Lookup and Reference Functions. You will explore other tools such as Power Query to help cleanse data and work with advanced filtering and sorting, including some new array functions.

Modules

Advanced Filtering and Sorting

• Using Advanced Filter and wildcards
• Using Custom Sort options
• Array functions (*Office 365 only) UNIQUE, SORT, FILTER

Grouping and Subtotals

• Grouping and Outlining
• Using the Subtotals feature
• SUBTOTAL Function

Advanced Function Writing

• Named Ranges
• Advanced Lookup and Reference Functions
• Using Nested Logical Functions: IF, AND, OR, IFERROR, ISFunctions

Using Power Query to Profile and Cleanse Data

• Using Format as Table to load an Excel dataset into Power Query
• Power Query tools: Split columns, Remove Blank Rows, Trim
• Using Column Profiling to identify errors and blanks

Using What-If Analysis Tools

• Scenario Manager
• Goal Seek
• Data Tables

Exploring Excel Chart Types

• Creating charts using a range of chart types such as Waterfall charts, Combo Charts, Histograms and Treemaps
• Using Sparklines to analyze trends over time
• Using Conditional Formatting to visualize patterns and trends

Enquire about this course

More courses

course

Data Analysis and Visualisation

This course covers the necessary skills to help you perform cleansing, analysing and visualizing large datasets. You will work with pivot tables, conditional formatting and learn how to write some of the most powerful functions in Excel such as VLOOKUP and IF Functions.

course

Excel Essentials

Whether you work in Finance, HR, Technology or any other sector, you will most likely be using Excel spreadsheets to store, track, analyse and visualize data. This course will get you started with the essentials and help you become more efficient in your day to day tasks in Excel.

course

Introduction to Programming with Python

Attain a comprehensive understanding of Python programming language fundamentals. Develop the skills needed to write, debug, and maintain Python code confidently.

course

Advanced Python Development

This course aims to extend and solidify your Python experience by exploring structural techniques and common Python APIs. You’ll learn how to write OO and functional code, implement GUIs, create Web sites backed up by relational databases, implement multithreaded code, and more.

course

Data Wrangling with Python

Learn the basics of the python programming language and Jupyter notebooks. Understand and use the key Python libraries for data manipulation, analysis, and visualization, including Pandas, NumPy, and popular data visualization packages.