Course Detail

Advanced Excel

Advanced Excel

  •    Duration: 1 Month
  •    Eligibility: Graduate/ Professional

Learn the most advanced formulas, functions and types of financial analysis to be an Excel power user. This advanced Excel training course builds on our free Excel Crash Course and is designed specifically for spreadsheet users who are already proficient and looking to take their skills to an advanced level. This advanced tutorial will help you become a world-class financial analyst for careers in investment banking, private equity, corporate development, equity research and FP&A. By watching the instructor build all the formulas and functions right on your screen, you can easily pause, rewatch, and repeat exercises until you've mastered them. The main formulas & functions covered in this training course include:


Syllabus:


Module 1: Advanced Excel Formulas and Functions
  • Advanced Excel Functions
    • Logical functions: IF, IFS, AND, OR, NOT, SWITCH
    • Lookup & Reference functions: VLOOKUP, HLOOKUP, XLOOKUP, INDEX, MATCH, OFFSET, INDIRECT
    • Text functions: TEXT, CONCATENATE, LEFT, RIGHT, MID, LEN, TRIM, TEXTJOIN
    • Date and Time functions: DATE, TIME, DATEDIF, NETWORKDAYS, EDATE, EOMONTH
    • Math & Statistical functions: SUMIF, SUMIFS, COUNTIF, COUNTIFS, AVERAGEIF, AVERAGEIFS, RANK, STDEV, PERCENTILE
    • Financial functions: PMT, NPV, FV, PV, RATE, IRR
  • Array Formulas
    • What are array formulas? Understanding the difference between regular formulas and array formulas
    • Using Ctrl + Shift + Enter to create array formulas
    • Advanced techniques with TRANSPOSE, FREQUENCY, SUMPRODUCT

Module 2: Data Management and Cleaning
  • Data Importing and Exporting
    • Importing data from external sources (CSV, Text, Web)
    • Using Power Query to import and clean data
    • Exporting data to different formats (PDF, CSV, XML)
  • Data Cleaning Techniques
    • Removing duplicates using Remove Duplicates feature
    • Text-to-columns for splitting data into multiple columns
    • Using the TRIM, CLEAN, and SUBSTITUTE functions
    • Identifying and correcting errors in data with ISERROR, IFERROR, ERROR.TYPE
    • Using Flash Fill for automatic data entry (Excel 2013 and later)
  • Data Validation and Conditional Formatting
    • Setting up data validation rules (dropdown lists, input messages, and error alerts)
    • Conditional formatting for data visualization (color scales, data bars, icon sets)
    • Advanced conditional formatting (using formulas for conditional formatting)

Module 3: Data Analysis and Pivot Tables
  • Pivot Tables
    • Creating and customizing Pivot Tables (drag and drop method)
    • Using multiple fields in rows, columns, values, and filters
    • Grouping data by date, month, quarter, and year
    • Using calculated fields and calculated items in Pivot Tables
    • Slicers and Timelines for filtering Pivot Tables dynamically
    • Formatting Pivot Tables for better readability and reporting
  • Advanced Pivot Table Techniques
    • Using Power Pivot for creating data models
    • Connecting multiple data tables with relationships
    • Using DAX (Data Analysis Expressions) for advanced calculations in Power Pivot
    • Pivot Charts (creating dynamic visualizations from Pivot Tables)
  • What-If Analysis and Scenario Manager
    • Using Goal Seek to find specific values
    • Using Data Tables for multiple variables
    • Scenario Manager for comparing multiple scenarios and outcomes
    • Solver Add-in for optimization problems (e.g., maximizing profits)

Module 4: Advanced Charting and Visualization
  • Creating and Customizing Charts
    • Creating advanced chart types: Waterfall, Radar, Combo charts
    • Customizing chart elements (titles, labels, axes, legends, etc.)
    • Using secondary axes and multiple data series in charts
    • Creating and formatting dynamic charts with named ranges
  • Data Visualization with Power BI Integration
    • Introduction to Power BI for Excel users
    • Importing data from Excel into Power BI for advanced reporting and dashboards
    • Creating interactive reports and visualizations in Power BI
  • Dynamic Dashboards and Reporting
    • Creating interactive dashboards using Excel’s form controls (buttons, drop-downs, and checkboxes)
    • Using slicers and PivotCharts for dynamic reporting
    • Building KPI indicators and other visual analysis tools in Excel

Module 5: Excel Automation and Macros
  • Introduction to Macros and VBA
    • What is VBA (Visual Basic for Applications)?
    • Recording simple macros using the Macro Recorder
    • Editing recorded macros in the VBA editor
    • Writing basic VBA code for automating tasks
    • Creating custom functions with VBA
  • Advanced Macro Techniques
    • Writing more complex VBA procedures for data manipulation
    • Using loops (For, For Each, While) and conditional statements (If, Select Case) in VBA
    • Error handling in VBA (On Error statements)
    • Creating user forms for better interaction with Excel
    • Automating repetitive tasks with VBA (e.g., formatting, data import/export, etc.)

Module 6: Advanced Excel Features and Tools
  • Power Query (Get & Transform)
    • Introduction to Power Query for data transformation
    • Filtering, merging, and appending data in Power Query
    • Advanced Power Query techniques (custom columns, group by, etc.)
    • Combining data from multiple sources using Power Query
  • Power Pivot for Data Modeling
    • Introduction to Power Pivot and its role in advanced data analysis
    • Creating relationships between multiple tables
    • Writing DAX formulas for custom measures and calculated columns
    • Using Power Pivot for large datasets and advanced data models
  • Excel as a Database
    • Structuring data for efficient analysis (tables and structured references)
    • Using Excel as a basic database (using INDEX and MATCH for database-like queries)
    • Advanced filtering and sorting techniques
    • Creating relationships and working with large data sets efficiently

