Content
INTRODUCTION ...................................................................................... 3
PART I GETTING STARTED WITH EXCEL ................................................... 9
CHAPTER 1: BASICS OF MICROSOFT EXCEL ..................................................... 10
1.1. WHAT IS MICROSOFT EXCEL? .................................................................... 10
1.2. WHY LEARN MICROSOFT EXCEL? ............................................................... 10
1.3. OPENING AND CLOSING EXCEL .................................................................. 11
1.4. SAVING AND OPENING DOCUMENTS .......................................................... 12
1.5. EXCEL INTERFACE .................................................................................... 14
1.6. EXCEL TERMINOLOGY ............................................................................... 17
CHAPTER 2: ENTERING AND EDITING DATA .................................................... 18
2.1. UNDERSTANDING CELLS ........................................................................... 18
2.2. ENTERING DATA INTO A CELL .................................................................... 18
2.3. EDITING DATA ........................................................................................ 19
2.4. COPYING AND PASTING DATA ................................................................... 19
2.5. USING THE CLIPBOARD ............................................................................. 20
2.6. PASTE SPECIAL ........................................................................................ 20
CHAPTER 3: BASIC OPERATIONS ON THE WORKSHEET ...................................... 22
3.1. CREATING AND MANAGING WORKSHEETS ................................................... 22
3.2. ROWS AND COLUMNS: ADDING, DELETING, MOVING ................................... 23
3.3. HIDING AND UNHIDING ROWS AND COLUMNS ............................................. 25
3.4. GROUPING AND UNGROUPING DATA ......................................................... 26
3.5. PRINTING WORKSHEETS AND PRINT SETUP .................................................. 27
PART II FORMATTING DATA AND WORKSHEETS ................................... 28
CHAPTER 4: FORMATTING CELLS .................................................................. 29
4.1. CHANGING FONT AND TEXT SIZE ................................................................ 29
4.2. ADJUSTING TEXT AND CELL FILL COLORS ..................................................... 29
4.3. ALIGNING TEXT ....................................................................................... 30
4.4. ADDING CELL BORDERS ............................................................................ 31
4.5. FORMATTING NUMBERS AND DATES .......................................................... 31
CHAPTER 5: WORKING WITH RANGES AND TABLES .......................................... 32
5.1. UNDERSTANDING RANGES ........................................................................ 32
5.2. CREATING AND FORMATTING TABLES ......................................................... 33
5.3. BENEFITS OF USING TABLES ...................................................................... 34
5.4. STRUCTURED REFERENCES IN TABLES .......................................................... 35
PART III WORKING WITH FORMULAS AND FUNCTIONS ........................ 36
CHAPTER 6: INTRODUCTION TO FORMULAS AND FUNCTIONS ............................. 37
6.1. INTRODUCTION TO FORMULAS AND FUNCTIONS ........................................... 37
6.2. BASIC FORMULAS (SUM, AVERAGE, MAX, MIN) ...................................... 38
CHAPTER 7: ADVANCED FORMULAS AND FUNCTIONS ....................................... 49
7.1. LOGICAL FUNCTIONS (AND, OR) ............................................................... 49
7.2. MATHEMATICAL FUNCTIONS (SUMIF, RANDBETWEEN) ............................ 52
7.3. STATISTICAL FUNCTIONS (COUNTIF, MODE, VAR) ..................................... 53
7.4. TEXT FUNCTIONS (TRANSPOSE, TRIM, TEXTJOIN) ................................... 57
7.5. FINANCIAL FUNCTIONS (PV, FV, NPV, SYD) ............................................... 59
CHAPTER 8: WORKING WITH DATES AND TIMES ............................................. 65
8.1. DATE AND TIME FORMATS ........................................................................ 65
8.2. DATE FUNCTIONS (TODAY, NOW, DATE, YEAR, MONTH, DAY) ................ 65
8.3. TIME FUNCTIONS (HOUR, MINUTE, SECOND) ......................................... 66
PART IV CREATING VISUALIZATIONS .................................................... 68
CHAPTER 9: CREATING CHARTS ................................................................... 69
9.1. INTRODUCTION TO EXCEL CHARTS .............................................................. 69
9.2. TYPES OF CHARTS .................................................................................... 69
9.3. CREATING AND CUSTOMIZING CHARTS ........................................................ 70
9.4. SWITCHING ROWS AND COLUMNS IN A CHART ............................................. 72
9.5. RESIZING CHARTS AND FORMATTING ELEMENTS ........................................... 73
CHAPTER 10: USING CONDITIONAL FORMATTING ........................................... 74
10.1. INTRODUCTION TO CONDITIONAL FORMATTING ......................................... 74
10.2. CREATING CONDITIONAL FORMATTING RULES ............................................ 74
10.3. EXAMPLES OF USING CONDITIONAL FORMATTING ...................................... 75
PART V MANAGING AND ANALYZING DATA ......................................... 80
CHAPTER 11: SORTING AND FILTERING DATA ................................................. 81
11.1. SORTING DATA ON A WORKSHEET ........................................................... 81
11.2. FILTERING DATA ................................................................................... 81
11.3. USING ADVANCED FILTERS ...................................................................... 83
11.4. SORTING AND FILTERING WITH CONDITIONAL FORMATTING ......................... 84
CHAPTER 12: DATA VALIDATION ................................................................. 85
12.1. INTRODUCTION TO DATA VALIDATION ...................................................... 85
12.3. DATA VALIDATION LISTS (DROP-DOWN MENUS) ....................................... 87
12.4. COPYING AND DELETING DATA VALIDATION RULES ..................................... 88
PART VI PROTECTING DATA .................................................................. 89
CHAPTER 13: DATA PROTECTION ................................................................. 90
13.1. PROTECTING A WORKSHEET AND WORKBOOK ........................................... 90
13.2. RESTRICTING ACCESS TO DATA ................................................................ 91
PART VII ADVANCED FEATURES AND TOOLS ......................................... 93
CHAPTER 14: USING MACROS AND VBA ....................................................... 94
14.1. INTRODUCTION TO MACROS AND VBA ..................................................... 94
14.2. RECORDING AND RUNNING MACROS ........................................................ 94
14.3. BASICS OF VBA PROGRAMMING .............................................................. 96
CHAPTER 15: INTRODUCTION TO POWER QUERY .......................................... 101
15.1. WHAT IS POWER QUERY? .................................................................... 101
15.2. IMPORTING DATA WITH POWER QUERY .................................................. 102
15.3. TRANSFORMING AND CLEANING DATA .................................................... 102
15.4. EXAMPLES OF USING POWER QUERY ...................................................... 104
CHAPTER 16: INTRODUCTION TO POWER PIVOT ............................................ 108
16.1. WHAT IS POWER PIVOT? ...................................................................... 108
16.2. IMPORTING DATA INTO POWER PIVOT .................................................... 108
16.3. CREATING RELATIONSHIPS AND WORKING WITH DATA MODELS ................. 110
16.4. EXAMPLES OF USING POWER PIVOT FOR DATA ANALYSIS ........................... 114
PART VIII WORKING WITH BIG DATA AND BUSINESS INTELLIGENCE ... 119
CHAPTER 17: DATA ANALYSIS TOOLS IN EXCEL ............................................. 120
17.1. USING THE SOLVER TOOL ..................................................................... 120
17.2. USING WHAT-IF ANALYSIS .................................................................... 123
17.3. CREATING FORECASTS AND TRENDS ....................................................... 124
17.4. EXAMPLES OF USING DATA ANALYSIS TOOLS ........................................... 125
CHAPTER 18: DATA VISUALIZATION WITH POWER BI ..................................... 126
18.1. INTRODUCTION TO POWER BI ............................................................... 126
18.2. IMPORTING DATA FROM EXCEL TO POWER BI .......................................... 127
18.3. CREATING REPORTS AND DASHBOARDS ................................................... 129
18.4. EXAMPLES OF USING POWER BI FOR DATA VISUALIZATION ........................ 130
CHAPTER 19: COLLABORATION IN EXCEL ..................................................... 133
19.1. CO-AUTHORING FILES .......................................................................... 133
19.2. USING COMMENTS AND NOTES ............................................................. 134
19.3. TRACKING CHANGES AND REVIEWING ..................................................... 134
CHAPTER 20: INTEGRATING EXCEL WITH OTHER APPLICATIONS ........................ 135
20.1. IMPORTING/EXPORTING DATA WITH EXCEL ............................................. 135
20.2. USING EXCEL WITH MICROSOFT TEAMS AND SHAREPOINT ......................... 136
20.3. AUTOMATING TASKS WITH MICROSOFT POWER AUTOMATE ...................... 136
CHAPTER 21: DATA PROTECTION AND SECURITY IN EXCEL ............................... 137
21.1. DATA PROTECTION WITH MICROSOFT 365 TOOLS .................................... 137
20.2. BEST PRACTICES FOR DATA SECURITY IN EXCEL ......................................... 138
EXCEL GLOSSARY ............................................................................... 141
3-D ..................................................................................................... 142
A ........................................................................................................ 142
B ........................................................................................................ 143
C ........................................................................................................ 143
D ........................................................................................................ 145
E ........................................................................................................ 147
F ........................................................................................................ 148
G ........................................................................................................ 149
H ........................................................................................................ 149
I ......................................................................................................... 150
J ......................................................................................................... 150
L ........................................................................................................ 151
M ....................................................................................................... 151
N ....................................................................................................... 152
O ....................................................................................................... 152
P ........................................................................................................ 153
Q ....................................................................................................... 155
R ........................................................................................................ 156
S ........................................................................................................ 157
T ........................................................................................................ 158
U ........................................................................................................ 159
V ........................................................................................................ 159
W ....................................................................................................... 160
X ........................................................................................................ 161
KEYBOARD SHORTCUTS IN EXCEL FOR WINDOWS .............................. 162
FREQUENTLY USED SHORTCUTS .................................................................. 163
USE ACCESS KEYS FOR RIBBON TABS .......................................................... 164
WORK IN THE RIBBON WITH THE KEYBOARD ................................................. 165
KEYBOARD SHORTCUTS FOR NAVIGATING IN CELLS ......................................... 166
KEYBOARD SHORTCUTS FOR FORMATTING CELLS ............................................ 168
KEYBOARD SHORTCUTS IN THE PASTE SPECIAL DIALOG BOX IN EXCEL ................ 171
KEYBOARD SHORTCUTS FOR MAKING SELECTIONS AND PERFORMING ACTIONS ...... 171
DATA & FORMULA BAR SHORTCUTS ........................................................... 173
KEYBOARD SHORTCUTS FOR REFRESHING EXTERNAL DATA ................................ 175
POWER PIVOT KEYBOARD SHORTCUTS ......................................................... 176
FUNCTION KEYS ...................................................................................... 178
OTHER USEFUL SHORTCUT KEYS ................................................................. 180
KEYBOARD SHORTCUTS IN EXCEL FOR MACOS ................................... 185
FREQUENTLY USED SHORTCUTS .................................................................. 185
WORK IN WINDOWS AND DIALOG BOXES ..................................................... 189
MOVE AND SCROLL IN A SHEET OR WORKBOOK ............................................. 190
ENTER DATA ON A SHEET .......................................................................... 192
WORK IN CELLS OR THE FORMULA BAR ........................................................ 194
FORMAT AND EDIT DATA .......................................................................... 196
SELECT CELLS, COLUMNS, OR ROWS ............................................................ 199
WORK WITH A SELECTION ......................................................................... 201
USE CHARTS .......................................................................................... 203
SORT, FILTER, AND USE PIVOTTABLE REPORTS ............................................... 203
OUTLINE DATA ....................................................................................... 203
USE FUNCTION KEY SHORTCUTS ................................................................. 204
DRAWING ............................................................................................. 206
BONUSES .............................................................................................. 207