Excel Functions and Formulas Pocketbook

Nonfiction, Computers, Application Software, Utilities, Spreadsheets, Financial Applications

• Author: Ali Akbar, Zico Pratama Putra
• ISBN: 1230001791419
• Publisher: Kanzul Ilmi
• Publication: August 9, 2017
• Language: English

Geared toward the intermediate to advanced Excel 2016 user, this pocketbook provides explanations and context for many powerful Excel 2016 spreadsheet formulas and functions. Step-by-step instructions for many formula/function-related features such as using range names, and Excel's troubleshooting features. Focus on clarity, accuracy, and the user's perspective, this guide will be a valuable resource to improve your proficiency in using Microsoft Excel 2016. This guide is suitable as a training handout, or simply an easy to use reference guide.

Topics include:

1. Controlling Order of Precedence
2. Conditionally Summing/Counting Data (SUMIF, COUNTIF)
3. Being Precise (Rounding functions)
4. Improving Clarity with Range Names: Creating Names, Limiting Scope, Defining a Constant or Formula for a Name, Managing Names, Indirectly Referring to a Named Range (INDIRECT)
5. New Functions for Office 365, Mobile & Online (CONCAT, TEXTJOIN, IFS, MAXIFS/MINIFS, SWITCH)
6. Merging Text and Numbers (CONCATENATE, TEXT, LEFT, RIGHT, MID, LEN, FIND, VALUE, TRIM, SUBSTITUTE)
7. Changing Results: IF, AND, OR, NOT
8. Table Lookups (VLOOKUP, HLOOKUP, INDEX, MATCH)
9. Error Recovery (ISNA, ISERROR)
10. Array Formulas (Single Cell & Multi-Cell Arrays)
11. Dates and Times (DATE, YEAR, MONTH, DAY, TODAY, WEEKDAY, NETWORKDAYS, EDATE)
12. Using a Formula for Data Validation
13. Troubleshooting: Types of Errors, Automatic Error Checking, Using the Error Checker, Showing/Hiding Formulas, Evaluating Nested Formulas, Selecting Related Cells, Displaying Cell Relationships, Removing Relationship Arrows. 

Examples: 

• Summing Selected Data; Being Precise; Merging Text & Numbers; Table Lookups; Table Lookups with IF and ISERROR; Dates & Times; Single- and Multi-Cell Arrays.
• Improving Clarity with Range Names: Creating Names, Limiting Scope, Defining a Constant or Formula for a Name, Managing Names, Indirectly Referring to a Named Range (INDIRECT) 
• New Functions for Office 365, Mobile & Online (CONCAT, TEXTJOIN, IFS, MAXIFS/MINIFS, SWITCH) 
• Merging Text and Numbers (CONCATENATE, TEXT, LEFT, RIGHT, MID, LEN, FIND, VALUE, TRIM, SUBSTITUTE) 
• Changing Results: IF, AND, OR, NOT 
• Table Lookups (VLOOKUP, HLOOKUP, INDEX, MATCH) 
• Error Recovery (ISNA, ISERROR) 
• Array Formulas (Single Cell & Multi-Cell Arrays) 
• Dates and Times (DATE, YEAR, MONTH, DAY, TODAY, WEEKDAY, NETWORKDAYS, EDATE) 
• Using a Formula for Data Validation 
• Troubleshooting: Types of Errors, Automatic Error Checking, Using the Error Checker, Showing/Hiding Formulas, Evaluating Nested Formulas, Selecting Related Cells, Displaying Cell Relationships, Removing Relationship Arrows.