| Public Courses | |||
 |
$440 | 1 Day Group discounts available |
|
 |
Times | 9:00am - 4:30pm | |
 |
Reference | Hosted manual included or Purchase printed book |
|
 |
Certificate | Attendance or Completion upon assessment |
|
 |
Live Online |
Hands-on, Live Instructor Dedicated Training Platform |
|
| Private Courses | |||
 |
Classroom | Group Training (hired room) Get a Quote |
|
 |
In House | On your site Get a Quote |
|
Excel Intermediate Courses
3 intermediate level courses are available - each focused on different business outcomes.
What is most important to you? Do you want to build confidence in using Excel formulas and function, formatting data, creating alerts and interactive charts, sorting, filtering and analysing data? Choose the course that is most appropriate for your needs.
All courses are suitable for Excel 365 and earlier desktop versions of Excel. Our training platform is loaded with Microsoft 365 for public courses.
Managing Data focuses on manipulating and analysing data
Presenting Data is focused on reporting and presenting data in various ways
Tables and PivotTables focuses more on using Tables, Filters and PivotTables
Managing Data
4.8 (25,250 reviews)
Brochure
Excel Managing Data
Improve your efficiency with Excel using formulas and functions.
Master complex functions to analyse data and produce well structured business reports.
Manage data effectively with lists and tables. Sort and filter data. Remove duplicates. Other list techniques
Manage large worksheets - multiple sheets, large volume or rows and columns
Link worksheets using formulas and hyperlinks
Course Details
Audience:
This course is designed for people who have a basic knowledge of Excel, and who want to increase their knowledge of the practical uses of Excel. By the completion of this course, participants will have a thorough knowledge of using Excel to manage data in multiple sheet workbooks including using complex formulas and functions.
Prerequisites:
Participants must have basic knowledge of entering data, formatting and simple formulas.
Course Content
Excel Formulas
- Using Absolute References
- Relative -v- Absolute
- Absolute Formulas
- Mastering Mixed References
Range Names
- Navigating Named Ranges
- Creating Range Names
- Assigning Names
- Using Range Names in Formulas
- Managing Names
Functions
- Date and Time functions
- NOW, TODAY, DATE,
- YEAR, MONTH,
- WORKDAY, WEEKDAY
- Statistical functions
- COUNT, COUNTA
- Financial functions
- PMT, FV
- Logical functions
- IF, COUNTIF, SUMIF, SUMIFS
- Lookup functions
- VLOOKUP, HLOOKUP
Sorting and Filtering Data
- Sorting Data
- Using multiple sort keys
- Using Filters
- Filtering or Sorting by colour
Data Validation
- Restrict Cell entries to data from a list
Working with Large Worksheets
- Managing Worksheet Views
- Using Freeze Panes
- Using an Outline to hide data
- Using multiple sort keys
Multiple Worksheets & Workbooks
- Working with Multiple Worksheets
- Group and Ungroup Sheets
- Move & Copy Worksheets
- Print Selected Worksheets
- 3-D Formulas
Linking
- Linking formulas
- Using Copy & Paste method
- Updating and Managing links
- Creating Hyperlinks
Paste Special
- Copying Formats between worksheets
- Copying Column Widths
- Transposing data
- Copying Column Widths
Managing Workbooks
- Arranging Windows and Worksheets
- Protect formulas
- Protecting Worksheet Structure
Available Dates
| Event | Event Date | Location | Individual Price | Register |
|---|---|---|---|---|
| Excel Managing Data | 25-02-2025 | Live Online | $440.00 | |
| Excel Managing Data | 13-03-2025 | Live Online | $440.00 | |
| Excel Managing Data | 31-03-2025 | Live Online | $440.00 | |
| Excel Managing Data | 16-04-2025 | Live Online | $440.00 | |
| Excel Managing Data | 01-05-2025 | Live Online | $440.00 | |
| Excel Managing Data | 12-05-2025 | Live Online | $440.00 | |
| Excel Managing Data | 27-05-2025 | Live Online | $440.00 | |
| Excel Managing Data | 09-06-2025 | Live Online | $440.00 | |
| Excel Managing Data | 23-06-2025 | Live Online | $440.00 |
Presenting Data
4.75 (2750 reviews)
$440
Excel Presenting Data
Highlight important data automatically using conditional formatting
Manage the presentation of data efficiently using Styles to produce impressive reports
Master formulas to manipulate data in preparation for charting
Learn quick charting techniques to improve your efficiency with Excel charts
Create simple interactive charts which dynamically change using combo boxes or check boxes
Create progress charts including actual -v- target, represent data within an acceptable band
Master the new charts available in Excel 2016 or later - Waterfall chart, Sunburst chart, Treemap chart, Map charts
Course Details
Audience
This course is designed for people who have basic knowledge of Excel, and who want to improve the presentation of data and step up their use of Excel. Excel provides many tools for analysing data however, it is often a challenge to present the data in a meaningful way for others to use.
Prerequisites
Participants must have basic knowledge of entering data, formatting and simple formulas.
Course Content
Conditional Formatting
- Understanding Conditional Formatting
- Highligting Top or Bottom Values
- Working with Data Bars, Colour scales & Icon sets
- Creating a Schedule using Conditional Formatting
Tables
- Creating a Table
- Filtering a Table
- Creating Charts based on Tables
- Dynamic charts with Tables
- Using structured references with tables
- Calculated columns in Tables
Manage Formats with Styles
- Using Styles
- Creating a Style
- Copying Styles to another Workbook
Formulas to Manage Data
- CHOOSE() function
- NA() function
- SUMIFS()
- LARGE() and SMALL()
Quick Charting Techniques
- Simple chart formatting
- Row/Column control
- Resizing charts
- Adding more data
- Creating combination charts
- User defined charts and templates
Creating Simple Interactive Charts
- Overview of worksheet controls
- Using a checkbox to include/exclude a data series
- Using a combo box to select data to be displayed
- Creating dynamic linked charts using option buttons
- Using a combo box to display different charts
- Using data validation for data selection
- Adding a scrollbar to control the display of a chart
Reporting Progress in Charts
- Actual v Target
- Sparklines
- Creating a dynamic Conditional Chart
- Creating a Waterfall chart to show the rise and fall of data
Creating Dynamic Labels and Legends
- Adding a dynamic message to a chart
- Managing Titles and Labels with formulas
- Managing Legends with formulas
- Using Symbols for Legends
- Using Conditional Formatting.
Tables & PivotTables
4.8 (1550 reviews)
$440
Excel Tables & PivotTables
Tables allow you to manage and analyse data in Excel quickly and easily.
Link tables to external data so that your data is always up to date
A pivot table allows you to extract the significance from a large, detailed data set.
Discover how to perform a variety of calculations with Pivot Table Data.
Create PivotCharts based on Pivot able Data.
Analyse data from multiple tables using a Data Model and PowerPivot.
Course Details
Audience:
Users needing to manipulate and analyse data stored in Excel lists using pivot tables.
Prerequisites:
Participants must have an intermediate level knowledge and experience with Microsoft Excel.
Course Content
Tables and Pivot tables are two of Excel's most powerful features. This course is designed to develop experience and confidence in using the Excel Tables and PivotTables to analyse data and produce sophisticated management reports.
PivotTable Fundamentals
- What is a PivotTable?
- Why should I use a Pivot Table?
- What are the advantages?
- PivotTable Terminology
- Preparing your data for use in a PivotTable
- Creating a PivotTable
- Selecting the Data Source
- PivotTable Fields List
- Filterering data with Page Fields
- Adding Fields
- Removing Fields
- Understanding the Field icons
Presenting Data in PivotTables
- PivotTable Ribbon
- Hiding and Unhiding Items
- Sorting Data in a PivotTable
- Using AutoSort
- Advanced AutoSort
- Refreshing a PivotTable Report
- Inserting Data into the Data Source
- Formatting Numerical Data
- Selecting Parts of a PivotTable
- PivotTable Formats
- PivotTable Options
- Change the Summary Function
- Adding Multiple Data Fields
- Changing Calculations
- Hiding and Showing Row/Column Details
- Displaying Data Details
Grouping Data
- Grouping Data
- Group by Dates
- Group by Number
- Ungrouping Data
Calculations in PivotTables
- Changing custom calculations
- Creating Calculated Fields
- Calculated Items
- GetPivotData() to extract information
- Using Other Sources of Data
- Selecting data from another Excel workbook
- Connecting to an Database
- Using a Saved Query as the data source
- Using a csv text file as the source of a PivotTable
Using PivotCharts
- PivotChart terms
- Modifying the Chart
- Caution - Loss of Formatting in PivotCharts
Publishing PivotTables to the Web
- Saving a PivotTable as a web page
- Creating Interactive PivotCharts - Web
- Adding Fields to a PivotChart - Browser
Re-using PivotTables
- Requerying data
- Using Saved queries
- Automating PivotTable Updates
- Saving a PivotTable Template
Creating a Data Model
A Data Model allows you to integrate data from multiple tables, effectively building a relational data source inside an Excel workbook.
- Create a data model with multiple tables
- Create a relationship between the tables
- Use the data model to create a PivotTable and PivotChart
- Adding data to the data model
Brochure