Home Page Icon
Home Page
Table of Contents for
11. Appendixes
Close
11. Appendixes
by Mark Dodge and Craig Stinson
Microsoft® Excel® 2010 Inside Out
Microsoft® Excel® 2010 Inside Out
Dedication
A Note Regarding Supplemental Files
Acknowledgments
Questions and Support
Using the Companion Content
Your Companion eBook
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
Alphabetical List of Excel 2010 Functions
Alphabetical List of Excel 2010 Functions (continued)
Index
About the Authors
Copyright
Search in book...
Toggle Font Controls
Playlists
Add To
Create new playlist
Name your new playlist
Playlist description (optional)
Cancel
Create playlist
Sign In
Email address
Password
Forgot Password?
Create account
Login
or
Continue with Facebook
Continue with Google
Sign Up
Full Name
Email address
Confirm Email Address
Password
Login
Create account
or
Continue with Facebook
Continue with Google
Prev
Previous Chapter
Using Excel to Supply Mail-Merge Data to Word
Next
Next Chapter
A. Menu to Ribbon Command Reference
Part 11. Appendixes
Appendix A
Appendix B
Appendix C
Add Highlight
No Comment
..................Content has been hidden....................
You can't read the all page of ebook, please click
here
login for view all page.
Day Mode
Cloud Mode
Night Mode
Reset