- 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
This part contains some very useful appendixes. Appendix A provides a complete reference of all the worksheet functions. Appendix B gives you the run-down on all the great sample files on the CD-ROM that accompanies this book. Appendix C contains some great additional Excel resources; discover Excel shortcut keys in Appendix D.
-
No Comment
..................Content has been hidden....................
You can't read the all page of ebook, please click here login for view all page.