Scribd
Upload
6 views4 pages

Intermediate Excel Course Overview

This Intermediate Excel course teaches participants to apply conditional formatting, create pivot tables, use nested built-in functions, assign range names to cells, create links between sheets and workbooks, generate specialized charts, and introduce recorded macros to automate processes. The course lasts approximately 20 hours and covers topics such as data table management, advanced filters, mathematical, statistical, financial, and text functions.

Translated by

ScribdTranslations
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
6 views4 pages

Intermediate Excel Course Overview

This Intermediate Excel course teaches participants to apply conditional formatting, create pivot tables, use nested built-in functions, assign range names to cells, create links between sheets and workbooks, generate specialized charts, and introduce recorded macros to automate processes. The course lasts approximately 20 hours and covers topics such as data table management, advanced filters, mathematical, statistical, financial, and text functions.

Translated by

ScribdTranslations
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd

Intermediate Excel

COURSE OBJECTIVE

Upon completing this Intermediate Excel Course, the participant will create tables.
data in which the main built-in functions of Excel will apply, which will
will allow for the quick construction of summarized information panels in a way that
reliable. Also the capture of information through the use of names of
range, will identify the procedure for creating pivot tables and
the creation of recorded Macros to automate processes.

Syllabus
I. FORMULAS AND FUNCTIONS, REVIEW OF CONCEPTS
BASIC
Handling formats: Number formats, date formats, create a
custom format.
Operators' priorities Preparation of formulas: References
relative, absolute, and mixed.
Review of basic functions: Sum, Average, Count, Counta, Max,
Min, [Link], [Link].

II. Conditional Formats


Apply formatting to all cells using a two-color scale.
Apply formatting to all cells using data bars.
Apply formatting to all cells using a set of icons.
Apply formatting only to cells that contain text values,
número, fecha.
Apply formatting only to the lower or upper range values.
Apply formatting to values above or below average.
Use a formula that determines the cells to apply formatting.
Find cells that have conditional formatting.

III. MANAGEMENT OF TABLES AND DATA


Characteristics
Table creation
Automatic table expansion
Application of Styles
Intermediate Excel

Highlight rows and columns


Filter and sort data
Use of custom advanced filters: features, preparation of
["advanced filter elements","application of the advanced filter","deactivate"]
the advanced auto filters.
Activation of the autofilters.
Remove filter criteria
Structured references
Printing a table

IV. MANAGEMENT OF INTEGRATED OR NESTED FUNCTIONS IN


EXCEL
Characteristics and importance of functions
Mathematical functions ([Link], Round, Truncate,
Sumaproducto, Residue
Statistical functions ([Link], [Link])
Date functions (Today, Now, Date, DayOfWeek, Month, Day, Year)
Sifecha
Basic financial functions (PV, FV, RATE, NPER, NPV, IRR)
Search and reference functions (Vlookup, Hlookup, Search, Choose,
Index, Column, Row, Match)
Logical functions (If, And, Or, Iferror)
Text functions (Text, Left, Right, Extract, Upper)
Lowercase, Proper noun, Length
Information functions (IsBlank, IsNumber, IsText)

V. ASSIGN RANGE NAMES TO CELLS


Name Chart.
Name Administrator.
Create names with scope
Create from selection.
Edit names
Use in the formula and paste names.

VI. PIVOT TABLES


Intermediate Excel

Creation and editing of a pivot table


Modification of a pivot table
Dynamic table styles
Table information update
Data segmentation
Pivot table tools (active field, group, sort,
etc.)
Report design
Dynamic graphics.

VII. MANAGEMENT OF LINKS


Features, creation of links.
Range of cells with links.
Saving sheets with links.
Links in the same workbook.
Links between various workbooks.
Creation of formulas with links.
Link updates.
Removal of links.
VIII. SPECIALIZED GRAPHS
Creation and formatting of charts
Use SmartArt graphics
Rotate a graph
Restore an image to its original state
Minicharts
Scatter plots
Bubble charts
Price charts, area and radial charts
Create a flowchart and an organizational chart

IX. INTRODUCTION TO MACROS


Definition and types of Macros
Creation of macro-enabled files
Recorded macros
Strategy for creating recorded macros
Intermediate Excel

Execution of Macros

Course duration approximately 20 hours

You might also like