GO! with Microsoft Office 365, Excel 2019 Comprehensive 1st Edition

Welcome
Welcome
Copyright Page and Preface
Section 1: Copyright
Section 2: About the Authors
Section 3: GO! with Microsoft Excel 2019 Comprehensive
Section 4: Reviewers of the GO! Series
1: Microsoft Office Features and Windows 10 File Management
Introduction: Microsoft Office Features and Windows 10 File Management
Project 1A: Chef Notes
Objective 1: Explore Microsoft Office
Objective 2: Create a Folder for File Storage
Objective 3: Download and Extract Zipped Files, Enter and Edit Text in an Office Application, and Use Editor to Check Documents
Activity 1.04: Checking Spelling
Objective 4: Perform Office Commands and Apply Office Formatting
Activity 1.06: Using Undo and Applying Text Effects
Activity 1.07: Performing Commands from and Customizing the Quick Access Toolbar
Activity 1.08: Performing Commands from the Ribbon
Activity 1.09: Minimizing the Ribbon
Activity 1.10: Changing Page Orientation and Zoom Level
Activity 1.11: Formatting Text by Using Fonts, Alignment, Font Colors, and Font Styles
Activity 1.12: Using Format Painter
Activity 1.13: Using Keyboard Shortcuts and Using the Clipboard to Copy, Cut, and Paste
Activity 1.14: Adding Alternative Text for Accessibility
Objective 5: Finalize an Office Document
Activity 1.16: Inspecting a Document
Activity 1.17: Inserting a Bookmark and a 3D Model
Activity 1.18: Printing a File and Closing a Desktop App
Objective 6: Use the Office Help Features
Project 1B: Hotel Files
Objective 7: Explore Windows 10
Activity 1.21: Turning On Your Computer, Signing In, and Exploring the Windows 10 Environment
Activity 1.22: Pinning a Program to the Taskbar
Objective 8: Prepare to Work with Folders and Files
Activity 1.24: Creating and Saving a File
Activity 1.25: Downloading and Extracting Zipped Files
Activity 1.26: Locating and Inserting a Saved File Into a PowerPoint Presentation
Activity 1.27: Using Snap and Task View
Objective 9: Use File Explorer to Extract Zipped Files and to Display Locations, Folders, and Files
Activity 1.29: Using File Explorer to Extract Zipped Files
Activity 1.30: Using File Explorer to Display Locations, Folders, and Files
Objective 10: Start Programs and Open Data Files
Activity 1.32: Opening Data Files
Activity 1.33: Searching, Pinning, Sorting, and Filtering in File Explorer
Objective 11: Create, Rename, and Copy Files and Folders
Activity 1.35: Creating Folders, Renaming Folders, and Renaming Files
Activity 1.36: Copying Files
Activity 1.37: Moving Files
Activity 1.38: Copying and Moving Files by Snapping Two Windows
GO! To Work
End of Chapter
Office and Windows Key Terms
Excel: Introducing Microsoft Excel 2019
Excel 2019: Introduction
1: Creating a Worksheet and Charting Data
Introduction: 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
Objective 2: Enter Data in a Worksheet
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.07: Copying a Formula by Using the Fill Handle
Objective 4: Format Cells with Merge & Center, Cell Styles, and Themes
Activity 1.09: Formatting Financial Numbers
Activity 1.10: Changing the Workbook Theme
Objective 5: Chart Data to Create a Column Chart and Insert Sparkline
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.15: Adding Document Properties and Printing a Workbook
Activity 1.16: Printing a Section of the Worksheet
Activity 1.17: Changing Page Orientation and Displaying, Printing, and Hiding Formulas
GO! With Google Sheets
Project 1B: Inventory Valuation
Objective 7: Check Spelling in a Worksheet
Objective 8: Enter Data by Range
Objective 9: Construct Formulas for Mathematical Operations
Activity 1.21: Using the Quick Analysis Tool
Activity 1.22: Copying Formulas Containing Absolute Cell References
Objective 10: Edit Values in a Worksheet
Activity 1.24: Formatting Cells with the Percent Style
Objective 11: Format a Worksheet
Activity 1.26: Adjusting Column Widths and Wrapping Text
Activity 1.27: Changing Theme Colors
GO! With Google Sheets
GO! To Work
End of Chapter
Project Guide for Excel Chapter 1
Excel Chapter 1 Key Terms
Chapter Review
Skills Review: Project 1D Band and Tubing Inventory
Content-Based Assessments (Mastery and Transfer of Learning)
Mastering Excel: Project 1E Gym Sales
Mastering Excel: Project 1F Balance Sales
Mastering Excel: Project 1G Regional Sales
Content-Based Assessments (Critical Thinking)
Outcomes-Based Assessments (Critical Thinking)
2: Using Functions, Creating Tables, and Managing Large Workbooks
Introduction: 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.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.07: Merging Cells and Rotating Text
Objective 3: Use COUNTIF and IF Functions and Apply Conditional Formatting
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
Objective 4: Use Date & Time Functions and Freeze Panes
Activity 2.13: Freezing and Unfreezing Panes
Objective 5: Create, Sort, and Filter an Excel Table
Activity 2.15: Sorting an Excel Table
Activity 2.16: Filtering an Excel Table and Displaying a Total Row
Activity 2.17: Clearing Filters
Objective 6: View, Format, and Print a Large Worksheet
Activity 2.19: Splitting a Worksheet Window into Panes
Activity 2.20: Printing Titles and Scaling to Fit
GO! With Google Sheets
Project 2B: Weekly Sales Summary
Objective 7: Navigate a Workbook and Rename Worksheets
Objective 8: Enter Dates, Clear Contents, and Clear Formats
Activity 2.23: Clearing Cell Contents and Formats
Objective 9: Copy and Paste by Using the Paste Options Gallery
Objective 10: Edit and Format Multiple Worksheets at the Same Time
Activity 2.26: Formatting and Constructing Formulas on Grouped Worksheets
Activity 2.27: Determining Multiple Totals at the Same Time
Activity 2.28: Formatting Grouped Worksheets
Activity 2.29: Ungrouping Worksheets
Objective 11: Create a Summary Sheet with Column Sparklines
Activity 2.31: Constructing Formulas that Refer to Cells in Another Worksheet
Activity 2.32: Changing Values in a Detail Worksheet to Update a Summary Worksheet
Activity 2.33: Inserting Column Sparklines
Activity 2.34: Determining Bonus Based on Sales Using the IFS Function
Objective 12: Format and Print Multiple Worksheets in a Workbook
Activity 2.36: Printing All or Individual Worksheets in a Workbook
GO! With Google Sheets
GO! To Work
End of Chapter
Project Guide For Excel Chapter 2
Excel Chapter 2 Key Terms
Chapter Review
Skills Review: Project 2D Canada
Content-Based Assessments (Mastery and Transfer of Learning)
Mastering Excel: Project 2F Bonus
Mastering Excel: Project 2G Inventory
Content-Based Assessments (Critical Thinking)
Outcomes-Based Assessments (Critical Thinking)
3: Analyzing Data with Pie Charts, Line Charts, and What-If Analysis Tools
Introduction: 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.02: Creating a Pie Chart and Moving a Chart to a Chart Sheet
Objective 2: Format a Pie Chart
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
Objective 4: Use Goal Seek to Perform What-If Analysis
Activity 3.12: Preparing and Printing a Workbook with a Chart Sheet
GO! With Google Sheets
Project 3B: Tourism Spending Projection and Analysis
Objective 5: Design a Worksheet for What-If Analysis
Activity 3.14: Using Format Painter
Activity 3.15: Calculating a Value After an Increase
Objective 6: Answer What-If Questions by Changing Values in a Worksheet
Objective 7: Chart Data with a Line Chart
Activity 3.18: Formatting Axes in a Line Chart
Activity 3.19: Formatting the Chart Area in a Line Chart
Activity 3.20: Formatting the Plot Area Gridlines and Axis Fonts in a Line Chart
Objective 8: Create a Map Chart and a Funnel Chart
Activity 3.22: Creating and Sizing a Funnel Chart
GO! With Google Sheets
GO! To Work
End of Chapter
Project Guide For Excel Chapter 3
Excel Chapter 3 Key Terms
Chapter Review
Skills Review: Project 3D Housing Permits
Content-Based Assessments (Mastery and Transfer of Learning)
Mastering Excel: Project 3F Streets
Mastering Excel: Project 3G Expenses
Content-Based Assessments (Critical Thinking)
Outcomes-Based Assessments (Critical Thinking)
4: Creating PivotTables and PivotCharts
Introduction: Creating PivotTables and PivotCharts
Project 4A: PivotTable and PivotChart
Objective 1: Create a PivotTable Report
Activity 4.02: Adding Fields to a PivotTable
Objective 2: Use Slicers and Search Filters
Activity 4.04: Clearing Filters and Filtering by Using the Search Box
Objective 3: Modify a PivotTable
Activity 4.06: Displaying PivotTable Report Details in a New Worksheet
Activity 4.07: Displaying PivotTable Data on Separate Pages
Activity 4.08: Changing Calculations in a PivotTable Report
Activity 4.09: Formatting a PivotTable Report
Activity 4.10: Updating PivotTable Report Data
Objective 4: Create a PivotChart
Activity 4.12: Modifying a PivotChart Report
Activity 4.13: Arranging and Hiding Worksheets in a Multi-sheet Workbook
Project 4B: Supply Expenditure Analysis
Objective 5: Create a PivotTable from a Data Model
Activity 4.15: Creating a PivotTable Using Multiple Tables
Objective 6: Create and Format a 3-D Pie PivotChart
Activity 4.17: Formatting a 3-D Pie PivotChart
Objective 7: Use Power BI to Analyze Data
GO! To Work
End of Chapter
Project Guide For Excel Chapter 4
Excel Chapter 4 Key Terms
Chapter Review
Skills Review: Project 4D Office Supplies
Content-Based Assessments (Mastery and Transfer of Learning)
Mastering Excel: Project 4F Vehicles
Mastering Excel: Project 4G Aquatics
Content-Based Assessments (Critical Thinking)
Outcome-Based Assessments (Critical Thinking)
5: Managing Large Workbooks and Using Advanced Sorting and Filtering
Introduction: Managing Large Workbooks and Using Advanced Sorting and Filtering
Project 5A: Class Schedule
Objective 1: Navigate and Manage Large Worksheets
Activity 5.02: Hiding Columns
Activity 5.03: Using the Go To Command and the COUNTIF Function
Activity 5.04: Arranging Multiple Workbooks and Splitting Worksheets
Objective 2: Enhance Worksheets with Themes and Styles
Activity 5.06: Creating and Applying a Custom Table Style
Objective 3: Format a Worksheet to Share with Others
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.12: Saving Excel Data in CSV File Format
Activity 5.13: Saving Excel Data as a PDF File
Project 5B: Sorted, Filtered, and Outlined Database
Objective 5: Use Advanced Sort Techniques
Activity 5.15: Sorting by Using a Custom List
Objective 6: Use Custom and Advanced Filters
Activity 5.17: Filtering by Custom Criteria Using AutoFilter
Activity 5.18: Using the Name Manager and Filtering by Using Advanced Criteria
Activity 5.19: Naming Ranges and Extracting Filtered Rows
Objective 7: Subtotal, Outline, and Group a List of Data
GO! To Work
End of Chapter
Project Guide for Excel Chapter 5
Excel Chapter 5 Key Terms
Chapter Review
Skills Review: Project 5D Spring Sections
Content-Based Assessments (Mastery and Transfer of Learning)
Mastering Excel: Project 5F Career Programs
Mastering Excel: Project 5G Sports Programs
Content-Based Assessments (Critical Thinking)
Outcomes-Based Assessments (Critical Thinking)
6: Creating Charts, Diagrams, and Templates
Introduction: Creating Charts, Diagrams, and Templates
Project 6A: Attendance Charts and Diagrams
Objective 1: Create and Format Sparklines and a Column Chart
Activity 6.02: Creating a Column Chart
Activity 6.03: Changing the Display of Chart Data
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.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.14: Modifying the Diagram Style
Objective 4: Create and Modify a Funnel Chart
Activity 6.16: Formatting a Funnel Chart
Activity 6.17: Preparing Worksheets Containing Charts and Diagrams for Printing
Project 6B: Order Form Template
Objective 5: Create an Excel Template
Activity 6.19: Formatting a Template
Activity 6.20: Entering Template Formulas
Activity 6.21: Inserting and Modifying an Image
Activity 6.22: Changing the Orientation of Text in a Cell
Activity 6.23: Saving a File as a Template
Objective 6: Protect a Worksheet
Objective 7: Create a Worksheet Based on a Template
GO! To Work
End of Chapter
Project Guide For Excel Chapter 6
Excel Chapter 6 Key Terms
Chapter Review
Skills Review: Project 6D Purchase Order
Content-Based Assessments (Mastery and Transfer of Learning)
Mastering Excel: Project 6F Event Budget
Mastering Excel: Project 6G Internships and Travel Template
Content-Based Assessments (Critical Thinking)
Outcome-Based Assessments (Critical Thinking)
Content-Based Assessments (Mastery and Transfer of Learning)
7: Use Financial and Lookup Functions, Define Names, Validate Data, and Audit Worksheets
Introduction: Use Financial and Lookup Functions, Define Names, Validate Data, and Audit Worksheets
Project 7A: Amortization Schedule and Merchandise Costs
Objective 1: Use Financial Functions
Objective 2: Use Goal Seek
Activity 7.03: Using Goal Seek to Find an Increased Payment Period
Objective 3: Create a Data Table
Activity 7.05: Using a Data Table to Calculate Options
Objective 4: Use Defined Names in a Formula
Activity 7.07: Inserting New Data into a Named Range
Activity 7.08: Creating a Defined Name by Using Row and Column Titles
Activity 7.09: Using Defined Names in a Formula
Project 7B: Lookup Form and Revenue Report
Objective 5: Use Lookup Functions
Activity 7.11: Inserting the VLOOKUP Function
Objective 6: Validate Data
Objective 7: Audit Worksheet Formulas
Activity 7.14: Tracing Dependents
Activity 7.15: Tracing Formula Errors
Activity 7.16: Using Error Checking
Activity 7.17: Circling Invalid Data
Objective 8: Use the Watch Window to Monitor Cell Values
Objective 9: Use the MATCH and INDEX Functions
GO! To Work
End of Chapter
Project Guide For Excel Chapter 7
Excel Chapter 7 Key Terms
Chapter Review
Skills Review: Project 7D Quarterly Cost Report and Lookup Form
Content-Based Assessments (Mastery and Transfer of Learning)
Mastering Excel: Project 7F Lookup Form and Sales Revenue
Mastering Excel: Project 7G Warehouse Loan and Staff Lookup Form
Content-Based Assessments (Critical Thinking)
Outcome-Based Assessments (Critical Thinking)
8: Using the Data Analysis, Solver, and Scenario Features, and Building Complex Formulas
Introduction: Using the Data Analysis, Solver, and Scenario Features, and Building Complex Formulas
Project 8A: Sales Analysis
Objective 1: Calculate a Moving Average
Activity 8.02: Calculating a Moving Average
Activity 8.03: Modifying the Moving Average Chart and Creating a Dual-Axis Chart
Activity 8.04: Calculating Growth Based on a Moving Average
Objective 2: Project Income and Expenses
Objective 3: Determine a Break-Even Point
Project 8B: Staffing Analysis
Objective 4: Use Solver
Activity 8.08: Understanding a Solver Worksheet
Activity 8.09: Using Solver
Objective 5: Create Scenarios
Activity 8.11: Creating a Scenario Using Solver
Activity 8.12: Creating a Scenario Summary
Objective 6: Use Logical Functions
Activity 8.14: Using the SUMIFS Function
Activity 8.15: Using the COUNTIFS Function
Objective 7: Create Complex Formulas
Activity 8.17: Using Nested Functions
Activity 8.18: Using the AVERAGEIFS Function and Inserting a Shape Containing Text
Activity 8.19: Using the IFS Function
GO! To Work
End of Chapter
Project Guide for Excel Chapter 8
Excel Chapter 8 Key Terms
Chapter Review
Skills Review: Project 8D Charlotte Staffing
Content-Based Assessments (Mastery and Transfer of Learning)
Mastering Excel: Project 8F Seafood Chowder
Mastering Excel: Project 8G Income Model
Content-Based Assessments (Critical Thinking)
Outcome-Based Assessments (Critical Thinking)
9: Using Macros and Visual Basic for Applications
Introduction: Using Macros and Visual Basic for Applications
Project 9A: Travel Expenses
Objective 1: Record a Macro
Activity 9.02: Changing the Macro Security Settings in Excel
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.06: Testing the Macro Button
Objective 3: Modify a Macro
Project 9B: Expense Report
Objective 4: Write a VBA Procedure to Use an ActiveX Control
Activity 9.09: Changing the Properties of an ActiveX Control
Activity 9.10: Writing a VBA Procedure for a Command Button
Activity 9.11: Modifying a VBA Procedure
Activity 9.12: Testing the VBA Procedure and the ActiveX Control
Objective 5: Restore Initial Settings
GO! To Work
End of Chapter
Project Guide For Excel Chapter 9
Excel Chapter 9 Key Terms
Chapter Review
Skills Review: Project 9D Spring Sections
Content-Based Assessments (Mastery and Transfer of Learning)
Mastering Excel: Project 9F Evaluation
Mastering Excel: Project 9G Billing
Content-Based Assessments (Critical Thinking)
Outcome-Based Assessments (Critical Thinking)
10: External Data, Database Functions, Side-by-Side Tables, and Workbook Distribution and Collaboration
Introduction: External Data, Database Functions, Side-by-Side Tables, and Workbook Distribution and Collaboration
Project 10A: Medical Center Information
Objective 1: Get External Data into Excel
Activity 10.02: Hide Worksheets, Hide Rows and Columns, and Modify Cell Alignment and Indentation
Activity 10.03: Importing Data into Excel from an XML File, Adding and Removing Table Rows and Columns, and Converting a Table to a Cell Range
Activity 10.04: Importing Data into Excel from a Delimited Text File
Objective 2: Clean Up and Manage Data
Activity 10.06: Using Flash Fill to Expand Data
Activity 10.07: Using Text Functions
Objective 3: Use Database Functions
Activity 10.09: Using the DSUM Database Function
Activity 10.10: Using the DCOUNT Function
Activity 10.11: Copying a Worksheet and Using the MAXIFS Function
Project 10B: Office Equipment Inventory
Objective 4: Insert a Second Table into a Worksheet
Activity 10.13: Sorting Side-by-Side Tables
Objective 5: Manage and Use Formulas in Conditional Formatting Rules
Objective 6: Create Custom Headers and Footers
Objective 7: Inspect a Workbook
Activity 10.17: Ensuring Backward-Compatibility in a Workbook
Activity 10.18: Inspecting a Document
Objective 8: Use Co-Authoring and Prepare a Workbook for Final Distribution
Activity 10.20: Encrypting a Workbook and Protecting Workbook Structure
Activity 10.21: Marking a Workbook as Final
GO! To Work
End of Chapter
Project Guide for Excel Chapter 10
Excel Chapter 10 Key Terms
Chapter Review
Skills Review: Project 10D Medical Supplies
Content-Based Assessments (Mastery and Transfer of Learning)
Mastering Excel: Project 10F Beverage Supplies
Mastering Excel: Project 10G Pharmacy Department
Content-Based Assessments (Critical Thinking)
Outcomes-Based Assessments (Critical Thinking)
Appendix
Appendix
Glossary
Glossary
Blank Page