Home / Non-Accredited / Microsoft Office & Computer Skills / Microsoft Excel Advanced (1 day)
Quick Look Course Summary:Microsoft Excel Advanced (1 day)
-
Next Public Course Date:
-
Length: 1 day(s)
-
Price (at your venue): 1 Person R 5,654 EX VAT 3 Person R 3,982 EX VAT 10 Person R 2,954 EX VAT
-
Certification Type:Non-Accredited
-
Locations & Venues: Off-site or in-house. We train in all major city centres throughout South Africa.
Get Free & personalised
Training Advice
Microsoft Excel Advanced (1 day)
Experienced Excel users leave able to build a model, a management report and a dashboard that other people can trust, protect it and automate the repetitive parts.
The people who build the budget model, the sales dashboard or the month-end pack are usually self-taught, and it shows in fragile formulas and the Friday-night rework when a lookup breaks. This one-day Excel advanced course is built on BOTI's licensed Excel Expert workshop kit, the top rung of our Excel ladder, for staff who already use pivot tables, Power Query and IF formulas and need to go further.
The day starts with multi-condition and lookup formulas – SUMIFS, COUNTIFS, AND, OR, NOT, VLOOKUP, HLOOKUP, INDEX and MATCH – and the custom number formats, formula-driven conditional formatting and form controls that make a report behave. Delegates run Goal Seek and Scenario Manager on a budget, consolidate branch workbooks, combine data through Power Query and the Data Model, and use PMT and NPV on an equipment finance decision. Then pivot tables at full strength: calculated fields, GETPIVOTDATA, connected slicers, pivot charts with drill-down, trendlines and dual-axis charts.
The last sessions make the work safe and repeatable: the Watch Window and Evaluate Formula, restricted editing and encryption, templates, and a recorded macro that formats and prints the monthly report at the click of a button. Examples are drawn from South African finance, sales, HR and operations data.
Why this course
For your organisation
- Management reports and models that stay correct when the data changes
- Budget scenarios and finance comparisons worked out in Excel, not by guesswork
- Dashboards with slicers and drill-down that managers use themselves
- Repetitive month-end steps recorded as macros
- Sensitive workbooks protected, with editing limited to the right people
For your delegates
- Lookups and multi-condition formulas that replace manual matching
- Goal Seek, scenarios and financial functions for real decisions
- Advanced pivot tables and charts for a one-page dashboard
- Tools to find and fix errors in someone else's workbook
- Your first recorded macro, saved in a template
What delegates will be able to do
- Calculate on several conditions at once with SUMIFS, COUNTIFS, AVERAGEIFS and nested AND, OR and NOT
- Look up data with VLOOKUP, HLOOKUP, INDEX and MATCH across tables and linked workbooks
- Create custom number formats, formula-based conditional formatting and validation rules
- Add form controls, custom styles and a company theme to a report
- Run Goal Seek and Scenario Manager, consolidate workbooks and combine data in the Data Model
- Apply PMT, FV and NPV to loan and finance comparisons
- Build advanced pivot tables with grouping, calculated fields, GETPIVOTDATA, connected slicers and pivot charts
- Create trendline and dual-axis charts and save chart templates
- Audit a workbook with the Watch Window, error checking and Evaluate Formula, and set protection and encryption
- Save a template, record and edit a simple macro and run it from a button
Who should attend
Financial and management accountants, analysts, bookkeepers, HR and payroll specialists, sales and operations managers, project coordinators and anyone who builds the workbooks other people rely on. Delegates should already be comfortable with pivot tables, IF formulas, data validation and the basics of Power Query – the level of our Microsoft Excel Intermediate day.
Course outline
Day 1 – Advanced – the Excel Expert kit: lookups, what-if analysis, advanced pivot tables, auditing, templates and macros
Lookups and multi-condition calculations
- SUMIFS, COUNTIFS and AVERAGEIFS for sales by rep by month, or absences by department by reason
- AND, OR and NOT inside nested formulas
- VLOOKUP and HLOOKUP, their limits, and when XLOOKUP replaces them in Microsoft 365
- INDEX and MATCH for two-way lookups and lookups to the left
- Structured references to tables and links to data in another workbook
- Naming cells, ranges and tables, and managing the names
- NOW and TODAY, how Excel stores dates and times, and working out hours, ageing and deadlines
Custom formats, validation and rule-based formatting
- Custom number formats for thousands, negatives in brackets and units
- Advanced Fill Series: weekdays and custom lists of branches and products
- Data validation with custom formulas, for example a 13-digit ID number
- Conditional formatting rules written as formulas that highlight the whole row
- Managing rule order and overlaps, and clearing rules
- Custom colours, cell styles and a company theme for every report
- Form controls: check boxes, option buttons and drop-downs on a dashboard sheet
- Dates, currencies and fonts for a group with operations outside South Africa
What-if analysis, consolidation and financial functions
- Goal Seek to find the sales volume or price that hits a target
- Scenario Manager for best, base and worst-case budgets
- Consolidating branch or departmental workbooks into one summary
- Importing, transforming and combining data with Power Query and the Data Model
- Cube functions to pull a single figure out of the Data Model
- PMT, FV and NPV for loan repayments and equipment finance comparisons
Advanced pivot tables and charts
- Grouping, calculated fields and value settings in a pivot table
- GETPIVOTDATA to feed pivot results into a formatted report
- Slicers connected to several pivot tables at once
- Pivot charts with drill-down, styles and layouts
- Trendlines and dual-axis combination charts, for example units against revenue
- Saving a chart as a template for the next report
Checking and protecting a complex workbook
- Watch Window to monitor key cells while you work elsewhere
- Error checking and Evaluate Formula to step through a calculation
- Calculation options: automatic, manual and iterative
- Restricting editing to specific ranges and users, and protecting workbook structure
- Encrypting a workbook with a password and managing document versions
- Showing hidden ribbon tabs such as Developer
Templates and simple macros
- Saving a workbook as a template that the whole team starts from
- Enabling macros safely and understanding macro security
- Recording a simple macro to format and print a monthly report
- Editing the recording and copying macros between workbooks
- Running a macro from a button or a form control
How it is delivered
One day, 08:30-16:00. In person at your offices anywhere in South Africa, at a BOTI venue, or live online. One delegate or a group of up to 20, each on a Windows computer with Microsoft 365 or Excel 2021/2024 (Power Query, the Data Model and macros need Excel for Windows); laptops with the software can be supplied on request. Course manual, exercise files and sample models included. We deliver it at your offices in Johannesburg, Pretoria, Durban, Cape Town and anywhere else in South Africa, at a BOTI venue in Johannesburg, or live online for teams in different places.
What our clients say
“The facilitator effectively encouraged participation and used practical examples, discussions and interactive activities to ensure that the concepts were understood and could be applied in the workplace.”
“Business Optimization Training Institute facilitated Microsoft Excel Training for our company. The course was efficiently scheduled and planned around our timetable. We were impressed by the quality of training.”
“The training was very informative and practical, leaving our employees feeling confident in their ability to leverage Excel more effectively.”
Frequently asked questions
Can you run this course at our offices?
Yes. Most groups are trained at the client's premises anywhere in South Africa, on your own computers or on laptops we bring. We can also host it at a BOTI venue or deliver it live online.
What should delegates know beforehand?
Pivot tables, IF formulas, data validation and the basics of Power Query – the content of Microsoft Excel Intermediate (1 day). A team that is not there yet should book Microsoft Excel Intermediate to Advanced (2 days).
Is it accredited?
No – it is a non-accredited short course and delegates receive a BOTI certificate of attendance. If you need an accredited route, ask us about BOTI's End User Computing skills programmes (QCTO accreditation pending).
Is this a VBA course?
No. Delegates record, edit and run simple macros, which covers most repetitive reporting tasks. Staff who need to write code should book the Writing Excel Macros with VBA Course (3 days).
Do we need Microsoft 365?
Power Query, the Data Model and macros need Excel for Windows (Microsoft 365, 2021 or 2024 recommended). XLOOKUP is only in Microsoft 365 and Excel 2021 and later, so on older versions we work with INDEX and MATCH. We can supply laptops with the software.
Can the exercises use our own spreadsheets?
Yes. Send us a sample register, export or report beforehand, with personal information removed, and the facilitator builds it into the exercises.