4.2 Create and Manage Pivot Tables | 77-728 Complete Techniques for the MOS Excel Expert Exam

Book Overview & Buying
Table Of Contents

77-728 Complete Techniques for the MOS Excel Expert Exam

By : Dave Murphy

Buy this Video

77-728 Complete Techniques for the MOS Excel Expert Exam

By: Dave Murphy

Buy this Video

Overview of this book

This course will prepare you to take the 77-728 Excel Expert certification exam from Microsoft. Every aspect of the exam is covered in detail, together with exercises and solutions. This course is delivered by a University professor who has many years' experience in teaching Excel to undergraduate students and is also a Microsoft Office Specialist examiner. If you take this course and follow the guidelines provided, you will enhance your chances of obtaining this valuable qualification. Prove your skills by obtaining independent certification from Microsoft. All the code files are placed at https://github.com/PacktPublishing/77-728-Complete-Techniques-for-the-MOS-Excel-Expert-Exam

Introduction

Understanding the Objective Domain

How is the exam be conducted and what will it be like?

1.1 Manage Workbooks

1.1.1 save a Workbook as a Template

1.1.2 Copy Macros between Workbooks

1.1.3 Reference Data in another Workbook

1.1.4 Reference Data by using Structured References

1.1.6 Display Hidden Ribbon Tabs

1.2 Manage Workbook Review

1.2.1 Restrict Editing

1.2.2 Protect a Worksheet

1.2.3 Configure Formula Calculation Option

1.2.4 Protect Workbook Structure

1.2.5 Manage Workbook Versions

1.2.6 Encrypt a Workbook with a Password

Objective Domain 1 - Exercises & Solutions

Domain Objective 1: Manage Workbook Options and Settings - Exercises & Solutions

2.1 Apply Custom Data Formats and Validation

2.1.1 (Part I) Create Custom Number Formats

2.1.1 (Part II) Create Custom Number Formats

2.1.2 Populate Cells by using Advanced Fill Series Options

2.1.2 (Part II) Issues with AutoFill using Dates

2.1.3 Configure Data Validation

2.2 Apply Advanced Conditional Formatting and Filtering

2.2.1 Create Custom Conditional Formatting Rules

2.2.2 Create Conditional Formatting Rules that use Formulas

2.2.3 Manage Conditional Formatting Rules

2.3 Create and Modify Custom Workbook Elements

2.3.1 Create Custom Colour Formats

2.3.2 Create and Modify Cell Styles

2.3.3 Create and Modify Custom Themes

2.3.4 (Part I) Create and Modify Simple Macros

2.3.4 (Part II) Create and Modify Simple Macros

2.3.5 (Part I) Insert and Configure Form Controls

2.3.5 (Part II) Insert and Configure Form Controls

2.3.5 (Part III) Insert and Configure Form Controls

2.4 Prepare a Workbook for Internationalization

2.4.1 and 2.4.2 Display Data in Multiple International Formats

2.4.3 Manage Multiple Options for +Body and +Heading Fonts

Domain Objective 2 - Exercises & Solutions

Domain Objective 2 Apply Custom Data Formats and Layouts - Exercises & Solutions

3.1 Apply Functions in Formulas

3.1.1 Perform Logical Operations by using AND, OR, and NOT Functions

3.1.2 Perform Logical Operations by using Nested Functions

3.1.3 Perform Statistical Operations by using SUMIFS, AVERAGEIFS, and COUNTIFS

3.2 Look up Data by using Functions

3.2.1 and 3.2.2 Look Up Data by using the VLOOKUP & HLOOKUP Functions

3.2.3 Look Up Data by using the INDEX and MATCH Functions

3.3 Apply Advanced Date and Time Functions

3.3.1 & 2 NOW, TODAY Functions and Serialise Numbers

3.4 Perform Data Analysis and Business Intelligence

Introduction to Domain Objective 3.4

3.4.1 (Part I) Connecting to Data with Power Pivot

3.4.1 (Part II) More on Power Pivot

3.4.1 (Part III) Measures

3.4.1 (Part IV) Get and Transform

3.4.2 Consolidate Data

3.4.3 Perform What-if Analysis by using Goal Seek and Scenario Manager

3.4.4 Cube Functions Part I

3.4.4 Cube Functions Part II

3.4.4 Cube Functions Part III

3.4.5 Calculate Data by using Financial Functions

3.5 Troubleshoot Formulas

3.5.1 Trace Precedence and Dependence

3.5.2 Monitor Cells and Formulas by using the Watch Window

3.5.3 Validate Formulas by using Error Checking Rules

3.5.4 Evaluate Formulas

3.6 Define Named Ranges and Objects

3.6.1 and 3.6.2 Name Cells and Data Ranges

3.6.3 Name Tables

3.6.4 Manage Named Ranges and Objects

Domain Objective 3 - Exercises & Solutions

Domain Objective 3 Create Advanced Formulas - Exercises & Solutions

4.1 Create Advanced Charts

4.1.1 Add Trendlines to Charts

4.1.2 Create Dual-axis Charts

4.1.3 Save a Chart as a Template

4.2 Create and Manage Pivot Tables

4.2.1 Create Pivot Tables

4.2.2 Modify Field Selections and Options

4.2.3 Create Slicers

4.2.4 Group Pivot Table Data

4.2.5 Reference Data in a Pivot Table by using the GETPIVOTDATA Function

4.2.6 Add Calculated Fields

4.2.7 Format Data

4.3 Create and Manage Pivot Charts

4.3.1 Create PivotCharts

4.3.2 Manipulate Options in Existing PivotCharts

4.3.3 Apply Styles to PivotCharts

4.3.4 Drill Down into PivotChart Details

Domain Objective 4 - Exercises & Solutions

Domain Objective 4 Create Advanced Charts and Tables - Exercises & Solutions

CUBE Function Questions

77-728 Complete Techniques for the MOS Excel Expert Exam

By : Dave Murphy

77-728 Complete Techniques for the MOS Excel Expert Exam

By: Dave Murphy

Overview of this book

Confirmation

Buy this book with your credits?

Submit Your Feedback

Create a Free Account To Continue Reading

Sign in to activate your 7-day free access