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 2007, 2010, 2013, 2016 and Office 365, its Boundaries & Features
-
Formatting Worksheets, Securing & Protecting Spreadsheets
-
All Tabs of Microsoft Excel 2013, 2016, Office 365
-
Simple Data Entry Number, Text, Date, Selecting Range
-
Cut, Copy, Paste, Format Printer and Move Data.
-
Saving Excel Format
LOOKUP FUNCTIONS:
-
The LOOKUP function, Date and time functions
-
Annotating formulas, Lookup, V-Lookup,
-
H-Lookup, Match, Index, Usage Of Match Function
-
Hlookup And Index, 2D & 3D Lookup, Mixed Lookup
-
Vlookup with Iferror, Multiple Vlookup, Vlookup with Choose
-
Reverse VLookup, Index with Double Match
DEVELOP THE WORKSHEET:
-
Plan a 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
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
-
BASIC FUNCTIONS:
-
Add, Subtract, Multiplication, Divide, Sum and Count
-
Auto Sum, Formatting a Cell- Number, Text, %
-
Date and Custom Format. Font , Borders, Fill Colors
-
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
-
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, Seriessum
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
GRAPHIC OPERATIONS
-
Create Charts, Enhance Charts, Drawing toolbar features
-
Print Preview, Print Setup, Page Layout, Page-break
-
Normal View, Headers & Footer, Print Title
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, Combine
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
DATA ANALYSIS, ADVANCE OPTIONS:
-
Advance Filter and Sorting Option in Excel
-
Filtering and Sorting By Color
-
Various Option of Text to Column
-
Remove Duplicate Record, Data Validation
-
Protecting and Sharing a Worksheet/Workbook
-
Freezing Pane, Hyperlink
-
Sub Total Reports, Auto Filter, Sheet Referencing
-
What-IF-analysis, GOAL SEEK, Reporting, Consolidation of Data, Data Validation
EXCEL MACRO:
-
Introduction and use
-
Assign a Macro, Run a Macro
-
Store a Micro in Different Worksheets
-
Introducing to VBA Program.