## With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

No credit card required

## Book Description

Conquer Microsoft Excel formulas and functions—from the inside out!

You’re beyond the basics, so dive right in and really put Excel formulas and functions to work! This supremely organized reference packs hundreds of timesaving solutions, troubleshooting tips, and workarounds. It’s all muscle and no fluff. Discover how the experts increase their data analysis capabilities using Excel 2003, 2007, or 2010—and challenge yourself to new levels of mastery.

• Customize Excel formulas using 350+ built-in functions

• Create reusable formulas for common calculations

• Learn smarter ways to calculate date and time values

• Systematically search worksheets with lookup and reference functions

• Perform advanced calculations using mathematical, statistical, and financial functions

• Build complex formulas by nesting one function inside of another

• Analyze profit margins and more with new functions in Excel 2010

• Develop your own functions with Visual Basic for Applications (VBA)

• NOTE: The sample Excel files that accompany the book were updated on 12/4/2012. Click the Companion Content link to download the files.

1. Microsoft® Excel® 2010 Formulas & Functions Inside Out
2. A Note Regarding Supplemental Files
3. Introduction
1. Who This Book Is For
2. How This Book Is Organized
3. Features and Conventions Used in This Book
5. Using the Sample Files
6. Acknowledgments
7. Support and Feedback
4. 1. Solving Problems with Functions
1. Introducing the New Functions in Excel 2010
2. Scenario and Goals
3. The New Way to Work with Data, Formulas, and Functions
4. Entering Test Data Fast
5. Converting Formula Results into Fixed Values
6. Formatting Numeric Values
7. Calculating Profit Margin
8. Formatting Data as a Table
9. Calculating Profit Margin as a Percentage
10. Applying Conditional Formatting
11. Creating Meaningful Charts
12. Working with PivotTables
13. Using PivotCharts: Graphical Pivot
5. 2. Using Functions and PowerPivot
1. Using Date and Time Functions
2. The Excel Date System
1. Date and Time Number Formats
2. Leap Years
3. Analysis Functions
4. Date and Time Functions in Practice
3. Using Text and Data Functions
4. Using Logical Functions
5. Using Search and Reference Functions
6. Using Information Functions
7. Using Statistical Functions
1. Overview
2. Statistical Functions in Practice
8. Using Database Functions
1. Dynamic Database Names
2. Database Functions in Practice
9. Using Cube Functions
10. Using Financial Math Functions
11. Using Math and Trigonometry Functions
12. PowerPivot
6. 3. From Numbers to Formulas
1. Entering Formulas
2. Operators
1. Arithmetic Operators
2. Comparison Operators
3. The & Text Operator
4. Reference Operators
3. References in Formulas
4. Array Formulas
5. Tips and Tricks
1. Turning the Formula View On and Off
2. Entering Formulas in Several Cells
3. Entering Formulas in Several Worksheets
4. Selecting Formula Cells
5. Determining Which Cells Are Referenced in a Formula
6. Copying and Moving Formulas
7. Moving Formula Cells
8. Copying Formula Cells
9. Converting Formula Results into Fixed Values
10. Converting Existing Values
11. Protecting Formulas
12. Hiding Formulas
13. Specifying the Formula Calculation Type
14. Analyzing Formulas
15. Troubleshooting: Example 1
16. Troubleshooting: Example 2
7. 4. Formulas and Functions
1. What Is a Worksheet Function?
1. The Syntax of a Function
2. Arguments as Arithmetic Data in a Function
3. Options for Passing Arguments
4. Including Calculations in a Function
2. Entering Functions
3. Tips and Tricks
8. 5. Functions in Special Operations
1. Functions in Names
1. Querying Current Information
2. Payment Targets as “Text Modules”
3. Dynamic Range Names
2. Functions for Conditional Formatting
1. Highlighting Weekends in Color
2. Using the MATCH() Function
3. Highlighting Identical Values
4. Finding the Differences Between Tables on Different Worksheets
5. List Print Layout
6. Emphasizing the Top Three Elements
7. Highlighting Cells Containing Spaces
8. Navigating in Tables with Reference Lines
9. Formatting Data Groups
10. Formatting Subtotal Results
11. Tips for Conditional Formatting in Excel 2003
12. Tips and Troubleshooting for Conditional Formatting
3. Functions for Validation
9. 6. Custom Functions
1. Creating a Custom Function
2. The AreaCircle() Function
4. The AreaSect() Function
5. The DigitSum() Function
6. The AreaCircle1() Function
7. The Functions in the Function Wizard
10. 7. Date and Time Functions
11. 8. Text and Data Functions
12. 9. Logical Functions
13. 10. Lookup and Reference Functions
14. 11. Information Functions
15. 12. Statistical Functions
16. 13. Database Functions
17. 14. Cube Functions
18. 15. Financial Functions
19. 16. Mathematical and Trigonometry Functions
20. 17. Engineering Functions
1. How Engineering Functions Are Organized
2. Conversion Functions
1. Number Systems
2. Binary System
3. BIN2DEC()
4. BIN2HEX()
5. BIN2OCT()
6. DEC2BIN()
7. DEC2HEX()
8. DEC2OCT()
9. HEX2BIN()
10. HEX2DEC()
11. HEX2OCT()
12. OCT2BIN()
13. OCT2DEC()
14. OCT2HEX()
15. CONVERT()
3. Functions for Complex Numbers
4. Functions for Higher Mathematics
5. Saltus Functions
21. A. Excel Functions (in Alphabetical Order)
22. B. Excel Functions (by Category)
23. C. What’s New in Excel 2007 and Excel 2010
1. New in Excel 2007
2. New in Excel 2010