Microsoft Excel - Intermediate

Course subjects

Cell Referencing and Names

• Understanding Relative References

• Understanding Absolute References

• Creating Mixed References

• Applying References in Formulas

• Understanding Named Ranges

• Creating and Managing Named Ranges

• Using Named Ranges for Clarity and Efficiency

• Referencing Data Across Worksheets and Workbooks

VLookup

• Understanding Lookup Concepts

• Setting Up a VLOOKUP Formula

• Exact Match vs Approximate Match

• Troubleshooting Common VLOOKUP Errors

• Improving Reliability with Absolute References

Data Validation

• Understanding Data Validation

• Restricting Data Entry by Type

• Creating Drop Down Lists

• Building Custom Validation Rules

• Creating Input Messages and Error Alerts

• Maintaining Data Integrity

Protecting Workbooks and Sheets

• Understanding Workbook and Sheet Protection

• Locking and Unlocking Cells

• Protecting a Worksheet

• Protecting a Workbook Structure

• Applying Password Protection

• Managing Permissions and Preventing Unauthorized Changes

Date and Time Functions

• Working with Dates and Times in Excel

• Converting Between Date and Time Formats

• Calculating Durations

• Using TODAY and NOW

• Creating and Using Timestamps

• Automating Time-Based Analysis

Nesting Functions

• Understanding Nested Formulas

• Planning a Nested Function

• Nesting IF Statements

• Combining Logical and Lookup Functions

• Building Complex Formulas Step by Step

• Testing and Troubleshooting Nested Functions

Conditional and Number Formatting

• Understanding Conditional Formatting

• Highlighting Values Using Rules

• Visualising Trends with Data Bars and Colour Scales

• Using Icon Sets for Quick Interpretation

• Applying Number Formats for Readability

• Formatting for Dates, Currency, Percentages, and Decimals

Creating Charts

• Selecting the Right Chart Type

• Creating Charts from Data

• Customising Chart Elements

• Adding Titles, Labels, and Legends

• Formatting Axes and Series

• Creating Professional-Quality Visuals

Creating Tables

• Understanding Excel Tables

• Creating a Table from Data

• Applying Table Styles

• Sorting and Filtering Table Data

• Using Table Features for Analysis

• Working with Table Formulas and Structured References

Creating Pivot Tables

• Understanding PivotTables

• Creating a PivotTable from Source Data

• Arranging Fields (Rows, Columns, Values, Filters)

• Filtering and Sorting PivotTable Results

• Formatting PivotTables for Clarity

• Refreshing and Updating PivotTables

• Using PivotTables to Identify Trends and Insights

Custom Number Formatting

• Understanding Custom Number Formats

• Creating Custom Formats for Numbers

• Formatting Currency, Percentages, and Large Values

• Custom Date and Time Formats

• Displaying Text with Numbers

• Improving Clarity in Financial and Operational Reporting