- •About the Author
- •About the Technical Editor
- •Credits
- •Is This Book for You?
- •Software Versions
- •Conventions This Book Uses
- •What the Icons Mean
- •How This Book Is Organized
- •How to Use This Book
- •What’s on the Companion CD
- •What Is Excel Good For?
- •What’s New in Excel 2010?
- •Moving around a Worksheet
- •Introducing the Ribbon
- •Using Shortcut Menus
- •Customizing Your Quick Access Toolbar
- •Working with Dialog Boxes
- •Using the Task Pane
- •Creating Your First Excel Worksheet
- •Entering Text and Values into Your Worksheets
- •Entering Dates and Times into Your Worksheets
- •Modifying Cell Contents
- •Applying Number Formatting
- •Controlling the Worksheet View
- •Working with Rows and Columns
- •Understanding Cells and Ranges
- •Copying or Moving Ranges
- •Using Names to Work with Ranges
- •Adding Comments to Cells
- •What Is a Table?
- •Creating a Table
- •Changing the Look of a Table
- •Working with Tables
- •Getting to Know the Formatting Tools
- •Changing Text Alignment
- •Using Colors and Shading
- •Adding Borders and Lines
- •Adding a Background Image to a Worksheet
- •Using Named Styles for Easier Formatting
- •Understanding Document Themes
- •Creating a New Workbook
- •Opening an Existing Workbook
- •Saving a Workbook
- •Using AutoRecover
- •Specifying a Password
- •Organizing Your Files
- •Other Workbook Info Options
- •Closing Workbooks
- •Safeguarding Your Work
- •Excel File Compatibility
- •Exploring Excel Templates
- •Understanding Custom Excel Templates
- •Printing with One Click
- •Changing Your Page View
- •Adjusting Common Page Setup Settings
- •Adding a Header or Footer to Your Reports
- •Copying Page Setup Settings across Sheets
- •Preventing Certain Cells from Being Printed
- •Preventing Objects from Being Printed
- •Creating Custom Views of Your Worksheet
- •Understanding Formula Basics
- •Entering Formulas into Your Worksheets
- •Editing Formulas
- •Using Cell References in Formulas
- •Using Formulas in Tables
- •Correcting Common Formula Errors
- •Using Advanced Naming Techniques
- •Tips for Working with Formulas
- •A Few Words about Text
- •Text Functions
- •Advanced Text Formulas
- •Date-Related Worksheet Functions
- •Time-Related Functions
- •Basic Counting Formulas
- •Advanced Counting Formulas
- •Summing Formulas
- •Conditional Sums Using a Single Criterion
- •Conditional Sums Using Multiple Criteria
- •Introducing Lookup Formulas
- •Functions Relevant to Lookups
- •Basic Lookup Formulas
- •Specialized Lookup Formulas
- •The Time Value of Money
- •Loan Calculations
- •Investment Calculations
- •Depreciation Calculations
- •Understanding Array Formulas
- •Understanding the Dimensions of an Array
- •Naming Array Constants
- •Working with Array Formulas
- •Using Multicell Array Formulas
- •Using Single-Cell Array Formulas
- •Working with Multicell Array Formulas
- •What Is a Chart?
- •Understanding How Excel Handles Charts
- •Creating a Chart
- •Working with Charts
- •Understanding Chart Types
- •Learning More
- •Selecting Chart Elements
- •User Interface Choices for Modifying Chart Elements
- •Modifying the Chart Area
- •Modifying the Plot Area
- •Working with Chart Titles
- •Working with a Legend
- •Working with Gridlines
- •Modifying the Axes
- •Working with Data Series
- •Creating Chart Templates
- •Learning Some Chart-Making Tricks
- •About Conditional Formatting
- •Specifying Conditional Formatting
- •Conditional Formats That Use Graphics
- •Creating Formula-Based Rules
- •Working with Conditional Formats
- •Sparkline Types
- •Creating Sparklines
- •Customizing Sparklines
- •Specifying a Date Axis
- •Auto-Updating Sparklines
- •Displaying a Sparkline for a Dynamic Range
- •Using Shapes
- •Using SmartArt
- •Using WordArt
- •Working with Other Graphic Types
- •Using the Equation Editor
- •Customizing the Ribbon
- •About Number Formatting
- •Creating a Custom Number Format
- •Custom Number Format Examples
- •About Data Validation
- •Specifying Validation Criteria
- •Types of Validation Criteria You Can Apply
- •Creating a Drop-Down List
- •Using Formulas for Data Validation Rules
- •Understanding Cell References
- •Data Validation Formula Examples
- •Introducing Worksheet Outlines
- •Creating an Outline
- •Working with Outlines
- •Linking Workbooks
- •Creating External Reference Formulas
- •Working with External Reference Formulas
- •Consolidating Worksheets
- •Understanding the Different Web Formats
- •Opening an HTML File
- •Working with Hyperlinks
- •Using Web Queries
- •Other Internet-Related Features
- •Copying and Pasting
- •Copying from Excel to Word
- •Embedding Objects in a Worksheet
- •Using Excel on a Network
- •Understanding File Reservations
- •Sharing Workbooks
- •Tracking Workbook Changes
- •Types of Protection
- •Protecting a Worksheet
- •Protecting a Workbook
- •VB Project Protection
- •Related Topics
- •Using Excel Auditing Tools
- •Searching and Replacing
- •Spell Checking Your Worksheets
- •Using AutoCorrect
- •Understanding External Database Files
- •Importing Access Tables
- •Retrieving Data with Query: An Example
- •Working with Data Returned by Query
- •Using Query without the Wizard
- •Learning More about Query
- •About Pivot Tables
- •Creating a Pivot Table
- •More Pivot Table Examples
- •Learning More
- •Working with Non-Numeric Data
- •Grouping Pivot Table Items
- •Creating a Frequency Distribution
- •Filtering Pivot Tables with Slicers
- •Referencing Cells within a Pivot Table
- •Creating Pivot Charts
- •Another Pivot Table Example
- •Producing a Report with a Pivot Table
- •A What-If Example
- •Types of What-If Analyses
- •Manual What-If Analysis
- •Creating Data Tables
- •Using Scenario Manager
- •What-If Analysis, in Reverse
- •Single-Cell Goal Seeking
- •Introducing Solver
- •Solver Examples
- •Installing the Analysis ToolPak Add-in
- •Using the Analysis Tools
- •Introducing the Analysis ToolPak Tools
- •Introducing VBA Macros
- •Displaying the Developer Tab
- •About Macro Security
- •Saving Workbooks That Contain Macros
- •Two Types of VBA Macros
- •Creating VBA Macros
- •Learning More
- •Overview of VBA Functions
- •An Introductory Example
- •About Function Procedures
- •Executing Function Procedures
- •Function Procedure Arguments
- •Debugging Custom Functions
- •Inserting Custom Functions
- •Learning More
- •Why Create UserForms?
- •UserForm Alternatives
- •Creating UserForms: An Overview
- •A UserForm Example
- •Another UserForm Example
- •More on Creating UserForms
- •Learning More
- •Why Use Controls on a Worksheet?
- •Using Controls
- •Reviewing the Available ActiveX Controls
- •Understanding Events
- •Entering Event-Handler VBA Code
- •Using Workbook-Level Events
- •Working with Worksheet Events
- •Using Non-Object Events
- •Working with Ranges
- •Working with Workbooks
- •Working with Charts
- •VBA Speed Tips
- •What Is an Add-In?
- •Working with Add-Ins
- •Why Create Add-Ins?
- •Creating Add-Ins
- •An Add-In Example
- •System Requirements
- •Using the CD
- •What’s on the CD
- •Troubleshooting
- •The Excel Help System
- •Microsoft Technical Support
- •Internet Newsgroups
- •Internet Web sites
- •End-User License Agreement
Part II: Working with Formulas and Functions
Tip
If your worksheet uses any data tables (described in Chapter 36), you may want to select the Automatically Except for Data Tables option. Large data tables calculate notoriously slowly. Note: A data table is not the same as a table created by choosing Insert Tables Table. n
When you’re working in Manual Calculation mode, Excel displays Calculate in the status bar when you have any uncalculated formulas. You can use the following shortcut keys to recalculate the formulas:
•F9: Calculates the formulas in all open workbooks.
•Shift+F9: Calculates only the formulas in the active worksheet. Other worksheets in the same workbook aren’t calculated.
•Ctrl+Alt+F9: Forces a complete recalculation of all formulas.
Note
Excel’s Calculation mode isn’t specific to a particular worksheet. When you change the Calculation mode, it affects all open workbooks, not just the active workbook. n
Using Advanced Naming Techniques
Using range names can make your formulas easier to understand, easier to modify, and even help prevent errors. It’s much easier to deal with a meaningful name such as AnnualSales than with a range reference, such as AB12:AB68.
Cross-Reference
See Chapter 4 for basic information regarding working with names. n
Excel offers a number of advanced techniques that make using names even more useful. I discuss these techniques in the sections that follow.
Using names for constants
Many Excel users don’t realize that you can give a name to an item that doesn’t appear in a cell. For example, if formulas in your worksheet use a sales-tax rate, you would probably insert the tax-rate value into a cell and use this cell reference in your formulas. To make things easier, you would probably also name this cell something similar to SalesTax.
Here’s how to provide a name for a value that doesn’t appear in a cell:
1.Choose Formulas Defined Names Define Name. Excel displays the New Name dialog box.
2.Enter the name (in this case, SalesTax) into the Name field.
222
Chapter 10: Introducing Formulas and Functions
3.Select a scope in which the name will be valid (either the entire workbook or a specific worksheet).
4.Click the Refers To text box, delete its contents, and replace the old contents with a value (such as .075).
5.(Optional). Use the Comment box to provide a comment about the name.
6.Click OK to close the New Name dialog box and create the name.
You just created a name that refers to a constant rather than a cell or range. Now if you type =SalesTax into a cell that’s within the scope of the name, this simple formula returns 0.075 — the constant that you defined. You also can use this constant in a formula, such as
=A1*SalesTax.
Tip
A constant also can be text. For example, you can define a constant for your company’s name. n
Note
Named constants don’t appear in the Name box or in the Go To dialog box. This makes sense because these constants don’t reside anywhere tangible. They do appear in the drop-down list that’s displayed when you enter a formula — which is handy because you use these names in formulas. n
Using names for formulas
Just like you can create a named constant, you can also create named formulas. Like with named constants, named formulas don’t appear in the worksheet.
You create named formulas the same way you create named constants — by using the New Name dialog box. For example, you might create a named formula that calculates the monthly interest rate from an annual rate; Figure 10.16 shows an example. In this case, the name MonthlyRate refers to the following formula:
=Sheet3!$B$1/12
FIGURE 10.16
Excel allows you to name a formula that doesn’t exist in a worksheet cell.
223
Part II: Working with Formulas and Functions
When you use the name MonthlyRate in a formula, it uses the value in B1 divided by 12. Notice that the cell reference is an absolute reference.
Naming formulas gets more interesting when you use relative references rather than absolute references. When you use the pointing technique to create a formula in the Refers To field of the New Name dialog box, Excel always uses absolute cell references — which is unlike its behavior when you create a formula in a cell.
For example, activate cell B1 on Sheet1 and create the name Cubed for the following formula:
=Sheet1!A1^3
In this example, the relative reference points to the cell to the left of the cell in which the name is used. Therefore, make certain that cell B1 is the active cell before you open the New Name dialog box; this is very important. The formula contains a relative reference; when you use this named formula in a worksheet, the cell reference is always relative to the cell that contains the formula. For example, if you enter =Cubed into cell D12, then cell D12 displays the contents of cell C12 raised to the third power (C12 is the cell directly to the left of D12).
Using range intersections
This section describes a concept known as range intersections — individual cells that two ranges have in common. Excel uses an intersection operator — a space character — to determine the overlapping references in two ranges. Figure 10.17 shows a simple example.
FIGURE 10.17
You can use a range-intersection formula to determine values.
The formula in cell B9 is
=B1:B6 A3:D3
This formula returns 130, the value in cell B3 — that is, the value at the intersection of the two ranges.
224
Chapter 10: Introducing Formulas and Functions
The intersection operator is one of three reference operators used with ranges. Table 10.4 lists these operators.
TABLE 10.4
|
Reference Operators for Ranges |
Operator |
What It Does |
|
|
: (colon) |
Specifies a range. |
|
|
, (comma) |
Specifies the union of two ranges. This operator combines multiple range references |
|
into a single reference. |
|
|
(space) |
Specifies the intersection of two ranges. This operator produces cells that are common |
|
to two ranges. |
|
|
The real value of knowing about range intersections is apparent when you use names. Examine Figure 10.18, which shows a table of values. I selected the entire table and then used Formulas Defined Names Create from Selection to create names automatically by using the top row and left column.
FIGURE 10.18
When you use names, using a range-intersection formula to determine values is even more useful.
Excel created the following names:
North |
=Sheet1!$B$2:$E$2 |
Quarter1 =Sheet1!$B$2:$B$5 |
|
South |
=Sheet1!$B$3:$E$3 |
Quarter2 =Sheet1!$C$2:$C$5 |
|
West |
=Sheet1!$B$4:$E$4 |
Quarter3 |
=Sheet1!$D$2:$D$5 |
East |
=Sheet1!$B$5:$E$5 |
Quarter4 |
=Sheet1!$E$2:$E$5 |
With these names defined, you can create formulas that are easy to read and use. For example, to calculate the total for Quarter 4, just use this formula:
=SUM(Quarter4)
225
Part II: Working with Formulas and Functions
To refer to a single cell, use the intersection operator. Move to any blank cell and enter the following formula:
=Quarter1 West
This formula returns the value for the first quarter for the West region. In other words, it returns the value that exists where the Quarter1 range intersects with the West range. Naming ranges in this manner can help you create very readable formulas.
Applying names to existing references
When you create a name for a cell or a range, Excel doesn’t automatically use the name in place of existing references in your formulas. For example, suppose you have the following formula in cell F10:
=A1–A2
If you define a name Income for A1 and Expenses for A2, Excel won’t automatically change your formula to =Income–Expenses. Replacing cell or range references with their corresponding names is fairly easy, however.
To apply names to cell references in formulas after the fact, start by selecting the range that you want to modify. Then choose Formulas Defined Names Define Name Apply Names. Excel displays the Apply Names dialog box, as shown in Figure 10.19. Select the names that you want to apply by clicking them and then click OK. Excel replaces the range references with the names in the selected cells.
FIGURE 10.19
Use the Apply Names dialog box to replace cell or range references with defined names.
226