Exploring Microsoft Office Excel 2019 Comprehensive, 1st Edition by Poatsy Test Bank

Document Content and Description Below

Test Bank for Exploring Microsoft Office Excel 2019 Comprehensive, 1st Edition, 1e by Mary Anne Poatsy, Keith Mulbery TEST BANK ISBN-13: 9780135452752 Full chapters included The Exploring Series ... and You New to This Edition Series Hallmarks The Exploring Series and MyLab IT Solving Teaching and Learning Challenges Developing Employability Skills Office CHAPTER 1 Common Features Taking the First Step Office 365 Common Features Objectives & Skills: Get Started with Office Applications Format Document Content Modify Document Layout and Properties Case Study Spotted Begonia Art Gallery Get Started with Office Applications Starting an Office Application Use Your Microsoft Account Use OneDrive Step 1 Working with Files Create a New File Save a File Step 2 Open a Saved File Using Common Interface Components Use the Ribbon Step 3 Use a Dialog Box and Gallery Customize the Ribbon Step 4 Use and Customize the Quick Access Toolbar Step 5 Use a Shortcut Menu Use Keyboard Shortcuts Getting Help Step 6 Use the Tell Me Box Use the Help Tab Use Enhanced ScreenTips Installing Add-ins Hands-On Exercises 1 Get Started with Office Applications Step 1 Open And Save A File Step 2 Open A Saved File and Use the Ribbon Step 3 Use a Dialog Box and Gallery Step 4 Use and Customize the Quick Access Toolbar Step 5 Use a Shortcut Menu Step 6 Use the Tell Me Box Format Document Content Using Templates and Applying Themes Step 1 Open a Template Step 2 Apply a Theme Modifying Text Step 3 Select Text Format Text Use the Mini Toolbar Relocating Text Step 4 Cut, Copy, and Paste Text Use the Office Clipboard Reviewing a Document Step 5 Check Spelling and Grammar Working with Pictures Step 6 Insert Pictures Modify a Picture Hands-On Exercises 2 Format Document Content Step 1 Open a Template Step 2 Apply a Theme Step 3 Select and Format Text Step 4 Cut, Copy, and Paste Text Step 5 Check Spelling and Grammar Step 6 Insert a Picture Step 7 Modify a Picture Modify Document Layout and Properties Step 1 Changing Document Views Change Document Views Using the Ribbon Change Document Views Using the Status Bar Step 2 Changing the Page Layout Change Margins Change Page Orientation Use the Page Setup Dialog Box Step 3 Creating a Header and a Footer Step 4 Configuring Document Properties View and Edit Document Properties Step 5 Previewing and Printing a File Hands-On Exercises 3 Modify Document Layout and Properties Step 1 Change the Document View Step 2 Change the Page Layout Step 3 Insert a Header and a Footer Step 4 Enter Document Properties Step 5 Preview a File and Change Print Settings Chapter Objectives Review Key Terms Matching Multiple Choice Practice Exercises 1 Designing Webpages 2 Upscale Bakery Mid-Level Exercises 1 Reference Letter 2 Medical Monitoring Running Case New Castle County Technical Services Disaster Recovery Resume Enhancement Capstone Exercise Open and Save Files Apply a Theme and Change the View Select Text, Move Text, and Format Text Insert and Modify a Picture Use the Tell me Box Insert Header and Footer Customize the Quick Access Toolbar Check Spelling and Change View Use Print Preview, Change Print Layout, and Adjust Document Properties Excel Excel Chapter 1 Introduction to Excel Creating and Formatting a Worksheet Objectives & Skills: Introduction to Spreadsheets Mathematical Operations and Formulas Worksheet Structure and Clipboard Tasks Worksheet Formatting Worksheet Management, Page Setup, and Printing Case Study Celebrity Musician’s Souvenir Shop Introduction to Spreadsheets Exploring the Excel Window Identify Excel Window Elements Identify Columns, Rows, and Cells Navigate in and Among Worksheets Entering and Editing Cell Data Plan the Worksheet Design Enter and Format the Data Complete the Workbook Step 1 Enter Text Check the Spelling in a Worksheet Step 2 Use Auto Fill to Complete a Sequence Step 3 Enter Values Enter Dates and Times Edit and Clear Cell Contents Step 4 Find and Replace Data Hands-On Exercises 1 Introduction to Spreadsheets Step 1 Enter Text and Check the Spelling in a Worksheet Step 2 Use Auto Fill to Complete a Sequence Step 3 Enter Values, Enter Dates, and Clear Contents Step 4 Find and Replace Data Mathematical Operations and Formulas Creating Formulas Step 1 Use Cell References in Formulas Copy a Formula Use Semi-Selection to Create a Formula Step 2 Apply the Order of Operations Step 3 Display Cell Formulas Hands-On Exercises 2 Mathematical Operations and Formulas Step 1 Use Cell References in a Formula, Copy a Formula, and Use Semi-Selection to Create a Formula APPLY THE ORDER OF OPERATIONS Step 3 Display Cell Formulas Worksheet Structure and Clipboard Tasks Managing Columns and Rows Step 1 Insert Cells, Columns, and Rows Step 2 Delete Cells, Columns, and Rows Step 3 Adjust Column Width Adjust Row Height Hide and Unhide Columns and Rows Selecting, Moving, Copying, and Pasting Data Step 4 Select a Range Move a Range Step 5 Copy and Paste a Range Step 6 Use Paste Options Hands-On Exercises 3 Worksheet Structure and Clipboard Tasks Step 1 Insert a Column Step 2 Insert and Delete Rows Step 3 Adjust Column Width, Adjust Row Height, and Hide a Column Step 4 Select a Range and Move a Range Step 5 Copy and Paste a Range Step 6 Use Paste Options Worksheet Formatting Applying Cell Styles, Cell Alignment, and Font Options Step 1 Apply Cell Styles Merge Cells Step 2 Change Cell Alignment Wrap Text Step 3 Increase and Decrease Indent Step 4 Apply Borders and Fill Color Applying Number Formats Step 5 Apply a Number Format Increase and Decrease Decimal Places Hands-On Exercises 4 Worksheet Formatting Step 1 Apply a Cell Style and Merge Cells Step 2 Change Cell Alignment and Wrap Text Step 3 Increase Indent Step 4 Apply a Border and Fill Color Step 5 Apply Number Formats and Increase and Decrease Decimal Places Worksheet Management, Page Setup, and Printing Managing Worksheets Insert and Delete a Worksheet Step 1 Move or Copy a Worksheet Rename a Worksheet Selecting Page Setup Options Step 2 Specify Page Options Set Margin Options Step 3 Create a Header or Footer Select Sheet Options Step 4 Previewing and Printing a Worksheet Hands-On Exercises 5 Worksheet Management, Page Setup, and Printing Step 1 Copy, Move, and Rename a Worksheet SET PAGE ORIENTATION, SCALING, AND MARGIN OPTIONS Step 3 Create a Header Step 4 View in Print Preview and Print Chapter Objectives Review Key Terms Matching Multiple Choice Practice Exercises 1 Mathematics and Formatting 2 Calendar Formatting Mid-Level Exercises 1 Guest House Rental Rates 2 Real Estate Sales Report Running Case New Castle County Technical Services Disaster Recovery Net Proceeds from House Sale Capstone Exercise Format the Title and Enter the Date Format Seating Labels Format Labels, Replace Text, and Checking Spelling Insert Formulas and Apply Number Formats Move a Column, Adjust Alignment, and Add Borders Format the Worksheet Excel Chapter 2 Performing Quantitative Analysis Formulas and Functions Objectives & Skills: Formula Basics Function Basics Logical, Lookup, and Financial Functions Case Study Townsend Mortgage Company Formula Basics Using Relative, Absolute, and Mixed Cell References in Formulas Step 1 Use a Relative Cell Reference Step 2 Use an Absolute Cell Reference Step 3 Use a Mixed Cell Reference Hands-On Exercises 1 Formula Basics Step 1 Use A Relative Cell Reference Step 2 Use an Absolute Cell Reference Step 3 Use a Mixed Cell Reference Function Basics Inserting a Function Inserting Basic Math and Statistics Functions Step 1 Use the SUM Function Step 2 Use the AVERAGE Function Step 3 Use the Median Function Step 4 Use the MIN and MAX Functions Use the COUNT Functions Perform Calculations with Quick Analysis Tools Using Date Functions Step 5 Use the TODAY Function Use the NOW Function Hands-On Exercises 2 Function Basics Step 1 Use the Sum Function Step 2 Use the Average Function Step 3 Use the Median Function Step 4 Use the Min, Max, and Count Functions Step 5 Use the Today Function Logical, Lookup, and Financial Functions Using Lookup Functions Step 1 Use the VLOOKUP function Create the Lookup Table Use the HLOOKUP Function Step 2 Using the PMT Function Step 3 Using the IF Function Design the Logical Test Design the Value_If_True and Value_If_False Arguments Hands-On Exercises 3 Logical, Lookup, and Financial Functions Step 1 Use the Vlookup Function Step 2 Use the Pmt Function Step 3 Use the IF Function Chapter Objectives Review Key Terms Matching Multiple Choice Practice Exercises 1 Hamilton Heights Auto Sales 2 Garten Realty Mid-Level Exercises 1 Metropolitan Zoo Gift Shop Weekly Payroll 2 Smartphone Upgrade Running Case New Castle County Technical Services Disaster Recovery Auto Finance Capstone Exercise Insert Current Date Create Item Lookup Determine Delivery Fee Calculate the Monthly Payment Finalize the Workbook Excel Chapter 3 Charts: Depicting Data Visually OBJECTIVES & SKILLS: Chart Basics Chart Elements Chart Design and Sparklines Case Study Computer Job Outlook Chart Basics Step 1 Creating a Basic Chart Select the Data Source Use the Insert Tab to Create a Chart Create a Column Chart Move a Chart Size a Chart Distribute a Chart Using Other Methods to Create Charts Step 2 Create a Recommended Chart Change the Chart Type Step 3 Create a Chart with Quick Analysis Create a Chart with Ideas Creating Other Charts Create a Bar Chart Create a Line Chart Create a Pie Chart Step 4 Create a Combo Chart Create Other Types of Charts Hands-On Exercises 1 Chart Basics Step 1 Create a Basic Chart Step 2 Create a Recommended Chart and Change the Chart Type Step 3 Create a Chart With Quick Analysis Step 4 Create a Combo Chart Chart Elements Adding and Formatting Chart Elements Apply a Quick Layout Step 1 Edit, Position, and Format Chart Titles Step 2 Add and Format Axis Titles Format the Axes Step 3 Add, Position, and Format Data Labels Position and Format the Legend Add and Format Gridlines Step 4 Format the Chart Area, Plot Area, and Data Series Include Alt Text Format a Data Point Hands-On Exercises 2 Chart Elements Step 1 Edit and Format Chart Titles Step 2 Add and Format Axis Titles and Format the Axes Step 3 Add and Format Data Labels Step 4 Format the Chart Area, Add Alt Text, and Format a Data Point Chart Design and Sparklines Step 1 Applying a Chart Style and Colors Save a Chart as a Template Modifying the Data Source Step 2 Change the Data Source Switch Row and Column Data Apply Chart Filters Creating and Customizing Sparklines Insert a Sparkline Customize a Sparkline Hands-On Exercises 3 Chart Design and Sparklines Step 1 Apply a Chart Style Step 2 Change the Data Source and Switch Row and Column Data Step 3 Insert and Customize Sparklines Chapter Objectives Review Key Terms Matching Multiple Choice Practice Exercises 1 Loza Family Utility Expenses 2 Trends in Market Value of Houses on Pine Circle Mid-Level Exercises 1 Airport Passenger Counts 2 Grade Analysis Running Case New Castle County Technical Services Disaster Recovery Harper County Houses Sold Capstone Exercise Create and Move a Basic Chart Create a Chart and Apply Filter Add and Format Chart Elements Create and Modify a Column Chart Create and Format a Line Chart Add Alt Text Insert Sparklines Excel Chapter 4 Managing Large Volumes of Data Datasets and Tables Objectives & Skills: Large Datasets Excel Tables Table Manipulation Table Aggregation and Conditional Formatting Case Study Reid Furniture Store Large Datasets Step 1 Freezing Rows and Columns Printing Large Datasets Step 2 Display and Change Page Breaks Step 3 Set and Clear a Print Area Step 4 Print Titles Control Print Page Order Hands-On Exercises 1 Large Datasets Step 1 Freeze Rows and Columns Step 2 Display and Change Page Breaks Step 3 Set and Clear a Print Area Step 4 Print Titles Excel Tables Exploring the Benefits of Data Tables Designing and Creating Tables Create a Table Step 2 Rename a Table Step 3 Add and Delete Fields Step 4 Add, Edit, and Delete Records Step 5 Remove Duplicate Rows Step 6 Applying a Table Style Hands-On Exercises 2 Excel Tables Step 1 Create a Table Step 2 Rename the Table Step 3 Add and Delete Fields ADD RECORDS Step 5 Remove Duplicate Rows Step 6 Apply a Table Style Table Manipulation Step 1 Creating Structured References in Formulas Sorting Data Step 2 Sort One Field Step 3 Sort Multiple Fields Step 4 Create a Custom Sort Filtering Data Clear Filters Step 1 Apply Text Filters Step 4 Apply Number Filters Step 3 Apply Date Filters Apply a Custom Filter Hands-On Exercises 3 Table Manipulation Step 1 Create a Structured Reference in a Formula Step 2 Sort one Field Step 3 Sort Multiple Fields CREATE A CUSTOM SORT Step 5 Apply Text Filters Step 6 Apply a Number Filter Step 7 Apply a Date Filter Table Aggregation and Conditional Formatting Adding a Total Row to a Table Applying Conditional Formatting Apply a Highlight Cells Rules Specify Top/Bottom Rules Display Data Bars, Color Scales, and Icon Sets Creating a New Conditional Formatting Rule Manage Rules Use Formulas in Conditional Formatting Hands-On Exercises 4 Table Aggregation and Conditional Formatting ADD A TOTAL ROW Step 7 Apply Highlight Cells Rules Step 6 Specify Top/Bottom Rules DISPLAY DATA BARS CREATE A NEW CONDITIONAL FORMATTING RULE Chapter Objectives Review Key Terms Matching Multiple Choice Practice Exercises 1 Institute for Study Abroad 2 Sunny Popcorn, Inc. Mid-Level Exercises 1 Crafton’s Pet Supplies 2 Riverwood Realty Running Case New Castle County Technical Services Disaster Recovery Dairy Farm Capstone Exercise Prepare the Large Worksheet as a Table Add a Structured Reference and a Total Row Sort and Filter the Table Apply Conditional Formatting Prepare the Worksheet for Printing Excel Chapter 5 Subtotals, PivotTables, and PivotCharts Summarizing and Analyzing Data Objectives & Skills: Subtotals and Outlines PivotTable Basics PivotTable Options Data Modeling and PivotCharts Case Study Ivory Halls Publishing Company Subtotals and Outlines Step 1 Subtotaling Data Step 2 Add a Second Subtotal Step 3 Collapse and Expand the Subtotals Step 4 Grouping and Ungrouping Data Hands-On Exercises 1 Subtotals and Outlines Step 1 Subtotal Data Step 2 Add a Second Subtotal Step 3 Collapse and Expand the Subtotals Step 4 Group Data PivotTable Basics Creating a PivotTable Step 1 Create a Recommended PivotTable Create a Blank PivotTable Step 2 Add Rows, Values, and Columns Modifying a PivotTable Collapse and Expand Categories Step 3 Remove Fields Rearrange Fields Step 4 Change Values Field Settings Step 5 Use the GETPIVOTDATA Function Refresh a PivotTable Hands-On Exercises 2 PivotTable Basics Step 1 Create a Recommended Pivottable Step 2 Add Rows, Values, and Columns Step 3 Remove and Rearrange Fields Step 4 Change the Values Field Settings Step 5 Use Getpivotdata Function and Refresh a Pivottable PivotTable Options Sorting, Filtering, and Slicing a PivotTable Sort Data within a Field Step 1 Add Filters Step 2 Insert a Slicer Customize a Slicer Insert a Timeline to Filter a PivotTable Group PivotTable Data Step 3 Creating a Calculated Field Step 4 Show Values as Calculations Changing the PivotTable Design Step 5 Change the PivotTable Style Change the PivotTable Layout Hands-On Exercises 3 PivotTable Options Step 1 Add Filters Step 2 Insert and Customize a Slicer Step 3 Create a Calculated Field Step 4 Show Values as Calculations Step 5 Change the Pivottable Style Data Modeling and PivotCharts Creating a Data Model Step 1 Create Relationships Step 2 Create a PivotTable from Related Tables Step 3 Creating a PivotChart Step 4 Modify the PivotChart Hands-On Exercises 4 Data Modeling and PivotCharts Step 1 Create Relationships Step 2 Create a Pivottable From Related Tables Step 3 Create a Pivotchart Step 4 Modify the Pivotchart Chapter Objectives Review Key Terms Matching Multiple Choice Practice Exercises 1 January Restaurant Revenue 2 Lake View Regional Hospital Mid-Level Exercises 1 Mountain View Realty 2 Fiesta Collection Running Case New Castle County Technical Services Disaster Recovery Innovative Game Studio Capstone Exercise Apartment Complexes Sort, Subtotal, and Outline Data Create a PivotTable Change Value Field Settings and Create a Calculated Field Apply a Style, Insert a Slicer, and Insert a Timeline Create a Relationship Between Tables, Create a PivotTable, and Create a PivotChart Finalizing Your Workbook Excel Chapter 6 Using Decision-Making Tools What-If Analysis Objectives & Skills: Range Names One- and Two-Variable Data Tables Goal Seek and Scenario Manager Solver Case Study Personal Finance: Buying Your First Home Case Study Personal Finance: Buying Your First Home Range Names Creating and Maintaining Range Names Step 1 Define a Range Name Step 2 Edit or Delete a Range Name Step 3 Use Range Names in Formulas Step 4 Insert a List of Range Names Hands-On Exercises 1 Range Names Step 1 Create a Range Name Step 2 Edit and Delete Range Names Step 3 Use a Range Name in a Formula Step 4 Insert a List of Range Names One- and Two-Variable Data Tables Creating a One-Variable Data Table Step 1 Set Up One-Variable Substitution Values Step 2 Add Formulas to a One-Variable Data Table Step 3 Calculate Results for a One-Variable Data Table Step 4 Format a One-Variable Data Table Creating a Two-Variable Data Table Step 5 Set Up Two-Variable Substitution Values Add a Formula to the Data Table Step 6 Calculate Results for a Two-Variable Data Table Hands-On Exercises 2 One- and Two-Variable Data Tables Step 1 Set up One-Variable Substitution Values Step 2 Add Formulas to a One-Variable Data Table Step 3 Calculate Results for a One-Variable Data Table Step 4 Format a One-Variable Data Table Step 5 Substitution Values and Add a Formula to the Data Table Step 6 Calculate Results for a Two-Variable Data Table Goal Seek and Scenario Manager Step 1 Determining Optimal Input Values Using Goal Seek Using Scenario Manager Step 2 Create and Add Scenarios Step 3 View and Edit Scenarios Step 4 Generate a Scenario Summary Report Hands-On Exercises 3 Goal Seek and Scenario Manager Step 1 Determine Optimal Input Values Using Goal Seek Step 2 Create and Add Scenarios Step 3 View and Edit Scenarios Step 4 Generate a Scenario Summary Report Solver Step 1 Loading the Solver Add-In Optimizing Results with Solver Step 2 Identify the Objective Cell and Changing Cells Step 3 Define Constraints Step 4 Create a Solver Report Configure Solver Save and Restore a Solver Model Hands-On Exercises 4 Solver Step 1 Load the Solver Add-In Step 2 Identify the Objective Cell and Changing Cells Step 3 Define Constraints CREATE A SOLVER REPORT Chapter Objectives Review Key Terms Matching Multiple Choice Practice Exercises 1 Monthly Commission 2 Burton’s Sandwich Shop Mid-Level Exercises 1 Housing Construction Cost Variables 2 Indy Deck Builders Running Case New Castle County Technical Services Disaster Recovery IT Management Capstone Exercise Range Names Goal Seek One-Variable Data Table Two-Variable Data Table Scenario Manager Use Solver Excel Chapter 7 Specialized Functions Using Date, Logical, Statistical, and Financial Functions Objectives & Skills: Date and Logical Functions Conditional Math and Statistical Functions Financial Functions Case Study Home Protection, Inc. Case Study Home Protection, Inc. Date and Logical Functions Using Date Functions Step 1 Use the DAY, MONTH, and YEAR Functions Use the DAYS and YEARFRAC Functions Step 2 Use the WEEKDAY Function Use Other Date Functions Using Advanced Logical Functions Step 3 Use the SWITCH Function Step 4 Use the IFS Function Nest IF within an IF Function Use AND, OR, and NOT Functions Step 5 Nest AND, OR, and NOT Functions within an IF Function Hands-On Exercises 1 Date and Logical Functions Step 1 Use the YEAR and YEARFRAC Functions Step 2 Use the WEEKDAY Function Step 3 Use the SWITCH Function Step 4 Use the IFS Function Step 5 NEST an AND Function within an IF Function Conditional Math and Statistical Functions Using Functions to Evaluate One Condition Step 1 Use the COUNTIF Function Step 2 Use the SUMIF Function Step 3 Use the AVERAGEIF Function Using Functions to Evaluate Multiple Conditions Step 4 Use the COUNTIFS, SUMIFS, and AVERAGEIFS Functions Use the MAXIFS and MINIFS Functions Step 5 Inserting a Map Hands-On Exercises 2 Conditional Math and Statistical Functions Step 1 Use the COUNTIF Function Step 2 Use the SUMIF Function Step 3 Use the AVERAGEIF Function Step 4 Use the COUNTIFS, SUMIFS, AVERAGEIFS, MAXIFS, and MINIFS Functions Step 5 Insert a Map Financial Functions Using Financial Functions Step 1 Use the PV, FV, and NPV Functions Use the NPER and RATE Functions Creating a Loan Amortization Table Step 2 Insert Formulas in a Loan Amortization Table Calculate Interest and Principal Payments with IPMT and PPMT Functions Using Cumulative Financial Functions Step 3 Calculate Cumulative Interest with the CUMIPMT Function Step 4 Calculate Cumulative Principal Payments with the CUMPRINC Function Hands-On Exercises 3 Financial Functions Step 1 Use the PV Function Step 2 Insert Formulas in a Loan Amortization Table Step 3 Calculate Cumulative Interest with the CUMIPMT Function Step 4 Calculate Cumulative Principal with the CUMPRINC Function Chapter Objectives Review Key Terms Matching Multiple Choice Practice Exercises 1 Furniture Sales 2 Loan Analysis Mid-Level Exercises 1 University Admissions Office 2 Personal Financial Management Running Case New Castle County Technical Services Disaster Recovery Home Studio Recording Equipment Capstone Exercise Real Estate Company Perform Calculations Calculate Cumulative Statistics for One Condition Calculate Cumulative Statistics for Two Conditions Insert a Map Loan Amortization Workbook Completion Excel Chapter 8 Analyzing Statistics Statistical Functions Objectives & Skills: Descriptive Statistical Functions Inferential Statistics Trend Analysis Case Study Education Evaluation Case Study Education Evaluation Descriptive Statistical Functions Measuring Central Tendency Step 1 Use the Standard Deviation and Variance Functions Step 2 Use the CORREL Function Step 3 Use the FREQUENCY Function Use the LARGE and SMALL Functions Use the RANK and PERCENTRANK Functions Use the QUARTILE and PERCENTILE Functions Hands-On Exercises 1 Descriptive Statistical Functions Step 1 Calculate Standard Deviation and Variance Step 2 Use the Correl Function Step 3 Use the Frequency Function Inferential Statistics Step 1 Loading the Analysis ToolPak Performing Analysis Using the Analysis ToolPak Step 2 Perform Analysis of Variance (ANOVA) Step 3 Calculate COVARIANCE Step 4 Create a Histogram Step 5 Creating a Forecast Sheet Hands-On Exercises 2 Inferential Statistics Step 1 Load the Analysis Toolpak Step 2 Perform Analysis of Variance (Anova) Step 3 Calculate Covariance Step 4 Create a Histogram Step 5 Create a Forecast Sheet Trend Analysis Performing Analysis Using Trendlines Step 1 Add a Linear Trendline to a Chart Using Statistical Functions to Perform Trend Analysis Step 2 Use the INTERCEPT and SLOPE Functions Step 3 Use the RSQ and STEYX Functions Step 4 Use the FORECAST Function Hands-On Exercises 3 Trend Analysis Step 1 Add a Trendline to a Chart Step 2 Use the Intercept and Slope Functions Step 3 Use The RSQ and STEYX Functions Step 4 Use the Forecast Function Chapter Objectives Review Key Terms Matching Multiple Choice Practice Exercises 1 Grind Stone Coffee 2 Indy Car Mid-Level Exercises 1 Portfolio Analysis 2 Reading Comprehension Scores Running Case New Castle County Technical Services Disaster Recovery College Admissions Capstone Exercise Measure Central Tendency Use the Analysis ToolPak and Forecast Sheet Perform Trend Analysis Excel Chapter 9 Multiple-Sheet Workbook Management Ensuring Quality Control Objectives & Skills: Multiple Worksheets 3-D Formulas and Linked Workbooks Formula Audits and Data Validation Workbook Protection Case Study Circle City Sporting Goods Multiple Worksheets Working with Grouped Worksheets Step 1 Group and Ungroup Worksheets Fill Across Worksheets Step 2 Enter and Format Data on Grouped Worksheets Step 3 Inserting Hyperlinks Managing Windows Hide and Unhide Worksheets Step 4 Open and Arrange Windows Split a Window Apply Other Window Settings Hands-On Exercises 1 Multiple Worksheets Step 1 Group and Fill Across Worksheets Step 2 Enter and Format Data on Grouped Worksheets Step 3 Insert Hyperlinks Step 4 Open and Arrange Windows 3-D Formulas and Linked Workbooks Inserting Formulas and Functions with 3-D References Step 1 Insert a Formula with a 3-D Reference Step 2 Insert a Function with a 3-D Reference Linking Workbooks Step 3 Create a Link to Another Workbook Manage and Update Linked Workbooks Hands-On Exercises 2 3-D Formulas and Linked Workbooks Step 1 Insert a Formula With a 3-D Reference Step 2 Insert a Function with a 3-D Reference Step 3 Create a Link to Another Workbook Formula Audits and Data Validation Auditing Formulas Step 1 Trace Precedents and Dependents Step 2 Check for and Repair Errors Evaluate a Formula Use the IFERROR Function to Detect Errors Step 3 Setting Up a Watch Window Step 4 Creating a Data Validation Rule Specify Data Validation Criteria Create an Input Message Create an Error Alert Step 5 Test the Data Validation Rule Hands-On Exercises 3 Formula Audits and Data Validation Step 1 Trace Precedents and Dependents Step 2 Check for and Repair Errors Step 3 Set up a Watch Window Step 4 Create a Data Validation Rule Step 5 Test the Data Validation Rule Workbook Protection Protecting Cells and Worksheets Step 1 Lock and Unlock Cells Step 2 Protect a Worksheet Protecting a Workbook Protect the Structure of a Workbook Step 3 Encrypt a Workbook File with a Password Step 4 Mark a Workbook as Final Hands-On Exercises 4 Workbook Protection Step 1 Unlock Cells Step 2 Protect a Worksheet Step 3 Encrypt a Workbook File With a Password Step 4 Mark a Workbook as Final Chapter Objectives Review Key Terms Matching Multiple Choice Practice Exercises 1 Organic Foods Corporation 2 Retirement Planning Mid-Level Exercises 1 Personal Expenses 2 Pizza Sales Running Case New Castle County Technical Services Disaster Recovery Gradebook Errors Capstone Exercise Housecleaning Service Group Worksheets Check for and Repair Errors Link Workbooks Create 3-D References Create Hyperlinks Finalize the Workbook Excel Chapter 10 Power Add-Ins Managing Data Objectives & Skills: Get and Transform Data Power Pivot 3D Maps Case Study Stock Analysis Case Study Stock Analysis Get and Transform Data Importing Data from an External Source Get Data from a Text/CSV File Import an Access Database Table or Query Import Data from Other Sources Transform Data Managing Queries and Connections Set External Data Properties Edit Query Properties Hands-On Exercises 1 Get and Transform Data IMPORT A TEXT FILE GET DATA FROM AN ACCESS DATABASE TRANSFORM DATA SET EXTERNAL DATA PROPERTIES Power Pivot Loading the Power Pivot Add-In Adding Data to a Data Model Create Relationships with Power Pivot Creating a PivotTable and PivotChart Using Power Pivot Hands-On Exercises 2 Power Pivot LOAD the Power Pivot Add-In ADD DATA TO A DATA MODEL CREATE RELATIONSHIPS CREATE A PIVOTTABLE USING POWER PIVOT 3D Maps Using 3D Maps Add Dimensions Create a Tour Hands-On Exercises 3 3D Maps CREATE A 3D MAP ADD DIMENSIONS CREATE A TOUR Chapter Objectives Review Key Terms Matching Multiple Choice Practice Exercises 1 Earline’s Bakery 2 Movie Rentals Mid-Level Exercises 1 Dow Jones Industrial Average 2 Flight Plan Running Case New Castle County Technical Services Disaster Recovery Personal Book Library Capstone Exercise Oxford Auto Sales Get and Transform Data Transform Data Add Data to a Data Model Create Relationships Create a PivotTable Using Power Pivot Create a 3D Map Excel Chapter 11 Using Text, Database, and Lookup Functions Additional Specialized Functions Objectives & Skills: Text Manipulation Database Filtering and Functions Lookup Functions Case Study Halloween Center Text Manipulation Filling and Separating Text in Columns Step 1 Use Flash Fill Step 2 Convert Text to Columns Manipulating Text with Functions Step 3 Use the CONCAT and TEXTJOIN Functions Step 4 Use the PROPER, UPPER, and LOWER Functions Use the LEFT, RIGHT, and MID Functions Use Other Text Functions Hands-On Exercises 1 Text Manipulation Step 1 Use Flash Fill Step 2 Convert Text to Columns Step 3 Use the TEXTJOIN Function Step 4 Use the PROPER and LEFT Functions Database Filtering and Functions Applying Advanced Filtering Step 1 Define the Criteria and Output Ranges Step 2 Apply an Advanced Filter Manipulating Data with Database Functions Step 3 Use the DSUM and DAVERAGE Functions Step 4 Use the DMAX and DMIN Functions Use the DCOUNT and DCOUNTA Functions Use the DGET Function Hands-On Exercises 2 Database Filtering and Functions Step 1 Define the Criteria and Output Ranges Step 2 Apply an Advanced Filter Step 3 Use the DSUM and DAVERAGE Functions Step 4 Use the DMAX, DMIN, DCOUNT, and dget Functions Lookup Functions Using Advanced Lookup Functions Step 1 Use the MATCH Function Step 2 Use the INDEX Function Use the CHOOSE Function Step 3 Use the FORMULATEXT Function Step 4 Use the TRANSPOSE Function Hands-On Exercises 3 Lookup Functions Step 1 Use the MATCH Function Step 2 Use the INDEX Function Step 3 Use the FORMULATEXT Function Step 4 Use the TRANSPOSE Function Chapter Objectives Review Key Terms Matching Multiple Choice Practice Exercises 1 Home Protection, Inc. Employees 2 Tech Support Calls Mid-Level Exercises 1 Student Internship Program 2 Innovations Game Studio Running Case New Castle County Technical Services Disaster Recovery Houses Sold Capstone Exercise University Donors List Extract and Join Test Fill in Columns Look Up Area Advanced Database Filtering Insert Database Functions Workbook Completion Excel Chapter 12 Templates, Workbook Inspection, and Macros Standardizing Workbooks Objectives & Skills: Templates Checking for Issues and Workbook Documentation Macros Visual Basic for Applications Case Study Wilhelmina’s Total Lawn Care Case Study Wilhelmina’s Total Lawn Care Templates Step 1 Selecting a Template Step 2 Edit an Existing Template Step 3 Creating a Template Hands-On Exercises 1 Templates Step 1 Select a Template Step 2 Edit an Existing Template Step 3 Create a Template Checking for Issues and Workbook Documentation Checking for Issues Step 1 Use Document Inspector Step 2 Check Accessibility Step 3 Check Compatibility Step 4 Annotating a Worksheet Step 5 Edit and Delete Annotations Print Annotations Step 6 Show and Hide Annotations Hands-On Exercises 2 Checking for Issues and Workbook Documentation Step 1 Use the Document Inspector Step 2 Check Accessibility Step 3 Check Compatibility Step 4 ANNOTATE A Worksheet Step 5 Edit and Delete Comments Step 6 Show and Hide Comments Macros Creating a Macro Step 1 Use the Macro Recorder Use Relative References Step 2 Run a Macro Set Macro Security Step 3 Creating a Macro Button Hands-On Exercises 3 Macros Step 1 Use the Macro Recorder Step 2 Run a Macro Step 3 Add a Macro Button Visual Basic for Applications Step 1 Creating a Sub Procedure Use VBA with Protected Worksheets Step 2 Edit a Macro in the Visual Basic Editor Step 3 Creating a Custom Function Hands-On Exercises 4 Visual Basic for Applications Step 1 Create a Sub Procedure Step 2 Edit a Macro Step 3 Create a Custom Function Chapter Objectives Review Key Terms Matching Multiple Choice Practice Exercises 1 Time Card 2 Sales Management Mid-Level Exercises 1 Sorority Dues 2 Travel Expense Template Running Case New Castle County Technical Services Disaster Recovery Real Estate Listings Capstone Exercise Bulldog Collectables Select and Edit a Template Create the Sort Macro Create a Custom Function Protect the Workbook Add Comments Check for Issues Test and Finalize Template Application Capstone Exercise Excel Application Capstone Exercise (Chs. 1– 4) Format the Worksheet Insert Formulas and Basic Functions Insert Lookup, Conditional, and Payment Functions Create a Column Chart Create a Pie Chart Insert a Table, Sort and Filter Data, and Apply Conditional Formatting Select Page Setup Options Excel Comprehensive Capstone Exercise (Chs. 5–12) Format the Workbook Use Conditional Logic and Conditional Formatting Enter Specialized Functions and Data Validation Perform Advanced Filtering and Database Functions [Show More]

Last updated: 1 year ago

Preview 1 out of 229 pages