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