Tables (Insert Table) and benefits in Excel - Practice Problems & Coding Challenges

Challenge - 5 Problems

🧠 Conceptual

intermediate

Why use Excel Tables instead of normal ranges?

Which of the following is NOT a benefit of converting a range to an Excel Table?

A Tables allow you to use structured references in formulas

B Tables prevent any data from being deleted accidentally

C Tables automatically expand when you add new data

D Tables provide easy filtering and sorting options

💡 Hint

Think about what Tables do automatically and what they cannot control.

Excel Tables help with dynamic ranges, structured references, and filtering, but they do not prevent data deletion.

📊 Formula Result

intermediate

Structured Reference Output in Excel Table

Given a table named Sales with columns Date, Product, and Amount, what is the result of the formula =SUM(Sales[Amount])?

A Error because structured references cannot be used in SUM

B Sum of all values in the Product column of the Sales table

C Sum of all values in the Date column of the Sales table

D Sum of all values in the Amount column of the Sales table

💡 Hint

Look at the column name inside the brackets.

SUM(Sales[Amount]) adds all numbers in the Amount column of the Sales table.

❓ Function Choice

advanced

Choosing the Best Formula for Dynamic Table Data

You have an Excel Table named Inventory with columns Item, Quantity, and Price. You want to calculate the total value of all items (Quantity * Price) in a single cell. Which formula is correct?

A =SUMPRODUCT(Inventory[Quantity], Inventory[Price])

B =SUM(Inventory[Quantity]) * SUM(Inventory[Price])

C =SUMPRODUCT(Inventory[Quantity] * Inventory[Price])

D =SUM(Inventory[Quantity] * Inventory[Price])

💡 Hint

Think about how to multiply corresponding rows and then add them up.

SUMPRODUCT multiplies corresponding elements and sums the results, perfect for total value calculation.

🎯 Scenario

advanced

Effect of Adding Rows to an Excel Table on Formulas

You have a table named Expenses with columns Date, Category, and Amount. You use the formula =SUM(Expenses[Amount]) in a cell. What happens to the formula result if you add new rows with amounts at the bottom of the table?

A The formula automatically includes the new rows and updates the sum

B The formula ignores the new rows unless you update the formula manually

C The formula returns an error because the table size changed

D The formula only sums the original rows and excludes new rows

💡 Hint

Think about how Excel Tables handle dynamic data ranges.

Excel Tables automatically expand to include new rows, so structured references update accordingly.

❓ data_analysis

expert

Analyzing Filtered Data in an Excel Table

You have an Excel Table named SalesData with columns Region, Salesperson, and SalesAmount. You apply a filter to show only the 'West' region. Which formula will correctly calculate the total SalesAmount for the visible (filtered) rows only?

A =SUM(SalesData[SalesAmount])

B =SUMIF(SalesData[Region], "West", SalesData[SalesAmount])

C =SUBTOTAL(109, SalesData[SalesAmount])

D =SUMPRODUCT(SalesData[Region] = "West", SalesData[SalesAmount])

💡 Hint

Which function ignores hidden rows caused by filters?

SUBTOTAL with function number 109 sums only visible cells, ignoring filtered-out rows.