Exam 77-420 Microsoft Excel 2013 / Edition 1

by Microsoft Official Academic Course

Overview

This Microsoft Excel 2013, Exam 77-420 book is the only Microsoft Official Academic Course (MOAC) textbook. This series includes a complete classroom instructional program. This Excel 2013 text is mapped to the Excel 2013 certification exam objectives and is designed to re-enforce workforce skills. With this book students learn to create and edit professional-looking spreadsheets for a variety of purposes and situations. It also covers such skills as charting, creating analytical, financial reports, data entry, developing budgets, formatting numerical (financial, statistical, etc.) reports, creating forms, graphing, processing data, reporting, technical support, trending and much more. The Microsoft Official Academic Course (MOAC) Office series also offers OfficeGrader. This valuable tool corrects your students tasked-based assignments. Students work on real-world problems like the ones they will encounter in the workforce ensuring they are ready for real professional challenges. OfficeGrader allows for efficient and consistent grading saving time for other important teaching activities. Grading is easier than ever allowing faster assignment turnaround to students. Excel 2013 certification can help students with classwork and differentiate job hunters in todays competitive job market. Students who have earned certification can broaden their employment opportunities in such fields as accounting, office administration, consulting, as executives and managers, help desk personnel, instructors/trainers, program/project managers, and sales careers.

Product Details

ISBN-13:	9780470133088
Publisher:	Wiley
Publication date:	08/05/2013
Series:	Microsoft Official Academic Course Series , #376
Pages:	512
Product dimensions:	9.00(w) x 10.90(h) x 0.70(d)

About the Author

The Microsoft Official Academic Course series is a complete program for instructors and institutions to prepare and deliver great courses on Microsoft software technologies. With MOAC, we recognize that, because of the rapid pace of change in the technology and curriculum developed by Microsoft, there is an ongoing set of needs beyond classroom instruction tools for an instructor to be ready to teach the course. MOAC endeavors to provide solutions for all these needs in a systematic manner in order to ensure a successful and rewarding course experience for both instructor and student—technical and curriculum training for instructor readiness with new software releases, the software itself for student use at home for building hands-on skills, assessment and validation of skill development, and a great set of tools for delivering instruction in the classroom and lab. All are important to the smooth delivery of an interesting course on Microsoft software, and all are provided with the Microsoft Official Academic Course program.

Table of Contents

Lesson Skill Matrix

Key Terms

Software Orientation

Starting Excel

Opening Excel

Working in the Excel Window

Using Onscreen Tools

Navigating the Ribbon

Introducing Office Backstage

Using the Microsoft Office FILE Tab and Backstage View

Changing Excel’s View

Change Excel’s View

Splitting a Window

Workplace Ready: Large Workbooks

Opening a New Window

Working with an Existing Workbook

Opening an Existing Workbook

Opening a Workbook from Your SkyDrive

Navigating a Worksheet

Navigating Data with the Go To Command

Working with Excel’s Help System

Using the Help System

Summary Skill Matrix

Assessment

Knowledge Assessment

Competency Assessment

Proficiency Assessment

Mastery Assessment

Lesson Skill Matrix

Key Terms

Creating Workbooks

Creating a Workbook from Scratch

Switching Between Open Workbooks

Saving Workbooks

Naming and Saving a Workbook

Workplace Ready: Organization File Conventions

Saving to Your Skydrive

Saving a Workbook Under a Different Name

Saving a Workbook in a Previous Excel Format

Saving in Different File Formats

Entering and Editing Basic Data in a Worksheet

Entering Basic Data in a Worksheet

Changing Column Width

Editing a Cell's Contents

Deleting and Clearing a Cell's Contents

Using Data Types to Populate a Worksheet

Entering Labels and Using AutoComplete

Entering Numeric Values

Entering Dates

Filling a Series with Auto Fill

Filling Cells with Flash Fill

Cutting, Copying, and Pasting Data

Copying a Data Series with the Mouse

Moving a Data Series with the Mouse

Copying and Pasting Data

Cutting and Pasting Data

Editing a Workbook's Properties

Assigning Keywords

 Summary Skill Matrix

Assessment

Knowledge Assessment

Competency Assessment

Proficiency Assessment

Mastery Assessment 

Lesson Skill Matrix

Key Terms

Software Orientation

Accessing and Using Backstage view

Printing With Backstage

Printing and Previewing a Document

Using Quick Print to Print a Worksheet

Setting the Print Area

Printing Selected Worksheets

Printing Selected Workbooks

Applying Printing Options

Changing a Printer

Changing the Excel Environment

Customizing the Quick Access Toolbar

Customizing the Ribbon

Customizing the Excel Default Settings

Resetting Default Settings, Ribbon, and Quick Access Toolbar

Accessing and Using Excel Templates

Selecting a Template from the New Tab

