Advanced Formulas and Data Analysis Using Microsoft Excel

Description

In this course, you will learn :

• Utilizing the SUM, COUNT, MAX, and MIN Functions Learn how to use SUMIF, SUMIFS, COUNTIF, COUNTIFS, VLOOKUP, INDEX, and MATCH for Data and Financial Analysis.
• Discover the Power of Data Analysis with Microsoft Excel Learn how to create Dynamic Reports and Charts with PIVOT TABLE and PIVOT CHART.
• Working with Images in Excel (Pictures, Shapes, Screenshots, and Smart Art Objects).
• Using the SUB TOTAL tool to calculate subtotals for grouped items.
• Working with charts (column, pie, bar, line, and many others). Working on Chart Designs and Editing Charts
• Understand how to manage large amounts of data, manipulate it, summarise it, and generate reports.
• Using Financial Functions to Understand Project Evaluation Techniques (PV, FV, NPV & IRR).
• Using the What-If-Analysis Tools to Create Sensitivity Analysis (Goal Seek, Data Table, Solver and Scenarios).
• Utilizing Logical Functions (IF, AND & OR and NESTING the IF Functions) Using the Data Consolidation Tool to combine periodic or regional reports.
• Using Data Validation Tools and Rules to validate and ensure data entry accuracy Making a Basic Data Presentation Dashboard
• Using the Hyperlink Tool to Create Links to Worksheets and External Files Working with the Spark line Chart tool to analyse and present in-cell data.
• Excel Macros and VBA Automation Overview (Macros Recording, codes, User Form creation, VBA Controls and Objects).
• Using names in formulas and working with names Working with ranges and generating tables from data sets Understanding Excel data protection.
• Making Use of Collaboration Tools (Comments & Workbook sharing) Working with the Financial Functions (PMT, PPMT, and IPMT) Using the DATE Functions (EOMONTH, TODAY, NOW, NETWORKDAYS, WORKDAY).
• Utilizing DATABASE Functions (DSUM, DCOUNT, DMIN, DMAX & DAVERAGE) Working with SORTING, FILTERING (AutoFilter, CustomFilter, and other filters), and the Conditional Formatting Tool.
• Working with Outside Data (Learn how to input Text File and Data Table from Web into Excel) Working with the Excel Data Entry Form.
• Using 3D Formulas and Functions to perform calculations and consolidate worksheets.
• Excel Formulas, Calculations, and Effective Function Use

Syllabus :

• Microsoft Excel Charts & Formula Auditing Tools
• Microsoft Excel Charts & Formula Auditing Tools
• How to use Excel Functions and References
• Microsoft Excel Functions (Learning Various Functions)
• Microsoft Excel Functions - Financials, Date, Text & Database
• Working with Data Sorting, Filtering, Formating Data & using Images
• Working with External Data and using Data Entry Tool
• Advanced Excel Data Consolidation, Analysis & Summary task
• Data Visualization, Validation and Financial Analysis
• Advanced Excel Function - Nesting Functions in Formulas
• What-If-Analysis, Sparkline and Excel Macros/VBA