Excel Advanced
Days 1 Code EX133
Excel Advanced
Course Details
This course introduces the skills necessary for working with pivot tables. Tasks include creating, revising, and formatting pivot tables, and adding pivot table fields. The course also introduces the skills necessary to utilize macros, and customize the Ribbon. It includes tasks to use, record, edit, and debug macros. Students will also learn how to use the Personal Macro Workbook, custom functions, and buttons; create a macro buttons. The course also aims to introduce a range of additional features to improve the knowledge of the learner and increase his/her portfolio of spreadsheet skills.
Prerequisites
Attended the Excel Introduction and Intermediate courses or have a very good working knowledge of the subject
Course Outline
- Using V and H Lookup Functions
- IF Functions (Nested, And, Or and ISERROR)
- Using Round Function
- Limited Precision
Lesson 2: Using Other Functions
- Function Argument
- Financial Functions
- Logical Functions
- Date Functions
Module 3: Using Worksheet Protection
- Protecting a worksheet
- Creating Allow-Editing Ranges
- Deleting Allow-Editing Ranges
- Protecting Workbook Windows
- Unprotecting Workbook Windows
- Assigning a Password
Lesson 4: Using Conditional Formatting
- Applying Conditional Formats
- Adding a Conditional Format
- Deleting a Conditional Format
- Creating a Custom Conditional Format
- Quick Analysis
- Using Data Bars
Lesson 5: Consolidating Worksheets
- Using Consolidated Worksheets
- Consolidating by Category
- Consolidating by Position
Lesson 6: Creating/Revising PivotTables
- Creating a PivotTable Report
- Changing the Summary Function
- Creating a Page Field Report
- Creating a Pivot Chart Report
- Multiple Consolidated Ranges
Lesson 7: Using Auditing Tools
- Displaying the Formula Auditing Toolbar
- Displaying /Removing Dependant Arrows
- Displaying/Removing Precedent Arrows
- Removing all Tracer Arrows
- Using the Trace Error Button
- Tracing Cells Causing Errors
- Using the Error Checking Button
- Using the Evaluate Formula Button
- Using the Watch Window
Lesson 8: Using Macros
- Opening a Workbook Containing Macros
- Running a Macro
- Opening the Visual Basic Editor Window
- Using the Visual Basic Editor Window
Lesson 9: Recording Macros
- Recording a Macro
- Using Relative References
- Deleting a Macro
Lesson 10: Editing Macros
- Writing a New Macro
- Copying Macro Commands
- Editing Macro Commands
- Typing Macro Commands
- Running a Macro from the Code Window
Lesson 11: Creating Macro Buttons
- Creating a Macro Button
- Formatting a Macro Button
- Moving /Sizing a Macro Button
- Adding a Macro to the QAT
Lesson 12: Exporting and Importing Data
- Exporting data to other Applications
- Importing Data
- Removing the Query Definition
- Importing Dynamic Data from the Web