Searching for Additional Templates

Workplace Ready: Job Interview Hint

Summary Skill Matrix

Assessment

Knowledge Assessment

Competency Assessment

Proficiency Assessment

Mastery Assessment

Lesson Skill Matrix

Key Terms

Software Orientation

Understanding and Displaying Formulas

Understanding Order of Operations

Building Basic Formulas

Creating a Formula that Performs Addition

Creating a Formula that Performs Subtraction

Creating a Formula that Performs Multiplication

Creating a Formula that Performs Division

Using Cell References in Formulas

Using Relative Cell References in a Formula

Using Absolute Cell References in a Formula

Workplace Ready: Absolute Cell References for Payroll Tracking

Using Mixed Cell References in a Formula

Using External Cell References

Using Cell Ranges in Formulas

Naming a Range

Changing the Size of a Range

Creating a Formula that Operates on a Named Range

Keeping Track of Named Ranges

Assessment

Knowledge Assessment

Competency Assessment

Proficiency Assessment

Mastery Assessment

Lesson Skill Matrix

Key Terms

Software Orientation

Exploring Functions

Displaying Dates and Times with Functions

Exploring Dates

Using TODAY

Using NOW

Workplace Ready: Using Dates at Work

Using the SUM Function

Using the COUNT Function

Using the COUNTA Function

Using the AVERAGE Function

Using the MIN Function

Using the MAX Function

Using a Financial Function

Use PMT

Using Formulas to Create Subtotals

Selecting and Creating Ranges for Subtotaling

Building Formulas for Subtotaling

Modifying Ranges for Subtotaling

Uncovering Formula Errors

Reviewing Error Messages

Tracing and Removing Trace Arrows

Displaying and Printing Formulas

Assessment

Knowledge Assessment

Competency Assessment

Proficiency Assessment

Mastery Assessment

Summary Skill Matrix

Assessment

Knowledge Assessment

Competency Assessment

Proficiency Assessment

Mastery Assessment

Lesson Skill Matrix

Key Terms

Software Orientation

Working with Rows and Columns

Inserting or Deleting a Row or Column

Modifying Row Height and Column Width

Formatting an Entire Row or Column

Hiding or Unhiding a Row or Column

Transposing Rows or Columns

Using Themes

Choosing a Theme for a Workbook

Customizing a Theme

Customizing a Theme by Selecting Fonts and Effects

 Workplace Ready: Putting Themes to Work

Modifying a Worksheet’s Onscreen and Printed Appearance

Formatting a Worksheet Background

Viewing and Printing a Worksheet’s Gridlines

Viewing and Printing Column and Row Headings

Inserting Headers and Footers

Adding Page Numbers to a Worksheet

Inserting a Predefined Header or Footer

Adding Content to a Header or Footer

Inserting a Watermark

Repeating Headers and Footers

Preparing a Document for Printing

Adding and Moving a Page Break

Setting Margins

Setting a Worksheet’s Orientation

Scaling a Worksheet to Fit on a Printed Page

 Summary Skill Matrix

Assessment

Knowledge Assessment

Competency Assessment

Proficiency Assessment

Mastery Assessment

Lesson Skill Matrix

Key Terms

Software Orientation

Organizing Worksheets

Copying a Worksheet

Renaming a Worksheet

Repositioning the Worksheets in a Workbook

Changing the Color of a Worksheet Tab

Hiding and Unhiding Worksheets

Inserting a New Worksheet into a Workbook

Deleting a Worksheet from a Workbook

Working with Multiple Worksheets

Working with Multiple Worksheets in a Workbook

Hiding and Unhiding Worksheet Windows in a Workbook

Workplace Ready: Ideas for Arranging Worksheets in Excel

Using Zoom and Freeze to Change the Onscreen View

Finding and Replacing Data

Locating Data with the Find Command

Replacing Data with the Replace Command

 Assessment

Knowledge Assessment

Competency Assessment

Proficiency Assessment

Mastery Assessment

Lesson Skill Matrix

Key Terms

Software Orientation

Importing Data

Opening Non-Native Files Directly in Excel

Getting External Data

Appending Data to a Worksheet

Workplace Ready: Working with Databases

Ensuring Your Data’s Integrity

Restricting Cell Entries to Certain Data Types

Allowing Only Specific Values to Be Entered in Cells

Removing Duplicate Rows from a Worksheet

Sorting Data

Sorting Data on a Single Criterion

Sorting Data on Multiple Criteria

Sorting Data Using Cell Attributes

Filtering Data

Using AutoFilter

Creating a Custom AutoFilter

Filtering Data Using Cell Attributes

Outlining and Subtotaling Data

Grouping and Ungrouping Data

Auto-Outlining Data

 Collapsing Groups of Data in an Outline

Subtotaling Data in Outlines

