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. 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.
Table of Contents
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
