Microsoft&#174 · Excel&#174 · 2010 Inside Out by Dodge, Mark -- Read -- Imperial Library of Trantor

Index

Microsoft® Excel® 2010 Inside Out

A Note Regarding Supplemental Files Acknowledgments Questions and Support

Using the Companion Content Support Information We Want to Hear from You

Conventions and Features Used in This Book

Text Conventions Design Conventions

1. Examining the Excel Environment

1. What’s New in Microsoft Excel 2010

New and Improved for 2010

Backstage View Ribbon Customization Sparklines Paste Preview Improved Picture Editing Office Web Apps Slicers Improved Conditional Formatting New Functions and Functional Consistency Improved Math Equation Support Improved Charting Capacity Additional SmartArt Graphics 64-Bit Edition Office Mobile 2010

If You Missed the Last Upgrade Retired in 2007 If You Missed the Last Two Upgrades Onward

2. Exploring Excel Fundamentals

What Happens After You Install Excel?

Activating Excel Getting Updates

Examining the Excel 2010 Workspace

Facts About Worksheets Using the Workbook Window

The Title Bar Getting Around in the Workbook Workbook Navigation Tips Resizing the Window

Exploring the Ribbon

The Ribbon Speaks Drop-Down Lists and Dialog Box Launchers Galleries and Live Preview Understanding Contextual Tool Sets

Using the Quick Access Toolbar Accessing Commands with the Keyboard The Joy of Shortcut Menus Meet the Formula Bar Facts About the Status Bar Introducing Backstage View

Exploring File Management Fundamentals

Creating Workbooks

Installing Your Own Templates Create Your Own Template Tabs

Saving Files

Rules for File Naming File Formats Ensuring File Compatibility with Previous Versions of Excel Understanding the “XL” Formats Specifying the Default File Format Creating Automatic Backup Files Protecting Files Adding Summary Information to Files Saving the Entire Workspace

Opening Files

Special Ways to Open Files Opening Files When You Start Excel Recovering Corrupted Files

Importing and Exporting Files

Using the Open and Save As Commands to Import and Export Files Sharing Data with Excel for the Macintosh Sharing Data Beyond Excel

Introducing Office Web Apps Using Web File Formats Importing and Exporting Text Files Other File Formats

Using the Help System

Help on the Surface Help in Depth

Recovering from Crashes

Understanding AutoRecover Managing Versions

3. Custom-Tailoring the Excel Workspace

Customizing the Ribbon

Identifying Items in the Customize Ribbon and Quick Access Toolbar Categories Creating a Custom Ribbon Tab Creating a Custom Command Group Adding Existing Command Groups to Custom Tabs Resetting and Recycling Your Customizations

Customizing the Quick Access Toolbar

Positioning the Toolbar Adding Tools to the Toolbar

Adding Tools as You Work Removing Tools Adding and Organizing Tools Too Many Tools?

Creating Your Own Buttons Saving and Resetting Your Custom Quick Access Toolbar Restoring the Toolbar Exploring Other Toolbar and Ribbon Options

Controlling Other Elements of the Excel 2010 Interface

Displaying Underlying Formulas Hiding Zeros

Enhancing Accessibility

4. Security and Privacy

The Trust Center

Trusted Publishers, Locations, and Documents Add-Ins, ActiveX Settings, and Macro Settings Message Bar External Content File Block Settings Privacy Options

File Security

Removing Personal Information from Your Workbooks Using Digital Signatures

Other Security Features

2. Building Worksheets

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?

6. How to Work a Worksheet

Moving Around Regions

Navigating Regions with the Keyboard Navigating Regions with the Mouse Navigating with Special Keys

Understanding Selection

Selecting with the Mouse

Zooming to Select Large Worksheet Areas Selecting Columns, Rows, and Nonadjacent Ranges Selecting Regions

Using the Find & Select Commands Selecting with Go To Special

Selecting Precedents and Dependents Selecting Row or Column Differences

Techniques for Entering Data

Making Entries in Cells and in the Formula Bar

