Deck 6: Creating, sorting, and Querying a Table

Question 1

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) Effects
B) Fill & Line
C) Shadow Effects
D) Series Options

Answer 1

B

Question 2

Cedric has a list of data that he wants to group and summarize,For this,he can create an outline of up to eight levels,one for each group.

Answer 2

True

Question 3

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?

A) =IF(B2>=60,C2>=60)
B) =OR(B2>=60,C2>=60)
C) =AND(B2>=60,C2>=60)
D) =NOT(OR(B2>=60,C2>=60))

Answer 3

B

Question 4

Soledad wants to create an auto outline in a Excel worksheet to hide details and only show the headers.To do this,she can click into any cell,then click Data tab > Group > Auto Outline.

Question 5

What will you select as X in the following series of clicks/entries/selections to apply a table style: Select cell to format as table > X > Format as table > Desired table style?

A) Data tab
B) Insert tab
C) Home tab
D) Review tab

Question 6

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.

Question 7

To collapse 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.

Question 8

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?

A) Right click on it and select Insert from the drop-down menu.
B) Right click on it,select Format Cells from the drop-down menu and select Add New Column.
C) Click on it,then click the arrow next to Insert in the drop-down menu.
D) Double click on it,select Format cells from the drop-down menu and click on Insert.

Question 9

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.

Question 10

The score of a student inserted in the B2 cell is 65 and in the C2 cell is 75.Which of the following functions will you insert in the D2 cell so that it returns FALSE if any of the conditions are false for the values in the B2 and C2 cells?

A) =IF(B2>=70,C2>=80)
B) =OR(B2>=70,C2>=80)
C) =AND(B2>=70,C2>=80)
D) =NOT(OR(B2>=70,C2>=80))

Question 11

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?

A) Tools group
B) Themes group
C) Page Setup group
D) Background group

Question 12

Haley wants to delete a column in a table created in MS Excel to get rid of some unwanted data.What should she do after selecting the column?

A) Left click on the selected column and press the Esc key.
B) Right click on the selected column and press the Delete key.
C) Double click on the selected column and press the Tab key.
D) Right click on the selected column and press Ctrl + Z key.

Question 13

What will you do in step X in the following series of clicks to change the font size of chart title: (Right-click)Chart Title > Font > Font tab > X> OK?

A) Select a color in Color box.
B) Enter +Heading in Latin text font box.
C) Select Normal Spacing in Spacing box.
D) Enter the desired size of the chart title in the Size box.

Question 14

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?

A) Add
B) Edit
C) Remove
D) Delete

Question 15

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.

Question 16

Dante wants to calculate the currently monthly subtotals for the sales of golf balls in his store.Which of the following will he select as X in the series of clicks to do so: Cell in the range > Data > X > Subtotal > OK?

A) Outline
B) Data Tools
C) Sort & Filter
D) Connections

Question 17

Which of the following keys will you press when the cell cursor is in the last cell of the last record (row)to add new data for the next record?

A) Esc
B) Ctrl
C) Tab
D) Shift

Question 18

To ungroup a range of cells in a row in a worksheet,select the rows or columns you wish to ungroup,then click the Group command in the Outline group on the Data tab.In the Group dialog box,select Rows and click OK .

Question 19

Ngabile wants to insert a Treemap chart for a data set.To do so,she can click on the following series: Range of cells > Page Layout tab > Apps group > Insert Hierarchy Chart > Treemap.

Question 20

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?

A) =IF(B2>=60,C2>=60)
B) =NOT(OR(B2>=60,C2>=60))
C) =OR(B2>=60,"Pass","Fail")
D) =AND(B2>=60,"Pass","Fail")

Question 21

Which of the following series of clicks will you select to remove banded columns from a table?

A) Home tab > Format as Table > Clear
B) Home tab > Format as Table > Custom > Clear
C) Page layout tab > Format as Table > Click on cell > Clear
D) Home tab > Format as Table > New Table Style > New Table Style dialog box > Table Element box > Format > Clear

Question 22

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 ?

A) Icon Sets
B) Color Scales
C) Top/Bottom Rules
D) Highlight Cells Rules

