Advanced Excel Course - SCORM

Advanced Excel Course - SCORM

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

About this course

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

The most widely used computing tool in businesses and institutions, Microsoft Office Excel, offers advanced skills such as linking files, nesting formulas, working with pivot tables, filters, and charts, making you competitive in job searches.

In this Advanced Excel course, you'll learn the functionalities and capabilities of Excel 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 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 

Unit 5: Graphics

• Sparklines
• Creating a chart
• New chart types
• Modify a chart
• Customize scale
• Add elements
• Analysis tools
• Pie charts
• Create chart templates
• 3D graphics
• Waterfall
• Histogram and Pareto
• Rectangles
• Mustaches and boxes
• Solar projection
• Funnel
• Maps
• Charts with non-proportional data
• Overlaid graphs with secondary axes at different scales.
• Bing Maps
• People

Unit 6: Hypothesis Analysis

• Goal Search
• One-dimensional data table
• Two-dimensional data table
• Using the Scenario Manager.
• Scenario Creation
• Consolidate
• Solver.
• Another example of solver
• Data analysis
• Forecast

Unit 7: Databases

• Import data
• Import data from a web page
• Import data from a text file
• Data sources
• Relations
• Creating tables and table appearance.
• 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 the model
• Pivot table from Power BI
• Sort a pivot table
• Modify a pivot table
• Grouping data in pivot tables
• Grouping numeric elements in a pivot table
• Group selected items in a pivot table
• Group by dates in a pivot table
• Ungroup grouped items in a pivot table
• Create dynamic charts.
• Data segmentation
• Time scale
• 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 Design
• Create multiple pivot tables
• Conditional formatting

Unit 9: Macros

• What is a macro?
• Record a macro
• Macros and Security Button
• Buttons on the leaf
• Buttons on the toolbar
• Absolute and relative references
• Example of absolute references
• Example of relative references
• How far do macros go?
• Automate tab

Prerequisites

Have intermediate knowledge of Excel. Preferably, you must have completed the "Excel 365. Basic and Intermediate" course.