Entering Simple Numeric and Text Values Using Special Characters Understanding the Difference Between Displayed Values and Underlying Values Creating Long Text Values Using Text Wrapping Understanding Numeric Text Entries Entering Symbols

Making Entries in Ranges Editing and Undoing Entries

Managing Worksheets

Inserting and Deleting Worksheets Naming and Renaming Worksheets Moving and Copying Worksheets

Viewing Worksheets

Splitting Worksheets into Panes Freezing Panes Zooming Worksheets Using Custom Views

Protecting Worksheets

Unlocking Individual Cells Protecting the Workbook Allowing Password Access to Specific Cell Ranges Hiding Cells and Worksheets Using Passwords

7. How to Work a Workbook

Managing Multiple Workbooks

Navigating Between Open Workbooks Arranging Workbook Windows Getting the Most Out of Your Screen Comparing Worksheets Side by Side

Opening Multiple Windows for the Same Workbook

Useful Inconsistencies of New Windows

Hiding and Protecting Workbooks

Hiding Workbooks Protecting Workbooks Encrypting Workbooks Saving Workbooks or Windows as Hidden Hiding Worksheets Marking as Final

3. Formatting and Editing Worksheets

8. Worksheet Editing Techniques

Copying, Cutting, and Pasting

Copying and Pasting

Collecting Multiple Items on the Clipboard Pasting Multiples Using the Paste Options Button

Cutting and Pasting

Pasting Selectively Using Paste Special Pasting Using Math Operators Pasting Links Skipping Blank Cells Transposing Entries

Pasting Hyperlinks Moving and Copying with the Mouse

Inserting and Deleting

Inserting Columns and Rows Inserting Cells

Inserting Copied or Cut Cells

Deleting Cells, Columns, and Rows Clearing Cells Inserting, Deleting, and Clearing Cells with the Mouse Dragging with the Right Mouse Button

Undoing Previous Actions

Redoing What You’ve Undone Repeating Your Last Action

Editing Cell Contents

Editing in Cells or in the Formula Bar Editing Options Filling and Creating Data Series

Extending with Auto Fill Dragging the Fill Handle with the Right Mouse Button Using the Series Command Using the Fill Menu Commands Distributing Long Entries Using the Justify Command Creating Custom Lists Importing Custom Lists

Extending Existing Formatting

Finding and Replacing Stuff

Finding Formatting Specifying Variables Using Wildcard Characters Replacing What You Find

Getting the Words Right

Fixing Errors as You Type

Typing Internet and Network Addresses Using Custom AutoCorrect Actions

Letting Excel Help with Typing Chores Cheking Yer Speling Research Resources

Editing Multiple Worksheets

Grouping Worksheets for Editing

What You Can Do in Group-Editing Mode

Filling a Group

Auditing and Documenting Worksheets

Checking for Errors

Checking for Errors Evaluating and Auditing Formulas Watching Formulas

Tracing Cell References

Tracing Dependent Cells Clearing Tracer Arrows Tracing Precedent Cells Tracing Errors Tracing References to Other Worksheets

Adding Comments to Cells

Tweaking Your Comments Printing Comments

Outlining Worksheets

Outlining a Worksheet with Nonstandard Layout Extending the Outline to New Worksheet Areas Hiding an Outline Collapsing and Expanding Outline Levels Displaying a Specific Outline Level Ungrouping and Grouping Columns and Rows

Consolidating Worksheets

Consolidating by Position Consolidating by Category

Creating Links to the Source Worksheets

9. Worksheet Formatting Techniques

Formatting Fundamentals

Formatting Tables

Options for Applying Table Formats Creating Custom Table Formats Removing the Automatic Table Features

Painting Formats

Using Themes and Cell Styles

Formatting with Themes

Creating Custom Themes

Formatting with Cell Styles

Creating Custom Cell Styles

Formatting Conditionally

Creating Conditional Formatting Rules Managing Conditional Formatting Rules

Copying, Clearing, and Finding Conditional Formats

Creating Conditional Formatting Formulas

