Master core Excel 2013 tools for building powerful, reliable spreadsheets!   Excel expert Paul McFedries shows how to use Excel 2013’s core features to solve problems and get the answers you need. Using real-world examples, McFedries helps you get the absolute most out of features and improvements ranging from FlashFill to Excel’s newest functions. Along the way, you discover the fastest, best ways to handle essential day-to-day tasks ranging from generating account numbers to projecting the impact of inflation.   Becoming an Excel expert has never been easier! You’ll find crystal-clear instructions; insider insights; even complete step-by-step projects for building timesheets, projecting cash flow, aging receivables, analyzing defects, and more..   •   Quickly create powerful spreadsheets with FlashFill •   Use conditional formatting to instantly reveal anomalies, problems, or opportunities •   Analyze your data with standard tables and PivotTables •   Use complex criteria to filter data in lists •   Understand correlations between data •   Perform sophisticated what-if analyses •   Use regression to track trends and make forecasts •   Build loan, investment, and discount formulas •   Validate data, troubleshoot problems, and build more accurate, trustworthy spreadsheets   About MrExcel Library:  Every book in the MrExcel Library pinpoints a specific set of crucial Excel tasks and presents focused skills and examples for performing them rapidly and effectively. Selected by Bill Jelen, Microsoft Excel MVP and mastermind behind the leading Excel solutions website MrExcel.com, these books will •   Dramatically increase your productivity—saving you 50 hours a year or more •   Present proven, creative strategies for solving real-world problems •   Show you how to get great results, no matter how much data you have •   Help you avoid critical mistakes that even experienced users make   CATEGORY: Spreadsheets COVERS: Microsoft Office Excel 2013  

