Take a leap forward in the way you use Excel. Learn from experts who have years of practical experience and are still developing high-quality Excel-based applications for clients throughout the region. Have you looked at all the “Advanced” Excel courses and thought “They’re not advanced”? Well, we have just the course for you!
● This course is for users who are confident in their Excel skills but know there is still a huge amount to learn and want to take their skills to the next level.
In this course, you will learn:
● Advanced formulae that will provide you with increased functionality plus improved spreadsheet structure.
● The power of Microsoft Query and SQL in extracting data directly from databases, accounting systems and other data sources.
● Techniques that will expand your understanding of the vast range of possibilities within Excel.
● The functions used by experts to increase consistency and reduce data entry.
● An awareness of the risk of VLOOKUP and the safer alternatives.
● Active X and Form controls to allow user interactivity while maintaining control of your spreadsheets.
● Powerful data analysis and visualization techniques to identify trends and anomalies.
● An introduction to Visual Basic programming so that you can write your own customized functions and perform tasks not possible with formulae alone.
Students will receive a course manual with presentation slides and reference materials.
There is no exam for this course.
● How to use array formulas to do things you can’t do with “normal” formulas
● How array formulas can make your workbooks more robust and secure
● How to harness the power of INDEX/MATCH
● Using OFFSET to produce more flexible and robust formulas
● How SUMPRODUCT can solve formula problems
● Using INDIRECT to improve the power and flexibility of your models
● The benefits of using HYPERLINK to dynamically create hyperlinks
● Autocorrect cheats that make you more efficient Advanced Names
● How dynamic ranges can improve your spreadsheet models and two different techniques to create these.
● What is a Named Formula and when should you use this?Advanced Functionality
● Input dependent drop-down lists
● Combo Boxes v List Boxes
● How to create a powerful Scenario Manager that displays live data from your the model under multiple scenarios
● How to create a Dynamic Chart that automatically re-sizes itself depending on the data available
● Formula-driven conditional formatting Advanced-Data Extraction and Manipulation
● Microsoft Query and SQL
● Extracting Data from external sources
● Pivot Table Tricks and new features in Excel 2010 plus Excel 2013 Power BI
● Introduction to Power Pivot, Power Query, and Power Map
● Importing data and linking without lookups
● Creating Custom Fields
● Power Map
● Ways to visualize data that has a geographic aspect Custom VBA Functions
● Extract Sheet Names
● Advanced Concatenate
● Impressive visualization techniques Highly useful VBA Code (Macros)
● Introduction to VBA and the key components of the VBA Editor
● Recording Macros & what to re-write
● Write VBA code
● Learn about risks and best practice
● Automatic Pivot Refresh
● Automatically show and hide sheets, rows, and columns
● Change-tracking code – how to build an audit trail into your models
Maximizing your Excel Productivity (1 day)