Master Advanced Excel & VBA

Learn Excel formulas, data analysis, dashboards, VBA programming, and business automation through structured practical lessons.

COURSE SYLLABUS
1. Excel Basics
Introduction to Advanced Excel Workbook, Worksheets, Rows & Columns Data Types & Cell Formatting Cell References & Keyboard Shortcuts
2. Advanced Excel Formulas
Advanced Formulas Introduction IF, AND, OR & IFERROR Excel Tables XLOOKUP INDEX & MATCH SUMIFS & COUNTIFS
3. Pivot Analytics
Pivot Analytics Introduction Pivot Tables Calculated Fields & Items Timelines & Slicers Text Functions & Data Cleaning
4. Excel Dashboards
Interactive Dashboards Introduction Data Validation Conditional Formatting Combo Charts Sparklines
5. Excel Data Tools
Data Integration Introduction Importing Text Files Power Query Worksheet Protection Formula Auditing
6. VBA Fundamentals
VBA Fundamentals Introduction Macro Recorder VBE Workspace VBA Objects & Properties Variables & Data Types MsgBox & InputBox If...Then...Else Select Case Loops & Dynamic Ranges
7. VBA Automation
VBA Automation Introduction Subroutines & Arguments User-Defined Functions Workbook Events Worksheet Events Form Controls & Buttons
8. VBA UserForms
UserForms Introduction UserForm Design Toolbox Controls Input Validation CRUD Operations with UserForms
9. VBA Optimization & Security
Optimization Introduction Error Handling Debugging Tools Performance Optimization VBA Project Security
10. Corporate Capstone Project
Capstone Project Introduction Data Consolidation Data Cleaning & Analysis Automated PDF Reports Dashboard Automation
11. Excel Practice & Assignments
Practice Introduction Excel Formula Exercises Pivot Table Exercises Dashboard Exercises VBA Programming Exercises Final Assignments

Workbook, Worksheets, Rows and Columns

Understand how Excel workbooks organize information into worksheets, rows, columns, and individual cells. Learn to create, rename, move, and manage worksheets using practical examples.

Learning objectives: Understand workbook and worksheet differences, identify rows and columns, use cell addresses, and organize related business information in a single Excel file.

1. What Is an Excel Workbook?

A workbook is an Excel file that can contain one or more worksheets. It is the main file in which you save related information.

For example, Samadhan is preparing a training report for CIIT Training Institute. He can save student details, examination marks, and attendance in separate worksheets inside one workbook.

Workbook: CIITTraining.xlsx One file can contain multiple worksheets Sheet 1 Student Details Names and IDs Sheet 2 Examination Marks Scores and results Sheet 3 Attendance Present and absent Related information organized in one Excel file

Diagram: a workbook containing three worksheets for different categories of training data.

2. What Is a Worksheet?

A worksheet is an individual spreadsheet inside a workbook. It contains a grid of rows and columns where you enter and manage information.

A workbook may contain multiple worksheets so that related information can be organized without creating separate files for every category.

Example: CIIT Training Records

Worksheet Name Information Stored
StudentDetails Student names, IDs, and courses
Marks Examination scores and results
Attendance Attendance dates and status
Summary Combined totals and key information

3. Rows, Columns and Cells

Every worksheet contains rows and columns. Their intersection creates a cell, which is the basic unit used to store data.

A B C D 1 2 3 4 B3 Columns: A, B, C, D Rows: 1, 2, 3, 4 Selected cell: B3

Diagram: column B and row 3 intersect at cell B3.

Rows

Rows run horizontally and are identified by numbers such as 1, 2, 3, and 4. Each row can contain information about one record, such as a student's details.

Columns

Columns run vertically and are identified by letters such as A, B, C, and D. A column commonly stores one type of information, such as student names, marks, or attendance.

Cells

A cell is the intersection of a row and a column. For example, cell B3 is located in column B and row 3. You can enter text, numbers, dates, or formulas into a cell.

4. Understanding Cell Addresses

A cell address, also called a cell reference, identifies a specific cell using its column letter followed by its row number.

Cell Address Meaning Example Use
A1 Column A, Row 1 Student Name heading
B2 Column B, Row 2 Student marks
C3 Column C, Row 3 Student result
D5 Column D, Row 5 Total marks
Remember: Write the column letter first and the row number second. For example, B3 is correct; 3B is not a valid standard cell address.

5. How to Create and Rename a Worksheet

When you create a new workbook, Excel usually provides a worksheet. You can add more worksheets and give them meaningful names to organize your work.

  1. Open Microsoft Excel and create a new workbook.
  2. Locate the worksheet tabs at the bottom of the window.
  3. Click the plus (+) button to insert a new worksheet.
  4. Double-click a worksheet tab to rename it.
  5. Type a meaningful name, such as StudentDetails, and press Enter.
  6. Use the worksheet tabs to move between sheets.
Worksheet Tabs StudentDetails Marks Attendance Click + to add another worksheet Double-click a tab to rename it.

Illustration: worksheet tabs and the button for adding a sheet. The exact appearance may differ between Excel versions.

Useful tip: Use short, descriptive worksheet names. Avoid duplicate names within the same workbook and keep related information together.

6. Practical Example: Organizing Student Data

Samiksha is organizing student records for CIIT Training Institute. She can create one workbook with separate worksheets for students, marks, and attendance.

Student ID Student Name Course Marks
101 Samadhan Advanced Excel 85
102 Samiksha Advanced Excel 92
103 Pradnya Advanced Excel 76
104 Omkar Advanced Excel 68
105 Neha Advanced Excel 88

Suggested Workbook Organization

  • StudentDetails: Student ID, name, and course.
  • Marks: Subject-wise marks and results.
  • Attendance: Attendance dates and status.
  • Summary: Totals and overall performance.

This approach makes it easier to maintain related information in one workbook and find the correct worksheet when needed.

7. Common Mistakes to Avoid

  • Using unclear worksheet names such as Sheet1 and Sheet2 for important reports.
  • Entering multiple unrelated records in the same row.
  • Leaving unnecessary blank rows inside a structured dataset.
  • Confusing a cell address with a worksheet name.
  • Saving changes to the wrong workbook or file location.

8. Practice Exercise

Create a workbook named CIITTraining.xlsx.

  1. Create three worksheets: StudentDetails, Marks, and Attendance.
  2. Enter the five sample student names from this lesson.
  3. Rename the default worksheet to StudentDetails.
  4. Identify cell B2 and explain its column and row.
  5. Save the workbook and verify that all worksheet names are correct.

Goal: Practice creating a workbook and organizing information across multiple worksheets.

Important Points to Remember

  • A workbook is an Excel file.
  • A worksheet is an individual spreadsheet inside a workbook.
  • Rows run horizontally and use numbers.
  • Columns run vertically and use letters.
  • A cell address combines the column letter and row number.
  • Meaningful worksheet names improve organization and readability.

Summary

In this lesson, you learned how Excel workbooks contain worksheets and how worksheets organize information into rows, columns, and cells. You also learned to identify cell addresses, create and rename worksheets, and organize student records.

These concepts are the foundation of spreadsheet management. In the next topic, we will learn about Data Types and Cell Formatting.