With Microsoft Excel 2007, Vol. 1

Chapter 1–Creating a Worksheet and Charting DataProject 1A–Create a Worksheet and Chart DataObjective 1: Create, Save, and Navigate an Excel WorkbookActivity 1.1 Starting Excel and Naming and Saving a WorkbookActivity 1.2 Navigating a Worksheet and a WorkbookActivity 1.3 Selecting Parts of a WorksheetObjective 2: Enter and Edit Date in a WorksheetActivity 1.4 Entering Text, Using AutoComplete, Filling a Series with AutoFill, and Using Spelling Checker and Undo to Correct Typing ErrorsActivity 1.5 Aligning Text and Adjusting the Size of Columns and RowsActivity 1.6 Entering NumbersActivity 1.7 Inserting and Deleting Rows and Columns and Using the Insert Options ButtonObjective 3: Construct and Copy Formulas, Use the Sum Function, and Edit CellsActivity 1.8 Constructing a Formula, Using the Sum Function and Editing Numbers in CellsActivity 1.9 Copying a Formula by Using the Fill HandleObjective 4: Format Data, Cells, and WorksheetsActivity 1.10 Formatting Financial Numbers, Using Column AutoFit, and Using Format PainterActivity 1.11 Formatting Text and Using Merge and CenterObjective 5: Close and Reopen a WorkbookActivity 1.12 Closing and Reopening an Existing WorkbookObjective 6: Chart DataActivity 1.13 Charting DataObjective 7: Use Page Layout View, Prepare a Worksheet for Printing and Close ExcelActivity 1.14 Changing Views, Creating a Footer and Using Print PreviewActivity 1.15 Deleting Unused Sheets in a WorkbookActivity 1.16 Printing a WorksheetActivity 1.17 Displaying, Printing, and Hiding FormulasProject 1B–Perform Calculations and Make Comparisons by Using a Pie ChartObjective 8: Design a WorksheetActivity 1.18 Setting Column Widths and Creating Row and Column TitlesActivity 1.19 Entering Data by RangeObjective 9: Construct Formulas for Mathematical OperationsActivity 1.20 Using Arithmetic OperatorsActivity 1.21 Copying Formulas Containing Absolute Cell ReferencesObjective 10: Format Percentages and Move FormulasActivity 1.22 Formatting Cells with the Percent Style ButtonActivity 1.23 Inserting Rows in a Worksheet Containing Formulas and Wrapping Text in a CellObjective 11: Create a Pie Chart and Chart SheetActivity 1.24 Creating a Pie Chart and a Chart SheetObjective 12: Use the Excel Help SystemActivity 1.25 Using the Excel Help SystemChapter 2–Managing Workbooks and Analyzing Data Project 2A Create a Summary Sheet from Multiple WorksheetsObjective 1: Create and Save a Workbook from an Existing WorkbookActivity 2.1 Creating and Saving a Workbook from an Existing WorkbookObjective 2: Navigate a Workbook and Rename WorksheetsActivity 2.2 Navigating Among Worksheets, Renaming Worksheets, and Changing the Tab Color of a WorksheetObjective 3: Enter Dates, Clear Contents, and Clear FormatsActivity 2.3 Entering and Formatting DatesActivity 2.4 Clearing Cell Contents and FormatsObjective 4: Move, Copy, and Paste Cell ContentsActivity 2.5 Copying, Pasting, and Moving Cell ContentsObjective 5: Edit and Format Multiple Worksheets at the Same TimeActivity 2.6 Wrapping Text in a Cell in Several Worksheets at the Same TimeActivity 2.7 Entering Data and Constructing Formulas on Multiple WorksheetsObjective 6: Create a Summary SheetActivity 2.8 Constructing Formulas that Refer to Cells in Another WorksheetObjective 7: Format and Print Multiple Worksheets in a WorkbookActivity 2.9 Moving and Formatting Worksheets in a WorkbookActivity 2.10 Printing All the Worksheets in a WorkbookProject 2 B Growth ProjectionObjective 8: Design a Worksheet for What-If AnalysisActivity 2.11 Using Parentheses in a FormulaActivity 2.12 Formatting as You TypeActivity 2.13 Calculating a Value After an IncreaseObjective 9: Perform What-If AnalysisActivity 2.14 Performing What-If Analysis and Using Paste SpecialObjective 10: Compare Data with a Line ChartActivity 2.15 Creating a Line ChartChapter 3–Using Functions and Tables Project 3A–Track Inventory by Using Math, Logical, and Statistical Functions and by Creating an Excel TableObjective 1: Use SUM, AVERAGE, MEDIAN, MIN, and MAX FunctionsActivity 3.1 Using the SUM, AVERAGE, and MEDIAN FunctionsActivity 3.2 Using the MIN and MAX FunctionsActivity 3.3 Moving Data, Adding Borders, and Rotating TextObjective 2: Use COUNTIF and IF Functions, and Apply Conditional FormattingActivity 3.4 Using the COUNTIF FunctionsActivity 3.5 Using the IF Function and Applying Conditional FormattingActivity 3.6 Applying Conditional Formatting Using Custom Formats and Data BarsActivity 3.7 Using Find and ReplaceObjective 3: Use a Date FunctionActivity 3.8 Using the NOW FunctionObjective 4: Freeze Panes and Create an Excel TableActivity 3.9 Freezing and Unfreezing PanesActivity 3.10 Sorting and Filtering in an Excel TableActivity 3.11 Inserting a Second Table in a WorksheetActivity 3.12 Converting a Table to a Range of DataObjective 5: Format and Print a Large WorksheetActivity 3.13 Printing Large WorksheetsProject 3B–Make Financial Decisions by Using Financial Functions and What-If AnalysisObjective 6: Use Financial FunctionsActivity 3.14 Designing a Loan WorksheetActivity 3.15 Inserting the PMT Financial FunctionObjective 7: Use Goal SeekActivity 3.16 Using Goal Seek to Produce the Desired ResultActivity 3.17 Using Goal Seek to Find an Increase PeriodObjective 8: Create a Data TableActivity 3.18 Designing a Two-Variable Data TableActivity 3.19 Using a Data Table to Calculate OptionsChapter 4 Using Templates, Range Names, and Lookup Functions Project 4A: Using Excel TemplatesObjective 1: Open an Excel TemplateActivity 4.1 Opening an Excel TemplateObjective 2: Customize a TemplateActivity 4.2 Adding a Company Logo to a Template Using Word ArtActivity 4.3 Adding ClipArt to the Company LogoActivity 4.4 Grouping Graphic ObjectsActivity 4.5 Adding a Formula to a TemplateObjective 3: Save a Workbook as a TemplateActivity 4.6 Saving a Workbook as a TemplateObjective 4: Enter Data in a Customized TemplateActivity 4.7 Opening and Using a TemplateProject 4B: Create a Protected Order Form Template Using Range Names and Lookup FunctionsObjective 5: Create Range Names (3.1.3)Activity 4.8 Naming a RangeActivity 4.9 Modifying a Named RangeActivity 4.10 Using Row and Column Titles to Name a RangeActivity 4.11 Deleting a Range NameObjective 6: Ensure Data Integrity (1.2)Activity 4.12 Creating a Validation List (1.2.1)Objective 7: Use Lookup FunctionsActivity 4.13 Retrieving Data Using a Named Range in a VLOOKUP FormulaActivity 4.14 Removing Error Codes Using IFERROR Function in a Nested IF StatementActivity 4.15 Creating an HLOOKUP TableActivity 4.16 Retrieving Data Using an HLOOKUP FormulaObjective 8: Protect the Worksheet and Save it as a TemplateActivity 4.17 Hiding Rows Containing Lookup TablesActivity 4.18 Unlocking Cells for Data Entry and Protecting a WorksheetActivity 4.19 Hiding Formulas in the WorksheetActivity 4.20 Setting a Print AreaActivity 4.21 Saving the Order Form as a TemplateChapter 5 Importing Data and Using Excel as a DatabaseProject A: Real Estate Company — Import list of customers, and massage list to send Holiday letters, notices of new houses on the market etc.Objective 1: Import Data into ExcelActivity 5.1 Importing Data from a Word TableActivity 5.2 Importing Data from a TXT FileActivity 5.3 Importing Data from AccessActivity 5.4 Importing Data from a Comma Delimited FileObjective 2: Clean-up Data from External SourcesActivity 5.5 Converting Text to ColumnsActivity 5.6 Using TRIM Function to Remove SpacesActivity 5.7 Consolidating DataActivity 5.8 Highlight Duplicate Values with Conditional FormattingActivity 5.9 Removing Duplicate RecordsProject B Table of Real Estate Sales ListingsObjective 3: Analyze Data with Excel Database ToolsActivity 5.10 Creating a Table and Printing the ResultsActivity 5.11 Adding Data and Using Custom AutoFiltersActivity 5.12 Creating Criteria Using Advanced Filter CriteriaActivity 5.13 Extracting Data to a New Area on the WorksheetActivity 5.14 Using Dates in a Calculated FieldActivity 5.15 Subtotaling, Grouping, and Outlining DataObjective 4: Use Database Functions (3.3)Activity 5.16 Using the DAVERAGE Database FunctionActivity 5.17 Using the DSUM Database FunctionActivity 5.18 Using DCOOUNTA FunctionActivity 5.19 Using DGET FunctionActivity 5.20 Controlling Print OptionsChapter 6–Summarizing Data and Making Business DecisionProject A: Grocery Sales data by region/store/departmentObjective 1: Create and Modify PivotTable ReportsActivity 6.1 Organizing Data to Display in a PivotTableActivity 6.2 Creating a PivotTableActivity 6.3 Changing the Value SummaryActivity 6.4 Creating a PivotTable with SubheadingsActivity 6.5 Formatting a PivotTable ReportObjective 2: Create PivotChart ReportsActivity 6.6 Creating a PivotChart ReportActivity 6.7 Modifying and Moving PivotChartActivity 6.8 Printing PivotTables and PivotChartsProject BObjective 3: Compare Dates and Round NumbersActivity 6.9 Determining the Number of Years Between Two DatesActivity 6.10 Creating Whole Numbers with ROUND FunctionsObjective 5: Apply Business Decisions with Logical OperatorsActivity 6.11 Applying a Logical Test to a Cell Value and Using the AND FunctionActivity 6.12 Applying the NOT FunctionActivity 6.13 Applying the OR FunctionActivity 6.14 Writing a Nested IF Statement to Determine the ResultsObjective 6: Insert Comments and Document a WorkbookActivity 6.15 Inserting CommentsActivity 6.16 Changing the Workbook PropertiesActivity 6.17 Printing Comments