Home / Non-Accredited / Microsoft Office & Computer Skills / Microsoft Excel Intermediate to Advanced (2 days)
Quick Look Course Summary:Microsoft Excel Intermediate to Advanced (2 days)
-
Length: 2 day(s)
-
Price (at your venue): 1 Person R 10,326 EX VAT 3 Person R 7,594 EX VAT 10 Person R 5,537 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 Intermediate to Advanced (2 days)
Staff who already work in Excel come back building reports, models and dashboards that refresh, check themselves and hold up under scrutiny.
The gap between 'uses Excel every day' and 'the person everyone sends their spreadsheet to' is about two days of structured, hands-on work. This two-day Excel intermediate to advanced course closes it. Each day is one of BOTI's licensed CTM workshop kits: day one the Excel 2024 kit pitched at intermediate level, day two the Excel Expert kit, the top rung of our Excel ladder.
Day one is about producing a clean report from raw data: validation and cleaning tools, IF with AND, OR and IFERROR, text and date formulas, the dynamic array functions, Power Query from the ERP or accounting export, pivot tables with slicers and pivot charts, and sharing and protecting the result. Day two is about analysis and control: SUMIFS and COUNTIFS, VLOOKUP, INDEX and MATCH, custom formats and formula-driven conditional formatting, Goal Seek and Scenario Manager, consolidation and the Data Model, financial functions, advanced pivot tables and dual-axis charts, the Watch Window, restricted editing, templates and recorded macros.
Examples come from South African finance, sales, HR and operations: a debtors age analysis, a budget with best and worst-case scenarios, a sales dashboard by province and an equipment finance comparison. Delegates should already be comfortable with the basics – the Beginners day is not repeated here.
Watch a short introduction
Why this course
For your organisation
- Reports built from the system export and refreshed with one click
- Models and dashboards that stay correct as the data changes
- Budget scenarios and finance comparisons worked out properly
- Fewer errors reaching management, and workbooks protected against accidental change
- Repetitive month-end steps recorded as macros
For your delegates
- Power Query, pivot tables and dynamic arrays on day one
- Lookups, what-if analysis, advanced pivot tables and macros on day two
- Confidence to take over and fix someone else's workbook
- Working files – tracker, dashboard, template with a macro – to adapt at work
What delegates will be able to do
- Clean, validate and reshape data with Excel's data tools and Power Query
- Write decision, text and date formulas that handle errors, and build self-updating lists with FILTER, SORT and UNIQUE
- Summarise data with pivot tables, slicers, timelines and charts
- Share, protect and check a workbook used by several people
- Calculate on several conditions with SUMIFS and COUNTIFS and look up data with VLOOKUP, INDEX and MATCH
- Apply custom number formats, formula-based conditional formatting and form controls
- Run Goal Seek and Scenario Manager, consolidate workbooks and use financial functions
- Build advanced pivot tables with calculated fields, GETPIVOTDATA and connected slicers, plus trendline and dual-axis charts
- Audit formulas with the Watch Window and Evaluate Formula, restrict editing and encrypt a workbook
- Save a template and record and run a simple macro
Who should attend
Bookkeepers, accountants, analysts, HR and payroll specialists, sales and operations coordinators, supervisors and managers who already build their own spreadsheets and want to report, analyse and automate properly. Delegates should be comfortable entering and formatting data, working with tables and writing simple formulas such as SUM and IF – the level of our Microsoft Excel Beginners day.
Course outline
Day 1 – Intermediate – the Excel 2024 kit: clean data, smarter formulas, Power Query, pivot tables and shared workbooks
Cleaning and controlling the data you receive
- Text to Columns and Flash Fill to split names, codes and dates from a system export
- Removing duplicate records and finding blanks with Go To Special
- Data validation: drop-down lists for cost centres, departments and leave types
- Input messages and error alerts that stop wrong entries at the source
- Freeze panes and split windows for long registers
- Keyboard shortcuts that save time every day
Formulas that make decisions
- Mixed references ($A1 and A$1) for a rates or commission table
- IF combined with AND and OR for two or three conditions at once
- IFERROR to clean up #N/A and #DIV/0! in a report
- TEXT and CONCAT to build reference numbers and readable labels
- TODAY and date arithmetic for days outstanding and the ageing of debtors
- Named ranges so formulas read like plain English
Dynamic arrays and Power Query
- FILTER to list only the outstanding invoices or the staff in one region
- SORT and UNIQUE for a live, de-duplicated customer or supplier list
- SEQUENCE for numbered schedules and date lists that build themselves
- Spill ranges and the # reference, and what happens when the source grows
- Power Query: importing a CSV or Excel export from the ERP or accounting system
- Removing columns, fixing data types, splitting and unpivoting in the Query Editor
- Appending twelve monthly files into one table and refreshing when the next month arrives
- The IMAGE function to show product pictures in a price list
Pivot tables, slicers and charts for reporting
- Building a pivot table from a table and arranging rows, columns and values
- Grouping dates by month and quarter, and numbers into bands
- Slicers and timelines so a manager can filter the report themselves
- Pivot charts and ordinary charts: titles, data labels and legends that read well
- Charts that update automatically from a spill range
- Sparklines and a map chart of sales by province
- Conditional formatting rules: data bars, colour scales, icon sets and top ten
Practical builds for the office
- A budget-versus-actual tracker with variance and percentage columns
- A staff roster or shift calendar driven by dates
- A simple project schedule laid out as a Gantt chart
- A stock and sales tracker with running totals and reorder flags
- Starting from a built-in template and adapting it
Sharing, protecting and checking a workbook
- Sharing a workbook from OneDrive or SharePoint and setting who may edit
- Co-authoring with colleagues, comments and notes, and version history
- Protecting a worksheet and locking input cells so the formulas survive
- Passwords and encryption for sensitive files, with POPIA in mind
- Trace precedents and dependents to see what feeds a total
- Customising the ribbon, page setup for large sheets and exporting to PDF or CSV
Day 2 – 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
Two days, 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 manuals for both kits, exercise files and sample data 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 training was very informative and practical, leaving our employees feeling confident in their ability to leverage Excel more effectively.”
“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.”
Enrol or ask a question
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.
Can we book only one of the two days?
Yes. Day one is sold on its own as Microsoft Excel Intermediate (1 day) and day two as Microsoft Excel Advanced (1 day).
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).
Our team still needs the basics – what then?
Book Microsoft Excel Beginners to Intermediate (2 days), or Microsoft Excel Beginners to Advanced (3 days) for the full ladder. This course assumes delegates can already build, format and total a sheet.
Which version of Excel do you train on?
Microsoft 365 or Excel 2021/2024 on Windows. Power Query, the dynamic array functions and the Data Model need a current version; tell us what your office runs and we adjust the exercises, or we supply laptops with the software.
Does day two cover VBA?
Day two records, edits and runs simple macros. Writing code is covered in the Writing Excel Macros with VBA Course (3 days).
Related courses
Get your price – the more delegates, the less each pays
Change the options to see your price per person, then email yourself the quote and course outline.
Contact us now – we reply within 15 minutes during office hours
Monday to Friday, 08:00–17:00. After hours, we reply on the next working day – or call 011 882 8853.