Formatting in Depth

Formatting Individual Characters Formatting as You Type Understanding the General Format Formatting Numbers

Using Currency Formats Using Accounting Formats Formatting Percentages Formatting Fractions Formatting Scientific (Exponential) Values Understanding the Text Format Using the Special Formats Creating Custom Number Formats

Aligning Data in Cells

Aligning Text Horizontally

Aligning Text Vertically

Controlling Text Orientation Shrinking Text to Fit in Cells

Using Fonts Customizing Borders Applying Colors and Patterns

Adding Graphic Backgrounds to Worksheets

Controlling the Size of Cells

Changing Column Widths Changing Row Heights Merging and Unmerging Cells

Using Template Files to Store Formatting

4. Adding Graphics and Printing

10. Creating and Formatting Graphics

Using the Shapes Tools

Drawing Constrained Objects Drawing Freehand Lines and Polygons

Adjusting Freehand Shapes with the Edit Points Command Working with Curves

Working with Text Boxes

Adding Text to Other Shapes

Working with Shapes

Using Connectors and Callouts

Creating WordArt Creating SmartArt Inserting Other Graphics

Inserting Clip Art and Media Files Inserting Pictures Inserting Other Objects

Formatting Graphics

Using Picture Tools

Making Adjustments to Your Images

Using Drawing Tools Using SmartArt Tools Formatting Text in Graphics Applying Compression to Pictures Using Advanced Object Formatting Effects

Formatting Fills and Lines Applying Shadows Applying 3-D Effects Formatting Objects That Contain Text

Formatting Embedded Objects

Working with Graphic Objects

Selecting and Grouping Objects Positioning Objects

Tools to Help You Position Objects on the Worksheet

Protecting Objects

More Tricks with Graphic Objects

Assigning Macros to Objects Creating Screenshots or Pictures of Your Worksheets

Taking Screenshots Copying Cells as a Picture Pasting Cells as a Picture Creating Linked Images of Cells Using the Camera Button

11. Printing and Presenting

Controlling the Appearance of Your Pages

Setting Page Options

Printing Wide or Tall Specifying Paper Size and Print Quality Setting a Reduction (Scaling) Ratio Setting the First Page Number

Working in Page Layout View Setting Margins Creating a Header and Footer

Using the Header/Footer Tab Adding Pictures to Headers and Footers

Setting Worksheet Options

Specifying the Area to Be Printed Specifying Rows and Columns to Print on Every Page Printing Gridlines and Headings Printing Comments and Errors Printing Drafts Translating Screen Colors to Black and White Setting the Printing Order of Large Print Ranges

Controlling What and Where to Print Adjusting Page Breaks

Using Page Break Preview Inserting and Removing Manual Page Breaks

Using Print Preview Creating Portable Documents

5. Creating Formulas and Performing Data Analysis

12. Building Formulas

Formula Fundamentals

Understanding the Precedence of Operators Using Cell References in Formulas

Entering Cell References by Clicking Understanding Relative, Absolute, and Mixed References Creating References to Other Worksheets in the Same Workbook Creating References to Worksheets in Other Workbooks How Copying Affects Cell References

Editing Formulas

Understanding Reference Syntax Using Numeric Text in Formulas About Text Values

Understanding Error Values

Using Functions: A Preview

Using the Sum Button Inserting a Function Using Formula AutoComplete

Working with Formulas

Naming Cells and Cell Ranges

Using Names in Formulas Defining and Managing Names Editing Names Workbook-Wide vs. Worksheet-Only Names Creating Names Semiautomatically Naming Constants and Formulas Using Relative References in Named Formulas Creating Three-Dimensional Names Inserting Names in Formulas Creating a List of Names Replacing References with Names Using Go To with Names

Getting Explicit About Intersections Creating Three-Dimensional Formulas Formula-Bar Formatting Using Structured References

Understanding Structured Reference Syntax Using Operators with Column Specifiers About the Special Item Specifiers Using Formula AutoComplete with Structured References Filling and Copying Structured References

Worksheet Calculation

