Diploma in Advance Excel

/ JOB Courses / By

Diploma in Advance Excel

“Become an Excel expert with NSNY’s ‘Diploma in Advance Excel’, and unlock the full potential of Microsoft Excel”

Are you ready to take your Excel skills to the next level? NSNY’s Diploma in Advance Excel course offers a comprehensive syllabus covering essential topics such as What-If Analysis, Data Validation, Pivot Tables, Excel Dashboard creation, and more. Let’s dive into what you’ll learn:

Syllabus Overview:

  • What-If Analysis: Master Goal Seek, Data Tables (PMT Function), Solver Tool, and Scenario Analysis techniques for in-depth data exploration.
  • Data Validation: Learn to ensure data accuracy and integrity through validation rules and logical analysis.
  • Lookup Functions: Explore advanced lookup functions for efficient data retrieval and analysis.
  • Pivot Tables: Harness the power of Pivot Tables for dynamic data analysis and reporting.
  • Excel Dashboard: Create professional dashboards to visualize and communicate insights effectively.
  • VBA Macro: Dive into Visual Basic for Applications (VBA) programming to automate tasks and enhance Excel functionality.
  • Mail Functions: Automate email communication using VBA, including Outlook integration and automated mail sending.

Why Choose NSNY’s Advanced Excel Course?

  • Expert Instruction: Learn from experienced Excel professionals who provide expert guidance and support throughout the course.
  • Hands-On Learning: Engage in practical exercises and projects that reinforce your learning and allow you to apply advanced Excel techniques.
  • Career Advancement: Gain valuable skills and certifications to boost your resume and excel in your career.
  • Flexible Learning Options: Choose from online or in-person classes to fit your schedule and learning preferences.

Diploma in Advance Excel

Details Syllabus  –

 

Introduction to Excel

A description of the interface, the menu system, and the fundamentals of spreadsheets

  • Various methods of selecting
  • Short Cut Keys

Personalising Excel

  • Changing Excel’s Default Options Using AutoCorrect and Customizing It Customizing the Ribbon

Understanding and Using Basic Functions

  • Using Functions – Sum, Average, Max, Min, Count, Counta
  • Absolute, Mixed, and Relative Referencing

Text Functions

  • Upper, Lower, Proper
  • Left, Mid, Right
  • Trim, Len, Exact
  • Concatenate
  • Find, Substitute

Arithmetic Functions

  • SumIf, SumIfs CountIf, CountIfs AverageIf, AverageIfs

Proofing and Formatting

  • Formatting Cells with Number formats, Font formats, Alignment, Borders, etc
  • Basic conditional formatting

Protecting Excel- Excel Security 

  • File Level Protection
  • Workbook, Worksheet Protection

Printing Workbooks

  1. Setting Up Print Area
  2. Customizing Headers & Footers
  3. Designing the structure of a template
  4. Print Titles –Repeat Rows / Columns

Advance Paste Special Techniques

  1. Paste Formulas, Paste Formats
  2. Transpose Tables
  3. Paste Validations

Time and Date Functions

  1. Today, Now
  2. Date, Date if, DateAdd
  3. Day, Month, Year
  4. Month, Weekday

New in Excel 2013 / 2016 & 365

  1. New Charts – Tree map & Waterfall
  2. Combo Charts – Secondary Axis
  3. Sunburst, Box, and whisker Charts
  4. Using Power Map and Power View
  5. Adding Slicers Tool in Pivot & Tables
  6. Sparklines -Line, Column & Win/ Loss
  7. Forecast Sheet
  8. Smart Lookup and manage Store
  9. New Controls in Pivot Table – Field, Items, and Sets
  10. Using 3-D Map
  11. Auto complete a data range and list
  12. Various Time Lines in Pivot Table
  13. Quick Analysis Tool

Filtering and Sorting

  1. Filtering on Text, Numbers & Colors
  2. Sorting Options
  3. Advanced Filters on 15-20 different criteria(s)

Printing Workbooks

  1. Setting Up Print Area
  2. Print Titles –Repeat Rows / Columns
  3. Designing the structure of a template
  4. Customizing Headers & Footers

 

Advance Excel

What-If Analysis

  1. Goal Seek
  2. Data Tables (PMT Function)
  3. Solver Tool
  4. Scenario Analysis

Data Validation

  1. Number, Date & Time Validation
  2. Dynamic Dropdown List Creation using Data Validation – Dependency List
  3. Custom validations based on a formula for a cell
  4. Text and List Validation

Logical Analysis

  1. If Function
  2. Complex if and or functions
  3. Nested If
  4. How to Fix Errors – iferror

Lookup Functions

  1. Vlookup / HLookup
  2. Vlookup with Helper Columns
  3. Creating Smooth User Interface Using Lookup
  4. Index and Match
  5. Reverse Lookup using Choose Function
  6. Nested VLookup
  7. Worksheet linking using Indirect

Arrays Functions

  1. What are the Array Formulas, Use of the Array Formulas?
  2. Array with if, len, and mid functions formulas.
  3. Basic Examples of Arrays (Using ctrl+shift+enter).
  4. Advanced Use of formulas with Array.
  5. Array with Lookup functions.

Pivot Tables

  1. Creating Simple Pivot Tables
  2. Classic Pivot table
  3. Basic and Advanced Value Field Setting
  4. Calculated Field & Calculated Items
  5. Grouping based on numbers and Dates

Excel Dashboard

  1. Planning a Dashboard
  2. Adding Dynamic Contents to Dashboard
  3. Adding Tables and Charts to Dashboard

Slicers and Charts

  1. Using SLICERS, Filter data with Slicers
  2. Various Charts i.e. Bar Charts / Pie Charts / Line Charts
  3. Manage Primary and Secondary Axis

VBA Macro

Introduction to VBA

  1. What Is VBA?
  2. Procedure and functions in VBA
  3. Recording a Macro
  4. What Can You Do with VBA?

Variables in VBA

  1. What are Variables?
  2. Using Non-Declared Variables
  3. Using Const variables
  4. Variable Data Types

Input box and Message Box Functions

  1. Customizing Msgboxes and Inputbox
  2. Reading Cell Values into Messages
  3. Various Button Groups in VBA

 

If and select statements

  1. Simple If Statements
  2. Defining select case statements
  3. The Elseif Statements

Looping in VBA

  1. Introduction to Loops and its Types
  2. Exiting from a Loop
  3. Advanced Loop Examples
  4. The Basic Dos and For Loop

Worksheet / Workbook Operations

  1. Merge Worksheets using Macro
  2. Split worksheets using VBA filters
  3. Worksheet copiers
  4. Merge multiple excel files into one-sheet

Mail Functions – VBA

  1. Using Outlook Namespace
  2. Outlook Configurations, MAPI
  3. Send automated mail

Course Duration :- 12 months

Course Fees :- 16000/-