Contents
Shortcut Keys. 7
Review Questions –
Shortcut Keys. 9
Selecting Data. 10
Unhiding. 14
Other
Quick Tips on Selecting Data. 15
Review Questions –
Selecting Data. 16
Navigating. 17
Navigating
To Your Most Used or Favorite Files. 17
Navigating
Around a Worksheet. 17
Range
Names. 17
Navigate
Between Sheets. 18
See
Multiple Worksheets in the Same Workbook at Once. 18
Navigate
Between Excel Files Using Side by Side. 19
Navigate
Between Excel Files. 19
Go To
Special 20
Create
a Table of Contents. 20
Copying / Moving/Grouping. 23
Grouping
Sheets. 26
Sum A
Cell in Multiple Sheets at One Time. 27
Copy
a Sheet. 27
Moving/Copying
Sheets to a Different Workbook. 28
Copying
Filtered or Subtotaled Data. 28
Review Questions-
Copying/Moving/Grouping. 29
Miscellaneous Productivity Tips Customizing Your Toolbar. 31
Creating
a Multi-Tiered List. 31
Watch
Window.. 34
Removing
Duplicates. 36
Sorting. 37
Sorting
and Custom Lists. 38
Subtotal(). 40
Create
Nested Subtotals. 41
Copying
Subtotals and Grand Totals Only. 41
Review Questions-
Miscellaneous Productivity. 43
Custom
Formats. 44
Fixed
Decimal Places. 46
Formatting
Subtotal Rows. 47
Conditional
Formatting. 47
Conditional
Formatting. 49
Review Questions-
Formatting. 50
Must Know Functions Nested IF. 51
AGGREGATE() 53
SUBTOTAL(). 54
Conditional Functions SUMIF(). 56
MATCH() and INDEX(). 60
Handling Text. 63
Text
Import Wizard. 63
Text
Commands. 65
LEFT(),
RIGHT(), MID(). 65
CONCATENATE(). 65
LEN(). 65
LEN()
and LEFT(). 65
IFERROR()
and IFNA(). 67
Miscellaneous Formulas You May Actually Need One Day. 68
Comparing
Lists. 68
Using
Count Functions with Other Functions. 69
TRIM(). 70
Convert
a Date. 70
Determining
the Median and Mode. 71
Determining
Rank. 71
TRUNC(). 72
Review Questions-
Functions. 72
Pivot Tables. 73
Creating
a Pivot Table. 73
Report
Filter. 75
Drilling
Down. 76
Pivot
Table Calculations. 77
Create
a Custom Calculation. 78
Create
a Calculated Field: 80
Grouping
by Date. 80
Slicers. 82
Miscellaneous Pivot Table
Tips. 84
Review Questions- Pivot
Tables. 85
Charts. 86
Quick
Tips. 86
Display
Units. 86
Switch
Rows/Columns – Reorder Legend/ Empty Cells. 87
Numbers
on the X axis. 88
Create
a Dynamic Version of Your Original Chart (or data). 90
Create
a Combination Chart. 90
Add a
Series into the Chart. 91
Delete
a Series in a Chart. 91
Selecting
Data For a Chart. 92
Dynamic
Chart Title. 92
Select
all Charts at Once. 92
Review Questions- Charts. 92
Miscellaneous. 93
Sort
Left to Right. 93
Printing. 93
Headers/Footers. 94
Glossary. 95
Answers/Solutions to the Review Questions. 96
INDEX. 103
APPENDIX. 105
Worksheet
Design Tips. 105
Automation. 107
Final Assessment Exam.. 110