Recalculating Manually Calculating Part of a Formula Working with Circular References Understanding the Precision of Numeric Values

Using Arrays

One-Dimensional Arrays Array Formula Rules Two-Dimensional Arrays Single-Cell Array Formulas Using Array Constants Understanding Array Expansion

Linking Workbooks

Saving Linked Workbooks Opening a Dependent Workbook Editing Links Copying, Cutting, and Pasting in Linked Workbooks

Copying and Pasting Between Workbooks Cutting and Pasting Between Workbooks

Creating Conditional Tests

Using Conditional Functions Using Lookup Functions

13. Using Functions

Using the Built-In Function Reference in Excel Exploring the Syntax of Functions

Expressions as Arguments Types of Arguments

Numeric Values Text Values Logical Values Named References Arrays Mixed Argument Types

Inserting Functions

Inserting References and Names

14. Everyday Functions

Understanding Mathematical Functions

Using the SUM Function

The Sum Button The SUMIF, SUMIFS, and COUNTIF Functions

Using Selected Mathematical Functions

The PRODUCT and SUMPRODUCT Functions The MOD Function The COMBIN Function The RAND and RANDBETWEEN Functions

Using the Rounding Functions

The ROUND, ROUNDDOWN, and ROUNDUP Functions The EVEN and ODD Functions The FLOOR and CEILING Functions The INT Function The TRUNC Function

Understanding Text Functions

Using Selected Text Functions

The TEXT Function The DOLLAR Function The LEN Function The ASCII Functions: CHAR and CODE The Cleanup Functions: TRIM and CLEAN The EXACT Function The Case Functions: UPPER, LOWER, and PROPER

Using the Substring Text Functions

The FIND and SEARCH Functions The RIGHT and LEFT Functions The MID Function The REPLACE and SUBSTITUTE Functions The CONCATENATE Function

Understanding Logical Functions

Using Selected Logical Functions

The IF Function The AND, OR, and NOT Functions Nested IF Functions Other Uses for Conditional Functions

Understanding Information Functions

Using Selected Information Functions

The TYPE and ERROR.TYPE Functions The COUNTBLANK Function

Using the IS Information Functions

Understanding Lookup and Reference Functions

Using Selected Lookup and Reference Functions

The VLOOKUP and HLOOKUP Functions The LOOKUP Function The ADDRESS Function The CHOOSE Function The MATCH Function The INDEX Function The INDIRECT Function The ROW and COLUMN Functions The ROWS and COLUMNS Functions The AREAS Function The TRANSPOSE Function

15. Formatting and Calculating Date and Time

Understanding How Excel Records Dates and Times Entering Dates and Times

Entering a Series of Dates

Extending an Existing Date Series

Formatting Dates and Times

Creating Your Own Date and Time Formats

Measuring Elapsed Time

Calculating with Date and Time Working with Date and Time Functions

Using the TODAY and NOW Functions

Using the TODAY and NOW Functions Using the WEEKDAY Function Using the YEAR, MONTH, and DAY Functions Using the HOUR, MINUTE, and SECOND Functions Using the DATEVALUE and TIMEVALUE Functions

Working with Specialized Date Functions

Using the EDATE and EOMONTH Functions Using the YEARFRAC Function Using the WORKDAY, NETWORKDAYS, WORKDAY.INTL and NETWORKDAYS.INTL Functions

16. Functions for Financial Analysis

Calculating Investments

The PV Function The NPV Function The FV Function The PMT Function The IPMT Function The PPMT Function The NPER Function The RATE Function The IRR Function The MIRR Function

Calculating Depreciation

The SLN Function The DDB and DB Functions The VDB Function The SYD Function

Analyzing Securities

The DOLLARDE and DOLLARFR Functions The ACCRINT and ACCRINTM Functions The INTRATE and RECEIVED Functions The PRICE, PRICEDISC, and PRICEMAT Functions The DISC Function The YIELD, YIELDDISC, and YIELDMAT Functions The TBILLEQ, TBILLPRICE, and TBILLYIELD Functions The COUPDAYBS, COUPDAYS, COUPDAYSNC, COUPNCD, COUPNUM, and COUPPCD Functions The DURATION and MDURATION Functions

