Excel Dashboard Course Syllabus


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

4.01 Charts & Tricks for Trending 0:13:53
4.02 Secondary Axis 0:02:48
4.03 Smoothing Data 0:04:11
4.04 Sparklines 0:15:18
4.05 Formatting Tricks 0:02:37
4.06 Highlighting Comparisons 0:04:50
4.07 Top & Bottom Ranking 0:08:38
4.08 Frequency Distribution 0:05:20
4.09 Target vs Variance Charts 0:04:07
4.10 Performance Against Target Range 0:04:05
4.11a Bullet Graphs Excel 2007/2010 0:12:40
4.11b Bullet Graphs Excel 2013 onward 0:14:09
4.12 Win/Loss/Draw Conditional Format 0:04:40
4.13 Custom Number Formats 0:17:47
4.14 Dynamic Labels 0:01:43
4.15 Text Formulas 0:09:06
4.16 Symbols in Formulas and Charts 0:04:33
4.17 Adding Series to Charts 0:03:55
4.18 Embedding Graphics in Charts 0:04:06
4.19 Small Multiples 0:05:12
4.20 In Cell Charts 0:07:47
4.21a Panel Charts Excel 2007/2010 0:06:51
4.21b Panel Charts Excel 2013 onward 0:05:34
4.22 Step Charts 0:08:10

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
0