Microsoft Excel - Advanced
Date: September 2022
Time: 9:30am to 4:30pm
Level: Excel Advanced - Level 3
Course Aims & Objectives
This intensive course will prepare you for your continued lifelong learning of Excel. It will concentrate on functions and formulas and how to manage, manipulate and extract data. It will show you how to create complex formulas. It will teach you how to design and create large databases with inbuilt controls, so that you and your colleagues get the most from Excel.
Who Should Attend?
Participants who attend this Advanced Level course should have a very good knowledge of formulas and should be creating them on a regular basis. You should already understand and use of all the items covered on the Intermediate level course, including the IF and VLOOKUP functions. You must know the difference between Absolute and Relative cell referencing and a knowledge of Charts and formatting is required also
Items not listed here are covered at another level. Some items may not be covered due to time constraints, but all will be covered in the notes. Items not listed here (including Macros, VBA, Dashboards and Add-Ins) are not covered on this course and are covered on other courses at a higher level
•Review of KEY Intermediate Excel Content.
•Nesting functions within other functions.
•Advanced Lookup Functions.
•Use Data Validation to force compliance with input standards.
•Creating dynamic drop down lists.
•Working with Tables within Excel
.•Using Named Cells and Named Ranges.
•Protect a document and specific cells.
•Dealing with error messages in formulas.
•Create and use a Pivot Table.
- Field Settings.
- Creating Formulas in Pivot Tables.
- Rearranging/filtering Pivot Tables
•Grouping data in Pivot Tables.
•Updating and Refreshing options.
Use the following Excel functions:
•Nesting IF Functions
•VLOOKUPs within VLOOKUPS
UPON RECEIVING YOUR BOOKING YOU WILL BE SENT A PRE-COURSE ASSESSMENT THAT MUST BE COMPLETED BEFORE THE TRAINING DATE
Important notes from the Trainer re these Online courses:
Participants need to ensure that they have Microsoft Excel working on their computer and that their sound is working.
They MUST know how to switch between applications during the course, i.e. They will have a folder open with work files, they will also have ZOOM open, and they will have Excel open. On the day, they will watch the tutor perform the task on ZOOM. They will then switch to the folder which contains the work files and they will open the file. The file will open in Excel where they will perform the task themselves. they must know how to open, save and close work files before attending the course. They therefore need to be able to switch between all three areas frequently. If they are unable to do this they should consider not attending an online course as the tutor will be unable to assist them from a distance – a classroom based course would be more beneficial in this circumstance.
The trainer will send a link to the work files needed for the course.
You will need to download these work files before the course and place them in a folder on your desktop.
All participants must have a copy of Microsoft Excel on their laptop or PC. Ideally the version of Excel will be from Office 365 or Office 203 or Office 2016.
You must have internet access. Your laptop or PC must have either built-in or connected speakers and ideally should also have a webcam.
You will be sent an invitation to join the course and you may need to download and install the ZOOM app if it is not already on your computer.
In an ideal setup, each participant would have a PC with duel screens, a camera, microphone and speakers, however we know this is not normally possible. At a minimum, each person must have a laptop with Excel installed and working. You must be able to hear the trainer therefore must have speakers or headphones attached to your laptop or PC. The trainer would prefer to be able to see you. It is possible to join the course on your smart phone, where you have a microphone and camera and then you can work on your computer,
however while this works, it isn’t ideal because you cannot share your computer screen with the trainer if you are in trouble. Also, it can be difficult to see the detail of an Excel worksheet on a tiny phone screen.