Using the Euro Currency Tools Add-In

17. Functions for Analyzing Statistics

Analyzing Distributions of Data

Using Built-In Statistical Functions

The AVERAGE Functions The MEDIAN, MODE.SNGL, MODE.MULT, MAX, MIN, and COUNT Functions

Using Functions That Analyze Rank and Percentile

The PERCENTRANK Functions The PERCENTILE and QUARTILE Functions The SMALL and LARGE Functions The RANK Functions

Using Sample and Population Statistical Functions

Calculating Sample Statistics: VAR.S and STDEV.S Calculating Total Population Statistics: VAR.P and STDEV.P

Understanding Linear and Exponential Regression

Calculating Linear Regression

The LINEST Function The TREND Function The FORECAST Function The SLOPE Function The STEYX Function

Calculating Exponential Regression

The LOGEST Function The GROWTH Function

Using the Analysis Toolpak Data Analysis Tools

Installing the Analysis Toolpak Using the Descriptive Statistics Tool Creating Histograms

Analyzing Distribution with the FREQUENCY Function

Using the Rank And Percentile Tool

Generating Random Numbers Distributing Random Numbers Uniformly Distributing Random Numbers Normally Generating Random Numbers Using Bernoulli Distribution Generating Random Numbers Using Binomial Distribution Generating Random Numbers Using Poisson Distribution Generating Random Numbers Using Discrete Distribution Generating Semi-Random Numbers Using Patterned Distribution

Sampling a Population of Numbers Calculating Moving Averages

18. Performing What-If Analysis

Using Data Tables

Data Tables Based on One Input Variable Single-Variable Tables with More Than One Formula Data Tables Based on Two Input Variables Editing Tables

Using the Scenario Manager

Defining Scenarios Browsing Your Scenarios Adding, Editing, and Deleting Scenarios

Tracking Changes

Routing and Merging Scenarios Creating Scenario Reports

The Scenario Summary Report The Scenario PivotTable Report

Using the Goal Seek Command

Precision and Multiple Solutions

Using the Solver

Stating the Objective Specifying Variable Cells Specifying Constraints

Specifying Integer Constraints

Other Solver Options

Linear Models Viewing Iteration Results

Saving and Reusing the Solver Parameters Assigning the Solver Results to Named Scenarios Generating Reports

The Sensitivity Report The Answer Report The Limits Report

6. Creating Charts

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 Adding Glow and Soft Edges to Chart Markers Saving Templates to Make Chart Formats Reusable

20. Using Sparklines

Creating Sparklines

Creating Groups of Sparklines Expanding a Set of Sparklines

Customizing Sparklines

Changing the Sparkline Color and Weight Emphasizing Particular Points Customizing Axes

Setting Minimum and Maximum Values for the Vertical Axis Plotting a Group of Sparklines Against a Common Vertical Axis Using a Time-Scaled Horizontal Axis

Adding Text to Sparklines

Removing Sparklines

21. Advanced Charting Techniques

Selecting Chart Elements Repositioning Chart Elements with the Mouse Formatting Lines and Borders Formatting Areas

Using Transparency to Create a Minimal Chart Display on the Worksheet Filling an Area with a Color Gradient Filling an Area with a Texture or Picture

Formatting Text Working with Axes

Specifying the Line Style, Color, and Weight Specifying the Position of Tick Marks and Axis Labels Changing the Numeric Format Used by Axis Labels Changing the Scale of a Value Axis

Changing the Positions of Tick Marks and Gridlines Changing the Point Where Axes Intersect Reversing the Value-Axis Scale Using Logarithmic Scaling Applying a Scaling Factor

Changing the Scale of a Text Category Axis

Adjusting the Spacing Between Gridlines Curing Label Overlap Adjusting the Position of Category Labels Changing the Intersection of the Value Axis Switching a Category Axis from Text to Date

