- •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
Other Internet-Related Features
Excel offers a few other Internet-related features, which I briefly describe here. Access these features from the Share tab of Backstage View (choose File Share). Figure 28.8 shows the Share options:
•Send Using E-Mail: Enables you to send the active workbook to one or more recipients via e-mail. The file can be the actual workbook, a PDF file, or an XPS file. If the workbook is saved to a shared location, you can send a link to the file (rather than the actual file). An additional option lets you fax the workbook (assuming that your system has a fax service provider).
•Save to SkyDrive: If you have a SkyDrive account, you can save the workbook to a folder on the Internet, where it can be accessed by others. SkyDrive is a free service offered by Microsoft (part of Windows Live). Go to www.windowslive.com for details.
•Save to SharePoint: Publish the workbook to your SharePoint server. A SharePoint server is a central location where files can be shared and worked on in collaborative manner. Check with your system administrator to see whether you have access to a SharePoint server.
•Publish to Excel Services: Excel Services also requires a SharePoint server.
FIGURE 28.8
The Share tab of Backstage View.
614
CHAPTER
Sharing Data with
Other Office
Applications
The applications in Microsoft Office are designed to work together. These programs have a common look and feel, and sharing data among these applications is usually quite easy.
Excel can import and export a variety of different file types. Besides sharing data using files, you can also transfer data to and from other open Windows applications in several other ways:
•Copy and paste, using either the Windows Clipboard or the Office Clipboard. Copying and pasting information creates a static copy of the data.
•Create a link so that subsequent changes in the source data will be reflected in the destination document.
•Embed an entire object from one application into another application’s document.
This chapter explores some ways in which you can make use of other applications while working with Excel, as well as some ways in which you can use Excel while working with other applications.
Copying and Pasting
Copying information from one Windows application to another is quite easy. The application that contains the information that you’re copying is the source application, and the application to which you’re copying the information is the destination application.
IN THIS CHAPTER
Understanding data sharing
Pasting and linking data
Embedding objects in a worksheet
Embedding a workbook in a Word document
615
Part IV: Using Advanced Excel Features
Here are the general steps that are required to copy from one application to another:
1.Activate the source document window that contains the information that you want to copy.
2.Select the information, using the mouse or the keyboard.
3.If the source application is a Microsoft Office 2010 application, choose Home Clipboard Copy. Most other applications have an Edit Copy menu command. In most cases, pressing Ctrl+C works as well. The selection is copied to the Clipboard.
Tip
You also can choose Home Clipboard Cut (or Edit Cut) from the source application menu. This step deletes your selection from the source application after placing the selection on the Clipboard. n
4.Activate the destination application. If the program isn’t running, you can usually start it without affecting the contents of the Clipboard.
5.Move to the appropriate position in the destination application (where you want to paste the copied material).
6.If the destination application is a Microsoft Office 2010 application, choose Home Clipboard Paste. Most other applications have an Edit Paste command, and you can usually use Ctrl+V to paste. If the Clipboard contents aren’t appropriate for pasting, the Paste command is disabled. You can sometimes select other paste options by choosing Home Clipboard Paste (or Edit Paste Special).
Note
If you repeat Step 3 in any Office application, the Office Clipboard task pane appears automatically. If it doesn’t appear, click the dialog launcher in the Home Clipboard group. Use the Options button at the bottom of the Office Clipboard to control when this task pane appears. n
Two Clipboards
If you copy or cut information while working in a Microsoft Office application, the application places the copied information on both the Windows Clipboard and the Office Clipboard. After you copy information to the Windows Clipboard, it remains on the Windows Clipboard even after you paste it so that you can use it multiple times.
Because the Windows Clipboard can hold only one item at a time, when you copy or cut something else, the information previously stored on the Windows Clipboard is replaced. The Office Clipboard, unlike the Windows Clipboard, can hold up to 24 separate selections. The Office Clipboard operates in all Office applications; for example, you can copy two selections from Word and three from Excel and paste any or all of them in PowerPoint.
616
Chapter 29: Sharing Data with Other Office Applications
Cross-Reference
See Chapter 4 for more information about the Office Clipboard. n
Copying from Excel to Word
One of the most frequently used software combinations is a spreadsheet and a word processor. This section describes some of the ways to copy information from an Excel worksheet to a Word document. Although I talk about Word here, most of the information in this section also applies to PowerPoint.
Generally speaking, you can copy something from Excel and paste it into Word in one of two ways:
•As static information: If the Excel data changes, the changes aren’t reflected in the Word document.
•As a link: If the Excel data changes, the changes are shown in the Word document.
You can find all the various paste options for Word in the Paste drop-down list of Word’s Home Clipboard group. In addition, various paste options are available from the Paste Special dialog box, which appears when you choose Home Clipboard Paste Paste Special.
Note
If you use Word’s standard paste command (Home Clipboard Paste, or Ctrl+V), you’ll find that the paste method varies, depending on what is pasted. An Excel range copied and pasted to Word is pasted as a static table. An Excel chart, on the other hand, is pasted as a link. n
Pasting static information
Often, you don’t need a link when you copy data from Excel to Word. For example, if you’re preparing a report in your word processor and you simply want to include a range of data from an Excel worksheet, you probably don’t need to create a link (unless the data in the Excel worksheet may be changed).
After you’ve copied an Excel range, activate Word and choose Home Clipboard Paste, or press Ctrl+V. The range appears as a Word table and is not linked to the Excel workbook.
New Feature
When you paste information from Excel into a Word document, Word 2010 displays a Paste Options Smart Tag, shown in Figure 29.1. This lets you choose a formatting option for the pasted information. n
617
Part IV: Using Advanced Excel Features
FIGURE 29.1
A Paste Options Smart Tag appears when you paste Excel 2010 data into a Word 2010 document.
For more control over pasting, use Home Clipboard Paste Paste Special, which displays the Paste Special dialog box (see Figure 29.2).
FIGURE 29.2
The Paste Special dialog box in Word.
618
Chapter 29: Sharing Data with Other Office Applications
Notice the two option buttons: Paste and Paste Link. If you select one of the choices in the Paste Special dialog box with the Paste option selected, the data is pasted without creating a link.
The paste options in Word’s Paste Special dialog box when a range is copied include
•Microsoft Excel Worksheet Object: You can edit this object with Excel. Double-click, and the Word Ribbon is replaced with the Excel Ribbon. See “Embedding an Excel range in a Word document,” later in this chapter.
•Formatted Text (RTF): The range is pasted as a table, with some formatting retained.
•Unformatted Text: Only the raw information is pasted, with no formatting. Cells are separated with a Tab character.
•Picture (Windows Metafile): The range is pasted as a picture.
•Bitmap: The range is pasted as a picture.
•Picture (Enhanced Metafile): The range is pasted as a picture.
•HTML Format: The range is pasted as a table, with all formatting retained. This format is pasted when you choose Home Clipboard Paste.
•Unformatted Unicode Text: Only the raw information is pasted, with no formatting. Cells are separated with a Tab character.
If you’ve copied an Excel chart to the Clipboard, Word’s Paste Special dialog box displays different options. Figure 29.3 shows the Paste Special dialog box when an Excel chart is copied. The options are
•Microsoft Excel Chart Object: You can edit this object with Excel. Double-click, and the Word Ribbon is replaced with the Excel Ribbon.
•Picture (Windows Metafile): The chart is pasted as a picture.
•Bitmap: The chart is pasted as bitmap image.
•Picture (Enhanced Metafile): The chart is pasted as a picture.
•Picture (GIF): The chart is pasted as a GIF image.
•Picture (PNG): The chart is pasted as a PNG (Portable Network Graphics) image.
•Picture (JPEG): The chart is pasted as a JPEG image, which usually results in a fuzzy image.
•Microsoft Office Graphic Object: The image is linked to the Excel range, and you can also edit it in Word. This format is pasted when you choose Home Clipboard Paste.
619
Part IV: Using Advanced Excel Features
FIGURE 29.3
Word paste options when an Excel chart is on the Clipboard.
Pasting a link
If the Excel data that you’re copying will change, you may want to paste a link. Use the Paste Link option in the Paste Special dialog box.
When would you want to use this technique? If you generate proposals using Word, for example, you may need to refer to pricing information that you store in an Excel worksheet. If you set up a link between your Word document and the Excel worksheet, you can be sure that your proposals always quote the latest prices.
The link is a one-way link. You cannot make changes to the linked Excel worksheet in Word.
If you paste the data by using the Paste Link option in the Paste Special dialog box, you can make changes to the source document, and those changes appear in the destination application. You can test these changes by displaying both applications onscreen, making changes to the source document, and watching for them to appear in the destination document.
Caution
You can break links rather easily. For example, if you move the source document to another directory or save it under a different name, the destination document’s application can’t update the link. In such a case, you need to re-establish the link manually (described later in this section). n
Figure 29.4 shows the Word Paste Special dialog box when an Excel range has been copied and the Paste Link option is specified. Note that, with one exception, these options are the same ones available when you select the Paste option. The only format that isn’t available for pasting a link is Picture (Enhanced Metafile).
620
Chapter 29: Sharing Data with Other Office Applications
FIGURE 29.4
The Word paste link options for an Excel range.
When an Excel chart is on the Clipboard, you can also choose the Paste Link option from Word’s Paste Special dialog box.
Note
When you paste an Excel chart to Word using the Microsoft Office Graphic Object option, only the chart’s data is linked. All other modifications (such as formatting or changing the chart type) aren’t reflected in the copy pasted in the Word document. When you activate the chart in Word, you can use the Chart Tools contextual menu to make changes to the chart. n
To edit (or break) a link, activate Word and choose File Info Edit Links To Files, which displays the Links dialog box shown in Figure 29.5. Select the file from the Source File list and then click the Break Link button. After breaking a link, the data remains in the destination document, but it’s no longer linked to the source document.
If the link has been broken (for example, the source file was moved or renamed), use the Change Source button to specify the source file and re-establish the link.
If the link isn’t showing updated information from the source file, you can force an update by using the Update Now button.
621