Setting up Data in a Table Format

Formatting a Table with a Quick Style

Removing Styles from a Table

Defining a Title for a Table

Using the Total Row Command in a Table

Adding and Removing Rows or Columns in a Table

Filtering Records in a Table

Sorting Data on Multiple Columns in a Table

Changing Sort Order in a Table

Removing Duplicates in a Table

Using a Slicer to View Table Data

Converting a Table into a Range

Saving Work with Macros

Recording a Basic Macro

Workplace Ready: Planning to Record a Macro

 Running a Macro

Managing Macro Security

Assessment

Knowledge Assessment

Competency Assessment

Proficiency Assessment

Mastery Assessment

Circling Back 2

Lesson Skill Matrix

Key Terms

Software Orientation

Workplace Ready: Begin Your Excel Portfolio

Using Formulas to Conditionally Summarize Data

Using SUMIF

Using SUMIFS

Using COUNTIF

Using COUNTIFS

Using AVERAGEIF

Using AVERAGEIFS

Using Formulas to Look Up Data in a Workbook

Using VLOOKUP

]Using HLOOKUP

Adding Conditional Logic Functions to Formulas

Using IF

Using AND

Using OR

Using NOT

Using IFERROR

Using Formulas to Modify Text

Converting Text to Columns

Using LEFT

Using RIGHT

Using MID      

Using TRIM

Using PROPER

Using UPPER

Using LOWER

Using CONCATENATE

Using FIND

Using SUBSTITUTE

Assessment

Knowledge Assessment

Competency Assessment

Proficiency Assessment

Mastery Assessment

Lesson 11: Securing and Sharing Workbooks

Lesson Skill Matrix

Key Terms

Software Orientation

Securing Your Work Before Sharing It with Others

Protecting a Worksheet

Protecting a Workbook

Workplace Ready: Passwords and Security

Allowing Multiple Users to Edit a Workbook Simultaneously

Using the Document Inspector

Marking a Document as Final

Distributing a Workbook by Email and the Cloud

Distributing a Workbook by Email

Sharing a Workbook in the Cloud

Tracking Changes to a Workbook

Turning Track Changes On and Off

Setting Track Changes Options

Inserting Tracked Changes

Deleting Your Changes

Accepting Changes from Another User

Rejecting Changes from Another User

Removing Shared Status from a Workbook

Adding Comments to a Workbook

Inserting a Comment

Viewing a Comment

Editing a Comment

Deleting a Comment

Printing Comments in a Workbook

 Assessment

Knowledge Assessment

Competency Assessment

Proficiency Assessment

Mastery Assessment

Lesson Skill Matrix

Key Terms

Software Orientation

Building Charts

Workplace Ready: Personal Charts

Selecting Data to Include in a Chart

Moving a Chart

Choosing the Right Chart for Your Data

Using Recommended Charts

Creating a Bar Chart

Formatting a Chart with a Quick Style

Formatting a Chart with a Quick Layout

Formatting the Parts of a Chart MANUALLY

Editing and Adding Text on Charts

Formatting the Data Series

Changing the Chart's Border Line

Modifying a Chart’s Legend

Modifying a Chart

Adding Elements to a Chart

Deleting Elements from a Chart

Adding Additional Data Series

Resizing a Chart

Choosing a Different Chart Type

Switching Between Rows and Columns in Source Data

Using New Quick Analysis Tools

Adding a Chart or Sparklines

Working with Totals

Applying Conditional Formatting

Creating PivotTables and PivotCharts

Creating a Basic PivotTable

Adding a PivotChart

Assessment

Knowledge Assessment

Competency Assessment

Proficiency Assessment

Mastery Assessment

Lesson Skill Matrix

Key Terms

Software Orientation

Inserting Pictures

Inserting a Picture from a File

Inserting an Online Picture

Adding Shapes, Lines, Text Boxes, and WordArt

Adding Shapes

Drawing Lines

Adding Text to a Shape

Adding a Text Box

Using WordArt

Deleting, Copying and Moving Graphics

Deleting Graphics

Moving Graphics

Copying Graphics

Formatting Graphics

Applying Styles to Shapes and Changing Styles

Resizing a Graphic

Rotating a Graphic

Stacking Overlapping Graphics

Adding Graphic Enhancements Using Picture Tools

Making Corrections to a Graphic

Using the Color Feature to Enhance Images

Changing a Graphic with Artistic Effects

Applying a Picture Style

Resetting a Picture to Its Original State

Using Picture Properties

Using a Screenshot to Capture and Crop Images

Workplace Ready: Using Graphics in Excel and Other Office Applications

Using SmartArt Graphics

Creating a Flowchart

Creating an Organization Chart

 Assessment

Knowledge Assessment

Competency Assessment

Proficiency Assessment

Mastery Assessment