Courses

Excel Data Analysis & Reporting

= Scheduled class    = Guaranteed to run    = Fully booked

Can't find a class in your area? Contact our sales team and request a class date.
Not sure about the version of your software? Click here to find out.
Want to know if this course is right for you? Assess your skills level with our online Skills Assessment tool.

About this Course

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.

Audience

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.

Prerequisites

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.

At Course Completion

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.

Course Outline

Excel Databases & Tables – How does Excel handle data and what can we do to make reporting and analysis more efficient?

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.

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.

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.

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

What-If Analysis (Data Tables, Goal Seek, Scenarios) – Built in Excel tools to help make forecasting easy.

Get and Transform Data with Power Query – Introduction to Excel Power Query, the tool that automates data cleansing, splitting and combining columns, calculating new columns and working with data from multiple other sources.

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.

 Print this page