You will be able to have skills at the medium level of data manipulation, as well as descriptive analysis to generate dynamic boards using different data sources.
Benefits of the program:
- You will learn the basic tools of the most famous software that is Excel.
- You will handle the organized information.
- You will work practical examples in each module.
Professionals who wish to move from a basic level to an intermediate level in Excel.
Content of the program
Diploma of 4 classroom modules that total 40 hours of learning in total.
Module 1. Data Manipulation ( 4 hours)
Include import of files to Excel, data cleaning, use of conditionals and match columns of different sheets.
- Relative, absolute and combined references
- Text manipulation
- Date Manipulation
- Type of data
- Data format
- Data tools:
- Text to columns
- Quick fill
- Consult duplicates
- Data validation
Module 2. Generation of Dynamic Tables (12 hours)
Create calculated columns, calculated fields, generation of tables and dynamic graphs based on Excel sheets.
1. Import data to Excel (.csv or .txt)
2. Advanced formulas
- Search and reference
3. Filters and ordinations:
- Text, number and color filters
- Advanced filters
4. Pivot tables:
- Dynamic table
- Value Field Settings
- Calculated field
- Filters, sorts and custom sorts
- (Create custom list)
- Data segmentation and timeline
Module 3. Basic Information Analysis (12 hours)
Use and create basic financial and statistical functions. Calculation of result status variables using transactional information, calculation of amortization tables, calculation of present value of projects.
1. Advanced formulas:
- Statistics (the most common)
- Mathematical and trigonometric (add yes, count yes, set, sum product and others)
- Financial (PAYMENT, IRR, VA, VF, VNA)
- Absolute and relative references (OFFSET)
- Relationship between sheets (what are the formulas contemplated here?)
- Custom formula
- Formula Audit
2. Hypothesis audit
- Find target
- Data table
- Scenario Management
Module 4. Advanced Information Display (12 hours)
Create digital boards for decision making and BSC.
1. Advanced graphics:
- Scatter plot and trend line
- Statistical graphs (Histogram, Pareto and Cash Diagram)
- Radial graph
- Combined graphics
- Waterfall graphics
- Dynamic chart
2. Conditional formatting
About the School
El Tecnológico de Monterrey es una institución de carácter privado, sin fines de lucro, independiente y ajena a partidos políticos y religiosos. Fue fundado en 1943 gracias a la visión del empresario ... Read More