TRACK 1: FOUNDATIONAL TO OFFICE PRODUCTIVITY
Basic Microsoft Excel Course
Specially designed for beginners, office assistants, accountants, and college students. Learn essential spreadsheet navigation, daily billing calculations, inventory sheets, and formatting standards with hands-on practice files.
Module 1
Excel Interface, Cell Navigation & Workbook Setup
Master the fundamental building blocks of Microsoft Excel. Understand how workbooks, worksheets, columns (A, B, C... up to 16,384), rows (1 to 1,048,576), and cells are structured. Learn vital navigation shortcuts, Name Box coordinate addressing, Formula Bar manipulation, and freezing header rows so massive data remains easy to read.
Key Concepts Covered:
- Workbook vs. Worksheet: Adding, renaming, color-coding, and reordering tabs.
- Cell Coordinate System: Understanding cell addresses like A1, D15, and ranges like A1:D20.
- Freezing Panes: Locking top header rows (View > Freeze Panes) so headers stay visible when scrolling down hundreds of records.
- Essential Keyboard Shortcuts: Ctrl + Home (jump to A1), Ctrl + Arrow Keys (jump to data boundary), and Ctrl + PgDn/PgUp (switch sheets).
Real-World Practice Scenario: Office Inventory Register
Imagine you are the office coordinator for an educational institute in Haldwani. You need to record supplies (copier paper, markers, keyboards) with frozen header rows, clean column widths, and automatic reorder warnings.
| Item Code |
Product Name |
Category |
Stock Qty |
Unit Price (₹) |
Reorder Level |
Status |
| ITM-101 |
A4 Copier Paper (500 sheets) |
Stationery |
85 |
₹260.00 |
20 |
IN STOCK |
| ITM-103 |
Wireless Optical Mouse |
Electronics |
18 |
₹450.00 |
10 |
IN STOCK |
| ITM-107 |
Spike Surge Protector 4-Way |
Electrical |
5 |
₹420.00 |
6 |
REORDER NEEDED |
Live Formula Used in Status Column: =IF(D5<=F5, "REORDER NEEDED", "IN STOCK")
Module 2
Data Entry, Cell Formatting & Smart AutoFill
Unformatted data is difficult to analyze and unappealing to clients. Learn to apply professional business formatting standards, Indian Rupee (₹) currency conventions, date formats, border styles, text wrapping, and how to automate repetitive typing using Excel's intelligent AutoFill drag handle.
Key Concepts Covered:
- Number Formatting: Displaying Indian Currency ₹#,##0.00, Percentages 0.0%, and standard dates DD-MMM-YYYY.
- Text Alignment & Wrap Text: Fitting multiline notes inside a cell without breaking column widths.
- Center Across Selection: The professional alternative to Merge & Center that centers titles without destroying column sorting capability.
- Smart AutoFill Sequences: Dragging months (Jan, Feb...), days (Mon, Tue...), custom numerical steps (10, 20, 30...), and Flash Fill (Ctrl + E).
Real-World Practice Scenario: Staff Duty Roster & Attendance
Quickly creating a 30-day shift planner using AutoFill drag for dates (01-Aug to 31-Aug) and days of the week, with color-coded "Present" (Green) and "Absent" (Amber) indicators.
Pro Tip Taught in Lab: Never manually type 30 dates in a row! Type 01-Aug-2026, grab the lower-right fill handle, and drag rightward. Excel automatically completes the full month.
Module 3
Core Mathematical & Statistical Formulas
Formulas are the heartbeat of Excel. In this module, you move from manual arithmetic into Excel's most powerful native mathematical functions. Learn how to calculate totals, compute percentages, find averages, count entries, and identify minimum and maximum benchmark figures effortlessly.
Primary Functions Mastered:
=SUM(range)
Adds all numerical values in a range. Shortcut: Alt + =
=AVERAGE(range)
Calculates the arithmetic mean of scores or sales figures.
=MAX(range) & =MIN()
Extracts the highest and lowest scores or prices instantly.
=COUNTA(range)
Counts non-empty text or number cells (e.g., student roster count).
Real-World Practice Scenario: Academic Marksheet & Grade Sheet
A complete student evaluation sheet calculating total marks across 5 subjects, overall percentage, Pass/Fail status, and summary rows identifying the Class Topper and Class Average.
| Roll No |
Student Name |
Comp Fund |
MS Word |
MS Excel |
Total |
Percentage |
Result |
| DSA-01 |
Rohit Bisht |
88 |
79 |
92 |
=SUM(C5:E5) |
=AVERAGE(C5:E5) |
PASS |
| DSA-06 |
Anjali Rawat |
95 |
91 |
98 |
284 |
94.7% |
PASS |
Summary Row Formula: Topper Marks = =MAX(F5:F12) | Average Score = =AVERAGE(G5:G12)
Module 4
Cell Referencing Rules: Relative vs. Absolute ($A$1)
The single most common mistake beginners make in Excel is formula drifting when dragging across rows. In this module, you master the dollar sign ($) locking rule and the F4 shortcut to anchor specific cells for tax rates, commission percentages, or discount targets.
The 3 Types of Cell References:
- Relative Reference (A1): Adjusts automatically when copied to another cell (e.g. =B5*C5 becomes =B6*C6).
- Absolute Reference ($A$1): Completely locks both column and row. When copied anywhere, it continues pointing to cell $A$1. Press F4 once to toggle!
- Mixed Reference ($A1 or A$1): Locks either only the column or only the row (crucial for multiplication tables and matrix models).
Real-World Practice Scenario: GST Sales Invoice Billing
A retail electronic shop invoice in Haldwani. The GST tax rate (18%) is held in fixed cell $B$4. Every invoice row must multiply its item subtotal by that exact locked cell.
| Item Description |
Qty |
Unit Price (₹) |
Net Total (₹) |
GST 18% (₹) |
Total Amount (₹) |
| Dell 24-inch IPS Monitor |
4 |
₹11,500.00 |
₹46,000.00 |
=E7*$B$4 |
₹54,280.00 |
| Logitech Wireless Combo |
12 |
₹1,250.00 |
₹15,000.00 |
=E8*$B$4 |
₹17,700.00 |
Why it matters: If you wrote =E7*B4 without dollar signs, dragging down would incorrectly read =E8*B5 (empty cell), breaking your entire bill!
Module 5
Conditional Logic Functions: IF, COUNTIF & SUMIF
Automate decision-making and selective aggregations. Learn how to test logical conditions using IF, count items that meet specific criteria using COUNTIF, and total expenses or sales by category using SUMIF.
Primary Functions Mastered:
- =IF(condition, value_if_true, value_if_false): Evaluates tests like =IF(Score>=50, "Pass", "Fail").
- =COUNTIF(range, criteria): Counts occurrences (e.g. =COUNTIF(StatusRange, "Approved")).
- =SUMIF(range, criteria, sum_range): Sums values conditionally (e.g. =SUMIF(CategoryRange, "Stationery", AmountRange)).
Real-World Practice Scenario: Office Expense Audit & Breakdown
An institution expense ledger with 10+ payment vouchers. On the side, build an automated summary box computing how much was spent on "Electricity" vs "Stationery", and how many bills are still "Pending Approval".
| Category (Criteria) |
Formula Applied |
Calculated Output |
| Total Rent Expense |
=SUMIF(C5:C14, "Rent", E5:E14) |
₹32,000.00 |
| Total Electricity Bills |
=SUMIF(C5:C14, "Electricity", E5:E14) |
₹12,650.00 |
| Count of Approved Bills |
=COUNTIF(G5:G14, "Approved") |
8 Vouchers |
Module 6
Data Sorting, AutoFilters & Visual Charts
Transform raw rows of numbers into intuitive visual insights. Learn how to sort datasets alphabetically or by revenue, filter records to isolate specific branches or dates, and insert clean 2D Column Charts, Bar Charts, and Pie Charts with professional titles and data callouts.
Key Concepts Covered:
- A to Z & Custom Multi-Level Sorting: Sort by Department first, then by Employee Salary (Largest to Smallest).
- AutoFilters: Adding filter dropdowns with Ctrl + Shift + L to quickly view only records from "Haldwani" or "Nainital".
- Chart Construction: Selecting data ranges, inserting Clustered Column Charts, adding Data Labels, and customizing legends.
Real-World Practice Scenario: Branch Sales Comparison
A quarterly sales report across Kumaon and Garhwal division branches (Haldwani, Nainital, Rudrapur, Dehradun). Learn to build a comparative bar chart showing quarterly performance trends.
Module 7
Page Setup, Print Scaling & PDF Exporting
A spreadsheet that looks great on screen but cuts off in print is useless in an office. In this essential module, learn how to configure print margins, orient sheets (Portrait vs. Landscape), repeat header rows on every printed page, force wide sheets onto exactly 1 Page Wide, and export clean PDF reports.
Key Printing Standards Taught:
- Setting Print Area: Highlighting and locking only the report table so draft notes don't print.
- Fit Sheet on One Page Wide: Avoiding the dreaded cut-off column on page 2.
- Print Titles: Repeating row 1 across multiple pages automatically (Page Setup > Print Titles).
- Gridlines in Print: Turning on gridlines so printed tables have clean boundaries.
Download Complete Basic Excel Practice Workbook
Get all 5 practical topic modules consolidated into one multi-tab, ready-to-use Excel workbook (.xlsx). Pre-loaded with working formulas, color-coded headers, and guided classroom instructions.
Download Master Workbook (.xlsx)
Compatible with Microsoft Excel 2016, 2019, 2021, Office 365, and Google Sheets
TRACK 2: MIS ANALYTICS & AUTOMATION
Advanced Microsoft Excel Course (8-Module MIS Blueprint)
Tailored for aspiring MIS Executives, Financial Analysts, Operations Leads, and Data Operators. Learn to manage massive multi-sheet datasets, construct matrix lookups, design interactive executive KPI dashboards, clean data with Power Query, and automate repetitive workflows with VBA Macros.
Structure
8 Core Modules
Classroom + Lab Practice
Advanced Module 1
Modern Lookup Queries (XLOOKUP & 2-Way INDEX-MATCH)
Formulas & Queries
Topic 1.1
Modern XLOOKUP Mastery
Transitioning beyond older VLOOKUP limits. Learn bidirectional search (look left and right), default error replacement without nesting IFERROR, multiple column returns in one formula, and wildcard match modes.
Formula Syntax: =XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode])
Topic 1.2
Two-Way 2D Grid Matrix Lookup (INDEX & MATCH)
Intersecting rows and columns simultaneously. Ideal for multi-branch price matrices, quarterly performance grids, and dynamic financial statement extraction.
Formula Syntax: =INDEX(table_grid, MATCH(row_val, row_header, 0), MATCH(col_val, col_header, 0))
Topic 1.3
Wildcard Pattern Matching & Clean Error Handling
Searching with asterisks * and question marks ? for partial company names or alphanumeric serial codes. Handling #N/A with IFNA and IFERROR.
Practical Classroom Scenario: HR & Employee Search Card
Type employee code in I5 to instantly pull Employee Name, Department, and Monthly Salary from an HR database of 500+ employees.
| Emp ID |
Employee Name |
Department |
Branch Location |
Monthly Salary |
Lookup Formula in I6 |
| EMP-201 |
Rahul Joshi |
Sales & Marketing |
Haldwani Main |
₹45,000.00 |
=XLOOKUP(I5, A5:A11, B5:B11, "Not Found") |
| EMP-202 |
Pooja Bisht |
Finance & Accounts |
Rudrapur |
₹52,000.00 |
=XLOOKUP(I5, A5:A11, C5:C11, "Not Found") |
Advanced Module 2
Dynamic Arrays & Multi-Condition Aggregations
Modern Excel 365/2021
Topic 2.1
Dynamic Spill Array Functions
Formulas that return arrays directly into neighboring cells without dragging: =FILTER(array, include), =SORT(array, sort_index), =UNIQUE(array), and =SEQUENCE(rows). Handling the #SPILL! error.
Topic 2.2
Multi-Condition Aggregations (SUMIFS, COUNTIFS, AVERAGEIFS)
Applying 3 or more concurrent criteria across sales branches, product categories, and date ranges. Combining logical operators with cell values: =SUMIFS(sum_range, cat_range, "IT Hardware", city_range, "Haldwani", date_range, ">=2026-09-01").
Topic 2.3
Essential Business Financial Functions
Calculating bank loan EMIs, interest components, and principal amortization schedules using PMT, IPMT, PPMT, and investment valuation with NPV and IRR.
Advanced Module 3
Power Pivot Tables, Grouping & Calculated Fields
Data Summarization
Topic 3.1
Pivot Table Layouts & Hierarchical Grouping
Compact, Outline, and Tabular reporting layouts. Grouping continuous transaction dates into Fiscal Quarters and Months. Grouping employee salaries or product prices into numeric distribution bins.
Topic 3.2
Calculated Fields & Calculated Items
Injecting custom business logic directly into Pivot Tables without changing raw data tables: Calculating 18% GST liability, 5% Sales Commission, or Gross Margin % directly in the Pivot engine.
Topic 3.3
Show Values As: YoY Growth, Running Totals & % of Total
Configuring Value Field Settings to automatically compute % of Column Total, % Difference From Previous Month, and cumulative Running Totals.
Advanced Module 4
Executive KPI Dashboards & Interactive Visualization
Executive Reporting
Topic 4.1
Multi-Chart Slicers & Report Connections
Creating floating interactive Slicer buttons and connecting one master slicer across 4 separate Pivot Tables and Pivot Charts simultaneously.
Topic 4.2
Timeline Filters, Sparklines & Combo Charts
Adding Timeline scrubbers for quick quarterly drill-downs, embedding in-cell Sparkline micro-trends, and creating Combination Charts with secondary axes (Revenue ₹ vs Profit Margin %).
Topic 4.3
Dynamic KPI Summary Cards & Clean Dashboard Layouts
Structuring presentation-ready executive boards: Hiding gridlines, creating metric cards with variance arrows (▲ Up / ▼ Down), and linking dynamic titles with text formulas.
Advanced Module 5
Cascading Data Validation & Formula Auditing
Data Governance
Topic 5.1
Dependent / Cascading Dropdown Lists
Creating multi-level dependent lists (e.g., Selecting State → dynamically filters City list, or Selecting Department → filters Role list) using =INDIRECT() and Named Ranges.
Topic 5.2
Formula Auditing & Error Tracing
Using Trace Precedents and Trace Dependents blue arrows to diagnose broken calculations in complex multi-sheet models. Using the Evaluate Formula dialog and Watch Window for background monitoring.
Topic 5.3
Granular Sheet Protection & Cell Locking
Locking formula cells and template headers while leaving data-entry cells unlocked. Protecting worksheets with passwords and allowing specific user ranges.
Advanced Module 6
What-If Analysis, Sensitivity & Forecasting
Financial Modeling
Topic 6.1
Goal Seek Break-Even Analysis
Finding reverse parameters: determining the exact sales volume needed to achieve a target profit of ₹5 Lakhs, or finding required loan tenure for an affordable monthly EMI.
Topic 6.2
Two-Variable Data Tables (Sensitivity Matrices)
Modeling loan EMI changes across various interest rates (8% to 11%) and loan tenures (3 to 10 years) simultaneously in an automated grid.
Topic 6.3
Scenario Manager & Summary Reports
Creating Best-Case, Base-Case, and Worst-Case business scenarios with varying cost structures and generating automated side-by-side comparison sheets.
Advanced Module 7
Power Query (Get & Transform Data)
Data Engineering / ETL
Topic 7.1
Connecting to Multi-File Directories & CSV Pipelines
Automatically importing and combining 12 monthly sales CSV files stored in a Windows folder into one unified dataset with zero manual copy-pasting.
Topic 7.2
Cleaning, Unpivoting & Merging Queries
Splitting delimited columns, removing null headers, converting messy cross-tabulated reports into normalized database rows with Unpivot Columns, and performing database joins (Merge Queries).
Topic 7.3
Automated 1-Click Refresh Schedules
Saving automated transformation recipes. When new monthly files are added, hitting Refresh All instantly updates the entire data model, pivot tables, and charts.
Advanced Module 8
VBA Macros Automation & MIS Capstone Project
Automation & Capstone
Topic 8.1
Developer Tab & Macro Recorder Fundamentals
Enabling the Developer tab, macro security configurations, absolute vs relative reference recording, and understanding the VBA Editor (Project Explorer, Code Window).
Topic 8.2
Writing & Customizing VBA Subroutines
Reading and modifying recorded VBA code: writing basic loops For Each...Next, clearing old ranges, formatting borders, and assigning macros to clickable custom buttons and keyboard shortcuts.
Topic 8.3
Capstone MIS Project: End-to-End Enterprise Analytics
Building a complete corporate analytics suite: taking raw multi-departmental logs, cleaning via Power Query, calculating KPIs with dynamic formulas, presenting through an interactive Slicer Dashboard, and automating daily report distribution.
Download Complete Advanced Excel Master Workbook
Consolidated master workbook (.xlsx) containing all 8 modules: Modern XLOOKUP cards, 2-Way Matrix models, Dynamic Arrays, 500-row Raw Pivot datasets, Executive KPI Dashboards, and Loan Sensitivity tables with working formulas.
Download Advanced Master Workbook (.xlsx)
Compatible with Microsoft Excel 2016, 2019, 2021, Office 365, and Google Sheets
Ready to Master Advanced MIS & Analytics in Haldwani?
Join morning or evening classroom batches at Digital Skills Academy. Get individual computer lab practice and personalized mentor reviews.
Enroll for Advanced Excel in Haldwani
Career Scope & Job Profiles in Haldwani
Excel proficiency is the foundational requirement for almost every administrative, marketing, banking, logistics, and retail position. Local employers in Haldwani, Rudrapur Industrial Area, and Dehradun specifically recruit individuals with practical Excel formula capabilities.
MIS Executive
Managing organizational databases, extracting daily reports, and updating tracking sheets.
Billing & Accounts Clerk
Generating GST customer invoices, calculating discounts, and tracking vendor payments.
Data Entry & Operations Specialist
Clean recording of attendance, inventory stock, patient records, or school enrollments.