Tables (Insert Table) and benefits in Excel - Real Business Scenario

Scenario Mode

👤 Your Role: You are a sales analyst at a retail company.

📋 Request: Your manager wants you to organize the monthly sales data into a table to easily filter, sort, and analyze the data.

📊 Data: You have sales data for different products, including Product Name, Category, Month, Units Sold, and Revenue.

🎯 Deliverable: Create an Excel table from the sales data and demonstrate how it helps with sorting, filtering, and calculating total revenue.

Sample Data

Product Name	Category	Month	Units Sold	Revenue
Notebook	Stationery	January	120	600
Pen	Stationery	January	200	400
Desk Chair	Furniture	January	15	1500
Notebook	Stationery	February	100	500
Pen	Stationery	February	180	360
Desk Chair	Furniture	February	10	1000
Desk Lamp	Furniture	January	20	400
Desk Lamp	Furniture	February	25	500

1

Step 1: Select the entire sales data range including headers (A1:E9).

No formula needed; just select the range.

Expected Result

The data range is highlighted and ready for table insertion.

2

Step 2: Insert a table by clicking Insert > Table and confirm the range includes headers.

No formula; use Excel's Insert Table feature.

Expected Result

The data is converted into a table with filter dropdowns on each header.

3

Step 3: Use the filter dropdown on the 'Category' column to show only 'Furniture' items.

No formula; use table filter feature.

Expected Result

Only rows with 'Furniture' in the Category column are visible.

4

Step 4: Sort the table by 'Revenue' column in descending order.

No formula; use table sort feature.

Expected Result

Rows are reordered so the highest revenue items appear first.

5

Step 5: Add a Total Row to the table by checking 'Total Row' in Table Design tab.

No formula; use Table Design > Total Row option.

Expected Result

A new row appears at the bottom showing totals for numeric columns.

6

Step 6: In the Total Row under 'Revenue' column, select 'Sum' to calculate total revenue.

No formula typed; select 'Sum' from dropdown in Total Row cell.

Expected Result

The total revenue for the filtered data is displayed in the Total Row.

Final Result

Product Name | Category  | Month    | Units Sold | Revenue
--------------------------------------------------------
Desk Chair   | Furniture | January  | 15         | 1500
Desk Chair   | Furniture | February | 10         | 1000
Desk Lamp    | Furniture | February | 25         | 500
Desk Lamp    | Furniture | January  | 20         | 400
--------------------------------------------------------
Total        |           |          |            | 3400

✓Using tables makes it easy to filter and sort data quickly.

✓The Total Row feature automatically calculates sums without extra formulas.

✓Tables help keep data organized and improve analysis efficiency.

Bonus Challenge

Create a slicer for the table to filter data by Month interactively.

Show Hint

Use Insert > Slicer, then select the 'Month' column to add clickable buttons for filtering.