MCA Microsoft Office Specialist (Office 365 and Office 2019) Study Guide Excel Associate Exam MO-200

MCA Microsoft Office Specialist (Office 365 and Office 2019) STUDY GUIDE

Your complete guide to preparing for the Microsoft Office Specialist Excel exam

The MCA Microsoft Office Specialist (Office 365 and Office 2019) Study Guide: Excel Associate Exam MO-200 is your one-stop resource for preparing you to earn the Excel Associate certification. The exam measures your competencies in the fundamentals of Excel. This guide starts you off with an assessment test to measure what you already know and what you still need to learn. Then you move into professional scenarios and examples, practical exercises, and challenging chapter review questions. Finally, you can reinforce and retain what you’ve learned with the Sybex online learning environment and test bank. Get prepared for the Excel Associate Exam with Sybex.

Coverage of 100% of all exam objectives in this Study Guide means you’ll be ready to:

• Create and manage worksheets and workbooks
• Apply formulas and functions
• Populate tables with data
• Create and edit a workbook with multiple sheets
• Add a graphic element to represent data visually

Interactive learning environment

Take your exam prep to the next level with Sybex’s superior interactive online study tools. To access our learning environment, simply visit www.wiley.com/go/sybextestprep, register your book to receive your unique PIN, and instantly gain one year of FREE access after activation to:

• Interactive test bank with a practice exam to help you identify areas where further review is needed. Get more than 90% of the answers correct, and you’re ready to take the certification exam.
• 100 electronic flashcards to reinforce learning and last-minute prep before the exam.
• Comprehensive glossary in PDF format gives you instant access to the key terms so you are fully prepared.

Eric Butow (Jackson, CA) has authored or co-authored 35 books, most recently Pro iOS Security and Forensics (Apress), Programming Interviews for Dummies (Wiley), and Instagram for Dummies (Wiley). Eric has also developed and taught networking, computing, and usability courses for Ed2Go, Virtual Training Company, California State University, Sacramento, and Udemy. Eric has also been a professional technical writer and started using Word, Excel, and PowerPoint professionally starting in 1994 for Dantel, a telecommunications company in Fresno, California. He also served as a technical writer for companies large and small including Intel, Wells Fargo Wachovia, TASQ Technology, Cisco Systems, and Hewlett-Packard. He is the owner of Butow Communications Group (BCG) in Jackson, California (www.butow.net). BCG offers website development, online marketing, and technical writing services.

Introduction xv

Assessment Test xix

Chapter 1 Managing Worksheets and Workbooks 1

Importing Data into Workbooks 2

Bringing in Data from TXT Files 3

Importing Data from CSV Files 4

Navigating Within Workbooks 8

Searching for Data Within a Workbook 8

Navigating to Named Cells, Ranges, or Workbook Elements 10

Inserting and Removing Hyperlinks 12

Formatting Worksheets and Workbooks 20

Modifying Page Settings 20

Adjusting Row Height and Column Width 22

Customizing Headers and Footers 30

Customizing Options and Views 36

Customizing the Quick Access Toolbar 37

Displaying and Modifying Workbook Content in Different Views 41

Freezing Worksheet Rows and Columns 44

Changing Window Views 46

Modifying Basic Workbook Properties 48

Displaying Formulas 49

Configuring Content for Collaboration 52

Setting a Print Area 52

Saving Workbooks in Other File Formats 54

Configuring Print Settings 54

Inspecting Workbooks for Issues 55

Summary 59

Key Terms 59

Exam Essentials 59

Review Questions 61

Chapter 2 Using Data Cells and Ranges 63

Manipulating Data in Worksheets 64

Pasting Data by Using Special Paste Options 64

Filling Cells by Using Auto Filling 68

Inserting and Deleting Multiple Columns or Rows 70

Adding and Removing Cells 72

Formatting Cells and Ranges 75

Merging and Unmerging Cells 75

Modifying Cell Alignment, Orientation, and Indentation 76

Formatting Cells by Using Format Painter 79

Wrapping Text Within Cells 80

Using Number Formatting 82

Applying Cell Formatting from the Format Cells Dialog Box 83

Working with Cell Styles 84

Clearing Cell Formatting 86

Defining and Referencing Named Ranges 88

Defining a Named Range 88

Naming a Table 93

Summarizing Data Visually 95

Inserting Sparklines 95

Applying Built-In Conditional Formatting 99

Removing Conditional Formatting 101

Summary 105

Key Terms 105

Exam Essentials 105

Review Questions 107

Chapter 3 Working with Tables and Table Data 109

Creating and Formatting Tables 110

Creating Excel Tables from Cell Ranges 110

Applying Table Styles 112

Converting Tables to Cell Ranges 113

Modifying Tables 115

Adding or Remove Table Rows and Columns 115

Configuring Table Style Options 118

Inserting and Configuring Total Rows 119

Filtering and Sorting Table Data 122

Filtering Records 122

Sorting Data by Multiple Columns 126

Summary 128

Key Terms 129

Exam Essentials 129

Review Questions 130

Chapter 4 Performing Operations by Using Formulas and Functions 133

Inserting References 134

Inserting Relative, Absolute, and Mixed References 135

Referencing Named Ranges and Named Tables in Formulas 137

Calculating and Transforming Datas 140

Performing Calculations Using the AVERAGE(), MAX(),  MIN(), and SUM() Functions 140

Perform Conditional Operations by Using the IF() Function 151

Formatting and Modifying Text 155

Formatting Text Using the RIGHT(), LEFT(), and MID() Functions 155

Formatting Text Using the UPPER(), LOWER(), and LEN() Functions 159

Formatting Text Using the CONCAT() and TEXTJOIN() Functions 163

Summary 166

Key Terms 167

Exam Essentials 167

Review Questions 168

Chapter 5 Managing Charts 171

Creating Charts 172

Building Charts 172

Working with Chart Sheets 174

Modifying Charts 178

Adding Data Series to Charts 178

Switching Between Rows and Columns in Source Data 181

Adding and Modifying Chart Elements 182

Formatting Charts 185

Using Chart Layouts 186

Applying Chart Styles 194

Adding Alternative Text to Charts for Accessibility 196

Summary 198

Key Terms 198

Exam Essentials 199

Review Questions 200

Appendix: Answers to Review Questions 203

Chapter 1: Managing Worksheets and Workbooks 204

Chapter 2: Using Data Cells and Ranges 204

Chapter 3: Working with Tables and Table Data 205

Chapter 4: Performing Operations by Using Formulas and Functions 206

Chapter 5: Managing Charts 206

Index 209

The New copy of this book will include any supplemental materials advertised. Please check the title of the book to determine if it should include any access cards, study guides, lab manuals, CDs, etc.

The Used, Rental and eBook copies of this book are not guaranteed to include any supplemental materials. Typically, only the book itself is included. This is true even if the title states it includes any access cards, study guides, lab manuals, CDs, etc.