Microsoft Excel - Advanced Formulas And Functions

Microsoft Excel - Advanced Formulas And Functions

Released September 2013
Publisher(s): Infinite Skills
ISBN: 9781771371483

Read it now on the O’Reilly learning platform with a 10-day free trial.

O’Reilly members get unlimited access to books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.

Start your free trial

Video description

In this Microsoft Excel Advanced Formulas and Functions training course, expert author Guy Vaccaro takes you beyond the basics of Excel, and teaches you how to use the advanced formulas and functions in this spreadsheet program by Microsoft. This course is designed for users that already have a basic working knowledge of Excel. You will begin by reviewing the basic operations, such as sum and count syntax, function processing order, and knowing the troubleshooting tools. You will explore extended formula usage with 3D referencing, naming cells and absolute referencing. Guy proceeds to instruct you on how to look up and reference type functions using VLookup and HLookup. Other features that are covered in this computer based training include; generating random numbers and values, locating data using text functions, and learning to use an array formula to count unique entries. By the completion of this training course, you will be comfortable with using many of the advanced formulas and functions that this powerful spreadsheet software from Microsoft offers. Working files are included, allowing you to follow along with the author throughout the lessons.

Table of contents

  1. Welcome
    1. How Advanced Is Advanced?
    2. Using The Working Files
    3. When Is A Formula A Function?
  2. Getting The Basics Right
    1. Sum And Count Syntax
    2. Accessing Unknown Function Syntax Within Excel
    3. Referencing Entire Rows/Columns
    4. Function Processing Order
    5. Convert Formulas To Values In A Split Second
  3. Extend Your Formula Usage
    1. Naming Cells To Improve Formula Appearance
    2. 3D Referencing
    3. Full Absolute Referencing
    4. Making Only Part Absolute Formula
  4. If And If Type Functions
    1. The If Logical Test
    2. Nested If Statements
    3. Using The And, Or, And Not Functions Within If
    4. CountIf, SumIf, And AverageIf
    5. Multiple Criteria With CountIfs, SumIfs, And AverageIfs
  5. Lookup And Reference Type Functions
    1. Looking Up Information With VLookup
    2. HLookup
    3. Nearest Match With VLookup
    4. Nested Lookup Functions
    5. Choose And Match Functions For Locating Data
    6. Index And Match Functions For Retrieving Data
  6. Mathematical Functions
    1. Varying Rounding Functions
    2. MRound, Ceiling, And Floor For Rounding Also
    3. Int And Mod For Integer And Remainder Values
    4. Generating Random Numbers And Values In Excel
    5. Password Generating Workbooks
    6. Display Different Number Types With Roman And Arabic
  7. Statistics
    1. The Middle Value With Median
    2. Ranking Data Without Sorting
    3. Large And Small For Retrieving Biggest And Smallest Values
    4. A Choice Of Count Functions
  8. Text Functions
    1. Extracting Data With Left, Right And Mid
    2. Locating Data With Find, Search, Exact
    3. Use Trim And Clean To Remove Unwanted Gaps
    4. Combine Data From Different Cells
    5. Changing The Case Of Text Using Proper, Upper, And Lower
    6. Replace And Substitute In Action
    7. Format Number Content Within Strings With Text
  9. Date And Time Functions
    1. Using Today And Now Functions
    2. How Does Excel Deal With Dates And Time
    3. What Day Of The Week Is It?
    4. Difference Between Network Days And Work Day
    5. DatedIf For Time Differences
    6. EDate And EOMonth
    7. DateValue And TimeValue Functions
  10. Information And Referencing Functions
    1. Extracting Information With Cell And Info Functions
    2. Using IsBlank, IsOdd, IsEven, IsText, And IsNumber
    3. IsErr, IsError, IfError, IsNA, And IfNA For Error Checking
    4. The New IsFormula Function And FormulaText
    5. Use Offset To Obtain Data From Related Cells
    6. InDirect And Its Uses
  11. Array Formulas And Functions
    1. What Is An Array Formula?
    2. Use An Array Formula To Count Unique Entries
    3. Flipping Rows Or Columns With Transpose
  12. Conclusion
    1. Advanced Formula Summary
    2. About Guy Vaccaro

Product information

  • Title: Microsoft Excel - Advanced Formulas And Functions
  • Author(s):
  • Release date: September 2013
  • Publisher(s): Infinite Skills
  • ISBN: 9781771371483