Module 7: Advanced Data Manipulation and Financial Modeling
  • Advanced Data Manipulation
    • Using Power Query for advanced data cleaning and reshaping
    • Merging and appending queries in Power Query
    • Using TEXTJOIN and CONCAT for advanced text processing
  • Financial Modeling Techniques
    • Building a simple financial model (forecasting, budgeting, and valuation models)
    • Using Excel for financial statement analysis (income statement, balance sheet, and cash flow)
    • Sensitivity analysis in financial models
    • Capital budgeting using Excel (NPV, IRR, Payback Period)

Module 8: Collaboration and Sharing Excel Workbooks
  • Collaborative Features in Excel
    • Sharing and co-authoring workbooks in Excel (Excel 365 features)
    • Protecting and restricting access to certain areas of a workbook
    • Using comments and notes for collaboration
    • Tracking changes and managing versions in shared workbooks
  • Excel and Cloud Integration
    • Saving Excel files to OneDrive for cloud access and sharing
    • Collaborating in real-time with cloud-based Excel (Office 365)
    • Using Excel in the browser (Excel Online)

Module 9: Final Project and Case Studies
  • Advanced Excel Project
    • A comprehensive project that incorporates multiple advanced Excel concepts
    • Creating a dynamic financial dashboard or business report using PivotTables, charts, and automation
    • Applying VBA to automate tasks and enhance reporting efficiency
    • Presenting the final solution with detailed documentation and explanations

Assignment:  Self-Assessment by Faculty

Exit Profile: Expert in Excel Application Software

Back

Nielit courses in lucknow

Nielit centers in lucknow

Nielit center in gomti nagar

O level in gomti nagar

O level course in gomti nagar

Best o level institute in lucknow

Best Computer Education Institute Lucknow

Best Software Institute Lucknow

NIELIT O Level Courses in Lucknow

O level training institutes in lucknow

Gst course in gomti nagar

Computer course in lucknow

Best computer institute in lucknow

Best computer institute in gomti nagar

Best computer center in gomti nagar

Full stack development institute near me Gomti Nagar Lucknow

Full stack development institute near me for freshers

Full stack development institute near me for beginners

Best full stack development institute near me

Web development course fees in lucknow

Web development institute in lucknow

Full stack developer course

Digital marketing training in gomti nagar, lucknow

Digital marketing course in gomti nagar, lucknow fees

Java full stack developer course free with certificate

Java full stack developer course fee

Java full stack developer course online

Python full stack developer course near Gomti Nagar Lucknow

Best Computer Courses After 12th Pass

Software Engineer Courses Lucknow

Best Web Graphic Designing courses in Lucknow

Web Designing course Lucknow

Graphics Designer Courses Lucknow

Graphic Designer Courses Lucknow

C Programming Language course Lucknow

C Programming Language courses fees

C++ courses Lucknow

php summer training Lucknow

English typing Course Lucknow

Digital Marketing Training fees Lucknow

O level course fees in lucknow

O level course fees in gomti nagar

Doeacc o level course in lucknow

CCC in lucknow

CCC lucknow

CCC course in lucknow

CCC course in gomti nagar

CCC course fees in lucknow

CCC course institute in lucknow

Tally institute in lucknow gomti nagar

Tally.ERP9 courses Lucknow

Advance Excel Course Lucknow

Tally academy in gomti nagar

Tally center in gomti nagar

ADCA Computer Courses Lucknow

Full stack developer course

Full stack course

Full stack developer classes

Courses for full stack developer

Full stack classes

Course for full stack developer

Best computer institute near me

Full stack web development course

Full stack development course

Java full stack developer course

Full stack java developer course

Python full stack developer course near me

Python full stack developer course fees

Best python full stack developer course near me

Python full stack developer course

full stack net developer course

net full stack developer course

dot net full stack developer course

Software Training Institute Lucknow

DCA Courses Fees Lucknow

Accounting and Finance Diploma Courses Lucknow

Office Training Courses Lucknow

Microsoft Office courses in Lucknow

Programming in C++

Fees of computer Courses Lucknow

php live project training

Java training Lucknow

Online typing course

SEO SMO Training Lucknow

CCC computer course in gomti nagar, lucknow

SEO course in lucknow

Project training in lucknow

Project training in gomti nagar

Industrial training in lucknow

Summer training in lucknow

Asp.net training in lucknow

ASP.NET Training Lucknow

ASP.NET Training Institute Lucknow

Summer Internship and Industrial Training lucknow

Summer Training Institute Uttar Pradesh

PHP Training Lucknow

Tally Fees

Tally Courses in Lucknow

Diploma in Computer Application

Java training in lucknow

Core java training in lucknow

PPH training in lucknow

Digital marketing course in gomti nagar, lucknow

Best digital marketing institute in gomti nagar, lucknow

Digital marketing institute in gomti nagar, lucknow

Best digital marketing institute in gomti nagar, lucknow

Digital marketing course in gomti nagar, lucknow

Best digital marketing course in gomti nagar, lucknow

Digital marketing training in gomti nagar, lucknow

Best digital marketing training institute in gomti nagar, lucknow

Digital marketing institute in gomti nagar, lucknow

Digital marketing training in gomti nagar, lucknow

Digital marketing course in gomti nagar, lucknow fees

python full stack developer course with placement

full stack web development course in lucknow

ASP.NET full stack developer course near Lucknow

ASP.NET full stack developer course near Gomti Nagar Lucknow

Training in PHP Technology

IT sector training Lucknow

Best IT sector courses Lucknow

Training in ASP.NET Technology

Hindi typing course Lucknow

Digital Marketing Training Institute Lucknow

Back to Top