New Perspectives Microsoft Office 365 & Excel 2016 Intermediate

• Year: 2016
• Tongue: English
• Leaves: 588
• Category: Library

✦ Table of Contents

Cover
Contents
Productivity Apps for School and Work
Introduction to OneNote 2016
Creating a OneNote Notebook
Syncing a Notebook to the Cloud
Taking Notes
Converting Handwriting to Text
Recording a Lecture
1: Taking Notes for a Week
2: Using OneNote to Organize a Research Paper
3: Planning Your Career
Introduction to Sway
Creating a Sway Presentation
Adding Content to Build a Story
Designing a Sway
Publishing a Sway
Sharing a Sway
1: Creating a Sway Resume
2: Creating an Online Sway Newsletter
3: Creating and Sharing a Technology Presentation
Introduction to Office Mix
Adding Office Mix to PowerPoint
Capturing Video Clips
Inserting Quizzes, Live Webpages, and Apps
Sharing an Office Mix Presentation
1: Creating an Office Mix Tutorial for OneNote
2: Teaching Augmented Reality with Office Mix
3: Marketing a Travel Destination with Office Mix
Introduction to Microsoft Edge
Browsing the Web with Microsoft Edge
Locating Information with Cortana
Annotating Webpages
1: Using Cortana in Microsoft Edge
2: Viewing Online News with Reading View
3: Inking with Microsoft Edge
Module 01: Getting Started with Excel
Session 1.1 Visual Overview:
The Excel Workbook
Introducing Excel and Spreadsheets
Opening an Existing Workbook
Using Keyboard Shortcuts to Work Faster
Getting Help
Using Excel 2016 in Touch Mode
Exploring a Workbook
Changing the Active Sheet
Navigating Within a Worksheet
Selecting a Cell Range
Closing a Workbook
Planning a Workbook
Starting a New Workbook
Renaming and Inserting Worksheets
Moving Worksheets
Deleting Worksheets
Saving a Workbook
Entering Text, Dates, and Numbers
Entering Text
Undoing and Redoing an Action
Editing Cell Content
Understanding AutoComplete
Displaying Numbers as Text
Entering Dates
Entering Numbers
Resizing Columns and Rows
Changing Column Widths
Wrapping Text Within a Cell
Changing Row Heights
Session 1.1 Quick Check
Session 1.2 Visual Overview:
Excel Formulas and Functions
Performing Calculations with Formulas
Entering a Formula
Copying and Pasting Formulas
Simplifying Formulas with Functions
Introducing Function Syntax
Entering Functions with AutoSum
Modifying a Worksheet
Moving and Copying a Cell or Range
Using the COUNT Function
Inserting a Column or Row
Deleting a Row or Column
Inserting and Deleting a Range
Using Flash Fill
Formatting a Worksheet
Adding Cell Borders
Changing the Font Size
Printing a Workbook
Changing Worksheet Views
Changing the Page Orientation
Setting the Scaling Options
Setting the Print Options
Viewing Worksheet Formulas
Saving a Workbook with a New Filename
Session 1.2 Quick Check
Review Assignments
Case Problems
Module 02: Formatting Workbook Text and Data
Session 2.1 Visual Overview:
Formatting a Worksheet
Formatting Cell Text
Applying Fonts and Font Styles
Applying a Font Color
Formatting Text Selections Within a Cell
Working with Fill Colors and Backgrounds
Changing a Fill Color
Adding a Background Image
Using Functions and Formulas to Calculate Sales Data
Formatting Numbers
Applying Number Formats
Formatting Dates and Times
Formatting Worksheet Cells
Aligning Cell Content
Indenting Cell Content
Adding Borders to Cells
Merging Cells
Rotating Cell Contents
Exploring the Format Cells Dialog Box
Session 2.1 Quick Check
Session 2.2 Visual Overview:
Designing a Printout
Calculating Averages
Applying Cell Styles
Copying and Pasting Formats
Copying Formats with the Format Painter
Copying Formats with the Paste Options Button
Copying Formats with Paste Special
Finding and Replacing Text and Formats
Working with Themes
Sharing Styles and Themes
Highlighting Data with Conditional Formats
Highlighting Cells Based on Their Values
Highlighting Cells with a Top/Bottom Rule
Other Conditional Formatting Options
Creating a Conditional Formatting Legend
Formatting a Worksheet for Printing
Using Page Break Preview
Defining the Print Area
Inserting Page Breaks
Adding Print Titles
Designing Headers and Footers
Setting the Page Margins
Session 2.2 Quick Check
Review Assignments
Case Problems
Module 03: Performing Calculations with Formulas and Functions
Session 3.1 Visual Overview:
Formulas and Functions
Making Workbooks User-Friendly
Documenting Formulas
Using Constants in Formulas
Identifying Notes, Input Values, and Calculated Values
Using Excel Functions
Understanding Function Syntax
Entering the COUNT function
Nesting the ROUND and AVERAGE Functions
Using the Function Library and the Insert Function Dialog Box
Performing What-If Analyses
Using Trial and Error
Using Goal Seek
Interpreting Error Values
Session 3.1 Quick Check
Session 3.2 Visual Overview:
Cell References and Formulas
AutoFilling Formulas and Data
Filling a Series
Exploring Auto Fill Options
Filling Formulas
Exploring Cell References
Understanding Relative References
Understanding Absolute References
Understanding Mixed References
Changing Cell References in a Formula
Summarizing Data with the Quick Analysis Tool
Working with Dates and Date Functions
Using Lookup Functions
Finding an Exact Match with the VLOOKUP Function
Working with Logical Functions
Session 3.2 Quick Check
Review Assignments
Case Problems
Module 04: Analyzing and Charting Financial Data
Session 4.1 Visual Overview:
Chart Elements
Introduction to Financial Functions
Using the PMT Function
Getting Started with Excel Charts
Creating a Pie Chart
Selecting the Data Source
Charting with the Quick Analysis Tool
Moving and Resizing a Chart
Working with Chart Elements
Choosing a Chart Style
Formatting a Chart Legend
Formatting Pie Chart Labels
Changing the Pie Slice Colors
Formatting the Chart Area
Performing What-If Analyses with Charts
Creating a Column Chart
Comparing Column Chart Subtypes
Creating a Clustered Column Chart
Moving a Chart to a Different Worksheet
Editing a Chart Title
Creating a Stacked Column Chart
Session 4.1 Quick Check
Session 4.2 Visual Overview:
Charts, Sparklines, and Data Bars
Creating a Line Chart
Working with Axes and Gridlines
Editing the Scale of the Vertical Axis
Adding Gridlines to a Chart
Working with Column Widths
Formatting Data Markers
Formatting the Plot Area
Creating a Combination Chart
Working with Primary and Secondary Axes
Editing a Chart Data Source
Exploring Other Chart Types
Creating a Histogram
Creating a Pareto Chart
Using a Waterfall Chart
Using a Hierarchical Chart
Creating Sparklines
Formatting the Sparkline Axis
Working with Sparkline Groups
Creating Data Bars
Modifying a Data Bar Rule
Session 4.2 Quick Check
Review Assignments
Case Problems
Module 05: Working with Excel Tables, PivotTables, and PivotCharts
Session 5.1 Visual Overview:
Elements of an Excel Table
Planning a Structured Range of Data
Freezing Rows and Columns
Creating an Excel Table
Renaming an Excel Table
Modifying an Excel Table
Maintaining Data in an Excel Table
Adding Records
Finding and Editing Records
Deleting a Record
Sorting Data
Sorting One Column Using the Sort Buttons
Sorting Multiple Columns Using the Sort Dialog Box
Sorting Using a Custom List
Session 5.1 Quick Check
Session 5.2 Visual Overview:
Filtering Table Data
Filtering Data
Filtering Using One Column
Filtering Using Multiple Columns
Clearing Filters
Selecting Multiple Filter Items
Creating Criteria Filters to Specify More Complex Criteria
Using the Total Row to Calculate Summary Statistics
Splitting the Worksheet Window into Panes
Inserting Subtotals
Using the Subtotal Outline View
Session 5.2 Quick Check
Session 5.3 Visual Overview:
PivotTable and PivotChart
Analyzing Data with PivotTables
Creating a PivotTable
Adding Fields to a PivotTable
Changing the Layout of a PivotTable
Formatting a PivotTable
Filtering a PivotTable
Adding a Field to the FILTERS Area
Filtering PivotTable Fields
Creating a Slicer to Filter a PivotTable
Refreshing a PivotTable
Creating a Recommended PivotTable
Creating a PivotChart
Session 5.3 Quick Check
Review Assignments
Case Problems
Module 06: Managing Multiple Worksheets and Workbooks
Session 6.1 Visual Overview:
Worksheet Groups and 3-D References
Grouping Worksheets
Entering Headings and Formulas in a Worksheet Group
Formatting a Worksheet Group
Ungrouping Worksheets
Working with Multiple Worksheets
Copying Worksheets
Referencing Cells and Ranges in Other Worksheets
Using 3-D References to Add Values Across Worksheets
Printing a Worksheet Group
Session 6.1 Quick Check
Session 6.2 Visual Overview:
Links and External References
Linking Workbooks
Navigating Multiple Workbooks
Arranging Multiple Workbooks
Creating Formulas with External References
Updating Linked Workbooks
Updating a Destination Workbook with Source Workbooks Open
Updating a Destination Workbook with Source Workbooks Closed
Managing Links
Session 6.2 Quick Check
Session 6.3 Visual Overview:
Templates and Hyperlinks
Creating a Hyperlink
Inserting a Hyperlink
Editing a Hyperlink
Using Templates
Creating a Workbook Based on an Existing Template
Creating a Custom Workbook Template
Creating a New Workbook from a Template
Session 6.3 Quick Check
Case Problems
Module 07: Developing an Excel Application
Session 7.1 Visual Overview:
Excel Application and Defined Names
Planning an Excel Application
Naming Cells and Ranges
Using the Name Box to Create Defined Names
Selecting Cells and Ranges by Their Defined Names
Creating Defined Names by Selection
Editing and Deleting Defined Names
Using the Paste Names Command
Using Defined Names in Formulas
Entering Formulas with Defined Names
Adding Defined Names to Existing Formulas
Session 7.1 Quick Check
Session 7.2 Visual Overview:
Data Validation and Protection
Validating Data Entry
Specifying Validation Criteria
Creating an Error Alert Style and Message
Creating an Input Message
Creating a List Validation Rule
Testing Data Validation Rules
Protecting a Worksheet and a Workbook
Locking and Unlocking Cells
Protecting a Worksheet
Protecting a Workbook
Unprotecting a Worksheet and a Workbook
Inserting Comments
Session 7.2 Quick Check
Session 7.3 Visual Overview:
Working with Macros
Automating Tasks with Macros
Protecting Against Macro Viruses
Macro Security Settings
Recording a Macro
Running a Macro
Creating the TransferData Macro
Fixing Macro Errors
Working with the Visual Basic Editor
Understanding the Structure of Macros
Editing a Macro Using the Visual Basic Editor
Creating Macro Buttons
Saving a Workbook with Macros
Opening a Workbook with Macros
Removing a Tab from the Ribbon
Session 7.3 Quick Check
Review Assignments
Case Problems
Module 08: Working with Advanced Functions
Session 8.1 Visual Overview:
Logical Functions
Working with Logical Functions
Inserting Calculated Columns in an Excel Table
Using the IF Function
Using the AND Function
Using the OR Function
Using Structured References to Create Formulas in Excel Tables
Session 8.1 Quick Check
Session 8.2 Visual Overview:
Lookup Tables and the IFERROR Function
Creating Nested IFs
Using LOOKUP Functions
Using the VLOOKUP Function to Find an Exact Match
Using the VLOOKUP Function to Find an Approximate Match
Using the HLOOKUP Function to Find an Exact Match
Using the IFERROR Function
Session 8.2 Quick Check
Session 8.3 Visual Overview:
Conditional Formatting and Functions
Applying Conditional Formatting
Highlighting Duplicate Values
Using the Conditional Formatting Rules Manager
Using Functions to Summarize Data Conditionally
Using the COUNTIF Function
Using the SUMIF Function
Using the AVERAGEIF Function
Session 8.3 Quick Check
Review Assignments
Case Problems
Module 09: Working with Text Functions and Creating Custom Formats
Opening and Saving Workbooks Created in Earlier Versions of Excel
Using Text Functions
Using the LEN and LEFT Functions
Using the Paste Values Command
Using the PROPER Function and the CONCATENATE Function
Applying the Text to Columns Command
Using the UPPER Function to Convert Case
Using the SUBSTITUTE Function
Using Special Formats
Creating Custom Formats
Creating a Custom Number Format
Creating a Custom Date Format
Using the Compatibility Checker
Review Assignments
Case Problems
Index