ExcelMan

Course overview and who is ExcelMan?

ExcelMan links and social media overview

Download course materials

1.1 Excel is open, what am I looking at?

1.2 What is Excel? What can it do? What will we cover?

1.3 Writing basic formulas (absolute vs relative)

1.4 Checking formulas (formula auditing tools)

1.5 Writing basic formulas (locking rows and columns)

1.6 Writing basic formulas (intro to named ranges)

1.7 Writing basic formulas (intro to Excel's functions)

1.8 Writing basic formulas (intro to Excel's array functions)

1.9 Writing basic formulas (intro to tables)

1.10 Intro to formatting numbers in cells

1.11 Useful shortcuts and productivity tips

1.12 Chapter recap

2.1 Chapter overview and aims

2.2 Data concepts (types, table design, workbook layout)

2.3 Excel's tools for inputting data (incl. data validation and conditional formatting)

2.4 Building a data request Excel form (full demo)

2.5 Limitations for inputting data in Excel

2.6 *BONUS* Solution 1: Intro to SharePoint lists

2.7 *BONUS* Solution 2: Intro to MS Forms

2.8 Chapter recap

3.1 Chapter aims

3.2 Intro to Power Query

3.3 Getting data without Power Query (inc. best practices)

3.4 Connecting to data sources with Power Query

3.5 Transforming rows with Power Query

3.6 Transforming columns with Power Query

3.7 Transforming shape with Power Query

3.8 Refreshing data in Power Query and editing queries

3.9 Combining data with Power Query (merge and append)

3.10 Chapter recap

4.1 Chapter aims

4.2 Text functions (inc. text splitting methods comparison)

4.3 Number and date functions (inc. advanced number formatting)

4.4 Data type conversion functions

4.5 Lookup and grouping functions (inc. multiple criteria and wildcard lookups)

4.6 Logic functions (inc. conditional stats functions, and conditionals in Power Query)

4.7 Error handling functions (inc. practical considerations and common methods to alert users)

4.8 Common multirow formulas (inc. sorting data and dynamic range references)

4.9 Chapter recap

5.1 Core statistics recap (inc. stats, distribution, central tendency, position, spread, and relationship)

5.2. Chapter aims and Pivot table intro

5.3 Using Pivot tables (inc. filtering and grouping data)

5.4 Analysing data using Pivot tables (inc. stats and display options)

5.5 Introduction to charts (inc. types, uses and examples)

5.6 Preparing chart source data for all key chart types

5.7 Plotting all key (pivot) chart types

5.8 Excel's 'recommended chart' and 'analyse data' tools

5.9 Pivot settings (inc. macro intro and refresh data macros)

5.10 Chapter recap