Course on Advanced MS Excel & Macros

Duration: 16 Hours
4 Hours Per day

Automate and extend Excel by adding Advanced Formula to your workbooks.
Learn Advanced Excel to make your Excel spreadsheets much more powerful.

Introduction
Participants will learn to how to use pivot table in excel, use advanced functions of Excel to improve productivity, enhance spreadsheets with templates, charts, graphics, and excel formulas and streamline their operational work.

They will apply visual elements and advanced formulas to a worksheet to display data in various formats. Students will also learn how to automate common tasks, apply advanced analysis techniques to more complex data sets, collaborate on worksheets with others, and leverage on Excel’s 2016 advanced functionality to simplify and streamline their day-to-day work.

Target Audience

Users with a good understanding of Microsoft Excel 2007, 2010, 2013 or 2016 who want to expand on their capabilities in a short space of time. Delegates should have a good working knowledge and understanding of the concepts covered in the Introduction and Intermediate courses.

Program Objectives
After completion of this program, the participants will be able to the following
• Create and use defined names in a workbook
• Work with logical function in excel
• Use a variety of data validation techniques
• Use a range of lookup and reference functions
• Create summaries in your spreadsheets using subtotals
• Understand and create simple pivot tables
• Construct and operate pivot tables using some of the more advanced techniques
• Perform a variety of analytical tasks using PowerPivot
• Use the data consolidation feature to combine data from several workbooks into one
• Create and work with scenarios and the scenario manager
• Use solver to solve more complex and intricate problems
• Import data into excel and export data from excel
• Create and use a range of controls in a worksheet
• Create recorded macros in excel
• Import data into excel and export data from excel

• Module 1: Advanced Formula Tasks
o Understanding Relative and Absolute Cell References
o Using Multiple Cell References
o Using 3D References
o Using Array Formulas

• Module 2: Working with Named Ranges
o Understanding Named Ranges
o Defining Named Ranges
o Editing Named Ranges
o Deleting Named Ranges
o Using Named Ranges in Formulas

• Module 3: Using Advanced Functions
o Using the SUMIF, SUMIFS, RANK, FORCAST, COUNTIFS, PMT Function
o Using the DATEDIF, FV Function
o Using VLOOKUP and HLOOKUP, INDEX & MATCH, IFERROR Functions
o Using IF, Nested If, AND, OR Functions

• Module 4: Resolving Formula Errors
o Tracing Formula Precedents and Dependents
o Showing Formulas
o Evaluating Formulas
o Setting Error Checking Options
o Using Error Option Buttons
o Running an Error Check

• Module 5: Managing Data
o Consolidating Information
o Removing Duplicates
o Configuring Data Validation
o Transposing Data
o Converting Text to Columns

• Module 6: Outlining and Grouping Data
o Outlining Data
o Showing and Hiding Outline Details
o Grouping Data
o Creating Subtotals
o Removing Outlining and Grouping

• Module 7: Advance charting
o Combining different chart types
within one plot area
o Creating user defined charts
o Saving and using chart templates
o Creating a Thermometer Chart and using Dashboard
o Creating a Doughnut Chart
o Creating a Pareto chart to analyze 80-20 Trends
o Advanced Options in Pivot Charts for
o creating versatile management dashboards
o Interactive charts

• Module 8: Working with Scenarios
o Creating Scenarios
o Loading Scenarios
o Merging Scenarios
o Editing Scenarios
o Creating a Scenario Summary Report
o Deleting Scenarios

• Module 9: Using Solver
o Setting up the Worksheet
o Running Solver
o Generating Reports and Scenarios with Solver
o Modifying Constraints
o Setting Solver Options

• Module 10: Analyzing Data
o Enabling the Analysis ToolPak
o Using Data Analysis Tools
o Using Goal Seek
o Using Data Tables

• Module 11: Advanced PivotTable Features
o Creating a Basic PivotTable
o Creating a Basic PivotChart
o Using the PivotTable Fields Pane
o Adding Calculated Fields
o Sorting Pivoted Data
o Filtering Pivoted Data
o Create Pivot Table using multiple Table,
o Create Filter Page Report
o Slicer

• Module 12: Using Macros
o Recording a Macro
o Writing a Macro using the Visual Basic Editor
o Editing a Macro
o Running a Macro