Intermediate Excel 2021 Course Overview
Translated by
ScribdTranslationsIntermediate Excel 2021 Course Overview
Translated by
ScribdTranslationsThe National University of Engineering (UNI) through the Directorate of Graduate Studies and
Continuing Education (DEPEC) appreciates your interest in our specialized update courses and
training and at the same time invites you to stay with us for your certification in this call.
COURSE:Intermediate Excel 2021.
DESCRIPCION DEL CURSO:
Upon completing this Intermediate Excel Course, the participant will create data tables in the
which will apply the main integrated functions of Excel, allowing you to build
summarized information panels in a quick and reliable manner. Additionally,
will identify the procedure for the creation of pivot tables and the creation of
Recorded macros to automate processes.
ADDRESSED TO:
People with basic knowledge of Excel who wish to delve deeper into this wonderful tool
tool, Staff responsible for presenting reports to senior management of companies.
Staff that handles data and metrics in the daily operations of the office,
students from all majors.
INCOME PROFILE:
People with basic knowledge of Excel and a strong desire to learn.
COMPETENCIAS:
• The student will be able to manage everyday office tasks.
the skills and knowledge acquired during the course.
• Preparation of medium complexity spreadsheets
• Optimize and organize the information in tables
CONTENT TO BE DEVELOPED:
Topic: Excel Environment and Introduction
• Subtopics
The format of tables
Name Administrator, Formula Auditing
Use and location of the components of the Ms Excel screen
Configuration of COM add-ins for Ms Excel
Programmer tab activation
Cell Formats and Data
Text Adjustment
Conditional format
Topic: Integrated Functions
• Subthemes
Sum and Count Function
o Logical functions (If, And, Or, Iferror)
Search and reference functions (VLOOKUP, HLOOKUP, LOOKUP, CHOOSE)
Index, Column, Row, Match
Rounding and truncation functions
Statistical functions ([Link], [Link])
Text functions (Text, Left, Right, Extract, Upper, Lower,
Proper name, Long
Tema: Obtener Y Combinar Fuentes De Datos Externos
• Subtopics
From Access
From Text
From Web
Other connections
Theme: Subtotals and groupings
• Subtopics
Use of subtotals
to group by
▪ Requirements
▪ Limits and scope
Link and Consolidate information
Advanced Filters
Topic: Database Functions
• Subtopics
oCrear Tablas en Ms Excel
oBdcontar, Bdcontara, Bdextraer, Bdmax, Bdmin, Bdsuma,
Bdproducto, Bdpromedio
Topic: Tools for Database Management
Subtopics:
Text in columns
Data validation
The list of dependent data
Highlight and remove duplicate data in a column
Management of schemes
oXML
Tema: Seguridad
• Subtopics
Protect Templates
Protect sheet content
To protect at the book level
Protect at sheet level
Protect at cell level
Protect and share
File password
Topic: Data Validation
• Subtopics:
oTipos de validación de datos
Error Messages for Data Validation
The use of functions in data validation
Topic: Dynamic tables and graphs
Subtopics
Introduction to Pivot Tables
Creation of a pivot table
The use of a pivot table
Modification of a pivot table
Change in the organization of a pivot table
Update of a pivot table
Formatting a pivot table
The use of various data fields
Grouping and separation of data
Change of the calculations of a pivot table
Creation of advanced pivot tables.
Filter, display or hide data from a pivot table.
Editing pivot tables.
Creation of formulas in pivot tables.
Calculated fields in a pivot table.
Calculated elements in a pivot table.
Creation of pivot tables from consolidation ranges
multiple.
Creation of pivot tables from external data.
Dynamic chart.
Create advanced dynamic charts.
To customize the appearance of the graphs.
Topic: Introduction to Macros in Ms Excel
• Subtopics
Introduction to Macros with Ms Excel
Configuring the macro security level
Macro recording
Execution of a macro
Editing a macro
Create a function with VBA in Ms Excel
Create a macro with VBA in Excel
TEACHING METHODOLOGY:
100% practical classes in computer rooms, modern and with updated software.
Applicable examples to real life and of great utility
MODE OF STUDY:
Virtual (Synchronous)
In-person
BENEFICIOS DEL CURSO:
✓ Teachers with over 20 years of experience in the work environment and 5 of
Teaching experience.
✓ Includes Digital Teaching Material
✓ Access to class recording
✓ Certificate of Approval for your resume.