Librería Portfolio

Leverage the full power of Excel formulas
Excel 2016 Formulas is fully updated to cover all of the tips, tricks, and techniques you need to maximize the power of Excel 2016 through the use of formulas. This comprehensive book explains how to create financial formulas, release the power of array formulas, develop custom worksheet functions with VBA, debug formulas, and much more. Whether you´re a beginner, a power user, or somewhere in between this is your essential go-to for the latest on Excel formulas.

When conducting simple math or building highly complicated spreadsheets that require formulas up to the task, leveraging the right formula can heighten the accuracy and efficiency of your work, and can improve the speed with which you compile and analyze data. Understanding which formulas to use and knowing how to create a formula when you need to are essential.

Access tips, tricks, and techniques that have been fully updated to reflect the latest capabilities of Microsoft Excel
Create and use formulas that have the power to transform your Excel experience
Leverage supplemental material online, including sample files, templates, and worksheets from the book



Table of Contents

Introduction xxvii
Part I: Understanding Formula Basics

Chapter 1: The Excel User Interface in a Nutshell 3

The Workings of Workbooks 3

Worksheets 4

Chart sheets 5

Macro sheets and dialog sheets 5

The Excel User Interface 5

The Ribbon 6

Backstage View 7

Shortcut menus and the mini toolbar 7

Dialog boxes 7

Customizing the UI 8

Task panes 9

Customizing onscreen display 9

Numeric formatting 9

Stylistic formatting 9

Protection Options 10

Securing access to the entire workbook 10

Limiting access to specific worksheet ranges 13

Protecting the workbook structure 16

Chapter 2: Basic Facts About Formulas 19

Entering and Editing Formulas 19

Formula elements 20

Entering a formula 20

Pasting names 22

Spaces and line breaks 22

Formula limits 23

Sample formulas 23

Editing formulas 24

Using Operators in Formulas 25

Reference operators 25

Sample formulas that use operators 26

Operator precedence 27

Nested parentheses 29

Calculating Formulas 30

Cell and Range References 30

Creating an absolute or a mixed reference 31

Referencing other sheets or workbooks 33

Copying or Moving Formulas 35

Making an Exact Copy of a Formula 36

Converting Formulas to Values 37

Hiding Formulas 39

Errors in Formulas 40

Dealing with Circular References 41

Goal Seeking 42

A goal seeking example 42

More about goal seeking 43

Chapter 3: Working with Names 45

What's in a Name? 45

A Name's Scope 46

Referencing names 47

Referencing names from another workbook 48

Conflicting names 48

The Name Manager 48

Creating names 49

Editing names 50

Deleting names 50

Shortcuts for Creating Cell and Range Names 50

The New Name dialog box 51

Creating names using the Name box 52

Creating names from text in cells 52

Naming entire rows and columns 54

Names created by Excel 55

Creating Multisheet Names 55

Working with Range and Cell Names 57

Creating a list of names 58

Using names in formulas 59

Using the intersection operators with names 59

Using the range operator with names 61

Referencing a single cell in a multicell named range 61

Applying names to existing formulas 62

Applying names automatically when creating a formula 63

Unapplying names 63

Names with errors 64

Viewing named ranges 64

Using names in charts 64

How Excel Maintains Cell and Range Names 65

Inserting a row or column 65

Deleting a row or a column 65

Cutting and pasting 65

Potential Problems with Names 66

Name problems when copying sheets 66

Name problems when deleting sheets 66

The Secret to Understanding Names 68

Naming constants 68

Naming text constants 69

Using worksheet functions in named formulas 70

Using cell and range references in named formulas 71

Using named formulas with relative references 72

Advanced Techniques That Use Names 75

Using the INDIRECT function with a named range 75

Using arrays in named formulas 77

Creating a dynamic named formula 78

Using an XLM macro in a named formula 80

Part II: Leveraging Excel Functions

Chapter 4: Introducing Worksheet Functions 85

What Is a Function? 85

Simplify your formulas 86

Perform otherwise impossible calculations 86

Speed up editing tasks 86

Provide decision-making capability 87

More about functions 87

Function Argument Types 88

Names as arguments 89

Full-column or full-row as arguments 89

Literal values as arguments 90

Expressions as arguments 90

Other functions as arguments 91

Arrays as arguments 91

Ways to Enter a Function into a Formula 91

Entering a function manually 91

Using the Function Library commands 93

Using the Insert Function dialog box 94

More tips for entering functions 96

Chapter 5: Manipulating Text 99

A Few Words About Text 99

How many characters in a cell? 99

Numbers as text 99

Text Functions 101

Determining whether a cell contains text 101

Working with character codes 102

Determining whether two strings are identical 105

Joining two or more cells 105

Displaying formatted values as text 106

Displaying formatted currency values as text 108

Removing excess spaces and nonprinting characters 108

Counting characters in a string 109

Repeating a character or string 109

Creating a text histogram 110

Padding a number 111

Changing the case of text 112

Extracting characters from a string 113

Replacing text with other text 113

Finding and searching within a string 114

Searching and replacing within a string 115

Advanced Text Formulas 115

Counting specific characters in a cell 116

Counting the occurrences of a substring in a cell 116

Removing trailing minus signs 116

Expressing a number as an ordinal 117

Determining a column letter for a column number 118

Extracting a filename from a path specification 118

Extracting the first word of a string 119

Extracting the last word of a string 119

Extracting all but the first word of a string 120

Extracting first names, middle names, and last names 120

Removing titles from names 122

Counting the number of words in a cell 122

Chapter 6: Working with Dates and Times 125

How Excel Handles Dates and Times 125

Understanding date serial numbers 126

Entering dates 127

Understanding time serial numbers 129

Entering times 130

Formatting dates and times 131

Problems with dates 133

Date-Related Functions 134

Displaying the current date 135

Displaying any date with a function 136

Generating a series of dates 137

Converting a nondate string to a date 138

Calculating the number of days between two dates 139

Cal