Table of Contents

Chapter 1 - Excel Tips Tricks and Tools for CPAs - Section 1. 10

Intro. 10

Shortcut Keys. 10

Chapter 1 - Section 1 Review Questions. 13

Chapter 1 - Excel Tips Tricks and Tools for CPAs - Section 2. 14

Selecting Data. 14

Unhiding. 18

Other Quick Tips on Selecting Data. 19

Chapter 1 - Section 2 Review Questions. 20

Chapter 1 - Excel Tips Tricks and Tools for CPAs - Section 3. 21

Navigating. 21

Navigating to Your Most Used or Favorite Files Pin It!. 21

Navigating Around a Worksheet 21

Range Names. 21

Navigate Between Sheets. 22

See Multiple Worksheets in the Same Workbook at Once. 22

Navigate Between Excel Files Using Side by Side. 23

Navigate Between Excel Files. 23

Go To Special 24

Create a Table of Contents. 24

Chapter 1 - Section 3 Review Questions. 27

Chapter 1 - Excel Tips Tricks and Tools for CPAs - Section 4. 28

Copying / Moving/Grouping. 28

Grouping Sheets. 31

Sum A Cell in Multiple Sheets at One Time. 32

Copy a Sheet 32

Copying Filtered or Subtotaled Data. 33

Chapter 1 - Section 4 Review Questions. 35

Chapter 1 - Excel Tips Tricks and Tools for CPAs - Section 5. 36

Miscellaneous Productivity Tips Customizing Your Toolbar. 36

Creating a Multi-Tiered List 36

Watch Window.. 40

Removing Duplicates. 42

Sorting. 43

Sorting and Custom Lists. 43

Subtotal(). 46

Create Nested Subtotals. 47

Copying Subtotals and Grand Totals Only. 48

Chapter 1 - Section 5 Review Questions. 50

Click here to see Answers to Review Questions at end of EBook. 50

Chapter 1 - Excel Tips Tricks and Tools for CPAs - Section 6. 51

Custom Formats. 51

Fixed Decimal Places. 53

Formatting Subtotal Rows. 54

Conditional Formatting. 55

Conditional Formatting. 56

Chapter 1 - Section 6 Review Questions. 58

Chapter 1 - Excel Tips Tricks and Tools for CPAs - Section 7. 59

Must Know Functions Nested IF. 59

IFS. 60

AGGREGATE(). 61

SUBTOTAL(). 62

Conditional Functions SUMIF(). 64

MATCH() and INDEX(). 68

Handling Text 71

Text Import Wizard. 71

Text Commands. 73

LEFT(), RIGHT(), MID(). 73

CONCATENATE(). 73

TEXTJOIN.. 73

LEN(). 75

LEN() and LEFT(). 75

IFERROR() and IFNA(). 76

Miscellaneous Formulas You May Actually Need One Day. 77

Comparing Lists. 77

Using Count Functions with Other Functions. 78

TRIM(). 79

Convert a Date. 79

Determining the Median and Mode. 80

Determining Rank. 80

TRUNC() and INT(). 81

Chapter 1 - Section 7 Review Questions. 82

Chapter 1 - Excel Tips Tricks and Tools for CPAs - Section 8. 83

Pivot Tables. 83

Creating a Pivot Table. 83

Report Filter. 84

Drilling Down. 85

Pivot Table Calculations. 87

Create a Custom Calculation. 88

Create a Calculated Field: 89

Grouping by Date. 90

Slicers. 91

Miscellaneous Pivot Table Tips. 93

Chapter 1 - Section 8 Review Questions. 95

Chapter 1 - Excel Tips Tricks and Tools for CPAs - Section 9. 96

Charts. 96

Quick Tips. 96

Display Units. 96

Switch Rows/Columns Reorder Legend/ Empty Cells. 97

Numbers on the X axis. 98

Create a Dynamic Version of Your Original Chart (or data). 99

Create a Combination Chart 100

Add a Series into the Chart 101

Delete a Series in a Chart 101

Selecting Data for A Chart 101

Dynamic Chart Title. 101

Select All Charts at Once. 101

Chapter 1 - Section 9 Review Questions. 103

Chapter 1 - Excel Tips Tricks and Tools for CPAs - Section 10. 104

Miscellaneous. 104

Sort Left to Right 104

Printing. 104

Headers/Footers. 105

Answers/Solutions to the Review Questions. 106

Glossary. 115

Index. 116

Final Examination. 119

CPASelfstudy.com Course Evaluation. 123