Excel Tables
Excel Tables are special data ranges that provide powerful features such as automatic formatting, sorting, filtering, structured references, and easy data analysis. Tables make large datasets easier to manage and work with.
Tip: Whenever you work with business data, student records, sales reports, or inventory lists, convert the data into an Excel Table.
Creating Excel Tables
An Excel Table converts a normal data range into a structured table with built-in tools.
Sample Data
| ID | Name | Course | Fees |
|---|---|---|---|
| 101 | Rahul | Excel | 5000 |
| 102 | Priya | Python | 7000 |
| 103 | Amit | Power BI | 8000 |
Steps to Create a Table
- Select the entire data range.
- Go to Insert Tab.
- Click Table.
- Verify the selected range.
- Check My Table Has Headers.
- Click OK.
Shortcut Key
Ctrl + T
Benefits of Excel Tables
| Feature | Benefit |
|---|---|
| Automatic Formatting | Applies professional formatting instantly. |
| Filtering | Adds filter buttons automatically. |
| Sorting | Quick sorting options. |
| Auto Expansion | Table grows automatically when new data is added. |
| Structured References | Uses column names instead of cell references. |
| Total Row | Calculates totals instantly. |
Table Formatting
Excel provides built-in table styles that improve readability and appearance.
Table Design Options
| Option | Purpose |
|---|---|
| Header Row | Displays column headings. |
| Banded Rows | Alternate row colors for easy reading. |
| First Column | Highlights the first column. |
| Last Column | Highlights the last column. |
| Filter Button | Shows filter dropdowns. |
| Total Row | Displays summary calculations. |
Applying Table Styles
- Select any cell inside the table.
- Go to Table Design tab.
- Choose a style from the Table Styles gallery.
- The selected style is applied instantly.
Structured References
Structured References use column names instead of cell addresses. They make formulas easier to read and understand.
Normal Formula
=SUM(D2:D10)
Structured Reference Formula
=SUM(StudentTable[Fees])
Here:
| Part | Meaning |
|---|---|
| StudentTable | Table Name |
| Fees | Column Name |
Common Structured References
| Formula | Purpose |
|---|---|
| =SUM(StudentTable[Fees]) | Total Fees |
| =AVERAGE(StudentTable[Fees]) | Average Fees |
| =MAX(StudentTable[Fees]) | Highest Fee |
| =MIN(StudentTable[Fees]) | Lowest Fee |
Subtotals
Subtotal is used to calculate totals for grouped data. It can calculate Sum, Average, Count, Max, Min and other functions automatically.
Sample Data
| Course | Student | Fees |
|---|---|---|
| Excel | Rahul | 5000 |
| Excel | Amit | 6000 |
| Python | Priya | 7000 |
| Python | Kiran | 7500 |
Steps to Apply Subtotal
- Sort the data by the column you want to group.
- Select any cell in the dataset.
- Go to Data Tab.
- Click Subtotal.
- Select the column to group by.
- Select the function (Sum, Average, Count, etc.).
- Select the field to calculate.
- Click OK.
Common Subtotal Functions
| Function | Purpose |
|---|---|
| Sum | Adds values. |
| Average | Calculates average. |
| Count | Counts records. |
| Max | Finds highest value. |
| Min | Finds lowest value. |
Shortcut Keys
| Shortcut | Purpose |
|---|---|
| Ctrl + T | Create Table |
| Alt + A + B | Open Subtotal Dialog Box |
