BASIC PLAN

Introduction, Environment and Shortcuts

Concept of cell, range, column, and row.
Status bars and quick access.
Options belt. Use and customization.
Fundamental operations: copy, cut, paste, special paste.
Ribbon customization.
Quick access bar.
Undo, redo, saved and autosaved.
Custom lists and rank names.
Comments and links.
Handling the tape with the keyboard.
17 fundamental Excel shortcuts (with a printable guide of 200 shortcuts).
Selection of cell blocks in Excel.
Operations with sheets and windows.
Divide and organize books.
Synchronized scroll.
Immobilize panels.
Moving between books.
Effective management of Zoom.
Customized Views.
Excel Online.
Format, Forms and Images
Numerical formats, currency and accounting.
I work with percentages.
TEXT() function and PROPER() function.
Effective use of dates and times.
Custom date format.
Operations with sheets and windows.
Edges.
Different types of horizontal and vertical alignment.
Merge cells vs. Center in selection.
Adjust text.
Insertion and editing of images.
I work with shapes and Smart Art.
Hyperlinks in forms.
Insertion of YouTube videos.
Data analysis with conditional formatting.
Upper and lower rules.
Data Bars.
Data Scales.
Highlight cell rules using formulas and functions.
Copy format productively. Shortcuts and tricks.
First Formulas and Functions
The ABC of formulas and functions.
Use of parentheses.
Operators and order of calculation.
Use of Relative, Absolute, and Mixed Cells.
My First Formulas with a Practical Case: Payroll Spreadsheet.
My First 7 Functions with a Practical Case: Quiz in Excel.
I work with data
Everything you need to work effectively with data is in this module. We will see:
Differences between ranges and tables.
Search and replace.
Use of the unknown tool Go to Special.
Sorting by one or more criteria.
Use of filters.
Highlight cell rules using formulas and functions.
Data import.
Convertir texto en columnas.
Remove duplicates.
How references work within tables.
Filters and sorting of data.
Table styles.
Print
Print Views.
Resolving the dilemma of line breaks and poor prints.
Titles and Printing Area.
Printing options.
Header and footer.
Print selection.
Spell check.
Print and export to PDF.
Graphics
Difference between series and labels.
Graph format options.
Graph styles.
Pie charts, bar charts, and column charts.
Bing Maps.
Line and scatter plots.
Combined charts and the use of the secondary axis.

In-Depth Functions - Part 1

Here you will learn the fundamental functions of Excel in detail. We will see:
Building conditionals with the IF function.
IF function combined with AND and OR functions.
Nested IF function.
How to use the COUNTIF function.
Multiple criteria with the COUNTIFS function.
How to Use the SUMIF Function.
Text Functions Part 1.
Text Functions Part 2.
Funciones de Tiempo: AÑO, MES, DIA, HOY, AHORA, [Link], HORA, MINUTO,
SECOND.
Practical Case Part 1: 3 Different Ways to Split a Text into Multiple Columns.
Practical Case Part 2: Mastering Date and Time Calculations in Excel.
Practical Case Part 3: Logical Functions, Conditional Formatting, and Data Validation.
INTERMEDIATE PLAN
In-Depth Functions - Part 2
The Popular VLOOKUP Function and its variants.
The HLOOKUP Function.
The MATCH Function.
The INDEX Function.
Working with Averages and Order of Calculation.
Audit of Formulas and Typical Errors (N/A, DIV/0, etc).
Schemas, Subtotals, and Data Validation
Case A: Validation of Numeric Data.
Case B: Validation List.
Case C: Text Length.
Case D: Customized.
Consolidate Data.
Consolidation by Position.
Consolidate by Categories.
Introduction to schemes.
Automatic creation of schemes.
Manual creation of diagrams.
Delete a schema.
Hide a scheme.
How to work with subtotals.
Create, modify, and delete automatic subtotals.
Subtotal levels.
Subtotal organization.
Reports with subtotals.
Security
Book protection.
Sheet protection.
Protection of form elements.
User protection.
Hide formulas.
Use passwords to protect the entire book.
Share a book.
Change control.
Pivot Tables
One of the fundamental tools of Excel, here you will learn how to use it in
My First Pivot Table.
The Pivot Table Environment.
Filters in Pivot Tables.
Grouping Dates.
Grouping Fields.
Formatting the Pivot Table.
Data Segmentation.
Timeline.
Showing Derived Values.
Linked Tables.
Dynamic Graphics
Make your charts linked to your pivot tables, essential for your
reports. You will learn:
Creating a Dynamic Chart.
Creating a Dynamic Chart and Pivot Table.
Formatting a Dynamic Chart.
Giving Design to a Dynamic Graph.
Changing the Chart Type.
Applying Filters to a Dynamic Chart.
Dynamic Chart with Data Segmentation.
Linked Dynamic Charts.
Creating and Using Templates.
ADVANCED PLAN
This plan includes all the previous levels plus the following list of advanced lessons.

Introduction to Power Query

