Excel 2007: The Missing Manual

Spis treści książki

• Excel 2007: The Missing Manual
  • SPECIAL OFFER: Upgrade this ebook with OReilly
  • A Note Regarding Supplemental Files
  • The Missing Credits
    • About the Author
    • About the Creative Team
    • Acknowledgements
    • The Missing Manual Series
  • Introduction
    • What You Can Do with Excel
    • Excels New Face
      • The Ribbon
      • Using the Ribbon with the Keyboard
      • The Office Menu
      • The Quick Access Toolbar
    • Excel's New Features
    • About This Book
      • About the Outline
      • About These Arrows
        • Contextual tabs
        • Drop-down buttons
        • Dialog box launchers
        • Ordinary menus
      • About Shortcut Keys
      • About Clicking
      • Examples
      • About MissingManuals.com
      • Safari Enabled
  • I. Worksheet Basics
    • 1. Creating and Navigating Worksheets
      • 1.1. Creating a Basic Worksheet
        • 1.1.1. Starting a New Workbook
        • 1.1.2. Adding the Column Titles
        • 1.1.3. Adding Data
      • 1.2. Editing Data
      • 1.3. Navigating in Excel
        • 1.3.1. The Tabs of the Ribbon
        • 1.3.2. The Formula Bar
        • 1.3.3. The Status Bar
        • 1.3.4. Excel Options
      • 1.4. Saving Files
        • 1.4.1. The Excel 2007 File Format
        • 1.4.2. Saving Your Spreadsheet in Older Formats
          • 1.4.2.1. Compatibility mode
          • 1.4.2.2. The Compatibility Checker
        • 1.4.3. Saving Your Spreadsheet As a PDF
        • 1.4.4. Saving Your Spreadsheet with a Password
        • 1.4.5. Disaster Recovery
      • 1.5. Opening Files
        • 1.5.1. Opening Fileswith a Twist
        • 1.5.2. Opening Multiple Spreadsheets at Once
    • 2. Adding Information to Worksheets
      • 2.1. Adding Different Types of Data
        • 2.1.1. Controlling Your Data Types
          • 2.1.1.1. How Excel decides your data is text
          • 2.1.1.2. How Excel decides your data is numeric
          • 2.1.1.3. How Excel decides your data is a date or time
      • 2.2. Quick Ways to Add Data
        • 2.2.1. AutoComplete
        • 2.2.2. AutoCorrect
        • 2.2.3. AutoFill
          • 2.2.3.1. Custom AutoFill lists
        • 2.2.4. AutoFit
        • 2.2.5. Undo and Redo
    • 3. Moving Data Around a Worksheet
      • 3.1. Selecting Cells
        • 3.1.1. Making Continuous Range Selections
        • 3.1.2. Making Non-Contiguous Selections
        • 3.1.3. Automatically Selecting Your Data
        • 3.1.4. Making Selections with the Keyboard
      • 3.2. Moving Cells Around
        • 3.2.1. A Simple Cut-and-Paste or Copy-and-Paste
        • 3.2.2. A Fancy Cut-and-Paste or Copy-and-Paste
        • 3.2.3. The Clipboard
        • 3.2.4. Special Pasting
      • 3.3. Adding and Moving Columns or Rows
        • 3.3.1. Inserting Columns
        • 3.3.2. Inserting Rows
        • 3.3.3. Inserting Copied or Cut Cells
        • 3.3.4. Deleting Columns and Rows
    • 4. Managing Worksheets and Workbooks
      • 4.1. Worksheets and Workbooks
        • 4.1.1. Adding, Removing, and Hiding Worksheets
        • 4.1.2. Naming and Rearranging Worksheets
        • 4.1.3. Grouping Sheets
          • 4.1.3.1. Moving, copying, deleting, or hiding grouped worksheets
          • 4.1.3.2. Formatting cells, columns, and rows in grouped worksheets
          • 4.1.3.3. Entering data or changing cells in grouped worksheets
          • 4.1.3.4. Cutting, copying, and pasting cells in grouped worksheets
          • 4.1.3.5. Adjusting printing and display options in grouped worksheets
        • 4.1.4. Moving Worksheets from One Workbook to Another
      • 4.2. Find and Replace
        • 4.2.1. The Basic Find
        • 4.2.2. Find All
        • 4.2.3. More Advanced Searches
        • 4.2.4. Finding Formatted Cells
        • 4.2.5. Finding and Replacing Values
      • 4.3. Spell Check
        • 4.3.1. Spell Checking Options
    • 5. Formatting Cells
      • 5.1. Formatting Cell Values
        • 5.1.1. Formatting Numbers
          • 5.1.1.1. General
          • 5.1.1.2. Number
          • 5.1.1.3. Currency
          • 5.1.1.4. Accounting
          • 5.1.1.5. Percentage
          • 5.1.1.6. Fraction
          • 5.1.1.7. Scientific
          • 5.1.1.8. Text
        • 5.1.2. Formatting Dates and Times
        • 5.1.3. Special Formats for Special Numbers
        • 5.1.4. Custom Formats
          • 5.1.4.1. Creating a custom format
          • 5.1.4.2. Custom format string codes
          • 5.1.4.3. Date and time format strings
          • 5.1.4.4. Number format strings
          • 5.1.4.5. Text format strings
      • 5.2. Formatting Cell Appearance
        • 5.2.1. Alignment and Orientation
        • 5.2.2. Fonts and Color
          • 5.2.2.1. Special characters
        • 5.2.3. Borders and Fills
        • 5.2.4. Drawing Borders by Hand
    • 6. Smart Formatting Tricks
      • 6.1. The Format Painter
      • 6.2. Styles and Themes
        • 6.2.1. Custom Styles
        • 6.2.2. Modifying Styles
        • 6.2.3. Transferring Styles Between Workbooks
        • 6.2.4. Themes: A Package of Styles
          • 6.2.4.1. Fonts
          • 6.2.4.2. Colors
          • 6.2.4.3. Effects
        • 6.2.5. Modifying Themes
      • 6.3. Conditional Formatting
        • 6.3.1. The Basics of Conditional Formatting
        • 6.3.2. Highlighting Specific Values
        • 6.3.3. Data Bars
        • 6.3.4. Color Scales
        • 6.3.5. Icon Sets
        • 6.3.6. Fine-Tuning a Formatting Rule
          • 6.3.6.1. Fine-tuning data bars
          • 6.3.6.2. Fine-tuning color scales
          • 6.3.6.3. Fine-tuning icon sets
        • 6.3.7. Using Multiple Rules
    • 7. Viewing and Printing Worksheets
      • 7.1. Controlling Your View
        • 7.1.1. Zooming
        • 7.1.2. Viewing Distant Parts of a Spreadsheet at Once
        • 7.1.3. Freezing Columns or Rows
        • 7.1.4. Hiding Data
        • 7.1.5. Saving View Settings
        • 7.1.6. Viewing Multiple Workbooks at Once
      • 7.2. Printing
        • 7.2.1. How to Print an Excel File
        • 7.2.2. Quick Printing
        • 7.2.3. Previewing Your Printout
        • 7.2.4. Creating Headers and Footers
        • 7.2.5. Customizing Print Settings
          • 7.2.5.1. Margins
          • 7.2.5.2. Paper size and orientation
          • 7.2.5.3. Sheet settings
      • 7.3. Controlling Pagination
        • 7.3.1. Page Breaks
        • 7.3.2. Scaling
        • 7.3.3. Page Break Preview: A Bird's-Eye View of Your Worksheet
  • II. Formulas and Functions
    • 8. Building Basic Formulas
      • 8.1. Creating a Basic Formula
        • 8.1.1. Excel's Order of Operations
        • 8.1.2. Cell References
        • 8.1.3. How Excel Formats Cells That Contain Cell References
        • 8.1.4. Functions
          • 8.1.4.1. Using a function in a formula
          • 8.1.4.2. Using cell references with a function
          • 8.1.4.3. Using cell ranges with a function
        • 8.1.5. Formula Errors
        • 8.1.6. Logical Operators
      • 8.2. Formula Shortcuts
        • 8.2.1. Point-and-Click Formula Creation
        • 8.2.2. Point-and-Click Formula Editing
        • 8.2.3. The Formulas Tab
        • 8.2.4. Using the Insert Function Button
      • 8.3. Copying Formulas
        • 8.3.1. Absolute Cell References
        • 8.3.2. Partially Fixed References
        • 8.3.3. Referring to Other Worksheets and Workbooks
          • 8.3.3.1. Updating formulas that refer to other workbooks
    • 9. Math and Statistical Functions
      • 9.1. Rounding Numbers
        • 9.1.1. ROUND( ), ROUNDDOWN( ), ROUNDUP( ): Rounding Numbers
        • 9.1.2. MROUND( ), CEILING( ), and FLOOR( ): More Rounding Functions
        • 9.1.3. INT( ) and TRUNC( ): Chopping Off Non-Whole Numbers
        • 9.1.4. EVEN( ) and ODD( ): Rounding Up to Even or Odd Values
      • 9.2. Groups of Numbers
        • 9.2.1. SUM( ): Summing Up Numbers
        • 9.2.2. COUNT( ), COUNTA( ), and COUNTBLANK( ): Counting Items in a List
        • 9.2.3. MAX( ) and MIN( ): Finding Maximum and Minimum Values
        • 9.2.4. LARGE( ), SMALL( ), and RANK( ): Ranking Your Numbers
        • 9.2.5. AVERAGE( ) and MEDIAN( ): Finding Average or Median Values
        • 9.2.6. MODE( ): Finding Numbers That Frequently Occur in a List
        • 9.2.7. PERCENTILE( ) and PERCENTRANK( ): Advanced Ranking Functions
        • 9.2.8. FREQUENCY( ): Identifying to Which Category a Number Belongs
      • 9.3. General Math Functions
        • 9.3.1. PRODUCT( ), FACT( ), POWER( ), and SQRT( ): Products, Factorials, Powers, and Square Roots
        • 9.3.2. QUOTIENT( ) and MOD( ): Higher Division
        • 9.3.3. ABS( ) and SIGN( ): Absolute Value and Determining a Number's Sign
        • 9.3.4. RAND( ) and RANDBETWEEN( ): Generating Random Numbers
        • 9.3.5. GCD( ) and LCM( ): Greatest and Least Common Denominator
        • 9.3.6. COMBIN( ) and PERMUT( ): Figuring Combinations and Permutations
      • 9.4. Trigonometry and Advanced Math
      • 9.5. Advanced Statistics
    • 10. Financial Functions
      • 10.1. The World of Finance
      • 10.2. Financial Functions
        • 10.2.1. FV( ): Future Value
        • 10.2.2. PV( ): Present Value
        • 10.2.3. PMT( ), PPMT( ), and IPMT( ): Calculating the Number of Payments You Need to Make
        • 10.2.4. NPER( ): Figuring Out How Much Time You'll Need to Pay Off a Loan or Meet an Investment Target
        • 10.2.5. RATE( ): Figuring the Interest Rate You Need to Achieve Future Value
        • 10.2.6. NPV( ) and IRR( ): Net Present Value and Internal Rate of Return
      • 10.3. Depreciation
      • 10.4. Other Financial Functions
    • 11. Manipulating Dates, Times, and Text
      • 11.1. Manipulating Text
        • 11.1.1. CONCATENATE( ): Joining Strings of Text Together
        • 11.1.2. LEFT( ), MID( ), and RIGHT( ): Copying Portions of a Text String
        • 11.1.3. LEN( ), FIND( ), and SEARCH( ): Counting Characters in a String
        • 11.1.4. UPPER( ), LOWER( ), and PROPER( ): Changing Capitalization
        • 11.1.5. TRIM( ) and CLEAN( ): Removing Unwanted Spaces and Non-Printing Characters
        • 11.1.6. SUBSTITUTE( ): Replacing One Sequence of Characters with Another
        • 11.1.7. TEXT( ), VALUE( ), FIXED( ), and DOLLAR( ): Converting Text to Numbers and Vice Versa
        • 11.1.8. Other Text Functions
      • 11.2. Manipulating Dates and Times
      • 11.3. Math with Dates and Times
        • 11.3.1. Using Dates and Times with Ordinary Functions
      • 11.4. Date and Time Functions
        • 11.4.1. TODAY( ) and NOW( ): Inserting the Current Date and Time
        • 11.4.2. DATE( ) and TIME( ): Dates and Times in Calculations
        • 11.4.3. DAY( ), MONTH( ), and YEAR( ): More Date Calculations
        • 11.4.4. HOUR( ), MINUTE( ), SECOND( ): More Time Calculations
        • 11.4.5. WEEKDAY( ): Determining the Day of the Week
        • 11.4.6. DATEDIF( ): Calculating the Difference Between Dates
        • 11.4.7. DATEVALUE( ) and TIMEVALUE( ): Converting Dates and Times into Serial Numbers
        • 11.4.8. DAYS360( ): Finding Out the Number of Days Between Two Dates
        • 11.4.9. EDATE( ): Calculating Future Dates
        • 11.4.10. YEARFRAC( ): Calculating the Percentage of a Year Between Two Dates
        • 11.4.11. EOMONTH( ): Finding the Last Day of Any Month
        • 11.4.12. NETWORKDAYS( ): Counting the Number of Business Days
        • 11.4.13. WORKDAY( ): Figuring Out When Days Will Fall in the Future
        • 11.4.14. WEEKNUM( ): Figuring Out in Which Week a Date Falls
    • 12. Lookup, Reference, and Information Functions
      • 12.1. The Basic Lookup
        • 12.1.1. VLOOKUP( ): Vertical Lookups
        • 12.1.2. HLOOKUP( ): Horizontal Lookups
      • 12.2. Advanced Lookups
        • 12.2.1. MATCH( ): Finding the Position of Items in a Range
        • 12.2.2. INDEX( ): Retrieving the Value from a Cell
        • 12.2.3. Performing a "Left Lookup"
        • 12.2.4. Performing a Double Lookup
        • 12.2.5. The Lookup Wizard
        • 12.2.6. OFFSET( ): Moving Cell References to a New Location
        • 12.2.7. Other Reference and Lookup Functions
        • 12.2.8. INDIRECT( ) and ADDRESS( ): Working with Cell References Stored As Text
        • 12.2.9. TRANSPOSE( ): Changing Rows into Columns and Vice Versa
      • 12.3. Information Functions
        • 12.3.1. The "IS" Functions: Checking the Value Inside a Cell
        • 12.3.2. TYPE( ) and ERROR.TYPE( ): Finding a Value's Data Type or Error Type
        • 12.3.3. INFO( ) and CELL( ): Gathering Info About Your Computer and Your Worksheet's Cells
      • 12.4. Tutorial: Quickly Generating Invoices from a Product Catalog
    • 13. Advanced Formula Writing and Troubleshooting
      • 13.1. Conditions in Formulas
        • 13.1.1. IF(): Building Conditional Formulas
        • 13.1.2. COUNTIF(): Counting Only the Cells You Specify
        • 13.1.3. SUMIF(): Adding Only the Cells You Specify
        • 13.1.4. COUNTIFS() and SUMIFS(): Counting and Summing Using Multiple Criteria
      • 13.2. Descriptive Names for Cell References
        • 13.2.1. Creating and Using a Named Range
        • 13.2.2. Creating Slightly Smarter Named Ranges
        • 13.2.3. Naming Formulas and Constants
        • 13.2.4. Managing Named Ranges
        • 13.2.5. Automatically Creating Named Ranges
        • 13.2.6. Applying Names to Existing Formulas
      • 13.3. Variable Data Tables
        • 13.3.1. Creating a One-Variable Data Table
        • 13.3.2. Creating a Two-Variable Data Table
      • 13.4. Controlling Recalculation
      • 13.5. Solving Formula Errors
        • 13.5.1. Step-by-Step Evaluation
        • 13.5.2. Tracing Precedents and Dependents
        • 13.5.3. Error Checking
  • III. Organizing Worksheets
    • 14. Tables: List Management Made Easy
      • 14.1. The Basics of Tables
        • 14.1.1. Creating a Table
        • 14.1.2. Formatting a Table
        • 14.1.3. Editing a Table
        • 14.1.4. Selecting Parts of a Table
      • 14.2. Sorting and Filtering a Table
        • 14.2.1. Applying a Simple Sort Order
        • 14.2.2. Sorting with Multiple Criteria
        • 14.2.3. Sorting by Color
        • 14.2.4. Filtering with the List of Values
        • 14.2.5. Creating Smarter Filters
          • 14.2.5.1. Filtering dates
          • 14.2.5.2. Filtering numbers
          • 14.2.5.3. Filtering text
      • 14.3. Dealing with Duplicate Rows
        • 14.3.1. Highlighting Duplicates
        • 14.3.2. Removing Duplicates Automatically
      • 14.4. Performing Table Calculations
        • 14.4.1. Dynamic Calculations
        • 14.4.2. Column Names
        • 14.4.3. Table Names
        • 14.4.4. The Total Row
        • 14.4.5. The SUBTOTAL() Function
        • 14.4.6. The Database Functions
    • 15. Grouping and Outlining Data
      • 15.1. Basic Data Grouping
        • 15.1.1. Creating a Group
        • 15.1.2. Nesting Groups Within Groups
        • 15.1.3. Summarizing Your Data
        • 15.1.4. Combining Data from Multiple Tables
      • 15.2. Grouping Timesavers
        • 15.2.1. Auto Outline
        • 15.2.2. Automatic Subtotaling
    • 16. Templates
      • 16.1. Understanding Templates
      • 16.2. Creating a New Workbook from a Template
        • 16.2.1. Downloading Templates (Method 1: The New Workbook Dialog Box)
        • 16.2.2. Downloading Templates (Method 2: The Office Online Web Site)
      • 16.3. Creating Templates
        • 16.3.1. Understanding Custom Templates
        • 16.3.2. Building a Custom Template
        • 16.3.3. Sharing Templates with Others
  • IV. Charts and Graphics
    • 17. Creating Basic Charts
      • 17.1. Charting 101
        • 17.1.1. Embedded and Standalone Charts
        • 17.1.2. Creating a Chart with the Ribbon
        • 17.1.3. The Chart Tools Ribbon Tabs
      • 17.2. Basic Tasks with Charts
        • 17.2.1. Moving and Resizing a Chart
        • 17.2.2. Creating a Standalone Chart
        • 17.2.3. Editing and Adding to Chart Data
        • 17.2.4. Changing the Chart Type
        • 17.2.5. Printing Charts
          • 17.2.5.1. Embedded charts
          • 17.2.5.2. Standalone charts
      • 17.3. Practical Charting
        • 17.3.1. Charts with Multiple Series of Numbers
        • 17.3.2. Controlling the Data Excel Plots on the X-Axis
        • 17.3.3. Data That Uses a Date or Time Scale
        • 17.3.4. Non-Contiguous Chart Ranges
        • 17.3.5. Changing the Order of Your Data Series
        • 17.3.6. Changing the Way Excel Plots Blank Values
      • 17.4. Chart Types
        • 17.4.1. Column
        • 17.4.2. Bar
        • 17.4.3. Line
        • 17.4.4. Pie
        • 17.4.5. Area
        • 17.4.6. XY (Scatter)
        • 17.4.7. Stock
        • 17.4.8. Surface
        • 17.4.9. Donut
        • 17.4.10. Bubble
        • 17.4.11. Radar
    • 18. Formatting and Perfecting Charts
      • 18.1. Chart Styles and Layouts
        • 18.1.1. Chart Styles
        • 18.1.2. Chart Layouts
      • 18.2. Adding Chart Elements
        • 18.2.1. Adding Titles
        • 18.2.2. Adding a Legend
        • 18.2.3. Adding Data Labels to a Series
        • 18.2.4. Adding Individual Data Labels
        • 18.2.5. Adding a Data Table
      • 18.3. Selecting Chart Elements
      • 18.4. Formatting Chart Elements
        • 18.4.1. Coloring the Background
        • 18.4.2. Fancy Fills
          • 18.4.2.1. Gradient fills
          • 18.4.2.2. Texture fills
          • 18.4.2.3. Picture fills
        • 18.4.3. Fancy Borders and Lines
        • 18.4.4. Formatting Data Series and Data Points
        • 18.4.5. Reusing Your Favorite Charts with Templates
      • 18.5. Improving Your Charts
        • 18.5.1. Controlling a Chart's Scale
        • 18.5.2. Adding a Trendline
        • 18.5.3. Adding Error Bars to Scientific Data
        • 18.5.4. Formatting 3-D Charts
        • 18.5.5. Changing the Shape of a 3-D Column
      • 18.6. Advanced Charting
        • 18.6.1. Exploding Slices in a Pie
        • 18.6.2. Grouping Slices in a Pie
        • 18.6.3. Gaps, Widths, and Overlays in a Column Chart
        • 18.6.4. Creating Combination Charts
    • 19. Inserting Graphics
      • 19.1. Adding Pictures to a Worksheet
        • 19.1.1. Inserting a Picture
        • 19.1.2. Positioning and Resizing a Picture
        • 19.1.3. Picture Touch-Up
        • 19.1.4. Compressing Pictures
        • 19.1.5. Cropping and Shaping a Picture
        • 19.1.6. Picture Borders, Effects, and Styles
      • 19.2. Excel's Clip Art Library
      • 19.3. Drawing Shapes
        • 19.3.1. Drawing a Shape
        • 19.3.2. Adding Text to a Shape
        • 19.3.3. Selecting and Arranging Shapes
        • 19.3.4. Connecting Shapes
        • 19.3.5. SmartArt
  • V. Advanced Data Analysis
    • 20. Scenarios and Goal Seeking
      • 20.1. Using Scenarios
        • 20.1.1. Creating a New Scenario
        • 20.1.2. Managing Scenarios
        • 20.1.3. Creating a Summary Report
      • 20.2. Using Goal Seek
        • 20.2.1. Goal Seeking with Complex Equations
      • 20.3. Solver
        • 20.3.1. Understanding Solver
        • 20.3.2. Defining a Problem in Solver
        • 20.3.3. Advanced Solver Solutions
        • 20.3.4. Saving Solver Models
        • 20.3.5. Configuring Solver
    • 21. Pivot Tables
      • 21.1. Summary Tables Revisited
        • 21.1.1. Life Without Pivot Tables
        • 21.1.2. Life with Pivot Tables
      • 21.2. Building Pivot Tables
        • 21.2.1. Preparing a Pivot Table
        • 21.2.2. Pivot Table Regions
        • 21.2.3. Laying Out a Pivot Table
        • 21.2.4. Formatting a Pivot Table
        • 21.2.5. Rearranging a Pivot Table
      • 21.3. Multi-Layered Pivot Tables
        • 21.3.1. Hiding and Showing Details
      • 21.4. Fine-Tuning Pivot Table Calculations
        • 21.4.1. Changing the Type of Calculation
        • 21.4.2. Adding a Calculated Field
      • 21.5. Filtering a Pivot Table
        • 21.5.1. Report Filtering
        • 21.5.2. Group Filtering
      • 21.6. Pivot Charts
        • 21.6.1. Creating a Pivot Chart
        • 21.6.2. Manipulating a Pivot Chart
  • VI. Sharing Data with the Rest of the World
    • 22. Protecting Your Workbooks
      • 22.1. Understanding Excel's Safeguards
      • 22.2. Data Validation
        • 22.2.1. Settings
        • 22.2.2. Input Message
        • 22.2.3. Error Alert
        • 22.2.4. Data Validation with Formulas and Cell References
        • 22.2.5. Data Validation with Lists
      • 22.3. Locked and Hidden Cells
        • 22.3.1. Protecting a Worksheet
        • 22.3.2. Protecting the Entire Workbook
        • 22.3.3. Protecting Cell Ranges (with More Passwords)
        • 22.3.4. Allowing Specific Windows Users to Edit a Range
    • 23. Worksheet Collaboration
      • 23.1. Preparing Your Workbook
        • 23.1.1. Document Properties
        • 23.1.2. Distributing a Document
      • 23.2. Adding Comments
        • 23.2.1. Inserting a Comment
        • 23.2.2. Showing and Hiding Comments
        • 23.2.3. Fine-Tuning Comments
        • 23.2.4. Reviewing Comments
        • 23.2.5. Printing Comments
      • 23.3. Tracking Changes
        • 23.3.1. Switching On Change Tracking
        • 23.3.2. Understanding the Change Log
        • 23.3.3. Highlighting Changes
        • 23.3.4. Examining the Change Log
        • 23.3.5. Accepting and Rejecting Changes
        • 23.3.6. Merging Multiple Revisions into One Workbook
      • 23.4. Sharing Your Workbook
        • 23.4.1. Multiple Users Without Workbook Sharing
        • 23.4.2. Turning On Workbook Sharing
        • 23.4.3. Workbook Sharing in Action
    • 24. Querying Databases and XML Files
      • 24.1. Excel and Databases
        • 24.1.1. Connecting to an Access Database
        • 24.1.2. Refreshing Data
        • 24.1.3. Data Source Security
        • 24.1.4. Connecting to a SQL Server Database
        • 24.1.5. Reusing Your Database Connection
      • 24.2. Understanding XML
        • 24.2.1. What Is XML, Really?
        • 24.2.2. Three Rules of XML
          • 24.2.2.1. The prolog
          • 24.2.2.2. Elements
          • 24.2.2.3. Nesting
        • 24.2.3. XML Files and Schemas
      • 24.3. Excel and XML
        • 24.3.1. Mapping a Simple Document
        • 24.3.2. Importing and Exporting XML
        • 24.3.3. Mapping Lists
        • 24.3.4. Gaining the Benefits of XML Mapping
    • 25. Exchanging Data with Other Programs
      • 25.1. Sharing Information in Windows
      • 25.2. Embedding and Linking Objects
        • 25.2.1. Exporting Charts out of Excel
        • 25.2.2. Editing a Linked Object
        • 25.2.3. Editing an Embedded Object
        • 25.2.4. Importing Objects into Excel
      • 25.3. Transferring Data
        • 25.3.1. Exporting Tables of Data
        • 25.3.2. Importing Tables of Data
        • 25.3.3. Importing Text Files
    • 26. Connecting Worksheets to the Web
      • 26.1. Putting Worksheets on the Web
        • 26.1.1. Saving an HTML File
      • 26.2. Performing Web Queries
        • 26.2.1. The Limitations of Web Queries
        • 26.2.2. Creating a Web Query
        • 26.2.3. The Research Panea Web Query Alternative
      • 26.3. Using Hyperlinks
        • 26.3.1. Adding a Hyperlink to a Web Page or Document
        • 26.3.2. Adding a Hyperlink to a Worksheet Location
        • 26.3.3. The HYPERLINK() Function: Creating a Link on Your Own
  • VII. Programming Excel
    • 27. Automating Tasks with Macros
      • 27.1. Macros 101
        • 27.1.1. Macro-Free and Macro-Enabled Workbooks
      • 27.2. The Macro Recorder
        • 27.2.1. Relative and Absolute Recording
        • 27.2.2. Where Macros Live
        • 27.2.3. Recording a Macro
        • 27.2.4. Playing a Macro
      • 27.3. Macro Security
        • 27.3.1. The Trust Center
        • 27.3.2. Setting Up a Trusted Location
      • 27.4. Creating Practical Macros
        • 27.4.1. Inserting a Header
        • 27.4.2. Alternating Row Formatting
        • 27.4.3. A Combined Task
        • 27.4.4. Placing a Macro on the Quick Access Toolbar
        • 27.4.5. Attaching a Macro to a Button Inside a Worksheet
    • 28. Programming Spreadsheets with VBA
      • 28.1. The Visual Basic Editor
        • 28.1.1. The Project Window
        • 28.1.2. Modules and Macros
        • 28.1.3. Finding and Moving Macros
        • 28.1.4. Debugging a Macro
      • 28.2. Understanding Macro Code
        • 28.2.1. The Anatomy of a Macro
        • 28.2.2. Objects 101
        • 28.2.3. Using Properties and Methods
        • 28.2.4. Hunting for Objects
      • 28.3. Exploring the VBA Language
        • 28.3.1. Entering Text in the Current Cell
        • 28.3.2. Moving to Other Cells
        • 28.3.3. Editing Specific Cells
        • 28.3.4. Formatting Cells
        • 28.3.5. Using Variables
        • 28.3.6. Making Decisions
        • 28.3.7. Repeating Actions with a Loop
        • 28.3.8. Creating Custom Functions
  • VIII. Appendix
    • A. Customizing the Quick Access Toolbar
      • A.1. The Quick Access Toolbar
        • A.1.1. Adding Buttons
        • A.1.2. Customizing Specific Workbooks
  • Index
  • About the Author
  • Colophon
  • SPECIAL OFFER: Upgrade this ebook with OReilly

pokaż cały spis treści

ukryj recenzje