Why Advance Excel?

With the help of  advance Excel tools, you can create more complex calculations in its simplest form, Excel is a  spreadsheet software. It allows you to carefully organize all of your data while providing you with the ability to sort the information in any way that you choose, with advanced skills in Excel, you can do more. Learn Excel so You can solve business problems with advanced data applications. You can apply tracking systems for a variety of departments and operations. Advanced Excel Course skills give you the ability to design professional-level spreadsheets.

What you will Learn in Advance Excel?

Overview of the Basics of Excel
Working with Functions
Data Validations
Working with Templates
Sorting and Filtering Data
Working with Reports
Creating Pivot tables
More Functions
WhatIf Analysis
Charts
power of Microsoft Excel by automating  day to day tasks through Macros and VBA

Course Overview

MODULE - 1

Lecture1.1 Introduction to MS Excel, Quick review on MS Excel Options, Ribbon, Sheets
Lecture1.2 Difference between Excel 2003, 2007, 2010 and 2013
Lecture1.3 Saving Excel File as PDF, CSV and Older versions
Lecture1.4 Using Excel Shortcuts with Full List of Shortcuts
Lecture1.5 Copy, Cut, Paste, Hide, Unhide, and Link the Data in Rows, Columns and Sheet
Lecture1.6 Using Paste Special Options
Lecture1.7 Formatting Cells, Rows, Columns and Sheets Lecture1.8 Protecting & Unprotecting Cells, Rows, Columns and Sheets with or without Password
Lecture1.9 Page Layout and Printer PropertiesInserting Pictures and other objects in Worksheets
Lecture1.10 Lookup and Reference Functions: VLOOKUP, HLOOKUP, INDEX, MATCH, etc
Lecture1.11 Logical Function: IF / ELSE, AND, OR, NOT, TRUE, NESTED IF/ELSE etc
Lecture1.12 Database Functions Date and Time Functions: DATE, DATEVALUE, DAY, DAY360, SECOND, MINUTES, HOURS, NOW, TODAY, MONTH, YEAR, YEARFRAC, TIME, WEEKDAY, WORKDAY etc
Lecture1.13 Information Functions : Math and Trigonometry Functions: RAND, ROUND, CEILING, FLOOR, INT, LCM, MOD, EVEN, SUMIF, SUMIFS etc
Lecture1.14 Statistical Functions: AVEDEV, AVERAGE, AVERAGEA, AVERAGEIF, COUNT, COUNTA, COUNTBLANK, COUNTIF,FORECAST, MAX, MAXA,MIN, MINA, STDEVA etc
Lecture1.15 Text Functions: LEFT, RIGHT, TEXT, TRIM, MID, LOWER, UPPER, PROPER, REPLACE, REPT, FIND, SEARCH, SUBSTITUTE, TRIM, TRUNC, CONVERT, CONCATENATE, DOLLAR etc And More
Lecture1.16 Using Conditional Formatting
Lecture1.17 Using Conditional Formatting with Multiple Cell Rules
Lecture1.18 Using Color Scales and Icon Sets in Conditional Formatting
Lecture1.19 Creating New Rules and Managing Existing Rules
Lecture1.20 Sorting Data A-Z and Z-A
Lecture1.21 Using Filters to Sort Data
Lecture1.22 Advance Filtering Options
Lecture1.23 Creating Pivot Tables
Lecture1.24 Using Pivot Table Options
Lecture1.25 Changing and Updating Data Range
Lecture1.26 Formatting Pivot Table and Making Dynamic Pivot Tables
Lecture1.27 Creating Pivot Charts
Lecture1.28 Types of Pivot Charts and Their Usage
Lecture1.29 Formatting Pivot Charts and Making Dynamic Pivot Charts
Lecture1.30 Advanced Filtering
Lecture1.31 Data validation
Lecture1.32 Consolidation
Lecture1.33 Groups
Lecture1.34 Subtotal
Lecture1.35 What is Excel Macro ?
Lecture1.36 How to Record / Run Excel Macro ?
Lecture1.37 How Add Developer’s Tab in Excel 2007 / 2010
Lecture1.38 How to add different types of controls like Text Box, Radio button, button etc. in Excel

MODULE - 2

Lecture2.1 Visual Basic Editor in Excel
Lecture2.2 VBA Control Property
Lecture2.3 How to write Excel Macro – Your First Excel Macro
Lecture2.4 Excel Macro Tutorial : Excel User Form
Lecture2.5 VBA Spin Button
Lecture2.6 Toggle Button in Excel
Lecture2.7 ComboBox / Drop Down List In Excel
Lecture2.8 ListBox In Excel
Lecture2.9 How to Open Excel Workbook using Excel Macro
Lecture2.10 Using RANGE Object in Excel Macro
Lecture2.11 What is difference between CELLS() and RANGE()
Lecture2.12 Excel Macros, Excel Terms, Macro comments
Lecture2.13 Userforms
Lecture2.14 Variables in Excel VBA
Lecture2.15 Implicit and Explicit declaration of variables in Excel Macro
Lecture2.16 Important : Declaring multiple Variables with one Dim Statement
Lecture2.17 For Next Loop in Excel Macro
Lecture2.18 While Loop and Do While Loop in Excel VBA
Lecture2.19 VBA Programming : If Then Statements / If else Statements
Lecture2.20 VBA Programming : Decision – Select Case in Excel VBA
Lecture2.21 Variables in Excel VBA
Lecture2.22 Implicit and Explicit declaration of variables in Excel Macro
Lecture2.23 Important : Declaring multiple Variables with one Dim Statement
Lecture2.24 For Next Loop in Excel Macro
Lecture2.25 While Loop and Do While Loop in Excel VBA
Lecture2.26 VBA Programming : Decision – Select Case in Excel VBA
Lecture2.27 Events, Error Handling
Lecture2.28 Excel Objects, Programming Charts
Lecture2.29 Message box, Input box
Lecture2.30 Introduction to SQL
Lecture2.31 Creating New Tables
Lecture2.32 SQL SELECT Statements
Lecture2.33 Manipulating Data(Insert, Delete, Update)
Lecture2.34 Distinct, Order By, Join clauses and Aggregate Functions
Lecture2.35 Using Primary keys, Foreign keys
Lecture2.36 Get Data from Multiple Tables
Lecture2.37 Using DDL Statements
Lecture2.38 Using Single-row Functions
Lecture2.39 Restricting and Sorting Data
Lecture2.40 Conversion Functions
Lecture2.41 Conditional Expressions
Lecture2.42 Using the Group Functions
Lecture2.43 Subqueries to solve queries