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
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.
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
- Create drop-down lists
- 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.
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. Minimum of 3, maximum of 12.
3 to 4 people – $350+GST per person
5 to 7 people – $325+GST per person
8 to 12 people – $295+GST per person
For 1-on-1 or 1-on-2 Coaching pricing and options please click here.
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
- Lock down what can be entered into a cell using drop-down lists
- 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
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