Question 23

A jewelry designer creates an excel sheet to list different items he sold on the previous day.Cells A1,B1,C1,and D1 contain headings-Order ID,Unit price,and Quantity,respectively.Cells A2 to A6 list the order ID for items sold-10001,10002,10003,10004,and 10005,respectively.Cells B2 to B6 list their respective unit price-$15,$18.5,$12,$9.5,and $6.Cells C2 to C6 list their respective quantities-20,32,16,15,and 10.If he enters the formula in cell D1 as: =VLOOKUP (10005,A1: H3,3,FALSE),what will the formula return?

A) 10
B) $6
C) $12
D) 10003

Question 24

Which of the following will you select as X in the following series of clicks to format a range as a table: Home tab > Styles group > X > Table Styles?

A) Cell Style
B) Cell Effects
C) Format as Table
D) Conditional Formatting

Question 25

Adele wants to extract a list of unique items in a database.Which of the following will she select in the series of clicks to do so: Select cell > Data tab > Advanced > Select option under Action (X)> Specify List range > Select a starting cell for copying to location > Check box beside Unique records only > OK?

A) Filter the list,in place
B) Copy to another location
C) Filter unique records
D) Copy unique records

Question 26

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?

A) =INDEX(A3:C9,4,2)
B) =INDEX(A7:C7,4,2)
C) =INDEX(A7:B7,2,4)
D) =INDEX(A3:B9,2,4)

Question 27

Kathy wants to rename a table she created to list the chapters from a textbook,and the topics they cover.She selects a cell in the table,then clicks on a Tab X on the Ribbon under the Table Tools tab,clicks on the Table Name box to select the existing name,and then types the new name "Book A" and presses the Y key.What can X and Y be in this series of clicks/selections/entries?

A) X-Insert tab,Y-Shift key
B) X-Home tab,Y-PgDn key
C) X-Design tab,Y-Enter key
D) X-Page Layout tab,Y-Esc key

Question 28

A shopkeeper creates an Excel sheet to list different items he sold today.Rows A1 to A3 contain headings-Order ID,Selling price,and Quantity,respectively.Cells B1 to H1 list the order ID for the items sold-1001,1002,1003,1004,and 1005,respectively.Cells B2 to H2 list their respective selling price-$10,$12.5,$14,$7.5,and $8.Cells B3 to H3 list their respective quantities-10,12,6,5,and 13.If he enters the formula in cell J1 as: =HLOOKUP (1004,A1: H3,2,FALSE),what will the formula return?

A) 5
B) $7.50
C) $12.50
D) 1002

Question 29

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?

A) Click on Data tab and click the Filter button.
B) Click the Filter icon button next to the column heading and then click Clear Filter from <"Date of order">.
C) Highlight the header row of the column and press the keys Alt+D+F+F.
D) Highlight the header row of the column and press keys CTRL + R.

Question 30

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?

A) X-Text Filters,Y-Ends with
B) X-Text Filters,Y-Begins with
C) X-Number Filters,Y-Between
D) X-Number Filters,Y-Does not equal

Question 31

Which dialog box opens after selecting a cell in the list of prepared data,clicking the Insert tab on Ribbon and then clicking the Table command in the Tables group?

A) New Table
B) Insert Table
C) Create Table
D) Include Table

Question 32

Shelley creates a table containing the marks of Language Arts students in her class with these columns: Names of Students and Marks.She now wants to see the names of students who scored exactly 60 marks.What will Shelley do after selecting the column header arrow for the column with heading Marks?

A) Check the box beside Select All and the number 60 and click OK.
B) Uncheck (Select All),select the box beside the number 60 and click OK.
C) Select Number Filters > Between > (Enter value 0 in dialog box on top)> (Enter value 60 in dialog box below it)> OK.
D) Select Number Filters > Less Than or Equal To > (Enter value 0 in dialog box on top)> (Enter value 60 in dialog box below it)> OK.

Question 33

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

Question 34

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.

Question 35

Which of the following boxes will you check after clicking on a table and then selecting Table Tools > Design,to add a total row to a table?

