- •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 IV: Using Advanced Excel Features
Working with Hyperlinks
A hyperlink is clickable text that provides a quick way to jump to other workbooks and files. You can set up hyperlinks to display files on your own computer, your network, and the Web. For example, you can create a series of hyperlinks to serve as a table of contents for a workbook. Or, you can insert a hyperlink that displays a Web page in the default Web browser.
Inserting a hyperlink
You can create hyperlinks from cell text or graphic objects, such as shapes and pictures. To create a text hyperlink in a cell, select the cell and choose Insert Links Hyperlink (or press Ctrl+K). Excel responds with the Insert Hyperlink dialog box, as shown in Figure 28.4.
FIGURE 28.4
Use the Insert Hyperlink dialog box to add hyperlinks to your Excel worksheets.
Select an icon in the Link To column that represents the type of hyperlink you want to create. You can create hyperlinks to a file on your hard drive, a Web page on the Internet, a new document, or a location in your current workbook. In addition, you can create a hyperlink that consists of an e-mail address. Then, specify the location of the file that you want to link to. The dialog box changes, depending upon the icon selected. If you like, click the ScreenTip button to provide some additional text that appears as a mouse-hover–activated ToolTip. Click OK, and Excel creates the hyperlink in the active cell.
Figure 28.5 shows a worksheet with hyperlinks that function as a table of contents for a workbook. Clicking a link activates a worksheet in the workbook. The example also shows an e-mail address that when clicked, activates the default e-mail program.
610
Chapter 28: Excel and the Internet
The appearance of hyperlinks in cells is controlled by two styles in the Style Gallery. The Hyperlink style controls the appearance of hyperlinks that haven’t been clicked, and the Followed Hyperlink style controls the appearance of “visited” hyperlinks. To change the appearance of your hyperlinks, modify either or both of those styles. See Chapter 6 for information about using and modifying document styles.
To add a hyperlink to a Shape, select the Shape and then choose Insert Links Hyperlink (or, press Ctrl+K). Specify the required information in the Insert Hyperlink dialog box, as outlined earlier in this section. (Read more about Shapes in Chapter 22.)
FIGURE 28.5
Hyperlinks in a workbook.
Using hyperlinks
When you hover your mouse pointer over a cell that contains a hyperlink, the mouse pointer turns into a hand. Click the hyperlink, and you’re taken to the hyperlinked document.
Tip
To select a cell that has a hyperlink with your mouse (without following the hyperlink), position your mouse over the cell, click, and hold for a second or two before you release the mouse button. Or just activate a nearby cell and use the navigation keys to select the cell that contains the hyperlink. n
When you hover your pointer over a Shape that contains a hyperlink, the mouse pointer turns into a hand. To follow a hyperlink from a Shape, just point to the Shape and click.
If the hyperlink contains an e-mail address, your default e-mail program launches so that you can send an e-mail to the address specified when you created the hyperlink.
611
Part IV: Using Advanced Excel Features
Using Web Queries
Excel enables you to pull in data contained in an HTML file by performing a Web query. The data is transferred to a worksheet, where you can manipulate it any way you like. Web queries are especially useful for data that is frequently updated, such as stock market quotes.
The term “Web Query” is a bit misleading. You can perform a Web Query on a local HTML file, a file stored on a network server, or a file stored on a Web server on the Internet. To retrieve information from a Web server, you must be connected to the Internet. After the information is retrieved, an Internet connection is not required to work with the information (unless you need to refresh the query).
Note
Performing a Web query doesn’t actually open the HTML file in Excel. Rather, it copies the information from the HTML file. n
The best part about a Web query is that Excel remembers where the data came from. Therefore, after you create a Web query, you can refresh the query to pull in the most recent data.
To create a Web query
1.Choose Data Get External Data From Web. Excel displays the New Web Query dialog box, shown in Figure 28.6. This dialog box is actually a resizable dialog box that functions as a Web browser.
2.Click links to navigate, or just type the URL of the HTML file in the Address field.
The HTML file can be on the Internet, on a corporate intranet, or on a local or network drive. Each table in the document is indicated by an arrow in a yellow box.
3.Click an arrow to select the table or tables you want to import.
4.You can also control how the imported data is formatted. In the New Web Query dialog box, click Options (upper right) to display the Web Query Options dialog box. Select the desired formatting and then click OK to return to the New Web Query dialog box.
5.When you’re ready to retrieve the information, click Import.
6.In the Import Data dialog box that opens, choose where you want to place the data.
The information on the Web page is retrieved and placed on your worksheet.
After you create your Web query, you have some options. Right-click any cell in the data range and choose Data Range Properties from the shortcut menu. Excel displays the External Data Range Properties dialog box, shown in Figure 28.7. These settings control when the data is refreshed, how it is formatted, and what happens if the amount of data changes when the query is refreshed.
To force a refresh at any time, right-click any cell in the data range and choose Refresh. The data in your worksheet is replaced by the latest of content of the Web page.
612
Chapter 28: Excel and the Internet
FIGURE 28.6
Use the New Web Query dialog box to specify the source of the data.
FIGURE 28.7
Use the External Data Range Properties dialog box to specify how Excel handles the imported data.
613