Understanding Excel Magic -- [v2019/v365+] Beginners' Course

This course is designed for those that are completely new to Excel or would like to brush up on their skills. We'll be using v2019 and v365 and will cover some of the exciting new and upcoming Excel features and functions, including Dynamic Arrays and XLOOKUP (some of which requires v365).

The course has been designed with efficiency as a prime driver so we'll be talking a fair bit about keyboard shortcuts, what to do and what not to do as well as some theory on data storage, data types, etc so that you get an understanding of why certain things are the way they are rather than "just do". There are about 65 lectures and 3 of those are technical, the other 62 are not techy at all so no need to worry. The 3 techy lectures will help you understand the bigger picture.

In terms of style/delivery: UK English, bit of added sarcasm (very minimal), generally cool and avoiding buzz. Also I tend to use "you" ("if you have a look at this/do that") rather us ("let us do this/that") in most cases. It's just to say some people prefer one or the other so you know what to expect. Furthermore I don't use "I'm going to go ahead and...". Just saying. :)

Course structure:

1. Using the Application

1.1: Spreadsheet Basics

1. Opening & Closing Excel & Spreadsheets

2. Introducing Excel’s Interface

3. Creating New Spreadsheets

4. Saving Locally / Online

5. Saving As (Different File Format) & Discussing File Formats

6. Dissecting an XLSX File (Techy but Useful)

1.2: Productivity Basics

1. Overview of Basic Options

2. Protected View

3. Using Help

4. Magnification/Zoom Tools

5. Working with the Ribbon

6. Navigation Best Practices: Using Shortcuts & Go to Tool

2. Working with Cells, Formulae, Functions

2.1: Inserting & Selecting Data

1. Best Practices: “One Cell, One Data Type Only”

2. Working with Text/Numbers/Date – Data Entry Methods

3. Best Practices: Understanding Regions, Avoiding Blanks

4. Selecting Cells, Adjacent and Non-Adjacent Ranges, Worksheet

2.2: Understanding Formulae

1. Best Practices: Cell References vs. Hard-Coding

2. Relative & Absolute Cell Referencing

3. Basic Arithmetic Operations (+-*/^)

4. Dissecting Formulae (Techy but Useful)

5. Dynamic Arrays [365 or Newer, not in 2019]

6. Understanding Basic Errors

2.3: Basic Functions

1. Working with the SUM, AVERAGE, MIN, MAX Functions

2. Working with the COUNT, COUNTA, COUNTBLANK Functions

3. Introduction to the IF Function ++ Understanding Logical Operators

4. “IF”-enabled Functions (SUMIF/S, AVERAGEIF/S, MINIFS, MAXIFS, COUNTIF/S)

2.4: Finding & Changing Data

1. Search -- Basics

2. Replace -- Basics

3. Sorting Cells (1 Criterion, Asc/Desc Order, Text, Numbers, Dates)

4. Undo & Redo ++ Caveats

2.5: Filling, Copying, Moving (Cutting), Deleting

1. (Auto) Incrementing Value – AutoFill Tool

2. Automate Data Fill in Other Columns – Flash Fill

3. Copying Contents Within and Between Worksheet(s)

4. Moving (Cutting) Within and Between Worksheet(s)

5. Clearing Cell Contents

6. Deleting, Inserting Cells

3. Worksheets Basics

3.1: Rows & Columns

1. Selecting Rows/Cols, Adjacent & Non-Adjacent Ranges

2. Delete, Insert Rows and Columns.

3. Modifying Width, Height, Manual & Auto

4. Freezing 1st Row, 1st Column, Unfreezing

3.2: Worksheets In-Depth

1. Toggling, Inserting, Deleting Worksheets

2. Best Practices: (Re)naming Worksheets ++ Caveats

3. Duplicating, Moving Sheets Within & Across Workbooks

4. Cell Formatting

4.1: Formatting Dates & Numbers

1. Understanding the Basic “Locale” Concept (Techy-ish)

2. Specifying # of Decimals, Using the 000 Separator, %s

3. ROUND, ROUNDUP, ROUNDDOWN Functions

4. Further Numbers and Currency Format Styles

5. Basic and Advanced Date Format Styles

4.2: Formatting Contents

1. Changing Font Type & Size

2. Changing Appearances: Bold, Italic, Underline, Double Underline.

3. Colouring Text & Cell Fill (Background)

4. Introducing Paste Special (Formats only)

5. Cell Borders

4.3: Alignment, Merging

1. Changing Horizontal/Vertical Alignment

2. Text Wrapping – How and Why?

3. Merging & Unmerging ++ Caveats

5. Charts (Basics)

5.1: Creating Charts

1. Understanding (& Creating) Certain Chart Types: Column, Bar, Line, Pie, Treemap Chart

2. Selecting, Moving, Resizing, Deleting Charts

3. Changing the Chart Type.

4. Brief Introduction to Other Chart Types

5.2: Working with Chart Elements

1. Introducing & Explaining Chart Elements

2. Adding/Removing Chart Elements (Title, Legend, Axis…)

3. Changing the Formatting of Chart Elements

6. Finding Data

6.1: Lookup Functions

1. Theory of Keys

2. Lookup Functions: HLOOKUP/VLOOKUP

3. Lookup Functions: INDEX & MATCH

4. Lookup Functions: XLOOKUP/XMATCH

+1: Excel Capabilities Demo

Demoing Advanced Excel functionality