MOS Excel: Tables, Sorting and Filtering Data

Question 1

Which keyboard shortcut converts a selected range of data into an Excel Table?

Accepted Answer

Ctrl+T

Answer

Ctrl+T (or Insert > Table) opens the Create Table dialog, which converts the selected range into a formatted Excel Table. Ctrl+L is an alternative in some versions, but Ctrl+T is the standard documented shortcut.

Question 2

A table named SalesData has a column called Amount. Which formula correctly totals that column using a structured reference?

Accepted Answer

=SUM(SalesData[Amount])

Answer

Structured references use the form TableName[ColumnName], so =SUM(SalesData[Amount]) totals the Amount column. The range adjusts automatically as rows are added.

Question 3

You want a row at the bottom of an Excel Table that shows a SUM or AVERAGE for each column. What should you do?

Accepted Answer

Select the Total Row check box on the Table Design tab

Answer

The Total Row option in the Table Style Options group on the Table Design tab adds a row whose cells each have a drop-down of functions such as Sum, Average and Count.

Question 4

Which command removes rows that have identical values in the selected columns of a table?

Accepted Answer

Data > Remove Duplicates

Answer

Data > Remove Duplicates (also available on the Table Design tab) deletes rows with duplicate values in the columns you choose, keeping the first occurrence.

Question 5

You need to sort a table by Region (A to Z) and then by Sales (largest to smallest) within each region. Which feature do you use?

Accepted Answer

Custom Sort with multiple levels using Add Level

Answer

The Custom Sort dialog (Data > Sort) lets you add several sort levels. Set Region as the first level and Sales as the second, with Largest to Smallest as the order.

MOS - Microsoft Office Specialist Certification Practice Test

MOS - Microsoft Office Specialist Certification Practice Test

MOS Excel: Tables, Sorting and Filtering Data