Formulas and Functions with Microsoft Office Excel 2007

by McFedries, Paul

• ISBN13: 9780789736680
• ISBN10: 0789736683
• Edition: 1st
• Format: Paperback
• Copyright: 2007-03-14
• Publisher: Que Pub

Summary

Power users finally have a book to help them create user friendly data analysis tools, formulas, and timesaving lists!

Author Biography

Paul McFedries is the president of Logophilia Limited, a technical writing company. Now primarily a writer, Paul has worked as a programmer, consultant, spreadsheet developer, and website developer. He has written more than 50 books that have sold more than three million copies worldwide. These books include Access 2007 Forms, Reports, and Queries (Que, 2007), Tricks of the Microsoft Office 2007 Gurus (Que, 2007), VBA for the 2007 Microsoft Office System (Que, 2007), and Windows Vista Unleashed (Sams, 2006).

Table of Contents

Introduction

Whats in the Book

This Books Special Features

I Mastering Excel Ranges and Formulas

Getting the Most Out of Ranges

Advanced Range-Selection Techniques

Mouse Range-Selection Tricks

Keyboard Range-Selection Tricks

Working with 3D Ranges

Selecting a Range Using Go To

Using the Go To Special Dialog Box

Data Entry in a Range

Filling a Range

Using the Fill Handle

Using AutoFill to Create Text and Numeric Series

Creating a Custom AutoFill List

Filling a Range

Creating a Series

Advanced Range Copying

Copying Selected Cell Attributes

Combining the Source and Destination Arithmetically

Transposing Rows and Columns

Clearing a Range

Applying Conditional Formatting to a Range

Creating Highlight Cells Rules

Creating Top/Bottom Rules

Adding Data Bars

Adding Color Scales

Adding Icon Sets From Here

Using Range Names

Defining a Range Name

Working with the Name Box

Using the New Name Dialog Box

Changing the Scope to Define Sheet-Level Names

Using Worksheet Text to Define Names

Naming Constants

Working with Range Names

Referring to a Range Name

Working with Name AutoComplete

Navigating Using Range Names

Pasting a List of Range Names in a Worksheet

Displaying the Name Manager

Filtering Names

Editing a Range Names Coordinates

Adjusting Range Name Coordinates Automatically

Changing a Range Name

Deleting a Range Name

Using Names with the Intersection Operator From Here

3 Building Basic Formulas

Understanding Formula Basics

Formula Limits in Excel 2007

Entering and Editing Formulas

Using Arithmetic Formulas

Using Comparison Formulas

Using Text Formulas

Using Reference Formulas

Understanding Operator Precedence

The Order of Precedence

Controlling the Order of Precedence

Controlling Worksheet Calculation

Copying and Moving Formulas

Understanding Relative Reference Format

Understanding Absolute Reference Format

Copying a Formula Without Adjusting Relative References

Displaying Worksheet Formulas

Converting a Formula to a Value

Working with Range Names in Formulas

Pasting a Name into a Formula

Applying Names to Formulas

Naming Formulas

Working with Links in Formulas

Understanding External References

Updating Links

Changing the Link Source

Formatting Numbers, Dates, and Times

Numeric Display Formats

Date and Time Display Formats

Deleting Custom Formats From Here

4 Creating Advanced Formulas

Working with Arrays

Using Array Formulas

Using Array Constants

Functions That Use or Return Arrays

Using Iteration and Circular References

Consolidating Multisheet Data

Consolidating by Position

Consolidating by Category

Applying Data-Validation Rules to Cells

Using Dialog Box Controls on a Worksheet

Using the Form Controls

Adding a Control to a Worksheet

Linking a Control to a Cell Value

Understanding the Worksheet Controls From Here

5 Troubleshooting Formulas

Understanding Excels Error Values #DIV/0! #N/A #NAME? Avoiding #NAME?

Errors When Deleting Range Names #NULL! #NUM! #REF! #VALUE!

Fixing Other Formula Errors

Missing or Mismatched Parentheses

Erroneous Formula Results

Fixing Circular References

Handling Formula Errors with IFERROR()

Using the Formula Error Checker

Choosing an Error Action

Setting Error Checker Options

Auditing a Worksheet

Understanding

Table of Contents provided by Publisher. All Rights Reserved.