Microsoft Excel Managing Tables and Table Data Questions and Answers 1 — Questions and Answers
Question 1: You have an Excel table named 'SalesData' with columns for '[Units]' and '[Unit_Price]'. You want to add a new calculated column named '[Revenue]' that multiplies the units by the unit price for each row. Which formula, when entered into the first cell of the new column, will correctly and automatically populate for all other rows in the table?
- =B2*C2
- =[@[Units]]*[@[Unit_Price]] (Correct answer)
- =SalesData[Units]*SalesData[Unit_Price]
- =PRODUCT([Units],[Unit_Price])
Correct answer: =[@[Units]]*[@[Unit_Price]]
In an Excel table, using `[@ColumnName]` creates a structured reference that refers to the value in the specified column for the current row. This syntax allows Excel to automatically fill the formula down the entire column, creating a calculated column.
Question 2: An analyst has a large table of customer data and has enabled the 'Total Row' feature from the 'Table Design' tab. To get a quick count of all customers in the '[CustomerID]' column, what is the most efficient action to take in the corresponding cell of the Total Row?
- Manually type the formula `=COUNT(Table1[CustomerID])`.
- Right-click the cell and select 'Summarize by Count'.
- Click the drop-down arrow that appears in the cell and select 'Count'. (Correct answer)
- Go to the 'Formulas' tab and use the 'AutoSum' > 'Count Numbers' feature.
Correct answer: Click the drop-down arrow that appears in the cell and select 'Count'.
The Total Row in an Excel table provides a built-in drop-down list of common summary functions (like Average, Count, Sum, etc.) for each cell, allowing the user to quickly change the calculation without manually writing a formula.
Question 3: A project manager has a table listing tasks, assigned team members, and departments. They need to sort the table first by 'Department' in alphabetical order (A to Z), and then within each department, sort by 'Task Priority' from 'High' to 'Low'. Which of the following is the correct method to apply this multi-level sort?
- Use the filter drop-down on the 'Task Priority' column to sort, then the drop-down on the 'Department' column to sort.
- On the 'Data' tab, open the 'Sort' dialog box, add a level, and set the primary and secondary sort criteria. (Correct answer)
- Create two separate slicers, one for 'Department' and one for 'Task Priority'.
- Sort the 'Department' column A to Z, then separately sort the 'Task Priority' column Z to A.
Correct answer: On the 'Data' tab, open the 'Sort' dialog box, add a level, and set the primary and secondary sort criteria.
The 'Sort' dialog box, found on the 'Data' tab, is specifically designed for multi-level sorting. It allows the user to define a primary sort key ('Department') and then add subsequent levels ('Task Priority') to sort the data within the primary groups.
Question 4: After creating an Excel table from a range of data, you want to provide users with a set of visible, clickable buttons to filter the 'Region' and 'Product Category' columns without using the drop-down arrows in the headers. Which feature should you insert?
- Slicer (Correct answer)
- Timeline
- Data Validation List
- Advanced Filter
Correct answer: Slicer
Slicers are interactive controls that provide buttons for filtering data in tables, PivotTables, and PivotCharts. They can be inserted from the 'Table Design' tab and offer a user-friendly way to see and change the current filter state.
Question 5: A user has finished their analysis using an Excel table and now needs to send the worksheet to a colleague who is using a much older version of Excel that does not support table features. The user wants to remove the table functionality (e.g., structured references, automatic expansion) but keep all the data and cell formatting. What is the most direct way to do this?
- Select the table and use the 'Clear Formats' button on the Home tab.
- Delete the table's name from the Name Manager.
- On the 'Table Design' tab, in the 'Tools' group, click 'Convert to Range'. (Correct answer)
- Copy the table and use 'Paste Special' with the 'Values' option.
Correct answer: On the 'Table Design' tab, in the 'Tools' group, click 'Convert to Range'.
The 'Convert to Range' command is the designated feature for removing the underlying table object and its functionality while preserving the data and the visual formatting that was applied by the table style.
Question 6: An inventory manager has a large table of product information. The table contains several rows with duplicate 'ProductSKU' values due to an import error. To clean the data, they need to permanently delete the extra rows, keeping only the first instance of each unique SKU. Which data tool is designed for this specific task?
- Conditional Formatting to highlight duplicates, then manual deletion.
- The FILTER function to create a new list of unique values.
- The 'Remove Duplicates' feature on the 'Data' tab. (Correct answer)
- A PivotTable with 'ProductSKU' in the Rows area.
Correct answer: The 'Remove Duplicates' feature on the 'Data' tab.
The 'Remove Duplicates' tool, found on the 'Data' tab, is the most direct and efficient feature for permanently deleting entire rows from a table or range based on duplicate values in one or more specified columns.
Microsoft Excel Managing Tables and Table Data Questions and Answers 1
You have an Excel table named 'SalesData' with columns for '[Units]' and '[Unit_Price]'.
You want to add a new calculated column named '[Revenue]' that multiplies the units by the unit price for each row.
Which formula, when entered into the first cell of the new column, will correctly and automatically populate for all other rows in the table?