Advanced Excel Course: Operations, Functions, and Analysis Tools - SCORM

Advanced Excel Course: Operations, Functions, and Analysis Tools

This course is taught in online mode and consists of 4 units. The duration of the course is 30 hours that is distributes between content and collaboration tools. Upon completion, the student will receive a certificate of completion.

Introduction

This advanced course is designed for users who have mastered the basics of Excel and want to delve deeper into the use of complex formulas, functions, and references for data analysis and task automation.

Throughout the course, participants will learn to combine mathematical, statistical, logical, text, and search functions , applying best practices and advanced techniques that optimize accuracy, productivity, and decision-making in professional environments.

Training objectives

At the end of this course, the participant will be able to:

• Perform basic and advanced calculations and operations in Excel, correctly applying relative, absolute, and mixed references between cells, sheets, and workbooks.
  
• Use mathematical and statistical functions to solve numerical problems, analyze data, and obtain accurate results using formulas such as SUMIF, AVERAGEIF, MAXIFS, or SUMPRODUCT.
  
• Manage and analyze temporal information using date and time functions, applying operations between dates, calculating working days, and automatically generating time series.
  
• Manipulate and transform text in Excel with functions such as CONCAT, JOIN, EXTRACT, or REPLACE, optimizing data cleansing and preparation.
  
• Apply logical and conditional functions, such as IF, IFSET, AND, OR, and IFERROR, to automate decision-making and build intelligent formulas tailored to different scenarios.
  
• Design custom conditional formats to highlight relevant information and improve data visualization.
  
• Use search and reference functions (VLOOKUP, XLOOKUP, INDEX, MATCH, OFFSET, INDIRECT, FILTER, SORT, UNIQUE, among others) to locate, combine, and analyze information from different data sources.
  
• Optimize the handling of large volumes of information using dynamic formulas and reference tools that increase efficiency in data analysis and management.
  
• Apply best practices in formula construction, understanding operator precedence and the use of range names, to ensure the accuracy and traceability of calculations.

Advanced Excel Course Content: Operations, Functions, and Analysis Tools

Unit 1: Basic calculations, mathematical functions and statistics

• Calculations in Excel
• Calculations with cells
• Operator priority
• Relative references
• Absolute references
• Mixed references
• Calculations with multiple sheets
• Calculations with multiple books
• Mathematical Functions
• AutoSum
• Abs
• Quotient and remainder
• Random and random.between
• Roman Numeral
• Power, root and high
• Round, truncate and Pi
• Sum and product
• SumProduct
• Various Mathematics Round.Multi. and Sequence
• Sum.if, Sum.if.set
• Subtotals
• Add
• Statistical functions
• Count, will count, count.white
• Maximum, Minimum
• Median and mode
• Average, averageA
• Hierarchy.EQV
• Count.if, count.if.set
• Average.if, average.if.set
• Maxifs and Minifs

Unit 2: Date and Text Functions

• Date Functions
• Operations between dates
• LabDays, LabDays.Intl
• Dia.lab, Dia.Lab.Intl, Días360
• Today and now
• Day, month and year
• Diasem, Num.de.semana
• Date
• Date. Month, End. Month
• Fill in dates
• Text Functions
• Concat and &
• Join.strings
• Separate texts
• Spaces and clean
• Quick fill
• Left, right, extract
• Capital, Lowercase, Proper
• Long
• Equals function and equals sign
• Text, value
• Find, find
• Replace and Substitute

Unit 3: Logical Functions

• If Function
  
• Nested if function
• Yes. Set
• References in conditionals
• Yes with the Y
• If with the O
• Restrict conditions with AND and OR
• Information functions
• Conditionals error.type, type, info, sheet, sheets
• Yes. Error
• Change
• Conditional formatting
• More conditional formats
• Order of conditions in conditional format
• Conditional formatting based on another cell

Unit 4: Search and Reference Functions

• SearchV
• Row and column with VLookup
• VLookup with range names
• Match and VLookup
• SearchX
• SearchH
• VSearch for conditions
• SearchV embedded
• Index
• Desref
• Another example of Desref
• Indirect
• Transpose
• Address
• Filter
• Order
• Sort by
• Unique 
• Formula text
• Choose
• Hyperlink 

Prerequisites

Intermediate knowledge of Excel (handling basic formulas, formats and simple functions).