Topics
Contents
Introduction to Excel
Modules
o An Overview Of Excel Screen, Basic Concept Of Spreadsheet And Workbook
o Excel Terminology - Rows, Column And Cell
o Simple Data Entry- Number, Text, Date
o Selecting Range, Cut, Copy, Paste, Format Painter and Move Data. Saving Excel Format.
o Description Of Excel Menu
o Paste Special, Paste Formula, Paste Format, Skip Blank And Other Paste Special Option
o Find, Replace And Merge & Centre Option
o Copying/ Moving/ Renaming, Inserting/ Deleting/ Grouping / Hiding & Un-hiding Worksheets
o Hiding And Displaying Data, Rows, Columns, Worksheet & Workbooks
Import Data In Excel
o Import data from .txt files
o Import data from .csv files
o Import Data from Website
o Import Data From Database
BASIC FUNCTIONS:
o Basic Formula Like Add, Subtract, Multiplication, Divide.
o Sum and Count Function. Auto Sum
o Formatting A Cell- Number, Text, %, Date And Custom Format. Font , Borders, Fill Colors And Patterns, Conditional Formatting, Format A Table, Cell Style
EXCEL FUNCTIONS/FORMULAS:
o Sum, Count, Counta, Countifs, Subtotal, Average, Ceiling, Floor, Round, Roundup, Rounddown.
o Chart, Text, Clean, Trim, Concatenate, Substitute, Hour, Date, Value, Day, Month, Year, Day, Today.
o Sumproduct, Rank, Rand, Randbetween.
o Transpose, Match, Upper, Lower, Proper, Left, Right, Mid, Row, Column, Combin,
LOOKUP FUNCTIONS:
o Lookup, V-Lookup, H-Lookup, Match, Index
o Usage Of Match Function In Vlookup, Hlookup And Index
LOGICAL & MATHEMATICAL FUNCTIONS:
o If, Sumif, Sumifs, Countifs, Nested If
o Iferror, Iserror, Isna, And, Or, False, Not, True
o Sumif, Sumproduct
USING PIVOT TABLE:
o Preparing Pivot Table, Using Pivot Table Wizard, Arranging Data, Various Options In Pivot Table
o Effective And Attractive Summarized Presentation Of Data Using Pivot Tables
o Modifying A Pivot Table, Pivot Table Option
ADVANCE OPTIONS:
o Advance Filter and Sorting Option in Excel, Filtering and Sorting By Color.
o Various Option Of Text To Column, Remove Duplicate Record, Data Validation, Freezing Pane
o Protecting And Sharing A Worksheet/Workbook, Hyperlink
CHART & DESIGNS:
o Preparing Charts Using Excel - Column Cart, Pie Chart, Line Chart, Bar Chart, Bubble Chart
o Area Of Chart, Editing Chart Contents, Formatting And Modifying Chart, Adding Data To A Chart
PRINTING TECHNIQUES:
o Print Preview, Print Setup, Page Layout, Page-break & Normal View
o Headers & Footer, Print Title
EXCEL MACRO:
o Introduction To Macro And VBA Editor
o Usage Of Macro
o Recording And Saving A Macro
o Running Macro In Different Worksheets
MIS Report / Data Analysis / Dashboard (Project)
o Project related to Profit and Loss Analysis
o Survey Data Analysis
o MIS Reports, Weekly, Monthly and Year on Year and Month on Month Analysis