This course equips participants with practical Excel skills essential for efficient data handling in daily work. It covers key intermediate features such as tables, conditional formatting, and data validation.
Learning Outcomes:
Apply and manage structured tables for organised data handling
Use conditional formatting to highlight trends and anomalies
Validate data entry to minimise errors and ensure consistency
Sort and filter data effectively for better analysis
Key Topics:
Excel tables and structured referencing
Conditional formatting rules and tools
Data validation
Sorting and filtering
Module 1: Data Management with Tables
- Apply Table Formats
- Changing Table Style
- Table Style Options
- Adding Table Rows and Columns
- Adding Total Row in Table
- Adding Calculated Column
- Apply Table Filters (AutoFilter)
- Add Custom Filter
- Sorting
- Multi-level Sorting
- Using Flash Fill
- Insert Slicers
- Finding Data
- Replacing Data
- Remove Duplicates
Module 2:. Working with Data Validation and Subtotal
- Creating Subtotals in a List
- Removing Subtotals from a List
- Using Data Validation
- Validating Data using a List
- Creating a Custom Error Message
- Removing Data Validation
- Labs
Module 3. Creating Charts
- Use Recommended Charts
- Inserting a column chart
- Resizing a chart
- Deleting a chart
- Adding a Chart Title
- Changing the Chart Background
- Changing a column, bar, line or pie slice colors
- Changing the chart type
- Adding a Data Table
- Changing the Chart Layout
- Copying and moving charts
- Creating a 3-D Chart
- Adjusting 3-D View
- Labs
Module 4. 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 5. 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
- Labs
Module 6. Using Conditional Formatting
- 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 7. Using Worksheet Protection
- Unlocking Cells in a Worksheet
- Protecting a Worksheet
- Unprotecting a Worksheet
- Assigning a Password
- Opening a Password-protected File
- Removing a Password
- Labs
