Home > Store

Formulas and Functions: Microsoft Excel 2010, Portable Documents: Microsoft Excel 2010

Register your product to gain access to bonus material or receive a coupon.

Formulas and Functions: Microsoft Excel 2010, Portable Documents: Microsoft Excel 2010

eBook (Watermarked)

  • Your Price: $29.43
  • List Price: $36.79
  • About Watermarked eBooks
  • This PDF will be accessible from your Account page after purchase and requires PDF reading software, such as Acrobat® Reader®.

    The eBook requires no passwords or activation to read. We customize your eBook by discreetly watermarking it with your name, making it uniquely yours.

    Watermarked eBook FAQ


  • Copyright 2013
  • Dimensions: 7" x 9-1/8"
  • Edition: 1st
  • eBook (Watermarked)
  • ISBN-10: 0-13-326069-0
  • ISBN-13: 978-0-13-326069-4

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

Sample Content

Table of Contents


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




Case Study: Avoiding #NAME? Errors When Deleting Range Names





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


Submit Errata

More Information

InformIT Promotional Mailings & Special Offers

I would like to receive exclusive offers and hear about products from InformIT and its family of brands. I can unsubscribe at any time.


Pearson Education, Inc., 221 River Street, Hoboken, New Jersey 07030, (Pearson) presents this site to provide information about products and services that can be purchased through this site.

This privacy notice provides an overview of our commitment to privacy and describes how we collect, protect, use and share personal information collected through this site. Please note that other Pearson websites and online products and services have their own separate privacy policies.

Collection and Use of Information

To conduct business and deliver products and services, Pearson collects and uses personal information in several ways in connection with this site, including:

Questions and Inquiries

For inquiries and questions, we collect the inquiry or question, together with name, contact details (email address, phone number and mailing address) and any other additional information voluntarily submitted to us through a Contact Us form or an email. We use this information to address the inquiry and respond to the question.

Online Store

For orders and purchases placed through our online store on this site, we collect order details, name, institution name and address (if applicable), email address, phone number, shipping and billing addresses, credit/debit card information, shipping options and any instructions. We use this information to complete transactions, fulfill orders, communicate with individuals placing orders or visiting the online store, and for related purposes.


Pearson may offer opportunities to provide feedback or participate in surveys, including surveys evaluating Pearson products, services or sites. Participation is voluntary. Pearson collects information requested in the survey questions and uses the information to evaluate, support, maintain and improve products, services or sites, develop new products and services, conduct educational research and for other purposes specified in the survey.

Contests and Drawings

Occasionally, we may sponsor a contest or drawing. Participation is optional. Pearson collects name, contact information and other information specified on the entry form for the contest or drawing to conduct the contest or drawing. Pearson may collect additional personal information from the winners of a contest or drawing in order to award the prize and for tax reporting purposes, as required by law.


If you have elected to receive email newsletters or promotional mailings and special offers but want to unsubscribe, simply email information@informit.com.

Service Announcements

On rare occasions it is necessary to send out a strictly service related announcement. For instance, if our service is temporarily suspended for maintenance we might send users an email. Generally, users may not opt-out of these communications, though they can deactivate their account information. However, these communications are not promotional in nature.

Customer Service

We communicate with users on a regular basis to provide requested services and in regard to issues relating to their account we reply via email or phone in accordance with the users' wishes when a user submits their information through our Contact Us form.

Other Collection and Use of Information

Application and System Logs

Pearson automatically collects log data to help ensure the delivery, availability and security of this site. Log data may include technical information about how a user or visitor connected to this site, such as browser type, type of computer/device, operating system, internet service provider and IP address. We use this information for support purposes and to monitor the health of the site, identify problems, improve service, detect unauthorized access and fraudulent activity, prevent and respond to security incidents and appropriately scale computing resources.

Web Analytics

Pearson may use third party web trend analytical services, including Google Analytics, to collect visitor information, such as IP addresses, browser types, referring pages, pages visited and time spent on a particular site. While these analytical services collect and report information on an anonymous basis, they may use cookies to gather web trend information. The information gathered may enable Pearson (but not the third party web trend services) to link information with application and system log data. Pearson uses this information for system administration and to identify problems, improve service, detect unauthorized access and fraudulent activity, prevent and respond to security incidents, appropriately scale computing resources and otherwise support and deliver this site and its services.

