Course Description

This course is taught in online mode and consists of 9 units.

The duration of the course is 60 hours That distributes between content and collaboration tools. Upon completion, the student will receive an accrediting diploma.

Training is done through our Virtual Campus , with this modality you will have all the didactic content on the course platform and it will be accessible, from the start day of the course, 24 hours a day, every day of the week. Access to the Campus will be available for 8 weeks.

The student will also have participation forums , as well as a continuous tutoring .

About this course

Knowing Excel at an advanced level means understanding the program's tools, tricks and shortcuts to speed up and facilitate work tasks even more.

The most used computing tool in companies and institutions, Microsoft Office Excel, proposes, at a higher level of knowledge, linking files, nesting formulas, working with dynamic tables, filters and graphs, making you competitive in job searches.

In this Advanced Excel course you will learn about the functionalities and potential at an advanced level of use and analysis.

Course content

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 several 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 Redond.Mult. and Sequence
• AddIf, AddIfSet
• Subtotals
• Add
• Statistical functions
• Count, will count, count.white
• Maximum minimum
• Median and mode
• Average, averageA
• Hierarchy.EQV
• Count.if, count.if.set
• Avg.if, average.if.ensemble
• Max.if.set and Min.if.set

Unit 2: Date and Text Functions

• Date Functions
• Operations between dates
• Days.Lab, Days.Lab.Intl
• Dia.lab, Dia.Lab.Intl, Días360
• today and now
• Day, month and year
• Days, Number of weeks
• Date
• Date. Month, End.Month
• Fill dates
• Text Features
• Concat and &
• Join.strings
• Separate texts
• Spaces and clean
• Quick filling
• Left, right, extract
• Mayusc, Minusc, Nompropio
• Long
• Equal function and equal sign
• Text, value
• find, find
• Replace and Substitute

Unit 3: Logical Functions

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

Unit 4: Search and Reference Functions

• SearchV
• Row and column with VLookup
• VLookup with range names
• Match and SearchV
• SearchX
• SearchH
• VLookup for conditions
• SearchV embedded
• Index
• Deref
• Another example of Desref
• Indirect
• transpose
• Address
• Filter
• Order
• Sort by
• Unique 
• Text formula
• Choose
• Hyperlink 

Unit 5: Graphics

• Sparklines
• Creating a chart
• New chart types
• Modify a chart
• Customize scale
• Add items
• Analysis tools
• Pie Charts
• Create chart templates
• 3D graphics
• Waterfall
• Histogram and Pareto
• Rectangles
• Mustaches and boxes
• solar projection
• Funnel
• Maps
• Graphs with non-proportional data
• Superimposed graphs with secondary axis with different scales.
• Bing Maps
• People

Unit 6: Hypothesis analysis

• Target Search
• Data table one dimension
• Two dimensional data table
• Use the Scenario Manager.
• Creation of Scenarios
• Consolidate
• Solver.
• Another example of solver
• Analysis of data
• Forecast

Unit 7: Databases

• Import data
• Import data from a web page
• Import data from a text file
• Data Sources
• Relations
• Creation of tables and appearance of the tables.
• Select parts of a table, and modify the table
• Remove duplicate rows from a table
• Data segmentation
• Data validation
• Examples of validations
• Order
• Text filters
• Number and date filters
• Advanced filters
• Database functions
• Totals row

Unit 8: Pivot tables

• Create a pivot table
  
• Pivot table from model
• Pivot table from Power Bi
• Sort a pivot table
• Modify a pivot table
• Group data in pivot tables
• Group numeric elements of a pivot table
• Group selected items from a pivot table
• Group by dates in a pivot table
• Ungroup grouped items from a pivot table
• Create dynamic graphics.
• Data segmentation
• Timeline
• Control panel – dashboard
• Other pivot table options
• Field properties
• Calculations with the value field
• Create a calculated field
• Reference to cells in a pivot table
• pivot table layout
• Create multiple pivot tables
• Conditional format

Unit 9: Macros

• What is a macro?
• Record a macro
• Macros and security button
• Buttons on the sheet
• Buttons on the toolbar
• Absolute and relative references
• Example absolute references
• Example relative references
• How far do macros go?
• automate tab

Previous requirements

Have intermediate knowledge of Excel. Preferably having completed the “Excel 365. Basic and Intermediate” course.