Go! with Excel 2007 Comprehensive

Chapter 1 Creating a Worksheet and Charting DataProject 1A Seattle PayrollObjective 1 Start Excel and Navigate a WorksheetActivity 1.1 Starting Excel and Naming and Saving a WorkbookActivity 1.2 Navigating a WorksheetObjective 2 Select Parts of a WorksheetActivity 1.3 Selecting Cells and Ranges and Entering Data 1Objective 3 Enter Data, Construct a Formula, and Use the SUM FunctionActivity 1.4 Opening an Existing Workbook, Entering Text, and UsingAutoCompleteActivity 1.5 Entering Numbers and Adjusting Column WidthActivity 1.6 Entering a Formula and Using the Sum FunctionObjective 4 Format Data, Cells, and WorksheetsActivity 1.7 Using Font Styles and Centering TextActivity 1.8 Using Merge and CenterObjective 5 Insert a Footer into a WorksheetActivity 1.9 Adding a Footer to a WorksheetObjective 6 Delete Unused Worksheets and Preview and Printa WorksheetActivity 1.10 Deleting Unused WorksheetsActivity 1.11 Previewing and Printing a WorksheetObjective 7 Print Formulas, Close a Workbook, and Exit ExcelActivity 1.12 Displaying and Printing Formulas, Closing a Workbook, and Exiting ExcelProject 1B Annual IncomeObjective 8 Check Spelling and Edit a WorksheetActivity 1.13 Opening and Saving an Existing WorkbookActivity 1.14 Using Wrap Text and the Undo CommandActivity 1.15 Checking for Spelling Errors in a WorksheetActivity 1.16 Editing Text in a WorksheetObjective 9 Enter Data by RangeActivity 1.17 Selecting Cells and Entering Data by RangeObjective 10 Create and Copy FormulasActivity 1.18 Using the Point-and-Click Method and Calculation Operatorsto Create a FormulaActivity 1.19 Using the Fill Handle to Copy a FormulaActivity 1.20 Determining PercentagesObjective 11 Use Format Painter and Chart DataActivity 1.21 Using Format PainterActivity 1.22 Charting DataActivity 1.23 Printing the Worksheet and its FormulasActivity 1.24 Using HelpChapter 2 Using Multiple-Sheet WorkbooksProject 2A Income From LodgingObjective 1 Use a Multiple-Sheet WorkbookActivity 2.1 Inserting a Worksheet and Entering and Formatting Data in a Multiple Sheet WorkbookActivity 2.2 Changing the Format of Worksheet Tabs and Using the TabScrolling ButtonsObjective 2 Enter a SeriesActivity 2.3 Entering a SeriesObjective 3 Copy and Paste Cell ContentsActivity 2.4 Using Copy and PasteActivity 2.5 Copying Data Between WorksheetsObjective 4 Use the Office ClipboardActivity 2.6 Using the Office Clipboard to Collect and Paste Data BetweenWorkbooksObjective 5 Total the Worksheet Range and Enter a Grand TotalActivity 2.7 Totaling the Worksheet in One Step and Correcting Formula ErrorsActivity 2.8 Creating a Grand Total in One StepObjective 6 Format a Multiple-Sheet Workbook GroupActivity 2.9 Formatting a Worksheet Group and Using Print PreviewObjective 7 Insert Columns and Rows in Multiple WorksheetsActivity 2.10 Inserting Columns in a WorksheetActivity 2.11 Inserting Rows That Adjust Relative References and ReviewingError MessagesProject 2B Hotel TaxesObjective 8 Copy a WorksheetActivity 2.12 Copying a Worksheet within a WorkbookObjective 9 Create Formulas with Absolute Cell References and Copy FormatsActivity 2.13 Creating and Copying a Formula That Uses an Absolute CellReferenceObjective 10 Find and Replace Text and Hide and Unhide ColumnsActivity 2.14 Using Find and ReplaceActivity 2.15 Hiding and Unhiding ColumnsObjective 11 Conduct a What-If Analysis and Use Statistical FunctionsActivity 2.16 Performing a What-If Analysis and Using Statistical FunctionsActivity 2.17 Planning and Preparing a WorksheetObjective 12 Create Accurate Worksheets with Accuracy ToolsActivity 2.18 Inserting a Worksheet into a WorkbookActivity 2.19 Using Undo and RedoActivity 2.20 Identifying and Correcting Errors in FormulasActivity 2.21 Formatting Page Placement and Adding Footersfor Multiple WorksheetsChapter 3 Working with IF Functions and Large WorksheetsProject 3A PayrollObjective 1 Construct an IF FunctionActivity 3.1 Preparing Regular Hours Worked in a Payroll Report Using anIF StatementActivity 3.2 Reporting Overtime Hours Worked with an IF FormulaActivity 3.3 Determining Payroll Deductions Using Absolute Referencesin FormulasActivity 3.4 Formatting the Worksheet and Merging and Centering a VerticalRange of CellsObjective 2 Link Data in WorkbooksActivity 3.5 Constructing Formulas That Refer to Cells in Another WorksheetObjective 3 Create IF Functions That Return TextActivity 3.6 Copying Cells and Creating an IF Formula to Enter Text UsingDrag-and-DropObjective 4Emphasize Data Using Conditional FormattingActivity 3.7 Highlighting Results with a Conditional FormatActivity 3.8 Correcting an Error and Reviewing ResultsObjective 5 Format with ThemesActivity 3.9 Formatting a Worksheet Using a ThemeActivity 3.10 Changing a Theme and FontObjective 6Add Information in the Header and FooterActivity 3.11 Editing the Footer and HeaderProject 3B EnrollmentsObjective 7 Enter DatesActivity 3.12 Entering a Date as TextObjective 8 Format Large WorksheetsActivity 3.13 Indenting Cell Contents and Using Zoom ControlActivity 3.14 Adding Vertical BordersActivity 3.15 Freezing PanesObjective 9 Apply Number FormatsActivity 3.16 Formatting NumbersActivity 3.17 Using a Theme on Large WorksheetsObjective 10 Control Print OptionsActivity 3.18 Printing Gridlines and HeadingsActivity 3.19 Previewing and Modifying Page BreaksActivity 3.20 Setting MarginsActivity 3.21 Inserting Page Numbers in a HeaderActivity 3.22 Setting the Print AreaChapter 4 Creating charts and Tables, Sorting and Filtering DataProject 4A CensusObjective 1 Use Text OrientationActivity 4.1 Rotating Text in CellsObjective 2 Create a Column ChartActivity 4.2 Creating a 3-D Column ChartObjective 3 Create a Chart Sheet and Edit the ChartActivity 4.3 Creating a Chart SheetActivity 4.4 Changing Chart Type and StyleActivity 4.5 Editing and Formatting a Chart TitleActivity 4.6 Editing and Formatting a Chart LegendActivity 4.7 Adding Axis Titles and Editing Worksheet DataObjective 4 Create and Modify a Pie ChartActivity 4.8 Creating a Pie ChartActivity 4.10 Rotating a Pie ChartActivity 4.11 Inserting a Text Box in a ChartObjective 5 Apply a Theme to a ChartActivity 4.12 Changing the Theme in a ChartObjective 6 Print ChartsActivity 4.13 Inserting Headers and Footers for Chart SheetsActivity 4.14 Printing a ChartProject 4B EmployeesObjective 7 Sort DataActivity 4.15 Sorting Alphabetical DataActivity 4.16 Sorting by NumbersActivity 4.17 Sorting by DateObjective 8 Convert Text into ColumnsActivity 4.18 Splitting Cell Contents into Multiple Cells and Copy and PasteActivity 4.19 Using the TRIM FunctionObjective 9 Apply Conditional Formatting Using Data Barsand Color ScalesActivity 4.20 Highlighting Cell RulesActivity 4.21 Using Top/Bottom RulesActivity 4.22 Formatting and Editing Data BarsActivity 4.23 Formatting and Editing Color ScalesObjective 10 Insert a Table and Filter DataActivity 4.24 Filtering a TableActivity 4.25 Filtering and Sorting on Two TablesActivity 4.26 Filtering by Using Text and Number FiltersActivity 4.27 Chart Filtered DataChapter 5 Making Decisions with FunctionsProject 5A Building PermitsObjective 1 Create Text FunctionsActivity 5.1 Using the PROPER FunctionObjective 2 Create Statistical FunctionsActivity 5.2 Using the MEDIAN and MODE Functions and Formula AutoComplete 462Activity 5.3 Counting CellsObjective 3 Insert Date and Time FunctionsActivity 5.4 Using Date and Time FunctionsActivity 5.5 Using Dates in a FormulaObjective 4 Create Logical Functions and Insert a CommentActivity 5.6 Using the AND FunctionActivity 5.7 Using the OR FunctionActivity 5.8 Inserting a CommentActivity 5.9 Printing CommentsProject 5B City FinancialObjective 5 Insert Financial FunctionsActivity 5.10 Creating an Amortization TableActivity 5.11 Using the PMT (Payment) FunctionActivity 5.12 Using the IPMT (Interest Payment) FunctionActivity 5.13 Using the PPMT (Principal Payment) Function and Determiningthe Ending BalanceActivity 5.14 Complete the Amortization ScheduleObjective 6 Create What-If Analysis with Goal SeekActivity 5.15 Using Goal Seek to Determine Length of LoanObjective 7 Determine Future Value and Present ValueActivity 5.16 Determining Present ValueActivity 5.17 Determining Future ValueChapter 6 Using Named Ranges, Templates, Lookup Values and 3-D ReferencesProject 6A Third QuarterObjective 1 Create Formulas Using Named RangesActivity 6.1 Defining and Using a Named CellActivity 6.2 Creating Names from Row and Column TitlesActivity 6.3 Creating Formulas Using Named RangesActivity 6.4 Inserting Rows Within a Range and Editing the WorksheetActivity 6.5 Using Named Cells to Create Quarterly TotalsObjective 2 Utilize Lookup ListsActivity 6.7 Creating a Lookup ListActivity 6.8 Looking Up Text in a Worksheet Using VLOOKUPActivity 6.9 Editing the Lookup ListActivity 6.10 Determining Sales Amounts Using the COUNTIF FunctionObjective 3 Customize and Use Microsoft-Created TemplatesActivity 6.11 Downloading and Personalizing a TemplateActivity 6.12 Completing a Worksheet Using a TemplateProject 6B Quarter 2 SalesObjective 4 Transpose Data in a Worksheet and Apply Cell StylesActivity 6.13 Transposing Data in a WorksheetActivity 6.14 Applying Cell Styles to a WorksheetActivity 6.15 Linking Worksheet Data Using a 3-D ReferenceActivity 6.16 Linking Workbook Data with a 3-D ReferenceActivity 6.17 Saving a Workspace and Editing Linked DataObjective 6 Create HyperlinksActivity 6.18 Inserting a Hyperlink in a WorksheetActivity 6.19 Inserting a Hyperlink in WorkbooksChapter 7 Importing Data, Expanding a Table, and utilizing Database Features Project 7A Closed CasesObjective 1 Create and Expand a Table and Insert a Calculated ColumnActivity 7.1 Creating and Expanding a TableActivity 7.2 Naming a TableActivity 7.3 Inserting a Calculated Column into the TableObjective 2 Create and Sort a Custom ListActivity 7.4 Creating a Custom ListObjective 3 Filter by Using Advanced CriteriaActivity 7.5 Filtering on Two Number CriteriaActivity 7.6 Filtering on a Calculated FieldObjective 4 Evaluate Data with Database FunctionsActivity 7.7 Determining the Average using the DAVERAGE Database FunctionActivity 7.8 Determining the Sum of Certain Contingencies Using the DSUM FunctionActivity 7.9 Counting Specific Records in a DatabaseProject 7B ClientsObjective 5 Import Data to ExcelActivity 7.10 Importing an Access Database into ExcelActivity 7.11 Importing a Word Document to ExcelActivity 7.12 Copying an Excel Worksheet and Editing the WorksheetObjective 6 Create Lookup Tables in Another WorkbookActivity 7.14 Using an External LookupObjective 7 Enter Subtotals and Outline the WorksheetActivity 7.15 Subtotaling the WorksheetObjective 8 Link and Embed a Worksheet and Chart into Word DocumentsActivity 7.16 Embedding a Worksheet into a Word MemoActivity 7.17 Linking a Worksheet and a Chart into a Word MemoActivity 7.18 Editing Linked and Embedded DataChapter 8 Creating Macros, Using Depreciation and Conditional Functions, and Creating PivotTables ad PivotChart ReportsProject 8A DepreciationObjective 1 Create and Run a MacroActivity 8.1 Accessing the Developer TabActivity 8.2 Creating a MacroActivity 8.3 Running the MacroActivity 8.5 Opening a Document that Contains a MacroActivity 8.6 Editing a Macro with VBA CodeObjective 2 Apply Depreciation FunctionsActivity 8.8 Depreciating an Asset Using the Sum-of-Years’ Digits MethodActivity 8.9 Depreciating an Asset Using the Declining Balance MethodActivity 8.10 Preparing a Schedule of DepreciationActivity 8.11 Create a Macro Using the Relative Reference FeatureProject 8B New Car SalesObjective 3 Evaluate Worksheet Data with Conditional IF FunctionsActivity 8.12 Determining Individual Cars Sold Using the COUNTIF FunctionActivity 8.13 Determining Individual Sales Using the SUMIF FunctionActivity 8.14 Determining Individual Sales Using the AVERAGEIF FunctionActivity 8.15 Counting the Number of Hybrids Sold Using the COUNTIFS FunctionActivity 8.16 Determining Sales of a Car Make Using the SUMIFS FunctionObjective 4 Create a PivotTable and PivotChartActivity 8.17 Creating a PivotTableActivity 8.18 Filtering DataActivity 8.19 Rearranging the Filtered Data to Display Sales InformationActivity 8.20 Removing Filters to Display More DataActivity 8.21 Refreshing the Pivot Table and Displaying the Tableon Separate PagesActivity 8.22 Establishing a Custom Calculation and Formattingthe PivotTableActivity 8.23 Creating a Pivot ChartChapter 9 Inserting Graphic Elements into Worksheets and ChartsProject 9A Quarter1 SalesObjective 1 Format with Graphic Images Including WordArt to Enhance WorksheetsActivity 9.1 Formatting with PatternsActivity 9.2 Inserting and Formatting a ShapeActivity 9.3 Formatting Text Within a ShapeActivity 9.4 Inserting and Formatting WordArtObjective 2 Insert SmartArt GraphicsActivity 9.5 Inserting a SmartArt Organization ChartActivity 9.6 Inserting a Picture as BackgroundActivity 9.7 Formatting SmartArtObjective 3 Create, Edit, and Publish a Web PageActivity 9.8 Preparing a Web PageActivity 9.9 Inserting and Formatting Graphic Elements into a Web PageActivity 9.10 Insert Hyperlinks into a Web PageActivity 9.11 Saving and Publishing a Web PageProject 9B SoAmericaObjective 4 Create Specialized ChartsActivity 9.12 Inserting a Line ChartActivity 9.13 Copy a Chart and Change Chart Type to Area ChartActivity 9.14 Format Data Series and Chart AreaActivity 9.15 Rearrange Data Markers 7Activity 9.16 Inserting a Scatter ChartActivity 9.17 Inserting a Trend lineActivity 9.18 Inserting Axis Titles 7Activity 9.19 Editing the Axis of a ChartActivity 9.20 Creating a Stock Chart 7Objective 5 Format Column Charts with Picturesand Other Graphic ElementsActivity 9.21 Using Pictures to Format a 2-D Column ChartActivity 9.22 Using Pictures to Format a 3-D Column Chart 7Chapter 10 Creating Templates and Creating and Validating Form10A Project 10A Payroll ReportObjective 1 Protect Worksheet ElementsActivity 10.1 Inserting a Picture into a HeaderActivity 10.2 Inserting Formulas and Formats into a ReportActivity 10.3 Completing a Chart for the WorksheetActivity 10.4 Protecting Elements of the WorksheetObjective 2 Unprotect Elements and Hide FormulasActivity 10.5 Unprotecting Cells in a Protected WorksheetActivity 10.6 Hiding Formulas and Other Sensitive InformationObjective 3 Protect a WorkbookActivity 10.7 Protecting a Workbook's Structure and WindowsActivity 10.8 Open a Protected WorkbookObjective 4 Save Worksheet and Chart TemplatesActivity 10.9 Saving a Workbook as a TemplateActivity 10.10 Creating a Chart to Save as a TemplateObjective 5 Complete a Report with Worksheet and Chart TemplatesActivity 10.11 Creating a Worksheet and Charts Using Templates10B Project 10B Time CardObjective 6 Create a FormActivity 10.12 Inserting a VLOOKUP to Enter DataActivity 10.13 Inserting Formulas to Display Dates in a Form and Formatting the Input AreaActivity 10.14 Creating the Output AreaActivity 10.15 Completing the Formulas in the Output AreaObjective 7 Validate a FormActivity 10.16 Validating the FormActivity 10.17 Testing the Validation RuleActivity 10.18 Creating a Drop-Down ListActivity 10.19 Hiding FormulasObjective 8 Insert Macro Command ButtonsActivity 10.20 Creating a Print MacroActivity 10.21 Creating a Macro to Clear the Input AreaActivity 10.22 Inserting a Macro Command ButtonActivity 10.23 Protecting the Worksheet and Saving It as a TemplateActivity 10.24 Preparing Payroll Reports for a WeekChapter 11 Nesting Functions and Consolidating Worksheets11A Project 11A Server BonusObjective 1 Nest One Function within AnotherActivity 11.1 Creating a Nested FunctionActivity 11.2 Nesting IF FunctionsActivity 11.3 Nesting IF Functions to Determine the Incentive for Sales for one MonthActivity 11.4 Nesting IF and AND Functions to Determine The Weekend RecipientsObjective 2 Use 3-D References and Nested LookupActivity 11.5 Using Nested 3-D ReferenceActivity 11.6 Nesting a Lookup FunctionObjective 3 Check Accuracy with Excel's Auditing ToolsActivity 11.7 Evaluating FormulaActivity 11.8 Using Auditing Tools to Trace Precedents and DependentsActivity 11.9 Using Error CheckingActivity 11.10 Using Watch WindowProject 11B Sales ReportObjective 4 Consolidate WorkbooksActivity 11.11 Consolidating by PositionActivity 11.12 Consolidating by CategoryObjective 5 Share and Merge WorkbooksActivity 11.13 Using a Shared WorkbookActivity 11.14 Creating a Shared WorkbookActivity 11.15 Entering Data into a Shared WorkbookActivity 11.16 Display the Compare and Merge ButtonActivity 11.17 Merge WorksheetsChapter 12 Working with Data Tables, Scenarios, Solver,12A Project 12A Analysis ToolsObjective 1 Create a Data TableActivity 12.1 Creating a One-variable Data TableActivity 12.2 Adding a Formula to One-Variable Data TableActivity 12.3 Creating a Two-Variable Data TableActivity 12.3 Creating a Two-Variable Data TableObjective 2 Create a ScenarioActivity 12.4 Creating and Displaying ScenarioActivity 12.5 Creating a Scenario Summary ReportObjective 3 Use SolverActivity 12.6 Loading SolverActivity 12.7 Using Solver12B Project 12B Price ListObjective 4 Prepare a Document for DistributionActivity 12.8 Changing the User InformationActivity 12.9 Examine a File for Sensitive InformationActivity 12.10 Using Document PropertiesActivity 12.11 Restricting Permissions Using Encryption and Information Rights ManagementActivity 12.12 Saving as PDF or XPS FormatsActivity 12.13 Checking for Features Not Supported in Earlier Versions of ExcelActivity 12.14 Using the Document InspectorActivity 12.15 Marking the Workbook as FinalObjective 5 UseActivity 12.16 Renaming an Excel 2007 file and Displaying its StructureActivity 12.17 Identifying the Functions of Folders and FilesActivity 12.18 Replacing a Picture in the Media FolderActivity 12.19 Identifying the Characteristics of anActivity 12.20 Editing anActivity 12.21 CheckingActivity 12.22 Replacing Parts and Zipping the Container FileActivity 12.23 Renaming the File and Restoring Default Values