Microsoft® Excel Combining, Analysing and Reporting – Stage 3

Half-day course (3.5 hours)

About this course

This half-day course has been designed to introduce the features that assist you in combining, analysing and reporting on large data lists.

The aim is to ensure you gain a good grounding in analysing and reporting using functions and advanced Excel features.

Who should attend?

This course is definitely for you if …

  • You find yourself spending a lot of time reformatted data before it can be useful
  • You want to learn how to combine and cross-reference from multiple worksheets
  • You want to analyse your data quickly using functions
  • You want to learn how to quickly consolidate and analyse data into meaningful Pivot Table reports

Prerequisite

Proficiency in creating, formatting, editing, saving and printing an Excel workbook. This includes the ability to create formulas and insert functions.

Learning objectives

By the end of this course you should be able to confidently:

  • Use functions, and data cleaning features to prepare data so that it’s fit for analysis and reporting
  • Quickly summarize and report on data pulled from multiple worksheets
  • Analyse data using statistical and logical functions
  • Create dynamic reports using Pivot Tables

Attendance Certificate

Need proof of your Continuing Professional Development (CPD)? On completion of the session, by request, we will send you a “Certificate of Attendance” confirming your participation and the duration of the training session.

Course Duration

Workshop delivery consists of 3.5 hours hands-on tuition. Please note the duration is set for a maximum of 12 people. Individual coaching can be customised to suit. Please contact us to discuss.

If you are looking for an advanced course that includes time to complete practical exercises please refer to our full day course Excel Data Cleaning and Reporting.

Course Content

Preparing data for reporting

  • Use functions and formulas to extract and piece together data
  • Split a single column of data into several columns
  • Control leading zeros and display numbers in the format you require
  • Quickly removing unwanted text, unwanted spaces inside of cells and empty rows
  • Change numbers formatted as text back to numbers
  • Identify and removing duplicate records

Cross-referencing to other data lists

  • Pull data from multiple worksheets into one summary sheet
  • Use the VLOOKUP function to match and pull data from another data list
  • Break links to the source data

Use Functions to analyse and present data

  • Analyse data using SUMIF, SUMIFS, COUNTIF, COUNTIFS, AVERAGEIF, AVERAGEIFS, SUMPRODUCT

Creating Pivot Tables

  • Ensure your data is fit for pivoting
  • Create a new Pivot Table
  • Understand the Pivot Table cache
  • Change the layout and formatting of the report
  • Control how the data, totals and subtotals are displayed and summarized
  • Sort and filter the data
  • Insert Slicers (and Timelines in Excel 2013 and 2016 only)
  • Group dates by month, quarter and year
  • Create calculations within the Pivot Table area
  • Display values as percentages of totals
  • Refreshing and resetting the source data
  • Create a PivotTable from multiple worksheets

Price

Please see Pricing and Booking details below.

Need more information? Contact us today!

The course was awesome!
Thank you for the course, it was great! I’d definitely recommend it to anyone.

Redaw Matthews, Ergo Consulting Limited

Price and Booking details

We bring the training to you…

That’s right! We don’t run scheduled courses. We come to your place of work.

Avoid traffic and parking hassles and learn at work. And if you don’t have a laptop, we can lend you one.

Price: $275 + GST per person, with a minimum booking of two people.

Please contact us to make a booking or for pricing if you would like to run this course for a large group or as personal coaching.

CONTACT US COACHING OPTIONS