In this module we will cover the incredible tool Power Query for import and
data transformation. We will see:
Introduction, environment, and initial consultations.
Data normalization in Power Query (unpivoting data).
Merge queries.
Append Queries.
Default and custom formats.
Calculated columns.
Use of IF ... THEN ... ELSE.
Report cleaning by replacing macros.
Duplicating and referencing queries.
Advanced Pivot Tables
We continue to delve into Pivot Tables until we cover every detail of this powerful tool.
tool. We will see:
Calculated fields.
Order of calculation.
Calculated elements.
OLAP tools.
Use of the [Link] function.
Use of the IMPORTDATA function.
Creation of pivot tables from multiple consolidation ranges.
Advanced Functions
In this module, we continue to delve into functions, picking up where we left off in the
Intermediate Excel course. We will see:
Advanced data search functions.
The basics: DESREF and INDIRECT functions.
Reference manipulation.
Displacement of references.
Search and selection of data.
Meeting of coincidences.
Direct selection of a data.
Search in rows and columns.
Use of dynamic ranges.
Funciones para estimaciones, de bases de datos
and matrix
You will see how these functions will improve your ability to perform analysis
Most used database functions.
Concept of matrix function.
Matrix functions that produce a single result.
Matrix functions that produce a set of results.
Various calculations related to a date.
Conditionals with dates.
Use of Excel Online.
Calculation of working days.
Views.
Scenarios, Search Target and Solver
We will delve into these analysis tools. We will see:
Analysis tool configuration.
Table with one variable.
Table with two variables.
Functions for making forecasts.
Scenario simulation.
Creation of scenarios.
Use of the scenarios.
Search for objective.
The solver tool.
Applying restrictions.
Reports and scenarios.
Resolution options.
Applications of solver.
Introduction to Power Pivot and DAX in Excel
In this module, we provide an introduction to Business Intelligence in Excel with Power Pivot.
DAX. You will learn:
Introduction, downloadable module files and necessary software.
Qué es BI y el Caso Práctico que Haremos.
Importing Multiple Data Sources with Power Query.
Creation of the Model.
Cleaning and Hiding Columns.
Calculated Columns vs. Measures.
Generating a Category with Calculated Columns.
Creating a Dynamic Calendar Table in Power Query.
Dashboard with Dynamic Charts.
Dashboard with Power View.
Geographic information with 3D maps.
Quick Tour of Power BI Desktop.
Preliminary Steps, Environment, and First Macro
Here you will take your first steps in Macros and VBA, we will see:
Welcome and Introductions
Bonus Extra: Library of 40 Macros
Developer Sheet and Visual Basic Environment
My first Macro with MsgBox
4 Distinct Ways to Run a Macro
Watching and Protecting Macro Files
How to Save a Macro File and Prevent Code Loss
Tips for Protecting Files that Have Macros
Basic Operations of Visual Basic for
Applications
How to Read and Write Data in a Cell with CELLS
How to Process a Vertical Data Range (FOR - NEXT)
How to Process a Horizontal Data Range (FOR - NEXT)
IF - THEN Statement - Simple Example
IF - THEN Statement - Advanced Example with ELSE
Input Box Function
Macro Recorder
Macro Recorder: Simple Example
Optimizing the Code of the Recorder
Macro Recorder: Complex Example
When Is It Appropriate to Use the Recorder and When Not To

Practical Case 1 - Report with Filters from

Advanced Filters
What is an Advanced Filter and How Does It Work
How to Display Filter Results on Another Sheet
Recording the Macro and Finalizing the Report

Caso Práctico 2 - Reporte con Filtros usando

Form Controls
Similar to the previous module, but using Form Controls. You will learn:
What is a Form Control and How to Search for a Value with the INDEX Function
Generating the Report from Controls
Practical Case 3 - Report with Filters from
Data Validation
Following the same example as the previous two modules, but using Validation of
Data. You will learn:
Preparation of the File Prior to the Macro
Writing the FOR - NEXT and the IF - THEN to Copy the Data
Tuning the Macro and Defining the Counter
Formatting with the Recorder and Deleting the Data
Practical Case 4 - Cleaning a Report of
Accounting
We will download a CSV file from accounting software and format it with
Macros saving 30 minutes of manual work daily. You will learn:
Definition of Variables with Input Box and explanation of RowCounter
Copied from Rows with the First FOR - NEXT
Copied Columns with Nested FOR - NEXT
Added IF - THEN statement to Exclude Empty Rows
Macro Verification or Step-by-Step Debugging
Practical Case 5 - Admission Form
Record Control
In this practical case, we will develop a form with Macros to simplify the entry of
data to an Excel book. You will learn:
Introduction to Forms and Controls
Step-by-Step Form Development Part 1
Development of the Form Step by Step Part 2
Step by Step Form Development Part 3
Validation 1 - Controlling the Correlative
Validation 2 - Controlling the Input of Numbers
Practical Case 6 - Dynamic Report with
Functions and Macros
In this practical case, we integrate advanced functions such as OFFSET, Tables, and Charts.
Dynamic with Macros. You will learn:
Use of OFFSET to define dynamic ranges
Report with form controls
Added linked dynamic charts to the dynamic range
Dynamic sorting of values with Macros
Final design details of the report