Autoplay
Autocomplete
Previous Lesson
Complete and Continue
Simple Spreadsheets
Step 0. The Absolute Beginner's Guide to Spreadsheets
Welcome & Logistics (6:25)
Download the Materials
1. Versions (5:41)
2. Lingo (10:48)
3. Sheets (14:13)
4. Re-Sizing Columns/Rows (6:15)
5. Inserting/Deleting Cells (3:34)
6. Freezing Panes (4:31)
7. Sorting (10:50)
8. Filtering (3:03)
9. Formatting Cells (6:46)
10. Styles (2:38)
11. Borders & Gridlines (2:27)
12. Pictures (3:47)
13. Icons & Symbols (9:35)
14. Hyperlinks (3:21)
15. Auto-Fill (11:51)
16. Formulas & Functions (3:58)
17. Ultimate Editing Control (3:12)
18. Keyboard Shortcuts (4:49)
19. Paste Special (9:13)
20. Format Painter (2:34)
Step 1. Data Management: Datasets We Receive
File Naming (4:17)
CSV vs. Excel files (5:56)
Get Acquainted with the Dataset (11:58)
Make Sure It's Contiguous!!! (7:13)
Excel Tables (6:18)
Raw vs. Clean Data (5:49)
Transpose (20:52)
Step 1. Data Management: Datasets We Create
Data Validation (14:02)
Drop-Down Lists (10:25)
Best-Case Scenario vs. Real-Life Scenario (5:13)
Step 1. Data Management: Datasets We Merge
Purpose of Merging (8:28)
Types of Lookups (7:42)
Unique Lists (8:35)
Relative, Absolute, and Mixed Cell References (the $s) (8:55)
Vlookup (21:54)
Hlookup (9:00)
Xlookup (8:33)
Index-Match (9:44)
Lookups with Tables (10:20)
Lookups Across Sheets (13:48)
Step 2. Data Cleaning: How Much Data We Have (duplicates, counts) & Don't Have (missing data)
Duplicates (17:03)
Counts (3:22)
Blanks (11:33)
Step 2. Data Cleaning: Pull Variables Apart & Push Variables Together
Text to Columns (17:45)
Textsplit (7:48)
Left, Mid, Right (10:38)
Concatenation (11:38)
Step 2. Data Cleaning: Formatting
Numbers (18:10)
Currency (3:08)
Dates (19:48)
Text (4:25)
Step 2. Data Cleaning: Recoding
If (8:50)
Or (4:01)
And (3:22)
Nested If (18:30)
Or, Lookups (6:34)
Step 3. Data Tabulation: Counts vs. Sums
Lingo (Numbers vs. Categories) (9:24)
Counts vs. Sums (3:51)
Countif & Sumif (6:08)
Countifs & Sumifs (8:05)
Counts & Sums in Excel Tables (11:42)
Step 3. Data Tabulation: Measures of Central Tendency
Average, Median, Mode (5:17)
Average If & Average Ifs (7:31)
Median If & Median Ifs (7:49)
Mode If & Mode Ifs (5:05)
Averages, Medians, and Modes in Excel Tables (5:43)
Step 3. Data Tabulation: Measures of Dispersion
Min, Max, Range (4:30)
Standard Deviation & Variance (3:03)
Min if, Min ifs, Max if, & Max ifs (7:17)
Quartiles (3:53)
Percentiles (3:46)
Step 3. Data Tabulation: Summarize Categories with Pivot Tables
STEP 3: TABULATE -- Summarize Categories with Pivot Tables (67:23)
Case Studies from Start to Finish
Download the Materials
Case Study: Demographics (63:45)
Case Study: Deaths from Falls (52:50)
Case Study: Technical Assistance Log (80:37)
Bonus Lessons
A Real-Life Excel Test from a Job Interview (28:58)
Tips for Passing the Excel Certification Exam (42:22)
Case Studies with Your Colleagues
Kharaam S. (39:49)
Akimi S. (38:10)
Kristen S. (55:52)
2021 Graduation Party (52:39)
Gerda Z.'s Dashboard in Excel (57:52)
Kay B.'s Spreadsheet Organization (81:22)
Guest Speakers
Bill Jelen a.k.a. Mr. Excel (48:33)
Maryfrances Porter and Alison Nagel West from Partnerships for Strategic Impact (59:11)
Asya Spears from Rose Data Studio (49:19)
Mynda Treacy from My Online Training Hub (44:23)
Next Steps
Recommended Resources
Averages, Medians, and Modes in Excel Tables
Lesson content locked
If you're already enrolled,
you'll need to login
.
Enroll in Course to Unlock