Excel Essential Course (20)

Chapter 1 – Introduction & Basics of Excel

• Basic Excel Introduction
• Useful Keyboard Shortcut
• How to prepare Proper Dataset
• Relative and Absolute cell reference in excel

Chapter 2 – Excel Functions

• AVERAGE, MAX, MIN, ROUND, TRUNC, COUNT, COUNTA
• LOWER, UPPER, PROPER, CONCATENATE
• LENGTH, MID, VALUE, LEFT, RIGHT
• COUNTIF, SUMIF, AVERAGEIF
• COUNTIFS, SUMIFS, AVERAGEIFS

Chapter 3 – Efficient Use of Filter, Sort

• Filter shortcut CTRL + SHIFT + L
• Basic shorting, shorting by color
• Text filter, Number filter, Date filter
• Using AND, OR in filter condition

Chapter 4 – Data Functions Subtotal and Advanced Filter

• Use Subtotal function for easy in analysis
• One click report with Advanced Filter

Chapter 5 – Working with Range and Tables

• What is a Table?
• Creating Tables
• Changing the Table Range
• Inserting Table Columns
• Inserting Table Rows
• Deleting Rows or Columns
• Creating a Table Total Row
• Introduction to Name Range
• Add, Edit, Delete Name Range

Chapter 6 – Different Types of Lookup in Excel

• Using VLOOKUP to Find Data
• How to Find an Exact Match with VLOOKUP
• Finding the Closest Match with VLOOKUP
• How to use the MATCH Function
• Index Function
• Lookup using Match and Index Function

Chapter 7 – Conditional & Logical Formula, Conditional Formatting

• The IF function
• Using the Function Library
• Manually entering a function
• Conditional Formatting
• Applying Conditional Formatting
• Conditional Formatting
• Creating a Conditional Formatting Rule
• Removing Conditional Formatting
• Using Logical Functions
• Manually entering a function
• The IF function
• The AND Function
• The OR Function
• Nested IF functions

Chapter 8 – Excel Pivot tables, Pivot Chart and Dashboard

• What is a PivotTable?
• Preparing Data to Create a PivotTable
• Creating a PivotTable
• Quick Analysis
• Adding Fields to the PivotTable
• Creating a PivotTable Frame (Classic PivotTable Layout)
• Rearranging PivotTable Data
• Hiding and Showing Field Data
• The PivotTable Tools Ribbon
• Modifying Calculations and Data Area Display
• Formatting a PivotTable
• Refreshing a PivotTable
• Time Grouping in PivotTables (Excel )
• Using Slicers to Manipulate PivotTables
• Use Timelines with Pivot Data
• Creating a PivotChart

Chapter 9 – Protecting Excel Data

• Excel Workbook and Worksheet Protection
• Unlocking Cells

Chapter 10 – Page Layout & Page Setups in Excel

• Printing Large Worksheets
• Print Titles
• Page Break Preview
• Inserting a Page Break
• Printing Multiple Sheets

Accounting Output with Excel (20 hrs)

Payroll (Payroll for small scale business)

• Employee record
• Monthly TDS
• Salary calcualtion sheet
• Salary Sheet

Point of Sale (Inventory, Stock, Sales Report)

• Daily sales data entry
• Monthly sales closing
• Monthly sales and inventory report
• Stock report in a single click

School Student Management (Student record, Result, Billing)

• Students Database
• Exam result entry sheet
• Result Sheet
• Billing form and billing history