Table of Contents

Course Objective. 4

Learning Objectives: 4

Student Data Files. 4

Chapter 1 - Drive Your Dashboard with Excel  Section 1. 8

What is a dashboard?. 8

Report versus Dashboard. 8

How Much?. 8

Overview.. 8

Design. 8

Design Layout 9

Dashboard Structure. 10

Dashboard Components. 11

Dashboard Mockup. 11

Space. 12

Review Questions  Section 1. 15

Chapter 1 - Drive Your Dashboard with Excel  Section 2. 16

Chart Overview.. 16

Bar charts. 16

Column Charts. 16

Line Charts. 16

Pie Charts. 17

Combination Charts. 17

Creating Charts. 17

Chart Basics. 19

Creating a Chart 19

Contextual Tabs. 19

Review Questions  Section 2. 21

Chapter 1 - Drive Your Dashboard with Excel  Section 3. 22

In-Cell Charts. 22

In-Cell Chart Using Symbols. 23

In-cell Stencil Font Size 7. 25

In-Cell Histogram Chart using BarGraph Font 27

Review Questions  Section 3. 29

Chapter 1 - Drive Your Dashboard with Excel  Section 4. 30

Linking Charts and Cells. 30

Linked Picture. 30

Camera Tool 33

Review Exercise: 35

Sparklines. 35

Review Exercise. 38

Review Questions -Section 4. 39

Chapter 1 - Drive Your Dashboard with Excel  Section 5. 40

Conditional Formatting. 40

Overview.. 40

Data Bars. 40

Icon Set 41

Icons in a Separate Column. 46

Formulas and Colors. 48

Review Exercise. 50

Review Questions -Section 5. 51

Chapter 1 - Drive Your Dashboard with Excel  Section 6. 52

Dynamic Chart Components Dynamic Legends. 52

Shapes. 57

Scroll Bars. 64

Scrolling Chart 68

Review Question  Section 6. 73

Chapter 1 - Drive Your Dashboard with Excel  Section 7. 74

Overlapping Charts. 74

Linking Charts. 77

Creating Mini Charts. 77

The Chart Makeover. 79

X Axis. 79

Combination Charts. 89

Review Questions-Section 7. 94

Chapter 1 - Drive Your Dashboard with Excel  Section 8. 95

Dynamic Charts. 95

Slicers. 97

Pivot Tables. 100

Review Questions -Section 8. 107

Chapter 1 - Drive Your Dashboard with Excel  Section 9. 108

Dynamic Chart: 4 in 1. 108

SUMIFs. 110

LOOKUP FUNCTIONS. 113

VLOOKUP. 113

INDEX MATCH.. 114

INDEX.. 120

Review Questions -Section 9. 127

Chapter 1 - Drive Your Dashboard with Excel  Section 10. 128

Table of Contents. 128

Protecting your Dashboard and Data. 130

To Save a File as Read-Only. 130

Protecting Worksheets and Workbooks. 131

Distribute your Dashboard using PDF Files. 133

SUMMARY.. 134

Review Questions -Section 10. 135

Review Question  Answers/Explanations. 136

Appendix. 150

Creating a Mockup. 150

Glossary. 153

Index. 154

Final Examination. 158