Special Edition Using® Microsoft® Office Excel 2003

Special Edition Using® Microsoft® Office Excel 2003

by Patrick Blattner
Released September 2003
Publisher(s): Que
ISBN: 9780789729538

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

Does your life play out in a spreadsheet? Are numbers in columns and rows what make or break you in the work world?

If you answered yes to either of these questions, then Special Edition Using Microsoft Office Excel 2003 is the book that will make it all better. Learn quickly and efficiently from a true Excel master using the tried and true Special Edition Using formula for success. Here, you'll find information that's undocumented elsewhere - even in Microsoft's own Help systems. You'll learn from finely crafted, real-life examples built by an author who lives and dies by the integrity of his spreadsheets.

Unlike most CDs pasted into the back of computer books, this is one that rivals the book in terms of value. You receive a FREE copy of Woody's Office Power Pack (WOPR) - the #1 enhancement to Office. This software sells for $49.99 and you get it for free here...no strings attached.

Table of contents

  1. Copyright
    1. Dedication
  2. About the Author
  3. We Want to Hear from You!
  4. Introduction
    1. New Features and Enhancements in Excel 2003
    2. How This Book Is Organized
      1. Part I: Getting Started with Excel 2003
      2. Part II: Formatting and Printing Excel Worksheets
      3. Part III: Using Formulas and Functions
      4. Part IV: Using Excel’s Analysis Tools
      5. Part V: Creating and Modifying Charts
      6. Part VI: Using Excel in Business
      7. Part VII: Integrating Excel with Other Applications
      8. Web Content
    3. Conventions Used in This Book
      1. Text Conventions
      2. Special Elements
        1. “Signature” Tips
        2. Notes
        3. Cautions
        4. Troubleshooting
        5. Excel in Practice
        6. Cross-References
  5. I. Getting Started with Excel 2003
    1. 1. Getting Around Excel
      1. Starting and Exiting the Excel Program
      2. Identifying Workbook Elements
        1. Using ScreenTips and What’s This? Help
        2. The New Workbook Task Pane
        3. Help, Search, and Clip-Art Task Panes
        4. Viewing Toolbars
        5. Working with Excel Menus
        6. Working with Shortcut Menus
      3. Working with Workbooks
        1. Coloring Workbook Tabs
        2. Inserting Sheets
        3. Naming Worksheets
        4. Rearranging Worksheets
        5. Grouping and Ungrouping Sheets
        6. Using Worksheet Keyboard Shortcuts
        7. Accessing Cells with Go To
      4. Understanding Cell Basics
      5. Entering Text
        1. Labeling Columns and Rows
        2. Adjusting Column Width and Row Height
      6. Saving Excel Data
        1. Performing a First-Time Save
        2. Saving Your Workbook with a Password and File Encryption
        3. Document Recovery
        4. Saving Excel Files in Alternative Formats
        5. Saving an Excel Workbook as a Reusable Template
          1. Creating Template Content
          2. Starting a New Workbook from a Template
      7. Controlling Your Worksheet View
        1. Switching Between Open Workbooks
        2. Freezing Columns and Rows
        3. Splitting the Screen
        4. Hiding and Unhiding Rows and Columns
      8. Troubleshooting
        1. Viewing More Sheet Tabs
        2. Distinguishing One Version of a File from Another
        3. Creating a Template from an Existing Workbook
        4. Why Save to the Templates Folder?
      9. Excel in Practice
    2. 2. Spreadsheet Basics
      1. Selecting Cells
      2. Selecting Columns and Rows
      3. Selecting Noncontiguous Ranges
      4. Selecting Ranges on Grouped Worksheets
      5. Naming Ranges for Fast Access
      6. Troubleshooting
        1. Range Naming Errors
        2. Deselecting Grouped Sheets
      7. Excel in Practice
    3. 3. Editing Spreadsheets
      1. The Clipboard Viewer
      2. Editing with the Keyboard
        1. Editing a Group of Cells
        2. Editing Using the Formula Bar
        3. Moving Cell Content with Drag and Drop
        4. Copying Cell Content with Drag and Drop
      3. Creating a Data Series
      4. Creating a Custom Fill Series
      5. Creating a Custom Fill to Define Linear or Growth Trends
      6. Creating a Custom List or Series
      7. Replacing Data
      8. Tracking Changes Made by Multiple Users
        1. Enabling and Disabling Revision Tracking
        2. Accepting and Rejecting Changes
      9. Using Comments to Explain Cell Content
      10. Linking Excel Data
        1. Establishing Links Between Worksheets or Workbooks
        2. Updating Links to a Workbook
        3. Locking Linked External Data
        4. Redirecting Links
        5. Breaking Links Between Files
      11. Troubleshooting
        1. Unaccepting Accepted Changes
      12. Excel in Practice
  6. II. Formatting and Printing Excel Worksheets
    1. 4. Applying Number and Date Formats
      1. Applying Common Numeric Formats from the Toolbar
      2. Applying Built-In Formats
        1. Formatting the Date and Time
        2. Fractions
        3. Special
      3. Creating Custom Formats
        1. Working with Date and Time Formatting Codes
        2. Working with Number and Text Formatting Codes
      4. Applying Custom Currency
      5. Working with Large Numbers
      6. Pasting In Custom Characters
      7. Creating Custom Conditions Codes
      8. Repeating Characters
      9. Excel in Practice
    2. 5. Formatting and Printing
      1. Why Change the Formatting?
        1. Using the Formatting Toolbar
        2. Using the Format Cells Dialog Box
      2. Changing the Font, Point Size, and Font Styles
        1. Changing the Font
        2. Setting the Point Size
        3. Adding Font Styles
        4. Formatting Individual Characters
        5. Resetting Excel’s Default Font
      3. Working with Styles
        1. Editing the Default Styles
        2. Creating and Applying Custom Styles
        3. Merging Styles from One Workbook to Another
        4. Formatting Titles with Merge and Center
        5. Adjusting Alignment Within Cells, Columns, and Rows
          1. Aligning Cell Content
          2. Rotating and Wrapping Text
      4. Applying and Drawing Borders and Shading
        1. Using Borders Effectively
        2. Using Colors, Patterns, and Textured Fills
      5. Using AutoFormat to Enhance Your Worksheets
      6. Using the Drawing Toolbar
      7. Ordering, Grouping, Moving, and Resizing Drawn Objects
        1. Changing the Order of Overlapping Drawn Objects
        2. Grouping Drawn Shapes and Lines
      8. Combining Drawing Tools with Charts and Worksheets
        1. Laying a Chart on a Bevel
      9. Printing a Worksheet
        1. Selecting a Printer
        2. Choosing the Print Range
      10. Previewing the Print Job
      11. Using Page Break Preview
      12. Working with Page Setup Options
        1. Working with Orientation
        2. Scaling the Printout
        3. Choosing a Paper Size
        4. Adjusting Print Quality
        5. Setting Worksheet Margins
          1. Setting Header and Footer Margins
          2. Centering the Worksheet on the Page
        6. Creating Headers and Footers
          1. Entering Header and Footer Content
          2. Inserting Header and Footer Fields
        7. Working with Sheet Settings
          1. Selecting a Print Area
      13. Troubleshooting
        1. Changing the Default Font for the Active Workbook
        2. Creating Permanent Styles
        3. Where Did the Worksheet Title Go?
      14. Excel in Practice
        1. Media Matrix
        2. Media Plan
        3. Slider Charts
        4. Inclusion Matrix with Webdings
        5. Other Formatting Tips
  7. III. Using Formulas and Functions
    1. 6. Function Fundamentals
      1. The Basic Parts of a Formula
        1. Functions Versus Formulas
        2. Arguments
        3. Operators
        4. Operator Order
        5. Using Arithmetic Operators for Simple Math
          1. Controlling the Order of Operations
          2. Nesting Calculations Within a Formula
        6. Custom Functions
        7. Applying a Name to a Range
        8. Pasting a Named Range in a Formula
      2. Using the AutoSum Feature
      3. Editing Formulas
        1. Dragging a Range Border to Change a Reference
        2. Typing References Directly into a Formula
      4. Writing Multiple Copies of a Formula
        1. Copying Formulas with AutoFill
        2. Entering Multiple Formulas All at Once
      5. Using AutoCalculate for Quick Totals
        1. Interpreting Formula Error Messages
      6. Relative Versus Absolute Referencing
      7. Using the Watch Window
      8. Referencing Values in Other Worksheets and Workbooks
        1. Referencing Other Worksheets
        2. Applying Cubed Formulas
        3. Referencing Other Workbooks
        4. Updating Values in Referenced Workbooks
      9. Troubleshooting
        1. Common Formula Errors
        2. Transposing Ranges with Formulas
        3. AutoSum Won’t Summarize the Entire List of Numbers
      10. Excel in Practice
    2. 7. Database Functions
      1. Database Functions Overview and Syntax
      2. DAVERAGE
      3. DCOUNT
      4. DGET
      5. DMAX
      6. DMIN
      7. DPRODUCT
      8. DSUM
      9. GETPIVOTDATA
      10. Troubleshooting
        1. Inserting PivotTables
      11. Excel in Practice
    3. 8. Date and Time Functions
      1. Date and Time Functions Overview
      2. DATE
      3. DAYS360
      4. EDATE
      5. MONTH
      6. NETWORKDAYS
      7. TODAY
      8. WEEKDAY
      9. YEAR
      10. Troubleshooting
        1. #VALUE Using SUM(MONTH
        2. #VALUE FROM NETWORKDAYS
      11. Excel in Practice
    4. 9. Engineering Functions
      1. Engineering Functions Overview
      2. BESSELI
      3. BIN2DEC
      4. COMPLEX
      5. CONVERT
      6. DEC2BIN
      7. DELTA
      8. GESTEP
      9. HEX2BIN
      10. IMCONJUGATE
      11. IMPOWER
      12. IMPRODUCT
      13. IMSUB
      14. IMSUM
      15. OCT2BIN
      16. Troubleshooting
        1. I Need to Convert One Number to Another Form of Measurement.
        2. I Get Errors for Most of the Functions Covered in This Chapter.
      17. Excel in Practice
    5. 10. Financial Functions
      1. Financial Functions Overview
      2. ACCRINT
      3. ACCRINTM
      4. COUPDAYBS
      5. COUPDAYS
      6. COUPNCD
      7. COUPNUM
      8. CUMIPMT
      9. DISC
      10. DURATION
      11. FV
      12. IPMT
      13. MDURATION
      14. NPER
      15. NPV
      16. ODDFPRICE
      17. PMT
      18. PRICE
      19. PV
      20. RATE
      21. RECEIVED
      22. TBILLPRICE
      23. TBILLYIELD
      24. YIELD
      25. YIELDMAT
      26. Troubleshooting
        1. When I follow the instructions for creating a formula using many of these functions I Get a #NAME? error.
      27. Excel in Practice
    6. 11. Information Functions
      1. Information Functions Overview
      2. CELL
      3. COUNTBLANK
      4. ERROR.TYPE
      5. INFO
      6. IS Functions
        1. ISBLANK
        2. ISNUMBER
      7. Troubleshooting
        1. Activating Cells
        2. Activating an Array
        3. ISERROR
      8. Excel in Practice
    7. 12. Logical Functions
      1. Logical Functions Overview
      2. AND
      3. IF
      4. NOT
      5. OR
      6. Troubleshooting
        1. Relative Timeline References
      7. Excel in Practice
    8. 13. Lookup and Reference Functions
      1. Lookup and Reference Functions Overview
      2. CHOOSE
      3. HLOOKUP
      4. INDEX (Array Form)
      5. INDEX (Reference Form)
      6. Using the Lookup Wizard
      7. LOOKUP (Array Form)
      8. LOOKUP (Vector Form)
      9. OFFSET
      10. Auto Update Charts Using the OFFSET Function
      11. TRANSPOSE
      12. VLOOKUP
      13. Troubleshooting
        1. VLOOKUP Matching
      14. Excel in Practice
    9. 14. Math and Trigonometry Functions
      1. Math and Trigonometry Functions Overview
      2. ABS
      3. ACOS
      4. ASIN
      5. COMBIN
      6. COUNTIF
      7. DEGREES
      8. LN
      9. MODE
      10. PERMUT
      11. PI
      12. RAND
      13. ROMAN
      14. SQRT
      15. SUBTOTAL
      16. SUM
      17. SUMIF
      18. TAN
      19. Troubleshooting
        1. Quick Sum
        2. Creating Quick Relative Versus Absolute
      20. Excel in Practice
    10. 15. Text Functions
      1. Text Functions Overview
      2. CONCATENATE
      3. EXACT
      4. FIND
      5. FIXED
      6. LEN
      7. LOWER
      8. MID
      9. PROPER
      10. RIGHT
      11. Add st, nd, rd, and th to the End of Numbers
      12. SUBSTITUTE
      13. TRIM
      14. UPPER
      15. Troubleshooting
        1. Text in Formulas
      16. Excel in Practice
    11. 16. Statistical Functions
      1. Statistical Functions Overview
      2. AVERAGE
      3. BINOMDIST
      4. CHIDIST
      5. CONFIDENCE
      6. CORREL
      7. COUNT
        1. COUNTA
        2. Returning the Last Record Entered
        3. COUNTBLANK
        4. COUNTIF
      8. COVAR
      9. EXPONDIST
      10. FDIST
      11. FORECAST
      12. FREQUENCY
      13. GEOMEAN
      14. GROWTH
      15. INTERCEPT
      16. LARGE
      17. LINEST
      18. LOGEST
      19. MAX
      20. MEDIAN
      21. MIN
      22. MINA
      23. MODE
      24. NORMDIST
      25. NORMINV
      26. PERCENTILE
      27. PERCENTRANK
      28. POISSON
      29. RANK
      30. SLOPE
      31. SMALL
      32. STDEV
      33. STDEVA
      34. TREND
      35. VAR
      36. VARA
      37. Troubleshooting
        1. Do you have a large list where you need to find the most common occurrence of a number?
        2. How do I find the largest value in a range?
      38. Excel in Practice
  8. IV. Using Excel’s Analysis Tools
    1. 17. Setting Up a List or Database in Excel
      1. Using Excel as a Database Program
      2. Building an Effective List
        1. Transposing List Data with the Paste Special Command
        2. Transposing Tables with Formulas (Without Absolute Referencing)
        3. Setting Up Date-Driven Lists
      3. Creating Structured Lists from Data Contained in One Cell
      4. Working with the Data Form
      5. Viewing and Printing the List
        1. Keeping the Field Names from Scrolling
        2. Arranging Multiple Windows
        3. Inserting Data Ranges into a List
        4. Establishing Custom Views
      6. Troubleshooting
        1. Freeze Panes
        2. Unique Records
      7. Excel in Practice
    2. 18. Using Excel’s Data-Management Features
      1. Data Management in Excel
      2. Using Conditional Formatting with Lists
      3. Using Formulas with Conditional Formatting
      4. Using Formulas with Conditional Formatting to Create Timelines
      5. Managing Data Using Text To Speech
      6. Sorting a List
      7. Filtering a List
        1. Managing the List with AutoFilters
          1. Creating a Custom AutoFilter
        2. Using the Advanced Filter
          1. Copying Records to a New Location
          2. Selecting Unique Records
      8. Adding Form Controls to Your Worksheets
        1. Using Controls with Calculation Tables
        2. Using Controls with Charts
        3. Control Characteristics
      9. Troubleshooting
        1. Intersecting Points in Lists or Tables
        2. Complex Formulas
        3. Formulas Are Slowing Down the Workbook
        4. Parsing a List of Names
      10. Excel in Practice
    3. 19. Outlining, Subtotaling, and Auditing Worksheet Data
      1. Organizing and Auditing Your Data
      2. Grouping or Outlining Data
        1. Grouping Data
          1. Grouping Summary Tables
          2. Creating an Auto Outline
          3. Clearing the Outline
        2. Changing the Outline Settings
      3. Consolidating Data
      4. Creating Automatic Subtotals
      5. Validating and Auditing Data Entry
        1. Data Validation
        2. Circling Invalid Data
        3. Auditing Precedents, Dependents, and Errors
      6. Troubleshooting
        1. Displaying Tracer Arrows
        2. Auto Outline Doesn’t Work
      7. Excel in Practice
  9. V. Creating and Modifying Charts
    1. 20. Building Charts with Excel
      1. An Overview of Excel Charts
        1. Chart Basics
        2. Chart Terms
      2. Creating Charts with the Chart Wizard
        1. Selecting the Chart Type
        2. Specifying the Chart Source Data
        3. Choosing the Chart Options
          1. Chart Titles
          2. Axes
          3. Gridlines
          4. Legend
          5. Data Labels
          6. Data Table
        4. Choosing a Chart Location
          1. Embedded Charts
          2. Creating the Chart on a Separate Chart Sheet
      3. Excel Chart Types
        1. Column Charts
        2. Bar Charts
        3. Line Charts
        4. Pie Charts
        5. Doughnut Charts
        6. Scatter Charts
        7. Area Charts
        8. Radar Charts
        9. Surface Charts
        10. Bubble Charts
        11. Stock Charts
        12. Cylinder, Cone, and Pyramid Charts
        13. Custom Charts
          1. Creating a Personalized Custom Chart
      4. Printing Charts
      5. Troubleshooting
        1. Selecting a Chart Element
        2. Changing a Chart Sheet to an Embedded Chart
        3. Eliminating the Legend Border
        4. Saving a Formatted Chart As a Custom Chart
        5. Adding a Trendline
        6. Adding Data Labels
      6. Excel in Practice
    2. 21. Modifying Excel Charts
      1. Options for Improving Your Charts
        1. Moving and Resizing Embedded Charts
        2. Selecting Parts of a Chart for Editing
      2. Changing the Chart Type
      3. Changing a Data Series
        1. Selecting a Data Series or Data Point in a Chart
        2. Removing a Series from a Chart
        3. Adding or Adjusting Source Data
          1. Adding Data Points or Data Series
          2. Changing the Data Source
      4. Adding a Secondary Axis to the Chart
      5. Value Axis Scaling
        1. Changing the Maximum, Minimum, and Tick Mark Values
        2. Resizing the Plot Area
        3. Changing the Origin
      6. Category Axis Scaling
        1. Repositioning the Axes
        2. Changing Tick Marks and Labels
      7. Changing the Series Order
        1. Reversing the Categories
        2. Reversing the Values
      8. Adding a Trendline to a Data Series
        1. Formatting the Trendline
        2. Trendline Options
      9. Troubleshooting
        1. Changing the Maximum Value for the Value Axis
        2. Changing the Chart Type of a Data Series
        3. Adding Data to a Chart
        4. Charting Dramatically Different Values on the Same Chart
        5. Customizing Tick Marks
      10. Excel in Practice
    3. 22. Formatting Charts
      1. An Overview of Formatting Charts
      2. Formatting Lines: Axes, Tick Marks, High/Low Lines, and Error Bars
        1. Formatting the Y-Axis, Secondary Y-Axis, and Z-Axis
        2. Formatting Axis Labels
        3. Adding High/Low Lines
        4. Adding Error Bars
      3. Formatting Text: Data Labels, Titles, Legends, and Text Boxes
        1. Adding and Formatting Data Labels
        2. Adding and Formatting Chart Titles
        3. Formatting the Legend
        4. Inserting and Formatting Text on a Chart
      4. Enhancing Charts with Shapes
      5. Formatting Data Series
        1. Changing the Series Order
        2. Plotting Data on the Secondary Axis
        3. Exploding Pie Slices
        4. Changing the Data Series Angle in Pie or Doughnut Charts
        5. Formatting a Data Point
      6. Changing the Border, Color, or Fill of a Chart Item
        1. Fill Effects
        2. Using Pictures as Backgrounds
      7. Formatting 3D Charts
        1. Formatting the Walls of a 3D Chart
        2. Formatting the Floor of a 3D Chart
        3. Formatting the Data Series of a 3D Chart
        4. Formatting the 3D View
      8. Troubleshooting
        1. Eliminating the Axis While Keeping Axis Labels
        2. Eliminating Borders and Backgrounds
        3. Formatting a Single Data Point
        4. Offsetting the Categories
        5. Eliminating Data Label Backgrounds
        6. Reducing the Number of Units in a Picture
      9. Excel in Practice
    4. 23. Professional Charting Techniques
      1. Formatting Charts for a Professional Look
        1. Key Elements in Professional Formatting
        2. Adding Pictures and Shapes to Charts
          1. Combining Charts, Pictures, and Drawing Objects
          2. Adding a Draft Stamp or Watermark with WordArt
          3. Positioning Charts Over Shapes
      2. Creating Column Depth
        1. Working with the Secondary Axis, Overlap, and Gap Width
        2. Single Stacked Thermometers
      3. Pie Chart Techniques
        1. Spinning the Pie Chart
        2. Organizing Pie Charts to Tell a Story
        3. Advanced Parsing of Pie Charts
      4. Using Fill Effects to Show Variance in 3D Charts
      5. Using Form Controls with Charts
        1. Creating Chart Models with Form Control Combo Boxes
        2. Creating Advanced Chart Models with Combo Boxes
        3. Creating Chart Models with Filters and Subtotals
      6. Stacking Multiple Charts
      7. Creating Cost and Production Curves with Charts for Variance
      8. Linking Chart Text to Worksheet Cells
      9. Charting Hidden Data
      10. Creating Effective Multiple-Combination Charts
        1. Visual Display in Excel
        2. Combining Charts, Worksheets, Text, and Time
        3. Creating a Custom L-Bar Axis
        4. Building Single-Stack Charts
        5. Stacked Charts with Series Lines
        6. Detailed Time Analysis with Annotation
        7. Advanced Bar Chart Formats
        8. Advanced Analytical Chart Formats
        9. Creating Lifetime Profitability/Breakeven Charts
      11. Troubleshooting
        1. Moving Category Labels
        2. Invisible Data Series
        3. Moving a Chart with Objects
        4. Aligning Chart Labels with Gridlines
      12. Excel in Practice
  10. VI. Using Excel in Business
    1. 24. Using PivotTables and PivotCharts
      1. Understanding PivotTables
      2. Using the PivotTable and PivotChart Wizard
        1. Laying Out the PivotTable
        2. Setting PivotTable Options
          1. Naming the PivotTable
          2. Adding Totals
          3. Applying AutoFormats
          4. Displaying Labels
          5. Controlling the Layout
          6. Handling Errors and Empty Cells
          7. Source Data Options
      3. Creating PivotCharts
      4. Modifying PivotTables and PivotCharts
        1. Dragging Fields in a PivotChart
        2. Hiding Field Data
        3. Showing and Hiding Detail
        4. Performing Calculations on a PivotTable
        5. Hiding Columns or Rows
        6. Drilling Down in a Field
        7. Dragging a Field for a Page View
        8. Audit Your Totals
      5. Working with Dates in PivotTables
      6. Creating a PivotTable from Multiple Ranges
      7. Saving and Editing PivotTables in HTML Format
      8. Troubleshooting
        1. Enabling Drilldown
        2. Grouping PivotTable Dates
        3. Viewing the Data Behind PivotTable Summaries
      9. Excel in Practice
        1. Grouping Data in Pivot Tables
        2. Managing Employee Hours and Costs with PivotTables
    2. 25. Using Analysis Tools: Goal Seek, Solver, and Data Tables
      1. Analyzing Your Data with Excel
      2. Using Goal Seek
      3. Using Solver
        1. Using Solver with Gantt Charts
        2. A Multiple-Project Solver Scenario
      4. Creating Amortization Tables to Calculate Mortgage Payments
        1. Creating Active Tables with Spinners
        2. Multiple-Variable Tables
        3. Adding Scrollbars to the Mortgage Table
      5. Using the Analysis ToolPak Add-In
      6. Excel in Practice
    3. 26. Using Excel in Business
      1. Important Tools for Any Business
        1. Sell In Versus Sell Through
        2. Channel Velocity
        3. Cascading Schedules
        4. Reverse Schedules
        5. Summing the Total Velocity
        6. Automating Projected Cash Flows
        7. Transposing Tables with Formulas (Without Absolute Referencing)
        8. Averaging Positive Numbers Only in a Range
        9. Auto Lookup the Last Number in a Column
        10. P&L—Direct Contribution
        11. Financial Ratios
          1. Liquidity Ratios
          2. Activity Ratios
          3. Profitability Ratios
          4. Coverage Ratios
        12. Resource Pools
        13. Building Custom Functions
        14. Producing a Line-Item Milestone Management Chart
        15. Conditional Formats to Show Progress Against Baseline
        16. Ramping Up Production on a Single Line Item
      2. Value Chains
        1. Creating a Market Opportunity Value Chain
        2. Creating a Strategic Risk Factor Value Chain
      3. Value Matrices
        1. Creating a 2D Matrix
        2. Creating a 3D Matrix
      4. Excel in Practice
    4. 27. Customizing Excel to Fit Your Working Style
      1. Why Customize Excel?
      2. Changing the Default Excel Settings
      3. Web Options Button
      4. Changing Workbook Settings
        1. Changing the Color Palette
        2. Hiding Parts of the Workbook
          1. Hiding Sheets
          2. Hiding or Changing the Display of Zero Values
          3. Hiding Cell Entries
      5. Changing the Excel Window Settings
        1. Adding and Removing Scrollbars
        2. Customizing the Status Bar for Quick Analysis
      6. Recent Option Tabs
      7. Modifying Toolbars
        1. Deleting Buttons from Toolbars
        2. Displaying Text on Buttons
        3. Changing the Button Images
        4. Assigning Hyperlinks
        5. Assigning Macros
        6. Building Custom Toolbars
        7. Deleting and Resetting Toolbars and Buttons
      8. Customizing the Excel Menus
        1. Creating a Custom Menu
        2. Turning Personalized Menus On and Off
      9. Troubleshooting
        1. Retrieving Deleted Menus
      10. Excel in Practice
  11. VII. Integrating Excel with Other Applications
    1. 28. Building Presentations with Excel
      1. Using Excel with Other Microsoft Office Programs
      2. Copying Excel Data to a Word Document
        1. Pasting Excel Data as a Word Table
        2. Formatting Excel Data in a Word Document
      3. Copying Excel Data to a PowerPoint Presentation
        1. Using Excel Ranges in a PowerPoint Slide
          1. Pasting Excel Ranges into a PowerPoint Slide
          2. Linking Excel Data to Your PowerPoint Slide
        2. Pasting a PivotTable into PowerPoint
        3. Pasting a Chart Over Images in PowerPoint
        4. Pasting Excel Images and Objects in PowerPoint
        5. Pasting Excel Data in a PowerPoint Datasheet
      4. Copying Word and PowerPoint Data to an Excel Worksheet
        1. Adding Word Text to an Excel Worksheet
          1. Sorting and Filtering Table Data
      5. Combining Word, Excel, and PowerPoint Files with Hyperlinks
        1. Creating a Hyperlink
        2. Using Hyperlinks to Access a Range of Cells
        3. Updating Hyperlinks
        4. Deleting Hyperlinks
      6. Troubleshooting
        1. Updating Links Between Files
        2. Editing an Existing Hyperlink
        3. Fixing Invalid Hyperlinks
      7. Excel in Practice
    2. 29. Using Excel with Access and Other Databases
      1. Using Excel with Database Software
      2. Using Access to Complement Excel
        1. When to Use Access Instead of Excel
        2. Sending Excel Data to Access for Further Analysis
          1. Copying Data Using Copy and Paste
          2. Importing/Exporting Excel Data with the Import Spreadsheet Wizard
          3. Linking Data Using the Link Spreadsheet Wizard
      3. Exporting Excel Data into Other Databases
      4. Retrieving Data from Access and Other Relational Databases
        1. Where Corporate Data Is Found: Relational Databases and ODBC
        2. Querying the Database with the Query Wizard
          1. Choosing the Data Source
          2. Creating an ODBC Data Source Definition
          3. Selecting the Right Information: Choosing Columns
          4. Selecting the Right Information: Filtering Data
          5. Sorting the Data
          6. The Last Step: Saving the Query for Reuse
        3. Using Microsoft Query
          1. When to Use Microsoft Query
          2. Starting Microsoft Query
          3. Choosing Which Database Tables to Use
          4. Creating Joins
          5. Selecting Which Table Columns to Use
          6. Limiting Which Information Is Returned
          7. Adding Counts and Totals
        4. Managing Database Data in Excel
          1. Refreshing Data
          2. Rerunning and Modifying Queries
      5. Importing Data from Text Files
        1. Using the Convert Text to Columns Wizard
      6. Troubleshooting
        1. Copying Visible Cells Only
        2. When Access Can’t Create an Index
      7. Excel in Practice
    3. 30. Retrieving Data from OLAP Servers
      1. What Is OLAP?
      2. Server Versus Client OLAP
      3. Creating an OLAP Data Source Definition
      4. Creating an OLAP PivotTable
      5. Using OLAP PivotTables
      6. Using OLAP PivotCharts
      7. Saving Offline Cubes from Server Cubes
      8. Performing OLAP Analysis on Database Data
        1. Starting the OLAP Cube Wizard
        2. Selecting Cube Measures
        3. Organizing Data into Hierarchies
        4. Saving the Cube
        5. Using the Cube
      9. Excel in Practice
    4. 31. Recording and Editing a Macro
      1. Create Your Own Commands with Macros
        1. What Is a Macro?
        2. Why Create Your Own Commands?
      2. Creating a Macro with the Macro Recorder
        1. What You Should Consider Before Recording
          1. Macro Storage Options
          2. Absolute Versus Relative Recording
        2. Recording a Macro
        3. Where Are Macros Saved?
        4. Opening Workbooks That Contain Macros
      3. Macro Playback
        1. Using the Macro Dialog Box
        2. Assigning a Keyboard Shortcut
        3. Assigning a Macro to a Toolbar or Menu
        4. Creating a New Toolbar or Menu for Macros
        5. Assigning a Macro to a Graphic Object
      4. Editing a Macro
        1. Example: Editing a Sheet-Naming Macro
      5. Deleting Macros, Custom Buttons, and Custom Menu Items
      6. Macros to Help You Work Faster
      7. Troubleshooting
        1. Increasing Macro Playback Speed
        2. Runtime Errors
      8. Excel in Practice
        1. Applying Custom Headers
        2. Creating an AutoFit Column Macro
    5. 32. Creating Interactive Excel Applications with VBA
      1. Why Write Macros Rather Than Record Them?
      2. Introduction to Object-Oriented Programming
        1. Objects
        2. Collections
        3. Methods
        4. Properties
        5. Functions
        6. Putting It All Together
      3. Variables and Constants
        1. What Is a Variable?
        2. What Is a Constant?
      4. Understanding the Visual Basic Editor
      5. Getting Help with Visual Basic
      6. VBA Procedures
        1. Creating a Nonrecorded Procedure
        2. Creating a Function Procedure
          1. Creating a Function to Calculate the Number of Days in a Month
          2. Creating a Percent Change Function
      7. Control Structures
        1. Decision-Making Structures
        2. Loops
      8. Code-Writing Tips
        1. Using the Auto List Members and Auto Quick Info
        2. Writing Easy-to-Read Code
        3. Commenting Code
      9. Debugging
        1. Compiling a Project
        2. Stepping Through Code Using the Step Command and Breakpoints
        3. The Immediate Window
        4. Watching Variables and Expressions
      10. Automatic Execution of VBA Code
      11. Excel in Practice
  12. VIII. Appendix
    1. A. What’s on the CD
      1. What is WOPR?
        1. WOPR Commander
        2. Enveloper
        3. WorkBar
        4. FileNew Popup
        5. FloppyCopy
        6. Lookup Zip+4
        7. Insert Picture
        8. Task Pane Customizer
        9. Image Extractor/Editor
        10. Document Notes
        11. Date and Time Tools
        12. Popup Contacts List
        13. QuickMarks
        14. Show/Hide All
        15. Formatting Toolbar
        16. Module Tools
        17. City2Airport Smart Tags
        18. WOPR Updater!
        19. LITTLE WOPRs LIBRARY
        20. Installing WOPR 2003
        21. Security Considerations
        22. Tech Support

Product information

  • Title: Special Edition Using® Microsoft® Office Excel 2003
  • Author(s): Patrick Blattner
  • Release date: September 2003
  • Publisher(s): Que
  • ISBN: 9780789729538