Dominando a Excel

Master Excel Intermediate Skills: Unlock Powerful Data Analysis

This comprehensive course elevates your Excel skills beyond the basics, equipping you with the tools to efficiently manage, analyze, and visualize data. Learn to harness the power of advanced features for increased productivity.

Data Formatting & Presentation

• Custom Formatting: Create visually appealing and informative spreadsheets with tailored formatting options.
• Quick Fill: Speed up data entry and formatting with efficient quick fill techniques.
• Conditional Formatting: Highlight key data points with rules based on cell values – apply formatting based on text, dates, or quantities.
• Data Bars & Color Scales: Quickly visualize data trends using intuitive data bars and color scales.
• Icon Sets: Enhance your charts and dashboards with meaningful icon sets.
• Data Validation: Ensure data accuracy and consistency with robust data validation rules.
• Text Manipulation: Master text functions for cleaning, transforming, and extracting information from text data.
• Date & Time Functions: Work effectively with dates and times using a range of built-in functions.
• Lists in Excel: Organize and manage data with dynamic lists – create, modify, and delete lists for efficient data handling.

Formulas & Functions: Unlock Data Power

• Creating Formulas: Build powerful formulas to perform calculations and analyze data.
• Logical Functions (IF): Implement conditional logic with IF, nested IF statements, AND, OR, and NOT functions.
• Date & Time Functions: Utilize functions like TODAY() for date-related calculations.
• Lookup Functions (VLOOKUP & HLOOKUP): Efficiently retrieve data from tables using VLOOKUP and HLOOKUP.
• Statistical Functions: Calculate essential statistics like MAX, MIN, AVERAGE, SUM, COUNTIF, SUMIF, COUNTA, COUNTA, and COUNTBLANK.
• Frequency: Determine the frequency of unique values in a dataset.
• Database Functions (DSUM, DAVERAGE, DMAX, DMIN): Perform calculations on database ranges.
• Text Functions (CONCATENATE, LEFT, RIGHT, MID, TEXT): Manipulate text strings for data extraction and formatting.
• Text Case Functions (UPPER, LOWER, PROPER): Convert text to uppercase, lowercase, or proper case.
• Financial Functions (PMT, IPMT, PPMT): Calculate loan payments and other financial metrics.

Data Management & Analysis

• Charts & Graphs: Create compelling visualizations to communicate data insights – including Pareto charts and optional charts.
• Absolute References: Understand and utilize absolute cell references in formulas.
• Mail Merge: Create personalized documents by combining Excel data with Word documents.
• Subtotals: Generate subtotals for data analysis within a database.

Advanced Excel Techniques

• Goal Seek & Solver: Find optimal solutions to complex data problems.
• Consolidate: Combine data from multiple workbooks into a single sheet.
• Importing External Data: Import data from various sources, including text files, Access databases, PDFs, images, and web pages.
• Pivot Tables: Analyze large datasets with interactive pivot tables – create field lists, calculated fields, and calculated items.
• Dynamic Charts: Build charts that automatically update based on data changes.
• Slicers & Timelines: Easily filter and analyze data with slicers and timelines.

Excel Automation with Macros

• What are Macros? Understand the fundamentals of Excel macros.
• VBA Environment: Prepare your environment for developing macros in the VBA editor.
• Relative References: Learn how relative references work in macros.
• VBA Developer Tab: Utilize the VBA Developer tab for macro development.
• Macro Examples: Practical examples, including:
  • Inventory Control System
  • Automating Repetitive Tasks (Reports)
• Developing Functions: Create custom functions for specific calculations:
  • Currency Conversion (Pesos to USD, Miles to Kilometers, Fahrenheit to Celsius, Pounds to Kilograms)
  • ISTP Tax Calculation
  • Text-to-Speech Function
  • Generating Certificates in PowerPoint

This course provides a solid foundation in intermediate Excel skills, empowering you to become a more efficient and effective data analyst.