You are previewing Excel 2000 in a Nutshell.
O'Reilly logo
Excel 2000 in a Nutshell

Book Description

By virtue of the estimated 20-million-strong installed base of Microsoft Office users, Excel has become the de facto standard for spreadsheet applications. A powerful application needs powerful documentation, and Excel 2000 in a Nutshell, a detailed reference to every one of Excel's menu options and functions, is the key to unlocking Excel's many capabilities. The book's feature-based format means users can find what they're looking for quickly without having to spend extra time searching the index. Written for intermediate and advanced users, this book is divided into three main sections. The first section introduces Excel basics, such as creating and formatting worksheets and formulas, and surveys the new features of Excel 2000. It also includes a chapter on the anatomy of Excel that dissects everything from cells to toolbars. The second section details each Excel Menu option, from the Chart Menu to the File Menu to the Tools Menu. This section is the place to find handy tips for accessing the full power of Excel 2000's many options. The third section deals with Excel Functions, organized in an easy and accessible grouping by categories such as Financial, Date and Time, Statistical, and Database Functions. Topics in this section include:

  • How to use the NetWorkedDays function, which calculates workdays, to determine accrued vacation time

  • Performing financial calculations to determine depreciation of an asset

  • The many functions included in the Analysis ToolPak that comes with Excel 2000

Excel 2000 in a Nutshell is a one-stop reference to Excel for both professional and home users. In typical Nutshell fashion, information is organized for quick and easy access, providing readers with everything they need to know about the premier spreadsheet application.

