Excel Advanced

Need to squeeze the most out of Excel?  The Advanced level takes you through some of Excel's less well known areas, as well as the development of more complex formulas using real life problems.  Along the way you'll also learn how to record and edit simple macros, use the new and very cool conditional formatting in 2007/2016, and dig deeper into data management and analysis.

Who is this for?
Aimed at those who want to progress from the Intermediate level of Excel use, this course focuses on problem solving using Excel features and formulas.  Any skill gaps (for example where the Intermediate course has not been undertaken) will be covered.

What does it cover?
This course builds upon the skills in the intermediate level, introducing some new concepts and presenting some existing ones in greater depth. Data analysis in covered in some depth, with more detailed coverage on pivot tables an Excel’s “What If?” tools. Macros are also introduced to show the power and potential of using this for recording and running repetitive tasks.

How long is it?
1 day or can be split into several sessions if required

Where is it run?
On your premises or via one of the training centres I use


What you'll learn ...

Dissecting data tables
Advantages and disadvantages
Creating a data table
Adding and deleting data
Creating new columns
Converting back to a range
Using functions with tables
SUMIF() and COUNTIF()
Looking up data between tables
Connecting to Access
More on functions!
Review of named ranges
The new IFS – SUMIFS(), AVERAGEIFS(), COUNTIFS()
The Excel text functions – their purpose, creation and uses
Nesting functions – searching for text
Nesting the IF() for multiple decisions
Looking up data from other sheets
Using VLOOKUP() with a drop down list
Combining VLOOKUP() and IF()
Using the approximate match
Using Excel’s data analysis tools
Sorting review
Soring by columns and custom lists
Filter review Filtering with the simple filter & Advanced Filter

Using criteria from other sheets
Send results to another sheet
Using simple & multiple level sub totals
Creating pivot tables
Creating review
Updating the results
Adding a calculated item and field
Changing the summary calculation
Calculating running sums and differences with the pivot table
Creating grouped items in a pivot table
Creating a pivot chart
Excel’s What if? Tools
Scenario manager – creating and showing scenarios
Goal seeker – setting the cell to a value
Solver – juggling the variables!
Being repetitive – recording and running macros
Recording a macro – relative versus absolute
Running a macro
Assigning a macro to a toolbar
Ending with style – Excel’s quick formats
Creating and applying a cell style
Modifying a style
Applying and modifying conditional formatting

Download the Course Outline here