75% OFF

Learn, Practice & Master Excel with Real-World Applications

Live + Recorded Classes | Hands-On Projects | Practical Business Applications

Course Outline

Day 1: Introduction to Excel for Accounting

• Understand the structure of Excel: workbooks, worksheets, rows, columns, cells
• Customize the Excel interface for productivity
• Configure essential accounting settings (number format, date locale, currency)
• Learn key terminology used in Excel for finance

---

Day 2: Professional Data Entry & Formatting

• Efficient techniques for accurate data input
• Apply accounting-specific number formats (currency, percentage, decimal)
• Master professional formatting for reports and ledgers
• Use AutoFill, Format Painter, and cell styles for speed

---

Day 3: Essential Excel Functions (Part 1)

• Perform basic calculations using SUM, AVERAGE, COUNT, MIN, MAX
• Understand the difference between COUNT, COUNTA, and COUNTBLANK
• Use cell referencing (relative, absolute, mixed) for accuracy
• Apply basic formulas to accounting examples

---

Day 4: Data Summarization Functions for Accountants

• Use SUBTOTAL() to summarize data dynamically (ignores hidden rows)
• Learn how to use AGGREGATE() for advanced summarization (e.g., ignoring errors, filtered data)
• Create running totals using formulas (cumulative debit/credit)
• Use SUMIFS, COUNTIFS, and AVERAGEIFS for condition-based summarization
• Build monthly, quarterly, and yearly summaries from raw transaction data
• Learn when to use DSUM() and other Database Functions
• Explore quick analysis and summary tools (Status Bar, Quick Totals, AutoSum variations)

---

Day 5: Logical Functions for Accounting Scenarios

• Use IF, AND, OR, IFS, and SWITCH functions for conditional logic
• Create multi-condition formulas using Nested IF
• Apply logic to credit/debit checks, late payment flags, etc.

---

Day 6: VLOOKUP and HLOOKUP for Account Lookup

• Lookup data from tables using VLOOKUP and HLOOKUP
• Use approximate vs exact match for tax rates, product prices
• Solve real-world lookup problems like account code mapping

---

Day 7: Advanced Lookup with INDEX-MATCH & XLOOKUP

• Replace VLOOKUP with INDEX-MATCH for better performance
• Combine MATCH with OFFSET for dynamic referencing
• Use XLOOKUP for flexible lookups

---

Day 8: Working with Date & Time Functions

• Use DATE, TODAY, NOW, EOMONTH, and NETWORKDAYS for calendar calculations
• Create month-end closing logic and due date calculators
• Format and manipulate date fields accurately

---

Day 9: Text Functions for Cleaning & Reporting

• Use LEFT, RIGHT, MID, CONCAT, TEXT, TRIM, UPPER/LOWER
• Clean messy import data (from Tally, SAP, etc.)
• Format account codes, customer IDs, and reports

---

Day 10: Data Validation & Entry Control

• Create drop-down lists (for account heads, months, vendors)
• Apply rules for numbers, dates, ranges, and text length
• Use custom formulas for validation and error messages

---

Day 11: Conditional Formatting for Financial Data

• Highlight overdue balances, high-value transactions, duplicates
• Use data bars, icon sets, and color scales for quick insights
• Set up visual alerts for errors or targets

---

Day 12: Sorting and Filtering in Large Datasets

• Use multi-level sorting for ledger, journal, and invoice records
• Apply advanced filter conditions
• Create custom filters and search tools

---

Day 13: Excel Tables and Dynamic Named Ranges

• Convert datasets into Excel Tables for smarter references
• Use structured references in formulas
• Create dynamic named ranges for dropdowns and dashboards

---

Day 14: Pivot Tables for Account Summary

• Build Pivot Tables for sales, expenses, journal entries
• Group data by date, category, or account head
• Customize field layout and number formats

---

Day 15: Advanced Pivot Table Analysis

• Add calculated fields for profit margin, tax, or net value
• Use slicers for interactive filtering
• Summarize by month, department, product

---

Day 16: Profit & Loss and Balance Sheet in Excel

• Design dynamic P&L and Balance Sheet templates
• Use formulas to auto-fill totals and sub-totals
• Make your statements printable and ready for clients or auditors

---

Day 17: Bank Reconciliation Format & Techniques

• Prepare structured templates for bank reconciliation
• Identify unmatched transactions using formulas
• Automate closing balances and reconciliation status

---

Day 18: Invoice Template with Auto Calculations

• Build VAT-compliant invoice templates
• Use formulas for tax, discount, subtotal, and grand total
• Enable auto-numbering and dropdowns for service/items

---

Day 19: Visualizing Accounts with Excel Charts

• Create Column, Pie, and Line charts for expense/income
• Compare monthly or quarterly performance visually
• Format charts professionally for board reports

---

Day 20: Designing Dashboards for Accountants

• Build interactive dashboards using Pivot, Charts, and Form Controls
• Add KPIs like Cash Flow, Receivables, or Expense Ratios
• Link reports with summary tables and slicers

---

Day 21: Introduction to Power Query (Data Cleanup & Merge)

• Import and clean messy accounting data from multiple sources
• Combine multiple ledgers, sales reports, or invoices into one table
• Unpivot, split columns, and transform data for analysis

---

Day 22: VBA Concept Class – Automation for Accountants

• Understand what VBA is and how it can help accountants
• Record your first Macro for repeating tasks
• Introduction to MsgBox, InputBox, and simple VBA buttons
• Real-world automation use cases (e.g., auto-formatting, PDF generation)

---

 

What You’ll Achieve by the End of This Course?

• Set up and manage accounting data in Excel using professional templates and best practices
• Apply key Excel functions (SUM, IF, VLOOKUP, XLOOKUP, SUMIFS, TEXT, DATE, etc.) to solve real accounting problems
• Create dynamic and error-free financial reports such as Profit & Loss statements, Balance Sheets, Cash Flow summaries, and Bank Reconciliation formats
• Analyze large datasets using PivotTables, sorting, filtering, and condition-based summaries
• Design and format professional invoice templates with automatic calculations for tax, discount, and totals
• Implement drop-down lists and validation rules to maintain data integrity in ledgers and forms
• Clean and prepare raw accounting data using Text functions and Power Query tools
• Use Conditional Formatting to highlight key metrics like overdue amounts, top expenses, and duplicates
• Summarize financial records using advanced techniques like SUBTOTAL, AGGREGATE, and SUMIFS with multiple criteria
• Visualize financial data through charts and dashboards to present KPIs, trends, and category-wise breakdowns
• Build and use dynamic Excel dashboards for monthly, quarterly, and annual reporting
• Automate simple tasks using Excel VBA, including creating buttons for data entry, formatting, or generating reports
• Gain practical Excel confidence to work faster, smarter, and with more accuracy in real accounting environments
• Complete a mini project by integrating all skills learned — from data entry to final report generation
• Become job-ready and upskill yourself for roles that demand advanced Excel proficiency in accounting, auditing, or finance

Join Excel Point Nepal Pvt. Ltd. today and take your Excel skills to the next level!