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.
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.
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
- Press Ctrl + T.
- Check that the selected range is correct.
- Tick My table has headers.
- Click OK.
Step 4: Apply a Table Style
- Click anywhere inside the newly created table.
- Open the Table Design tab.
- Choose a suitable table style.
- Optionally enable Banded Rows for easier reading.
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
- Click any cell inside the table.
- Open the Table Design tab.
- Find the Table Name box.
- Enter
StudentResultsand press Enter.
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
- Click the filter arrow in the Student Name column.
- Clear Select All if you want to select a specific student.
- Select Samiksha.
- Click OK to display matching records.
Example B: Filter Marks Greater Than 80
- Open the filter arrow in the Marks column.
- Select Number Filters.
- Choose Greater Than.
- Enter 80 and confirm.
Example C: Sort Marks from Highest to Lowest
- Open the filter arrow in the Marks column.
- Select Sort Largest to Smallest.
- Review the reordered student records.
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 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
=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.
7. Total Row and Automatic Expansion
Enable the Total Row
- Click inside the Excel Table.
- Open the Table Design tab.
- Enable Total Row.
- 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
- Click the first row immediately below the table.
- Enter Neha's new record or another student record.
- Check whether the table expands to include the new row.
- 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.
- Enter five student records with names, courses and marks.
- Convert the range into an Excel Table using Ctrl + T.
- Rename the table StudentResults.
- Apply a green table style.
- Filter students who scored more than 80 marks.
- Sort marks from highest to lowest.
- Enable the Total Row and calculate the sum of marks.
- Add another student and verify table expansion.
- 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.