Adv. Excel - Business Professionals

 Home / Adv. Excel - Business Professionals

Adv. Excel - Business Professionals

 This isn't just Advanced Excel for the sake of it

 
Our Advanced Excel course offers real, practical benefits in the general use of spreadsheets; making your spreadsheets 
more accurate and reliable, and more automatic and interactive.
 
We show you: how to use advanced graphics techniques to maximise impact; how macros and Visual Basic automate your 
spreadsheets and increase interactivity; how superpower functions can solve complex problems elegantly and how PivotTables 
turn raw data into refined information that supports key decisions.
 
You will learn how to take Excel further through illustrated articles, practice exercises and video tutorials. Our test 
and diagnostic tool ensure we don't teach you what you already know.
 
Start learning today and join over 250,000 users globally.
 
We has been involved in computer software for 17 years during which he has provided a wide range of training services to 
professionals of all levels. His expertise lies in all the main Microsoft applications:
 
Access, Word, PowerPoint and Excel, about which he speaks frequently at conferences. 
 
We has been running hands-on Excel training courses for over 17 years, dealing with all aspects of Excel from basic 
competence to advanced data analysis. We is able to bring these years of practical experience to our Advanced Excel course,
ensuring that the course explains advanced concepts clearly and concisely, and with real, practical applications and even 
the occasional joke.
 
 
Supercharge your Excel abilities
 
We believe in effective training and tangible results which is why our Advanced Excel Course concentrates on Excel features 
and techniques that you will be able to apply every day in order to make your spreadsheets more reliable, more efficient, 
more automatic and to give them maximum impact. Each of the areas our syllabus addresses is intended to equip you with 
knowledge and techniques that you will be able to use over and over again to not only make your own spreadsheets better, 
but also to enable you to help your colleagues and solve their Excel problems.
 
Our test and diagnostic tool objectively assess your knowledge and understanding at the outset so that we can individually 
tailor the course for you.
 
Syllabus
 
Design and risk
 
1. Templates
 
Get a head start with templates ● Set up an Excel template that will save you an hour each time you create a new workbook 
Create new default workbook and worksheet templates ● Learn how modular templates can make your spreadsheets more consistent 
and much quicker to set up ● Your Excel, your way: customise the Excel interface to put the tools that you need at your fingertips.
 
2. Efficiency and Risk
 
Make spreadsheets more efficient and less risky ● How to build in checks and controls from the outset ● Key techniques 
that reduce risk and increase automation and efficiency ● Introduce standards to help your team ● Documentation and review 
tools
 
3. Data Validation
 
Extended uses of Data Validation ● Working with validation formulae ● Lists and lists that depend on other list selections
Other methods of tracking down invalid entries
 
Advanced techniques and problem solving
 
4. Functions and Superpowers
 
Functions with superpowers ● Finding the right functions and how they work ● Functions with hidden powers – MOD() for 
patterns, OFFSET() for simple choices, INDIRECT() to manipulate formulae ● Making references to ranges of cells adapt 
automatically for new data
 
5. Array Formulae
 
Array formulae ● One formula, one million calculations ● SUMPRODUCT() - all the gain of an array formula with less of the
 pain
 
6. Tables
 
Tables – structure comes to Excel ● Why Tables are so much more than just a new format ● Make your spreadsheets more 
automatic with a single command ● Table formulae – instant readability ● Data tools in Tables ● It’s not all good
 
7. Advanced Range Names
 
Advanced Range Names ● Use the same name on different sheets ● Using the Name Manager ● Names in formulae – efficient
 inclusion of Names, the use of intersections ● Formulae in Names – how to allocate a formula directly to a Range Name
 and why you might need to
 
8. What if?
 
Using Excel to help you make decisions ● Use Goal Seek to find where you need to start to get where you need to go ● Use
 an Excel Data Table to calculate dozens of possible outcomes ● Create and manage alternative scenarios ● Make more profit 
