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.2
91
about 2 months ago
Preview (31 of 319)
Sign in to access the full document!
Table of Contents
Cover
Title Page
Copyright
Dedication
Acknowledgments
About the Author
About the Technical Editor
Table of Exercises
Introduction
Interactive Online Learning Environment and Test Bank
Objective Map
Assessment Test
Answers to Assessment Test
Chapter 1: Managing Worksheets and Workbooks
Importing Data into Workbooks
Navigating Within Workbooks
Formatting Worksheets and Workbooks
Customizing Options and Views
Configuring Content for Collaboration
Summary
Key Terms
Exam Essentials
Review Questions
Chapter 2: Using Data Cells and Ranges
Manipulating Data in Worksheets
Formatting Cells and Ranges
Defining and Referencing Named Ranges
Cover
Title Page
Copyright
Dedication
Acknowledgments
About the Author
About the Technical Editor
Table of Exercises
Introduction
Interactive Online Learning Environment and Test Bank
Objective Map
Assessment Test
Answers to Assessment Test
Chapter 1: Managing Worksheets and Workbooks
Importing Data into Workbooks
Navigating Within Workbooks
Formatting Worksheets and Workbooks
Customizing Options and Views
Configuring Content for Collaboration
Summary
Key Terms
Exam Essentials
Review Questions
Chapter 2: Using Data Cells and Ranges
Manipulating Data in Worksheets
Formatting Cells and Ranges
Defining and Referencing Named Ranges
Table of Contents
Cover
Title Page
Copyright
Dedication
Acknowledgments
About the Author
About the Technical Editor
Table of Exercises
Introduction
Interactive Online Learning Environment and Test Bank
Objective Map
Assessment Test
Answers to Assessment Test
Chapter 1: Managing Worksheets and Workbooks
Importing Data into Workbooks
Navigating Within Workbooks
Formatting Worksheets and Workbooks
Customizing Options and Views
Configuring Content for Collaboration
Summary
Key Terms
Exam Essentials
Review Questions
Chapter 2: Using Data Cells and Ranges
Manipulating Data in Worksheets
Formatting Cells and Ranges
Defining and Referencing Named Ranges
Cover
Title Page
Copyright
Dedication
Acknowledgments
About the Author
About the Technical Editor
Table of Exercises
Introduction
Interactive Online Learning Environment and Test Bank
Objective Map
Assessment Test
Answers to Assessment Test
Chapter 1: Managing Worksheets and Workbooks
Importing Data into Workbooks
Navigating Within Workbooks
Formatting Worksheets and Workbooks
Customizing Options and Views
Configuring Content for Collaboration
Summary
Key Terms
Exam Essentials
Review Questions
Chapter 2: Using Data Cells and Ranges
Manipulating Data in Worksheets
Formatting Cells and Ranges
Defining and Referencing Named Ranges
Summarizing Data Visually
Summary
Key Terms
Exam Essentials
Review Questions
Chapter 3: Working with Tables and Table Data
Creating and Formatting Tables
Modifying Tables
Filtering and Sorting Table Data
Summary
Key Terms
Exam Essentials
Review Questions
Chapter 4: Performing Operations by Using Formulas and
Functions
Inserting References
Calculating and Transforming Datas
Formatting and Modifying Text
Summary
Key Terms
Exam Essentials
Review Questions
Chapter 5: Managing Charts
Creating Charts
Modifying Charts
Formatting Charts
Summary
Key Terms
Exam Essentials
Summary
Key Terms
Exam Essentials
Review Questions
Chapter 3: Working with Tables and Table Data
Creating and Formatting Tables
Modifying Tables
Filtering and Sorting Table Data
Summary
Key Terms
Exam Essentials
Review Questions
Chapter 4: Performing Operations by Using Formulas and
Functions
Inserting References
Calculating and Transforming Datas
Formatting and Modifying Text
Summary
Key Terms
Exam Essentials
Review Questions
Chapter 5: Managing Charts
Creating Charts
Modifying Charts
Formatting Charts
Summary
Key Terms
Exam Essentials
Loading page 4...
Review Questions
Appendix: Answers to Review Questions
Chapter 1: Managing Worksheets and Workbooks
Chapter 2: Using Data Cells and Ranges
Chapter 3: Working with Tables and Table Data
Chapter 4: Performing Operations by Using Formulas and
Functions
Chapter 5: Managing Charts
Index
Online Test Bank
Register and Access the Online Test Bank
End User License Agreement
List of Illustrations
Chapter 1
FIGURE 1.1 Text Import Wizard dialog box
FIGURE 1.2 Get & Transform section
FIGURE 1.3 Import Data dialog box
FIGURE 1.4 Preview dialog box
FIGURE 1.5 Find And Replace dialog box
FIGURE 1.6 The Define Name menu option
FIGURE 1.7 Go To dialog box
FIGURE 1.8 The Link icon
FIGURE 1.9 The Insert Hyperlink dialog box
FIGURE 1.10 The Place In This Document menu option
FIGURE 1.11 A list of recently opened files
FIGURE 1.12 Browsed Pages list
Appendix: Answers to Review Questions
Chapter 1: Managing Worksheets and Workbooks
Chapter 2: Using Data Cells and Ranges
Chapter 3: Working with Tables and Table Data
Chapter 4: Performing Operations by Using Formulas and
Functions
Chapter 5: Managing Charts
Index
Online Test Bank
Register and Access the Online Test Bank
End User License Agreement
List of Illustrations
Chapter 1
FIGURE 1.1 Text Import Wizard dialog box
FIGURE 1.2 Get & Transform section
FIGURE 1.3 Import Data dialog box
FIGURE 1.4 Preview dialog box
FIGURE 1.5 Find And Replace dialog box
FIGURE 1.6 The Define Name menu option
FIGURE 1.7 Go To dialog box
FIGURE 1.8 The Link icon
FIGURE 1.9 The Insert Hyperlink dialog box
FIGURE 1.10 The Place In This Document menu option
FIGURE 1.11 A list of recently opened files
FIGURE 1.12 Browsed Pages list
Loading page 5...
FIGURE 1.13 E mail Address menu option
FIGURE 1.14 Edit Hyperlink dialog box
FIGURE 1.15 The Remove Hyperlink option in the context
menu
FIGURE 1.16 Page Setup section options
FIGURE 1.17 Column Width option
FIGURE 1.18 AutoFit Column Width option
FIGURE 1.19 Keep Source Column Widths icon
FIGURE 1.20 Default Width option
FIGURE 1.21 The resize mouse pointer between two column
headings
FIGURE 1.22 Row Height option
FIGURE 1.23 AutoFit Row Height option
FIGURE 1.24 The resize mouse pointer between two row
headings
FIGURE 1.25 Header & Footer icon
FIGURE 1.26 Header element drop down list
FIGURE 1.27 Header & Footer Elements section
FIGURE 1.28 Page Setup dialog box
FIGURE 1.29 The new icon in the Quick Access Toolbar
FIGURE 1.30 Customize Quick Access Toolbar drop down
menu
FIGURE 1.31 The Quick Access Toolbar below the ribbon
FIGURE 1.32 Reset button in Excel Options dialog box
FIGURE 1.33 Zoom dialog box
FIGURE 1.34 Custom Views icon
FIGURE 1.35 Add View dialog box
FIGURE 1.14 Edit Hyperlink dialog box
FIGURE 1.15 The Remove Hyperlink option in the context
menu
FIGURE 1.16 Page Setup section options
FIGURE 1.17 Column Width option
FIGURE 1.18 AutoFit Column Width option
FIGURE 1.19 Keep Source Column Widths icon
FIGURE 1.20 Default Width option
FIGURE 1.21 The resize mouse pointer between two column
headings
FIGURE 1.22 Row Height option
FIGURE 1.23 AutoFit Row Height option
FIGURE 1.24 The resize mouse pointer between two row
headings
FIGURE 1.25 Header & Footer icon
FIGURE 1.26 Header element drop down list
FIGURE 1.27 Header & Footer Elements section
FIGURE 1.28 Page Setup dialog box
FIGURE 1.29 The new icon in the Quick Access Toolbar
FIGURE 1.30 Customize Quick Access Toolbar drop down
menu
FIGURE 1.31 The Quick Access Toolbar below the ribbon
FIGURE 1.32 Reset button in Excel Options dialog box
FIGURE 1.33 Zoom dialog box
FIGURE 1.34 Custom Views icon
FIGURE 1.35 Add View dialog box
Loading page 6...
FIGURE 1.36 The selected view in the list is at the top.
FIGURE 1.37 Freeze Panes drop down menu
FIGURE 1.38 Page Break Preview view
FIGURE 1.39 Page Layout view
FIGURE 1.40 Info screen
FIGURE 1.41 Protect Sheet option
FIGURE 1.42 Protect Sheet dialog box
FIGURE 1.43 Set Print Area option
FIGURE 1.44 A partial list of file formats
FIGURE 1.45 Print screen
FIGURE 1.46 Document Inspector dialog box
Chapter 2
FIGURE 2.1 The Paste drop down menu
FIGURE 2.2 Paste Special dialog box
FIGURE 2.3 Pop up box shows what value you will fill.
FIGURE 2.4 Inserted columns
FIGURE 2.5 Inserted rows
FIGURE 2.6 Insert Sheet Columns menu option
FIGURE 2.7 Insert Sheet Rows menu option
FIGURE 2.8 Merge options in the drop down menu
FIGURE 2.9 Align Left, Align Center, and Align Right icons
FIGURE 2.10 Orientation drop down menu
FIGURE 2.11 Format Cells dialog box
FIGURE 2.12 Format Painter icon
FIGURE 2.13 Wrap Text option
FIGURE 2.14 Number format options
FIGURE 1.37 Freeze Panes drop down menu
FIGURE 1.38 Page Break Preview view
FIGURE 1.39 Page Layout view
FIGURE 1.40 Info screen
FIGURE 1.41 Protect Sheet option
FIGURE 1.42 Protect Sheet dialog box
FIGURE 1.43 Set Print Area option
FIGURE 1.44 A partial list of file formats
FIGURE 1.45 Print screen
FIGURE 1.46 Document Inspector dialog box
Chapter 2
FIGURE 2.1 The Paste drop down menu
FIGURE 2.2 Paste Special dialog box
FIGURE 2.3 Pop up box shows what value you will fill.
FIGURE 2.4 Inserted columns
FIGURE 2.5 Inserted rows
FIGURE 2.6 Insert Sheet Columns menu option
FIGURE 2.7 Insert Sheet Rows menu option
FIGURE 2.8 Merge options in the drop down menu
FIGURE 2.9 Align Left, Align Center, and Align Right icons
FIGURE 2.10 Orientation drop down menu
FIGURE 2.11 Format Cells dialog box
FIGURE 2.12 Format Painter icon
FIGURE 2.13 Wrap Text option
FIGURE 2.14 Number format options
Loading page 7...
FIGURE 2.15 Format Cells option
FIGURE 2.16 Format Cells dialog box
FIGURE 2.17 Cell Styles drop down list
FIGURE 2.18 Clear drop down list
FIGURE 2.19 Define Name option
FIGURE 2.20 New Name dialog box
FIGURE 2.21 Create From Selection option
FIGURE 2.22 Create Names From Selection dialog box
FIGURE 2.23 Use In Formula drop down menu
FIGURE 2.24 Highlighted table name in the Table Name Box
FIGURE 2.25 Highlighted table in the Name Box
FIGURE 2.26 The three Sparkline icons
FIGURE 2.27 The selected cell range
FIGURE 2.28 Sparkline menu ribbon
FIGURE 2.29 Quick Analysis icon
FIGURE 2.30 Quick Analysis drop down menu
FIGURE 2.31 Clear Rules From Entire Sheet option
FIGURE 2.32 Clear Rules From Selected Cells option
FIGURE 2.33 Go To Special dialog box
Chapter 3
FIGURE 3.1 Table styles in the Format As Table drop down
menu
FIGURE 3.2 Format As Table dialog box
FIGURE 3.3 Highlighted default name in the New Table Style
dialog box
FIGURE 3.4 Format Cells dialog box
FIGURE 3.5 Convert To Range menu option
FIGURE 2.16 Format Cells dialog box
FIGURE 2.17 Cell Styles drop down list
FIGURE 2.18 Clear drop down list
FIGURE 2.19 Define Name option
FIGURE 2.20 New Name dialog box
FIGURE 2.21 Create From Selection option
FIGURE 2.22 Create Names From Selection dialog box
FIGURE 2.23 Use In Formula drop down menu
FIGURE 2.24 Highlighted table name in the Table Name Box
FIGURE 2.25 Highlighted table in the Name Box
FIGURE 2.26 The three Sparkline icons
FIGURE 2.27 The selected cell range
FIGURE 2.28 Sparkline menu ribbon
FIGURE 2.29 Quick Analysis icon
FIGURE 2.30 Quick Analysis drop down menu
FIGURE 2.31 Clear Rules From Entire Sheet option
FIGURE 2.32 Clear Rules From Selected Cells option
FIGURE 2.33 Go To Special dialog box
Chapter 3
FIGURE 3.1 Table styles in the Format As Table drop down
menu
FIGURE 3.2 Format As Table dialog box
FIGURE 3.3 Highlighted default name in the New Table Style
dialog box
FIGURE 3.4 Format Cells dialog box
FIGURE 3.5 Convert To Range menu option
Loading page 8...
FIGURE 3.6 Resize Table option
FIGURE 3.7 The reordered table rows
FIGURE 3.8 The Delete Table Rows and Delete Table
Columns options
FIGURE 3.9 Table Style Options section check boxes
FIGURE 3.10 Total Row check box
FIGURE 3.11 Sum option in drop down menu
FIGURE 3.12 Filter drop down menu
FIGURE 3.13 The filtered table shows one row that contains
the cell.
FIGURE 3.14 Sort & Filter drop down menu
FIGURE 3.15 Sort dialog box
Chapter 4
FIGURE 4.1 Relative cell reference
FIGURE 4.2 Absolute cell reference
FIGURE 4.3 Mixed cell reference
FIGURE 4.4 Absolute reference in the Formula Bar
FIGURE 4.5 The formula in the Formula Bar
FIGURE 4.6 The average of all five numbers
FIGURE 4.7 Average of five numbers in the cell
FIGURE 4.8 The calculated MAX result in the cell and the
formula in the Form...
FIGURE 4.9 The calculated MIN result in the cell and the
formula in the Form...
FIGURE 4.10 The calculated SUM result in the cell and the
formula in the For...
FIGURE 4.11 The count result in the cell and the formula in
the Formula Bar...
FIGURE 3.7 The reordered table rows
FIGURE 3.8 The Delete Table Rows and Delete Table
Columns options
FIGURE 3.9 Table Style Options section check boxes
FIGURE 3.10 Total Row check box
FIGURE 3.11 Sum option in drop down menu
FIGURE 3.12 Filter drop down menu
FIGURE 3.13 The filtered table shows one row that contains
the cell.
FIGURE 3.14 Sort & Filter drop down menu
FIGURE 3.15 Sort dialog box
Chapter 4
FIGURE 4.1 Relative cell reference
FIGURE 4.2 Absolute cell reference
FIGURE 4.3 Mixed cell reference
FIGURE 4.4 Absolute reference in the Formula Bar
FIGURE 4.5 The formula in the Formula Bar
FIGURE 4.6 The average of all five numbers
FIGURE 4.7 Average of five numbers in the cell
FIGURE 4.8 The calculated MAX result in the cell and the
formula in the Form...
FIGURE 4.9 The calculated MIN result in the cell and the
formula in the Form...
FIGURE 4.10 The calculated SUM result in the cell and the
formula in the For...
FIGURE 4.11 The count result in the cell and the formula in
the Formula Bar...
Loading page 9...
FIGURE 4.12 The COUNTA results in the cell and the formula in
the Formula Ba...
FIGURE 4.13 The calculated COUNTBLANK result in the cell and
formula in the ...
FIGURE 4.14 The FALSE result in the cell with the formula in
the Formula Bar...
FIGURE 4.15 The results of the formula in column R
FIGURE 4.16 The last two letters in cell A4
FIGURE 4.17 The first two letters in cell A6
FIGURE 4.18 The three characters in cell A11
FIGURE 4.19 All uppercase text in column C
FIGURE 4.20 All lowercase text in column C
FIGURE 4.21 Length in characters in column C
FIGURE 4.22 Combined text with CONCAT() function in the
Formula Bar
FIGURE 4.23 The combined text with spaces between each
text string
Chapter 5
FIGURE 5.1 The Recommended Charts icon in the Insert
ribbon
FIGURE 5.2 The Insert Chart dialog box
FIGURE 5.3 The Column category in the All Charts tab
FIGURE 5.4 The chart in the worksheet
FIGURE 5.5 Move Chart icon in the Chart Design ribbon
FIGURE 5.6 Move Chart dialog box
FIGURE 5.7 The chart in a new tab
FIGURE 5.8 The updated chart and expanded selection area
in the table
the Formula Ba...
FIGURE 4.13 The calculated COUNTBLANK result in the cell and
formula in the ...
FIGURE 4.14 The FALSE result in the cell with the formula in
the Formula Bar...
FIGURE 4.15 The results of the formula in column R
FIGURE 4.16 The last two letters in cell A4
FIGURE 4.17 The first two letters in cell A6
FIGURE 4.18 The three characters in cell A11
FIGURE 4.19 All uppercase text in column C
FIGURE 4.20 All lowercase text in column C
FIGURE 4.21 Length in characters in column C
FIGURE 4.22 Combined text with CONCAT() function in the
Formula Bar
FIGURE 4.23 The combined text with spaces between each
text string
Chapter 5
FIGURE 5.1 The Recommended Charts icon in the Insert
ribbon
FIGURE 5.2 The Insert Chart dialog box
FIGURE 5.3 The Column category in the All Charts tab
FIGURE 5.4 The chart in the worksheet
FIGURE 5.5 Move Chart icon in the Chart Design ribbon
FIGURE 5.6 Move Chart dialog box
FIGURE 5.7 The chart in a new tab
FIGURE 5.8 The updated chart and expanded selection area
in the table
Loading page 10...
FIGURE 5.9 Select Data Source dialog box and selected table
cells
FIGURE 5.10 Updated chart in chart sheet
FIGURE 5.11 Row titles in the horizontal axis
FIGURE 5.12 Chart elements list
FIGURE 5.13 Trend line for teddy bears
FIGURE 5.14 Excel previews the layout in the chart.
FIGURE 5.15 Format ribbon sections
FIGURE 5.16 Current Selection section
FIGURE 5.17 Insert Shapes section
FIGURE 5.18 Shape Styles section
FIGURE 5.19 WordArt Styles section
FIGURE 5.20 Arrange section
FIGURE 5.21 Size section
FIGURE 5.22 The preview of the style in the chart
FIGURE 5.23 Six swatch colors in the selected swatch group
FIGURE 5.24 Alt Text pane
cells
FIGURE 5.10 Updated chart in chart sheet
FIGURE 5.11 Row titles in the horizontal axis
FIGURE 5.12 Chart elements list
FIGURE 5.13 Trend line for teddy bears
FIGURE 5.14 Excel previews the layout in the chart.
FIGURE 5.15 Format ribbon sections
FIGURE 5.16 Current Selection section
FIGURE 5.17 Insert Shapes section
FIGURE 5.18 Shape Styles section
FIGURE 5.19 WordArt Styles section
FIGURE 5.20 Arrange section
FIGURE 5.21 Size section
FIGURE 5.22 The preview of the style in the chart
FIGURE 5.23 Six swatch colors in the selected swatch group
FIGURE 5.24 Alt Text pane
Loading page 11...
MCA
Microsoft® Office Specialist
(Office 365 and Office 2019)
Study Guide
Excel Associate Exam MO‐200
Eric Butow
Microsoft® Office Specialist
(Office 365 and Office 2019)
Study Guide
Excel Associate Exam MO‐200
Eric Butow
Loading page 12...
Loading page 13...
Copyright © 2021 by John Wiley & Sons, Inc., Indianapolis, Indiana
Published simultaneously in Canada
ISBN: 978 1 119 71824 6
ISBN: 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 in
any form or by any means, electronic, mechanical, photocopying, recording, scanning or
otherwise, except as permitted under Sections 107 or 108 of the 1976 United States Copyright
Act, without either the prior written permission of the Publisher, or authorization through
payment of the appropriate per copy fee to the Copyright Clearance Center, 222 Rosewood
Drive, Danvers, MA 01923, (978) 750 8400, fax (978) 646 8600. Requests to the Publisher for
permission should be addressed to the Permissions Department, John Wiley & Sons, Inc., 111
River Street, Hoboken, NJ 07030, (201) 748 6011, fax (201) 748 6008, or online at
www.wiley.com/go/permissions.
Limit of Liability/Disclaimer of Warranty: The publisher and the author make no
representations or warranties with respect to the accuracy or completeness of the contents of
this work and specifically disclaim all warranties, including without limitation warranties of
fitness for a particular purpose. No warranty may be created or extended by sales or
promotional materials. The advice and strategies contained herein may not be suitable for
every situation. This work is sold with the understanding that the publisher is not engaged in
rendering legal, accounting, or other professional services. If professional assistance is
required, the services of a competent professional person should be sought. Neither the
publisher nor the author shall be liable for damages arising herefrom. The fact that an
organization or Web site is referred to in this work as a citation and/or a potential source of
further information does not mean that the author or the publisher endorses the information
the organization or Web site may provide or recommendations it may make. Further, readers
should be aware that Internet Web sites listed in this work may have changed or disappeared
between 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 the
U.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. Some
material included with standard print versions of this book may not be included in e books or
in print on demand. If this book refers to media such as a CD or DVD that is not included in
the version you purchased, you may download this material at booksupport.wiley.com. For
more information about Wiley products, visit www.wiley.com.
Library of Congress Control Number: 2020952862
TRADEMARKS: Wiley, the Wiley logo, and the Sybex logo are trademarks or registered
trademarks of John Wiley & Sons, Inc. and/or its affiliates, in the United States and other
countries, and may not be used without written permission. Microsoft and Office 365 are
registered trademarks of Microsoft Corporation. All other trademarks are the property of their
respective owners. John Wiley & Sons, Inc. is not associated with any product or vendor
mentioned in this book.
Published simultaneously in Canada
ISBN: 978 1 119 71824 6
ISBN: 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 in
any form or by any means, electronic, mechanical, photocopying, recording, scanning or
otherwise, except as permitted under Sections 107 or 108 of the 1976 United States Copyright
Act, without either the prior written permission of the Publisher, or authorization through
payment of the appropriate per copy fee to the Copyright Clearance Center, 222 Rosewood
Drive, Danvers, MA 01923, (978) 750 8400, fax (978) 646 8600. Requests to the Publisher for
permission should be addressed to the Permissions Department, John Wiley & Sons, Inc., 111
River Street, Hoboken, NJ 07030, (201) 748 6011, fax (201) 748 6008, or online at
www.wiley.com/go/permissions.
Limit of Liability/Disclaimer of Warranty: The publisher and the author make no
representations or warranties with respect to the accuracy or completeness of the contents of
this work and specifically disclaim all warranties, including without limitation warranties of
fitness for a particular purpose. No warranty may be created or extended by sales or
promotional materials. The advice and strategies contained herein may not be suitable for
every situation. This work is sold with the understanding that the publisher is not engaged in
rendering legal, accounting, or other professional services. If professional assistance is
required, the services of a competent professional person should be sought. Neither the
publisher nor the author shall be liable for damages arising herefrom. The fact that an
organization or Web site is referred to in this work as a citation and/or a potential source of
further information does not mean that the author or the publisher endorses the information
the organization or Web site may provide or recommendations it may make. Further, readers
should be aware that Internet Web sites listed in this work may have changed or disappeared
between 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 the
U.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. Some
material included with standard print versions of this book may not be included in e books or
in print on demand. If this book refers to media such as a CD or DVD that is not included in
the version you purchased, you may download this material at booksupport.wiley.com. For
more information about Wiley products, visit www.wiley.com.
Library of Congress Control Number: 2020952862
TRADEMARKS: Wiley, the Wiley logo, and the Sybex logo are trademarks or registered
trademarks of John Wiley & Sons, Inc. and/or its affiliates, in the United States and other
countries, and may not be used without written permission. Microsoft and Office 365 are
registered trademarks of Microsoft Corporation. All other trademarks are the property of their
respective owners. John Wiley & Sons, Inc. is not associated with any product or vendor
mentioned in this book.
Loading page 14...
To my family and friends
Loading page 15...
Acknowledgments
I have many people to thank, starting with my literary agent, Matt
Wagner. He connected me with Sybex to write this book and managed
our relationship well. Next, I want to give a shout out to my excellent
editing team: Gary Schwartz, Barath Kumar Rajasekaran, Christine
O'Connor, and Senior Acquisitions Editor Kenyon Brown.
And, as always, I want to thank my family and friends for their
everlasting support. I couldn't write this book without them.
—Eric Butow
I have many people to thank, starting with my literary agent, Matt
Wagner. He connected me with Sybex to write this book and managed
our relationship well. Next, I want to give a shout out to my excellent
editing team: Gary Schwartz, Barath Kumar Rajasekaran, Christine
O'Connor, and Senior Acquisitions Editor Kenyon Brown.
And, as always, I want to thank my family and friends for their
everlasting support. I couldn't write this book without them.
—Eric Butow
Loading page 16...
About the Author
Eric Butow is the owner of Butow Communications Group (BCG) in
Jackson, California. BCG offers website development, online
marketing, and technical writing services. Eric is a native Californian
who started working with his friend's Apple II Plus and Radio Shack
TRS 80 Model III in 1980 when he lived in Fresno, California. He
learned about programming, graphic design, and desktop publishing
in the Fresno PC Users Group in his professional career and when he
started BCG in 1994.
Eric has written 37 other technical books as an author, as a co author
or, in one case, as a ghostwriter. Most of Eric's works were written for
the general book market, but some were written for specific clients,
Eric Butow is the owner of Butow Communications Group (BCG) in
Jackson, California. BCG offers website development, online
marketing, and technical writing services. Eric is a native Californian
who started working with his friend's Apple II Plus and Radio Shack
TRS 80 Model III in 1980 when he lived in Fresno, California. He
learned about programming, graphic design, and desktop publishing
in the Fresno PC Users Group in his professional career and when he
started BCG in 1994.
Eric has written 37 other technical books as an author, as a co author
or, in one case, as a ghostwriter. Most of Eric's works were written for
the general book market, but some were written for specific clients,
Loading page 17...
including HP and F5 Networks. Two of his books have been translated
into Chinese and Italian. Eric's most recent books are Programming
Interviews for Dummies (For Dummies, 2019) with John Sonmez,
Instagram for Dummies (For Dummies, 2019) with Jennifer Herman
and Corey Walker, and Ultimate 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 1996
with a master's degree in communication, Eric moved to Roseville,
California, where he lived for 13 years. Eric continued to build his
business and worked as a technical writer for a wide variety of
businesses, from startups to large companies, including Intel, Wells
Fargo Wachovia, TASQ Technology, Cisco Systems, and Hewlett
Packard. Many of those clients required their technical writers to know
Microsoft Excel, which Eric has used since the early 1990s. From 1997
to 1999, during his off time, Eric produced 30 issues of Sacra Blue, the
award winning monthly magazine of the Sacramento PC Users Group.
When Eric isn't working in (and on) his business or writing books, you
can find him enjoying time with friends, walking around the historic
Gold Rush town of Jackson, and helping his mother manage her infant
and toddler daycare business.
into Chinese and Italian. Eric's most recent books are Programming
Interviews for Dummies (For Dummies, 2019) with John Sonmez,
Instagram for Dummies (For Dummies, 2019) with Jennifer Herman
and Corey Walker, and Ultimate 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 1996
with a master's degree in communication, Eric moved to Roseville,
California, where he lived for 13 years. Eric continued to build his
business and worked as a technical writer for a wide variety of
businesses, from startups to large companies, including Intel, Wells
Fargo Wachovia, TASQ Technology, Cisco Systems, and Hewlett
Packard. Many of those clients required their technical writers to know
Microsoft Excel, which Eric has used since the early 1990s. From 1997
to 1999, during his off time, Eric produced 30 issues of Sacra Blue, the
award winning monthly magazine of the Sacramento PC Users Group.
When Eric isn't working in (and on) his business or writing books, you
can find him enjoying time with friends, walking around the historic
Gold Rush town of Jackson, and helping his mother manage her infant
and toddler daycare business.
Loading page 18...
About the Technical Editor
Kristen Merritt is an experienced technical editor who has reviewed
books for several publishers, including Wiley and Microsoft Press.
Kristen spent 12 years in technical sales, and she is currently employed
as a digital marketer.
Kristen Merritt is an experienced technical editor who has reviewed
books for several publishers, including Wiley and Microsoft Press.
Kristen spent 12 years in technical sales, and she is currently employed
as a digital marketer.
Loading page 19...
Table of Exercises
Exercise 1.1 Importing Text and CSV Format Files
Exercise 1.2 Navigating in a Workbook
Exercise 1.3 Formatting a Workbook
Exercise 1.4 Changing Workbook Properties and Views
Exercise 1.5 Changing Print Settings and Inspecting a
Workbook
Exercise 2.1 Manipulating Data
Exercise 2.2 Formatting Cells and Ranges
Exercise 2.3 Define and Reference Named Ranges
Exercise 2.4 Summarizing Data Visually
Exercise 3.1 Creating a Table
Exercise 3.2 Modifying a Table
Exercise 3.3 Filtering and Sorting Your Table Data
Exercise 4.1 Inserting References
Exercise 4.2 Calculate and Transform Datas
Exercise 4.3 Formatting and Modifying Text
Exercise 5.1 Creating Charts
Exercise 5.2 Modifying Charts
Exercise 5.3 Formatting Charts
Exercise 1.1 Importing Text and CSV Format Files
Exercise 1.2 Navigating in a Workbook
Exercise 1.3 Formatting a Workbook
Exercise 1.4 Changing Workbook Properties and Views
Exercise 1.5 Changing Print Settings and Inspecting a
Workbook
Exercise 2.1 Manipulating Data
Exercise 2.2 Formatting Cells and Ranges
Exercise 2.3 Define and Reference Named Ranges
Exercise 2.4 Summarizing Data Visually
Exercise 3.1 Creating a Table
Exercise 3.2 Modifying a Table
Exercise 3.3 Filtering and Sorting Your Table Data
Exercise 4.1 Inserting References
Exercise 4.2 Calculate and Transform Datas
Exercise 4.3 Formatting and Modifying Text
Exercise 5.1 Creating Charts
Exercise 5.2 Modifying Charts
Exercise 5.3 Formatting Charts
Loading page 20...
Introduction
Welcome to MCA Microsoft Office Specialist (Office 365 and Office
2019) Study Guide: Excel Associate Exam MO 200! This book is the
first step in your journey to becoming a Microsoft Certified Office
Specialist for Microsoft Excel, which is a component of the Microsoft
365 suite of productivity applications to which you can subscribe. You
can 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 on
many platforms, including Windows, macOS, iOS, iPadOS, and
Android. You can even use the web version of Excel on the free online
version of Microsoft 365. This book, however, talks about using the
most 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 regular
documentation tasks or you're new to the application, you'll learn a lot
about the power that Excel gives you to create all kinds of documents.
Who Should Read This Book
If you want to prepare to take the Microsoft Excel Exam MO 200,
which will help you become a certified Excel specialist and hopefully
increase your stature, marketability, and income, then this is the book
for you. Even if you're not going to take the exam but you want to learn
how to use Excel more effectively, this book will show you how to get
the most out of using Excel based on features that Microsoft believes
are important for you to know.
What You'll Learn from This Book
What you learn in this book adheres to the topics in the Microsoft
Excel Exam MO 200, because this book is designed to help you learn
about the topics in the exam and pass it on the first try.
Welcome to MCA Microsoft Office Specialist (Office 365 and Office
2019) Study Guide: Excel Associate Exam MO 200! This book is the
first step in your journey to becoming a Microsoft Certified Office
Specialist for Microsoft Excel, which is a component of the Microsoft
365 suite of productivity applications to which you can subscribe. You
can 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 on
many platforms, including Windows, macOS, iOS, iPadOS, and
Android. You can even use the web version of Excel on the free online
version of Microsoft 365. This book, however, talks about using the
most 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 regular
documentation tasks or you're new to the application, you'll learn a lot
about the power that Excel gives you to create all kinds of documents.
Who Should Read This Book
If you want to prepare to take the Microsoft Excel Exam MO 200,
which will help you become a certified Excel specialist and hopefully
increase your stature, marketability, and income, then this is the book
for you. Even if you're not going to take the exam but you want to learn
how to use Excel more effectively, this book will show you how to get
the most out of using Excel based on features that Microsoft believes
are important for you to know.
What You'll Learn from This Book
What you learn in this book adheres to the topics in the Microsoft
Excel Exam MO 200, because this book is designed to help you learn
about the topics in the exam and pass it on the first try.
Loading page 21...
After you finish reading the book and complete all the exercises, you'll
have an in depth understanding of Excel that you can use to become
more productive at work and at home (or in your home office).
Hardware and Software Requirements
You should be running a computer with Windows 10 installed, and
you should have Excel for Microsoft 365 or Excel 2019 installed and
running too before you dive into this book. Either version of Excel
contains all the features that are documented in this book so that you
can pass the exam.
How to Use This Book
Start by taking the Assessment Test after this introduction to see how
well you know Excel already. Even if you've been using Excel for a
while, 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 the
chapter to reinforce the concepts in each section. When you reach the
end of the chapter, answer each of the 10 Review Questions to test
what you learned. You can check your answers in the appendix at the
back of the book.
If you're indeed taking the exam, then there are two other valuable
tools that you can use: Flashcards and a Practice Exam. You may
remember flashcards from when you were in school, and they're useful
when you want to reinforce your knowledge. Use the Flashcards with a
friend or relative if you like. (They might appreciate learning about
Excel, too.) The Practice Exam will help you further hone your ability
to answer any question on the real exam with no worries.
How to Contact Wiley or the Author
If you believe you have found an error in this book and it is not listed
on the book's errata page, you can report the issue to our customer
technical support team at support.wiley.com.
You can email the author with your comments or questions at
eric@butow.net. You can also visit Eric's website at www.butow.net.
have an in depth understanding of Excel that you can use to become
more productive at work and at home (or in your home office).
Hardware and Software Requirements
You should be running a computer with Windows 10 installed, and
you should have Excel for Microsoft 365 or Excel 2019 installed and
running too before you dive into this book. Either version of Excel
contains all the features that are documented in this book so that you
can pass the exam.
How to Use This Book
Start by taking the Assessment Test after this introduction to see how
well you know Excel already. Even if you've been using Excel for a
while, 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 the
chapter to reinforce the concepts in each section. When you reach the
end of the chapter, answer each of the 10 Review Questions to test
what you learned. You can check your answers in the appendix at the
back of the book.
If you're indeed taking the exam, then there are two other valuable
tools that you can use: Flashcards and a Practice Exam. You may
remember flashcards from when you were in school, and they're useful
when you want to reinforce your knowledge. Use the Flashcards with a
friend or relative if you like. (They might appreciate learning about
Excel, too.) The Practice Exam will help you further hone your ability
to answer any question on the real exam with no worries.
How to Contact Wiley or the Author
If you believe you have found an error in this book and it is not listed
on the book's errata page, you can report the issue to our customer
technical support team at support.wiley.com.
You can email the author with your comments or questions at
eric@butow.net. You can also visit Eric's website at www.butow.net.
Loading page 22...
How This Book Is Organized
Chapter 1: Managing Worksheets and Workbooks This
chapter introduces you to importing data into workbooks,
navigating within a workbook, formatting worksheets and
workbooks so that they look the way you want, customizing Excel
options and views, saving a workbook, sharing a workbook, and
inspecting a workbook before you share it, so that all of your
recipients can read it.
Chapter 2: Using Data Cells and Ranges This chapter
follows up by showing you how to manipulate your data in a
worksheet to show the data that you want to see; formatting cells
and ranges in a worksheet using Excel tools, including Format
Painter and styles; defining and referencing cell ranges; and
summarizing your data with Sparklines and conditional
formatting.
Chapter 3: Working with Tables and Table Data This
chapter shows you how to use the built in table tools to create
tables of information, convert the table to a cell range (and vice
versa), 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 Formulas
and Functions This chapter tells you how to insert references
into a cell formula, perform calculations, count cells, execute
conditional operations, as well as format text using a variety of
built in Excel functions.
Chapter 5: Managing Charts This chapter covers how to
create charts within a worksheet and on a separate worksheet,
modify a chart to show the data you want, format a chart with
layouts and styles, and add alternative text to a chart so that
everyone who sees the chart will know what it's about.
Interactive Online Learning Environment and
Test Bank
Chapter 1: Managing Worksheets and Workbooks This
chapter introduces you to importing data into workbooks,
navigating within a workbook, formatting worksheets and
workbooks so that they look the way you want, customizing Excel
options and views, saving a workbook, sharing a workbook, and
inspecting a workbook before you share it, so that all of your
recipients can read it.
Chapter 2: Using Data Cells and Ranges This chapter
follows up by showing you how to manipulate your data in a
worksheet to show the data that you want to see; formatting cells
and ranges in a worksheet using Excel tools, including Format
Painter and styles; defining and referencing cell ranges; and
summarizing your data with Sparklines and conditional
formatting.
Chapter 3: Working with Tables and Table Data This
chapter shows you how to use the built in table tools to create
tables of information, convert the table to a cell range (and vice
versa), 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 Formulas
and Functions This chapter tells you how to insert references
into a cell formula, perform calculations, count cells, execute
conditional operations, as well as format text using a variety of
built in Excel functions.
Chapter 5: Managing Charts This chapter covers how to
create charts within a worksheet and on a separate worksheet,
modify a chart to show the data you want, format a chart with
layouts and styles, and add alternative text to a chart so that
everyone who sees the chart will know what it's about.
Interactive Online Learning Environment and
Test Bank
Loading page 23...
Learning the material in the MCA Microsoft Office Specialist (Office
365 and Office 2019) Study Guide: Excel Associate Exam MO 200 is
an important part of preparing for the Excel Exam MO 200, but we
also provide additional tools to help you prepare. The online test bank
will help you understand the types of questions that will appear on the
certification exam.
The sample tests in the test bank include all of the questions in each
chapter as well as the questions from the Assessment Test. In addition,
there is a Practice Exam containing 50 questions. You can use this test
to evaluate your understanding and identify areas that may require
additional study.
The flashcards in the test bank will push the limits of what you should
know for the certification exam. The flashcards contain 100 questions
provided in digital format. Each flashcard has one question and one
correct answer.
The online glossary is a searchable list of key terms introduced in this
Study Guide that you should know for the Excel Exam MO 200.
To start using these tools to study for the MCA Microsoft Office
Specialist Study Guide: Excel Exam MO 200, go to
www.wiley.com/go/sybextestprep and register your book to receive
your unique PIN. Once you have the PIN, return to
www.wiley.com/go/sybextestprep, find your book, and click Register, or
log in and follow the link to register a new account or to add this book
to an existing account.
Exam objectives are subject to change at any time
without prior notice and at Microsoft's sole discretion. Please visit
the Exam MO 200: Microsoft Excel (Excel and Excel 2019) website
(https://docs.microsoft.com/en-
us/learn/certifications/exams/MO-200) for the most current
listing of exam objectives.
365 and Office 2019) Study Guide: Excel Associate Exam MO 200 is
an important part of preparing for the Excel Exam MO 200, but we
also provide additional tools to help you prepare. The online test bank
will help you understand the types of questions that will appear on the
certification exam.
The sample tests in the test bank include all of the questions in each
chapter as well as the questions from the Assessment Test. In addition,
there is a Practice Exam containing 50 questions. You can use this test
to evaluate your understanding and identify areas that may require
additional study.
The flashcards in the test bank will push the limits of what you should
know for the certification exam. The flashcards contain 100 questions
provided in digital format. Each flashcard has one question and one
correct answer.
The online glossary is a searchable list of key terms introduced in this
Study Guide that you should know for the Excel Exam MO 200.
To start using these tools to study for the MCA Microsoft Office
Specialist Study Guide: Excel Exam MO 200, go to
www.wiley.com/go/sybextestprep and register your book to receive
your unique PIN. Once you have the PIN, return to
www.wiley.com/go/sybextestprep, find your book, and click Register, or
log in and follow the link to register a new account or to add this book
to an existing account.
Exam objectives are subject to change at any time
without prior notice and at Microsoft's sole discretion. Please visit
the Exam MO 200: Microsoft Excel (Excel and Excel 2019) website
(https://docs.microsoft.com/en-
us/learn/certifications/exams/MO-200) for the most current
listing of exam objectives.
Loading page 24...
Objective Map
Objective Chapter
Section 1: Manage worksheets and workbooks
1.1 Import data into workbooks 1
1.2 Navigate within workbooks 1
1.3 Format worksheets and workbooks 1, 2, 3, 4,
5
1.4 Customize options and views 1
1.5 Configure content for collaboration 1
Section 2: Manage data cells and ranges
2.1 Manipulate data in worksheets 2
2.2 Format cells and ranges 1, 2
2.3 Define and reference named changes 2
2.4 Summarize data visually 2
Section 3: Manage tables and table data
3.1 Create and format tables 3
3.2 Modify tables 1, 2, 3
3.3 Filter and sort table data 3
Section 4: Perform operations by using formulas
and functions
4.1 Insert references 4
4.2 Calculate and transform datas 4
4.3 Format and modify text 1, 2, 4
Section 5: Manage charts
5.1 Create charts 5
5.2 Modify charts 5
5.3 Format charts 5
Objective Chapter
Section 1: Manage worksheets and workbooks
1.1 Import data into workbooks 1
1.2 Navigate within workbooks 1
1.3 Format worksheets and workbooks 1, 2, 3, 4,
5
1.4 Customize options and views 1
1.5 Configure content for collaboration 1
Section 2: Manage data cells and ranges
2.1 Manipulate data in worksheets 2
2.2 Format cells and ranges 1, 2
2.3 Define and reference named changes 2
2.4 Summarize data visually 2
Section 3: Manage tables and table data
3.1 Create and format tables 3
3.2 Modify tables 1, 2, 3
3.3 Filter and sort table data 3
Section 4: Perform operations by using formulas
and functions
4.1 Insert references 4
4.2 Calculate and transform datas 4
4.3 Format and modify text 1, 2, 4
Section 5: Manage charts
5.1 Create charts 5
5.2 Modify charts 5
5.3 Format charts 5
Loading page 25...
Assessment Test
1. 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 ribbon
B. Right clicking the cell and then clicking Copy in the context
menu
C. Pressing Ctrl+C
D. Adding the Copy icon to the Quick Access Toolbar
3. What are ways in which you can identify data in a chart? (Choose
all that apply.)
A. Axes
B. Legend
C. Table
D. Titles
4. What are the three reference types that you can add in a formula?
(Choose all that apply.)
A. Relative
B. Absolute
C. Numeric
D. Mixed
5. What two types of files can you import into an Excel workbook?
(Choose all that apply.)
A. Word
1. 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 ribbon
B. Right clicking the cell and then clicking Copy in the context
menu
C. Pressing Ctrl+C
D. Adding the Copy icon to the Quick Access Toolbar
3. What are ways in which you can identify data in a chart? (Choose
all that apply.)
A. Axes
B. Legend
C. Table
D. Titles
4. What are the three reference types that you can add in a formula?
(Choose all that apply.)
A. Relative
B. Absolute
C. Numeric
D. Mixed
5. What two types of files can you import into an Excel workbook?
(Choose all that apply.)
A. Word
Loading page 26...
B. Text
C. CSV
D. PowerPoint
6. How can you select a chart element in your chart? (Choose all that
apply.)
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 itself
change its orientation?
A. Rotate Text Up
B. Align Center
C. Vertical Text
D. Rotate Text Down
8. What does the SUM() function do?
A. Adds all of the numbers in selected cells
B. Summarizes the numbers in selected cells
C. Calculates the average of all numbers in selected cells
D. Counts all of the selected cells that have numbers in them
9. What drop down list box do you select when you want to find
information in an entire workbook?
A. Search
B. Look In
C. Within
D. Find What
10. What function do you use in a new cell to have Excel return the
C. CSV
D. PowerPoint
6. How can you select a chart element in your chart? (Choose all that
apply.)
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 itself
change its orientation?
A. Rotate Text Up
B. Align Center
C. Vertical Text
D. Rotate Text Down
8. What does the SUM() function do?
A. Adds all of the numbers in selected cells
B. Summarizes the numbers in selected cells
C. Calculates the average of all numbers in selected cells
D. Counts all of the selected cells that have numbers in them
9. What drop down list box do you select when you want to find
information in an entire workbook?
A. Search
B. Look In
C. Within
D. Find What
10. What function do you use in a new cell to have Excel return the
Loading page 27...
first 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? (Choose
all that apply.)
A. Header row
B. First column
C. Filter button
D. Banded rows
12. Why would you assign a name to a range of cells?
A. You don't have to because cells automatically have row
numbers 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 points
B. 15 points
C. One inch
D. 72 points
14. How does the COUNTA() function differ from the COUNT() function?
A. COUNTA() counts all the blank cells in a selected range.
B. You can count specific numbers in the COUNTA() function.
C. COUNTA()allows you to only count text, not numbers.
D. The COUNTA() function counts selected cells that are not
A. MID()
B. UPPER()
C. LEN()
D. LEFT()
11. What information can you show and hide in a table style? (Choose
all that apply.)
A. Header row
B. First column
C. Filter button
D. Banded rows
12. Why would you assign a name to a range of cells?
A. You don't have to because cells automatically have row
numbers 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 points
B. 15 points
C. One inch
D. 72 points
14. How does the COUNTA() function differ from the COUNT() function?
A. COUNTA() counts all the blank cells in a selected range.
B. You can count specific numbers in the COUNTA() function.
C. COUNTA()allows you to only count text, not numbers.
D. The COUNTA() function counts selected cells that are not
Loading page 28...
empty.
15. What does Excel call a chart created in a new worksheet?
A. Chart sheet
B. Chart1
C. Whatever you decide the new worksheet should be
D. Excel creates a new workbook and then you must give it a
name.
16. When you need to sort table data with text and numbers in it,
what is the best way to sort?
A. In ascending order
B. Using the sort and filter buttons in the header row
C. A custom sort
D. In descending order
17. What is a Sparkline chart?
A. A chart format that lets you add graphic sparkles to your
chart
B. A small chart that quickly summarizes what you see in a row
C. One of the built in chart styles
D. Another term for a win loss chart
18. When would you use the TEXTJOIN() function instead of the
CONCAT() function?
A. When you want to ignore blank cells in the selected range
B. You don't have to because TEXTJOIN() replaces CONCAT() in the
latest version of Excel.
C. To add a space between text in each cell
D. When you don't want to type in the cell references within the
formula
19. What does an error bar in a chart show? (Choose all that apply.)
15. What does Excel call a chart created in a new worksheet?
A. Chart sheet
B. Chart1
C. Whatever you decide the new worksheet should be
D. Excel creates a new workbook and then you must give it a
name.
16. When you need to sort table data with text and numbers in it,
what is the best way to sort?
A. In ascending order
B. Using the sort and filter buttons in the header row
C. A custom sort
D. In descending order
17. What is a Sparkline chart?
A. A chart format that lets you add graphic sparkles to your
chart
B. A small chart that quickly summarizes what you see in a row
C. One of the built in chart styles
D. Another term for a win loss chart
18. When would you use the TEXTJOIN() function instead of the
CONCAT() function?
A. When you want to ignore blank cells in the selected range
B. You don't have to because TEXTJOIN() replaces CONCAT() in the
latest version of Excel.
C. To add a space between text in each cell
D. When you don't want to type in the cell references within the
formula
19. What does an error bar in a chart show? (Choose all that apply.)
Loading page 29...
A. How inaccurate the data is in the chart
B. Margins of error
C. Standard deviation
D. How much you can change the numeric value in a cell
formula
20. When you format a table style, what formats can you change?
(Choose all that apply.)
A. Font
B. Alignment
C. Border
D. Fill color and pattern
21. Your boss likes your chart but wants the background of the chart
to be dark so that the text will stand out. How do you do this
quickly?
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 than
white.
22. What are the minimum and maximum magnification views in a
worksheet?
A. 20 percent and 125 percent
B. 10 percent and 150 percent
C. 25 percent and 200 percent
D. 5 percent and 300 percent
23. If you delete a row or column and immediately decide that you
didn't want to do that, what do you do?
A. Nothing
B. Margins of error
C. Standard deviation
D. How much you can change the numeric value in a cell
formula
20. When you format a table style, what formats can you change?
(Choose all that apply.)
A. Font
B. Alignment
C. Border
D. Fill color and pattern
21. Your boss likes your chart but wants the background of the chart
to be dark so that the text will stand out. How do you do this
quickly?
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 than
white.
22. What are the minimum and maximum magnification views in a
worksheet?
A. 20 percent and 125 percent
B. 10 percent and 150 percent
C. 25 percent and 200 percent
D. 5 percent and 300 percent
23. If you delete a row or column and immediately decide that you
didn't want to do that, what do you do?
A. Nothing
Loading page 30...
B. 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 paste
a number from one cell into a blank cell?
A. Formats
B. Values
C. None
D. Validation
25. What file formats can you save an Excel file to? (Choose all that
apply.)
A. XML
B. Word
C. PDF
D. Excel
Answers to Assessment Test
1. B, D. Each cell in the header row contains a button that allows you
to sort and filter data in the column. See Chapter 3 for more
information.
2. C. Press Ctrl+C to copy all the information in one cell into an
empty cell. See Chapter 2 for more information.
3. B, C. You can add a legend, a data table, as well as data labels to a
chart to help you and others understand what the chart
represents. See Chapter 5 for more information.
4. A, B, D. Excel can create relative, absolute, and mixed reference
types in a cell formula. See Chapter 4 for more information.
5. B, C. You can import files with the TXT and CSV formats into an
Excel workbook. See Chapter 1 for more information.
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 paste
a number from one cell into a blank cell?
A. Formats
B. Values
C. None
D. Validation
25. What file formats can you save an Excel file to? (Choose all that
apply.)
A. XML
B. Word
C. PDF
D. Excel
Answers to Assessment Test
1. B, D. Each cell in the header row contains a button that allows you
to sort and filter data in the column. See Chapter 3 for more
information.
2. C. Press Ctrl+C to copy all the information in one cell into an
empty cell. See Chapter 2 for more information.
3. B, C. You can add a legend, a data table, as well as data labels to a
chart to help you and others understand what the chart
represents. See Chapter 5 for more information.
4. A, B, D. Excel can create relative, absolute, and mixed reference
types in a cell formula. See Chapter 4 for more information.
5. B, C. You can import files with the TXT and CSV formats into an
Excel workbook. See Chapter 1 for more information.
Loading page 31...
28 more pages available. Scroll down to load them.
Preview Mode
Sign in to access the full document!
100%
Study Now!
XY-Copilot AI
Unlimited Access
Secure Payment
Instant Access
24/7 Support
AI Assistant
Document Details
Subject
Microsoft Certified Associate