Inhaltsverzeichnis
The Missing Credits
Introduction
Part One: Worksheet Basics
Chapter 1. Creating and Navigating Worksheets
Creating a Basic Worksheet
Starting a New Workbook
Adding the Column Titles
Adding Data
Editing Data
Navigating in Excel
The Tabs of the Ribbon
The Formula Bar
The Status Bar
Excel Options
Saving Files
The Excel 2007 File Format
Saving Your Spreadsheet in Older Formats
Saving Your Spreadsheet As a PDF
Saving Your Spreadsheet with a Password
Disaster Recovery
Opening Files
Opening Files-with a Twist
Opening Multiple Spreadsheets at Once
Chapter 2. Adding Information to Worksheets
Adding Different Types of Data
Controlling Your Data Types
Quick Ways to Add Data
AutoComplete
AutoCorrect
AutoFill
AutoFit
Undo and Redo
Chapter 3. Moving Data Around a Worksheet
Selecting Cells
Making Continuous Range Selections
Making Non-Contiguous Selections
Automatically Selecting Your Data
Making Selections with the Keyboard
Moving Cells Around
A Simple Cut-and-Paste or Copy-and-Paste
A Fancy Cut-and-Paste or Copy-and-Paste
The Clipboard
Special Pasting
Adding and Moving Columns or Rows
Inserting Columns
Inserting Rows
Inserting Copied or Cut Cells
Deleting Columns and Rows
Chapter 4. Managing Worksheets and Workbooks
Worksheets and Workbooks
Adding, Removing, and Hiding Worksheets
Naming and Rearranging Worksheets
Grouping Sheets
Moving Worksheets from One Workbook to Another
Find and Replace
The Basic Find
Find All
More Advanced Searches
Finding Formatted Cells
Finding and Replacing Values
Spell Check
Spell Checking Options
Chapter 5. Formatting Cells
Formatting Cell Values
Formatting Numbers
Formatting Dates and Times
Special Formats for Special Numbers
Custom Formats
Formatting Cell Appearance
Alignment and Orientation
Fonts and Color
Borders and Fills
Drawing Borders by Hand
Chapter 6. Smart Formatting Tricks
The Format Painter
Styles and Themes
Custom Styles
Modifying Styles
Transferring Styles Between Workbooks
Themes: A Package of Styles
Modifying Themes
Conditional Formatting
The Basics of Conditional Formatting
Highlighting Specific Values
Data Bars
Color Scales
Icon Sets
Fine-Tuning a Formatting Rule
Using Multiple Rules
Chapter 7. Viewing and Printing Worksheets
Controlling Your View
Zooming
Viewing Distant Parts of a Spreadsheet at Once
Freezing Columns or Rows
Hiding Data
Saving View Settings
Viewing Multiple Workbooks at Once
Printing
How to Print an Excel File
Quick Printing
Previewing Your Printout
Creating Headers and Footers
Customizing Print Settings
Controlling Pagination
Page Breaks
Scaling
Page Break Preview: A Bird's-Eye View of Your Worksheet
Part Two: Formulas and Functions
Chapter 8. Building Basic Formulas
Creating a Basic Formula
Excel's Order of Operations
Cell References
How Excel Formats Cells That Contain Cell References
Functions
Formula Errors
Logical Operators
Formula Shortcuts
Point-and-Click Formula Creation
Point-and-Click Formula Editing
The Formulas Tab
Using the Insert Function Button
Copying Formulas
Absolute Cell References
Partially Fixed References
Referring to Other Worksheets and Workbooks
Chapter 9. Math and Statistical Functions
Rounding Numbers
ROUND( ), ROUNDDOWN( ), ROUNDUP( ): Rounding Numbers
MROUND( ), CEILING( ), and FLOOR( ): More Rounding Functions
INT( ) and TRUNC( ): Chopping Off Non-Whole Numbers
EVEN( ) and ODD( ): Rounding Up to Even or Odd Values
Groups of Numbers
SUM( ): Summing Up Numbers
COUNT( ), COUNTA( ), and COUNTBLANK( ): Counting Items in a List
MAX( ) and MIN( ): Finding Maximum and Minimum Values
LARGE( ), SMALL( ), and RANK( ): Ranking Your Numbers
AVERAGE( ) and MEDIAN( ): Finding Average or Median Values
MODE( ): Finding Numbers That Frequently Occur in a List
PERCENTILE( ) and PERCENTRANK( ): Advanced Ranking Functions
FREQUENCY( ): Identifying to Which Category a Number Belongs
General Math Functions
PRODUCT( ), FACT( ), POWER( ), and SQRT( ): Products, Factorials, Powers, and Square Roots
QUOTIENT( ) and MOD( ): Higher Division
ABS( ) and SIGN( ): Absolute Value and Determining a Number's Sign
RAND( ) and RANDBETWEEN( ): Generating Random Numbers
GCD( ) and LCM( ): Greatest and Least Common Denominator
COMBIN( ) and PERMUT( ): Figuring Combinations and Permutations
Trigonometry and Advanced Math
Advanced Statistics
Chapter 10. Financial Functions
The World of Finance
Financial Functions
FV( ): Future Value
PV( ): Present Value
PMT( ), PPMT( ), and IPMT( ): Calculating the Number of Payments You Need to Make
NPER( ): Figuring Out How Much Time You'll Need to Pay Off a Loan or Meet an Investment Target
RATE( ): Figuring the Interest Rate You Need to Achieve Future Value
NPV( ) and IRR( ): Net Present Value and Internal Rate of Return
Depreciation
Other Financial Functions
Chapter 11. Manipulating Dates, Times, and Text
Manipulating Text
CONCATENATE( ): Joining Strings of Text Together
LEFT( ), MID( ), and RIGHT( ): Copying Portions of a Text String
LEN( ), FIND( ), and SEARCH( ): Counting Characters in a String
UPPER( ), LOWER( ), and PROPER( ): Changing Capitalization
TRIM( ) and CLEAN( ): Removing Unwanted Spaces and Non-Printing Characters
SUBSTITUTE( ): Replacing One Sequence of Characters with Another
TEXT( ), VALUE( ), FIXED( ), and DOLLAR( ): Converting Text to Numbers and Vice Versa
Other Text Functions
Manipulating Dates and Times
Math with Dates and Times
Using Dates and Times with Ordinary Functions
Date and Time Functions
TODAY( ) and NOW( ): Inserting the Current Date and Time
DATE( ) and TIME( ): Dates and Times in Calculations
DAY( ), MONTH( ), and YEAR( ): More Date Calculations
HOUR( ), MINUTE( ), SECOND( ): More Time Calculations
WEEKDAY( ): Determining the Day of the Week
DATEDIF( ): Calculating the Difference Between Dates
DATEVALUE( ) and TIMEVALUE( ): Converting Dates and Times into Serial Numbers
DAYS360( ): Finding Out the Number of Days Between Two Dates
EDATE( ): Calculating Future Dates
YEARFRAC( ): Calculating the Percentage of a Year Between Two Dates
EOMONTH( ): Finding the Last Day of Any Month
NETWORKDAYS( ): Counting the Number of Business Days
WORKDAY( ): Figuring Out When Days Will Fall in the Future
WEEKNUM( ): Figuring Out in Which Week a Date Falls
Chapter 12. Lookup, Reference, and Information Functions
The Basic Lookup
VLOOKUP( ): Vertical Lookups
HLOOKUP( ): Horizontal Lookups
Advanced Lookups
MATCH( ): Finding the Position of Items in a Range
INDEX( ): Retrieving the Value from a Cell
Performing a "Left Lookup"
Performing a Double Lookup
The Lookup Wizard
OFFSET( ): Moving Cell References to a New Location
Other Reference and Lookup Functions
INDIRECT( ) and ADDRESS( ): Working with Cell References Stored As Text
TRANSPOSE( ): Changing Rows into Columns and Vice Versa
Information Functions
The "IS" Functions: Checking the Value Inside a Cell
TYPE( ) and ERROR.TYPE( ): Finding a Value's Data Type or Error Type
INFO( ) and CELL( ): Gathering Info About Your Computer and Your Worksheet's Cells
Tutorial: Quickly Generating Invoices from a Product Catalog
Chapter 13. Advanced Formula Writing and Troubleshooting
Conditions in Formulas
IF( ): Building Conditional Formulas
COUNTIF( ): Counting Only the Cells You Specify
SUMIF( ): Adding Only the Cells You Specify
COUNTIFS( ) and SUMIFS( ): Counting and Summing Using Multiple Criteria
Descriptive Names for Cell References
Creating and Using a Named Range
Creating Slightly Smarter Named Ranges
Naming Formulas and Constants
Managing Named Ranges
Automatically Creating Named Ranges
Applying Names to Existing Formulas
Variable Data Tables
Creating a One-Variable Data Table
Creating a Two-Variable Data Table
Controlling Recalculation
Solving Formula Errors
Step-by-Step Evaluation
Tracing Precedents and Dependents
Error Checking
Part Three: Organizing Worksheets
Chapter 14. Tables: List Management Made Easy
The Basics of Tables
Creating a Table
Formatting a Table
Editing a Table
Selecting Parts of a Table
Sorting and Filtering a Table
Applying a Simple Sort Order
Sorting with Multiple Criteria
Sorting by Color
Filtering with the List of Values
Creating Smarter Filters
Dealing with Duplicate Rows
Highlighting Duplicates
Removing Duplicates Automatically
Performing Table Calculations
Dynamic Calculations
Column Names
Table Names
The Total Row
The SUBTOTAL( ) Function
The Database Functions
Chapter 15. Grouping and Outlining Data
Basic Data Grouping
Creating a Group
Nesting Groups Within Groups
Summarizing Your Data
Combining Data from Multiple Tables
Grouping Timesavers
Auto Outline
Automatic Subtotaling
Chapter 16. Templates
Understanding Templates
Creating a New Workbook from a Template
Downloading Templates (Method 1: The New Workbook Dialog Box)
Downloading Templates (Method 2: The Office Online Web Site)
Creating Templates
Understanding Custom Templates
Building a Custom Template
Sharing Templates with Others
Part Four: Charts and Graphics
Chapter 17. Creating Basic Charts
Charting 101
Embedded and Standalone Charts
Creating a Chart with the Ribbon
The Chart Tools Ribbon Tabs
Basic Tasks with Charts
Moving and Resizing a Chart
Creating a Standalone Chart
Editing and Adding to Chart Data
Changing the Chart Type
Printing Charts
Practical Charting
Charts with Multiple Series of Numbers
Controlling the Data Excel Plots on the X-Axis
Data That Uses a Date or Time Scale
Non-Contiguous Chart Ranges
Changing the Order of Your Data Series
Changing the Way Excel Plots Blank Values
Chart Types
Column
Bar
Line
Pie
Area
XY (Scatter)
Stock
Surface
Donut
Bubble
Radar
Chapter 18. Formatting and Perfecting Charts
Chart Styles and Layouts
Chart Styles
Chart Layouts
Adding Chart Elements
Adding Titles
Adding a Legend
Adding Data Labels to a Series
Adding Individual Data Labels
Adding a Data Table
Selecting Chart Elements
Formatting Chart Elements
Coloring the Background
Fancy Fills
Fancy Borders and Lines
Formatting Data Series and Data Points
Reusing Your Favorite Charts with Templates
Improving Your Charts
Controlling a Chart's Scale
Adding a Trendline
Adding Error Bars to Scientific Data
Formatting 3-D Charts
Changing the Shape of a 3-D Column
Advanced Charting
Exploding Slices in a Pie
Grouping Slices in a Pie
Gaps, Widths, and Overlays in a Column Chart
Creating Combination Charts
Chapter 19. Inserting Graphics
Adding Pictures to a Worksheet
Inserting a Picture
Positioning and Resizing a Picture
Picture Touch-Up
Compressing Pictures
Cropping and Shaping a Picture
Picture Borders, Effects, and Styles
Excel's Clip Art Library
Drawing Shapes
Drawing a Shape
Adding Text to a Shape
Selecting and Arranging Shapes
Connecting Shapes
SmartArt
Part Five: Advanced Data Analysis
Chapter 20. Scenarios and Goal Seeking
Using Scenarios
Creating a New Scenario
Managing Scenarios
Creating a Summary Report
Using Goal Seek
Goal Seeking with Complex Equations
Solver
Understanding Solver
Defining a Problem in Solver
Advanced Solver Solutions
Saving Solver Models
Configuring Solver
Chapter 21. Pivot Tables
Summary Tables Revisited
Life Without Pivot Tables
Life with Pivot Tables
Building Pivot Tables
Preparing a Pivot Table
Pivot Table Regions
Laying Out a Pivot Table
Formatting a Pivot Table
Rearranging a Pivot Table
Multi-Layered Pivot Tables
Hiding and Showing Details
Fine-Tuning Pivot Table Calculations
Changing the Type of Calculation
Adding a Calculated Field
Filtering a Pivot Table
Report Filtering
Group Filtering
Pivot Charts
Creating a Pivot Chart
Manipulating a Pivot Chart
Part Six: Sharing Data with the Rest of the World
Chapter 22. Protecting Your Workbooks
Understanding Excel's Safeguards
Data Validation
Settings
Input Message
Error Alert
Data Validation with Formulas and Cell References
Data Validation with Lists
Locked and Hidden Cells
Protecting a Worksheet
Protecting the Entire Workbook
Protecting Cell Ranges (with More Passwords)
Allowing Specific Windows Users to Edit a Range
Chapter 23. Worksheet Collaboration
Preparing Your Workbook
Document Properties
Distributing a Document
Adding Comments
Inserting a Comment
Showing and Hiding Comments
Fine-Tuning Comments
Reviewing Comments
Printing Comments
Tracking Changes
Switching On Change Tracking
Understanding the Change Log
Highlighting Changes
Examining the Change Log
Accepting and Rejecting Changes
Merging Multiple Revisions into One Workbook
Sharing Your Workbook
Multiple Users Without Workbook Sharing
Turning On Workbook Sharing
Workbook Sharing in Action
Chapter 24. Querying Databases and XML Files
Excel and Databases
Connecting to an Access Database
Refreshing Data
Data Source Security
Connecting to a SQL Server Database
Reusing Your Database Connection
Understanding XML
What Is XML, Really?
Three Rules of XML
XML Files and Schemas
Excel and XML
Mapping a Simple Document
Importing and Exporting XML
Mapping Lists
Gaining the Benefits of XML Mapping
Chapter 25. Exchanging Data with Other Programs
Sharing Information in Windows
Embedding and Linking Objects
Exporting Charts out of Excel
Editing a Linked Object
Editing an Embedded Object
Importing Objects into Excel
Transferring Data
Exporting Tables of Data
Importing Tables of Data
Importing Text Files
Chapter 26. Connecting Worksheets to the Web
Putting Worksheets on the Web
Saving an HTML File
Performing Web Queries
The Limitations of Web Queries
Creating a Web Query
The Research Pane-a Web Query Alternative
Using Hyperlinks
Adding a Hyperlink to a Web Page or Document
Adding a Hyperlink to a Worksheet Location
The HYPERLINK( ) Function: Creating a Link on Your Own
Part Seven: Programming Excel
Chapter 27. Automating Tasks with Macros
Macros 101
Macro-Free and Macro-Enabled Workbooks
The Macro Recorder
Relative and Absolute Recording
Where Macros Live
Recording a Macro
Playing a Macro
Macro Security
The Trust Center
Setting Up a Trusted Location
Creating Practical Macros
Inserting a Header
Alternating Row Formatting
A Combined Task
Placing a Macro on the Quick Access Toolbar
Attaching a Macro to a Button Inside a Worksheet
Chapter 28. Programming Spreadsheets with VBA
The Visual Basic Editor
The Project Window
Modules and Macros
Finding and Moving Macros
Debugging a Macro
Understanding Macro Code
The Anatomy of a Macro
Objects 101
Using Properties and Methods
Hunting for Objects
Exploring the VBA Language
Entering Text in the Current Cell
Moving to Other Cells
Editing Specific Cells
Formatting Cells
Using Variables
Making Decisions
Repeating Actions with a Loop
Creating Custom Functions
Part Eight: Appendix
Appendix. Customizing the Quick Access Toolbar
Index