About this course
This onsite half-day intermediate to advanced course introduces the VLOOKUP and Pivot Tables features, designed to assist you in combining and analysing large data lists before building reports. Topics include super helpful tips on preparing data for use in VLOOKUP formulas and Pivot Table reports.
The aim is to ensure you gain a good grounding in analysing and reporting using VLOOKUP functions and the more advanced Excel Pivot Table features.
Who should attend?
This course is definitely for you if …
- You’re 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
- It would save you a lot of time learning how to analyse your data quickly using the VLOOKUP function
- You want to learn how to quickly consolidate and analyse data into meaningful Pivot Table 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:
- Use functions, and data cleaning features to prepare data so that it’s fit for use in a VLOOKUP and in Pivot Table reporting
- Quickly combine and cross-reference data from multiple worksheets
- Manage and analyse data using Excel Tables
- Create dynamic reports using Pivot Tables
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.
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.
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 – $462.50+GST
2 to 3 people – $275+GST per person
4 to 6 people – $250+GST per person
7 to 12 people – $210+GST per person
For Online Coaching please contact us for a quote.
Preparing data for reporting
- Identify and removing duplicate records
- Change numbers formatted as text back to numbers
- Join the content of several cells into one cell
- Split a single column of data into several columns
- Quickly remove unwanted text and unwanted spaces inside of cells
Combining and analysing data
- Pull data from multiple worksheets into one summary sheet
- Use the VLOOKUP function to match and pull data from other data lists
- Manage and analyse data ranges using the Table feature
- Use dynamic 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 and filter the data
- Insert Slicers (and Timelines in Excel 2013 and 2016 only)
- Group dates by month, quarter and year
- Display values as percentages of totals
- Refreshing and resetting the source data
- Create a PivotTable from multiple worksheets