or incur less expense by using Excel Solver to identify the best solution
 
9. Problem solving
 
Problem solving ● Calculations that make decisions – understanding TRUE ad FALSE ● What Boolean Logic is and why it’s 
useful in practice ● combining logic and arrays to solve complex problems ● Practical examples
 
Spreadsheets that talk back – Excel interactivity
 
10. Form Controls
 
Use Form controls to make life easier for users ● Use a Spin Button to choose a value easily ● Use an Option Button to 
choose with a single click ● Choosing from lists
 
11. Visual Basic and Macros
 
Create macros by writing Visual Basic code ● A macro that performs one or more actions on selected cells ● Write your own 
Excel functions with VB code ● Understanding volatile functions ● Trigger a macro when a particular cell is changed ● 
Handling errors elegantly
 
Give your spreadsheets impact with graphics
 
12. Practical Interactivity with VBA
 
Exchanging information with VB code ● Displaying a Message box ● Asking for user input using an Input box ● Create an Excel
 form with a List box containing values from a range of cells ● Sample VB projects: an automatic index to sheets, printing 
selected ranges ● Avoiding macros when they’re not really necessary
 
13. Conditional Formatting
 
Conditional Formatting – beyond simple Conditional Formats ● Basing conditions on a formula and deciding whether a batsman 
is out or not ● Choose currency symbols for a whole sheet by changing a single cell ● Getting your rules in the right order
 and knowing when to stop ● Graphical Conditional Formats – the detailed options ● Using invisibility to your advantage
 
14. Charts that Inspire
 
Create charts to inspire ● What makes a good chart – is it really a 6.5 cucumbers? ● Simple steps to make your charts 
clearer ● Why small can be better than large ● Are pie charts evil? ● Why 3D charts can be 50% worse ● Mixed chart types,
 trendlines and projections ● Advanced chart techniques: break-even lines and waterfall charts ● Pictures in chart columns
 
15. Sparklines
 
In-cell charts – showing 12 times as much information in the same amount of space ● Careful with that Axis ● The different
 types of Sparkline: lines, columns and win loss ● Sparklines based on a dynamic data range
 
16. Graphics Tricks and Techniques
 
Further graphics tips and techniques ● Taking dynamic pictures with the Excel camera ● Use the Excel camera to combine 
areas from multiple sheets on the same sheet of paper ● Formatting Excel Camera pictures ● Power View – using the 
Excel 2013 data visualisation add-in including plotting values on maps and ‘playing’ bubble charts
 
Turning data into decisions
 
17. Working with External Data
 
Getting at your data using the Get External Data tools ● Understand relational databases in 10 minutes and liberate your 
data ● Excel 2013 Data Model create relationships within Excel
 
18. Advanced Uses of PivotTables
 
Use advanced PivotTable techniques to do more with your data ● Calculated Fields and Calculated items ● Using PivotTables 
as the calculation engine behind management reports ● Using GETPIVOTDATA() and CUBE formulae to create flexible reports 
● Excel 2010 and 2013 Slicers and Timelines - make your PivotTables more interactive ● Working with Pivot Charts ● Building 
and interactive dashboard using PivotTables and Slicers
 
19. PowerPivot
 
The Excel 2010 and 2013 Power Pivot add-in ● PowerPivot data tools ● Calculations in Power Pivot – an introduction to 
Data Analysis Expressions (DAX) ● DAX in table columns ● DAX to create new measures and calculated fields ● Understanding 
advanced DAX expressions – functions that combine calculations and database techniques ● the Time Intelligence DAX 
functions – why you need a table containing all possible dates
 
20. Reporting with PowerPivot
 
Creating powerful reports with PowerPivot ● Hierarchies, Perspectives and Sets ● Adding Key Performance Indicators (KPIs)
 to a Power Pivot report ● Creating CUBE formulae to report on your data in just the way you want to ● Use all these
 techniques to turn millions of rows of data into a dashboard that supports better decisions