A) Total Row
B) Header Row
C) Footer Row
D) Banded Rows

Question 36

The components of a structured reference are as follows: Table 1 is Table Name,[Net Amount] and [Commission Amount] are column specifiers and [#Totals] and [#Increase] are item specifiers.Which of these can be the formula for the structured reference?

A) =SUM(#Totals[[Table1],[Net Amount]],#Increase[[Table1],[Commission Amount]])
B) =SUM(Net Amount[[#Totals],[able1]],Commission Amount[[#Increase],[Table1]])
C) =SUM(Table1[[#Totals],[Net Amount]],Table1[[#Increase],[Commission Amount]])
D) =SUM(Table1[[#Totals],[#Increase]],Table1[[Net Amount],[Commission Amount]])

Question 37

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?

A) =MATCH (A14,A3:C9,1)
B) =MATCH (A14,A3:C3,0)
C) =MATCH (B14,A3:C3,0)
D) =MATCH (B14,A4:C9,1)

Question 38

Which of the following will you do to reapply a filter to a table after the steps: Selecting any cell in the filtered table and clicking the Data tab?

A) Clicking Subtotal in the Outline group
B) Clicking Reapply in the Sort & Filter group
C) Clicking Consolidate in the Data Tools group
D) Clicking Refresh All in the Connections group

Question 39

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?

A) 8
B) 30
C) 1003
D) Mangoes

Question 40

Which of the following will you do to manually turn off autofilter in a table between the steps: Clicking any cell in the filtered range and clicking the Filter button?

A) Clicking the Data tab on the Excel Ribbon
B) Clicking the Insert tab on the Excel Ribbon
C) Clicking the Review tab on the Excel Ribbon
D) Clicking the Formulas tab on the Excel Ribbon

Question 41

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.

Question 42

The manager of a footwear company creates a table that contains data in three columns,A to C under headings: Names of customers,Date of order,and Size of footwear ordered,respectively.To select a separate table to sort the number of footwear that were ordered on different dates,she can select the cell range she wants to sort,select the Data tab on the Ribbon,then click the Sort command.In the Sort dialog box that appears,she can choose the column she want to sort by,decide the sorting order and click OK.

Question 43

A teacher creates a table that contains data in three columns,A to C under headings : First name,Last name,and Date of birth,respectively.To sort the table by the last names of students from A to Z,she can select a cell in the column B,select the Data tab on the Ribbon,then click the Ascending command.

Question 44

A manager creates data for cost of products of the firm he works for.He enters headings in cells A1,B1,C1 and D1 as Order ID,Quantity,Unit Cost and Total Cost respectively.He mentions the criteria >1000 and >=5 in cells A2 and B2 respectively.Cells A3 to A10 with heading "Order ID" in A4 cell contain data for the order IDs,cells B3 to B10 with heading "Quantity" in B3 cell contain data for quantities for each order ID,cells C3 to C10 with heading "Unit Cost" in C3 cell contain data for unit cost of each order ID and cells D3 to D10 with heading "Total Cost" in D3 cell contain data for total cost of each order ID.He can use the formula: =DCOUNT(A4:D10,"Unit Cost",A1:B2)
to count the occurrences that meet the conditions in A1:B2.

Question 45

Gregory creates data for different colors of paints he has bought to paint his house.He enters headings in cells A1,B1,C1,and D1 as Color,Price,Quantity,and Total,respectively.He mentions the criteria: Green and >3 in cells A2 and C2,respectively.Cells A4 to A10 with heading "Colors" in A4 cell contain data for the color of paints bought,cells B4 to B10 with heading "Price" in B4 cell contain data for the price of each color of paint,cells C4 to C10 with heading "Quantity" in C4 cell contain data for the quantity of each paint bought and cells D4 to D10 with heading "Total" in D4 cell contain data of total cost of each color of paint bought.Gregory can use the formula: =DCOUNTA(A1:D10,"Price",A1:D2)to determine the average price value from the column labeled "Price," entries where the colors are green and the quantity is less than 3.

Question 46

