|
Excel 2007 Foundation
Getting Started
Starting Out
What is Microsoft Excel
2007?
Opening Microsoft Excel
Interacting with Excel
Closing Excel
Step-By-Step
About Workbooks
Creating a New
Workbook
Opening a Workbook
Saving a Workbook
About Excel File Types
Closing a Workbook
Step-By-Step
Skill Sharpener
Exploring your
Workbook
Using Worksheets
The Active Cell
Selecting Cells
Exploring a Worksheet
The Zoom Feature
Step-By-Step
Skill Sharpener
Getting Help in Excel
2007
Using the Help Screen
About Online Help
About Offline Help
Step-By-Step
Skill Sharpener
Case Study
Review Questions
The New Interface
The Quick Access
Toolbar
The Default Buttons
Adding Buttons
Removing Buttons
Customizing the Toolbar
Step-By-Step
Skill Sharpener
The Home Ribbon
Clipboard
Font
Alignment
Number
Style
Cells
Editing
Step-By-Step
Skill Sharpener
The Insert Ribbon
Tables
Charts
Illustrations
Shapes
Links
Text
Step-By-Step
Skill Sharpener
The Page Layout Ribbon
Themes
Page Setup
Scale to Fit
Sheet Options
Arrange
Step-By-Step
Skill Sharpener
The Formulas Ribbon
The Function Library
Defined Names
Formula Auditing
Calculation
Step-By-Step
Skill Sharpener
The Data Ribbon
Get External Data
Connections
Sort and Filter
Data Tools
Outline
Step-By-Step
Skill Sharpener
The Review Ribbon
Proofing
Comments
Changes
Step-By-Step
Skill Sharpener
Case Study
Review Questions
Excel Basics
Working With Excel 2007
Columns, Rows, Cells, and
Ranges
Creating Worksheet Labels
Entering and Deleting Data
Printing Your Worksheet
Step-By-Step
Skill Sharpener
Basic Excel Features
What is AutoFill?
What is AutoSum?
What is AutoComplete?
Working with Basic Formulas
Step-By-Step
Skill Sharpener
Moving your Data
Dragging and Dropping
Cells
How to Cut, Copy, and Paste Cells
How to Cut, Copy, and Paste Multiple Cells and Items
How to Use Paste Special
How to Insert and Delete Cells, Rows, and Columns
Using Undo, Redo, and Repeat
Step-By-Step
Skill Sharpener
Smart Tags and Options
Buttons
What Are Smart Tags?
The Error Option Button
The AutoFill Option Button
The Paste Option Button
Setting Smart Tag Options
Step-By-Step
Skill Sharpener
Editing Tools
Using AutoCorrect
Using Spell Check
Using Find and Replace
Adding Comments
Step-By-Step
Skill Sharpener
Case Study
Review Questions
Editing Your Workbook
Modifying Cells and
Data
Changing the Size of Rows or
Columns
Adjusting Cell Alignment
Rotating Text
Creating Custom Number and Date Formats
Step-By-Step
Skill Sharpenr
Cell Formatting
Conditional Formatting
The Format Painter
Cell Merging and AutoFit
Find and Replace Formatting
Step-By-Step
Skill Sharpener
Enhancing a Worksheet’s
Appearance
Adding Patterns and
Colors
Adding Borders
Working with Styles
Step-By-Step
Skill Sharpener
Working With Charts
Creating a Chart
Formatting a Chart
Modifying Charts with the Layout Ribbon
Manipulating a Chart
Enhancing a Chart with Shapes and Graphics
Step-By-Step
Skill Sharpener
Working with Charts, Part
2
Changing the Type of
Chart
Changing the Source Data
Working with the Chart Axis and Data Series
Saving a Chart as a Template
Step-By-Step
Skill Sharpener
Case Study
Review Questions
Printing and Viewing
your Workbook
Using the View Ribbon
Using Normal View
Using Full Screen View
Using Page Layout View
Page Break Preview
Step-By-Step
Skill Sharpener
Managing a Single Window
Creating a New
Window
Hiding a Window
Unhiding a Window
Freezing a Pane
Step-By-Step
Skill Sharpener
Managing Multiple Windows
Switching Between Open
Workbooks
Arranging Workbooks
Comparing Workbooks Side by Side
Synchronous Scrolling, Resetting a Window, and Saving a
Workspace
Step-By-Step
Skill Sharpener
Printing your Workbook
Opening Print Preview
Using the Print Preview Ribbon
Quick Printing
The Print Dialogue
Using Page Setup
Step-By-Step
Skill Sharpener
Case Study
Review Questions
Index
Excel 2007
Intermediate
Advanced File Tasks
Using My Computer
within Excel
Using the My Places
Toolbar
Navigating with My Computer
Performing Basic Tasks with My Computer
Changing Views
Step-By-Step
Skill Sharpener
Saving your Files
File Formats
File Properties
AutoRecover
Upgrading a Workbook
Step-By-Step
Skill Sharpener
Using Templates
Opening a Template
Downloading a Template
Using a Template
Creating a Template
Step-By-Step
Skill Sharpener
Case Study
Review Questions
Working with Functions and Formulas
Using Formulas in Excel
Understanding Relative and
Absolute Cell References
Basic Mathematical Operators
Using Formulas with Multiple Cell References
The Formula Auditing Buttons
Fixing Formula Errors
Displaying and Printing Formulas
Step-By-Step
Skill Sharpener
Exploring Excel Functions
What are Functions?
Finding the Right Function
Some Useful and Simple Functions
Step-By-Step
Skill Sharpener
Using Functions in Excel
Inserting Functions
Using Functions and AutoFill to Perform Difficult
Calculations
Using the IF Function
Working with Nested Functions
Step-By-Step
Skill Sharpener
Working with Names and
Ranges
What Are Range Names?
Defining and Using Range Names
Selecting Nonadjacent Ranges
Using AutoCalculate
Step-By-Step
Skill Sharpener
Working with Array
Formulas
What are Array
Formulas?
Using Basic Array Formulas
Using Functions with Array Formulas
Using the IF function in Array Formulas
Step-By-Step
Skill Sharpener
Case Study
Review Questions
Managing Tables
Working with
Tables
What is a Table?
Creating Tables
Modifying Tables
What is the Total Row?
Step-By-Step
Skill Sharpener
Working with Records and
Fields
What are Records and Fields?
Adding Fields by Inserting Columns
Adding Records by Inserting Rows
Quickly Adding Records to a Data Table
Deleting Records or Fields
Step-By-Step
Skill Sharpener
Working with Tables and
Filters
Sorting Data in a Table
What is an AutoFilter?
Custom AutoFilters
Using an Advanced Filter
Copying Filtered Records
Step-By-Step
Skill Sharpener
Using Excel as a Database
Filtering with Wildcard
Characters
Validating Your Data
What are Database Functions?
Step-By-Step
Skill Sharpener
Case Study
Review Questions
Enhancing your
Workbook
Customizing your Workbook
Inserting a Symbol or Special
Character
Adding and Editing Shapes
Creating and Altering Diagrams
Changing the Diagram Type
Incorporating Text
Adding a Signature Line
Step-By-Step
Skill Sharpener
Working with Text Boxes
Adding a Text Box
Selecting a Text Box
Manipulating a Text Box
Formatting a Text Box
Step-By-Step
Skill Sharpener
WordArt
Adding WordArt to your
Spreadsheet
Changing the Font Color
Changing the Outline Color
Adding Effects
Step-By-Step
Skill Sharpener
ClipArt
Finding ClipArt
Inserting ClipArt
Manipulating ClipArt
Inserting a Photographic Image
Step-By-Step
Skill Sharpener
Using Objects
Creating New Embedded Objects
Embedding Existing Files
Editing Embedded Objects
Embedding Parts of Files
Step-By-Step
Skill Sharpener
Case Study
Review Questions
Finalizing Your
Workbook
Protecting your Workbook
Protecting your
Workbook
Protecting your Worksheets
Unlocking Cells
Protecting your Excel Files
Step-By-Step
Skill Sharpener
Finishing Your Workbook
Using the Document Inspector
Using the Compatibility Checker
Marking a Workbook as Final
Step-By-Step
Skill Sharpener
Using Excel in Word
Inserting Excel Data in
Word
Linking Excel Data in a Word Document
Modifying Excel Data after Insertion
Insert an Excel Chart into a Word Document
Step-By-Step
Skill Sharpener
Using Excel and Access
Linking an Excel Workbook to
an Access Database
Importing Table, PivotTable, and PivotChart Data from Access
Transforming an Excel Workbook into an Access Database
Step-By-Step
Skill Sharpener
Using Excel With other
Programs and Files
Using Outlook to Send Excel
Data
Opening an Excel File in a Different Format
Importing Data from a Text File
Importing Data from External Data Sources
Publishing a Workbook as a PDF File
Faxing a Workbook
Step-By-Step
Skill Sharpener
Case Study
Review Questions
Index
Excel 2007
Advanced
Advanced Topics
Setting Excel Options
Changing Your User
Name
Changing your Default Font
Changing Your Default File Format
Setting Formula Options
Changing the Appearance of Your Excel Interface
Step-By-Step
Skill Sharpener
Excel and Windows
Searching for
Documents
Using Office Diagnostics
Checking for Updates
Starting Excel Automatically when Windows Boots
Step-By-Step
Skill Sharpener
Using the Excel
Viewer
What is the Excel
Viewer?
Downloading the Excel Viewer
Using Excel Viewer
Step-By-Step
Skill Sharpener
Review Questions
Getting the Most From Your Data
Getting The Most From Your
Data
Using Automatic
Outlining
Displaying and Collapsing Levels
Grouping Data Manually
Creating Subtotals
Step-By-Step
Skill Sharpener
PivotTables
What is a PivotTable?
Creating a PivotTable
Creating a PivotTable Frame
Specifying PivotTable Data
Rearranging PivotTable Data
Step-By-Step
Skill Sharpener
More About PivotTables
Modifying PivotTable
Calculations
Formatting a PivotTable
Refreshing a PivotTable
Charting a PivotTable
Creating a PivotTable Based on External Data
Step-By-Step
Skill Sharpener
Exploring Scenarios
What is a Scenario?
Creating a Scenario
Creating a Scenario Summary Report
Saving Multiple Scenarios
Step-By-Step
Skill Sharpener
What If Analysis
Using a One and Two Input
Data Table
Using Goal Seek
Using Solver
Step-By-Step
Skill Sharpener
Case Study
Review Questions
Excel and the Internet
Excel and Hyperlinks
What is a Hyperlink?
Inserting Hyperlinks
Modifying Hyperlinks
Using Hyperlinks in Excel
Step-By-Step
Skill Sharpener
Saving Workbooks as Web
Pages
HTM and MHTML Files
Saving a Workbook as a Web
Page
Saving a Worksheet as a Web Page
Saving a Chart as a Web Page
Saving a Range as a Web Page
Step-By-Step
Skill Sharpener
Getting Data from the
Internet
Importing from an External
Data Source
Creating a Web Query
Saving a Web Query
Refreshing a Web Query
Step-By-Step
Skill Sharpener
Excel and Office Servers
Publishing to a Document
Server
Saving to a Document Management Server
Using Excel with Excel Services
Step-By-Step
Skill Sharpener
Case Study
Review Questions
Advanced Excel Tasks
Creating and Using Shared
Workbooks
Sharing a Workbook
Opening and Editing a Shared Workbook
Tracking Changes
Resolving Conflicts in a Shared Workbook
Step-By-Step
Skill Sharpener
Using Custom AutoFill
Lists
What is an AutoFill
List?
Creating a Custom AutoFill List
Modifying a Custom AutoFill List
Using a Custom AutoFill List
Step-By-Step
Skill Sharpener
Using the VLOOKUP
Function
Using VLOOKUP to Find
Data
How to Find an Exact Match with VLOOKUP
Finding the Closest Match with VLOOKUP
Step-By-Step
Skill Sharpener
Linking, Consolidating,
and Combining Workbooks
Linking Workbooks
Consolidating Workbooks
Combining Worksheets
Step-By-Step
Skill Sharpener
Case Study
Review Questions
Index
Excel 2007 Expert
Add-ins, Smart Tags, and Digital
Security
Add-ins and the Trust Center
What is a COM
Add-in?
Add-In Management
Installing Add-ins
Using the Trust Center
Add-Ins
Step-By-Step
Skill Sharpener
Information Rights
Management
What is IRM?
Requirements for IRM
Downloading and Installing the Rights Management Client Software
Using IRM
Opening an IRM Protected Document
Step-By-Step
Skill Sharpener
Digital Signatures
What is a Digital
Signature?
Obtaining a Digital Signature
Attaching your Signature to a Workbook
Step-By-Step
Skill Sharpener
Smart Tags and XML
What is a Smart Tag?
Inserting Smart Tags into a Worksheet
Setting Smart Tag Options
Creating Custom Smart Tags
Step-By-Step
Skill Sharpener
Case Study
Review Questions
Macros, VBA, and Excel Programming
Macros
Recording Macros
Playing a Macro
Macro Security
Step-By-Step
Skill Sharpener
More Macro Tasks
Recording a Relative
Reference Macro
Running a Relative Reference Macro
Assigning a Keystroke to a Macro
Copying a Macro from a Workbook or Template
Step-By-Step
Skill Sharpener
Visual Basic and Macros
Opening the Visual Basic
Editor
Adding Code to your Macro
Adding Comments to VBA Code
Declaring Variables
Prompting for User Input
Iteration over a Range
Using IF Then Else Statements
Step-By-Step
Skill Sharpener
Case Study
Review Questions
Expert Topics
Form Controls
What is a Form
Control?
Adding a Control to a Worksheet
Assigning a Macro to a Control
Using Form Controls
Step-By-Step
Skill Sharpener
Advanced Functions
Using Financial Functions
(PMT and FV)
Understanding Logical Functions
Applying Logical Functions
Step-By-Step
Skill Sharpener
Using SharePoint
Creating a Document
Workspace
Accessing a Workspace
Publishing to a Document Workspace
Step-By-Step
Skill Sharpener
User Forms and ActiveX
Controls
Creating a Blank User
Form
Adding Controls to the Form
Adding Buttons and VBA Code
Starting the User Form with a Macro
Step-By-Step
Skill Sharpener
Case Study
Review Questions
Index
|