Changing the Scale of a Date Category Axis

Changing the Minimum and Maximum Changing the Major and Minor Units Changing the Base Unit

Formatting a Depth (Series) Axis

Working with Data Labels

Labeling Individual Data Points Labeling with Ad Hoc Text

Formatting Data Series and Markers

Assigning a Series to a Secondary Axis Using Two or More Chart Types in the Same Chart

Modifying the Data Source for Your Chart

Using the Mouse to Add Data Points Modifying Data with the Select Data Source Command

Plotting Noncontiguous Source Ranges Changing the Way Excel Plots Empty and Hidden Cells

Using Multilevel Categories Adding Moving Averages and Other Trendlines Adding Error Bars Adding High-Low Lines and Up and Down Bars

7. Managing Databases and Tables

22. Managing Information in Tables

How to Organize a Table Creating a Table

Overwriting Default Headers Turning a Table Back into an Ordinary Range Naming a Table Expanding a Table

Adding Totals to a Table Sorting Tables and Other Ranges

Sorting on a Single Column Sorting on More Than One Column Sorting Only Part of a List Sorting by Column Sorting Cells That Contain Formulas Sorting Months, Weekdays, or Custom Lists Performing a Case-Sensitive Sort

Filtering a List or Table

Using Filters

Determining How Many Rows Pass the Filter Removing a Filter Using Filter Criteria in More Than One Column Using a Filter to Find the Top or Bottom n Items Using a Filter to Display Blank Entries Using Filters to Select Dates Using Filters to Specify More Complex Criteria Using Custom Filters to Specify Complex Relationships

Using the Advanced Filter Command

Specifying a Criteria Range An Example Using Two Columns Joined by OR

An Example Using Three ORs on a Column An Example Using Both OR and AND Applying Multiple Criteria to the Same Column Using Computed Criteria Extracting Filtered Rows Removing Duplicate Records

Using Formulas with Tables

Referencing the Total Row Explicitly Referencing the Current Row Referencing Parts of a Table

Formatting Tables

Using Themes to Change Style Appearance Customizing Table Styles

23. 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

Customizing the Display of Empty or Error Cells Merging and Centering Field Labels Hiding Outline Controls Hiding Row Labels and Column Labels

Displaying Totals and Subtotals

Customizing Subtotals

Sorting PivotTable Fields Filtering PivotTable Fields

Filtering with the Report Filter Axis Filtering with Slicers

Connecting a Slicer to Multiple PivotTables

Formatting Slicers Filtering in the Field List Window

Changing PivotTable Calculations

Using a Different Summary Function Applying Multiple Summary Functions to the Same Field Using Custom Calculations Using Calculated Fields and Items

Creating a Calculated Field Creating a Calculated Item Displaying a List of Calculated Fields and Items

Grouping and Ungrouping Data

Creating Ad Hoc Item Groupings Grouping Items in Date or Time Ranges

Displaying the Details Behind a Data Value Creating PivotCharts

24. Working with External Data

Using and Reusing Data Connections

Setting Refresh Options Requiring or Not Requiring a Password to Refresh Refreshing on Demand

Opening an Entire Access Table in Excel Working with Data in Text Files

Using the Text Import Wizard Parsing Clipboard Text

Working with XML Files

Creating an Ad Hoc Mapping of XML Elements to Table Columns Importing XML Data Using an Existing XML Structure

Using Microsoft Query to Import Data

Choosing Tables and Fields (Columns) Filtering Records Sorting Records

Saving the Query or Moving to Microsoft Query

Working Directly with Microsoft Query

Getting to Query Adding and Removing Tables Working with Joins Adding, Removing, and Moving Fields Renaming Fields Sorting the Result Set Filtering the Result Set Performing Aggregate Calculations Creating a Parameter-Based Query Saving a Query Returning the Result Set to Excel

Using a Web Query to Return Internet Data

Using an Existing Web Query Creating Your Own Web Query

Using the From Web Command Copying and Pasting from the Web Browser Exporting from Internet Explorer to Excel

8. Collaborating