Table of Contents

  1. Excel 2000 in a Nutshell
    1. Preface
      1. Organization of This Book
        1. Part I
        2. Part II
        3. Part III
        4. Part IV
      2. Conventions Used in This Book
      3. How to Contact Us
      4. Microsoft Office 2000 Service Releases
      5. Acknowledgments
    2. I. Excel 2000 Overview
      1. 1. Excel Basics
        1. Installing Excel 2000
        2. Opening Excel
        3. Excel Menus and Shortcuts
          1. File
            1. File Task List
            2. Keyboard Shortcuts
          2. Edit
            1. Edit Task List
            2. Keyboard Shortcuts
          3. View
            1. View Task List
          4. Insert
            1. Insert Task List
            2. Keyboard Shortcuts
          5. Format
            1. FormatTask List
            2. Keyboard Shortcuts
          6. Tools
            1. Tools Task List
            2. Keyboard Shortcuts
          7. Data
            1. Data Task List
            2. Keyboard Shortcuts
          8. Chart
            1. Chart Task List
          9. Window
            1. Window Task List
            2. Keyboard Shortcuts
          10. Help
            1. Help Task List
            2. Keyboard Shortcuts
      2. 2. Excel Anatomy
        1. The Excel Window
          1. Working with Multiple Workbooks
            1. Arranging multiple workbooks
            2. Hiding the current workbook
            3. Splitting the active workbook
          2. Getting Help with Excel
            1. Help → Microsoft Excel Help
            2. Help → Hide the Office Assistant
            3. Help → What’s This
            4. Help → Office on the Web
            5. Help → Lotus 1-2-3 Help
            6. Help → Detect and Repair
            7. Help → About Microsoft Excel
        2. The External Side of Excel
          1. Understanding Templates
            1. Changing default workbook and worksheet templates
        3. Using Styles
          1. Default Excel Styles
          2. Creating a New Style
          3. Using Styles from Other Workbooks
        4. Customizing in Excel
          1. Setting Customization Options
          2. Creating and Removing Toolbars
          3. Customizing Toolbar and Menu Commands
    3. II. Menu Reference
      1. 3. File
        1. Microsoft Excel Files
          1. Web Page
          2. Text File
          3. Lotus 1-2-3 Files
          4. Quattro-Pro Files
          5. Microsoft Works Files
          6. dBase Files
            1. File → New
            2. File → Open
            3. File → Close
            4. File → Save
            5. File → Save As
            6. File → Save As Web Page
            7. File → Save Workspace
            8. File → Web Page Preview
            9. File → Page Setup
            10. File → Print Area
            11. File → Print Preview
            12. File → Print
            13. File → Send To
            14. File → Properties
            15. File → 1-4
            16. File → Exit
      2. 4. Edit
        1. Windows Clipboard Versus Office Clipboard
          1. Edit → Undo
          2. Edit → Redo
          3. Edit → Cut
          4. Edit → Copy
          5. Edit → Paste
          6. Edit → Paste Special
          7. Edit → Paste as Hyperlink
          8. Edit → Fill
          9. Edit → Clear
          10. Edit → Delete
          11. Edit → Delete Sheet
          12. Edit → Move or Copy Sheet
          13. Edit → Find
          14. Edit → Replace
          15. Edit → Go To
          16. Edit → Links
          17. Edit → Object
      3. 5. View
        1. View → Normal
        2. View → Page Break Preview
        3. View → Toolbars
        4. View → Formula Bar
        5. View → Status Bar
        6. View → Header and Footer
        7. View → Comments
        8. View → Custom View
        9. View → Report Manager
        10. View → Full Screen
        11. View → Zoom
      4. 6. Insert
        1. Insert → Cells
          1. Insert → Cells
        2. Insert → Rows
          1. Insert → Rows
        3. Insert → Columns
          1. Insert → Columns
        4. Insert → Worksheet
          1. Insert → Worksheet
        5. Insert → Chart
          1. Insert → Chart
        6. Insert → Page Break
          1. Insert → Page Break
        7. Insert → Function
          1. Insert → Function
        8. Insert → Name
          1. Insert → Name
        9. Insert → Comment
          1. Insert → Comment
        10. Insert → Picture
          1. Insert → Picture
        11. Insert → Object
          1. Insert → Object
        12. Insert → Hyperlink
          1. Insert → Hyperlink
        13. Some Hyperlink Tips
      5. 7. Format
        1. Format → Cells
        2. Format → Row
        3. Format → Column
        4. Format → Sheet
        5. Format → AutoFormat
        6. Format → Conditional Formatting
        7. Format → Style
      6. 8. Tools
        1. Tools → Spelling
        2. Tools → AutoCorrect
        3. Tools → AutoSave
        4. Tools → Share Workbook
        5. Tools → Track Changes
        6. Tools → Merge Workbooks
        7. Tools → Protection
        8. Tools → Online Collaboration
        9. Tools → Goal Seek
        10. Tools → Scenarios
        11. Tools → Auditing
        12. Tools → Solver
        13. Tools → Macro
        14. Tools → Add-Ins
        15. Tools → Customize
        16. Tools → Options
        17. Tools → Wizard
        18. Tools → Data Analysis
        19. Tools → Update Add-In Links
      7. 9. Data
        1. Data → Sort
        2. Data → Filter
        3. Data → Form
        4. Data → Subtotals
        5. Data → Validation
        6. Data → Table
        7. Data → Text to Columns
        8. Data → Template Wizard
        9. Data → Consolidate
        10. Data → Group and Outline
        11. Data → PivotTable and PivotChart Report
        12. Data → Get External Data
      8. 10. Chart
        1. Column Charts
          1. Bar Charts
          2. Line Charts
          3. Pie Charts
          4. XY (Scatter) Charts
          5. Area Charts
          6. Doughnut Charts
          7. Radar Charts
          8. Surface Charts
          9. Bubble Charts
          10. Stock Charts
          11. Cylinder, Cone, and Pyramid Charts
            1. Chart → Chart Type
            2. Chart → Source Data
            3. Chart → Chart Options
            4. Chart → Location
            5. Chart → Add Data
            6. Chart → 3-D View
            7. Chart → Add Trendline
    4. III. Function Reference
      1. 11. Working with Functions
        1. Function or Formula?
        2. Function Categories
          1. Analysis ToolPak Functions
        3. Function Arguments
        4. Inserting a Function
      2. 12. Financial Functions
        1. Understanding the Financial Jargon
        2. The Financial Functions
          1. Recurring Arguments
            1. ACCRINT
            2. ACCRINTM
            3. AMORDEGRC
            4. AMORLINC
            5. COUPDAYBS
            6. COUPDAYS
            7. COUPDAYSNC
            8. COUPNCD
            9. COUPNUM
            10. COUPPCD
            11. CUMIPMT
            12. CUMPRINC
            13. DB
            14. DDB
            15. DISC
            16. DOLLARDE
            17. DOLLARFR
            18. DURATION
            19. EFFECT
            20. FV
            21. FVSCHEDULE
            22. INTRATE
            23. IPMT
            24. IRR
            25. ISPMT
            26. MDURATION
            27. MIRR
            28. NOMINAL
            29. NPER
            30. NPV
            31. ODDFPRICE
            32. ODDFYIELD
            33. ODDLPRICE
            34. ODDLYIELD
            35. PMT
            36. PPMT
            37. PRICE
            38. PRICEDISC
            39. PRICEMAT
            40. PV
            41. RATE
            42. SLN
            43. SYD
            44. TBILLEQ
            45. TBILLPRICE
            46. TBILLYIELD
            47. VDB
            48. XIRR
            49. XNPV
            50. YIELD
            51. YIELDDISC
            52. YIELDMAT
      3. 13. Date and Time Functions
        1. How Excel Stores Dates and Times
          1. What About Two-Digit Years?
          2. Date and Time Formatting
          3. The Date and Time Functions
            1. Specifying a Date or Time Value for a Function
          4. Recurring Arguments
            1. DATE
            2. DATEDIF
            3. DATEVALUE
            4. DAY
            5. DAYS360
            6. HOUR
            7. MINUTE
            8. MONTH
            9. NETWORKDAYS
            10. NOW
            11. SECOND
            12. TIME
            13. TIMEVALUE
            14. TODAY
            15. WEEKDAY
            16. WEEKNUM
            17. WORKDAY
            18. YEAR
            19. YEARFRAC
      4. 14. Mathematics and Trigonometry Functions
        1. Understanding Math Lingo
          1. The Math and Trigonometric Functions
            1. ABS
            2. ACOS
            3. ACOSH
            4. ASIN
            5. ASINH
            6. ATAN
            7. ATAN2
            8. ATANH
            9. CEILING
            10. COMBIN
            11. COS
            12. COSH
            13. DEGREES
            14. EVEN
            15. EXP
            16. FACT
            17. FLOOR
            18. GCD
            19. INT
            20. LCM
            21. LN
            22. LOG
            23. LOG10
            24. MDETERM
            25. MINVERSE
            26. MMULT
            27. MOD
            28. MROUND
            29. MULTINOMIAL
            30. ODD
            31. PI
            32. POWER
            33. PRODUCT
            34. QUOTIENT
            35. RADIANS
            36. RAND
            37. RANDBETWEEN
            38. Roman
            39. ROUND
            40. ROUNDDOWN
            41. ROUNDUP
            42. SERIESSUM
            43. SIGN
            44. SIN
            45. SINH
            46. SQRT
            47. SQRTPI
            48. SUBTOTAL
            49. SUM
            50. SUMIF
            51. SUMPRODUCT
            52. SUMSQ
            53. SUMX2MY2
            54. SUMX2PY2
            55. SUMXMY2
            56. TAN
            57. TANH
            58. TRUNC
      5. 15. Statistical Functions
        1. Common Statistical Terminology
        2. The Statistical Functions
          1. Recurring Arguments
            1. AVEDEV
            2. AVERAGE
            3. AVERAGEA
            4. BETADIST
            5. BETAINV
            6. BINOMDIST
            7. CHIDIST
            8. CHIINV
            9. CHITEST
            10. CONFIDENCE
            11. CORREL
            12. COUNT
            13. COUNTA
            14. COUNTBLANK
            15. COUNTIF
            16. COVAR
            17. CRITBINOM
            18. DEVSQ
            19. EXPONDIST
            20. FDIST
            21. FINV
            22. FISHER
            23. FINSHERINV
            24. FORECAST
            25. FREQUENCY
            26. FTEST
            27. GAMMADIST
            28. GAMMAINV
            29. GAMMALN
            30. GEOMEAN
            31. GROWTH
            32. HARMEAN
            33. HYPGEOMDIST
            34. INTERCEPT
            35. KURT
            36. LARGE
            37. LINEST
            38. LOGEST
            39. LOGINV
            40. LOGNORMDIST
            41. MAX
            42. MAXA
            43. MEDIAN
            44. MIN
            45. MINA
            46. MODE
            47. NEGBINOMDIST
            48. NORMDIST
            49. NORMINV
            50. NORMSDIST
            51. NORMSINV
            52. PEARSON
            53. PERCENTILE
            54. PERCENTRANK
            55. PERMUT
            56. POISSON
            57. PROB
            58. QUARTILE
            59. RANK
            60. RSQ
            61. SKEW
            62. SLOPE
            63. SMALL
            64. STANDARDIZE
            65. STDEV
            66. STDEVA
            67. STDEVP
            68. STDEVPA
            69. STEYX
            70. TDIST
            71. TINV
            72. TREND
            73. TRIMMEAN
            74. TTEST
            75. VAR
            76. VARA
            77. VARP
            78. VARPA
            79. WEIBULL
            80. ZTEST
      6. 16. Lookup and Reference Functions
        1. The Lookup and Reference Functions
          1. Recurring Arguments
            1. ADDRESS
            2. AREAS
            3. CHOOSE
            4. COLUMN
            5. COLUMNS
            6. GETPIVOTDATA
            7. HLOOKUP
            8. HYPERLINK
            9. INDEX
            10. INDIRECT
            11. LOOKUP
            12. MATCH
            13. OFFSET
            14. ROW
            15. ROWS
            16. TRANSPOSE
            17. VLOOKUP
      7. 17. Database Functions
        1. The Database Functions
          1. Recurring Arguments
            1. DAVERAGE
            2. DCOUNT
            3. DCOUNTA
            4. DGET
            5. DMAX
            6. DMIN
            7. DPRODUCT
            8. DSTDEV
            9. DSTDEVP
            10. DSUM
            11. DVAR
            12. DVARP
      8. 18. Text Functions
        1. The Text Functions
          1. Recurring Arguments
            1. CHAR
            2. CLEAN
            3. CODE
            4. CONCATENATE
            5. DOLLAR
            6. EXACT
            7. FIND
            8. FIXED
            9. LEFT
            10. LEN
            11. LOWER
            12. MID
            13. PROPER
            14. REPLACE
            15. REPT
            16. RIGHT
            17. SEARCH
            18. SUBSTITUTE
            19. T
            20. TEXT
            21. TRIM
            22. UPPER
            23. VALUE
      9. 19. Logical Functions
        1. The Logical Functions
          1. AND
          2. FALSE
          3. IF
          4. NOT
          5. OR
          6. TRUE
      10. 20. Information Functions
        1. The Information Functions
          1. Recurring Arguments
            1. CELL
            2. ERROR.TYPE
            3. INFO
            4. ISBLANK
            5. ISERR
            6. ISERROR
            7. ISEVEN
            8. ISLOGICAL
            9. ISNA
            10. ISNONTEXT
            11. ISNUMBER
            12. ISODD
            13. ISREF
            14. ISTEXT
            15. N
            16. NA
            17. TYPE
    5. IV. Appendices
      1. A. Keyboard Shortcuts
      2. B. Tip Reference
    6. Index
    7. Colophon