Home » Microsoft Excel Data Cleaning and Reporting – Stage 3

This course is perfectly pitched at an intermediate to advanced level. I was concerned I wouldn’t keep up but I totally loved every minute of it

read more testimonials

Microsoft® Excel Data Cleaning and Reporting – Stage 3

Full day course (6.5 hours)

BOOK NOW

   About this course

This intermediate to advanced course has been designed to introduce the more advanced features that assist you in formatting, referencing and reporting on large data lists. The aim is to ensure you gain a good grounding in preparing data and then creating reports using these fabulous tools.

   Who should attend?

This course is definitely for you if …

  • You want to create reports from csv or txt files imported into Excel
  • You’re finding yourself spending a lot of time reformatted data before it can be useful
  • You would like to become confident using some of the more advanced features of Excel
  • Cross-referencing and updating data using Lookup functions is something you would like to be confident in
  • Consolidating and analysing data into meaningful Pivot Table reports is something you want to be confident in

   Prerequisite

As this is an intermediate to advanced course the prerequisite is 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:

  • Control how delimited files are imported into and exported from Excel
  • Use functions, and data cleaning features to prepare data so that it’s fit for analysis and reporting
  • Cross-reference to other data lists using Lookup functions
  • Quickly summarize and report on data pulled from multiple worksheets
  • Create and use dynamic data ranges in formulas and Pivot Tables
  • Create dynamic reports using Pivot Tables and Pivot Charts

   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 6 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.

   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 on a day and time that works for you. Avoid traffic and parking hassles and learn at work. And if you don’t have a laptop, we can lend you one. Pricing is adjusted depending on the number of attendees.

1 person only – $650+GST (5 hours)
2 to 3 people – $350+GST per person
4 to 6 people – $325+GST per person
7 to 12 people – $295+GST per person

For Online Coaching please contact us for a quote.

 Book now! Save the date for this onsite Excel course

Dates and preferred times go quickly so book yours today!

Use our online booking system to reserve and manage your session.
Our Booking Terms and Conditions

BOOK NOW

COURSE CONTENT

Importing data files into Excel

  • Opening csv and txt files in Excel without losing vital data
  • Retain data formats while importing
  • Exclude unwanted data from the import
  • Export data from Excel into a text file

Preparing data for reporting

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

Cross-referencing to other data lists

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

Working with data ranges

  • Create and use Named ranges in formulas
  • Manage and analyse data ranges using the Table feature
  • Use Table ranges in formulas and Pivot Tables

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, filter and rank the data
  • Insert Slicers (and Timelines in Excel 2013 and 2016 only)
  • Move items into logical groups for ease of summarizing and performing data analysis
  • 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
  • Work with multiple data cache

Creating Pivot Charts

  • Create a Pivot Table fit for charting
  • Utilise Recommended Charts (2013 and 2016 only)
  • Understand the relationship between Pivot Tables and Charts
  • Add and hide elements of the chart
  • Changing the chart format, design and layout
  • Creating a combination chart

It was invaluable!

Thank you so much. I really meant it when I said it was invaluable. Just the simple tools you gave us, like LEFT and TRIM etc are so useful, let alone the amazing things we’ll be able to do with VLOOKUP and MATCH and INDEX and TEXT etc. Very exciting.

Rachael Stevenson – Alexander Dorrington Lawyers

read more testimonials

This was a great course

I will save 1 to 2 hours a day and additional time for my team as I will be running them through some of the content. Thank you!! This was a great course.

Bridget Armstrong – Kotahi Logistics

read more testimonials