The ‘Virtual MS Excel Training Course- Option A- Full Time Presenters’ has been adapted from our MS Excel training to address the current lockdown environment, by making sure the training is as similar to attending a live MS Excel course as possible, with all the benefits of a full time presenter providing the lectures.
- Duration – Course dependent
- Skill Level – Course dependent
- All Live Courses
- Online Courses
- Ask a Question
Virtual Excel Course- Option A- Course Structure
The course is presented by an Excel trainer using virtual streaming technologies like Microsoft Teams or Zoom. A typical course would include:
- All delegates complete our free MS Excel skills assessment to make sure they are on the correct course
- All delegates receive a workbook and the MS Excel worksheet
- All delegates can be at a central point or disbursed e.g. either at their desks or at home depending on the lockdown stage.
- The course runs on specific days and at specific times. It can be either a full day course (8 hrs per day) or can be broken into 4 hour modules.
- The presenter presents each segment via the virtual platform. Delegates are able to ask questions directly in the presentation.
- Delegates complete exercises after each segment to cement their knowledge.
- The presenters are available via the virtual platform, WhatsApp and/ or phone to assist (in South Africa). There are always multiple presenters available in cases of larger groups to reduce waiting times.
- Using the online platform, the delegates can share their screen with the presenter or visa versa and the presenter is able to interact with the delegates computer (when using Microsoft Teams)
- After each segment there is a brief Q&A for the presenter to share what questions were asked, especially for questions that are company specific and would help other people (in a normal live course everyone would have heard the question)
- At the end of the day an attendance register is created based on the interaction with the delegates to ensure that all delegates were diligent.
What the delegates receive
- Lifetime access to the online MS Excel training material
- Manual
- MS Excel spreadsheet with all the training material and exercises
- Certificate on successful completion
Course Outline
The course outline depends on the requirements of the business. All the courses listed in the MS Excel Live Training and MS Excel Online training are available with the method explained.
The most common course chosen is the MS Excel Intermediate to Advanced (the equivalent of our 3 day MS Excel Advanced course). Below the outline of this course.
Ultimate Excel Tool
- Flash Fill- Your new best friend?
MS Excel Tips and Tricks
- Viewing many parts of the spreadsheet (at the same time)
- Don’t hide rows- group them rather
- See sheet names in portrait style
- Selecting ranges with mouse and arrow keys- short cuts
- Enter date and time stamps- unchanging
- Doing Quick Calculations in a spreadsheet
- The Quick Access Toolbar
Filtering and Sorting
- Data sorting-Revision, colour and icon sorting
- Sorting the columns instead of the rows
- Create a custom sorting list
- Data Filtering
- Remove rows with the Filter
- Remove duplicate rows
- Use Advanced Filter for repeating filters
The Specials- GOTO and Paste
- GOTO Special- find comments, values, formulas and more
- Find errors, conditional formatting and data validation
- Copy Visible Cells Only
- Fill in all the blanks
- Paste Special- Values, Formulas with/without formatting
- Paste Special- convert negatives to positives
- Switch rows to columns and columns to rows
- Paste without overwriting blank cells
- Re Use the Format Painter
Tools for text
- Find Replace- standard uses
- Find Replace- Change formatting on many cells
- Remove between brackets or characters
- Single Column to Multiple Columns
- Import CSV’s into the correct columns
- Find partial names or between characters (fuzzy logic)
- Change the $ signs on multiple cells with FIND/ REPLACE
- Import CSV but correct for ‘numbers’ that start with zero e.g. credit card numbers
Pivot Tables
- Required data format for Pivot tables
- Create a Pivot Table- Step by Step
- Inserting Values into a Pivot Table
- Enhancing the values e.g. percentage of another column
- Show the details of a number in the Pivot Table
- Laying out the Pivot Report in completely separate columns
- Working with the rows in a Pivot Table- dropdown
- Working with the rows in a Pivot Table- field settings
- Normalize Pivot Reports to use elsewhere as a copy paste
- Working with the Columns in a Pivot Table
- Working with the Filter in a Pivot Table
- Automatically duplicate your Pivot Tables with different selections
- Slicers in a Pivot Table
- Control all Pivot Tables and Charts at once
- Timelines in a Pivot Table
- Grouping Dates into days, months, quarters and years in a Pivot Table
- Grouping Dates into weeks in a Pivot Table
- Grouping Text in a Pivot Table
- Grouping Numbers in a Pivot Table
- Pivot Charts in Excel
- Problems with grouping in Pivot Tables
- Protect the source data of a pivot table
- Add a calculation INSIDE a Pivot Table
- Conditional Formatting within a Pivot
- Get information out of a Pivot into a cell
- Switching off the GETPIVOTDATA
- Add up ITEMs within a Pivot Table Field
Conditional Formatting
- Change cell colour based on value
- Change color based on benchmark in another cell
- Highlight all above average cells
- Add comparative bars into cells
- Create heat maps in Excel
- Add risk robots, milestone, up and down icons to Excel cells
- Control the heat maps, icons and data bars in conditional formatting
- Conditional Formatting- Linking to different cells
- Control what is entered in an Excel cell- Data Validation
- Control the inputs in a cell based on another cell
Function and Formula Basics
- Tip- Opening Frequent files
- Enter formula into many cells (at the same time)
- Function Wizard
- Auditing Toolbar
- Referring to fixed cells
- Using the F4 shortcut for $ signs
- Dollar Sign ($A$1) Examples
- The basic “IF” function
- How to (safely) build a nested IF formula
- IF this AND this AND this is True
- See the numbers behind a formula
- Change formula across many sheets at the same time
- Update the calculations on only one sheet (for large spreadsheets)
Text Functions
- Fitting text inside a cell
- Quick entry of rows of data
- Quick Drop Down List
- Printing Tips
- Get Excel to read out the text and numbers
- Join text from many cells
- Extract parts of the text from cells
- Force Excel to see the value (instead of text)
- Change text to lower, UPPER or Proper case
- Remove unnecessary spaces
- Create a find/ replace formula
- Find where a certain character is in a cell
- Force excel to treat number as text
Key Lookup Functions
- VLOOKUP explained in simple terms
- Build your first VLOOKUP
- The many uses of VLOOKUP
- Vlookup approximate match (True instead of False)
- Vlookup partial text from single cell- fuzzy lookup
- Automatic Sorting in Excel
- Handling Error Messages- Remove the impact of #NA, #DIV/0! and more
SUM, COUNT, AVERAGE IFS
- Sum, count or average if certain cells match
- Sum, count or average cells e.g. bigger than
- Sum, count or average with many conditions
- Sum, count or average with partial matches
Date functions and issues
- How Excel Handles Dates
- Safest way to capture a date in Excel
- Pull the day, month or year out of the date
- Create a VALID Excel date with the day, month and year
- Calculate the proper end of month
- Determine what day of the week a date is
- When is the next workday
- How many working days between dates
- What week number is a date
- Make Excel recognise a date
Charting In Excel
- Excel Charting introduction
- Create a chart
- Align chart to the grid lines
- Chart doesn’t look right? First thing to try
- Customise the chart title, area, legend and more
- Vertical Axis- force the scale, reverse the order, labels and more
- Horizontal Axis- dates vs text, reverse order, show all labels
- Series- overlapping, big and small series, gaps
- Add more series to the chart- 3 ways
- Show big and small numbers on the same chart (and be able to see them)
- Show months, quarters AND years on the chart axis
- Bar Charts
- Pie Charts
- Area Charts
- Radar Charts (to compare projects?)
- Show mini trend charts for large reports
- Copy the format of one chart to another chart
- Remove Zeros from chart labels
- Combine a column with a line chart (or other combinations)
- Stop charts stretching when the column width changes
- Customise the series marker to your own image
- Add linked commentary directly to the chart
- Add commentary to the axis
- Add commentary to labels and call outs
- Add commentary to labels for older versions of Excel
- Hiding a series with NA()
- Create a waterfall chart (pre Excel 2016)
- Create a waterfall chart (Excel 2016 and higher)
- Understand and use XY charts e.g. Risk Register
- Change the default colour scheme in charts
- Create a chart template for re-use
- Add a trendline to a chart
- Benefit of using a table for chart data
- Link Excel chart to PowerPoint or Word
Feedback & Who Should Attend
Ideal option for companies wanting a virtual solution to their MS Excel training needs i.e.
- wanting to maintain social distancing during lockdown phases but continue to train their staff.
- with Excel users spread over wide geographic regions.
- with an international presence or with differing time zones.
- in countries where it is difficult to get qualified trainers to visit.
- that would rather not fly to attend the course.
Pre Requisite (s)
Requirements
Due to the dispersed nature of the training, we require that the following is addressed prior to running the course:
- Free Skills Assessment completed to ensure delegates are on the correct course.
- All delegates require a computer with access to:
-
- MS Excel
- a online platform (Microsoft Teams, Zoom etc)
- fast and reliable internet access (for live presenting)
- sufficient data to be able to stream the presentation for the entire period (estimate is 16 GB for a 2 day course)
- mobile phone or landline for communication in the case of internet failure for whatever reason.
- headphones to listen to lectures without distracting others
- whitelisting of our online portal and video clips (backup in case there are any internet issues on the presentation days)
-
Feedback
What an experience! Much easier to attend the online excel course as you learn just as much as you would when attending a class. Very impressed with the course – I would definitely recommend Audit Excel. Thank you for the journey!
I did the Excel beginners course and I can honestly say I enjoyed every minute. The videos that Adrian does is very clear and the training pages is very helpful, I would recommend Audit Excel to everyone considering online training
Course was easy to follow & understand – Informative & practical – The course has increased my efficiency (I had no idea excel could do so much!) – Short lessons make it easy to chip away at – even if you only have 15 minutes to spare at a time! – Ability to log in & revise is hugely beneficial I would gladly recommend this course to anyone utilising excel on a daily basis.
The content covered was very good – really like the fact that there is access to videos and the content page which has links to each worksheet is quite helpful and easy to maneuver.
From the Democratic Republic of Congo, did the online courses and visited SA for the face to face training sessions and had chance also to attend some of the public courses and the financial modelling course. Overall the training was very informative, learned great/new techniques and understand more about excel. Definitely can recommend to all!!
Intermediate Excel course is excellent! Very thorough, easy to follow and informative. Lots of great tips to help you work more efficiently. Highly recommended for intermediate and advanced Excel users.
Request info on training dates, prices and venue