ADVANCED EXCEL

An expert in advanced excel is a good asset for any organization, this tool is very much helpful in analyzing data, making reports, dashboards etc.  these are major benefits of going for an advanced training in Microsoft Excel.

Our trainers have the practical and industry based knowledge, which is very beneficial for the students or professionals who want to learn advanced excel.  In the industry, practical data analysis is necessary for making MIS reports and all.

Analytical Edupoint is best Excel Training Institute in Delhi and NCR

INTRODUCTION TO EXCEL:
➢ An Overview of Excel Screen
➢ Basic Concept of Spreadsheet and Workbook (Creating, Editing Saving, and printing)
➢ Excel Terminology: – Rows, Column, and Cell
➢ Working with functions & Formulas
➢ Graphically representing data: Charts and Graphs
➢ Analyzing Data: Data Menu Subtotal, Filtering Data
➢ Difference between Excel 2003, 2007, 2010, 2013 and 2016, its Boundaries & Features
➢ Formatting Worksheets, Securing & Protecting Spreadsheets
➢ All Tabs of Microsoft Excel 2010/13 / 16
➢ Simple Data Entry Number, Text, Date, Selecting Range, Cut, Copy, Paste, Format Printer and Move Data.
➢ Saving Excel Format.

BASIC FUNCTIONS:
➢ Add, Subtract, Multiplication, Divide, Sum and Count, Auto Sum, Formatting a Cell- Number, Text, %, Date and Custom Format. Font, Borders, Fill Colors And Patterns, Conditional Formatting, Format A Table, Cell Style
➢ Use the Function wizard, Common functions (AVERAGE, MIN, MAX, COUNT, COUNTA, ROUND, INT)
➢ Text functions, Statistical function, Mathematical Function, Financial Function
➢ Logical Functions Using IF, AND, OR, NOT
➢ Nested functions, Name cells/ranges/constants
➢ Relative Absolute, Mixed cell references:><,=operators

ADVANCE EXCEL FUNCTIONS/FORMULAS:

➢ Sum, Count, Counta, Countifs, Subtotal, Average, Ceiling, Floor, Round, Roundup, Rounddown, Chart, Text, Clean, Trim, Concatenate, Substitute, Hour, Date, Value, Day, Month, Year, Day, Today. Exact, Forecast, Getpivotdata, Sumproduct, Frequency, Rank, Rand, Randbetween. Transpose, Match, Upper, Lower, Proper, Left, Right, Mid, Row, Column, Combin.

LOOKUP FUNCTIONS:
➢ The LOOKUP function, Date and time functions, Annotating formulas
➢ Lookup, V-Lookup, H-Lookup, Match, Index, Usage Of Match Function In Vlookup, Hlookup, And Index

➢ Reverse V_Lookup, Index with Double Match Function, Picture Lookup Function, 15-20 types of other lookup functions

 

LOGICAL & MATHEMATICAL FUNCTIONS:
➢ If, Sumif, Sumifs, Countifs, Nested If (Usage of More Than 1 If Condition Within 1 Formula), Iferror, Iserror, Isna, And, Or, False, Not, True, Ceiling, Even, Int, Lcm, Power, Odd, Sumif, Sumproduct, Series sum

DESCRIPTION OF EXCEL MENU
➢ Paste Special, Paste Formula, Paste Format, Skip Blank and Other Paste Special Option, Find, Replace and Merge & Center Option, Copying/ Moving/ Renaming, Inserting/ Deleting/ Grouping / Hiding & Un-hiding Worksheets, Hiding and Displaying Data, Rows, Columns, Worksheet & Workbooks

DEVELOP THE WORKSHEET:
➢ Plan a new Worksheet, Row and Column labels
➢ Split Worksheet/box/bar, copy data and formulas
➢ Display/Move Toolbars, Enhance worksheet appearance
➢ Use multiple windows: Copy/Paste between worksheets
➢ Link Worksheets, Consolidate worksheets
➢ Important link from other Applications
➢ Use Auto Format: Create, use and modify styles and templates
➢ Print Features: Create/edit an outline

USING PIVOT TABLE:
➢ Preparing Pivot Table
➢ Using Pivot Table Wizard
➢ Arranging Data
➢ Various Options in Pivot Table
➢ Effective and Attractive Summarized Presentation of Data Using Pivot Tables
➢ Modifying a Pivot Table
➢ Pivot Table Option

DATA ANALYSIS, ADVANCE OPTIONS:
➢ Advanced Filter and Sorting Option in Excel
➢ Filtering and Sorting By Color
➢ Various Option of Text to Column
➢ Remove Duplicate Record
➢ Data Validation
➢ Freezing Pane
➢ Protecting and Sharing a Worksheet/Workbook
➢ Hyperlink
➢ Sub Total Reports, Auto Filter, Sheet Referencing
➢ What-IF-analysis, GOAL SEEK, Reporting, Consolidation of Data, Data Validation

CHART & DESIGNS & Dashboard
➢ Preparing Charts Using Excel – Column Chart, Pie Chart, Line Chart, Bar Chart, Bubble Chart, Area of Chart, Editing Chart Contents, Formatting and Modifying Chart, Adding Data to a Chart

GRAPHIC OPERATIONS
➢ Create Charts, Enhance Charts, Drawing toolbar features

PRINTING TECHNIQUES:
➢ Print Preview, Print Setup, Page Layout, Page-break & Normal View, Headers & Footer, Print Title

EXCEL MACRO:
➢ Introduction and use
➢ Assign a Macro, Run a Macro
➢ Store a Micro in Different Worksheets
➢ Introducing to VBA Program.