Exam 6: Creating, sorting, and Querying a Table

Kylie is a corporate trainer.She creates a table with three columns,with column headings as: Skill Number,Skill Name and Learning Objective.She wants to display numbers in the first column in the range of 400 to 500.What can Kylie select as X and Y in the following series of clicks or entries to do so: Click filter icon on the column heading > X > Y > (Enter value 400 in dialog box on top)> (Enter value 500 in dialog box below it)> OK?

The score of a student in two subjects are inserted in the B2 and C2 cells and the passing score for each subject is 60.Which of these functions will you insert in the D2 cell so that it returns TRUE if at least one score is greater than or equal to 60,or else it returns FALSE?

To apply banded rows to a table,we can select the range of cells that we want to apply banded rows to,then click on the following: Insert tab > Table > OK in Create table dialog box > Design tab > Table Styles Options group > Check Banded rows box.

Luke owns his own business.creates a table with three columns titled as: Date of order,Regions and Products Ordered.He applied filters on the columns to analyze the data he entered.He now wants to clear filters from the first column.What should Luke do after clicking on the column heading?

Josh teaches sixth grade math.He created a table showing marks that his students scored on an assessment-the last column on the right displays this information.He now wants to add another column to the right showing percentage of marks that each students scored.What should he do after selecting the current last column on the right?

Which of the following will you select as X in the given series of clicks to remove a data series from a chart: Chart > Chart Tools > Design Tab > Select Data > Data series > X?

Warren creates a table listing the height and weight of students in his class.He enters the heading "Name" in cell A3 and the names of students in cells A4 to A9,the heading "Height" in cell B3 and the height of students in cells B4 to B9,the heading "Weight" in cell C3 and the weight of students in cells C4 to C9.What formula can he use to look up the height of one of the students,Sari,whose name,height,and weight are mentioned in cells A7,B7,and C7,respectively?

Tomoko has created a table for the sale of sweatshirts in her shop last month with the headings,Customer's Name,Sweatshirt Size,Order ID and Payment Date.She wants to sort the number of sweatshirts of each size -Small,Medium,Large and Extra Large,bought by different customers.To do so,she can first sort her worksheet by sweatshirt size from smallest to largest,then click Subtotal command under the Data tab.Tomoko can then click the drop-down arrow for the At each change in field and select 'Sweatshirt Size',then select COUNT to count the number of sweatshirts ordered in each size.Then she can select 'Sweatshirt Size' in the Add subtotal to field and finally click OK.

Barry creates a table showing ranks and percentage his students obtained in their final exams.He enters the heading "Name" in cell A3 and the names of students in cells A4 to A9,the heading "Rank" in cell B3 and the height of students in cells B4 to B9,the heading "Percentage" in cell C3 and the weight of students in cells C4 to C9.One of the students,Cara's name,rank,and percentage are mentioned in cells A7,B7,and C7,respectively.He types Cara's rank in cell A14.What formula can he use in cell B14 to find the column in which Cara's rank is?

Which of the following will you select as X in the following series of clicks to convert a table to range: Table > Table Tools > Design > X > Convert to Range?

The score of a student in two subjects are inserted in B2 and C2 cell and the passing score for each subject is 60.which of these functions will you insert in D2 cell to get FALSE as the result if the student has passed and TRUE as the result if the student has failed?

Which of the following will you select as X in the following series of clicks to change the change the color of a data series: (double-click)Data Series > Format Data Series > Series Options > X > Color > Color of your choice?

A teacher creates a table that contains data about winners of a Sports Day events in 4 columns,A to D under headings : Event,Rank,First name,Last name and Grade,respectively.To sort the table by Event and then by Rank,she can select a cell in the column A,select the Data tab on the Ribbon,then select the Sort command.Under the Sort dialog box,in the Sort by drop down menu,she can select Event,click Add Level and then select Rank in the Then by drop down menu and finally click OK.

To group specific rows in Excel,select them,then click the Group command on the Data tab in the Outline group.Finally select Rows or Columns in the Group dialog box and click OK.

Judith wants to sort data about information of students in a school into three columns: first by gender,second by state and third by age.To do so,she can select all the cells in the list,then click the Data tab,click the Sort button in the Sort & Filter group,then click the Add Level button to add the first sorting level.From the Sort by dropdown,she can select the name of the first column,then the options in Sort on and Order.She can further click the Add Level button to add the next two columns and select options for each from the drop down boxes.

Which of the following will you select as X in the series of clicks to add a color scale to a range of cells: (Select)Range of cells > Home tab > Styles group > Conditional formatting > X > (select)Subtype ?

Which of the following can help you quickly format a cell range with labels in the left column and top row,and totals in the bottom row?

To expand data views using the Outline feature,select the rows with similar data by clicking and dragging on the rows numbers to the left of your data.Then click on Data tab > Group > - sign.

A student enters the names of months in cells A2 to A5 with the heading "Month" in cell A1.Then he enters the number of days in each moth in cells B2 to B5 with the heading "Days" in cell B2.In order to arrange the names of the months in cells A6 to E6 with heading "Month" in cell A6 and the number of days each month in cells A7 to E7 with heading "Days" in cell A7,he can use the formula =TRANSPOSE(A1:B5).

A small grocery owner creates a table for the different types of fruits sold in his shop.He inserts headings in cells A1,B1,C1,and D1 as Order ID,Fruit,Unit Price,and Quantity.He then inserts Order ID 1001 to 1004 for each fruit in cells A2 to A5,respectively,type of fruits-Apples,Oranges,Mangoes,and Grapes in cells B2 to B5,respectively,unit price of each fruit as $10,$9,$30,and $18 in cells C2 to C5,respectively,and quantity of each fruit as 5,7,8,and 9 in cells D2 to D5,respectively.In cell F1,he inserts the formula: =LOOKUP(1003,A1:A5,B1:B5).What will the following function return?