Microsoft Excel Managing Tables and Table Data 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?

Accepted Answer

=[@[Units]]*[@[Unit_Price]]

Answer

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?

Accepted Answer

Click the drop-down arrow that appears in the cell and select 'Count'.

Answer

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?

Accepted Answer

On the 'Data' tab, open the 'Sort' dialog box, add a level, and set the primary and secondary sort criteria.

Answer

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?

Accepted Answer

Slicer

Answer

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?

Accepted Answer

On the 'Table Design' tab, in the 'Tools' group, click 'Convert to Range'.

Answer

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.

Excel Practice Test

Excel Practice Test

Microsoft Excel Managing Tables and Table Data Questions and Answers