Cookies and Related Technologies

This site uses cookies and similar technologies to personalize content, measure traffic patterns, control security, track use and access of information on this site, and provide interest-based messages and advertising. Users can manage and block the use of cookies through their browser. Disabling or blocking certain cookies may limit the functionality of this site.

Do Not Track

This site currently does not respond to Do Not Track signals.


Pearson uses appropriate physical, administrative and technical security measures to protect personal information from unauthorized access, use and disclosure.


This site is not directed to children under the age of 13.


Pearson may send or direct marketing communications to users, provided that

  • Pearson will not use personal information collected or processed as a K-12 school service provider for the purpose of directed or targeted advertising.
  • Such marketing is consistent with applicable law and Pearson's legal obligations.
  • Pearson will not knowingly direct or send marketing communications to an individual who has expressed a preference not to receive marketing.
  • Where required by applicable law, express or implied consent to marketing exists and has not been withdrawn.

Pearson may provide personal information to a third party service provider on a restricted basis to provide marketing solely on behalf of Pearson or an affiliate or customer for whom Pearson is a service provider. Marketing preferences may be changed at any time.

Correcting/Updating Personal Information

If a user's personally identifiable information changes (such as your postal address or email address), we provide a way to correct or update that user's personal data provided to us. This can be done on the Account page. If a user no longer desires our service and desires to delete his or her account, please contact us at customer-service@informit.com and we will process the deletion of a user's account.


Users can always make an informed choice as to whether they should proceed with certain services offered by InformIT. If you choose to remove yourself from our mailing list(s) simply visit the following page and uncheck any communication you no longer want to receive: www.informit.com/u.aspx.

Sale of Personal Information

Pearson does not rent or sell personal information in exchange for any payment of money.

While Pearson does not sell personal information, as defined in Nevada law, Nevada residents may email a request for no sale of their personal information to NevadaDesignatedRequest@pearson.com.

Supplemental Privacy Statement for California Residents

California residents should read our Supplemental privacy statement for California residents in conjunction with this Privacy Notice. The Supplemental privacy statement for California residents explains Pearson's commitment to comply with California law and applies to personal information of California residents collected in connection with this site and the Services.

Sharing and Disclosure

Pearson may disclose personal information, as follows:

  • As required by law.
  • With the consent of the individual (or their parent, if the individual is a minor)
  • In response to a subpoena, court order or legal process, to the extent permitted or required by law
  • To protect the security and safety of individuals, data, assets and systems, consistent with applicable law
  • In connection the sale, joint venture or other transfer of some or all of its company or assets, subject to the provisions of this Privacy Notice
  • To investigate or address actual or suspected fraud or other illegal activities
  • To exercise its legal rights, including enforcement of the Terms of Use for this site or another contract
  • To affiliated Pearson companies and other companies and organizations who perform work for Pearson and are obligated to protect the privacy of personal information consistent with this Privacy Notice
  • To a school, organization, company or government agency, where Pearson collects or processes the personal information in a school setting or on behalf of such organization, company or government agency.


This web site contains links to other sites. Please be aware that we are not responsible for the privacy practices of such other sites. We encourage our users to be aware when they leave our site and to read the privacy statements of each and every web site that collects Personal Information. This privacy statement applies solely to information collected by this web site.

Requests and Contact

Please contact us about this Privacy Notice or if you have any requests or questions relating to the privacy of your personal information.

Changes to this Privacy Notice

We may revise this Privacy Notice through an updated posting. We will identify the effective date of the revision in the posting. Often, updates are made to provide greater clarity or to comply with changes in regulatory requirements. If the updates involve material changes to the collection, protection, use or disclosure of Personal Information, Pearson will provide notice of the change through a conspicuous notice on this site or other appropriate way. Continued use of the site after the effective date of a posted revision evidences acceptance. Please contact us if you have questions or concerns about the Privacy Notice or any objection to any revisions.

Last Update: November 17, 2020