Microsoft Excel Managing Tables and Data Questions and Answers

Question 1

A user has an Excel table named 'SalesData' with columns 'Region', 'Product', and 'Revenue'.
Which of the following formulas correctly calculates the total revenue for the 'East' region using a structured reference?

Accepted Answer

A) =SUMIF(SalesData[Region],"East",SalesData[Revenue])

Answer

B) =TOTAL(SalesData[Revenue] WHERE SalesData[Region]="East")

Answer

C) =SUM(SalesData[Revenue]) IF [Region]="East"

Answer

D) =CALCULATE(SUM(Revenue), Region="East")

Question 2

You have converted a range of data into an Excel table. To quickly add a row at the bottom of the table that calculates the sum for a numeric column, which feature should you use?

Accepted Answer

B) The Total Row option in the Table Design tab

Answer

A) The AutoSum button on the Home tab

Answer

C) Manually inserting a row and using the SUM function

Answer

D) The Subtotals feature on the Data tab

Question 3

In a large table of customer orders, a manager wants to identify and permanently delete all rows where the combination of 'CustomerID' and 'OrderDate' is identical, keeping only the first instance of each unique combination. What is the most direct tool in Excel for this task?

Accepted Answer

C) The Remove Duplicates tool on the Data tab

Answer

A) Conditional Formatting to highlight duplicates, then manual deletion

Answer

B) The Advanced Filter with the 'Unique records only' option

Answer

D) Sorting the data by 'CustomerID' and 'OrderDate' and then using the UNIQUE function

Question 4

A user wants to filter a large sales data table in a highly visual and interactive way, allowing them to see the current filtering state clearly and quickly filter by multiple product categories at once. Which of the following is the BEST tool for this requirement?

Accepted Answer

C) Slicers

Answer

A) The AutoFilter dropdown arrows in the header row

Answer

B) The Advanced Filter dialog box

Answer

D) Data Validation lists

Question 5

When applying conditional formatting to an entire table row based on the value of a single cell in that row, what is a critical consideration when writing the formula?

Accepted Answer

C) The formula must use a mixed reference, locking the column but not the row, like `=$A1 > 100`.

Answer

A) The formula must use structured references like `=[ColumnName] > 100`.

Answer

B) The formula must use absolute references for both the row and column, like `=$A$1 > 100`.

Answer

D) The table must be converted back to a range before applying the formula.

Question 6

Which of the following is an advantage of converting a range of data into an Excel table?

Accepted Answer

D) It removes the ability to sort or filter the data.

Answer

A) It prevents any changes to the data within the table.

Answer

B) It automatically applies a single, unchangeable format.

Answer

C) Formulas automatically fill down new rows and structured references adjust dynamically.

A user has an Excel table named 'SalesData' with columns 'Region', 'Product', and 'Revenue'.Which of the following formulas correctly calculates the total revenue for the 'East' region using a structured reference?

A user has an Excel table named 'SalesData' with columns 'Region', 'Product', and 'Revenue'.
Which of the following formulas correctly calculates the total revenue for the 'East' region using a structured reference?