Microsoft Excel 365 Data Analysis: Parts 1 through 4 Exam Only
with experts Kari Hoien, Sondra Nelson
Watch trailer
Course at a glance
| Release date | 4/25/2020 | |
| Level | Intermediate | |
| Runtime | 4h 2m | |
| Closed captioning | N/A | |
| Transcript | N/A | |
| eBooks / courseware | N/A | |
| Hands-on labs | N/A | |
| Sample code | N/A | |
| Exams | Included |
Course description
Exam only version of
Microsoft Excel 365 Data Analysis, Part 1 of 4: Functions and Formulas
Microsoft Excel 365 Data Analysis, Part 2 of 4: Lookups and DataTables
Microsoft Excel 265 Data Analysis, Part 3 of 4: PivotTables and PivotCharts
Microsoft Excel 265 Data Analysis, Part 4 of 4: Analytical Tools
Prerequisites
This course is designed for students with a base knowledge of Excel and want to learn higher-level skills to display data and perform functions. Students should know how to perform calculations, modify worksheets, format worksheets, print workbooks and manage workbooks.
Meet the experts
I’m Kari Hoien. I have a Bachelors degree in Business and Accounting from Concordia College in Moorhead, I was a CPA and went on to get my Masters in Education Technology from Pepperdine in Malibu, California. I have been working as a trainer and consultant for Microsoft Office products for more than 20 years and I’ve helped clients in almost every industry in a wide range of different capacities and applications. I have taught thousands of people how to use Microsoft Office apps (Excel, Access, Outlook, PowerPoint etc). I have done stand-up classroom style training, virtual training, on-demand training, self-paced training, executive training, one-on-one training – you name it – I've probably done it. I LOVE teaching people how to use these everyday programs better, ways to save time so you can work SMARTER and have more time for the things that you love. Thanks so much for allowing me to help you learn.
Sondra Nelson is a Microsoft Certified Trainer and has been teaching Microsoft Office for over 15 years. She has teaching and consulting experience with high school students, college students and corporate employees.
Course outline
Functions and Formulas
Logical and Conditional Functions (27:43)
- Introduction (00:33)
- Logical Functions (00:55)
- IF and AND Functions (03:31)
- Demo: IF and AND (04:43)
- Demo: Using IF and AND Together (03:34)
- Conditional Functions (01:00)
- SUMIF (02:30)
- Demo: SUMIF and SUMIFS (05:50)
- Logical Functions Continued (00:33)
- ISBLANK and EXACT (01:06)
- Demo: ISBLANK and EXACT (03:03)
- Summary (00:20)
Financial and Statistical Functions (19:19)
- Introduction (00:18)
- Financial Functions (02:05)
- Financial Functions Continued (01:47)
- PMT and IPMT (01:48)
- Demo: PMT (02:43)
- Demo: IPMT (03:42)
- Statistical Functions (00:48)
- AVERAGE, MEDIAN, and MODE.SNGL Functions (01:34)
- Demo: Mean, Median, and Mode (04:13)
- Summary (00:16)
Date/Time and Arrays (13:06)
- Introduction (00:24)
- Date and Time Functions (01:13)
- Date and Time Functions Continued (00:32)
- Time Functions (01:34)
- Demo: Date and Time Functions (03:01)
- Array Formulas (01:45)
- Array Formulas Continued (00:38)
- Array Demo (00:57)
- Demo: Array Formulas (02:37)
- Summary (00:20)
Lookups and Data Tables
Lookup Functions (08:49)
- Introduction (00:30)
- Using Lookup Functions (01:29)
- VLOOKUP and HLOOKUP Functions (01:52)
- Demo: Lookup Functions (04:40)
- Summary (00:16)
Data Tables (12:15)
- Introduction (00:18)
- Creating Data Tables (01:23)
- Demo: Creating a Table (02:29)
- Demo: Data Table Calculation (02:06)
- Data Management (00:43)
- Demo: Data Management (05:00)
- Summary (00:14)
Validating Cells (15:10)
- Introduction (00:24)
- Validating Cell Entries (01:58)
- Validation (02:25)
- Demo: Date Validation (03:32)
- Demo: Value Validation (01:32)
- Demo: Populate Data Area (01:41)
- Demo: Custom Validation (03:22)
- Summary (00:13)
Filtering (10:10)
- Introduction (00:21)
- Filter Demo (00:26)
- Filtering (01:44)
- Demo: Filter in the Table Header (05:23)
- Demo: Slicers (01:59)
- Summary (00:15)
Charting and Graphics (17:30)
- Introduction (00:22)
- Charting (00:50)
- Bubble Chart (00:39)
- Demo: Create a Chart (05:23)
- Combining Charts (01:22)
- Demo: Combining Charts (02:47)
- Graphics (00:34)
- Graphics Demos (01:22)
- Demo: Using Graphics in Excel (03:46)
- Summary (00:20)
PivotTables
Working with PivotTables (13:28)
- Introduction (00:23)
- Working with PivotTables (00:26)
- Create PivotTable (00:50)
- Demo: Create a PivotTable (02:20)
- Recommended PivotTable (00:37)
- Demo: RecommendedPivotTable (01:30)
- Working with PivotTables Continued (00:28)
- Data Model (02:19)
- Data Model Continued (01:05)
- Demo: Data Model (03:13)
- Summary (00:12)
Modifying PivotTable Data (09:47)
- Introduction (00:21)
- Modifying PivotTable Data (00:39)
- PivotTable Filter, Sort, and Aggregate (00:31)
- Demo: Filter, Sort, and Aggregate (02:47)
- PivotTable Calculated Column (02:10)
- Demo: Calculated Column (01:14)
- Timeline (00:32)
- Demo: Timeline (01:14)
- Summary (00:15)
Formatting PivotTables (16:57)
- Introduction (00:23)
- Formatting PivotTables (00:52)
- Custom Style PivotTables (01:12)
- Demo: Custom Style PivotTable (02:32)
- Format Layout (01:07)
- Demo: Report Layouts (05:57)
- Number Formats (00:58)
- Demo: Formatting Numbers (03:39)
- Summary (00:13)
Using PivotCharts (04:19)
- Introduction (00:22)
- Using PivotCharts (00:30)
- PivotChart Demo (00:55)
- Demo: Using PivotCharts (02:17)
- Summary (00:14)
PowerPivot (12:24)
- Introduction (00:12)
- PowerPivot (00:59)
- PowerPivot Demo (00:42)
- Demo: PowerPivot (02:06)
- PowerPivot Continued (00:52)
- Demo: Add a Column (01:44)
- PivotCharts in PowerPivot (00:43)
- Demo: PivotCharts (03:07)
- PivotTables in PowerPivot (00:36)
- Demo: PivotTables (01:02)
- Summary (00:15)
Exporting and Importing Data (08:29)
- Introduction (00:24)
- Exporting and Importing Data (00:38)
- Export Adoble PDF (00:32)
- Demo: Export PDF (00:51)
- Export PDF/XPS Document (00:37)
- Demo: Export PDF/XPS (01:01)
- Export Different File Types (00:32)
- Demo: Export Different File Types (01:28)
- Import (00:29)
- Demo: Import Data (01:35)
- Summary (00:16)
Analytical Tools
Goal Seek (07:28)
- Introduction (00:28)
- Goal Seek (00:58)
- Calculate Interest Rate (01:33)
- Demo: Calculate Interest Rate (02:05)
- Voting Results (00:45)
- Demo: Voting Results (01:19)
- Summary (00:16)
Scenarios (17:46)
- Introduction (00:21)
- Scenarios (00:44)
- Scenarios Demo (00:28)
- Demo: Scenarios (01:03)
- Worst-Case Scenario (00:44)
- Demo: Worst-Case Scenario (02:17)
- Worst-Case Scenario Continued (01:02)
- Demo: Worst-Case Scenario Continued (01:32)
- Best-Case Scenario (01:27)
- Demo: Best-Case Scenario (04:05)
- Scenario Summary (01:10)
- Demo: Scenario Summary (02:28)
- Summary (00:19)
Solver (12:20)
- Introduction (00:15)
- Solver (00:36)
- Using the Solver (02:18)
- Demo: Using the Solver (05:16)
- Solver Answer Report (01:14)
- Demo: Answer Report (02:21)
- Summary (00:17)
Instant Data Analysis (11:36)
- Introduction (00:17)
- Instant Data Analysis (00:40)
- Instant Data Analysis Formatting (00:35)
- Demo: Formatting and Charts (00:00)
- Instant Data Analysis Charts (03:31)
- Instant Data Analysis Totals (00:46)
- Instant Data Analysis Tables (00:32)
- Instant Data Analysis Sparklines (00:43)
- Demo: Totals, Tables, and Sparklines (04:15)
- Summary (00:14)
Analysis and Formatting (04:10)
- Introduction (00:08)
- Demo: Analysis and Formatting (03:54)
- Summary (00:08)