In this hands-on course we will explore how Excel handles data and introduce a variety of techniques to make your life easier when setting up Excel reports or preparing your data for analysis. We will cover efficient ways of handling dates in data exports, combining or splitting columns (like first and last name), reporting on data that is stored across multiple workbooks, grouping and filtering data (even when the information that we want to group by is a column in another table) and using ‘What If’ analysis for forecasting. Best of all, we will show you how to set up links and connections to the data source so that you can set and forget and build a robust Excel report that refreshes as your new data is added or updated.

This course is designed for analysts, managers, or anyone who uses Excel to get insights from business data. If you spend a lot of time preparing the monthly report, want to learn how Excel thinks, are keen to take your formula knowledge to the next level or just work with big data sets on a regular basis, this is the course for you.

We recommend that students have knowledge equivalent to the topics covered in our Excel Essential Skills and Excel Timesaving Features courses, OR have:

• A thorough understanding of formulae including absolute references ($A$1).
• A thorough understanding of using Excel’s common functions such as AVERAGE, MAX,COUNT, IF and the function wizard.
• The knowledge to create and use range names.

You'll gain a solid understanding of how Excel ‘thinks’ and learn handy tricks to work more efficiently with large datasets. You'll get an introduction to the concepts of Excel databases and take away techniques to create efficient reports, freeing you up to focus on the data insights rather than the data cleansing.

Group and Outline Data – Make large datasets easier to read by grouping them by region, month or topic. Quickly hide and unhide the columns that you need to see.

Consolidating Workbooks – Do you need to report across an entire department or company by have been collecting the data in individual workbooks? See how Excel can combine information from multiple workbooks and even maintain links to the original data source.

Splitting and Combining Data in Columns – Last name, First name or T-shirt, S (Blue) can appear in our data source, but make it challenging to do proper analysis. Learn a few techniques and formulas that split, combine and prepare your data for reporting.

Transforming Data with Power Query Editor - Introduction to Excel Power Query, the tool that automates data cleansing, splitting and combining columns, calculating new columns and working with data from multiple sources.

Introduction to the Excel Data Model - Did you know that Excel can be a relational database? Using the Excel Data Model you can connect to multiple sources of data and transform the data to make analysing and reporting more comprehensive.

Flash Fill – Add an email address column to a table of Last Name, First Name with just one click. Understand how Excel uses pattern recognition to make copying, combining, and cleaning data more efficient.

Looking Up and Retrieving Data (VLOOKUP, INDEX, MATCH) – Expand your database knowledge to work with multiple tables of data.

Summarising Data with Pivot Tables and Pivot Charts – Pull together your newly cleansed data into a beautiful Excel Dashboard or report in no time using Pivot Charts.

Advanced Filters – Start using Excel databases to find the best candidate for the job in a list of applicants or filter data based on advanced criteria.

Database Functions – Use database functions to create custom reports and tables to meet exact business requirements.