Microsoft® Office Excel® 2007 Inside Out

Microsoft® Office Excel® 2007 Inside Out

Released January 2007
Publisher(s): Microsoft Press
ISBN: 9780735623217

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

Learn how to conquer Microsoft Office Excel 2007—from the inside out! This book packs hundreds of time-saving solutions, troubleshooting tips, and workarounds, all in concise, fast-answer format. Includes a companion CD.

Table of contents

  1. Microsoft® Office Excel® 2007 Inside Out
    1. SPECIAL OFFER: Upgrade this ebook with O’Reilly
    2. A Note Regarding Supplemental Files
    3. Acknowledgments
    4. About the CD
      1. What’s on the CD
      2. System Requirements
      3. Support Information
    5. Conventions and Features Used in This Book
      1. Text Conventions
      2. Design Conventions
    6. 1. Examining the Excel Environment
      1. 1. What’s New in Microsoft Office Excel 2007
        1. New and Improved for 2007
          1. Results-Oriented User Interface
          2. Graphic Enhancements
          3. Improved Formatting and Output Tools
            1. Page Layout View
          4. New Templates
          5. Updated Conditional-Formatting Features
          6. Increased Capacity and Speed
          7. Better and Easier Tables
          8. Improved PivotTables and PivotCharts
          9. Better Database Connectivity
          10. Formula AutoComplete
          11. Tools for Creating Formulas
          12. Built-In Analysis Toolpak Functions
          13. Enhanced Charting Features
          14. New XLSX File Format
          15. Improved Sorting and Filtering
          16. Collaboration Enhancements
          17. Enhanced Security Features
        2. Retired in 2007
        3. If You Missed the Last Upgrade . . .
        4. Onward . . .
      2. 2. Exploring Excel Fundamentals
        1. What Happens After You Install Excel?
          1. Activating Excel
          2. Getting Updates
        2. Examining the Excel 2007 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 the Ribbon
            1. The Ribbon Speaks
            2. Drop-Down Lists and Dialog Box Launchers
            3. Galleries and Live Preview
            4. Introducing Contextual Tool Sets
          4. Where Is the File Menu?
          5. Using the Quick Access Toolbar
          6. Accessing Commands with the Keyboard
          7. The Joy of Shortcut Menus
          8. Meet the Formula Bar
          9. Facts About the Status Bar
        3. Exploring File Management Fundamentals
          1. Creating Workbooks
            1. Installing Your Own Templates
            2. Create Your Own Template Tabs
          2. Saving Files
            1. Rules for File Naming
            2. File Formats
            3. Ensuring File Compatibility with Previous Versions of Excel
            4. Understanding the "XL" Formats
            5. Specifying the Default File Format
            6. Creating Automatic Backup Files
            7. Protecting Files
            8. Adding Summary Information to Files
            9. 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. Importing and Exporting Files
          1. Using the Open and Save As Commands to Import and Export Files
          2. Sharing Data with Excel for the Macintosh
          3. Sharing Data Beyond Excel
            1. Using Web File Formats
            2. Importing and Exporting Text Files
            3. Other File Formats
        5. Using the Online Help System
          1. Help on the Surface
          2. Help In Depth
        6. Recovering from Crashes
          1. Using AutoRecover
          2. Calling Dr. Office
      3. 3. Custom-Tailoring the Excel Workspace
        1. Customizing the Quick Access Toolbar
          1. Positioning the Toolbar
          2. Adding Tools to the Toolbar
            1. Adding Tools As You Work
            2. Removing Tools
            3. Adding and Organizing Tools
            4. Identifying Items in the Customize Dialog Box
            5. Too Many Tools?
          3. Creating Your Own Buttons
          4. Attaching Custom Toolbar Configurations to Workbooks
          5. Restoring the Toolbar
          6. Exploring Other Toolbar and Ribbon Options
        2. Controlling Other Elements of the Excel 2007 Interface
          1. Displaying Underlying Formulas
          2. Hiding Zeros
        3. Enhancing Accessibility
      4. 4. Security and Privacy
        1. The Trust Center
          1. Trusted Publishers and Locations
          2. Add-Ins, ActiveX Settings, and Macro Settings
          3. Message Bar
          4. External Content
          5. Privacy Options
        2. File Security
          1. Removing Personal Information from Your Workbooks
          2. Using Digital Signatures
        3. Other Security Features
    7. 2. Building Worksheets
      1. 5. Planning Your Worksheet Design
        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 for New Data?
      2. 6. How to Work a Worksheet
        1. Moving Around Regions
          1. Navigating Regions with the Keyboard
          2. Navigating Regions with the Mouse
          3. Navigating with Special Keys
        2. Understanding Selection
          1. Selecting with the Mouse
            1. Zooming to Select Large Worksheet Areas
            2. Selecting Columns, Rows, and Multiple Areas
            3. Selecting Regions
          2. Using the Find & Select Commands
          3. Selecting with Go To Special
            1. Selecting Precedents and Dependents
            2. Selecting Row or Column Differences
        3. Techniques for Entering Data
          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 Text 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 Worksheets
          2. Naming and Renaming Worksheets
          3. Moving and Copying Worksheets
        5. Viewing Worksheets
          1. Splitting Worksheets into Panes
          2. Freezing Panes
          3. Zooming Worksheets
          4. Using Custom Views
        6. Protecting Worksheets
          1. Unlocking Individual Cells
          2. Protecting the Workbook
          3. Allowing Password Access to Specific Cell Ranges
          4. Hiding Cells and Worksheets
          5. Using Passwords
      3. 7. 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 Worksheets 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. Encrypting Workbooks
          4. Saving Workbooks or Windows as Hidden
          5. Hiding Worksheets
    8. 3. Formatting and Editing Worksheets
      1. 8. 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
          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. Clearing Cells
          5. Inserting, Deleting, and Clearing Cells with the Mouse
          6. 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 Cells or in the Formula Bar
          2. Editing Options
          3. Filling and Creating Data Series
            1. Extending with AutoFill
            2. Dragging the Fill Handle with the Right Mouse Button
            3. Using the Series Command
            4. Using the Fill Menu Commands
              1. Distributing Long Entries Using the Justify Command
            5. Creating Custom Lists
            6. Importing Custom Lists
          4. Extending Existing Formatting
        5. Finding and Replacing Stuff
          1. Finding Formatting
          2. Specifying Variables Using Wildcard Characters
          3. Replacing What You Find
        6. Getting the Words Right
          1. Fixing Errors As You Type
            1. Typing Internet and Network Addresses
            2. Using Custom AutoCorrect Smart Tags
          2. Letting Excel Help with Typing Chores
          3. Cheking Yer Speling
          4. Research Resources
        7. Editing Multiple Worksheets
          1. Grouping Worksheets for Editing
            1. What You Can Do in Group-Editing Mode
          2. Filling a Group
        8. Auditing and Documenting Worksheets
          1. Checking for Errors
          2. Evaluating and Auditing Formulas
          3. Watching Formulas
          4. Tracing Cell References
            1. Tracing Dependent Cells
            2. Clearing Tracer Arrows
            3. Tracing Precedent Cells
            4. Tracing Errors
            5. Tracing References to Other Worksheets
          5. Adding Comments to Cells
            1. Tweaking Your Comments
            2. Printing Comments
        9. Outlining Worksheets
          1. Outlining a Worksheet with Nonstandard Layout
          2. Extending the Outline to New Worksheet Areas
          3. Hiding an Outline
          4. Collapsing and Expanding Outline Levels
          5. Displaying a Specific Outline Level
          6. Ungrouping and Grouping Columns and Rows
        10. Consolidating Worksheets
          1. Consolidating by Position
          2. Consolidating by Category
            1. Creating Links to the Source Worksheets
      2. 9. Worksheet Formatting Techniques
        1. Formatting Fundamentals
          1. Formatting Tables
            1. Options for Applying Table Formats
            2. Creating Custom Table Formats
            3. Removing the Automatic Table Features
          2. Painting Formats
        2. Using Themes and Cell Styles
          1. Formatting with Themes
            1. Creating Custom Themes
          2. Formatting with Cell Styles
            1. Creating Custom Cell Styles
              1. Creating Cell Styles by Example
              2. Modifying and Duplicating Cell Styles
              3. Merging Cell Styles from Different Workbooks
              4. Deleting a Cell Style
        3. Formatting Conditionally
          1. Creating Conditional Formatting Rules
          2. Managing Conditional Formatting Rules
            1. Copying, Clearing, and Finding Conditional Formats
          3. Creating Conditional Formatting Formulas
        4. Formatting in Depth
          1. Formatting Individual Characters
          2. Formatting As You Type
          3. Understanding the General Format
          4. Formatting Numbers
            1. Using Currency Formats
            2. Using Accounting Formats
            3. Formatting Percentages
            4. Formatting Fractions
            5. Formatting Scientific (Exponential) Values
            6. Understanding the Text Format
            7. Using the Special Formats
            8. Creating Custom Number Formats
              1. Creating New Number Formats
              2. Creating Four-Part Formats
              3. Adding Color to Formats
              4. Using Custom Format Conditional Operators
          5. 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
          6. Aligning Text Vertically
            1. Controlling Text Orientation
            2. Shrinking Text to Fit in Cells
          7. Using Fonts
          8. Customizing Borders
          9. Applying Colors and Patterns
            1. Adding Graphic Backgrounds to Worksheets
          10. Controlling the Size of Cells
            1. Changing Column Widths
            2. Changing Row Heights
            3. Merging and Unmerging Cells
        5. Using Template Files to Store Formatting
    9. 4. Adding Graphics and Printing
      1. 10. Creating Spiffy Graphics
        1. Using the Shapes Tools
          1. Drawing Constrained Objects
          2. Drawing Freehand Lines and Polygons
            1. Adjusting Freehand Shapes with the Edit Points Command
            2. Working with Curves
          3. Working with Text Boxes
            1. Adding Text to Other Shapes
          4. Working with Shapes
            1. Using Connectors and Callouts
        2. Creating WordArt
        3. Creating SmartArt
        4. Inserting Graphics
          1. Inserting Clip Art and Media Files
            1. Managing Clips
          2. Inserting Pictures
          3. Importing Pictures
          4. Inserting Other Objects
        5. Formatting Graphics
          1. Formatting Text in Graphics
          2. Applying Compression to Pictures
          3. Using the Format Shape Dialog Box
            1. Formatting Fills and Lines
              1. Gradient Fills
              2. Gradient Stops
              3. Picture or Texture Fill
              4. Line Colors and Styles
            2. Applying Shadows
            3. Applying 3-D Effects
            4. Formatting Pictures
            5. Formatting Objects That Contain Text
          4. Formatting Embedded Objects
        6. Working with Graphic Objects
          1. Selecting and Grouping Objects
          2. Positioning Objects
            1. Tools to Help You Position Objects on the Worksheet
          3. Protecting Objects
        7. More Tricks with Graphic Objects
          1. Assigning Macros to Objects
          2. Taking Pictures of Your Worksheets
            1. Using the Camera Button
            2. Using the Copy As Picture and Paste As Picture Commands
        8. 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. Specifying Paper Size
            3. Setting a Reduction Ratio
            4. Setting the First Page Number
          2. Working in Page Layout View
          3. Setting Margins
          4. 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
          5. Setting Worksheet Options
            1. Specifying the Area to Be Printed
            2. Specifying Rows and Columns to Print on Every Page
            3. Printing Gridlines and Headings
            4. Printing Comments and Errors
            5. Printing Drafts
            6. Translating Screen Colors to Black and White
            7. Setting the Printing Order of Large Print Ranges
        2. Specifying What and Where to Print
          1. Printing Immediately
          2. Printing to a File
        3. Adjusting Page Breaks
          1. Using Page Break Preview
          2. Inserting and Removing Manual Page Breaks
        4. Using Print Preview
        5. Setting Printer Driver Options
        6. Creating Portable Documents
    10. 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 by Clicking
            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
              1. Copying Relative References
              2. Copying Absolute References
              3. Copying Mixed References
          3. Editing Formulas
            1. Understanding Reference Syntax
            2. Using Numeric Text in Formulas
            3. About Text Values
          4. Understanding Error Values
        2. Using Functions: A Preview
          1. Using the Sum Button
          2. Inserting a Function
          3. Using Formula AutoComplete
        3. Working with Formulas
          1. 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. Using Names in Formulas
            9. Creating a List of Names
            10. Replacing References with Names
            11. Using Go To with Names
          2. Getting Explicit About Intersections
          3. Creating Three-Dimensional Formulas
          4. Formula-Bar Formatting
          5. Using Structured References
            1. Understanding Structured Reference Syntax
            2. Using Operators with Column Specifiers
            3. About the Special Item Specifiers
            4. Using Formula AutoComplete with Structured References
            5. Filling and Copying Structured References
        4. Worksheet Calculation
          1. Recalculating Manually
          2. Calculating Part of a Formula
          3. Working with Circular References
          4. Understanding the Precision of Numeric Values
        5. 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
        6. 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
        7. 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 the Built-In Function Reference in Excel
        2. 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
        3. Inserting Functions
          1. Inserting References and Names
      3. 14. Everyday Functions
        1. Understanding Mathematical Functions
          1. Using the SUM Function
            1. The Sum 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 Cleanup 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 REPLACE and SUBSTITUTE Functions
            5. 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
          2. Using the WEEKDAY Function
          3. Using the YEAR, MONTH, and DAY Functions
          4. Using the HOUR, MINUTE, and SECOND Functions
          5. Using the DATEVALUE and TIMEVALUE Functions
          6. 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
        4. Using the Euro Currency Tools Add-In
      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, SUMIFS, 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. Installing the Analysis Toolpak
          2. Using the Descriptive Statistics Tool
          3. Creating Histograms
            1. Analyzing Distribution with the FREQUENCY Function
          4. Using the Rank And Percentile Tool
          5. 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
          6. Sampling a Population of Numbers
          7. 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
    11. 6. Creating Charts
      1. 19. Basic Charting Techniques
        1. Selecting Data for Your Chart
        2. Choosing a Chart Type
        3. Changing the Chart Type
        4. Switching Rows and Columns
        5. Choosing a Chart Layout
        6. Choosing a Chart Style
        7. Moving the Chart to a Separate Chart Sheet
        8. Adding, Editing, and Removing a Chart Title
        9. Adding, Editing, and Removing a Legend
        10. Adding and Positioning Data Labels
        11. Adding a Data Table
        12. Manipulating Axes
        13. Adding Axis Titles
        14. Changing the Rotation of Chart Text
        15. Displaying Gridlines
        16. Adding Text Annotations
        17. Changing the Font or Size of Chart Text
        18. Applying Shape Styles and WordArt Styles
        19. Saving Templates to Make Chart Formats Reusable
      2. 20. Charting Beyond the Ribbon
        1. Selecting Chart Elements
        2. Repositioning Chart Elements with the Mouse
        3. Formatting Lines and Borders
        4. Formatting Areas
          1. Using Transparency to Create a Minimal Chart Display on the Worksheet
          2. Filling an Area with a Color Gradient
          3. Filling an Area with a Texture or Picture
        5. Formatting Text
        6. Working with Axes
          1. Specifying the Line Style, Color, and Weight
          2. Specifying the Position of Tick Marks and Axis Labels
          3. Changing the Numeric Format Used by Axis Labels
          4. Changing the Scale of a Value Axis
            1. Changing the Positions of Tick Marks and Gridlines
            2. Changing the Point Where Axes Intersect
            3. Reversing the Value-Axis Scale
            4. Using Logarithmic Scaling
            5. Applying a Scaling Factor
          5. Changing the Scale of a Text Category Axis
            1. Adjusting the Spacing Between Gridlines
            2. Curing Label Overlap
            3. Adjusting the Position of Category Labels
            4. Changing the Intersection of the Value Axis
            5. Switching a Category Axis from Text to Date
          6. Changing the Scale of a Date Category Axis
            1. Changing the Minimum and Maximum
            2. Changing the Major and Minor Units
            3. Changing the Base Unit
          7. Formatting a Depth (Series) Axis
        7. Working with Data Labels
          1. Labeling Individual Data Points
          2. Labeling with Ad Hoc Text
        8. Formatting Data Series and Markers
          1. Assigning a Series to a Secondary Axis
          2. Using Two or More Chart Types in the Same Chart
        9. Modifying the Data Source for Your Chart
          1. Using the Mouse to Add Data Points
          2. Modifying Data with the Select Data Source Command
            1. Using Discontiguous Source Ranges
            2. Changing the Way Excel Plots Empty and Hidden Cells
        10. Using Multilevel Categories
        11. Adding Moving Averages and Other Trendlines
        12. Adding Error Bars
        13. Adding High-Low Lines and Up and Down Bars
    12. 7. Managing Databases and Tables
      1. 21. Managing Information in Tables
        1. How to Organize a Table
        2. Creating a Table
          1. Overwriting Default Headers
          2. Turning a Table Back into an Ordinary Range
          3. Naming a Table
          4. Expanding a Table
        3. Adding Totals to a Table
        4. Sorting Tables 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 Column
          5. Sorting Cells That Contain Formulas
          6. Sorting Months, Weekdays, or Custom Lists
          7. Performing a Case-Sensitive Sort
        5. Filtering a List or Table
          1. Using Filters
            1. Determining How Many Rows Pass the Filter
            2. Removing a Filter
            3. Using Filter Criteria in More Than One Column
            4. Using a Filter to Find the Top or Bottom n Items
            5. Using a Filter to Display Blank Entries
            6. Using Filters to Select Dates
            7. Using Filters to Specify More Complex Criteria
            8. Using Custom Filters to Specify Complex Relationships
              1. Finding an Alphabetical Range of Text Values
              2. Using Wildcards in Custom 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
              1. Referencing Cells Within the List
              2. Referencing a Cell Outside the List
              3. Referencing All Rows in a Column
            7. Extracting Filtered Rows
              1. The Unique Records Only Check Box
          3. Removing Duplicate Records
        6. Using Formulas with Tables
          1. Referencing the Total Row
          2. Explicitly Referencing the Current Row
          3. Referencing Parts of a Table
        7. Formatting Tables
          1. Using Themes to Change Style Appearance
          2. Customizing Table Styles
      2. 22. Analyzing Data with PivotTable Reports
        1. Introducing PivotTables
        2. Creating a PivotTable
        3. Rearranging PivotTable Fields
        4. Refreshing a PivotTable
        5. Changing the Numeric Format of PivotTable Data
        6. Choosing Report Layout Options
        7. Formatting a PivotTable
          1. Customizing the Display of Empty or Error Cells
          2. Merging and Centering Field Labels
          3. Hiding Outline Controls
          4. Hiding Row Labels and Column Labels
        8. Displaying Totals and Subtotals
          1. Customizing Subtotals
        9. Sorting PivotTable Fields
        10. Filtering PivotTable Fields
        11. Changing PivotTable 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
        12. Grouping and Ungrouping Data
          1. Creating Ad Hoc Item Groupings
          2. Grouping Items in Date or Time Ranges
        13. Displaying the Details Behind a Data Value
        14. Creating PivotCharts
      3. 23. Working with External Data
        1. Using and Reusing Data Connections
          1. Setting Refresh Options
          2. Requiring or Not Requiring a Password to Refresh
          3. Refreshing on Demand
        2. Opening an Entire Access Table in Excel
        3. Working with Data in Text Files
          1. Using the Text Import Wizard
          2. Parsing Clipboard Text
        4. Working with XML Files
          1. Creating an Ad Hoc Mapping of XML Elements to Table Columns
          2. Importing XML Data Using an Existing XML Structure
        5. Using Microsoft Query to Import Data
          1. Choosing Tables and Fields (Columns)
          2. Filtering Records
          3. Sorting Records
            1. Saving the Query or Moving to Microsoft Query
          4. Working Directly with Microsoft Query
            1. Getting to Query
            2. Adding and Removing Tables
            3. Working with Joins
            4. Adding, Removing, and Moving Fields
            5. Renaming Fields
            6. Sorting the Result Set
              1. Sorting with the Toolbar
            7. Filtering the Result Set
              1. Creating Exact-Math Criteria
              2. Using Multiple Exact-Match Criteria
              3. Using Menu Commands to Specify Exact-Match Criteria
              4. Removing Criteria
              5. Specifying Comparison Criteria
              6. Filtering on Fields That Are Not in the Result Set
              7. Limiting the Result Set to Unique Entries
              8. Comparing Fields
            8. Performing Aggregate Calculations
              1. Clicking Through the Totals
              2. Cycling Through the Functions
              3. Using Menu Commands
              4. Aggregating Groups of Records
              5. Using More Than One Aggregate Field
              6. Filtering on Calculated Fields
            9. Creating a Parameter-Based Query
            10. Saving a Query
            11. Returning the Result Set to Excel
        6. Using a Web Query to Return Internet Data
          1. Using an Existing Web Query
          2. Creating Your Own Web Query
            1. Using the From Web Command
              1. Saving the IQY File and Setting Formatting Options
            2. Copying and Pasting from the Web Browser
            3. Using the Internet Explorer Export To Microsoft Excel Command
    13. 8. Collaborating
      1. 24. Collaborating on a Network or by E-Mail
        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
        4. Distributing Workbooks and Worksheets by E-Mail
          1. Sending an Entire Workbook as an E-Mail Attachment
          2. Sending a Worksheet or Range by E-Mail
          3. Sending a Workbook for Review
        5. Controlling Document Access with Information Rights Management
          1. Installing IRM
          2. Protecting a Document with IRM
            1. Setting an Expiration Date
            2. Allowing Users to Print
            3. Allowing Users to Click the Copy Command
            4. Allowing Programmatic Access
            5. Making the Current Settings the Default
            6. Adding New Users and Modifying Permissions Settings
          3. Using a Protected Document
      2. 25. Collaborating Using the Internet
        1. Using a Windows SharePoint Services Site
          1. Downloading and Uploading Documents
          2. Checking Documents In and Out
          3. Using the Document Management 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
          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
        2. Exporting Excel Tables to SharePoint
        3. Using Excel Services
          1. Publishing a Workbook to Excel Services
    14. 9. Automating Excel
      1. 26. Recording Macros
        1. Configuring Macro Security
        2. Using the Macro Recorder
          1. Recording with Relative References
          2. What to Do When the Macro Recorder Does Not Give You What You Expect
        3. Introducing the Visual Basic Editor
        4. Learning the Basics of Visual Basic
          1. Objects, Methods, and Properties
          2. The Object Browser
          3. Collections of Objects
          4. Manipulating an Object’s Properties Without Selecting the Object
          5. Naming Arguments to Methods
        5. Adding Code to or Editing Recorded Macros
        6. Using Subroutines in Macros
        7. Using the Personal Macro Workbook
        8. Going On from Here
      2. 27. Creating Custom Functions
        1. Creating a Simple Custom Function
        2. Using Custom Functions
        3. Understanding Custom Function Rules
        4. Using VBA Keywords in Custom Functions
        5. Documenting Macros and Custom Functions
        6. Creating Custom Functions with Optional Arguments
        7. Making Your Custom Functions Available Anywhere
      3. 28. 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
    15. 10. Integrating Excel with Other Applications
      1. 29. 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 Shape, Image, or Chart
        3. Selecting a Cell or Object Without Triggering the Hyperlink
          1. Editing or Deleting a Hyperlink
        4. Using Formulas to Create Hyperlinks
      2. 30. 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. Managing Links
          1. Choosing Automatic or Manual Update
          2. Fixing Broken Links
        6. Linking vs. Hyperlinking
      3. 31. Using Excel Data in Word 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 Office Excel Worksheet Object Format
            2. Using Formatted Text (RTF) and HTML Format
            3. Using Unformatted Text and Unformatted Unicode Text
            4. Using Picture (Windows Metafile), Bitmap, and Picture (Enhanced Metafile)
            5. Paste-Linking an Excel Table into Word
            6. 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
    16. 11. Appendixes
      1. A. Menu to Ribbon Command Reference
        1. File Menu
        2. Edit Menu
        3. View Menu
        4. Insert Menu
        5. Format Menu
        6. Tools Menu
        7. Data Menu
        8. Chart Menu
        9. Window Menu
        10. Help Menu
        11. Standard Toolbar
        12. Formatting Toolbar
      2. B. Keyboard Shortcuts
        1. Keyboard Shortcuts by Key
          1. Function Keys
          2. Control and Navigation Keys
          3. Numeric Keys
          4. Symbols and Keypad
          5. Alphabet Keys
        2. Keyboard Shortcuts by Task
          1. Charts and Select Chart Elements
          2. Dialog Box Text Boxes
          3. Dialog Boxes
          4. Edit Data
          5. Enter and Calculate Formulas
          6. Enter Data
          7. Extend a Selection
          8. Filter Lists
          9. Format Cells Dialog Box—Border Tab
          10. Format Data
          11. Help
          12. Help Window
          13. Insert, Delete, and Copy Cells
          14. Macros
          15. Ribbon and Buttons
          16. Move and Scroll—In End Mode
          17. Move and Scroll—Worksheets
          18. Move Within a Selected Range
          19. Print
          20. Print Preview
          21. Select Cells, Rows, Columns, and Objects
          22. Select Cells with Special Characteristics
          23. Show, Hide, and Outline Data
          24. Smart Tags
          25. Task Panes
          26. Windows and Office Interface
          27. Worksheets
      3. C. Function Reference
    17. D. Index to Troubleshooting Topics
    18. E. About the Authors
    19. F. Choose the Right Book for You
      1. Published and Upcoming Titles
        1. Plain & Simple
        2. Step by Step
        3. Inside Out
        4. Other Titles
    20. Index
    21. About the Authors
    22. SPECIAL OFFER: Upgrade this ebook with O’Reilly

Product information

  • Title: Microsoft® Office Excel® 2007 Inside Out
  • Author(s):
  • Release date: January 2007
  • Publisher(s): Microsoft Press
  • ISBN: 9780735623217