You’re beyond the basics, so dive in and really put your spreadsheet skills to work! This supremely organized reference is packed with hundreds of timesaving solutions, troubleshooting tips, and workarounds. It’s all muscle and no fluff. Discover how the experts tackle Excel 2007—and challenge yourself to new levels of mastery!
Learn expert techniques for designing powerful spreadsheets
Apply built-in functions—or write your own—and carry out complex calculations
Use rich charting capabilities and new SmartArt graphics to visually represent data
Perform sophisticated data analysis: financial, statistical, and “what-if”
Design PivotTable reports to dynamically analyze data
Share spreadsheets and collaborate with colleagues—and control access to sensitive data
Link and embed Excel data into other documents
Create macros with Microsoft Visual Basic for Applications
CD includes:
Fully searchable eBook
Sample spreadsheets from inside the book
Add-ins and other resources to help you extend Microsoft Office programs
Links to demos, user communities, and product support
References for finding Excel 2003 commands in Excel 2007
Windows Vista Product Guide eReference and other eBooks
A Note Regarding the CD or DVD
The print version of this book ships with a CD or DVD. For those customers purchasing one of the digital formats in which this book is available, we are pleased to offer the CD/DVD content as a free download via OReilly Medias Digital Distribution services. To download this content, please visit OReillys web site, search for the title of this book to find its catalog page, and click on the link below the cover image (Examples, Companion Content, or Practice Files). Note that while we provide as much of the media content as we are able via free download, we are sometimes limited by licensing restrictions. Please direct any questions or concerns to booktech@oreilly.com.
Examining the Excel Environment
Chapter 1 What’s New in Microsoft Office Excel 2007
New and Improved for 2007
Retired in 2007
If You Missed the Last Upgrade . . .
Onward . . .
Chapter 2 Exploring Excel Fundamentals
What Happens After You Install Excel?
Examining the Excel 2007 Workspace
Exploring File Management Fundamentals
Importing and Exporting Files
Using the Online Help System
Recovering from Crashes
Chapter 3 Custom-Tailoring the Excel Workspace
Customizing the Quick Access Toolbar
Controlling Other Elements of the Excel 2007 Interface
Enhancing Accessibility
Chapter 4 Security and Privacy
The Trust Center
File Security
Other Security Features
Building Worksheets
Chapter 5 Planning Your Worksheet Design
Which Data Should Be in Rows, and Which in Columns?
Will You Need to Print the Worksheet?
Who Is the Audience?
Would Your Worksheet Survive Without You?
Does the Worksheet Rely on Imported Data?
Do You Need More Than One Worksheet?
Have You Allowed Room for New Data?
Chapter 6 How to Work a Worksheet
Moving Around Regions
Understanding Selection
Techniques for Entering Data
Managing Worksheets
Viewing Worksheets
Protecting Worksheets
Chapter 7 How to Work a Workbook
Managing Multiple Workbooks
Opening Multiple Windows for the Same Workbook
Hiding and Protecting Workbooks
Formatting and Editing Worksheets
Chapter 8 Worksheet Editing Techniques
Copying, Cutting, and Pasting
Inserting and Deleting
Undoing Previous Actions
Editing Cell Contents
Finding and Replacing Stuff
Getting the Words Right
Editing Multiple Worksheets
Auditing and Documenting Worksheets
Outlining Worksheets
Consolidating Worksheets
Chapter 9 Worksheet Formatting Techniques
Formatting Fundamentals
Using Themes and Cell Styles
Formatting Conditionally
Formatting in Depth
Using Template Files to Store Formatting
Adding Graphics and Printing
Chapter 10 Creating Spiffy Graphics
Using the Shapes Tools
Creating WordArt
Creating SmartArt
Inserting Graphics
Formatting Graphics
Working with Graphic Objects
More Tricks with Graphic Objects
Gallery of Spiffy Examples
Chapter 11 Printing and Presenting
Controlling the Appearance of Your Pages
Specifying What and Where to Print
Adjusting Page Breaks
Using Print Preview
Setting Printer Driver Options
Creating Portable Documents
Creating Formulas and Performing Data Analysis
Chapter 12 Building Formulas
Formula Fundamentals
Using Functions: A Preview
Working with Formulas
Worksheet Calculation
Using Arrays
Linking Workbooks
Creating Conditional Tests
Chapter 13 Using Functions
Using the Built-In Function Reference in Excel
Exploring the Syntax of Functions
Inserting Functions
Chapter 14 Everyday Functions
Understanding Mathematical Functions
Understanding Text Functions
Understanding Logical Functions
Understanding Information Functions
Understanding Lookup and Reference Functions
Chapter 15 Formatting and Calculating Date and Time
Understanding How Excel Records Dates and Times
Entering Dates and Times
Formatting Dates and Times
Calculating with Date and Time
Working with Date and Time Functions
Chapter 16 Functions for Financial Analysis
Calculating Investments
Calculating Depreciation
Analyzing Securities
Using the Euro Currency Tools Add-In
Chapter 17 Functions for Analyzing Statistics
Analyzing Distributions of Data
Understanding Linear and Exponential Regression
Using the Analysis Toolpak Data Analysis Tools
Chapter 18 Performing What-If Analysis
Using Data Tables
Using the Scenario Manager
Using the Goal Seek Command
Using the Solver
Creating Charts
Chapter 19 Basic Charting Techniques
Selecting Data for Your Chart
Choosing a Chart Type
Changing the Chart Type
Switching Rows and Columns
Choosing a Chart Layout
Choosing a Chart Style
Moving the Chart to a Separate Chart Sheet
Adding, Editing, and Removing a Chart Title
Adding, Editing, and Removing a Legend
Adding and Positioning Data Labels
Adding a Data Table
Manipulating Axes
Adding Axis Titles
Changing the Rotation of Chart Text
Displaying Gridlines
Adding Text Annotations
Changing the Font or Size of Chart Text
Applying Shape Styles and WordArt Styles
Saving Templates to Make Chart Formats Reusable
Chapter 20 Charting Beyond the Ribbon
Selecting Chart Elements
Repositioning Chart Elements with the Mouse
Formatting Lines and Borders
Formatting Areas
Formatting Text
Working with Axes
Working with Data Labels
Formatting Data Series and Markers
Modifying the Data Source for Your Chart
Using Multilevel Categories
Adding Moving Averages and Other Trendlines
Adding Error Bars
Adding High-Low Lines and Up and Down Bars
Managing Databases and Tables
Chapter 21 Managing Information in Tables
How to Organize a Table
Creating a Table
Adding Totals to a Table
Sorting Tables and Other Ranges
Filtering a List or Table
Using Formulas with Tables
Formatting Tables
Chapter 22 Analyzing Data with PivotTable Reports
Introducing PivotTables
Creating a PivotTable
Rearranging PivotTable Fields
Refreshing a PivotTable
Changing the Numeric Format of PivotTable Data
Choosing Report Layout Options
Formatting a PivotTable
Displaying Totals and Subtotals
Sorting PivotTable Fields
Filtering PivotTable Fields
Changing PivotTable Calculations
Grouping and Ungrouping Data
Displaying the Details Behind a Data Value
Creating PivotCharts
Chapter 23 Working with External Data
Using and Reusing Data Connections
Opening an Entire Access Table in Excel
Working with Data in Text Files
Working with XML Files
Using Microsoft Query to Import Data
Using a Web Query to Return Internet Data
Collaborating
Chapter 24 Collaborating on a Network or by E-Mail
Saving and Retrieving Files on Remote Computers
Sharing Workbooks on a Network
Combining Changes Made to Multiple Workbooks
Distributing Workbooks and Worksheets by E-Mail
Controlling Document Access with Information Rights Management
Chapter 25 Collaborating Using the Internet
Using a Windows SharePoint Services Site
Exporting Excel Tables to SharePoint
Using Excel Services
Automating Excel
Chapter 26 Recording Macros
Configuring Macro Security
Using the Macro Recorder
Introducing the Visual Basic Editor
Learning the Basics of Visual Basic
Adding Code to or Editing Recorded Macros
Using Subroutines in Macros
Using the Personal Macro Workbook
Going On from Here
Chapter 27 Creating Custom Functions
Creating a Simple Custom Function
Using Custom Functions
Understanding Custom Function Rules
Using VBA Keywords in Custom Functions
Documenting Macros and Custom Functions
Creating Custom Functions with Optional Arguments
Making Your Custom Functions Available Anywhere
Chapter 28 Debugging Macros and Custom Functions
Using Design-Time Tools
Dealing with Run-Time Errors
Integrating Excel with Other Applications
Chapter 29 Using Hyperlinks
Creating a Hyperlink in a Cell
Assigning a Hyperlink to a Shape, Image, or Chart
Selecting a Cell or Object Without Triggering the Hyperlink
Mark Dodge has coauthored several popular books on Excel®, including Microsoft® Office Excel® 2003 Inside Out. He is a former senior technical writer for the Microsoft® Office User Assistance group.
Craig Stinson is a journalist and author. He has written or coauthored more than 20 books including Windows Vista® Inside Out, Deluxe Edition and Microsoft® Office Excel® 2007 Inside Out.
