24hrs – ICDL Perform Advanced Spreadsheet Functions (Excel Advanced)

Advanced Formatting

• Considerations in Designing a Spreadsheet
• Working with Cell Styles & Document Themes
• Apply, Create & Manage Conditional Formatting
• Creating a Custom Number Format

Working with Tables

• Create a Table & working with the Total Row
• Sorting & Filtering a Table
• Formatting the Table
• Converting to a Range

 Advanced Functions and Formulas

• Formulas with Multiple Operators
• Using and Managing Defined Names
• Using Mixed Cell Referencing
• Date and Time Functions
• Using Logical Functions (IF, AND and OR)
• Using Lookup Functions
• Mathematical and Statistical Functions
• Using Financial, Text & Database Functions
• Using 3D Reference within SUM Function

Validating Data and Auditing Worksheets

• Using Data Validation
• Auditing Tools
• Displaying and Tracing Formulas
• Understanding Formula Errors

Managing Charts

• Editing, Adding, and Removing Chart Data
• Changing Chart Data
• Change Scales and Display Unit of the Axis
• Add Secondary Axis & Change Series Chart Type
• Changing and Aligning Data Labels and Chart Legend

Working with Data Ranges

• Sorting by a Custom List
• Creating a Custom AutoFilter
• Using Advanced Filter

Working with PivotTables

• Creating a PivotTable
• Specifying PivotTable Data
• Changing a PivotTable’s Calculation
• Filtering and Sorting a PivotTable
• Working with PivotTable Layout
• Grouping PivotTable Items
• Updating & Formatting a PivotTable
• Creating a PivotChart
• Using Slicers between PivotTables

Analyzing Data

• Creating Scenarios
• Working with Data Tables
• Using Goal Seek
• Using Text to Columns
• Grouping and Outlining Data
• Using Subtotals
• Consolidating Data by Position or Category

Managing Templates

Linking and Importing Data

• Inserting a Hyperlink
• Linking Data & Managing External Links
• Importing Data
• Working with Existing Data Connections

Working with Macros

• Recording a Macro
• Playing and Deleting a Macro
• Adding a Macro to the Quick Access Toolbar

Managing Collaborative Editing

• Working with Comments to Cells
• Tracking Changes
• Protecting Worksheets and Worksheet Elements
• Protecting a Workbook

This course is meant for users who have good basic to intermediate level of knowledge on Microsoft Excel and would like to further extend the knowledge on more advanced functions of Excel to become more productive in their work.

This course is most suitable for Account Executive, Admin Executive, and HR Personnel who deals with number reporting. It allows them to create managed worksheets, create charts, do calculations to manage reports such as inventory listing, payrolls, invoices, payment and product pricing even more professionally.