CramX Logo
RegisterLog in
MCA Microsoft Office Specialist Study Guide: Excel Associate Exam MO-200 (2021) - Document preview page 1

MCA Microsoft Office Specialist Study Guide: Excel Associate Exam MO-200 (2021) - Page 1

Document preview content for MCA Microsoft Office Specialist Study Guide: Excel Associate Exam MO-200 (2021)

MCA Microsoft Office Specialist Study Guide: Excel Associate Exam MO-200 (2021)

Ace your certification with MCA Microsoft Office Specialist Study Guide: Excel Associate Exam MO-200 (2021), packed with essential study resources.

Ava Martinez
Contributor
4.3
91
over 1 year ago
Preview (31 of 319 Pages)
100%
Log in to unlock
Page 1 of 31
MCA Microsoft Office Specialist Study Guide: Excel Associate Exam MO-200 (2021) - Page 1 preview image
Page 2 of 31
MCA Microsoft Office Specialist Study Guide: Excel Associate Exam MO-200 (2021) - Page 2 preview image
Page 3 of 31
MCA Microsoft Office Specialist Study Guide: Excel Associate Exam MO-200 (2021) - Page 3 preview imageTable of ContentsCoverTitle PageCopyrightDedicationAcknowledgmentsAbout the AuthorAbout the Technical EditorTable of ExercisesIntroductionInteractive Online Learning Environment and Test BankObjective MapAssessment TestAnswers to Assessment TestChapter 1: Managing Worksheets and WorkbooksImporting Data into WorkbooksNavigating Within WorkbooksFormatting Worksheets and WorkbooksCustomizing Options and ViewsConfiguring Content for CollaborationSummaryKey TermsExam EssentialsReview QuestionsChapter 2: Using Data Cells and RangesManipulating Data in WorksheetsFormatting Cells and RangesDefining and Referencing Named Ranges
Page 4 of 31
MCA Microsoft Office Specialist Study Guide: Excel Associate Exam MO-200 (2021) - Page 4 preview imageSummarizing Data VisuallySummaryKey TermsExam EssentialsReview QuestionsChapter 3: Working with Tables and Table DataCreating and Formatting TablesModifying TablesFiltering and Sorting Table DataSummaryKey TermsExam EssentialsReview QuestionsChapter 4: Performing Operations by Using Formulas andFunctionsInserting ReferencesCalculating and Transforming DatasFormatting and Modifying TextSummaryKey TermsExam EssentialsReview QuestionsChapter 5: Managing ChartsCreating ChartsModifying ChartsFormatting ChartsSummaryKey TermsExam Essentials
Page 5 of 31
MCA Microsoft Office Specialist Study Guide: Excel Associate Exam MO-200 (2021) - Page 5 preview imageReview QuestionsAppendix: Answers to Review QuestionsChapter 1: Managing Worksheets and WorkbooksChapter 2: Using Data Cells and RangesChapter 3: Working with Tables and Table DataChapter 4: Performing Operations by Using Formulas andFunctionsChapter 5: Managing ChartsIndexOnline Test BankRegister and Access the Online Test BankEnd User License AgreementList of IllustrationsChapter 1FIGURE 1.1 Text Import Wizard dialog boxFIGURE 1.2 Get & Transform sectionFIGURE 1.3 Import Data dialog boxFIGURE 1.4 Preview dialog boxFIGURE 1.5 Find And Replace dialog boxFIGURE 1.6 The Define Name menu optionFIGURE 1.7 Go To dialog boxFIGURE 1.8 The Link iconFIGURE 1.9 The Insert Hyperlink dialog boxFIGURE 1.10 The Place In This Document menu optionFIGURE 1.11 A list of recently opened filesFIGURE 1.12 Browsed Pages list
Page 6 of 31
MCA Microsoft Office Specialist Study Guide: Excel Associate Exam MO-200 (2021) - Page 6 preview imageFIGURE 1.13 E mail Address menu optionFIGURE 1.14 Edit Hyperlink dialog boxFIGURE 1.15 The Remove Hyperlink option in the contextmenuFIGURE 1.16 Page Setup section optionsFIGURE 1.17 Column Width optionFIGURE 1.18 AutoFit Column Width optionFIGURE 1.19 Keep Source Column Widths iconFIGURE 1.20 Default Width optionFIGURE 1.21 The resize mouse pointer between two columnheadingsFIGURE 1.22 Row Height optionFIGURE 1.23 AutoFit Row Height optionFIGURE 1.24 The resize mouse pointer between two rowheadingsFIGURE 1.25 Header & Footer iconFIGURE 1.26 Header element drop down listFIGURE 1.27 Header & Footer Elements sectionFIGURE 1.28 Page Setup dialog boxFIGURE 1.29 The new icon in the Quick Access ToolbarFIGURE 1.30 Customize Quick Access Toolbar drop downmenuFIGURE 1.31 The Quick Access Toolbar below the ribbonFIGURE 1.32 Reset button in Excel Options dialog boxFIGURE 1.33 Zoom dialog boxFIGURE 1.34 Custom Views iconFIGURE 1.35 Add View dialog box
Page 7 of 31
MCA Microsoft Office Specialist Study Guide: Excel Associate Exam MO-200 (2021) - Page 7 preview imageFIGURE 1.36 The selected view in the list is at the top.FIGURE 1.37 Freeze Panes drop down menuFIGURE 1.38 Page Break Preview viewFIGURE 1.39 Page Layout viewFIGURE 1.40 Info screenFIGURE 1.41 Protect Sheet optionFIGURE 1.42 Protect Sheet dialog boxFIGURE 1.43 Set Print Area optionFIGURE 1.44 A partial list of file formatsFIGURE 1.45 Print screenFIGURE 1.46 Document Inspector dialog boxChapter 2FIGURE 2.1 The Paste drop down menuFIGURE 2.2 Paste Special dialog boxFIGURE 2.3 Pop up box shows what value you will fill.FIGURE 2.4 Inserted columnsFIGURE 2.5 Inserted rowsFIGURE 2.6 Insert Sheet Columns menu optionFIGURE 2.7 Insert Sheet Rows menu optionFIGURE 2.8 Merge options in the drop down menuFIGURE 2.9 Align Left, Align Center, and Align Right iconsFIGURE 2.10 Orientation drop down menuFIGURE 2.11 Format Cells dialog boxFIGURE 2.12 Format Painter iconFIGURE 2.13 Wrap Text optionFIGURE 2.14 Number format options
Page 8 of 31
MCA Microsoft Office Specialist Study Guide: Excel Associate Exam MO-200 (2021) - Page 8 preview imageFIGURE 2.15 Format Cells optionFIGURE 2.16 Format Cells dialog boxFIGURE 2.17 Cell Styles drop down listFIGURE 2.18 Clear drop down listFIGURE 2.19 Define Name optionFIGURE 2.20 New Name dialog boxFIGURE 2.21 Create From Selection optionFIGURE 2.22 Create Names From Selection dialog boxFIGURE 2.23 Use In Formula drop down menuFIGURE 2.24 Highlighted table name in the Table Name BoxFIGURE 2.25 Highlighted table in the Name BoxFIGURE 2.26 The three Sparkline iconsFIGURE 2.27 The selected cell rangeFIGURE 2.28 Sparkline menu ribbonFIGURE 2.29 Quick Analysis iconFIGURE 2.30 Quick Analysis drop down menuFIGURE 2.31 Clear Rules From Entire Sheet optionFIGURE 2.32 Clear Rules From Selected Cells optionFIGURE 2.33 Go To Special dialog boxChapter 3FIGURE 3.1 Table styles in the Format As Table drop downmenuFIGURE 3.2 Format As Table dialog boxFIGURE 3.3 Highlighted default name in the New Table Styledialog boxFIGURE 3.4 Format Cells dialog boxFIGURE 3.5 Convert To Range menu option
Page 9 of 31
MCA Microsoft Office Specialist Study Guide: Excel Associate Exam MO-200 (2021) - Page 9 preview imageFIGURE 3.6 Resize Table optionFIGURE 3.7 The reordered table rowsFIGURE 3.8 The Delete Table Rows and Delete TableColumns optionsFIGURE 3.9 Table Style Options section check boxesFIGURE 3.10 Total Row check boxFIGURE 3.11 Sum option in drop down menuFIGURE 3.12 Filter drop down menuFIGURE 3.13 The filtered table shows one row that containsthe cell.FIGURE 3.14 Sort & Filter drop down menuFIGURE 3.15 Sort dialog boxChapter 4FIGURE 4.1 Relative cell referenceFIGURE 4.2 Absolute cell referenceFIGURE 4.3 Mixed cell referenceFIGURE 4.4 Absolute reference in the Formula BarFIGURE 4.5 The formula in the Formula BarFIGURE 4.6 The average of all five numbersFIGURE 4.7 Average of five numbers in the cellFIGURE 4.8 The calculatedMAXresult in the cell and theformula in the Form...FIGURE 4.9 The calculatedMINresult in the cell and theformula in the Form...FIGURE 4.10 The calculatedSUMresult in the cell and theformula in the For...FIGURE 4.11 The count result in the cell and the formula inthe Formula Bar...
Page 10 of 31
MCA Microsoft Office Specialist Study Guide: Excel Associate Exam MO-200 (2021) - Page 10 preview imageFIGURE 4.12 TheCOUNTAresults in the cell and the formula inthe Formula Ba...FIGURE 4.13 The calculatedCOUNTBLANKresult in the cell andformula in the ...FIGURE 4.14 TheFALSEresult in the cell with the formula inthe Formula Bar...FIGURE 4.15 The results of the formula in column RFIGURE 4.16 The last two letters in cell A4FIGURE 4.17 The first two letters in cell A6FIGURE 4.18 The three characters in cell A11FIGURE 4.19 All uppercase text in column CFIGURE 4.20 All lowercase text in column CFIGURE 4.21 Length in characters in column CFIGURE 4.22 Combined text withCONCAT()function in theFormula BarFIGURE 4.23 The combined text with spaces between eachtext stringChapter 5FIGURE 5.1 The Recommended Charts icon in the InsertribbonFIGURE 5.2 The Insert Chart dialog boxFIGURE 5.3 The Column category in the All Charts tabFIGURE 5.4 The chart in the worksheetFIGURE 5.5 Move Chart icon in the Chart Design ribbonFIGURE 5.6 Move Chart dialog boxFIGURE 5.7 The chart in a new tabFIGURE 5.8 The updated chart and expanded selection areain the table
Page 11 of 31
MCA Microsoft Office Specialist Study Guide: Excel Associate Exam MO-200 (2021) - Page 11 preview imageFIGURE 5.9 Select Data Source dialog box and selected tablecellsFIGURE 5.10 Updated chart in chart sheetFIGURE 5.11 Row titles in the horizontal axisFIGURE 5.12 Chart elements listFIGURE 5.13 Trend line for teddy bearsFIGURE 5.14 Excel previews the layout in the chart.FIGURE 5.15 Format ribbon sectionsFIGURE 5.16 Current Selection sectionFIGURE 5.17 Insert Shapes sectionFIGURE 5.18 Shape Styles sectionFIGURE 5.19 WordArt Styles sectionFIGURE 5.20 Arrange sectionFIGURE 5.21 Size sectionFIGURE 5.22 The preview of the style in the chartFIGURE 5.23 Six swatch colors in the selected swatch groupFIGURE 5.24 Alt Text pane
Page 12 of 31
MCA Microsoft Office Specialist Study Guide: Excel Associate Exam MO-200 (2021) - Page 12 preview imageMCAMicrosoft®Office Specialist(Office 365 and Office 2019)Study GuideExcel Associate Exam MO‐200Eric Butow
Page 13 of 31
MCA Microsoft Office Specialist Study Guide: Excel Associate Exam MO-200 (2021) - Page 13 preview image
Page 14 of 31
MCA Microsoft Office Specialist Study Guide: Excel Associate Exam MO-200 (2021) - Page 14 preview imageCopyright © 2021 by John Wiley & Sons, Inc., Indianapolis, IndianaPublished simultaneously in CanadaISBN: 978 1 119 71824 6ISBN: 978 1 119 71827 7 (ebk)ISBN: 978 1 119 71825 3 (ebk)No part of this publication may be reproduced, stored in a retrieval system or transmitted inany form or by any means, electronic, mechanical, photocopying, recording, scanning orotherwise, except as permitted under Sections 107 or 108 of the 1976 United States CopyrightAct, without either the prior written permission of the Publisher, or authorization throughpayment of the appropriate per copy fee to the Copyright Clearance Center, 222 RosewoodDrive, Danvers, MA 01923, (978) 750 8400, fax (978) 646 8600. Requests to the Publisher forpermission should be addressed to the Permissions Department, John Wiley & Sons, Inc., 111River Street, Hoboken, NJ 07030, (201) 748 6011, fax (201) 748 6008, or online atwww.wiley.com/go/permissions.Limit of Liability/Disclaimer of Warranty: The publisher and the author make norepresentations or warranties with respect to the accuracy or completeness of the contents ofthis work and specifically disclaim all warranties, including without limitation warranties offitness for a particular purpose. No warranty may be created or extended by sales orpromotional materials. The advice and strategies contained herein may not be suitable forevery situation. This work is sold with the understanding that the publisher is not engaged inrendering legal, accounting, or other professional services. If professional assistance isrequired, the services of a competent professional person should be sought. Neither thepublisher nor the author shall be liable for damages arising herefrom. The fact that anorganization or Web site is referred to in this work as a citation and/or a potential source offurther information does not mean that the author or the publisher endorses the informationthe organization or Web site may provide or recommendations it may make. Further, readersshould be aware that Internet Web sites listed in this work may have changed or disappearedbetween when this work was written and when it is read.For general information on our other products and services or to obtain technical support,please contact our Customer Care Department within the U.S. at (877) 762 2974, outside theU.S. at (317) 572 3993 or fax (317) 572 4002.Wiley publishes in a variety of print and electronic formats and by print on demand. Somematerial included with standard print versions of this book may not be included in e books orin print on demand. If this book refers to media such as a CD or DVD that is not included inthe version you purchased, you may download this material at booksupport.wiley.com. Formore information about Wiley products, visit www.wiley.com.Library of Congress Control Number: 2020952862TRADEMARKS: Wiley, the Wiley logo, and the Sybex logo are trademarks or registeredtrademarks of John Wiley & Sons, Inc. and/or its affiliates, in the United States and othercountries, and may not be used without written permission. Microsoft and Office 365 areregistered trademarks of Microsoft Corporation. All other trademarks are the property of theirrespective owners. John Wiley & Sons, Inc. is not associated with any product or vendormentioned in this book.
Page 15 of 31
MCA Microsoft Office Specialist Study Guide: Excel Associate Exam MO-200 (2021) - Page 15 preview imageTo my family and friends
Page 16 of 31
MCA Microsoft Office Specialist Study Guide: Excel Associate Exam MO-200 (2021) - Page 16 preview imageAcknowledgmentsI have many people to thank, starting with my literary agent, MattWagner. He connected me with Sybex to write this book and managedour relationship well. Next, I want to give a shout out to my excellentediting team: Gary Schwartz, Barath Kumar Rajasekaran, ChristineO'Connor, and Senior Acquisitions Editor Kenyon Brown.And, as always, I want to thank my family and friends for theireverlasting support. I couldn't write this book without them.—Eric Butow
Page 17 of 31
MCA Microsoft Office Specialist Study Guide: Excel Associate Exam MO-200 (2021) - Page 17 preview imageAbout the AuthorEric Butowis the owner of Butow Communications Group (BCG) inJackson, California. BCG offers website development, onlinemarketing, and technical writing services. Eric is a native Californianwho started working with his friend's Apple II Plus and Radio ShackTRS 80 Model III in 1980 when he lived in Fresno, California. Helearned about programming, graphic design, and desktop publishingin the Fresno PC Users Group in his professional career and when hestarted BCG in 1994.Eric has written 37 other technical books as an author, as a co authoror, in one case, as a ghostwriter. Most of Eric's works were written forthe general book market, but some were written for specific clients,
Page 18 of 31
MCA Microsoft Office Specialist Study Guide: Excel Associate Exam MO-200 (2021) - Page 18 preview imageincluding HP and F5 Networks. Two of his books have been translatedinto Chinese and Italian. Eric's most recent books areProgrammingInterviews for Dummies(For Dummies, 2019) with John Sonmez,Instagram for Dummies(For Dummies, 2019) with Jennifer Hermanand Corey Walker, andUltimate Guide to Social Media Marketing(Entrepreneur Press, 2020) with Jenn Herman, Stephanie Liu,Amanda Robinson, and Mike Allton.Upon his graduation from California State University, Fresno in 1996with a master's degree in communication, Eric moved to Roseville,California, where he lived for 13 years. Eric continued to build hisbusiness and worked as a technical writer for a wide variety ofbusinesses, from startups to large companies, including Intel, WellsFargo Wachovia, TASQ Technology, Cisco Systems, and HewlettPackard. Many of those clients required their technical writers to knowMicrosoft Excel, which Eric has used since the early 1990s. From 1997to 1999, during his off time, Eric produced 30 issues ofSacra Blue, theaward winning monthly magazine of the Sacramento PC Users Group.When Eric isn't working in (and on) his business or writing books, youcan find him enjoying time with friends, walking around the historicGold Rush town of Jackson, and helping his mother manage her infantand toddler daycare business.
Page 19 of 31
MCA Microsoft Office Specialist Study Guide: Excel Associate Exam MO-200 (2021) - Page 19 preview imageAbout the Technical EditorKristen Merrittis an experienced technical editor who has reviewedbooks for several publishers, including Wiley and Microsoft Press.Kristen spent 12 years in technical sales, and she is currently employedas a digital marketer.
Page 20 of 31
MCA Microsoft Office Specialist Study Guide: Excel Associate Exam MO-200 (2021) - Page 20 preview imageTable of ExercisesExercise 1.1Importing Text and CSV Format FilesExercise 1.2Navigating in a WorkbookExercise 1.3Formatting a WorkbookExercise 1.4Changing Workbook Properties and ViewsExercise 1.5Changing Print Settings and Inspecting aWorkbookExercise 2.1Manipulating DataExercise 2.2Formatting Cells and RangesExercise 2.3Define and Reference Named RangesExercise 2.4Summarizing Data VisuallyExercise 3.1Creating a TableExercise 3.2Modifying a TableExercise 3.3Filtering and Sorting Your Table DataExercise 4.1Inserting ReferencesExercise 4.2Calculate and Transform DatasExercise 4.3Formatting and Modifying TextExercise 5.1Creating ChartsExercise 5.2Modifying ChartsExercise 5.3Formatting Charts
Page 21 of 31
MCA Microsoft Office Specialist Study Guide: Excel Associate Exam MO-200 (2021) - Page 21 preview imageIntroductionWelcome toMCA Microsoft Office Specialist(Office 365 and Office2019) Study Guide: Excel Associate Exam MO 200!This book is thefirst step in your journey to becoming a Microsoft Certified OfficeSpecialist for Microsoft Excel, which is a component of the Microsoft365 suite of productivity applications to which you can subscribe. Youcan also use this book with the one time purchase version of Excel,which Microsoft calls Excel 2019.Microsoft 365 allows you to use the different versions of Excel onmany platforms, including Windows, macOS, iOS, iPadOS, andAndroid. You can even use the web version of Excel on the free onlineversion of Microsoft 365. This book, however, talks about using themost popular version of Excel on the most popular operating system,which happens to be Excel for Microsoft 365 running on Windows 10.You may already know about a lot of Excel features by working with it,but regardless of whether you use Excel for your regulardocumentation tasks or you're new to the application, you'll learn a lotabout the power that Excel gives you to create all kinds of documents.Who Should Read This BookIf you want to prepare to take the Microsoft Excel Exam MO 200,which will help you become a certified Excel specialist and hopefullyincrease your stature, marketability, and income, then this is the bookfor you. Even if you're not going to take the exam but you want to learnhow to use Excel more effectively, this book will show you how to getthe most out of using Excel based on features that Microsoft believesare important for you to know.What You'll Learn from This BookWhat you learn in this book adheres to the topics in the MicrosoftExcel Exam MO 200, because this book is designed to help you learnabout the topics in the exam and pass it on the first try.
Page 22 of 31
MCA Microsoft Office Specialist Study Guide: Excel Associate Exam MO-200 (2021) - Page 22 preview imageAfter you finish reading the book and complete all the exercises, you'llhave an in depth understanding of Excel that you can use to becomemore productive at work and at home (or in your home office).Hardware and Software RequirementsYou should be running a computer with Windows 10 installed, andyou should have Excel for Microsoft 365 or Excel 2019 installed andrunning too before you dive into this book. Either version of Excelcontains all the features that are documented in this book so that youcan pass the exam.How to Use This BookStart by taking the Assessment Test after this introduction to see howwell you know Excel already. Even if you've been using Excel for awhile, you may be surprised at how much you don't know about it.Next, read each chapter and go through each of the exercises in thechapter to reinforce the concepts in each section. When you reach theend of the chapter, answer each of the 10 Review Questions to testwhat you learned. You can check your answers in the appendix at theback of the book.If you're indeed taking the exam, then there are two other valuabletools that you can use: Flashcards and a Practice Exam. You mayremember flashcards from when you were in school, and they're usefulwhen you want to reinforce your knowledge. Use the Flashcards with afriend or relative if you like. (They might appreciate learning aboutExcel, too.) The Practice Exam will help you further hone your abilityto answer any question on the real exam with no worries.How to Contact Wiley or the AuthorIf you believe you have found an error in this book and it is not listedon the book's errata page, you can report the issue to our customertechnical support team atsupport.wiley.com.You can email the author with your comments or questions ateric@butow.net. You can also visit Eric's website atwww.butow.net.
Page 23 of 31
MCA Microsoft Office Specialist Study Guide: Excel Associate Exam MO-200 (2021) - Page 23 preview imageHow This Book Is OrganizedChapter 1: Managing Worksheets and WorkbooksThischapter introduces you to importing data into workbooks,navigating within a workbook, formatting worksheets andworkbooks so that they look the way you want, customizing Exceloptions and views, saving a workbook, sharing a workbook, andinspecting a workbook before you share it, so that all of yourrecipients can read it.Chapter 2: Using Data Cells and RangesThis chapterfollows up by showing you how to manipulate your data in aworksheet to show the data that you want to see; formatting cellsand ranges in a worksheet using Excel tools, including FormatPainter and styles; defining and referencing cell ranges; andsummarizing your data with Sparklines and conditionalformatting.Chapter 3: Working with Tables and Table DataThischapter shows you how to use the built in table tools to createtables of information, convert the table to a cell range (and viceversa), as well as modify the table to look the way that you want.You'll also learn how to sort and filter text in a table.Chapter 4: Performing Operations by Using Formulasand FunctionsThis chapter tells you how to insert referencesinto a cell formula, perform calculations, count cells, executeconditional operations, as well as format text using a variety ofbuilt in Excel functions.Chapter 5: Managing ChartsThis chapter covers how tocreate charts within a worksheet and on a separate worksheet,modify a chart to show the data you want, format a chart withlayouts and styles, and add alternative text to a chart so thateveryone who sees the chart will know what it's about.Interactive Online Learning Environment andTest Bank
Page 24 of 31
MCA Microsoft Office Specialist Study Guide: Excel Associate Exam MO-200 (2021) - Page 24 preview imageLearning the material in theMCA Microsoft Office Specialist (Office365 and Office 2019) Study Guide: Excel Associate Exam MO 200isan important part of preparing for theExcel Exam MO 200, but wealso provide additional tools to help you prepare. The online test bankwill help you understand the types of questions that will appear on thecertification exam.The sample tests in the test bank include all of the questions in eachchapter as well as the questions from the Assessment Test. In addition,there is a Practice Exam containing 50 questions. You can use this testto evaluate your understanding and identify areas that may requireadditional study.The flashcards in the test bank will push the limits of what you shouldknow for the certification exam. The flashcards contain 100 questionsprovided in digital format. Each flashcard has one question and onecorrect answer.The online glossary is a searchable list of key terms introduced in thisStudy Guide that you should know for theExcel Exam MO 200.To start using these tools to study for theMCA Microsoft OfficeSpecialist Study Guide: Excel Exam MO 200, go towww.wiley.com/go/sybextestprepand register your book to receiveyour unique PIN. Once you have the PIN, return towww.wiley.com/go/sybextestprep, find your book, and click Register, orlog in and follow the link to register a new account or to add this bookto an existing account.Exam objectives are subject to change at any timewithout prior notice and at Microsoft's sole discretion. Please visitthe Exam MO 200: Microsoft Excel (Excel and Excel 2019) website(https://docs.microsoft.com/en-us/learn/certifications/exams/MO-200) for the most currentlisting of exam objectives.
Page 25 of 31
MCA Microsoft Office Specialist Study Guide: Excel Associate Exam MO-200 (2021) - Page 25 preview imageObjective MapObjectiveChapterSection 1: Manage worksheets and workbooks1.1 Import data into workbooks11.2 Navigate within workbooks11.3 Format worksheets and workbooks1, 2, 3, 4,51.4 Customize options and views11.5 Configure content for collaboration1Section 2: Manage data cells and ranges2.1 Manipulate data in worksheets22.2 Format cells and ranges1, 22.3 Define and reference named changes22.4 Summarize data visually2Section 3: Manage tables and table data3.1 Create and format tables33.2 Modify tables1, 2, 33.3 Filter and sort table data3Section 4: Perform operations by using formulasand functions4.1 Insert references44.2 Calculate and transform datas44.3 Format and modify text1, 2, 4Section 5: Manage charts5.1 Create charts55.2 Modify charts55.3 Format charts5
Page 26 of 31
MCA Microsoft Office Specialist Study Guide: Excel Associate Exam MO-200 (2021) - Page 26 preview imageAssessment Test1. What does the header row do in a table? (Choose all that apply.)A. It tells you what the columns are about.B. It allows you to sort data in a column.C. It sets apart the table from the rest of the worksheet.D. It allows you to filter data in a column.2. When you copy a cell, what's the fastest way of copying it?A. Clicking Copy in the Home ribbonB. Right clicking the cell and then clicking Copy in the contextmenuC. Pressing Ctrl+CD. Adding the Copy icon to the Quick Access Toolbar3. What are ways in which you can identify data in a chart? (Chooseall that apply.)A. AxesB. LegendC. TableD. Titles4. What are the three reference types that you can add in a formula?(Choose all that apply.)A. RelativeB. AbsoluteC. NumericD. Mixed5. What two types of files can you import into an Excel workbook?(Choose all that apply.)A. Word
Page 27 of 31
MCA Microsoft Office Specialist Study Guide: Excel Associate Exam MO-200 (2021) - Page 27 preview imageB. TextC. CSVD. PowerPoint6. How can you select a chart element in your chart? (Choose all thatapply.)A. Click the chart element.B. Click within the chart.C. Click the Chart Area box in the Format ribbon.D. Click Select Data in the Chart Design ribbon.7. What option do you use to rotate text but not make the text itselfchange its orientation?A. Rotate Text UpB. Align CenterC. Vertical TextD. Rotate Text Down8. What does theSUM()function do?A. Adds all of the numbers in selected cellsB. Summarizes the numbers in selected cellsC. Calculates the average of all numbers in selected cellsD. Counts all of the selected cells that have numbers in them9. What drop down list box do you select when you want to findinformation in an entire workbook?A. SearchB. Look InC. WithinD. Find What10. What function do you use in a new cell to have Excel return the
Page 28 of 31
MCA Microsoft Office Specialist Study Guide: Excel Associate Exam MO-200 (2021) - Page 28 preview imagefirst few characters in a cell that contains text?A.MID()B.UPPER()C.LEN()D.LEFT()11. What information can you show and hide in a table style? (Chooseall that apply.)A. Header rowB. First columnC. Filter buttonD. Banded rows12. Why would you assign a name to a range of cells?A. You don't have to because cells automatically have rownumbers and column letters.B. You need to do this before you save the workbook.C. You can't find cells in a worksheet without naming them.D. You want to find groups of cells in a worksheet more easily.13. What is the default row height in an Excel worksheet?A. 10 pointsB. 15 pointsC. One inchD. 72 points14. How does theCOUNTA()function differ from theCOUNT()function?A.COUNTA()counts all the blank cells in a selected range.B. You can count specific numbers in theCOUNTA()function.C.COUNTA()allows you to only count text, not numbers.D. TheCOUNTA()function counts selected cells that are not
Page 29 of 31
MCA Microsoft Office Specialist Study Guide: Excel Associate Exam MO-200 (2021) - Page 29 preview imageempty.15. What does Excel call a chart created in a new worksheet?A. Chart sheetB. Chart1C. Whatever you decide the new worksheet should beD. Excel creates a new workbook and then you must give it aname.16. When you need to sort table data with text and numbers in it,what is the best way to sort?A. In ascending orderB. Using the sort and filter buttons in the header rowC. A custom sortD. In descending order17. What is a Sparkline chart?A. A chart format that lets you add graphic sparkles to yourchartB. A small chart that quickly summarizes what you see in a rowC. One of the built in chart stylesD. Another term for a win loss chart18. When would you use theTEXTJOIN()function instead of theCONCAT()function?A. When you want to ignore blank cells in the selected rangeB. You don't have to becauseTEXTJOIN()replacesCONCAT()in thelatest version of Excel.C. To add a space between text in each cellD. When you don't want to type in the cell references within theformula19. What does an error bar in a chart show? (Choose all that apply.)
Page 30 of 31
MCA Microsoft Office Specialist Study Guide: Excel Associate Exam MO-200 (2021) - Page 30 preview imageA. How inaccurate the data is in the chartB. Margins of errorC. Standard deviationD. How much you can change the numeric value in a cellformula20. When you format a table style, what formats can you change?(Choose all that apply.)A. FontB. AlignmentC. BorderD. Fill color and pattern21. Your boss likes your chart but wants the background of the chartto be dark so that the text will stand out. How do you do thisquickly?A. Apply a chart layout.B. Change the background color of the chart.C. Apply a different chart style.D. Tell your boss that there are no chart backgrounds other thanwhite.22. What are the minimum and maximum magnification views in aworksheet?A. 20 percent and 125 percentB. 10 percent and 150 percentC. 25 percent and 200 percentD. 5 percent and 300 percent23. If you delete a row or column and immediately decide that youdidn't want to do that, what do you do?A. Nothing
Page 31 of 31
MCA Microsoft Office Specialist Study Guide: Excel Associate Exam MO-200 (2021) - Page 31 preview imageB. Open the Home menu ribbon.C. Add the new row or column again.D. Press Ctrl+Z.24. In the Paste Special dialog box, what button do you click to pastea number from one cell into a blank cell?A. FormatsB. ValuesC. NoneD. Validation25. What file formats can you save an Excel file to? (Choose all thatapply.)A. XMLB. WordC. PDFD. ExcelAnswers to Assessment Test1. B, D. Each cell in the header row contains a button that allows youto sort and filter data in the column. See Chapter 3 for moreinformation.2. C. Press Ctrl+C to copy all the information in one cell into anempty cell. See Chapter 2 for more information.3. B, C. You can add a legend, a data table, as well as data labels to achart to help you and others understand what the chartrepresents. See Chapter 5 for more information.4. A, B, D. Excel can create relative, absolute, and mixed referencetypes in a cell formula. See Chapter 4 for more information.5. B, C. You can import files with the TXT and CSV formats into anExcel workbook. See Chapter 1 for more information.
Preview Mode

This document has 319 pages. Sign in to access the full document!

Study Now!

X-Copilot AI
Unlimited Access
Secure Payment
Instant Access
24/7 Support
Document Chat

Document Details

Subject
Microsoft Certified Associate

Related Documents

View all
Certification Guides

MCA Microsoft Office Specialist Study Guide: PowerPoint Associate Exam MO-300 (2021)

4.4
•132