Excel Dashboard Course Syllabus 10hrs 16min
Session 1: Introduction0:10:13
| 1 | Introduction | 0:10:13 |
Session 2: Getting Your Data Ready0:06:37
| 2 | Getting Your Data Ready | 0:06:37 |
Session 3: Design Principles0:05:57
| 3 | Design Principles | 0:05:57 |
Session 4: Display Principles2:52:00
Session 5: Interactive Controls2:47:32
| 5.01 | Form Controls Introduction | 0:01:55 | ||
| 5.02 | Check Boxes | 0:03:53 | ||
| 5.03 | List Boxes | 0:16:20 | ||
| 5.04 | Option Buttons | 0:09:08 | ||
| 5.05 | Group Boxes | 0:02:28 | ||
| 5.06 | Scroll Bar | 0:03:43 | ||
| 5.07 | Buttons | 0:02:28 | ||
| 5.08 | Combo Box with Macro | 0:06:04 | ||
| 5.09 | Dynamic Date Filters 4 Ways | 0:24:35 | ||
| 5.10 | Animated Charts | 0:17:02 | ||
| 5.11 | INDIRECT Function | 0:11:15 | <||
| 5.12 | Dynamic Named Range with OFFSET | 0:07:10 | ||
| 5.13 | Dynamic Named Range with INDEX | 0:09:46 | ||
| 5.14 | Dynamic Ranges for Charts | 0:08:13 | ||
| 5.15 | Rollover Technique | 0:13:14 | ||
| 5.16a | Scroll and Sort Table | 0:15:35 | ||
| 5.16b | Scroll and Sort Table with Dynamic Arrays | 0:05:01 | ||
| 5.17a | Dynamic Unique List | 0:05:48 | ||
| 5.17b | Dynamic Unique List with Dynamic Arrays | 0:03:54 |
Session 6: Analysing the Data0:40:57
| 6.01 | PivotTables and PivotCharts | 0:13:10 | ||
| 6.02 | Slicers | 0:08:36 | ||
| 6.03 | GETPIVOTDATA Function | 0:11:31 | ||
| 6.04 | Capture Slicer Selection in Formula | 0:03:34 | ||
| 6.05 | Slicer & PivotChart Dashboard Overview | 0:04:06 |
Session 7: Automating your Dashboard0:20:25
| 7.01 | Linking to Access | 0:08:07 | ||
| 7.02 | Incorporating New Data Automatically | 0:09:39 | ||
| 7.03 | Auto Refresh PivotTables | 0:02:39 |
Session 8: Putting It All Together0:53:59
| 8.01 | Quick Re-cap | 0:01:37 | ||
| 8.02 | New Dashboard Tour | 0:03:47 | ||
| 8.03 | Pyramid Chart | 0:08:06 | ||
| 8.04 | Array Formula Alternative to SUMIFS | 0:09:50 | ||
| 8.05 | LARGE and SMALL Array and DAVERAGE | 0:06:23 | ||
| 8.06 | Rank Values with PivotTable | 0:03:50 | ||
| 8.07 | Scatter Chart | 0:08:31 | ||
| 8.08 | DMAX, DMIN, DAVERAGE | 0:05:43 | ||
| 8.09 | Formatting Quickly | 0:06:12 |
Session 9: Distributing your Dashboard0:37:08
| 9.01 | Distributing your Dashboard | 0:03:39 | ||
| 9.02 | Excel Web App - Introduction | 0:05:35 | ||
| 9.03 | Preparing Your File for Excel Web App | 0:04:41 | ||
| 9.04 | Publish with Excel Web App | 0:09:21 | ||
| 9.05 | Password Protection | 0:13:52 |
Session 10: Tour de France Dashboard0:42:17
| 10.01 | Dashboard Overview | 0:13:07 | ||
| 10.02 | SUMIFS Formulas | 0:07:42 | ||
| 10.03 | INDEX, MATCH and OFFSET | 0:05:12 | ||
| 10.04 | INDEX, MATCH multiple match Array | 0:03:54 | ||
| 10.05 | Zoom Images VBA Code | 0:12:22 |
Session 11: 3D Maps/Power Maps (Excel 2013 Onward)0:32:51
| 11.01 | Introduction to 3D Maps | 0:04:30 | ||
| 11.02 | Creating 3D Maps | 0:18:59 | ||
| 11.03 | Custom Maps (Excel 2016 Only) | 0:09:22 |
Homework (download the file first) 0:26:54
| Mynda's Homework Dashboard Part 1 | 0:08:52 | |||
| Mynda's Homework Dashboard Part 2 | 0:18:02 |
Optional Related Excel Lessons 3hrs 36min
Charts in Excel 2013 Onward0:17:40
| 1 | Formatting | 0:08:37 | ||
| 2 | Labels | 0:07:03 | ||
| 3 | Combo Charts | 0:02:00 |
Excel Course Session 9: Conditional Formatting0:12:29
| 9.01 | Introduction | 0:01:02 | ||
| 9.02 | Highlight Cell Rules | 0:02:41 | ||
| 9.03 | Top/Bottom Rules | 0:02:34 | ||
| 9.04 | Data Bars | 0:01:19 | ||
| 9.05 | Color Scales | 0:01:39 | ||
| 9.06 | Custom Formatting Rule | 0:03:14 |
Excel Course Session 10: Tables0:14:24
| 10.01 | Introduction | 0:01:03 | ||
| 10.02 | Insert a Table and Style Options | 0:03:26 | ||
| 10.03 | Add Rows and Columns | 0:02:39 | ||
| 10.04 | Perform a Function in a Table | 0:04:18 | ||
| 10.05 | Summarise with PivotTable | 0:02:58 |
Excel Course Session 11: Data Tools0:10:31
| 11.02 | Data Validation | 0:06:27 | ||
| 11.03 | Drop Down Lists | 0:04:04 |
Excel Course Session 14: Lookup Functions0:35:49
| 14.01 | Introduction | 0:01:14 | ||
| 14.02 | VLOOKUP | 0:06:20 | ||
| 14.03 | VLOOKUP Exact Match | 0:09:17 | ||
| 14.04 | HLOOKUP | 0:03:52 | ||
| 14.05 | HLOOKUP Exact Match | 0:01:36 | ||
| 14.06 | CHOOSE | 0:06:12 | ||
| 14.07 | INDEX & MATCH | 0:07:05 |
Excel Course Session 15: Conditional Logic0:51:53
| 15.02 | IF Statement | 0:07:43 | ||
| 15.03 | Nested IF | 0:04:21 | ||
| 15.04 | IF AND | 0:03:01 | ||
| 15.05 | IF OR | 0:02:43 | ||
| 15.06 | IF NOT | 0:04:21 | ||
| 15.07 | IFERROR | 0:03:13 | ||
| 15.08 | SUMIF and SUMIFS | 0:04:15 | ||
| 15.09 | AVERAGEIF and AVERAGEIFS | 0:04:41 | ||
| 15.10 | COUNTIF & COUNTIFS | 0:09:46 | ||
| 15.11 | SUMPRODUCT | 0:10:18 |
Excel Course Session 19: Adding Graphics0:26:57
| 19.01 | Introduction | 0:01:07 | ||
| 19.02 | Insert Pictures | 0:02:37 | ||
| 19.03 | Modifying Pictures | 0:03:44 | ||
| 19.04 | Insert Shapes | 0:04:01 | ||
| 19.05 | Insert SmartArt | 0:05:11 | ||
| 19.06 | Apply Themes | 0:03:42 | ||
| 19.07 | Arrange | 0:06:36 |
Excel Course Session 21: PivotTables0:15:52
| 21.01 | Introduction | 0:01:14 | ||
| 21.02 | Creating PivotTables | 0:02:48 | ||
| 21.03 | Choosing Fields | 0:04:07 | ||
| 21.04 | PivotTable Layout | 0:03:29 | ||
| 21.05 | Filtering PivotTables | 0:02:04 | ||
| 21.06 | Modifying PivotTable Data | 0:02:10 |
Excel Course Session 22: Protecting Data0:11:28
| 22.01 | Introduction | 0:00:40 | ||
| 22.02 | Workbook Passwords | 0:03:40 | ||
| 22.03 | Protecting Workbooks | 0:03:08 | ||
| 22.04 | Unlocking Cells | 0:04:00 |
Excel Course Session 26: Macros0:18:28
| 26.01 | Introduction & Macro Security | 0:03:40 | ||
| 26.02 | Recording a Macro | 0:04:17 | ||
| 26.03 | Assign a Macro to a Button or Shape | 0:03:33 | ||
| 26.04 | Run a Macro Upon Opening a Workbook | 0:02:57 | ||
| 26.05 | How to Inspect and Modify a Macro | 0:03:45 |