Course Info
This course covers the advance features of Excel. It is suitable for Excel users who wish to enhance their knowledge on more advanced formulas and functions. At the end of this course you will have better understanding of the more advanced features of Excel. You will be able to link and consolidate information from different Excel files, manipulate data lists using filtering, subtotals and pivot tables. You will also be able to use logical and lookup functions.
What Will I Learn From This Course?
Create formulas to link multiple worksheets
Consolidate data across multiple worksheets
Create custom list and data form
Filter data using Advanced Filter feature
Compute subtotals
Analyze data using Pivot Table
Analyze and lookup data using database functions
Record macros and assign macros to button
Pre-requisites
Course Outline for This Programme
Module A1: Consolidating Data with Excel
- Creating Links
i. Linking Sheets Within the Workbook
ii. Linking to Other Workbooks
iii. Amending Broken Links - Using Data Consolidate
i. Consolidate By Position
ii. Consolidate By Category - Consolidate Data Using Formulas content
Module A2: Auditing a Worksheet
- Tracing Formulas and Errors
i. Trace cells that provide data to a formula (precedents)
ii. Trace formulas that reference a particular cell (dependents) - Error Checking
i. Correct common formula errors one at a time
ii. Correct an error value - Watch Window
i. Add cells to the Watch Window
ii. Remove cells from the Watch Window - Protecting Your Files and Worksheets
i. Locking & Unlocking Cells
ii. Setting Multiple Range Passwords
iii. Protecting The Contents of a Worksheet
iv. Protecting The Structure of a Workbook
v. Encrypting Your Workbook
Module A3: Managing Excel Data
Introducing Excel Tables
i. Inserting a Table
ii. Benefits of A Table
iii. Removing A TableSorting Data
i. Performing A Quick Sort
ii. Performing A Complex Sort
iii. Using A Custom List
iv. Sorting Based On ColorsEntering Data Using Forms
i. Customizing The Quick Access Toolbar
ii. Entering New Data
iii. Editing Data
iv. Performing A Criteria Base SearchFiltering Data
i. Using Filters
ii. Using Custom AutoFilterAdvanced Filtering
i. Using Multiple OR To Filter
ii. Using Multiple AND To Filter
iii. Using AND & OR To Filter
iv. Using Wildcards To FilterSubtotals
i. Adding Subtotals
ii. Adjusting Views with Subtotals
iii. Removing Subtotals
iv. Performing Nested SubtotalsValidating Data Entry
i. Creating A Drop Down List
ii. Creating A Customized Error Message
iii. Controlling Values & Dates
iv. Using Formulas to Control DataImporting Text Files
i. When to Use Delimited
ii. When to Use Fixed Width
iii. Using Text To Column to manipulate data
Module A4: Introduction to Pivot Tables and Pivot Charts
- Introduction to PivotTables
i. What Are PivotTables?
ii. Creating a PivotTable
iii. Customizing Your Report
iv. Filtering Data - Creating a Pivot Chart
i. Inserting A Pivot Chart
ii. Formatting & Adding Elements
iii. Removing Buttons From A Pivot Chart
Module A5: Logical & Lookup Category Functions
- Using the Function Library
- Introduction to Logical Functions
i. IF Function
ii. Nested IF function - Introduction to LookUp Functions
i. When to Use The Exact Match
ii. When to User The Nearest Match
iii. VLOOKUP
iv. HLOOKUP
v. Problems In VLOOKUP & HLOOKUP
Module A6: “What if” Analysis
- Creating Data Tables
i. Creating A One Input Data table
ii. Creating A Two Input Data table - Using Goal Seek For Simple Problems
- Using Solver For Complex Problems
i. Using Solver
ii. Minimizing & Maximizing Models
iii. Adding Constraints to Your Model
iv. Saving Solver Solutions to the Scenario Manager
v. Retrieving a Solution From the Scenario Manager
Module A7: Introduction to Macros
- Automating Tasks with Macros
i. Turning On The Developer Tab
ii. Viewing The Project Explorer
iii. Recording a Macro
iv. Running a Macro
v. Editing a Macro
vi. Assigning Macros to Buttons
vii. Saving A Macro Enabled Workbook
Appendix A: Excel Keyboard Short Cuts
- Keyboard access to the ribbon
- CTRL combination shortcut keys
- Function keys
- Other useful shortcut keys
