Course Description
This course takes up where the Optima Train Excel Pivot Tables and Pivot Charts course leaves off. It revolves around the fairly new suite of Microsoft “Power” tools, often called Power BI. The course has three primary themes. First, it teaches you a number of methods for importing data (from a database, text files, or other sources) into Excel. The primary emphasis is on Power Query. Second, it devotes considerable time to the Excel Data Model and Power Pivot for analyzing data with pivot tables. These take traditional pivot tables to a whole new level. Third, it shows how Power View and Power Map can be used to create insightful reports and maps with very little work.
Objectives
- Be proficient in advanced Excel functions and formulas for data processing.
- Understand data visualization techniques to create impactful charts, dashboards, and reports.
- Master data analysis tools such as PivotTables, Power Query, and Power Pivot.
- Automate workflows using macros and an introduction to VBA.
- Gain expertise in troubleshooting and optimizing workbooks for performance and efficiency.
- Build interactive and professional dashboards to present data insights.
Who Should Attend
- Data Analysts and Financial Analysts looking to enhance data manipulation and reporting capabilities.
- Business Managers and Decision-Makers need to interpret and visualize data for insights.
- Accountants and Financial Professionals manage large datasets and complex calculations.
- Professionals in Operations, Marketing, and HR aiming to improve data handling and reporting efficiencies.
- Anyone with Basic to Intermediate Excel Skills who wants to transition to an advanced skill level.
Course Modules
Advanced Formulas and Functions
- Complex formulas including INDEX-MATCH, SUMPRODUCT, array functions, and conditional formulas.
- Logical, statistical, and text functions for efficient data processing.
Data Analysis Tools
- Advanced PivotTable techniques for dynamic data segmentation.
- Using Power Query to automate data imports, cleaning, and transformation.
- Power Pivot for advanced data modeling and creating data relationships.
Data Visualization and Reporting
- Designing dynamic charts and graphs for data storytelling.
- Dashboard creation techniques, using slicers, interactive charts, and custom visuals.
- Conditional formatting for highlighting key data trends.
Macros and Automation with VBA
- Recording macros to automate repetitive tasks.
- A basic introduction to VBA for custom functions and automated processes.
Workbook Optimization and Troubleshooting
- Tips for enhancing workbook performance and efficiency.
- Debugging and auditing tools to manage large, complex datasets.
