________________________________________________________________
Do you want to take this course remotely or in person?
Contact us by email: info@nanforiberica.com , phone: +34 91 031 66 78, WhatsApp: +34 685 60 05 91 , or contact Our Offices
________________________________________________________________
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.
Training is carried out through our Virtual Campus, With this option, you will have all the educational content on the course platform and it will be accessible 24 hours a day, 7 days a week, from the start of the course. The student will also have access to participation forums , as well as a continuous tutoring .
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 cleaning
- 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).
Other training modalities
If you are interested in taking this course in person or online, please contact us:
