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

Excel Tables

Learn how to convert ordinary data into structured Excel Tables. Explore table creation, formatting, automatic expansion, filtering, sorting, structured references, and practical reporting techniques using student records.

Learning objectives: Create an Excel Table, apply table styles, filter and sort records, use structured references, and calculate totals for a student performance report.

1. What Is an Excel Table?

An Excel Table is a structured range of related data. It provides features such as header filters, consistent formatting, automatic expansion when new rows are added, and structured references that make formulas easier to understand.

For example, CIIT Training Institute can maintain student names, course names, marks, and results in a single table instead of managing an unstructured range of cells.

CIIT Student Performance Table Student Name Course Marks Attendance Result Samadhan Advanced Excel 85 90% Pass Samiksha Advanced Excel 92 88% Pass Pradnya Advanced Excel 76 82% Pass Omkar Advanced Excel 32 70% Fail Structured data with consistent formatting and filterable headers

Illustration: an Excel Table organizes student records into clearly labeled columns and rows.

2. Benefits of Excel Tables

Feature Explanation Practical Benefit
Header Filters Filter and sort records using column headings. Find specific students quickly.
Table Formatting Apply consistent styles and banded rows. Make reports easier to read.
Automatic Expansion The table can expand when data is entered directly below it. Include new records more easily.
Calculated Columns Formulas can fill down through a table column automatically. Reduce repeated formula entry.
Structured References Refer to table columns by their names. Make formulas more readable.

3. How to Create an Excel Table

Follow these steps to convert a normal range of student records into an Excel Table.

Step 1: Enter the Data

Open Excel and enter the following data, including the column headers in the first row.

Student Name Course Marks
Samadhan Advanced Excel 85
Samiksha Advanced Excel 92
Pradnya Advanced Excel 76
Omkar Advanced Excel 68
Neha Advanced Excel 88

Step 2: Select the Data

Click any cell inside the data range. You can also select the complete range, including the headers.

Step 3: Convert the Range into a Table

  1. Press Ctrl + T.
  2. Check that the selected range is correct.
  3. Tick My table has headers.
  4. Click OK.

Step 4: Apply a Table Style

  1. Click anywhere inside the newly created table.
  2. Open the Table Design tab.
  3. Choose a suitable table style.
  4. Optionally enable Banded Rows for easier reading.
Result: Your data is now an Excel Table with header filters and table formatting. The exact ribbon labels may vary by Excel version.

4. Rename an Excel Table

Excel gives each table a name, such as Table1. Meaningful table names make formulas easier to understand.

Step-by-step instructions

  1. Click any cell inside the table.
  2. Open the Table Design tab.
  3. Find the Table Name box.
  4. Enter StudentResults and press Enter.
Important: Use a valid table name without spaces. Avoid using a name that already belongs to another table in the same workbook.

5. Sort and Filter Table Data

Excel Tables include filter controls in their headers. You can sort records alphabetically or numerically and filter records to show only the information you need.

Example A: Filter by Student Name

  1. Click the filter arrow in the Student Name column.
  2. Clear Select All if you want to select a specific student.
  3. Select Samiksha.
  4. Click OK to display matching records.

Example B: Filter Marks Greater Than 80

  1. Open the filter arrow in the Marks column.
  2. Select Number Filters.
  3. Choose Greater Than.
  4. Enter 80 and confirm.

Example C: Sort Marks from Highest to Lowest

  1. Open the filter arrow in the Marks column.
  2. Select Sort Largest to Smallest.
  3. Review the reordered student records.
Remember: Filtering hides rows that do not match the selected criteria; it does not delete those records. Clear the filter to display all the data again.

6. Structured References

Structured references use table names and column headers instead of ordinary cell addresses. They are especially helpful when working with named Excel Tables.

Example: Calculate Total Marks

Suppose the table is named StudentResults and it contains columns named Excel Marks and VBA Marks. To add both marks in a calculated column, use:

Excel Formula
=[@[Excel Marks]]+[@[VBA Marks]]

The @ symbol refers to the current row in the table. Excel adds the Excel Marks and VBA Marks values for that student.

Example: Total of an Entire Column

Excel Formula
=SUM(StudentResults[Marks])

This formula adds the values in the Marks column of the StudentResults table. The column heading must match the actual table column name.

Note: If your table uses a different name or column heading, update the structured reference to match your workbook.

7. Total Row and Automatic Expansion

Enable the Total Row

  1. Click inside the Excel Table.
  2. Open the Table Design tab.
  3. Enable Total Row.
  4. In the total cell under Marks, select Sum from the dropdown.

Excel adds a total row and calculates the selected summary for the chosen column.

Add a New Student

  1. Click the first row immediately below the table.
  2. Enter Neha's new record or another student record.
  3. Check whether the table expands to include the new row.
  4. Verify that table formatting and calculated-column formulas extend where applicable.

Automatic expansion depends on where and how the new data is entered. If necessary, resize the table from Table Design.

8. Common Problems and Solutions

Problem Possible Reason Solution
Headers show Column1 or Column2 The original range did not have proper headers. Rename the table column headings.
Filter arrows are missing The data may still be a normal range. Convert the range into a Table using Ctrl + T.
New row is not included The new data was entered outside the table range. Resize the table or enter data directly below it.
Structured reference formula fails The table or column name does not match. Verify the exact table name and column heading.
Sort order looks incorrect Numbers may be stored as text. Check the data type and convert values to numbers if needed.

9. Practical Exercise

Create a StudentResults table for CIIT Training Institute.

  1. Enter five student records with names, courses and marks.
  2. Convert the range into an Excel Table using Ctrl + T.
  3. Rename the table StudentResults.
  4. Apply a green table style.
  5. Filter students who scored more than 80 marks.
  6. Sort marks from highest to lowest.
  7. Enable the Total Row and calculate the sum of marks.
  8. Add another student and verify table expansion.
  9. Practice a structured-reference formula using the actual column names.

Goal: Build a structured, filterable student report that is easy to maintain and analyze.

Important Points to Remember

  • Use Ctrl + T to create an Excel Table from a data range.
  • Include clear column headers when creating a table.
  • Use filters and sorting to analyze records quickly.
  • Rename tables with meaningful names.
  • Structured references use table names and column headings.
  • Use the Total Row to summarize table data.
  • Check that new records and formulas are included correctly.

Summary

In this lesson, you learned how to create and format Excel Tables, rename tables, sort and filter records, use structured references, enable a Total Row, and add new records.

Excel Tables make structured data easier to maintain and analyze. In the next topic, we will learn about XLOOKUP for finding information in a dataset.