Advanced Excel is for people who already can create moderately complex spreadsheets and still want to add more sophistication and automation.
This training will equip participants with relevant tools and functions in MS Excel for the development of automated systems to replace certain manual and repetitive processes.
By the end of this course participants will be able to:
Use outlines, range names, databases and the data form
Use AutoFilter and advanced filters
Work with Data Series
Use advanced functions
Use Scenarios and Goal Seek
Use templates, data tables and solve problems
Use automatic formatting and styles
Work with views, consolidate worksheets
Create and revise PivotTables
Use, record and edit macros
Participants should already be using Excel on a regular basis. Alternatively, they must have a good knowledge of Excel introduction topics
Work with Outlines
Apply an Outline, Collapse/Expand an Outline, Modify Outline Settings, Clear an Outline, Use Auto Outline, Create Subtotals in a List, and Remove Subtotals from a List.
Use Multiple Workbooks
Open and Arrange Multiple Workbooks, Save a Workspace, Create and Maintain Links.
Use Range Names
Jump to a Named Range, Assign Names, Use Range Names in Formulas, Create Range Names from Headings, Apply Range Names, Delete Range Names, Use Range Names in 3-D Formulas, Create 3-D Range Names, Use 3-D Range Names in Formulas.
Work with Tables
Use Tables, Create a Table from Existing Data, Change the Table Name, Change the Table Style, Change Table Style Options, Create a Total Row, Add Table Rows and Columns, Insert/Delete Table Rows/Columns, Create a Calculated Column, Select Parts of a Table, Move a Table, Sort Data by Multiple Levels, Use Text Filters, Use Number Filters, Use Data Validation, Validate Data using a List, Create a Custom Error Message, Remove Data Validation.
Work with Advanced Filters
Create a Criteria Range, Use a Criteria Range, Show All Records, Use Comparison Criteria, Use an Advanced And Condition, Use an Advanced Or Condition, Copy Filtered Records, Use Database Functions, Find Unique Records, Remove Duplicates from a Table.
Work with Views
Create, Display and Delete a Custom View.
Save a Shared Workbook, View Users Sharing a Workbook, View Shared Workbook Changes,
Highlight Changes, Resolve Conflicting Changes, Add a History Worksheet, Review Tracked Changes, Merge Shared Workbook Files.
Create, Display and Delete a Custom View.
Consolidating Worksheets by Category or by Position
Use Advanced Functions
Use the VLOOKUP and HLOOKUP Function, Use the IF Function and Nested IF Function, Use the ISERROR Function, Use an AND Condition with IF, Use an OR Condition with IF, Use the ROUND Function, Limit the Precision of Numbers.
Create a PivotTable Report, Add PivotTable Report Fields, Select a Report Filter Field Item, Refresh a PivotTable Report, Change the Summary Function, Add New Fields to a PivotTable.
Add, Remove and Format Gridlines, Format an Axis, Change the Axis Scale, Format the Data Series, Add Data - Different Worksheets, Use a Secondary Axis, Change Data Series Chart Types, Add a Trendline, Create User-defined Charts, Create, Customise, Remove Sparklines.
Save a Workbook as a Template, Use a Template, Edit a Template, Delete a Template, Create Default Templates.
Use Auditing Tools
Display the Formula Auditing Toolbar, Display/Remove Dependent and Precedent Arrows, Remove Tracer Arrows, Use Auditing Tools Buttons.
Use Scenarios and Seeking Goal
Use the Scenario Manager, Create, Display and Edit a Scenario, Create a Scenario Summary Report, Use Goal Seek.
Use Solver, Save a Solution as a Scenario, Change a Constraint, Create a Solver Report, View Solutions using Scenarios.
Use Automatic Formatting and Styles
Apply an AutoFormat, Change AutoFormat Options, Extend List Formats and Formulas, Create a Style by Example, Create a New Style, Edit an Existing Style, Merge Styles.
Use Conditional and Custom Formats
Apply Conditional Formats, Change a Conditional Format, Add a Conditional Format, Create a Custom Conditional Format, Use Data Bars, Delete a Conditional Format, Create a Custom Number Format.
Save a Shared Workbook, View Users Sharing a Workbook, View Shared Workbook Changes, Highlight Changes, Resolve Conflicting Changes, Add a History Worksheet, Review Tracked Changes, Merge Shared Workbook Files.
Create a PivotTable Report, Add PivotTable Report Fields, Select a Report Filter Field Item, Refresh a PivotTable Report, Change the Summary Function, Add New Fields to a PivotTable Report, Move PivotTable Report Fields, Use