Excel For Experts
This course is targeted at advanced users who require the highest level of proficiency in Microsoft Excel. Learn about creating your own custom functions, automating your spreadsheets with macros, advanced uses of Visual Basic for Applications (VBA), creating dynamic charts, working with advanced tools, etc.
- Advanced Data Validation; including custom validation using formulasHide FormulasThe ‘Go to Special…’ menuDatabase Function: DAVERAGE, DCOUNT, etc.
Formulae and Functions
- Design Custom ReportsWriting Custom FormulasAdvanced Functions (INDEX, MATCH, SMALL, etc.)VLOOKUP vs. INDEX-MATCH combinationUse of wildcard characters in look-up functionsUser-Defined Functions (UDFs)Work with Excel ‘Tables’Wrap Formula/Expression Results
Working with Named Ranges
- Create dynamic Named Ranges with FormulasWork with Array Formulas and Functions
Advanced Charting Tools
- Create Dynamic Charts (e.g. using one chart for varying time series, locations, etc.)Use Form Controls for ChartsCharts with Secondary AxisLink Charts to MS Word and PowerPoint, with automatic updateSpecial charts
- Waterfall ChartGANTT ChartTornado Chart
- Dashboard LayoutPre-formatting the WorksheetAutomating Charts with use of Form Controls:Spin buttonOption box
- Advanced Custom FormatsApply Custom Conditional Formattingmwith FormulasCreate GANTT Charts with Conditional Formatting
- Building ScenariosSensitivity TablesOptimisation using SolverUsing Form Controls on Worksheets
- Option ButtonSpin ButtonCombo BoxTrack Changes by Multiple Users
Visual Basic for Applications
- IntroductionRecording MacrosAssigning Macros to Form ControlsEditing MacrosKey Visual Basic commands e.g.
- - IF…..THENDO UNTIL…….LOOPDO WHILE…….LOOPWITH ….END WITHWorking with ‘ActiveX’ ControlsTesting and Debugging CodeDesigning / implementing custom Applications