Accounting with Excel

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
  • Protecting your Worksheets
  • 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

Voucher, Ledger, Trial Balance, Income Statement, Balance Sheet

Readiness for Auditing