Microsoft Excel Managing Tables and Data Questions and Answers 1

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

The correct formula uses the SUMIF function, which is designed for conditional summing. The structured references 'SalesData[Region]' and 'SalesData[Revenue]' correctly refer to the entire data range of the 'Region' and 'Revenue' columns within the 'SalesData' table, making the formula dynamic and readable.

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

The 'Total Row' is a specific feature for Excel tables that adds a special row at the end. This row allows users to easily select summary functions like SUM, AVERAGE, COUNT, etc., from a dropdown for each column. The calculations in the Total Row use the SUBTOTAL function, which correctly calculates only the visible cells if the table is filtered.

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

The 'Remove Duplicates' tool is specifically designed for this purpose. It allows the user to select one or more columns to define what constitutes a duplicate record. It will then permanently delete the entire rows for the duplicate entries found based on the selected columns.

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

Slicers provide a user-friendly, visual way to filter data in tables, PivotTables, and PivotCharts. They are interactive buttons that show the current filtering state and allow for easy, single-click filtering, which is ideal for dashboards and interactive reports.

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

To apply formatting to an entire row, the conditional formatting formula should be written as if it were for the first cell in the selection, but with a mixed reference. The column part of the reference should be absolute (e.g., `$A`) to ensure that every cell in the row checks the value in that specific column, while the row part should be relative (e.g., `1`) so that the rule correctly adjusts for each row in the table.

Excel Practice Test

Excel Practice Test

Microsoft Excel Managing Tables and Data Questions and Answers 1