Microsoft® Office Excel 2003 Inside Out

Microsoft® Office Excel 2003 Inside Out

Released September 2003
Publisher(s): Microsoft Press
ISBN: 9780735615113

Read it now on the O’Reilly learning platform with a 10-day free trial.

O’Reilly members get unlimited access to books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.

Start your free trial

Book description

Take your Microsoft Excel experience to the next level! This supremely organized reference packs hundreds of timesaving solutions, troubleshooting tips, and handy workarounds for Excel 2003 in concise, fast-answer format.

Table of contents

  1. Microsoft® Office Excel 2003
    1. SPECIAL OFFER: Upgrade this ebook with O’Reilly
    2. A Note Regarding Supplemental Files
    3. Acknowledgments
    4. We'd Like to Hear from You!
    5. About the CD
      1. What's On the CD
      2. Using the CD
      3. System Requirements
      4. Support Information
    6. Conventions and Features Used in this Book
      1. Text Conventions
      2. Design Conventions
    7. About the Authors
    8. 1. Examining the Excel Environment
      1. 1. What's New in Microsoft Office Excel 2003
        1. New and Improved for 2003
          1. Online Assistance
            1. Controlling Web Interaction
            2. Getting Software Updates
          2. Comparing Worksheets Side by Side
          3. Improved Statistical Functions
          4. New Task Panes
          5. Document Workspaces
          6. Information Rights Management
          7. Improved List Management
            1. Support for User-Defined XML Structures
        2. If You Missed the Last Upgrade …
        3. Onward …
      2. 2. Excel Fundamentals
        1. What Happens After You Install Excel?
          1. Registering Excel
          2. Subscribing to Excel
            1. Getting Updates
        2. Recovering from Crashes
          1. Using AutoRecover
          2. Heroic Measures
        3. Examining the Excel Workspace
          1. Facts About Worksheets
          2. Using the Workbook Window
            1. The Title Bar
            2. Getting Around in the Workbook
            3. Workbook Navigation Tips
            4. Resizing the Window
          3. Exploring Menus and Dialog Boxes
            1. Morphing Menus
            2. Accessing Menus and Commands with the Keyboard
            3. Dialog Boxes and Tabs
            4. The Joy of Shortcut Menus
          4. Understanding the Formula Bar
          5. About the Status Bar
          6. Introducing Toolbars
        4. File Management Fundamentals
          1. Creating and Opening Workbooks
            1. Installing Your Own Templates
            2. Use Your Own Folders for Templates
            3. Changing the Default Book and Sheet
          2. Saving Files
            1. Rules for File Naming
            2. File Formats
            3. Specifying the Default File Format
            4. Creating Automatic Backup Files
            5. Protecting Files
            6. Adding Summary Information to Files
            7. Saving the Entire Workspace
          3. Opening Files
            1. Special Ways to Open Files
            2. Opening Files When You Start Excel
            3. Recovering Corrupted Files
          4. Searching for Files
        5. Importing and Exporting Files
          1. Using the Open and Save As Commands to Import and Export Files
            1. What Is XML?
          2. Importing and Exporting Text Files
          3. Sharing Data with Excel for the Macintosh
            1. Adjusting Date Values
        6. Online Help Works—Really!
          1. The Best Way to Use Help
          2. Managing the Assistant
            1. You're Fired!
          3. Using the Assistant
            1. Selecting Office Assistant Options
            2. Getting Answers to Unasked Questions
          4. Browsing the Help System
            1. Identifying Toolbar Buttons
          5. Customer Feedback Options
            1. Online Content
            2. Shared Workspace
          6. What's on the Help Menu
      3. 3. Custom-Tailoring the Excel Workspace
        1. Customizing Toolbars and Menus
          1. Managing Buttons and Commands
          2. Customizing Toolbars on the Fly
            1. Removing and Adding Buttons and Commands
            2. Rearranging Buttons and Commands
            3. Controlling the Display of Button Faces
            4. Giving Face to the Faceless
            5. Rearranging Menus and Submenus
          3. Changing the Face of Buttons
            1. How to Use the Button Editor
            2. Copying Button Faces
            3. Does Size Matter?
        2. Managing Toolbars
          1. Positioning Bars on Your Screen
        3. Creating New Toolbars and Menus
          1. Creating New Toolbars
          2. Attaching Custom Toolbars to Workbooks
            1. Deleting Custom Toolbars
          3. Creating New Menus
            1. Resetting Menus
            2. Preserving Toolbar and Menu Changes
          4. Restoring Toolbars and Menus
            1. Restoring Personalized Toolbars and Menus
          5. Other Toolbar and Menu Options
        4. Controlling Other Elements of the Excel Interface
          1. Displaying Underlying Formulas
          2. Hiding Zeros
          3. Changing the Color Palette
            1. Copying Palettes from Other Workbooks
        5. Enhancing Accessibility
    9. 2. Building Worksheets
      1. 4. Worksheet Design Tips
        1. Which Data Should Be in Rows, and Which in Columns?
        2. Will You Need to Print the Worksheet?
        3. Who Is the Audience?
        4. Would Your Worksheet Survive Without You?
        5. Does the Worksheet Rely on Imported Data?
        6. Do You Need More Than One Worksheet?
        7. Have You Allowed Room to Insert New Rows and Columns?
      2. 5. How to Work a Worksheet
        1. Moving Around
          1. Navigating Regions
            1. Navigating Regions with the Keyboard
            2. Navigating Regions with the Mouse
            3. Navigating with Special Keys
        2. Selecting Stuff
          1. Selecting with the Mouse
            1. Selecting Beyond the Window Borders
            2. Selecting Columns, Rows, and Multiple Areas
            3. Selecting Regions
          2. Selecting with the Go To Command
        3. Entering Stuff
          1. Making Entries in Cells and in the Formula Bar
            1. Entering Simple Numeric and Text Values
            2. Using Special Characters
            3. Understanding the Difference Between Displayed Values and Underlying Values
            4. Creating Long Text Values
            5. Using Word Wrapping
            6. Understanding Numeric Text Entries
            7. Entering Symbols
          2. Making Entries in Ranges
          3. Editing and Undoing Entries
        4. Managing Worksheets
          1. Inserting and Deleting Sheets
          2. Naming and Renaming Sheets
            1. Coloring Sheet Tabs
          3. Moving and Copying Sheets
        5. Looking at Worksheets
          1. Splitting Sheets into Panes
          2. Freezing Panes
          3. Zooming Worksheets
            1. Using Custom Views
        6. Protecting Worksheets
          1. Unlocking Individual Cells
          2. Protecting Workbooks
            1. Allowing Password Access to Specific Cell Ranges
          3. Hiding Cells and Sheets
            1. Using Passwords
      3. 6. How to Work a Workbook
        1. Managing Multiple Workbooks
          1. Navigating Between Open Workbooks
          2. Arranging Workbook Windows
          3. Getting the Most Out of Your Screen
          4. Comparing Sheets Side-By-Side
        2. Opening Multiple Windows for the Same Workbook
          1. Useful Inconsistencies of New Windows
        3. Hiding and Protecting Workbooks
          1. Hiding Workbooks
          2. Protecting Workbooks
          3. Saving Workbooks or Windows as Hidden
          4. Hiding Worksheets
    10. 3. Formatting and Editing Worksheets
      1. 7. Worksheet Editing Techniques
        1. Copying, Cutting, and Pasting
          1. Copying and Pasting
            1. Collecting Multiple Items on the Clipboard
            2. Pasting Multiples
            3. Using the Paste Options Smart Tag
          2. Cutting and Pasting
            1. Pasting Selectively Using Paste Special
            2. Pasting Using Math Operators
            3. Pasting Links
            4. Skipping Blank Cells
            5. Transposing Entries
            6. Using Paste Special with Arrays
          3. Pasting Hyperlinks
          4. Moving and Copying with the Mouse
        2. Inserting and Deleting
          1. Inserting Columns and Rows
          2. Inserting Cells
            1. Inserting Copied or Cut Cells
          3. Deleting Cells, Columns, and Rows
          4. Inserting, Deleting, and Clearing Cells with the Mouse
            1. Dragging with the Right Mouse Button
        3. Undoing Previous Actions
          1. Redoing What You've Undone
          2. Repeating Your Last Action
        4. Editing Cell Contents
          1. Editing in the Formula Bar
          2. Editing Directly in Cells
          3. Editing Options
          4. Clearing Cell Contents and Formats
          5. Filling and Creating Data Series
            1. Extending with AutoFill
            2. Dragging the Fill Handle with the Right Mouse Button
            3. Creating Custom Lists
            4. Importing Custom Lists
            5. Using Fill Options on the Edit Menu
          6. Extending Existing Formatting
        5. Finding and Replacing Stuff
          1. Finding Formatting
          2. Specifying Variables Using Wildcard Characters
          3. Replacing What You Find
        6. Editing Multiple Worksheets
          1. Grouping Sheets for Editing
            1. Editing by Group
          2. Filling a Group
        7. Getting the Words Right
          1. Fixing Errors as You Type
            1. Formatting As You Type
            2. Using Custom AutoCorrect Smart Tags
          2. Letting Excel Help with Typing Chores
            1. Creating Your Own Typing Shorthand
          3. Cheking Yer Speling
      2. 8. Worksheet Formatting Techniques
        1. Formatting Fundamentals
          1. Painting Formats
          2. Turbo Formatting with AutoFormat
        2. Formatting in Cells
          1. Formatting Individual Characters
          2. Formatting as You Type
          3. Using the General Format
          4. Formatting Numbers
          5. Formatting Currency
          6. Using the Euro Currency Tools Add-in
          7. Using Accounting Formats
          8. Using Accounting Underlines
          9. Formatting Percentages
          10. Formatting Fractions
          11. Formatting Scientific (Exponential) Values
          12. Understanding the Text Format
          13. Using the Special Formats
        3. Creating Custom Formats
          1. Creating New Number Formats
          2. Creating Four-Part Formats
          3. Adding Color to Formats
          4. Using Custom Format Condition Operators
        4. Aligning Data in Cells
          1. Aligning Text Horizontally
            1. Indenting Cell Contents
            2. Distributing Cell Contents
            3. Centering Text Across Columns
            4. Filling Cells with Characters
            5. Wrapping Text in Cells
            6. Justifying Text in Cells
        5. Aligning Text Vertically
          1. Controlling Text Orientation
          2. Shrinking Text to Fit in Cells
          3. Selecting Alignment Using Toolbars
        6. Using Fonts
          1. Formatting Fonts with Toolbar Buttons
        7. Customizing Borders
          1. Applying Border Formats with Toolbar Buttons
            1. Drawing Borders
        8. Applying Colors and Patterns
          1. Adding Colors with Toolbars
          2. Adding Graphic Backgrounds to Worksheets
        9. Controlling the Size of Cells
          1. Changing Column Widths
            1. Using Column Commands
          2. Changing Row Heights
            1. Using Row Commands
          3. Merging and Unmerging Cells
      3. 9. Advanced Formatting and Editing Techniques
        1. Applying Conditional Formats
        2. Formatting with Styles
          1. Applying a Style
          2. Defining Styles
            1. Defining Styles by Example
          3. Defining Styles from Scratch
          4. Modifying a Style
          5. Overriding a Style with Direct Formatting Changes
          6. Merging Styles from Different Workbooks
          7. Deleting a Style
        3. Using Template Files to Store Formatting
          1. Adding Templates to the XLStart Folder
        4. Auditing and Documenting Worksheets
          1. Checking for Errors
            1. Evaluating and Auditing Formulas
            2. Watching Formulas
          2. Tracing Cell References
            1. Tracing Dependent Cells
            2. Clearing Tracer Arrows
            3. Tracing Precedent Cells
            4. Tracing Errors
            5. Tracing References to Other Worksheets
          3. Adding Comments to Cells
            1. Printing Comments
          4. Using Go To Special
            1. Selecting Precedents and Dependents
            2. Selecting Row or Column Differences
            3. Having Excel Read Cells to You
        5. Outlining Worksheets
          1. Outlining a Worksheet with Nonstandard Layout
            1. Extending the Outline to New Worksheet Areas
          2. Hiding or Clearing an Outline
          3. Collapsing and Expanding Outline Levels
          4. Displaying a Specific Outline Level
          5. Selecting Only Visible Cells
          6. Ungrouping and Grouping Columns and Rows
        6. Consolidating Worksheets
          1. Consolidating by Position
          2. Consolidating by Category
            1. Creating Links to the Source Worksheets
    11. 4. Adding Graphics and Printing
      1. 10. Creating Spiffy Graphics
        1. Using the Drawing Tools
          1. Drawing Constrained Objects
          2. Using Tear-Off Palettes
        2. Creating Graphic Objects
          1. Drawing Freehand Lines and Polygons
            1. Adjusting Freehand Shapes with the Edit Points Command
            2. Working with Curves
          2. Working with Text Boxes
          3. Working with AutoShapes
            1. Using Connectors and Callouts
            2. More AutoShapes
            3. Shape Shifting
          4. Creating WordArt
        3. Working with Graphic Objects
          1. Selecting and Grouping Objects
            1. Selecting Objects That Contain Text
          2. Formatting Objects
            1. Changing Colors in the Palette
            2. Adding Transparency
            3. Formatting Lines and Arrows
            4. Applying Shadows
            5. Applying 3-D Effects
            6. Formatting Objects That Contain Text
            7. Setting the Default Format for Objects
          3. Positioning Objects
            1. Tools to Help You Position Objects on the Worksheet
          4. Protecting Objects
        4. Inserting Other Objects
          1. Using Clip Art and Other Media
            1. Managing Your Clip Organizer Exhibitions
          2. Importing Graphics
          3. Inserting Pictures
            1. Inserting Organization Charts
            2. Inserting Diagrams
            3. Inserting Scanned Images
          4. Formatting Pictures
        5. More Tricks with Objects
          1. Assigning Macros to Objects
          2. Taking Pictures of Your Worksheets
            1. Using the Camera Button
            2. Using the Copy Picture and Paste Picture Commands
        6. Gallery of Spiffy Examples
      2. 11. Printing and Presenting
        1. Controlling the Appearance of Your Pages
          1. Setting Page Options
            1. Printing Wide or Tall
            2. Setting a Reduction Ratio
            3. Specifying Paper Size and Print Quality
            4. Setting the First Page Number
          2. Setting Margins
            1. Centering Your Work on the Page
          3. Creating a Header and Footer
            1. Creating Custom Headers and Footers
            2. Adding Pictures to Headers and Footers
            3. Changing Fonts in Headers and Footers
          4. Setting Sheet Options
            1. Specifying Rows and Columns to Print on Every Page
            2. Printing Gridlines
            3. Printing Comments
            4. Printing Drafts
            5. Translating Screen Colors to Black and White
            6. Printing Row and Column Headings
            7. Setting the Printing Order of Large Print Ranges
        2. Adjusting Page Breaks
          1. Inserting and Removing Manual Page Breaks
        3. Using Print Preview
        4. Specifying What and Where to Print
          1. Defining a Default Print Area
        5. Setting Printer Driver Options
    12. 5. Creating Formulas and Performing Data Analysis
      1. 12. Building Formulas
        1. Formula Fundamentals
          1. Understanding the Precedence of Operators
          2. Using Cell References in Formulas
            1. Entering Cell References with the Mouse
            2. Understanding Relative, Absolute, and Mixed References
            3. Creating References to Other Worksheets in the Same Workbook
            4. Creating References to Worksheets in Other Workbooks
            5. Understanding Row-Column Reference Style
            6. How Copying Affects Cell References
          3. Editing Formulas
            1. Understanding Reference Syntax
            2. Using Numeric Text in Formulas
            3. About Text Values
        2. Using Functions: A Preview
          1. Using the AutoSum Button
          2. Inserting Functions
          3. Creating Three-Dimensional Formulas
          4. Formula-Bar Formatting
          5. Creating Natural-Language Formulas
            1. Substituting Labels Automatically
        3. Naming Cells and Cell Ranges
          1. Using Names in Formulas
          2. Defining and Managing Names
          3. Editing Names
          4. Workbook-Wide vs. Worksheet-Only Names
          5. Creating Names Semiautomatically
          6. Naming Constants and Formulas
            1. Using Relative References in Named Formulas
          7. Creating Three-Dimensional Names
          8. Pasting Names into Formulas
          9. Creating a List of Names
          10. Replacing References with Names
          11. Using Go To with Named Ranges
        4. Understanding Error Values
        5. Worksheet Calculation
          1. Recalculating Manually
          2. Copying Formulas and Pasting Only Their Resulting Values
          3. Evaluating Part of a Formula
          4. Using Circular References
          5. Understanding the Precision of Numeric Values
        6. Using Arrays
          1. One-Dimensional Arrays
          2. Array Formula Rules
          3. Two-Dimensional Arrays
          4. Single-Cell Array Formulas
          5. Using Array Constants
          6. Understanding Array Expansion
        7. Linking Workbooks
          1. Saving Linked Workbooks
          2. Opening a Dependent Workbook
          3. Editing Links
          4. Copying, Cutting, and Pasting in Linked Workbooks
            1. Copying and Pasting between Workbooks
            2. Cutting and Pasting between Workbooks
            3. Cutting and Pasting Cells Referred to by Dependent Formulas
        8. Creating Conditional Tests
          1. Using the Conditional Sum and Lookup Wizards
          2. Creating Conditional Sum Formulas
          3. Creating Lookup Formulas
      2. 13. Using Functions
        1. Using Excel's Built-In Function Reference
        2. Installing the Analysis ToolPak
        3. Exploring the Syntax of Functions
          1. Expressions as Arguments
          2. Types of Arguments
            1. Numeric Values
            2. Text Values
            3. Logical Values
            4. Named References
            5. Arrays
            6. Mixed Argument Types
        4. Inserting Functions
          1. Inserting References and Names
      3. 14. Everyday Functions
        1. Understanding Mathematical Functions
          1. Using the SUM Function
            1. The AutoSum Button
          2. Using Selected Mathematical Functions
            1. The PRODUCT and SUMPRODUCT Functions
            2. The MOD Function
            3. The COMBIN Function
            4. The RAND and RANDBETWEEN Functions
          3. Using the Rounding Functions
            1. The ROUND, ROUNDDOWN, and ROUNDUP Functions
            2. The EVEN and ODD Functions
            3. The FLOOR and CEILING Functions
            4. The INT Function
            5. The TRUNC Function
        2. Understanding Text Functions
          1. Using Selected Text Functions
            1. The TEXT Function
            2. The DOLLAR Function
            3. The LEN Function
            4. The ASCII Functions: CHAR and CODE
            5. The Clean-Up Functions: TRIM and CLEAN
            6. The EXACT Function
            7. The Case Functions: UPPER, LOWER, and PROPER
          2. Using the Substring Text Functions
            1. The FIND and SEARCH Functions
            2. The RIGHT and LEFT Functions
            3. The MID Function
            4. The CONCATENATE Function
        3. Understanding Logical Functions
          1. Using Selected Logical Functions
            1. The IF Function
            2. The AND, OR, and NOT Functions
            3. Nested IF Functions
            4. Other Uses for Conditional Functions
        4. Understanding Information Functions
          1. Using Selected Information Functions
            1. The TYPE and ERROR.TYPE Functions
            2. The COUNTBLANK Function
          2. Using the IS Information Functions
        5. Understanding Lookup and Reference Functions
          1. Using Selected Lookup and Reference Functions
            1. The VLOOKUP and HLOOKUP Functions
            2. The LOOKUP Function
            3. The ADDRESS Function
            4. The CHOOSE Function
            5. The MATCH Function
            6. The INDEX Function
            7. The INDIRECT Function
            8. The ROW and COLUMN Functions
            9. The ROWS and COLUMNS Functions
            10. The AREAS Function
            11. The TRANSPOSE Function
      4. 15. Formatting and Calculating Date and Time
        1. Understanding How Excel Records Dates and Times
        2. Entering Dates and Times
          1. Entering a Series of Dates
            1. Extending an Existing Date Series
        3. Formatting Dates and Times
          1. Creating Your Own Date and Time Formats
            1. Measuring Elapsed Time
        4. Calculating with Date and Time
        5. Working with Date and Time Functions
          1. Using the TODAY and NOW Functions
            1. Using the WEEKDAY Function
            2. Using the YEAR, MONTH, and DAY Functions
            3. Using the HOUR, MINUTE, and SECOND Functions
            4. Using the DATEVALUE and TIMEVALUE Functions
          2. Working with Specialized Date Functions
            1. Using the EDATE and EOMONTH Functions
            2. Using the YEARFRAC Function
            3. Using the WORKDAY and NETWORKDAYS Functions
      5. 16. Functions for Financial Analysis
        1. Calculating Investments
          1. The PV Function
          2. The NPV Function
          3. The FV Function
          4. The PMT Function
          5. The IPMT Function
          6. The PPMT Function
          7. The NPER Function
          8. The RATE Function
          9. The IRR Function
          10. The MIRR Function
        2. Calculating Depreciation
          1. The SLN Function
          2. The DDB and DB Functions
          3. The VDB Function
          4. The SYD Function
        3. Analyzing Securities
          1. The DOLLARDE and DOLLARFR Functions
          2. The ACCRINT and ACCRINTM Functions
          3. The INTRATE and RECEIVED Functions
          4. The PRICE, PRICEDISC, and PRICEMAT Functions
          5. The DISC Function
          6. The YIELD, YIELDDISC, and YIELDMAT Functions
          7. The TBILLEQ, TBILLPRICE, and TBILLYIELD Functions
          8. The COUPDAYBS, COUPDAYS, COUPDAYSNC, COUPNCD, COUPNUM, and COUPPCD Functions
          9. The DURATION and MDURATION Functions
      6. 17. Functions for Analyzing Statistics
        1. Analyzing Distributions of Data
          1. Using Built-In Statistical Functions
            1. The AVERAGE Function
            2. The MEDIAN, MODE, MAX, MIN, and COUNT Functions
            3. The SUMIF and COUNTIF Functions
          2. Using Functions That Analyze Rank and Percentile
            1. The PERCENTRANK Function
            2. The PERCENTILE and QUARTILE Functions
            3. The SMALL and LARGE Functions
            4. The RANK Function
          3. Using Sample and Population Statistical Functions
            1. Calculating Sample Statistics: VAR and STDEV
            2. Calculating Total Population Statistics: VARP and STDEVP
        2. Understanding Linear and Exponential Regression
          1. Calculating Linear Regression
            1. The LINEST Function
            2. The TREND Function
            3. The FORECAST Function
            4. The SLOPE Function
            5. The STEYX Function
          2. Calculating Exponential Regression
            1. The LOGEST Function
            2. The GROWTH Function
        3. Using the Analysis Toolpak Data Analysis Tools
          1. Using the Descriptive Statistics Tool
          2. Creating Histograms
            1. Analyzing Distribution with the FREQUENCY Function
          3. Using the Rank and Percentile Tool
          4. Generating Random Numbers
            1. Random Number Distribution Methods
            2. Distributing Random Numbers Uniformly
            3. Distributing Random Numbers Normally
            4. Generating Random Numbers Using Bernoulli Distribution
            5. Generating Random Numbers Using Binomial Distribution
            6. Generating Random Numbers Using Poisson Distribution
            7. Generating Random Numbers Using Discrete Distribution
            8. Generating Semi-Random Numbers Using Patterned Distribution
          5. Sampling a Population of Numbers
          6. Calculating Moving Averages
      7. 18. Performing What-If Analysis
        1. Using Data Tables
          1. Data Tables Based on One Input Variable
          2. Single-Variable Tables with More Than One Formula
          3. Data Tables Based on Two Input Variables
          4. Editing Tables
        2. Using the Scenario Manager
          1. Defining Scenarios
          2. Browsing Your Scenarios
          3. Adding, Editing, and Deleting Scenarios
            1. Tracking Changes
          4. Routing and Merging Scenarios
          5. Creating Scenario Reports
            1. The Scenario Summary Report
            2. The Scenario PivotTable Report
        3. Using the Goal Seek Command
          1. Precision and Multiple Solutions
        4. Using the Solver
          1. Stating the Objective
          2. Specifying Variable Cells
          3. Specifying Constraints
            1. Specifying Integer Constraints
          4. Saving and Reusing the Solver Parameters
          5. Assigning the Solver Results to Named Scenarios
          6. Other Solver Options
            1. Linear Models
            2. Viewing Iteration Results
          7. Generating Reports
            1. The Sensitivity Report
            2. The Answer Report
            3. The Limits Report
    13. 6. Collaboration and the Internet
      1. 19. Collaborating with Excel
        1. Saving and Retrieving Files on Remote Computers
        2. Sharing Workbooks on a Network
          1. Using Advanced Sharing Options
          2. Tracking Changes
            1. Protecting the Change History
          3. Reviewing Changes
          4. Canceling the Shared Workbook Session
        3. Combining Changes Made to Multiple Workbooks
          1. Merging Workbooks
        4. Distributing Workbooks and Worksheets by E-Mail
          1. Sending an Entire Workbook as an E-Mail Attachment
          2. Sending the Current Sheet as the Body of an E-Mail Message
          3. Sending a Workbook for Review
          4. Routing Workbooks to a Workgroup
        5. Controlling Document Access with Information Rights Management
          1. Protecting a Document with IRM
            1. Setting an Expiration Date
            2. Allowing Users to Print
            3. Enabling the Copy Command
            4. Allowing Programmatic Access
            5. Making the Current Settings the Default
            6. Adding New Users and Modifying Permissions Settings
          2. Using a Protected Document
        6. Using a SharePoint Team Services Site
          1. Downloading and Uploading Documents
          2. Checking Documents In and Out
          3. Using the Shared Workspace Task Pane
            1. Contacting Team Members
            2. Getting Document Status Information
            3. Assigning and Monitoring Tasks
            4. Opening Related Documents
            5. Using Links
            6. Getting Information About the Current Document
            7. Tracking Changes with Document Versioning
          4. Creating a New Document Workspace
            1. Creating a Document Workspace from Within Excel
            2. Creating a Document Workspace via E-Mail
            3. Creating a Document Workspace from Within SharePoint
            4. Managing a Document Workspace
        7. Using Web Discussions
      2. 20. Transferring Files to and from Internet Sites
        1. Working with FTP Sites
          1. Adding a Site to Your My Places Bar
        2. Saving and Publishing Excel Files in HTML
          1. Considering the Options
            1. Do You Want Interactivity?
            2. To Save or To Publish?
            3. Single-File Web Page or Web Page?
          2. Saving an Entire Workbook Without Interactivity
            1. Changing the Title
          3. Publishing Without Interactivity
          4. Publishing with Interactivity
        3. Using the Interactive Web Components
    14. 7. Integrating Excel with Other Applications
      1. 21. Linking and Embedding
        1. Embedding vs. Linking
        2. Embedding vs. Static Pasting
        3. Embedding and Linking from the Clipboard
        4. Embedding and Linking with the Object Command
        5. Manipulating Embedded Objects
        6. Managing Links
          1. Choosing Automatic or Manual Update
          2. Updating on File Open
          3. Fixing Broken Links
        7. Linking vs. Hyperlinking
      2. 22. Using Hyperlinks
        1. Creating a Hyperlink in a Cell
          1. Turning Ordinary Text into a Hyperlink
          2. Linking to a Web Site or Local File
          3. Linking to a Location in the Current Document
          4. Linking to a New File
          5. Linking to an E-Mail Message
        2. Assigning a Hyperlink to a Graphic, Toolbar Button, or Menu Command
          1. Editing, Removing, and Deleting a Hyperlink
          2. Formatting a Hyperlink
        3. Using the HYPERLINK Function
      3. 23. Using Excel Data in Word and PowerPoint Documents
        1. Using Excel Tables in Word Documents
          1. Pasting an Excel Table from the Clipboard
          2. Using Paste Special to Control the Format of Your Table
            1. Using the Microsoft Excel Worksheet Object
            2. Using Formatted Text (RTF) and HTML Format
            3. Using Unformatted Text and Unformatted Unicode Text
            4. Using Picture, Bitmap, and Picture (Enhanced Metafile)
            5. Paste-Linking an Excel Table into Word
            6. Linking to Named Ranges
            7. Linking with Hyperlinks
          3. Using the Object Command
        2. Using Excel Charts in Word Documents
        3. Using Excel to Supply Mail-Merge Data to Word
        4. Using Excel Data in PowerPoint
          1. Paste-Linking Excel Data into PowerPoint
          2. Using Excel Charts in PowerPoint
    15. 8. Creating Charts
      1. 24. Basic Charting Techniques
        1. Creating a New Chart
          1. Step 1: Choosing a Chart Type
          2. Step 2: Specifying the Data to Plot
          3. Step 3: Choosing Chart Options
            1. Specifying Chart and Axis Titles
            2. Displaying or Hiding Axes
            3. Category vs. Time Scaling
            4. Manual Scaling and Other Axis Formatting Options
            5. Displaying Gridlines
            6. Displaying or Hiding a Legend
            7. Displaying Data Labels
            8. Adding a Data Table
          4. Step 4: Telling Excel Where to Put Your Chart
        2. Creating Combination (Overlay) Charts
        3. Changing a Chart's Size and Position
        4. Plotting Hidden Cells
        5. Handling Missing Values
        6. Changing the Default Chart Type
        7. Printing Charts
        8. Saving, Opening, and Protecting Charts
        9. Working with Embedded Chart Objects
      2. 25. Enhancing the Appearance of Your Charts
        1. Working with the Chart Menu and Chart Toolbar
        2. Selecting Chart Elements
        3. Copying Formats from One Chart to Another
        4. Adding a Customized Chart to the Chart Wizard Gallery
        5. Repositioning Chart Elements with the Mouse
        6. Moving and Resizing the Plot Area
        7. Working with Titles
          1. Creating a Two-Line Title
          2. Formatting a Title
          3. Formatting Individual Characters in a Title
        8. Adding Text Annotations
        9. Working with Data Labels
          1. Label Positioning and Alignment Options
          2. Numeric Formatting Options for Data Labels
          3. Font and Patterns Options for Data Labels
          4. Editing Data Labels
          5. Positioning and Formatting Data Labels Individually
          6. Generating Useful Data Labels on XY (Scatter) Charts
        10. Working with Axes
          1. Specifying the Line Style, Color, and Weight
          2. Specifying the Position of Tick Marks and Tick-Mark Labels
          3. Changing the Numeric Format Used by Tick-Mark Labels
          4. Scaling Axes Manually
            1. Scaling a Value Axis Manually
            2. Scaling a Time-Scaled Category Axis Manually
        11. Adding, Removing, and Formatting Gridlines
        12. Formatting Data Series and Markers
          1. Assigning a Series to a Secondary Value Axis
          2. Using Two or More Chart Types in the Same Chart
          3. Changing the Series Order
          4. Toggling the Column/Row Orientation
          5. Changing Colors, Patterns, Fills, and Borders for Markers
            1. Using the Invert If Negative Option
            2. Using the Vary Colors by Point (Slice) Option
          6. Adjusting Spacing in Two-Dimensional Column and Bar Charts
          7. Adjusting Data Point Spacing in Three-Dimensional Charts
          8. Adding Series Lines in Stacked Column and Bar Charts
          9. Changing Shapes in Three-Dimensional Column and Bar Charts
          10. Smoothing the Lines in Line and XY (Scatter) Charts
          11. Changing Line and Marker Styles in Line, XY (Scatter), and Radar Charts
          12. Adding High-Low Lines and Up and Down Bars to Line Charts
          13. Adding Drop Lines to Area and Line Charts
          14. Exploding Pie Slices and Doughnut Bites
          15. Using Formatting and Split Options in Pie-Column and Pie-Pie Charts
          16. Changing the Angle of the First Pie Slice or Doughnut Bite
        13. Working with Data Tables
        14. Formatting Background Areas
          1. Filling an Area with a Color Gradient
          2. Filling an Area with a Pattern
          3. Filling an Area with a Texture or Picture
        15. Changing Three-Dimensional Viewing Angles
          1. Adjusting the Elevation
          2. Changing the Rotation
          3. Changing the Height
          4. Changing the Perspective
          5. Changing the Axis Angle and Scale
      3. 26. Working with Chart Data
        1. Adding Data
          1. Using Copy and Paste
          2. Adding Series
          3. Using List Features to Create Expanding Charts
        2. Removing Data
        3. Changing or Replacing Data
        4. Plotting or Marking Every nth Point
        5. Changing the Plot Order
        6. Using Multilevel Categories
        7. Adding Trend Lines
        8. Adding Error Bars
        9. What-If Charting: Dragging Markers to Change Data
      4. 27. Advanced Charting Techniques
        1. Using Named Ranges to Create Dynamic Charts
          1. Plotting New Data Automatically
          2. Plotting Only the Most Recent Points
        2. Using Arrays to Create a Static Chart
        3. Using Bubble Charts
        4. Using Radar Charts
        5. Creating Gannt Charts
        6. Assorted Formatting Issues
          1. Tick-Mark Labels Without Axes
          2. Tick-Mark Labels on the Plot Area
          3. Formatting Selected Gridlines or Tick-Mark Labels
          4. Staggered Tick-Mark Labels
          5. Plotting Your Own Projection (Extrapolation) Line
    16. 9. Managing Databases and Lists
      1. 28. Managing Information in Lists
        1. Building and Maintaining a List
          1. Using Label-Based Formulas in Calculated Columns
            1. Turning on the Label-Based Formula Feature
            2. Making Label-Based Formulas Available in All Workbooks
          2. Using (or Disabling) Other List-Building Aids
            1. Automatic Format and Formula Extension
            2. AutoComplete for Cell Values
            3. Move on Enter
          3. Custom Lists
        2. Working with List Objects
          1. Publishing a List Object
            1. Dealing with Data Type and Formatting Issues
          2. Toggling the Total Row
          3. Resizing a List Object
          4. Inserting and Deleting Rows and Columns within a List Object
        3. Validating Data Entry
          1. Specifying Data Type and Acceptable Values
          2. Specifying an Input Message (Prompt)
          3. Specifying Error Alert Style and Message
        4. Using Excel's Form Command to Work with Lists
          1. Adding Rows
          2. Finding Records
        5. Sorting Lists and Other Ranges
          1. Sorting on a Single Column
          2. Sorting on More than One Column
          3. Sorting Only Part of a List
          4. Sorting by Columns
          5. Sorting Cells That Contain Formulas
          6. Sorting Months, Weekdays, or Custom Lists
          7. Performing a Case-Sensitive Sort
            1. Making Case-Sensitive Sorting the Default
        6. Filtering a List
          1. Using the AutoFilter Command
            1. Using AutoFilter Criteria in More than One Column
            2. Using AutoFilter to Find the Top or Bottom n Items
            3. Using AutoFilter to Display Blank or Nonblank Entries
            4. Using the Custom Option to Specify More Complex Criteria
            5. Finding an Alphabetical Range of Text Values
            6. Using Wildcards in Custom Criteria
            7. Performing Aggregate Calculations on AutoFiltered Data
            8. Removing AutoFilter Criteria
          2. Using the Advanced Filter Command
            1. Specifying a Criteria Range
            2. An Example Using Two Columns Joined by OR
            3. An Example Using Three ORs on a Column
            4. An Example Using Both OR and AND
            5. Applying Multiple Criteria to the Same Column
            6. Using Computed Criteria
            7. Extracting Filtered Rows
            8. Performing Aggregate Calculations on Filtered Data
        7. Using Subtotals to Analyze a List
          1. Subtotaling on More Than One Column
          2. Subtotaling with More Than One Aggregation Formula
          3. Using Automatic Page Breaks
          4. Removing or Replacing Subtotals
          5. Grouping by Date
          6. Using the SUBTOTAL Function
        8. Using Functions to Extract Details from a List
          1. The Database Statistical Functions
          2. COUNTIF and SUMIF
            1. Using the Conditional Sum Wizard
          3. COUNTBLANK
          4. VLOOKUP and HLOOKUP
            1. Using the Lookup Wizard
          5. MATCH and INDEX
            1. Using INDEX with a List Control to Create a Data Browser
      2. 29. Working with External Data
        1. Using File, Open to Import External Data Files
          1. Opening Text Files
          2. Using the Text Import Wizard
            1. Parsing Clipboard Text
          3. Opening Microsoft Access Tables in Excel
          4. Opening dBase Files
        2. Working with XML Files
          1. Opening or Importing an XML List
            1. Refreshing an XML List
            2. Manipulating Columns in an XML List
            3. Creating an Ad Hoc Mapping of XML Elements to List Columns
          2. Exporting an XML List
        3. Using a Query to Retrieve External Data
          1. Reusing an Existing Query
          2. Creating a New Database Query
            1. Creating a Simple Example
            2. Modifying a Query's Refresh Behavior and Other Properties
            3. Editing a Query
          3. Working Directly with Microsoft Query
          4. Getting to Query
            1. Adding and Removing Tables
            2. Working with Joins
            3. Adding, Removing, and Moving Fields
            4. Renaming Fields
            5. Sorting the Result Set
            6. Filtering the Result Set
            7. Performing Aggregate Calculations
            8. Creating a Parameter-Based Query
            9. Saving a Query
            10. Returning the Result Set to Excel
        4. Using a Web Query to Return Internet Data
          1. Using an Existing Web Query
          2. Creating Your Own Web Query
            1. Using the New Web Query Command
            2. Copying and Pasting from the Web Browser
            3. Using Internet Explorer's Export To Microsoft Excel Command
      3. 30. Analyzing Data with PivotTable Reports
        1. A Simple Example
        2. Creating a PivotTable
          1. Starting the PivotTable And PivotChart Wizard
          2. Step 1: Specifying the Type of Data Source
          3. Step 2: Indicating the Location of Your Source Data
          4. Step 3: Telling the Wizard Where to Put Your PivotTable
          5. Laying Out the PivotTable
        3. Pivoting a PivotTable
          1. Using the Page Axis
          2. Displaying Totals for a Field in the Page Area
          3. Moving Page Fields to Separate Workbook Pages
          4. Selecting Items to Display on the Row and Column Axes
        4. Creating a PivotChart
        5. Refreshing a PivotTable
          1. Refreshing on File Open
        6. Selecting Elements of a PivotTable
        7. Formatting a PivotTable
          1. Using AutoFormat with PivotTables
          2. Changing the Numeric Format for the Data Area
          3. Changing the Way a PivotTable Displays Empty Cells
          4. Changing the Way a PivotTable Displays Error Values
          5. Merging Labels
        8. Using Multiple Data Fields
        9. Renaming Fields and Items
        10. Sorting Items
          1. Using AutoSort
          2. Rearranging Items by Hand
        11. Showing the Top or Bottom Items in a Field
        12. Hiding and Showing Inner Field Items
        13. Displaying the Details Behind a Data Value
        14. Grouping and Ungrouping Data
          1. Creating Ad Hoc Item Groupings
          2. Grouping Numeric Items
          3. Grouping Items in Date or Time Ranges
          4. Removing Groups (Ungrouping)
        15. Using Grand Totals and Subtotals
          1. Grand Totals
          2. Subtotals
          3. Subtotals for Innermost Fields
        16. Changing a PivotTable's Calculations
          1. Using a Different Summary Function
          2. Applying Multiple Summary Functions to the Same Field
          3. Using Custom Calculations
          4. Using Calculated Fields and Items
            1. Creating a Calculated Field
            2. Creating a Calculated Item
            3. Displaying a List of Calculated Fields and Items
        17. Referencing PivotTable Data from Worksheet Cells
        18. Creating a PivotTable from External Data
          1. Refreshing PivotTable Data from an External Source
            1. Refreshing at Regular Time Intervals
        19. Using a PivotTable to Consolidate Ranges
        20. Building a PivotTable from an Existing PivotTable
        21. Printing PivotTables
          1. Using Row and Column Headings as Print Titles
          2. Repeating Item Labels on Each Printed Page
          3. Printing Each Outer Row Field Item on a New Page
        22. Using the PivotTable Web Component
    17. 10. Automating Excel
      1. 31. Recording Macros
        1. Using the Macro Recorder
          1. Running a Macro Without Using a Keyboard Shortcut
        2. Behind the Scenes: The VBA Environment
          1. Getting Help on VBA Keywords
          2. Objects, Methods, and Properties
            1. The Object Browser
            2. Collections of Objects
          3. Manipulating an Object's Properties Without Selecting the Object
          4. Naming Arguments to Methods
        3. Adding Code to an Existing Macro
        4. Using Absolute and Relative References
        5. Macro Subroutines
        6. Using the Personal Macro Workbook
        7. Going On from Here
      2. 32. Creating Custom Functions
        1. Using Custom Functions
          1. What's Happening
        2. Understanding Custom Function Rules
        3. Using VBA Keywords in Custom Functions
        4. Documenting Macros and Custom Functions
        5. Creating Custom Functions with Optional Arguments
        6. Making Your Custom Functions Available Anywhere
      3. 33. Debugging Macros and Custom Functions
        1. Using Design-Time Tools
          1. Catching Syntax Errors
          2. Catching Misspelled Variable Names
          3. Stepping Through Code
          4. Setting Breakpoints with the Toggle Breakpoint Command
          5. Setting Conditional Breakpoints Using Debug.Assert
          6. Using the Watch Window to Monitor Variable Values and Object Properties
            1. Setting Conditional Breakpoints with the Watch Window
            2. Using Quick Watch to Monitor a Variable or Add a Watch Item
          7. Using the Immediate Window
        2. Dealing with Run-Time Errors
    18. 11. Appendixes
      1. A. Installing Microsoft Excel
        1. System Requirements
          1. Additional Requirements and Recommendations
        2. Installing Office
        3. Uninstalling Office
        4. Installing Additional Components
          1. Installing International Features
          2. Using On-Screen Keyboard
        5. Repairing Your Office Installation
      2. B. Using Speech and Handwriting Recognition
        1. Using the Language Bar
          1. Controlling the Language Bar
        2. Using Speech Recognition
          1. Training Your Computer and Your Voice
          2. Issuing Verbal Commands
            1. Special Voice Commands
          3. Using Your Voice to Input Text
            1. Voice Commands for Dictation
            2. Speaking Punctuation
            3. Forcing Numeric Recognition
            4. Spelling Out Words
        3. Using Handwriting Recognition
          1. Setting Handwriting Options
      3. C. Keyboard Shortcuts
        1. Charts and Select Chart Elements
        2. Data Forms
        3. Dialog Box Edit Boxes
        4. Dialog Boxes
        5. Edit Data
        6. Enter and Calculate Formulas
        7. Enter Data
        8. Enter Special Characters
        9. Extend a Selection
        10. Filter Lists
        11. Format, Cells Dialog Box—Border Tab
        12. Format Data
        13. Help
        14. Help Window
        15. Insert, Delete, and Copy Cells
        16. Languages
        17. Macros
        18. Menus and Toolbars
        19. Move and Scroll—In End Mode
        20. Move and Scroll—Worksheets
        21. Move Within a Selected Range
        22. Open, Save As, and Insert Picture Dialog Boxes
        23. PivotTable and PivotChart Wizard Layout Dialog Box
        24. PivotTable—Display and Hide Items
        25. PivotTable—Change the Layout
        26. Print
        27. Print Preview
        28. Select Cells, Rows, Columns, and Objects
        29. Select Cells with Special Characteristics
        30. Send E-Mail Messages
        31. Show, Hide, and Outline Data
        32. Smart Tags
        33. Speech Recognition and Text-To-Speech
        34. Task Panes
        35. Windows and Office Interface
        36. Worksheets
      4. D. Function Reference
    19. Index
    20. SPECIAL OFFER: Upgrade this ebook with O’Reilly

Product information

  • Title: Microsoft® Office Excel 2003 Inside Out
  • Author(s):
  • Release date: September 2003
  • Publisher(s): Microsoft Press
  • ISBN: 9780735615113