Microsoft Excel Basics for Data Management Course

Lesson 1 • Navigating Cells and Ranges

Covers keyboard and mouse techniques for moving within large datasets. Efficient navigation reduces errors and speeds up all data entry tasks.

Lesson 2 • Customising the Excel Environment

Adjusts display settings, zoom, and view options to suit individual workflows. A personalised environment improves accuracy and reduces fatigue.

Lesson 3 • Workbooks, Worksheets, and Files

Explains the relationship between workbooks and worksheets and covers file formats. Students can create, save, and reopen files in appropriate formats.

Lesson 1 • Copying, Cutting, and Pasting Data

Covers clipboard operations and Paste Special options for values, formats, and transposing. Proper paste techniques preserve data integrity.

Lesson 2 • Data Validation Rules

Restricts cell input to defined types, ranges, or lists to enforce data quality. Validation is the first line of defence against entry errors.

Lesson 3 • Editing, Clearing, and Undoing

Teaches in-cell editing, content clearing, and undo/redo workflows. These skills allow quick correction without disrupting surrounding data.

Lesson 4 • Filling and Flash Fill Techniques

Uses Fill Series and Flash Fill to populate patterns and extract text automatically. These tools dramatically reduce repetitive manual entry.

Lesson 1 • Font and Alignment Formatting

Controls font style, size, colour, and cell alignment options. Consistent typography signals professionalism and aids readability.

Lesson 2 • Number Formats and Custom Codes

Applies built-in and custom number formats to display values correctly without altering underlying data. Proper formatting prevents misreading of figures.

Lesson 3 • Formatting Tables with Table Styles

Converts ranges to structured Excel Tables and applies table styles for banded rows and filter arrows. Tables automate formatting as data grows.

Lesson 4 • Borders, Shading, and Cell Styles

Adds borders and background colours and applies named cell styles for consistency. Visual structure guides the reader's eye through complex data.

Lesson 1 • Formula Syntax and Cell References

Explains operator precedence, relative vs. absolute references, and mixed references. Correct reference types are the foundation of reusable formulas.

Lesson 2 • SUM, AVERAGE, COUNT, and Variants

Applies the most-used aggregate functions and their conditional variants. These functions answer the majority of everyday business calculation needs.

Lesson 3 • Logical Functions: IF and Nesting

Builds conditional logic using IF, AND, OR, and nested IF statements. Logical functions enable data categorisation and decision-based outputs.

Lesson 4 • Date and Time Functions

Calculates durations, extracts date parts, and generates dynamic date values. Date functions support scheduling, aging, and time-based reporting.

Lesson 1 • Named Ranges in Formulas

Defines named ranges to make formulas readable and easier to audit. Named ranges reduce errors when referencing large or frequently updated tables.

Lesson 2 • HLOOKUP and Lookup Alternatives

Applies HLOOKUP for horizontal tables and introduces INDEX and MATCH as flexible alternatives. These tools handle lookup scenarios VLOOKUP cannot.

Lesson 3 • VLOOKUP Fundamentals

Explains VLOOKUP syntax, exact vs. approximate match, and common errors. VLOOKUP is the most widely used lookup tool in business spreadsheets.

Lesson 1 • AutoFilter and Custom Filters

Applies AutoFilter to display only rows meeting specified criteria. Filtering isolates subsets without deleting data, enabling focused review.

Lesson 2 • Sorting Data Single and Multi-Level

Sorts data by one or multiple columns in ascending or descending order. Proper sorting is prerequisite to many analysis and reporting tasks.

Lesson 3 • Grouping, Subtotals, and Outlines

Groups rows or columns and inserts automatic subtotals for hierarchical data views. Outlines let users collapse detail and focus on summary levels.

Lesson 4 • Advanced Filter for Complex Criteria

Uses the Advanced Filter tool with a criteria range to apply multi-condition filters. Advanced Filter can extract unique records to a separate location.

Lesson 1 • Grouping and Filtering in PivotTables

Groups date fields by month or quarter and applies report filters and slicers. Grouping and filtering enable time-series and segment analysis.

Lesson 2 • Summarising Values and Calculations

Changes summary functions and adds calculated fields for custom metrics. Value settings transform raw counts into meaningful business measures.

Lesson 3 • PivotTable Design and Layout Options

Adjusts layout, style, and display options to produce presentation-ready summaries. Design choices affect how quickly stakeholders interpret results.

Lesson 4 • PivotCharts from PivotTables

Creates PivotCharts linked to PivotTables for dynamic visual summaries. PivotCharts update automatically when the underlying PivotTable changes.

Lesson 1 • Sparklines for In-Cell Trends

Inserts sparklines into cells to show trends alongside tabular data. Sparklines provide compact visual context without requiring a full chart.

Lesson 2 • Choosing the Right Chart Type

Maps common data relationships to appropriate chart types such as bar, line, and pie. Selecting the correct chart type prevents misleading visual representations.

Lesson 3 • Formatting Chart Elements

Formats axes, titles, legends, gridlines, and data labels for clarity. Well-formatted elements eliminate ambiguity and reinforce the data story.

Lesson 4 • Inserting and Editing Charts

Inserts charts from selected data and modifies source data ranges and chart placement. Editing skills allow charts to stay accurate as data changes.