Home / Non-Accredited / Microsoft Office & Computer Skills / Microsoft Excel Beginners to Advanced (3 days)
Quick Look Course Summary:Microsoft Excel Beginners to Advanced (3 days)
-
Length: 3 day(s)
-
Price (at your venue): 1 Person R 14,997 EX VAT 3 Person R 11,205 EX VAT 10 Person R 8,120 EX VAT
-
Certification Type:Non-Accredited
-
Locations & Venues: Off-site or in-house. We train in all major city centres throughout South Africa.
Microsoft Excel: Beginners to Advanced Course – self-paced online, 6 months’ access: R1,250 per learner (ex VAT); teams of 10 or more pay less.
Get Free & personalised
Training Advice
Microsoft Excel Beginners to Advanced (3 days)
A whole team goes from a blank workbook to reports, models and dashboards that refresh and check themselves – in three consecutive days.
When a department, a new intake or a project team needs everyone working in Excel to one standard, three separate bookings are slow and expensive. This three-day Excel beginners to advanced course runs the full ladder with one group. Each day is one of BOTI's licensed CTM workshop kits – Excel Essentials, then Excel 2024 at intermediate level, then Excel Expert – so each day builds on the last and nothing is taught twice.
Day one, Beginners: workbooks, data entry, formatting, Excel tables, SUM, AVERAGE, COUNT, IF, SUMIF and COUNTIF, charts and printing. Day two, Intermediate: cleaning and validating data, IF with AND and OR, IFERROR, date formulas, FILTER, SORT and UNIQUE, Power Query, pivot tables with slicers and pivot charts, sharing and protection. Day three, Advanced: SUMIFS and COUNTIFS, VLOOKUP, INDEX and MATCH, custom formats and rule-based formatting, Goal Seek and scenarios, the Data Model and financial functions, advanced pivot tables and dual-axis charts, auditing tools, templates and recorded macros.
Exercises follow South African workplace files from register to dashboard: an attendance register, an expense claim, a debtors age analysis, a sales report by province, a VAT reconciliation and a budget with scenarios. The pace is brisk; a group that needs more time at one level can book the one- and two-day courses instead.
Why this course
For your organisation
- A whole team or intake trained to one standard in a single week
- Lower cost and less disruption than three separate bookings
- Reports, models and dashboards that refresh from the system export
- Fewer spreadsheet errors reaching management
- Month-end steps recorded as macros and shared as templates
For your delegates
- Each day builds on the one before, with nothing repeated
- Formulas, tables and charts; then Power Query and pivot tables; then lookups, scenarios and macros
- A full set of working files to take back to the office
- Course manuals for all three kits as a reference
What delegates will be able to do
- Create, organise, format and print workbooks and work with Excel tables
- Write formulas from SUM and IF through SUMIF and COUNTIF to the text functions
- Clean and validate data and control entry with drop-down lists
- Build self-updating lists with FILTER, SORT, UNIQUE and SEQUENCE
- Import, reshape and refresh data with Power Query
- Summarise data with pivot tables, slicers, timelines and charts, from a first pivot to calculated fields and GETPIVOTDATA
- Look up and calculate across tables with VLOOKUP, INDEX and MATCH, SUMIFS and COUNTIFS
- Run Goal Seek and Scenario Manager, consolidate workbooks and apply financial functions
- Audit, protect, share and encrypt workbooks
- Save templates and record simple macros
Who should attend
Teams, new intakes, graduate and internship groups and departments moving to a common way of working in Excel – from administrators and clerks to bookkeepers, analysts, supervisors and managers. Delegates should be comfortable with a Windows computer, keyboard and mouse; no Excel experience is needed on day one, and the pace rises each day.
Course outline
Day 1 – Beginners – the Excel Essentials kit: build, format, calculate, chart and print
Getting around Excel and setting up a workbook
- The Excel screen: ribbon, Quick Access Toolbar, name box, formula bar and status bar
- Creating, saving and opening workbooks, and what the different file formats are for
- Adding, renaming, colouring, moving and copying worksheets – one sheet per branch or per month
- Moving around a large sheet quickly and searching for a value anywhere in the workbook
- Opening a CSV export from your payroll, point-of-sale or accounting system
- Hiding and unhiding sheets, rows and columns; zoom and window views
- Adding a hyperlink to a supporting document or another sheet
Entering and editing data cleanly
- Typing, correcting and replacing data without breaking what is already there
- AutoFill for dates, months, employee numbers and repeating patterns
- Cut, copy and paste, and Paste Special to paste values only or transpose a list
- Inserting and deleting cells, rows and columns in a live register
- Adjusting row height and column width so everything shows
- Find and Replace to fix a supplier name across a whole sheet
Formatting so a manager can read it at a glance
- Number formats: currency, percentages, decimals and South African date formats
- Alignment, indenting and wrapping text; when to merge cells and when not to
- Format Painter and cell styles for a consistent look
- Workbook themes and sensible colour choices for printed reports
- Conditional formatting to flag overdue accounts, low stock or absences
- Sparklines to show a month-by-month trend inside a single cell
Working with lists as Excel tables
- Turning a range into a table and why it makes sorting and filtering safer
- Table styles, banded rows and the total row
- Sorting by more than one column, for example by department and then by surname
- Filtering a staff list, stock sheet or sales register down to the records you need
- Removing duplicate records such as repeated ID or invoice numbers
- Subtotals and outlining to collapse detail under group totals
- Converting a table back to an ordinary range
Formulas and functions for everyday calculations
- How a formula is built: cell references, operators and the order of calculation
- Relative and absolute references so a formula copies correctly down a column
- SUM, AVERAGE, COUNT, MIN and MAX on an expense claim or a sales sheet
- IF to show Pass or Fail, Paid or Outstanding
- SUMIF, COUNTIF and AVERAGEIF to total sales per branch or count staff per shift
- LEFT, RIGHT and MID to pull a code out of a longer reference
- UPPER, LOWER, PROPER and CONCATENATE to tidy names and build e-mail addresses
- Showing formulas on the sheet to check your work
Charts, pictures and printing
- Choosing a column, line or pie chart for the numbers in front of you
- Adding a second data series and switching rows and columns
- Quick Analysis for an instant chart, total or formatting
- Chart titles, labels, legends, layouts and styles, and moving a chart to its own sheet
- Inserting shapes, text boxes and the company logo, with alt text for accessibility
- Print area, print titles on every page, scaling to fit, headers and footers
- Saving as PDF and checking a workbook for hidden personal information before it goes out
Day 2 – 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 3 – 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
Three days, 08:30-16:00, usually consecutive. 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 (days two and three use Power Query, the dynamic array functions, the Data Model and macros); laptops with the software can be supplied on request. Course manuals for all three 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
“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.”
“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.”
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 the three days be split across weeks?
Yes. Many clients run one day a week so that staff can practise in between; the content is the same as the three single-day courses taken in order.
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).
What if some people only need one level?
Each day is also sold on its own – Microsoft Excel Beginners (1 day), Microsoft Excel Intermediate (1 day) and Microsoft Excel Advanced (1 day) – and as the two-day pairs Beginners to Intermediate and Intermediate to Advanced.
Is three days enough to go from beginner to advanced?
For a group that practises between days, yes: each day is a full workshop kit at its own level. A group that wants more time on one level is better served by the separate courses, and the facilitator will say so after day one.
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.
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.