Microsoft Office Excel 2007 In Business, Comprehensive

Microsoft Office Excel 2007 In Business, Comprehensive, 1/e

Table of Contents


Chapter 1 - Introduction

    What is Excel?

        Why Are We Here?

        A Decision-Making Tool 

        An Overview of Excel

        Ribbons, Tabs, and Icons

        The File Menu

        Excel 97-2003 File Format

        Right Click and Control Keys

        Settings and Status Bar

        Excel Help

 

Chapter 2 -  Excel Basics

    Data Management

        Data Entry

        Auto Fill

        Adjusting Columns and Rows

        Hiding Columns and Rows

        Copy and Paste

        Sorting Data (Single Level)

        Sorting Data (Multiple Levels)

    Formatting

        Data Formats

        Data Alignment

        Number Formats

        Borders (Line Formats)

        Cell Colors (Fill Color)

    Editing

        Editing Data in a Cell

        Moving Data

        Deleting Columns and Rows

        Deleting Worksheets

        Inserting Columns and Rows

        Inserting, Moving, and Renaming Worksheet Tabs

    Printing

        Page Setup

        Printing a Worksheet

 

 

Chapter 3 - Calculating Data: Formulas and Functions

    Formulas

        Basic Formulas

        Copy and Pasting Formulas (Relative Reference and Paste Special)

        Complex Formulas

        Auditing Formulas

    Basic Functions

        Basic Statistical Functions

        AutoSum

        Absolute References (Turning Off Relative References)

    Financial Functions

        The Future Value Function

        The Payment Function

        The Function Library

 

 

Chapter 4 - Evaluating Data

    Logical Functions (The IF Function)

        IF Function

        Nested IF Function

    Logical Functions (AND OR)

        AND Function

        OR Function

        Combining AND, OR, and IF Functions

Lookup Functions

        VLookup Function

        HLookup Function

 

 

Chapter 5 - Presenting Information with Charts

    Creating Charts

        Column Charts (Data Comparisons)

        Stacked Column Charts (Percent to Total Over Time)

        Line Charts (Trends Over Time)

        Pie Charts (Percent to Total)

Formatting Charts

        Titles and Legends

        Plot Area and Data Series

        X- and Y-Axes Labels

        Adding Annotations and Objects 

Advanced Chart Options

        Defining the X- and Y-Axes Manually

        The Scatter Plot Chart

        Pasting Charts into PowerPoint and Word

 

 

Chapter 6 - Applying Core Competency Skills: Financial Planning and Accounting

    Linking Data

        Linking Worksheets and Workbooks

        Updating Workbook Links

        SUMIF Function

    Financial Planning

        Financial Assumptions

        Freeze Panes

        Financial Data

        Cash Analysis (The NPV Function)

    Financial Accounting

        T-Accounts

        Journals

        Ledgers

        Trial Balance and Work Sheets

        Statements

 

Chapter 7 Date, Text and Error Functions

    Date Functions

        Today

        Year

        Month

        Date and Day

    Text Functions

        Concatenate

        Search, Left, Len and Mid

    IS Functions

        Is Number and Is Text

        Is Error

 

Chapter 8 Managing Large Volumes of Data

    PivotTables

        Creating A New PivotTable

        Adding Fields and Changing Field Settings

        Applying Filters to Fields

        Adding and Removing Report Totals

        Adding Formulas (Calculated Fields)

        Sorting Data

        PivotCharts

    Tools for Selecting and Summarizing Data

        Filters

        Subtotals

        Adding the Developer Tab

        List Boxes

 

Chapter 9 Scenarios and Advanced Statistics

    Scenario Tools

        Scroll Bar

        Conditional Formatting

        Data Tables

        Goal Seek

        Scenario Manager

    Data Analysis Tools

        Add-Ins

        Descriptive Statistics

        Histograms

        Regression

    Solver

      Defining Constraints

        Defining Solver Parameters

 

Chapter 10 Advanced Excel: Importing External Data

    Importing Text Files

        Importing Delimited Text Files

        Importing Fixed Width Text Files

    Importing Data from a Database

        Database Queries

        Editing a Database Query

        Advanced Database Queries (Microsoft Query)

    Importing Data from the Internet

        Web Queries

 

APPENDIX  - Macros and VBA

    Macros

        Recording a Macro

        Action Buttons

        Editing Action Buttons

    VBA (Visual Basic for Applications)

        The Visual Basic Editor

        Adding a Procedure

        Defining Variables

        Referencing and Assigning Values in Cell Locations

        For Next Loops

        If Statements

        Running a Program

        Initiating VBA Code through User Events