- Copyright
- About the Author
- Credits
- Acknowledgments
- Introduction
- I. Getting Started with Excel
- 1. Introducing Excel
- 2. What’s New in Excel 2007?
- A New User Interface
- Larger Worksheets
- New File Formats
- Worksheet Tables
- Styles and Themes
- Better Looking Charts
- Page Layout View
- Enhanced Conditional Formatting
- Consolidated Options
- SmartArt
- Formula AutoComplete
- Collaboration Features
- Compatibility Checker
- Improved Pivot Tables
- New Worksheet Functions
- Other New Features
- 3. Entering and Editing Worksheet Data
- Exploring the Types of Data You Can Use
- Entering Text and Values into Your Worksheets
- Entering Dates and Times into Your Worksheets
- Modifying Cell Contents
- Erasing the contents of a cell
- Replacing the contents of a cell
- Editing the contents of a cell
- Learning some handy data-entry techniques
- Automatically moving the cell pointer after entering data
- Using arrow keys instead of pressing Enter
- Selecting a range of input cells before entering data
- Using Ctrl+Enter to place information into multiple cells simultaneously
- Entering decimal points automatically
- Using AutoFill to enter a series of values
- Using AutoComplete to automate data entry
- Forcing text to appear on a new line within a cell
- Using AutoCorrect for shorthand data entry
- Entering numbers with fractions
- Simplifying data entry by using a form
- Entering the current date or time into a cell
- Applying Number Formatting
- 4. Essential Worksheet Operations
- Learning the Fundamentals of Excel Worksheets
- Controlling the Worksheet View
- Working with Rows and Columns
- 5. Working with Cells and Ranges
- Understanding Cells and Ranges
- Copying or Moving Ranges
- Using Names to Work with Ranges
- Adding Comments to Cells
- 6. Introducing Tables
- What Is a Table?
- Creating a Table
- Changing the Look of a Table
- Working with Tables
- 7. Worksheet Formatting
- 8. Understanding Excel’s Files
- 9. Using and Creating Templates
- 10. Printing Your Work
- Printing with One Click
- Changing Your Page View
- Adjusting Common Page Setup Settings
- Adding a Header or Footer to Your Reports
- Adjusting the Settings in the Print Dialog Box
- Preventing Certain Cells from Being Printed
- Preventing Objects from being Printed
- Creating Custom Views of Your Worksheet
- II. Working with Formulas and Functions
- 11. Introducing Formulas and Functions
- 12. Creating Formulas That Manipulate Text
- A Few Words About Text
- Text Functions
- Determining whether a cell contains text
- Working with character codes
- Determining whether two strings are identical
- Joining two or more cells
- Displaying formatted values as text
- Displaying formatted currency values as text
- Repeating a character or string
- Creating a text histogram
- Padding a number
- Removing excess spaces and nonprinting characters
- Counting characters in a string
- Changing the case of text
- Extracting characters from a string
- Replacing text with other text
- Finding and searching within a string
- Searching and replacing within a string
- Advanced Text Formulas
- Counting specific characters in a cell
- Counting the occurrences of a substring in a cell
- Extracting a filename from a path specification
- Extracting the first word of a string
- Extracting the last word of a string
- Extracting all but the first word of a string
- Extracting first names, middle names, and last names
- Removing titles from names
- Creating an ordinal number
- Counting the number of words in a cell
- 13. Working with Dates and Times
- How Excel Handles Dates and Times
- Date-Related Functions
- Displaying the current date
- Displaying any date
- Generating a series of dates
- Converting a nondate string to a date
- Calculating the number of days between two dates
- Calculating the number of work days between two dates
- Offsetting a date using only work days
- Calculating the number of years between two dates
- Calculating a person’s age
- Determining the day of the year
- Determining the day of the week
- Determining the date of the most recent Sunday
- Determining the first day of the week after a date
- Determining the nth occurrence of a day of the week in a month
- Calculating dates of holidays
- Determining the last day of a month
- Determining whether a year is a leap year
- Determining a date’s quarter
- Time-Related Functions
- Displaying the current time
- Displaying any time
- Calculating the difference between two times
- Summing times that exceed 24 hours
- Converting from military time
- Converting decimal hours, minutes, or seconds to a time
- Adding hours, minutes, or seconds to a time
- Rounding time values
- Working with non-time-of-day values
- 14. Creating Formulas That Count and Sum
- Counting and Summing Worksheet Cells
- Basic Counting Formulas
- Advanced Counting Formulas
- Summing Formulas
- Conditional Sums Using a Single Criterion
- Conditional Sums Using Multiple Criteria
- 15. Creating Formulas That Look Up Values
- Introducing Lookup Formulas
- Functions Relevant to Lookups
- Basic Lookup Formulas
- Specialized Lookup Formulas
- Looking up an exact value
- Looking up a value to the left
- Performing a case-sensitive lookup
- Choosing among multiple lookup tables
- Determining letter grades for test scores
- Calculating a grade-point average
- Performing a two-way lookup
- Performing a two-column lookup
- Determining the cell address of a value within a range
- Looking up a value by using the closest match
- 16. Creating Formulas for Financial Applications
- The Time Value of Money
- Loan Calculations
- Investment Calculations
- Depreciation Calculations
- 17. Introducing Array Formulas
- 18. Performing Magic with Array Formulas
- Working with Single-Cell Array Formulas
- Summing a range that contains errors
- Counting the number of error values in a range
- Summing the n largest values in a range
- Computing an average that excludes zeros
- Determining whether a particular value appears in a range
- Counting the number of differences in two ranges
- Returning the location of the maximum value in a range
- Finding the row of a value’s nth occurrence in a range
- Returning the longest text in a range
- Determining whether a range contains valid values
- Summing the digits of an integer
- Summing rounded values
- Summing every nth value in a range
- Removing non-numeric characters from a string
- Determining the closest value in a range
- Returning the last value in a column
- Returning the last value in a row
- Ranking data with an array formula
- Working with Multicell Array Formulas
- Working with Single-Cell Array Formulas
- III. Creating Charts and Graphics
- 19. Getting Started Making Charts
- 20. Learning Advanced Charting
- Understanding Chart Customization
- Selecting Chart Elements
- User Interface Choices for Modifying Chart Elements
- Modifying the Chart Area
- Modifying the Plot Area
- Working with Chart Titles
- Working with the Legend
- Working with Gridlines
- Modifying the Axes
- Working with Data Series
- Creating Chart Templates
- Learning Some Chart-Making Tricks
- 21. Visualizing Data Using Conditional Formatting
- 22. Enhancing Your Work with Pictures and Drawings
- IV. Using Advanced Excel Features
- 23. Customizing the Quick Access Toolbar
- 24. Using Custom Number Formats
- About Number Formatting
- Creating a Custom Number Format
- Custom Number Format Examples
- 25. Using Data Validation
- 26. Creating and Using Worksheet Outlines
- 27. Linking and Consolidating Worksheets
- 28. Excel and the Internet
- 29. Sharing Data with Other Applications
- 30. Using Excel in a Workgroup
- 31. Protecting Your Work
- 32. Making Your Worksheets Error-Free
- Finding and Correcting Formula Errors
- Mismatched parentheses
- Cells are filled with hash marks
- Blank cells are not blank
- Extra space characters
- Formulas returning an error
- Absolute/relative reference problems
- Operator precedence problems
- Formulas are not calculated
- Actual versus displayed values
- Floating point number errors
- “Phantom link” errors
- Using Excel’s Auditing Tools
- Searching and Replacing
- Spell Checking Your Worksheets
- Using AutoCorrect
- Finding and Correcting Formula Errors
- V. Analyzing Data with Excel
- 33. Using Microsoft Query with External Database Files
- 34. Introducing Pivot Tables
- 35. Analyzing Data with Pivot Tables
- 36. Performing Spreadsheet What-If Analysis
- 37. Analyzing Data Using Goal Seek and Solver
- 38. Analyzing Data with the Analysis ToolPak
- The Analysis ToolPak: An Overview
- Installing the Analysis ToolPak Add-in
- Using the Analysis Tools
- Introducing the Analysis ToolPak Tools
- The Analysis of variance tool
- The Correlation tool
- The Covariance tool
- The Descriptive Statistics tool
- The Exponential Smoothing tool
- The F-Test (two-sample test for variance) tool
- The Fourier Analysis tool
- The Histogram tool
- The Moving Average tool
- The Random Number Generation tool
- The Rank and Percentile tool
- The Regression tool
- The Sampling tool
- The t-Test tool
- The z-Test (Two-Sample Test for Means) tool
- VI. Programming Excel with VBA
- 39. Introducing Visual Basic for Applications
- 40. Creating Custom Worksheet Functions
- 41. Creating UserForms
- 42. Using UserForm Controls in a Worksheet
- 43. Working with Excel Events
- 44. VBA Examples
- 45. Creating Custom Excel Add-Ins
- VII. Appendixes
- A. Worksheet Function Reference
- B. What’s on the CD-ROM
- System Requirements
- Using the CD
- What’s on the CD
- eBook version of Excel 2007 Bible
- Sample files for the Excel 2007 Bible
- Chapter 01
- Chapter 03
- Chapter 05
- Chapter 06
- Chapter 07
- Chapter 11
- Chapter 12
- Chapter 13
- Chapter 14
- Chapter 15
- Chapter 16
- Chapter 18
- Chapter 19
- Chapter 20
- Chapter 21
- Chapter 24
- Chapter 25
- Chapter 26
- Chapter 27
- Chapter 28
- Chapter 33
- Chapter 34
- Chapter 35
- Chapter 36
- Chapter 37
- Chapter 38
- Chapter 39
- Chapter 40
- Chapter 41
- Chapter 42
- Chapter 43
- Chapter 44
- Chapter 45
- Troubleshooting
- Customer Care
- C. Additional Excel Resources
- D. Excel Shortcut Keys
- Wiley Publishing, Inc. End-User License Agreement
Formulas and worksheet functions are essential to manipulating data and obtaining useful information from your Excel workbooks. The chapters in this part present a wide variety of formula examples that use many of Excel’s functions. Two of the chapters are devoted to array formulas. These chapters are intended primarily for advanced users who need to perform calculations that may otherwise be impossible.
-
No Comment
..................Content has been hidden....................
You can't read the all page of ebook, please click here login for view all page.