For Microsoft Excel Expert

MOS 2016 Study Guide for Microsoft Excel Expert

Paul McFedries

Microsoft Office Specialist Exam 77-728

MOS 2016 Study Guide for Microsoft Excel Expert

Published with the authorization of Microsoft Corporation by: Pearson Education, Inc.

Copyright ? 2017 by Pearson Education, Inc.

All rights reserved. Printed in the United States of America. This publication is protected by copyright, and permission must be obtained from the publisher prior to any prohibited reproduction, storage in a retrieval system, or transmission in any form or by any means, electronic, mechanical, photocopying, recording, or likewise. For information regarding permissions, request forms, and the appropriate contacts within the Pearson Education Global Rights & Permissions Department, please visit . No patent liability is assumed with respect to the use of the information contained herein. Although every precaution has been taken in the preparation of this book, the publisher and author assume no responsibility for errors or omissions. Nor is any liability assumed for damages resulting from the use of the information contained herein.

ISBN-13: 978-0-7356-9942-7 ISBN-10: 0-7356-9942-7

Library of Congress Control Number: 2016953074

First Printing October 2016

Microsoft and the trademarks listed at on the "Trademarks" webpage are trademarks of the Microsoft group of companies. All other marks are property of their respective owners.

Every effort has been made to make this book as complete and as accurate as possible, but no warranty or fitness is implied. The information provided is on an "as is" basis. The author, the publisher, and Microsoft Corporation shall have neither liability nor responsibility to any person or entity with respect to any loss or damages arising from the information contained in this book or from the use of the practice files accompanying it.

For information about buying this title in bulk quantities, or for special sales opportunities (which may include electronic versions; custom cover designs; and content particular to your business, training goals, marketing focus, or branding interests), please contact our corporate sales department at corpsales@ or (800) 382-3419.

For government sales inquiries, please contact governmentsales@.

For questions about sales outside the U.S., please contact intlcs@.

Editor-in-Chief Greg Wiegand

Senior Acquisitions Editor Laura Norman

Senior Production Editor Tracey Croom

Editorial Production Online Training Solutions, Inc. (OTSI)

Series Project Editor/ Copy Editor Kathy Krause (OTSI)

Technical Editor Joan Lambert (OTSI)

Compositor/Indexer Susie Carr (OTSI)

Proofreader Jaime Odell (OTSI)

Editorial Assistant Cindy J. Teeters

Interior Designer Joan Lambert (OTSI)

Cover Designer Twist Creative ? Seattle

Contents

Introduction

vii

Taking a Microsoft Office Specialist exam

xi

Exam 77-728 Excel 2016 Expert: Interpreting Data for Insights 1 Prerequisites. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2

1

Manage workbook options and settings

3

Objective 1.1: Manage workbooks. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 4

Save a workbook as a template

4

Hide or display ribbon tabs

5

Enable macros in a workbook

6

Copy macros between workbooks

8

Reference data in another workbook

10

Reference table data by using structured references

12

Objective 1.1 practice tasks. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 15

Objective 1.2: Manage workbook review . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 17

Restrict editing

17

Protect workbook structure

21

Encrypt a workbook with a password

22

Manage workbook versions

23

Configure formula calculation options

25

Objective 1.2 practice tasks. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 29

What do you think of this book? We want to hear from you!

Microsoft is interested in hearing your feedback so we can improve our books and learning resources for you. To participate in a brief survey, please visit:



iii

Contents

2

Apply custom data formats and layouts

31

Objective 2.1: Apply custom data formats and validation. . . . . . . . . . . . . . . . 32

Create custom data formats

32

Populate cells by using advanced Fill Series options

36

Configure data validation

38

Objective 2.1 practice tasks. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 43

Objective 2.2: Apply advanced conditional formatting and filtering. . . . . . 45

Create custom conditional formatting rules

45

Create conditional formatting rules that use formulas

48

Manage conditional formatting rules

49

Objective 2.2 practice tasks. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .51

Objective 2.3: Create and modify custom workbook elements. . . . . . . . . . . 53

Create and modify cell styles

53

Create custom themes and theme elements

55

Create and modify simple macros

61

Insert and configure form controls

64

Objective 2.3 practice tasks. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 67

Objective 2.4: Prepare a workbook for internationalization. . . . . . . . . . . . . . 68

Objective 2.4 practice tasks . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 70

3

Create advanced formulas

71

Objective 3.1: Apply functions in formulas. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 72

Insert functions into a formula

72

Perform logical operations by using the

IF, AND, OR, and NOT functions

72

Perform logical operations by using nested functions

76

Perform statistical operations by using the

SUMIFS, AVERAGEIFS, and COUNTIFS functions

77

Objective 3.1 practice tasks. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 81

iv

Contents

Objective 3.2: Look up data by using functions. . . . . . . . . . . . . . . . . . . . . . . . . 82

Objective 3.2 practice tasks. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 88

Objective 3.3: Apply advanced date and time functions. . . . . . . . . . . . . . . . . 89

Reference the date and time by using the

NOW and TODAY functions

89

Serialize numbers by using date and time functions

91

Objective 3.3 practice tasks. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 96

Objective 3.4: Perform data analysis and business intelligence. . . . . . . . . . . 97

Import, transform, combine, display, and connect to data 97

Consolidate data

103

Perform what-if analysis by using Goal Seek and

Scenario Manager

107

Use cube functions to get data out of the Excel data model 112

Calculate data by using financial functions

116

Objective 3.4 practice tasks . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 120

Objective 3.5: Troubleshoot formulas . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 121

Trace precedence and dependence

121

Monitor cells and formulas by using the Watch Window 122

Validate formulas by using error-checking rules

124

Evaluate formulas

127

Objective 3.5 practice tasks. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 128

Objective 3.6: Define named ranges and objects. . . . . . . . . . . . . . . . . . . . . . . 129

Name a cell or range

130

Name a table

132

Manage named ranges and objects

133

Objective 3.6 practice tasks. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 134

v

................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download