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.
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
Proficiency in creating, formatting, editing, saving and printing an Excel workbook. This includes the ability to create formulas and insert functions.
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
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.
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
Please see Pricing and Booking details below.
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.