book

MOS 2016 Study Guide for Microsoft Excel Expert

Name: MOS 2016 Study Guide for Microsoft Excel Expert
Author: Paul McFedries
ISBN: 9781509300068

by Paul McFedries

November 2016

Intermediate to advanced

192 pages

4h 26m

English

Microsoft Press

Read now

Unlock full access

Cover
Title Page
Copyright Page
Contents
Introduction
Who this book is forHow this book is organizedDownload the practice filesEbook editionErrata, updates, and book supportWe want to hear from youStay in touch
Taking a Microsoft Office Specialist exam
Microsoft Office Specialist certificationSelecting a certification pathTest-taking tipsCertification benefitsFor more information
Exam 77-728. Excel 2016 Expert: Interpreting Data for Insights
Prerequisites
Objective group 1. Manage workbook options and settings
Objective 1.1: Manage workbooksSave a workbook as a templateHide or display ribbon tabsEnable macros in a workbookCopy macros between workbooksReference data in another workbookReference table data by using structured referencesObjective 1.1 practice tasksObjective 1.2: Manage workbook reviewRestrict editingProtect workbook structureEncrypt a workbook with a passwordManage workbook versionsConfigure formula calculation optionsObjective 1.2 practice tasks
Objective group 2. Apply custom data formats and layouts
Objective 2.1: Apply custom data formats and validationCreate custom data formatsPopulate cells by using advanced Fill Series optionsConfigure data validationObjective 2.1 practice tasksObjective 2.2: Apply advanced conditional formatting and filteringCreate custom conditional formatting rulesCreate conditional formatting rules that use formulasManage conditional formatting rulesObjective 2.2 practice tasksObjective 2.3: Create and modify custom workbook elementsCreate and modify cell stylesCreate custom themes and theme elementsCreate and modify simple macrosInsert and configure form controlsObjective 2.3 practice tasksObjective 2.4: Prepare a workbook for internationalizationObjective 2.4 practice tasks
Objective group 3. Create advanced formulas
Objective 3.1: Apply functions in formulasInsert functions into a formulaPerform logical operations by using the IF, AND, OR, and NOT functionsPerform logical operations by using nested functionsPerform statistical operations by using the SUMIFS, AVERAGEIFS, and COUNTIFS functionsObjective 3.1 practice tasksObjective 3.2: Look up data by using functionsObjective 3.2 practice tasksObjective 3.3: Apply advanced date and time functionsReference the date and time by using the NOW and TODAY functionsSerialize numbers by using date and time functionsObjective 3.3 practice tasksObjective 3.4: Perform data analysis and business intelligenceImport, transform, combine, display, and connect to dataConsolidate dataPerform what-if analysis by using Goal Seek and Scenario ManagerUse cube functions to get data out of the Excel data modelCalculate data by using financial functionsObjective 3.4 practice tasksObjective 3.5: Troubleshoot formulasTrace precedence and dependenceMonitor cells and formulas by using the Watch WindowValidate formulas by using error-checking rulesEvaluate formulasObjective 3.5 practice tasksObjective 3.6: Define named ranges and objectsName a cell or rangeName a tableManage named ranges and objectsObjective 3.6 practice tasks

Objective group 4. Create advanced charts and tables
Objective 4.1: Create advanced chartsAdd trendlines to chartsCreate dual-axis chartsSave a chart as a templateObjective 4.1 practice tasksObjective 4.2: Create and manage PivotTablesCreate PivotTablesModify PivotTable field selections and optionsCreate slicersGroup PivotTable dataReference data in a PivotTable by using the GETPIVOTDATA functionAdd calculated fieldsFormat dataObjective 4.2 practice tasksObjective 4.3: Create and manage PivotChartsCreate PivotChartsModify PivotChartsDrill down into PivotChart detailsObjective 4.3 practice tasks
Index
About the author
Survey

Overview

Hone your advanced Excel 2016 skills. And earn the credential that proves it!

Demonstrate your expertise with Microsoft Excel! Designed to help you practice and prepare for Microsoft Office Specialist (MOS): Excel Expert 2016 certification, this official Study Guide delivers:

In-depth preparation for each MOS objective

Detailed procedures to help build the skills measured by the exam

Hands-on tasks to practice what you’ve learned

Ready-made practice files

Sharpen the skills measured by these objectives:

Create and Manage Workbooks

Apply Custom Formatting and Layouts

Create Advanced Formulas

Perform Data Analysis

Create Advanced Charts and PivotTables

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.

Read now

Unlock full access

More than 5,000 organizations count on O’Reilly

O’Reilly covers everything we've got, with content to help us build a world-class technology community, upgrade the capabilities and competencies of our teams, and improve overall team performance as well as their engagement.

Julian F.

Head of Cybersecurity

I wanted to learn C and C++, but it didn't click for me until I picked up an O'Reilly book. When I went on the O’Reilly platform, I was astonished to find all the books there, plus live events and sandboxes so you could play around with the technology.

Addison B.

Field Engineer

I’ve been on the O’Reilly platform for more than eight years. I use a couple of learning platforms, but I'm on O'Reilly more than anybody else. When you're there, you start learning. I'm never disappointed.

Amir M.

Data Platform Tech Lead

I'm always learning. So when I got on to O'Reilly, I was like a kid in a candy store. There are playlists. There are answers. There's on-demand training. It's worth its weight in gold, in terms of what it allows me to do.

Mark W.

Embedded Software Engineer

MOS 2016 Study Guide for Microsoft Excel

Publisher Resources

ISBN: 9781509300068Purchase book

Cloud Computing

Data Engineering

Data Science

AI & ML

Programming Languages

Software Architecture

IT/Ops

Security

Design

Business

Soft Skills