25. Collaborating on a Network or by E-Mail

Saving and Retrieving Files on Remote Computers Sharing Workbooks on a Network

Using Advanced Sharing Options Tracking Changes Protecting the Change History Reviewing Changes Canceling the Shared Workbook Session

Combining Changes Made to Multiple Workbooks Distributing Workbooks and Worksheets by E-Mail

Sending an Entire Workbook as an E-Mail Attachment Sending a Worksheet, Chart, or Range by E-Mail Sending a Workbook for Review

Controlling Document Access with Information Rights Management

Installing IRM Protecting a Document with IRM

Setting an Expiration Date Allowing Users to Print Allowing Users to Click the Copy Command Allowing Programmatic Access Making the Current Settings the Default Adding New Users and Modifying Permissions Settings

Using a Protected Document

26. Collaborating Using the Internet

Using Windows Live SkyDrive

Saving Files to SkyDrive Managing SkyDrive Folders

Using Microsoft Office Web Apps

Issues with Web Apps

9. Automating Excel

27. Recording Macros

Configuring Macro Security Using the Macro Recorder

Recording with Relative References What to Do When the Macro Recorder Does Not Give You What You Expect

Introducing the Visual Basic Editor Learning the Basics of Visual Basic

Objects, Methods, and Properties The Object Browser Collections of Objects Manipulating an Object’s Properties Without Selecting the Object Naming Arguments to Methods

Adding Code to or Editing Recorded Macros Using Subroutines in Macros Using the Personal Macro Workbook Going On from Here

28. 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

29. Debugging Macros and Custom Functions

Using Design-Time Tools

Catching Syntax Errors Catching Misspelled Variable Names Stepping Through Code Setting Breakpoints with the Toggle Breakpoint Command Setting Conditional Breakpoints Using Debug.Assert Using the Watch Window to Monitor Variable Values and Object Properties

Setting Conditional Breakpoints with the Watch Window Using Quick Watch to Monitor a Variable or Add a Watch Item

Using the Immediate Window

Dealing with Run-Time Errors

10. Integrating Excel with Other App ication

30. Using Hyperlinks

Creating a Hyperlink in a Cell

Turning Ordinary Text into a Hyperlink Linking to a Web Site or Local File Linking to a Location in the Current Document Linking to a New File Linking to an E-Mail Message

Assigning a Hyperlink to a Shape, Image, or Chart Selecting a Cell or an Object Without Triggering the Hyperlink

Editing or Deleting a Hyperlink

Using Formulas to Create Hyperlinks

31. Linking and Embedding

Embedding vs. Linking Embedding vs. Static Pasting Embedding and Linking from the Clipboard Embedding and Linking with the Object Command Managing Links

Choosing Automatic or Manual Update Fixing Broken Links

Linking vs. Hyperlinking

32. Using Excel Data in Word Documents

Using Excel Tables in Word Documents

Pasting an Excel Table from the Clipboard Using Paste Special to Control the Format of Your Table

Using the Microsoft Excel Worksheet Object Format Using RTF and HTML Formats Using Unformatted Text and Unformatted Unicode Text Using Picture (Windows Metafile), Bitmap, and Picture (Enhanced Metafile) Paste-Linking an Excel Table into Word Linking with Hyperlinks Using the Object Command

Using Excel Charts in Word Documents Using Excel to Supply Mail-Merge Data to Word

11. Appendixes

A. Menu to Ribbon Command Reference

Excel 2003 File Menu Excel 2003 Edit Menu Excel 2003 View Menu Excel 2003 Insert Menu Excel 2003 Format Menu Excel 2003 Tools Menu Excel 2003 Data Menu Excel 2003 Chart Menu Excel 2003 Window Menu Excel 2003 Help Menu Excel 2003 Standard Toolbar Excel 2003 Formatting Toolbar

B. Keyboard Shortcuts

Keyboard Shortcuts by Key Keyboard Shortcuts by Task

C. Function Reference

Index About the Authors

← Prev
Back
Next →

← Prev
Back
Next →