Ms Excel Advance 2013/2016 - TED Learning

DAY 1

MODULE A1: CONSOLIDATING DATA WITH EXCEL

1. Creating Links
2. Linking Sheets Within the Workbook
3. Linking to Other Workbooks
4. Amending Broken Links
5. Using Data Consolidate
6. Consolidate By Position
7. Consolidate By Category
8. Consolidate Data Using Formulas

MODULE A2: AUDITING A WORKSHEET

1. Tracing Formulas and Errors
2. Trace cells that provide data to a formula (precedents)
3. Trace formulas that reference a particular cell (dependents)
4. Error Checking
5. Correct common formula errors one at a time
6. Correct an error value
7. Watch Window
8. Add cells to the Watch Window
9. Remove cells from the Watch Window
10. Protecting Your Files and Worksheets
11. Locking & Unlocking Cells
12. Setting Multiple Range Passwords
13. Protecting The Contents of a Worksheet
14. Protecting The Structure of a Workbook
15. Encrypting Your Workbook

MODULE A3: MANAGING EXCEL DATA

1. Introducing Excel Tables
2. Inserting a Table
3. Benefits of A Table
4. Removing A Table
5. Sorting Data
6. Performing A Quick Sort
7. Performing A Complex Sort
8. Using A Custom List
9. Sorting Based On Colours
10. Entering Data Using Forms
11. Customising The Quick Access Toolbar
12. Entering New Data
13. Editing Data
14. Performing A Criteria Base Search
15. Filtering Data
16. Using Filters
17. Using Custom AutoFilter
18. Advanced Filtering
19. Using Multiple OR To Filter
20. Using Multiple AND To Filter
21. Using AND & OR To Filter
22. Using Wildcards To Filter
23. Subtotals
24. Adding Subtotals
25. Adjusting Views with Subtotals
26. Removing Subtotals
27. Performing Nested Subtotals
28. Validating Data Entry
29. Creating A Drop Down List
30. Creating A Customised Error Message
31. Controlling Values & Dates
32. Using Formulas to Control Data
33. Importing Text Files
34. When to Use Delimited
35. When to Use Fixed Width
36. Using Text To Column to manipulate data

MODULE A4: INTRODUCTION TO PIVOT TABLES AND PIVOT CHARTS

1. Introduction to PivotTables
2. What Are PivotTables?
3. Creating a PivotTable
4. Customizing Your Report
5. Filtering Data
6. Creating a Pivot Chart
7. Inserting A Pivot Chart
8. Formatting & Adding Elements
9. Removing Buttons From A Pivot Chart

DAY 2

MODULE A5: LOGICAL & LOOKUP CATEGORY FUNCTIONS

1. Using The Function Library
2. Introduction to Logical Functions
3. IF Function
4. Nested IF function
5. Introduction to LookUp Functions
6. When to Use The Exact Match
7. When to User The Nearest Match
8. VLOOKUP
9. HLOOKUP
10. Problems In VLOOKUP & HLOOKUP

MODULE A6: WHAT IF ANALYSIS

1. Creating Data Tables
2. Creating A One Input Data table
3. Creating A Two Input Data table
4. Using Goal Seek For Simple Problems
5. Using Solver For Complex Problems
6. Using Solver
7. Minimising & Maximising Models
8. Adding Constraints to Your Model
9. Saving Solver Solutions to the Scenario Manager
10. Retriving a Solution From thr Scenario Manager

MODULE A7: INTRODUCTION TO MACROS

1. Automating Tasks with Macros
2. Turning On The Developer Tab
3. Viewing The Project Explorer
4. Recording a Macro
5. Running a Macro
6. Editing a Macro
7. Assigning Macros to Buttons
8. Saving A Macro Enabled Workbook

APPENDIX A: EXCEL KEYBOARD SHORT CUTS

1. Keyboard access to the ribbon
2. CTRL combination shortcut keys
3. Function keys
4. Other useful shortcut keys