Table of Contents:
Introduction Part I: Mastering Excel Ranges and Formulas Chapter 1 Getting the Most Out of Ranges Advanced Range-Selection Techniques     Mouse Range-Selection Tricks     Keyboard Range-Selection Tricks     Working with 3D Ranges     Selecting a Range Using Go To     Using the Go To Special Dialog Box Data Entry in a Range Filling a Range     Using the Fill Handle     Flash-Filling a Range Creating a Series Advanced Range Copying     Copying Selected Cell Attributes     Combining Two Ranges Arithmetically     Transposing Rows and Columns Clearing a Range Applying Conditional Formatting to a Range     Creating Highlight Cells Rules     Creating Top/Bottom Rules     Adding Data Bars     Adding Color Scales     Adding Icon Sets From Here Chapter 2 Using Range Names Defining a Range Name     Working with the Name Box     Using the New Name Dialog Box     Changing the Scope to Define Sheet-Level Names     Using Worksheet Text to Define Names     Naming Constants Working with Range Names     Referring to a Range Name     Working with Name AutoComplete     Navigating Using Range Names     Pasting a List of Range Names in a Worksheet     Displaying the Name Manager     Filtering Names     Editing a Range Name’s Coordinates     Adjusting Range Name Coordinates Automatically     Changing a Range Name     Deleting a Range Name     Using Names with the Intersection Operator From Here Chapter 3 Building Basic Formulas Understanding Formula Basics     Formula Limits in Excel 2013     Entering and Editing Formulas     Using Arithmetic Formulas     Using Comparison Formulas     Using Text Formulas     Using Reference Formulas Understanding Operator Precedence     The Order of Precedence     Controlling the Order of Precedence Controlling Worksheet Calculation Copying and Moving Formulas     Understanding Relative Reference Format     Understanding Absolute Reference Format     Copying a Formula Without Adjusting Relative References Displaying Worksheet Formulas     Displaying All Worksheet Formulas     Displaying a Cell’s Formula Using FORMULATEXT() Converting a Formula to a Value Working with Range Names in Formulas     Pasting a Name into a Formula     Applying Names to Formulas     Naming Formulas Working with Links in Formulas     Understanding External References     Updating Links     Changing the Link Source Formatting Numbers, Dates, and Times     Numeric Display Formats     Date and Time Display Formats     Deleting Custom Formats From Here Chapter 4 Creating Advanced Formulas Working with Arrays     Using Array Formulas     Using Array Constants     Functions That Use or Return Arrays Using Iteration and Circular References Consolidating Multisheet Data     Consolidating by Position     Consolidating by Category Applying Data-Validation Rules to Cells Using Dialog Box Controls on a Worksheet     Displaying the Developer Tab     Using the Form Controls     Adding a Control to a Worksheet     Linking a Control to a Cell Value     Understanding the Worksheet Controls From Here Chapter 5 Troubleshooting Formulas Understanding Excel’s Error Values     #DIV/0!     #N/A     #NAME? Case Study: Avoiding #NAME? Errors When Deleting Range Names     #NULL!     #NUM!     #REF!     #VALUE! Fixing Other Formula Errors     Missing or Mismatched Parentheses     Erroneous Formula Results     Fixing Circular References Handling Formula Errors with IFERROR() Using the Formula Error Checker     Choosing an Error Action     Setting Error Checker Options Auditing a Worksheet     Understanding Auditing     Tracing Cell Precedents     Tracing Cell Dependents     Tracing Cell Errors     Removing Tracer Arrows     Evaluating Formulas     Watching Cell Values From Here Part II: Harnessing the Power of Functions Chapter 6 Understanding Functions About Excel’s Functions The Structure of a Function Typing a Function into a Formula Using the Insert Function Feature Loading the Analysis ToolPak From Here Chapter 7 Working with Text Functions Excel’s Text Functions Working with Characters and Codes     The CHAR() Function     The CODE() Function Converting Text     The LOWER() Function     The UPPER() Function     The PROPER() Function     The DECIMAL() Function     The NUMBERVALUE() Function Formatting Text     The DOLLAR() Function     The FIXED() Function     The TEXT() Function     Displaying When a Workbook Was Last Updated Manipulating Text Removing Unwanted Characters from a String     The TRIM() Function     The CLEAN() Function     The REPT() Function: Repeating a Character     Padding a Cell     Building Text Charts Extracting a Substring     The LEFT() Function     The RIGHT() Function     The MID() Function     Converting Text to Sentence Case     A Date-Conversion Formula Case Study: Generating Account Numbers Searching for Substrings     The FIND() and SEARCH() Functions     Extracting a First Name or Last Name     Extracting First Name, Last Name, and Middle Initial     Determining the Column Letter Substituting One Substring for Another     The REPLACE() Function     The SUBSTITUTE() Function     Removing a Character from a String     Removing Two Different Characters from a String Case Study: Generating Account Numbers, Part II     Removing Line Feeds From Here Chapter 8 Working with Logical and Information Functions Adding Intelligence with Logical Functions     Using the IF() Function     Performing Multiple Logical Tests     Combining Logical Functions with Arrays Case Study: Building an Accounts Receivable Aging Worksheet     Calculating a Smarter Due Date     Aging Overdue Invoices Getting Data with Information Functions     The CELL() Function     The ERROR.TYPE() Function     The INFO() Function     The SHEET() and SHEETS() Functions     The IS Functions From Here Chapter 9 Working with Lookup Functions Understanding Lookup Tables The CHOOSE() Function     Determining the Name of the Day of the Week     Determining the Month of the Fiscal Year     Calculating Weighted Questionnaire Results     Integrating CHOOSE() and Worksheet Option Buttons Looking Up Values in Tables     The VLOOKUP() Function     The HLOOKUP() Function     Returning a Customer Discount Rate with a Range Lookup     Returning a Tax Rate with a Range Lookup     Finding Exact Matches     Advanced Lookup Operations From Here Chapter 10 Working with Date and Time Functions How Excel Deals with Dates and Times     Entering Dates and Times     Excel and Two-Digit Years Using Excel’s Date Functions     Returning a Date     Returning Parts of a Date     Calculating the Difference Between Two Dates Using Excel’s Time Functions     Returning a Time     Returning Parts of a Time     Calculating the Difference Between Two Times Case Study: Building an Employee Time Sheet     Entering the Time Sheet Data     Calculating the Daily Hours Worked     Calculating the Weekly Hours Worked     Calculating the Weekly Pay From Here Chapter 11 Working with Math Functions Understanding Excel’s Rounding Functions     The ROUND() Function     The MROUND() Function     The ROUNDDOWN() and ROUNDUP() Functions     The CEILING.MATH() and FLOOR.MATH() Functions     Determining the Fiscal Quarter in Which a Date Falls     Calculating Easter Dates     The EVEN() and ODD() Functions     The INT() and TRUNC() Functions     Using Rounding to Prevent Calculation Errors     Setting Price Points Case Study: Rounding Billable Time Summing Values     The SUM() Function     Calculating Cumulative Totals     Summing Only the Positive or Negative Values in a Range The MOD() Function     A Better Formula for Time Differences     Summing Every nth Row     Determining Whether a Year Is a Leap Year     Creating Ledger Shading Generating Random Numbers     The RAND() Function     The RANDBETWEEN() Function From Here Chapter 12 Working with Statistical Functions Understanding Descriptive Statistics Counting Items with the COUNT() Function Calculating Averages     The AVERAGE() Function     The MEDIAN() Function     The MODE() Function     Calculating the Weighted Mean Calculating Extreme Values     The MAX() and MIN() Functions     The LARGE() and SMALL() Functions     Performing Calculations on the Top k Values     Performing Calculations on the Bottom k Values Calculating Measures of Variation     Calculating the Range     Calculating the Variance     Calculating the Standard Deviation Working with Frequency Distributions     The FREQUENCY() Function     Understanding the Normal Distribution and the NORMDIST() Function     The Shape of the Curve I: The SKEW() Function     The Shape of the Curve II: The KURT() Function Using the Analysis ToolPak Statistical Tools     Using the Descriptive Statistics Tool     Determining the Correlation Between Data     Working with Histograms     Using the Random Number Generation Tool     Working with Rank and Percentile From Here Part III: Building Business Models Chapter 13 Analyzing Data with Tables Converting a Range to a Table Basic Table Operations Sorting a Table     Performing a More Complex Sort     Sorting a Table in Natural Order     Sorting on Part of a Field     Sorting Without Articles Filtering Table Data     Using Filter Lists to Filter a Table     Using Complex Criteria to Filter a Table     Entering Computed Criteria     Copying Filtered Data to a Different Range Referencing Tables in Formulas     Using Table Specifiers     Entering Table Formulas Excel’s Table Functions     About Table Functions     Table Functions That Don’t Require a Criteria Range     Table Functions That Accept Multiple Criteria     Table Functions That Require a Criteria Range Case Study: Applying Statistical Table Functions to a Defects Database   From Here Chapter 14 Analyzing Data with PivotTables What Are PivotTables?     How PivotTables Work     Some PivotTable Terms Building PivotTables     Building a PivotTable from a Table or Range     Building a PivotTable from an External Database     Working with and Customizing a PivotTable Working with PivotTable Subtotals     Hiding PivotTable Grand Totals     Hiding PivotTable Subtotals     Customizing the Subtotal Calculation Changing the Data Field Summary Calculation     Using a Difference Summary Calculation     Using a Percentage Summary Calculation     Using a Running Total Summary Calculation     Using an Index Summary Calculation Creating Custom PivotTable Calculations     Creating a Calculated Field     Creating a Calculated Item Case Study: Using PivotTable Results in a Worksheet Formula   From Here Chapter 15 Using Excel’s Business-Modeling Tools Using What-If Analysis     Setting Up a One-Input Data Table     Adding More Formulas to the Input Table     Setting Up a Two-Input Table     Editing a Data Table Working with Goal Seek     How Does Goal Seek Work?     Running Goal Seek     Optimizing Product Margin     A Note About Goal Seek’s Approximations     Performing a Break-Even Analysis     Solving Algebraic Equations Working with Scenarios     Understanding Scenarios     Setting Up Your Worksheet for Scenarios     Adding a Scenario     Displaying a Scenario     Editing a Scenario     Merging Scenarios     Generating a Summary Report     Deleting a Scenario From Here Chapter 16 Using Regression to Track Trends and Make Forecasts Choosing a Regression Method Using Simple Regression on Linear Data     Analyzing Trends Using Best-Fit Lines     Making Forecasts Case Study: Trend Analysis and Forecasting for a Seasonal Sales Model       About the Forecast Workbook     Calculating a Normal Trend     Calculating the Forecast Trend     Calculating the Seasonal Trend     Computing the Monthly Seasonal Indexes     Calculating the Deseasoned Monthly Values     Calculating the Deseasoned Trend     Calculating the Reseasoned Trend     Calculating the Seasonal Forecast     Working with Quarterly Data Using Simple Regression on Nonlinear Data     Working with an Exponential Trend     Working with a Logarithmic Trend     Working with a Power Trend     Using Polynomial Regression Analysis Using Multiple Regression Analysis From Here Chapter 17 Solving Complex Problems with Solver Some Background on Solver     The Advantages of Solver     When Do You Use Solver? Loading Solver Using Solver Adding Constraints Saving a Solution as a Scenario Setting Other Solver Options     Selecting the Method Solver Uses     Controlling How Solver Works     Working with Solver Models Making Sense of Solver’s Messages Case Study: Solving the Transportation Problem   Displaying Solver’s Reports     The Answer Report     The Sensitivity Report     The Limits Report From Here Part IV: Building Financial Formulas Chapter 18 Building Loan Formulas Understanding the Time Value of Money Calculating the Loan Payment     Loan Payment Analysis     Working with a Balloon Loan     Calculating Interest Costs, Part I     Calculating the Principal and Interest     Calculating Interest Costs, Part 2     Calculating Cumulative Principal and Interest Building a Loan Amortization Schedule     Building a Fixed-Rate Amortization Schedule     Building a Dynamic Amortization Schedule Calculating the Term of the Loan Calculating the Interest Rate Required for a Loan Calculating How Much You Can Borrow Case Study: Working with Mortgages       Building a Variable-Rate Mortgage Amortization Schedule     Allowing for Mortgage Principal Paydowns From Here Chapter 19 Building Investment Formulas Working with Interest Rates     Understanding Compound Interest     Nominal Versus Effective Interest     Converting Between the Nominal Rate and the Effective Rate Calculating the Future Value     The Future Value of a Lump Sum     The Future Value of a Series of Deposits     The Future Value of a Lump Sum Plus Deposits Working Toward an Investment Goal     Calculating the Required Interest Rate     Calculating the Required Number of Periods     Calculating the Required Regular Deposit     Calculating the Required Initial Deposit     Calculating the Future Value with Varying Interest Rates Case Study: Building an Investment Schedule   From Here Chapter 20 Building Discount Formulas Calculating the Present Value     Taking Inflation into Account     Calculating Present Value Using PV()     Income Investing Versus Purchasing a Rental Property     Buying Versus Leasing Discounting Cash Flows     Calculating the Net Present Value     Calculating Net Present Value Using NPV()     Net Present Value with Varying Cash Flows     Net Present Value with Nonperiodic Cash Flows Calculating the Payback Period     Simple Undiscounted Payback Period     Exact Undiscounted Payback Point     Discounted Payback Period Calculating the Internal Rate of Return     Using the IRR() Function     Calculating the Internal Rate of Return for Nonperiodic Cash Flows     Calculating Multiple Internal Rates of Return Case Study: Publishing a Book       Per-Unit Constants     Operating Costs and Sales     Cash Flow     Cash-Flow Analysis From Here     9780789748676   TOC   1/25/2013