Advanced Excel Course - SCORM

Course Description

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, students 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 companies and institutions, Microsoft Office Excel, offers, at a higher level of knowledge, linking files, nesting formulas, working with dynamic tables, filters and graphics, making you competitive in job searches.

In this Advanced Excel course you will learn the functionalities and potentialities 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
• Miscellaneous Mathematics Multi-round and Sequence
• Sum.if, Sum.if.set
• Subtotals
• Add
• Statistical functions
• Count, count, count.white
• Maximum, Minimum
• Median and mode
• Average, averageA
• Hierarchy.EQV
• Count.if, count.if.set
• Average.if, average.if.set
• Max.if.set and Min.if.set

Unit 2: Date and text functions

• Date Functions
• Operations between dates
• Dias.Lab, Dias.Lab.Intl
• Dia.lab, Dia.Lab.Intl, Days360
• Today and now
• Day, month and year
• Diasem, Weekly No.
• Date
• Date.Month, End.Month
• Fill in dates
• Text Functions
• Concat and &
• Join.chains
• Separate texts
• Spaces and cleaning
• Quick fill
• Left, right, extract
• Uppercase, Lowercase, Proper
• Long
• Equals function and equals sign
• Text, value
• Find, find
• Replace and Substitute

Unit 3: Logical Functions

• Function Yes
  
• Nested if function
• Yes.Set
• References in conditionals
• If 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 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 VSearch
• SearchX
• SearchH
• VLOOKUP for conditions
• SearchV fitted
• 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 types of charts
• 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
• Charts with non-proportional data
• Overlapping graphics and secondary axes with different scales.
• Bing Maps
• People

Unit 6: Hypothesis analysis

• Goal Search
• One Dimension Data Table
• Two-dimensional data table
• Using the Scenario Manager.
• Creating Scenarios
• 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 how tables look.
• 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 PivotTable
• 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 PivotTable 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 sheet
• Buttons on the toolbar
• Absolute and relative references
• Example of absolute references
• Example of relative references
• How far do macros reach?
• Automate tab

Prerequisites

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