MOS 2013 Study Guide for Microsoft® Excel® Expert by Dodge, Mark -- Read -- Imperial Library of Trantor

Index

MOS 2013 Study Guide for Microsoft® Excel® Expert A Note Regarding Supplemental Files Introduction

Who this book is for How this book is organized Download the practice files Ebook edition Get support and give feedback

Errata We want to hear from you Stay in touch

Taking a Microsoft Office Specialist Exam

Microsoft Office Specialist certification Selecting a certification path Test-taking tips Certification benefits For more information

I. Exams 77-427 and 77-428: Microsoft Excel 2013 Expert

1. Manage and Share Workbooks

1.1 Manage multiple workbooks

Modifying workbook templates Managing workbook versions Copying styles between templates Copying macros between workbooks Connecting to external data

1.2 Prepare workbooks for review

Tracking changes Protecting workbooks for sharing

Restricting editing and limiting editors Protecting worksheet structure Removing workbook metadata Controlling calculation Encrypting workbooks by using a password Marking as final

1.3 Manage workbook changes

Displaying all changes Reviewing changes Managing comments Merging workbooks Identifying errors Troubleshooting by using tracing

Objective review

2. Apply Custom Formats and Layouts

2.1 Apply custom data formats

Creating custom formats (number, time, date)

Creating custom accounting formats Creating custom date formats Creating custom time formats

Using advanced Fill Series options

Creating custom fill sequences

2.2 Apply advanced conditional formatting and filtering

Creating custom conditional formats Using functions to format cells Creating advanced filters

2.3 Apply custom styles and templates

Creating custom templates Creating and modifying cell styles Creating custom color and font formats Creating themes Creating form fields

Inserting form controls

2.4 Prepare workbooks for internationalization and accessibility

Modifying worksheets for use with accessibility tools Displaying data in multiple international formats

Working in other languages Utilizing international symbols

Objective review

3. Create Advanced Formulas

3.1 Apply functions in formulas

Using nested functions Using the IF, AND, and OR functions Using the SUMIFS, AVERAGEIFS, and COUNTIFS functions Using financial functions

Calculating investments Calculating depreciation Analyzing securities

3.2 Look up data by using functions

Using the VLOOKUP and HLOOKUP functions Using the LOOKUP function Using the TRANSPOSE function

3.3 Apply advanced date and time functions

Using the NOW and TODAY functions Using functions to serialize dates and times

Using the YEAR, MONTH, DAY, HOUR, MINUTE, and SECOND functions Using the DATEVALUE and TIMEVALUE functions Using the WEEKDAY, WORKDAY, and NETWORKDAYS functions

3.4 Create scenarios

Using what-if analysis tools

Using Goal Seek Using Solver

Using the Scenario Manager Consolidating data

Consolidating by position Consolidating by category

Objective review

4. Create Advanced Charts and Tables

4.1 Create advanced chart elements

Adding trendlines to charts Creating dual-axis charts Creating custom chart templates

4.2 Create and manage PivotTables

Creating PivotTables

Formatting PivotTables

Modifying field selections and options

Using field headers Changing your view of the data Using calculated fields and items

Creating slicers Using PowerPivot

Managing relationships Creating hierarchies

4.3 Create and manage PivotCharts

Creating PivotCharts Manipulating options in existing PivotCharts Applying styles to PivotCharts

Objective review

Index About the Author Copyright

← Prev
Back
Next →

← Prev
Back
Next →