Microsoft® Excel Data Cleaning and Reporting – Stage 3

Full day course (6 hours)

About this course

This course has been designed to introduce the 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.

Please note: We specialise in delivering this course in-house to small groups or individuals. We don’t run scheduled public courses.

Who should attend?

This course is definitely for you if …

  • You want to create reports from csv or txt files imported into Excel
  • You find yourself spending a lot of time reformatted data before it can be useful
  • You want to learn how to cross-reference and update data using Lookup functions
  • You want to learn how to quickly consolidate and analyse data into meaningful 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:

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

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, unwanted spaces inside of cells and empty rows
  • 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
  • Create dynamic 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

Price

Please see Pricing and Booking details below.

Need more information? Contact us today!

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

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: $350 + 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