Table of Contents
GO! Walkthrough
Office
Chapter 1 Introduction to Microsoft Office 2013 Features
Project 1A Note Form
Objective 1 Use File Explorer to Download, Extract, and Locate Files and Folders
Activity 1.01 Using File Explorer to Download, Extract, and Locate Files and Folders
Objective 2 Use Start Search to Locate and Start a Microsoft Office 2013 Desktop App
Activity 1.02 Using Start Search to Locate and Start a Microsoft Office 2013 Desktop App
Objective 3 Enter, Edit, and Check the Spelling of Text in an Office 2013 Program
Activity 1.03 Entering and Editing Text in an Office 2013 Program
Activity 1.04 Checking Spelling
Objective 4 Perform Commands from a Dialog Box
Activity 1.05 Performing Commands from a Dialog Box
Activity 1.06 Using Undo
Objective 5 Create a Folder and Name and Save a File
Activity 1.07 Creating a Folder and Naming and Saving a File
Objective 6 Insert a Footer, Add Document Properties, Print a File, and Close a Desktop App
Activity 1.08 Inserting a Footer, Inserting Document Info, and Adding Document Properties
Activity 1.09 Printing a File and Closing a Desktop App
Project 1B Memo
Objective 7 Open an Existing File and Save It with a New Name
More Knowledge Read-Only
Objective 8 Sign In to Office and Explore Options for a Microsoft Office Desktop App
Activity 1.00 Perform Commands from the Ribbon and Quick Access Toolbar
More Knowledge Customizing the Ribbon
Objective 9 Apply Formatting in Office Programs
More Knowledge Zooming to Page Width
Objective 10 Compress Files and Use the Microsoft Office 2013 Help System
Objective 11 Install Apps for Office and Create a Microsoft Account
Summary, GO! Learn It Online
Glossary
Excel Introduction to Microsoft Excel 2013
Chapter 1 Creating a Worksheet and Charting Data
Project 1A Sales Report with Embedded Column Chart and Sparklines
Objective 1 Create, Save, and Navigate an Excel Workbook
Activity 1.01 Starting Excel, Navigating Excel, and Naming and Saving a Workbook
More Knowledge Creating a New Workbook by Using a Template
Objective 2 Enter Data in a Worksheet
Activity 1.02 Entering Text, Using AutoComplete, and Using the Name Box to Select a Cell
Activity 1.03 Using Auto Fill and Keyboard Shortcuts
Activity 1.04 Aligning Text and Adjusting the Size of Columns
Activity 1.05 Entering Numbers
Objective 3 Construct and Copy Formulas and Use the SUM Function
Activity 1.06 Constructing a Formula and Using the SUM Function
Activity 1.07 Copying a Formula by Using the Fill Handle
Objective 4 Format Cells with Merge & Center, Cell Styles, and Themes
Activity 1.08 Using Merge & Center and Applying Cell Styles
Activity 1.09 Formatting Financial Numbers
Activity 1.10 Changing the Workbook Theme
More Knowledge Formatting a Cell's Font, Style, Size, or Color with Individual Commands
Objective 5 Chart Data to Create a Column Chart and Insert Sparklines
Activity 1.11 Charting Data and Using Recommended Charts to Select and Insert a Column Chart
Activity 1.12 Using the Chart Tools to Apply Chart Styles
Activity 1.13 Creating and Formatting Sparklines
Objective 6 Print a Worksheet, Display Formulas, and Close Excel
Activity 1.14 Creating a Footer and Centering a Worksheet
Activity 1.15 Adding Document Properties and Printing a Workbook
More Knowledge Inserting a Watermark
Activity 1.16 Printing a Section of the Worksheet
Activity 1.17 Changing Page Orientation and Displaying, Printing, and Hiding Formulas
Activity 1.18 Creating a Sales Report with Embedded Column Chart Using the Excel Web App
Project 1B Inventory Valuation
More Knowledge Calculate a Percentage if You Know the Total and the Amount
More Knowledge Hiding Rows and Columns
Summary, GO! Learn It Online, GO! For Job Success
Glossary
Skills Review
Mastering Excel
Rubric
GO! Think
Chapter 2 Using Functions, Creating Tables, and Managing Large Workbooks
Project 2A Inventory Status Report
Objective 1 Use Flash Fill and the SUM, AVERAGE, MEDIAN, MIN, and MAX Functions
Activity 2.01 Using Flash Fill
Activity 2.02 Moving a Column
Activity 2.03 Using the SUM and AVERAGE Functions
Activity 2.04 Using the MEDIAN Function
Activity 2.05 Using the MIN and MAX Functions
Objective 2 Move Data, Resolve Error Messages, and Rotate Text
Activity 2.06 Moving Data and Resolving a # # # # # Error Message
Activity 2.07 Rotating Text
Objective 3 Use COUNTIF and IF Functions and Apply Conditional Formatting
Activity 2.08 Using the COUNT and COUNTIF Functions
Activity 2.09 Using the IF Function
Activity 2.10 Applying Conditional Formatting by Using Highlight Cells Rules and Data Bars
Activity 2.11 Using Find and Replace
More Knowledge NOW Function Recalculates Each Time a Workbook Opens
Objective 4 Use Date & Time Functions and Freeze Panes
Activity 2.12 Using the NOW Function to Display a System Date
Activity 2.13 Freezing and Unfreezing Panes
More Knowledge Freeze Columns or Freeze Both Rows and Columns
Objective 5 Create, Sort, and Filter an Excel Table
Activity 2.14 Creating an Excel Table and Applying a Table Style
Activity 2.15 Sorting an Excel Table
More Knowledge Band Rows and Columns in a Table
Activity 2.16 Filtering an Excel Table and Displaying a Total Row
Activity 2.17 Clearing Filters and Converting a Table to a Range of Data
Objective 6 View, Format, and Print a Large Worksheet
Activity 2.18 Modifying and Shrinking the Worksheet View
Activity 2.19 Splitting a Worksheet Window into Panes
Activity 2.20 Printing Titles and Scaling to Fit
More Knowledge Scaling for Data That Is Slightly Larger Than the Printed Page
Activity 2.21 Sort, Filter, and Use Functions in a Table
Project 2B Weekly Sales Summary
More Knowledge Copying a Worksheet
More Knowledge Clearing an Entire Worksheet
More Knowledge Hide Worksheets
Summary, GO! Learn It Online, GO! For Job Success
Glossary
Skills Review
Mastering Excel
Rubric
GO! Think
Chapter 3 Analyzing Data with Pie Charts, Line Charts, and What-If Analysis Tools
Project 3A Enterprise Fund Pie Chart
Objective 1 Chart Data with a Pie Chart
Activity 3.01 Calculating Values for a Pie Chart
Activity 3.02 Creating a Pie Chart in a Chart Sheet
Objective 2 Format a Pie Chart
Activity 3.03 Formatting a Chart Title by Applying a WordArt Style and Changing Font Size
Activity 3.04 Formatting Chart Elements by Removing a Legend and Adding and Formatting Data Labels
Activity 3.05 Formatting a Data Series with 3-D Effects
Activity 3.06 Formatting a Data Series with a Shadow Effect
Activity 3.07 Rotating a Pie Chart by Changing the Angle of the First Slice
Activity 3.08 Exploding and Coloring a Pie Slice
Activity 3.09 Formatting the Chart Area
Objective 3 Edit a Workbook and Update a Chart
Activity 3.10 Editing a Workbook and Updating a Chart
Objective 4 Use Goal Seek to Perform What-If Analysis
Activity 3.11 Using Goal Seek to Perform What-If Analysis
Activity 3.12 Preparing and Printing a Workbook with a Chart Sheet
Activity 3.13 Creating a JPEG Photo of a Chart and Uploading It to a OneNote Web App Notebook
Project 3B Tourism Spending Projection with Line Chart
More Knowledge Use of Parentheses in a Formula
More Knowledge Percentage Calculations
More Knowledge Percent Increase or Decrease
More Knowledge Resizing a Chart
Summary, GO! Learn It Online, GO! For Job Success
Glossary
Skills Review
Mastering Excel
Rubric
GO! Think
Chapter 4 Use Financial and Lookup Functions, Define Names, Validate Data, and Audit Worksheets
Project 4A Amortization Schedule and -Merchandise Costs
Objective 1 Use Financial Functions
Activity 4.01 Inserting the PMT Financial Function
Objective 2 Use Goal Seek
Activity 4.02 Using Goal Seek to Produce a Desired Result
Activity 4.03 Using Goal Seek to Find an Increased Period
Objective 3 Create a Data Table
Activity 4.04 Designing a Two-Variable Data Table
Activity 4.05 Using a Data Table to Calculate Options
Objective 4 Use Defined Names in a Formula
Activity 4.06 Defining a Name
Activity 4.07 Inserting New Data into a Named Range
Activity 4.08 Creating a Defined Name by Using Row and Column Titles
Activity 4.09 Using Defined Names in a Formula
Project 4B Lookup Form and Revenue Report
More Knowledge Creating Validation Messages
More Knowledge
Summary, GO! Learn It Online, GO! For Job Success
Glossary
Skills Review
Mastering Excel
Rubric
GO! Think
Chapter 5 Managing Large Workbooks and Using Advanced Sorting and Filtering
Project 5A Large Worksheet for a Class Schedule
Objective 1 Navigate and Manage Large Worksheets
Activity 5.01 Using the Go To Special Command
Activity 5.02 Hiding Columns
Activity 5.03 Using the Go To Command
Activity 5.04 Arranging Multiple Workbooks and Splitting Worksheets
Objective 2 Enhance Worksheets with Themes and Styles
Activity 5.05 Changing and Customizing a Workbook Theme
Activity 5.06 Creating and Applying a Custom Table Style
Objective 3 Format a Worksheet to Share with Others
Activity 5.07 Previewing and Modifying Page Breaks
Activity 5.08 Repeating Column or Row Titles
Activity 5.09 Inserting a Hyperlink in a Worksheet
Activity 5.10 Modifying a Hyperlink
Objective 4 Save Excel Data in Other File Formats
Activity 5.11 Viewing and Saving a Workbook as a Web Page
Activity 5.12 Saving Excel Data in CSV File Format
Activity 5.13 Saving Excel Data as a PDF or XPS File
More Knowledge Converting a Tab Delimited Text File to a Word Table
Project 5B Sorted, Filtered, and Outlined Database
More Knowledge A Custom List Remains Available for All Workbooks in Excel
More Knowledge Using Wildcards
Summary, GO! Learn It Online, GO! For Job Success
Glossary
Skills Review
Mastering Excel
Rubric
GO! Think
Chapter 6 Creating Charts, Diagrams, and Templates
Project 6A Attendance Charts and Diagrams
Objective 1 Create and Format Sparklines and a Column Chart
Activity 6.01 Creating and Formatting Sparklines
Activity 6.02 Creating a Column Chart
More Knowledge Sizing Handles and Selection Handles
Activity 6.03 Changing the Display of Chart Data
More Knowledge Changing the Data in a Chart
Activity 6.04 Editing and Formatting the Chart Title
Activity 6.05 Adding, Formatting, and Aligning Axis Titles
Activity 6.06 Editing Source Data
Activity 6.07 Formatting the Chart Floor and Chart Walls
Objective 2 Create and Format a Line Chart
Activity 6.08 Creating a Line Chart
Activity 6.09 Changing a Chart Title
Activity 6.10 Changing the Values on the Value Axis
Activity 6.11 Formatting the Plot Area and the Data Series
Activity 6.12 Inserting a Trendline
Objective 3 Create and Modify a SmartArt Graphic
Activity 6.13 Creating a Process SmartArt Graphic
Activity 6.14 Modifying the Diagram Style
Objective 4 Create and Modify an Organization Chart
Activity 6.15 Creating and Modifying a SmartArt Organization Chart
Activity 6.16 Adding Effects to a SmartArt Graphic
Activity 6.17 Preparing Worksheets Containing Charts and Diagrams for Printing
Project 6B Order Form Template
More Knowledge Setting the Default Font for New Worksheets
More Knowledge Modifying a Template
Summary, GO! Learn It Online, GO! For Job Success
Glossary
Skills Review
Mastering Excel
Rubric
GO! Think
Chapter 7 Creating PivotTable and PivotChart Reports and Using BI Tools in Excel
Project 7A PivotTable and PivotChart
Objective 1 Create a PivotTable Report
Activity 7.01 Creating a PivotTable Report
More Knowledge
Activity 7.02 Adding Fields to a PivotTable Report
Objective 2 Use Slicers and Search Filters
Activity 7.03 Using Slicer to Filter a PivotTable
Activity 7.04 Clearing Filters and Filtering by Using the Search Box
Objective 3 Modify a PivotTable Report
Activity 7.05 Rearranging a PivotTable Report
Activity 7.06 Displaying PivotTable Report Details in a New Worksheet
Activity 7.07 Displaying PivotTable Data on Separate Pages
Activity 7.08 Changing Calculations in a PivotTable Report
Activity 7.09 Formatting a PivotTable Report
Activity 7.10 Updating PivotTable Report Data
Objective 4 Create a PivotChart Report
Activity 7.11 Creating a PivotChart Report from a PivotTable Report
Activity 7.12 Modifying a PivotChart Report
Activity 7.13 Arranging and Hiding Worksheets in a Multi-sheet Workbook
Project 7B PowerPivot and Power View
Objective 5 Create a Data Model Using PowerPivot
Summary, GO! Learn It Online, GO! For Job Success
Glossary
Skills Review
Mastering Excel
Rubric
GO! Think
Chapter 8 Using the Data Analysis, Solver, and Scenario Features, and Building Complex Formulas
Project 8A Sales Analysis
Objective 1 Calculate a Moving Average
Activity 8.01 Creating a Custom Number Format
More Knowledge The Transpose Function
Activity 8.02 Calculating a Moving Average
Activity 8.03 Modifying the Moving Average Chart
More Knowledge Using a Moving Average to Forecast Trends
Activity 8.04 Calculating Growth Based on a Moving Average
Objective 2 Project Income and Expenses
Activity 8.05 Projecting Income and Expenses
Objective 3 Determine a Break-Even Point
Activity 8.06 Charting the Break-Even Point with a Line Chart
Project 8B Staffing Analysis
More Knowledge Solver Reports
Summary, GO! Learn It Online, GO! For Job Success
Glossary
Skills Review
Mastering Excel
Rubric
GO! Think
Chapter 9 Using Macros and Visual Basic for Applications
Project 9A Travel Expenses
Objective 1 Record a Macro
Activity 9.01 Adding the Developer Tab to the Ribbon
Activity 9.02 Changing the Macro Security Settings in Excel 5
Activity 9.03 Unprotecting a Workbook
Activity 9.04 Recording a Macro
Objective 2 Assign a Macro to a Button on the Quick Access Toolbar
Activity 9.05 Adding a Button to the Quick Access Toolbar
Activity 9.06 Testing the Macro Button
Objective 3 Modify a Macro
Activity 9.07 Changing the Visual Basic Code
More Knowledge
Copy a Macro to Another Workbook
Project 9B VBA Procedure
Summary, GO! Learn It Online, GO! For Job Success
Glossary
Skills Review
Mastering Excel
Rubric
GO! Think
Chapter 10 External Data, Database Functions, and Side-by-Side Tables
Project 10A Medical Center Information
Objective 1 Get External Data into Excel
Activity 10.01 Importing Data into Excel from an Access Database Excel
Activity 10.02 Importing Data into Excel from a Webpage
Activity 10.03 Importing Data into Excel from an XML File
Activity 10.04 Importing Data into Excel from a Text File
Objective 2 Cleanup and Manage Data
Activity 10.05 Removing Duplicates
Activity 10.06 Using Flash Fill to Expand Data
Activity 10.07 Using Text Functions
Objective 3 Create a Query and Use the Query Wizard to Sort and Filter
Activity 10.08 Creating a Query to Filter and Sort Data by Using the Query Wizard
Activity 10.09 Converting a Table into a Range
Objective 4 Use Database Functions
Activity 10.10 Using the DAVERAGE Database Function
Activity 10.11 Using the DSUM Database Function
Activity 10.12 Using DCOUNT
Project 10B Office Equipment Inventory
Summary, GO! Learn It Online, GO! For Job Success
Glossary
Skills Review
Mastering Excel
Rubric
GO! Think
Chapter 11 Collaborating with Others and Preparing a Workbook for Distribution
Summer Schedule
Objective 1 Create a Shared Workbook
Activity 11.01 Locating and Modifying Workbook Properties
More Knowledge Read-only Property
Activity 11.02 Making Edits in a Foreign Language and Inserting a Watermark Image
Activity 11.03 Activating Track Changes to Create a Shared Workbook
Objective 2 Track Changes Made to a Workbook
Activity 11.04 Making a Copy of a Shared Workbook
Activity 11.05 Making Changes to a Shared Workbook
Activity 11.06 Making Changes to a Copy of the Shared Workbook
Objective 3 Merge Workbooks and Accept Changes
Activity 11.07 Merging Revisions
Activity 11.08 Accepting or Rejecting Tracked Changes
Activity 11.09 Removing the Shared Designation, Resetting the User Name, and Removing a Command
from the Quick Access Toolbar
More Knowledge Change History
Activity 11.10 Adding a Signature Line
Distributed Workbook
Objective 4 Prepare a Final Workbook for Distribution
More Knowledge Making Passwords More Secure
More Knowledge Mark as Final Feature
Objective 5 Upload a Workbook to SkyDrive
More Knowledge The World of Cloud Computing Is Fast Changing
More Knowledge Syncing SkyDrive with Your Computer
Summary, GO! Learn It Online,
Glossary
Skills Review
Mastering Excel
Rubric
GO! Think
Glossary
Index