Home / Non-Accredited / Microsoft Office & Computer Skills / Microsoft Excel Intermediate (1 day)
Quick Look Course Summary:Microsoft Excel Intermediate (1 day)
-
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 Intermediate (1 day)
Staff who already use Excel turn a raw system export into a clean, checked report with pivot tables and formulas that make decisions – in one day.
Many people who use Excel every day are stuck at the same level: they can type into a sheet and add up a column, but every month-end still means hours of copying, re-typing and manual checking. This one-day Excel intermediate course moves them on. It is built on BOTI's licensed Excel 2024 workshop kit, pitched for people who already have the basics, so the introductory material the kit opens with is skipped.
The day follows the life of a real report. Delegates clean a system export with Text to Columns, Flash Fill and data validation, write formulas that decide and label – IF with AND and OR, IFERROR, TEXT, CONCAT and TODAY – and use the dynamic array functions FILTER, SORT, UNIQUE and SEQUENCE to build lists that update themselves. They import and reshape a CSV in Power Query, summarise it with a pivot table, slicers and a pivot chart, and build a budget tracker, a staff roster and a simple Gantt chart. The day closes with sharing, co-authoring, protection and formula checking.
Exercises use South African workplace data: a debtors age analysis, a leave register, a sales report by province and a VAT reconciliation from the accounting export. Delegates who want lookups, what-if analysis, advanced pivot tables and macros continue to the Advanced day.
Watch a short introduction
Why this course
For your organisation
- Month-end reports built in minutes from the system export, not re-typed
- Drop-down lists and validation that stop wrong entries before they spread
- Pivot tables and slicers that managers can filter themselves
- Shared workbooks that are protected, checked and version-controlled
For your delegates
- Formulas that handle conditions, errors, text and dates
- Power Query skills that replace hours of manual cleaning
- Your first pivot table report with slicers and a chart
- A set of working files – budget tracker, roster, Gantt chart – to adapt at work
What delegates will be able to do
- Clean a system export with Text to Columns, Flash Fill, Remove Duplicates and Go To Special
- Control data entry with validation rules, drop-down lists and error alerts
- Write formulas that combine IF with AND, OR and IFERROR, and that handle text and dates
- Use mixed references and named ranges so formulas copy and read correctly
- Build self-updating lists with FILTER, SORT, UNIQUE and SEQUENCE
- Import, reshape and refresh data from a CSV or Excel export with Power Query
- Summarise data with a pivot table, group dates, add slicers and timelines and chart the result
- Apply conditional formatting rules that highlight what matters in a large data set
- Share a workbook, co-author it, protect sheets and trace what feeds a formula
Who should attend
Administrators, bookkeepers, HR and payroll staff, sales and operations coordinators, supervisors and anyone who produces weekly or monthly reports in Excel. Delegates should already 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
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 and the dynamic array functions need a current version); laptops with the software can be supplied on request. Course manual, exercise files and sample data included; delegates may bring their own exports. 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.
Is this the right level for my team?
If they can build a sheet, format it and write SUM and IF formulas, yes. If not, start with Microsoft Excel Beginners (1 day). If they already use pivot tables and lookups confidently, book Microsoft Excel Advanced (1 day) instead.
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).
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.
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.
Can we combine levels in one booking?
Yes. Microsoft Excel Beginners to Intermediate (2 days) adds the Beginners day before this one; Microsoft Excel Intermediate to Advanced (2 days) adds the Advanced day after it; Microsoft Excel Beginners to Advanced (3 days) runs all three.
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.