In which of these boxes will you list all the possible fields that can be included in your formula in a PivotTable?

A) Name text box
B) Fields lists box
C) Formula text box
D) Calculated Field dialog box

Question 47

To open a workbook after you start Excel,you can use the Open Other Workbooks command on the Navigation bar.

Question 48

A student creates data for trees in an orchard.He enters headings in cells A1,B1,C1,D1 and E1 as Tree,Height,Age,Yield,and Height,respectively.He mentions the criteria ="=Mango",>10,="Orange",>12 and <15 in cells A2,B2,A3,and B3,respectively.Cells A4 to A10 with heading "Type of trees" in A4 cell contain data for the type of trees,cells B4 to B10 with heading "Height" in B4 cell contain data for the height of trees,cells C4 to C10 with heading 'Age' in C4 cell contain data for the age of trees and cells D4 to D10 with heading "Yield" in D4 cell contain data of their annual yield.He can use the formula: =DGET(A5:E10,"Yield",A1:E3)to extract the value of yield that matches the specified criteria.

Question 49

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).

Question 50

If you wish to see how formatting changes affect your table,the Preview list on the right side of the New Table Style box that opens under Table Styles gallery lets you do so.

Question 51

To select a table to copy it to another worksheet,you can click anywhere in the table,and then press CTRL+ C to select and copy the table data in the entire table.

Question 52

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.

Question 53

A student creates data for trees in an orchard.He enters headings in cells A1,B1,C1,D1,and E1 as Tree,Height,Age,Yield,and Height respectively.He mentions the criteria ="=Mango",>10,="Orange" and <15 in cells A2,B2,A3 and E2 respectively.Cells A4 to A10 with heading "Type of trees" in A4 cell contain data for the type of trees,cells B4 to B10 with heading "Height" in B4 cell contain data for the height of trees,cells C4 to C10 with heading "Age" in C4 cell contain data for the age of trees and cells D4 to D10 with heading "Yield" in D4 cell contain data of their annual yield.He can use the formula: =DCOUNTA(A5:D10,"Yield",A1:E2)to count the rows containing Mango in column A with a height >10 and <15.

Question 54

To delete duplicate records from a table,select the range of cells,then click Remove Duplicates on the Data tab in the Data Tools group,then select one or more columns under Columns.Click OK,and then click Ok again to dismiss a message that indicate the number of duplicate values removed or how many unique values remain.

Question 55

We can use the Remove Duplicates feature to permanently delete the duplicate data by selecting the range of cells that has duplicate values we want to remove,then clicking Data > Remove Duplicates,and then checking the columns where we want to remove the duplicates under Columns.

Question 56

Which of the following helps you move around a worksheet that is too large to fit on the screen at once?

A) sheet tabs
B) arguments
C) scroll bars
D) operators

Question 57

Rogelio wants to use a named range formula in a cell.To do so,he can select the cell,press F2 to edit the cell,then select (Insert > Name > Paste)and select the name for the cell.

Question 58

Ned wants to calculate the total sale of a product during quarters 3 and 4 of a financial year.He creates a table of quarterly sales of different products o the company in different regions.In the table,cells B1 to B20 with heading "Quarter" list the quarters of the years I to IV,cells C1 to C20 with heading "Product" lists the different products and cells D1 to D20 with heading "Sales" list the sales amount for each quarter and product in dollars.The criteria specified-Quarter and Product are in cells H5 and I5 and their values >3 and Balloons respectively are mentioned in cells H6 and I6.To calculate the total sale of a particular product,he can use the formula =DSUM(B1:D20,"Sales",H5:I6).

Question 59

To remove duplicate values from an Excel worksheet,click Data > Data Tools > Remove Duplicates.

Question 60

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?

A) table style
B) cell style
C) font style
D) number format

Question 61

To illustrate different data levels using small graphics representing values,which of the following can you use?

A) icon sets
B) data bars
C) Highlight Cells Rules
D) color scales

Question 62

If you see a cells with blue fills of varying lengths representing the values,the cells most likely have ________ applied to them.

A) themes
B) icon sets
C) data bars
D) cell styles