Managing Tables and Data Flashcards
6 cards from real Microsoft Excel practice questions. Tap to flip, then mark Knew It or Still Learning — missed cards come back until you master them.
Read the first 6 Managing Tables and Data flashcards as text
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?
Answer: A) =SUMIF(SalesData[Region],"East",SalesData[Revenue])
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.
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?
Answer: B) The Total Row option in the Table Design tab
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.
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?
Answer: C) The Remove Duplicates tool on the Data tab
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.
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?
Answer: C) Slicers
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.
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?
Answer: C) The formula must use a mixed reference, locking the column but not the row, like `=$A1 > 100`.
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.
Which of the following is an advantage of converting a range of data into an Excel table?
Answer: D) It removes the ability to sort or filter the data.
A key benefit of Excel tables is their dynamic nature. When you enter a formula in a column, it can automatically populate down to all other rows (a calculated column). Also, formulas that reference table data using structured references (e.g., `TableName[ColumnName]`) automatically expand to include new rows added to the table.