This course is designed to equip learners with advanced spreadsheet skills using Microsoft Excel. It focuses on enhancing productivity, improving data analysis, and producing professional reports. The module aligns with the ICDL Management Spreadsheets certification and is ideal for individuals seeking to manage complex data effectively in a business or administrative context.
Learning Outcomes:
Apply advanced formatting and analysis features in Excel 365
Apply best practices in data validation, protection, and auditing
Automate repetitive tasks using macros
Confidently use advanced spreadsheet features to manage and analyze data
Key Topics:
Advanced formatting, cell styles, and custom views
Date, logical, and lookup functions
Workbook protection and version management
Template development and macro basics
Module 1. Using Conditional and Custom Formats
- Applying Conditional Formats
- Changing a Conditional Format
- Adding a Conditional Format
- Creating a Custom Conditional Format
- Using Data Bars
- Deleting a Conditional Format
- Creating a Custom Number Format
- Labs
Module 2. Using Templates
- Copying Worksheets
- Hiding Columns and Rows
- Unhiding Columns and Rows
- Insert Comments
- Saving a Workbook as a Template
- Using a Template
- Editing a Template
- Inserting a New Worksheet
- Hiding/Unhiding a Worksheet
- Deleting a Template
- Finding Online Templates
- Labs
Module 3. Working with Databases
- Creating Subtotals in a List
- Removing Subtotals from a List
- Sorting Data by Multiple Levels
- Using Custom Sort
- Using Data Validation
- Validating Data using a List
- Creating a Custom Error Message
- Removing Data Validation
- Labs
Module 4. Working with Advanced Filters
- Creating a Criteria Range
- Using a Criteria Range
- Showing All Records
- Using an Advanced And Condition
- Using an Advanced Or Condition
- Copying Filtered Records
- Using Database Functions
- Removing Duplicates from a Table
- Labs
Module 5. Revising Charting Features
- Formatting an Axis
- Changing the Axis Scaling
- Formatting the Data Series
- Adding Data from Different Worksheets
- Using a Secondary Axis
- Changing Data Series Chart Types
- Changing Source Data Range
- Labs
Module 6. Creating/Revising PivotTables
- Creating a PivotTable Report
- Adding PivotTable Report Fields
- Selecting a Page Field Item
- Refreshing a PivotTable Report
- Changing the Summary Function
- Adding New Fields to a PivotTable Report
- Moving PivotTable Report Fields
- Hiding/Unhiding PivotTable Report Items
- Deleting PivotTable Report Fields
- Creating a Page Field Report
- Formatting a PivotTable Report
- Creating a PivotChart Report
- Grouping Data Manually
- Labs - Creating/Revising PivotTables
Module 7. Using Auditing Tools and Range Names
- Jumping to a Named Range
- Assigning Names
- Using Range Names in Formulas
- Creating Range Names from Headings
- Applying Range Names
- Deleting Range Names
- Using Range Names in 3-D Formulas
- Displaying/Removing Dependent Arrows
- Displaying/Removing Precedent Arrows
- Showing Formulas
- Labs
Module 8. Exporting and Importing Data
- Importing Data from Text Files
- Changing External Data Range Properties
- Removing the Query Definition
- Creating a Hyperlink
- Editing a Hyperlink
- Labs
Module 9. Using Advanced Functions
- Using the VLOOKUP Function
- Using the HLOOKUP Function
- Using the IF Function
- Using Nested IF Functions
- Using the ISERROR Function
- Using an AND Condition with IF
- Using an OR Condition with IF
- Using the ROUND Function
- Using COUNTIF Function
- Using RANK Function
- Using Financial Functions
- Using Text Functions
- Labs
Module 10. Using Scenarios
- Creating a Scenario
- Displaying a Scenario
- Editing a Scenario
- Creating a Scenario Summary Report
- Working with Data Tables
- Placing Formulas in Data Tables
- Creating a One-Variable Table
- Creating a Two-Variable Table
- Labs
Module 11. Using Worksheet Protection
- Unlocking Cells in a Worksheet
- Protecting a Worksheet
- Unprotecting a Worksheet
- Creating Allow-Editing Ranges
- Deleting Allow-Editing Ranges
- Protecting Workbook Windows
- Unprotecting Workbook Windows
- Assigning a Password
- Opening a Password-protected File
- Removing a Password
- Labs - Using Worksheet Protection
Module 12. Tracking and Merging Workbooks
- Saving a Shared Workbook
- Viewing Users Sharing a Workbook
- Viewing Shared Workbook Changes
- Changing the Update Frequency
- Highlighting Changes
- Managing Conflicting Changes
- Resolving Conflicting Changes
- Setting Change History Options
- Adding a History Worksheet
- Reviewing Tracked Changes
- Merging Shared Workbook Files
- Labs
Module 13. Recording Macros
- Recording a Macro
- Saving a Macro-Enabled Workbook
- Running a Macro
- Assigning a Shortcut Key
- Using a Shortcut Key
- Deleting a Macro
- Adding a Macro to Quick Access Toolbar
- Deleting a Macro Button from QAT
- Labs
.png?width=50&name=Artificial%20Intelligence%20(AI).png)
-1.png?width=360&name=Server%20(4)-1.png)
/AI%20(6)-1-1.png?width=360&name=AI%20(6)-1-1.png)
/AI%20(8)-1.png?width=360&name=AI%20(8)-1.png)
-1.png?width=360&name=Automation%20RPA%20(2)-1.png)
/AI%20(5)-1.png?width=360&name=AI%20(5)-1.png)
