Question 1

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?

Accepted Answer

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 
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 2

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?

Accepted Answer

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 
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 3

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?

Accepted Answer

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]]) 
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 4

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?

Accepted Answer

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

Question 5

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.

Accepted Answer

A) True 
 B)False

Question 6

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?

Accepted Answer

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

Question 7

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 10 and <15.

Accepted Answer

A) True 
 B)False

Question 8

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?

Accepted Answer

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

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.

Accepted Answer

A) True 
 B)False

Question 10

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.

Accepted Answer

A) True 
 B)False

Question 11

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.

Accepted Answer

A) True 
 B)False

Question 12

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 .

Accepted Answer

A) True 
 B)False

Question 13

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.

Accepted Answer

A) True 
 B)False

Question 14

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?

Accepted Answer

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

Question 15

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

Accepted Answer

A) True 
 B)False

Question 16

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?

Accepted Answer

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

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.

Accepted Answer

A) True 
 B)False

Question 18

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

Accepted Answer

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

Question 19

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

Accepted Answer

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

Question 20

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?

Accepted Answer

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

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?

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?

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?

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.

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?

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.

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 .

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.

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?

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.

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

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

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?

Creating a Worksheet and a Chart

Formulas,functions,and Formatting

Working With Large Worksheets, charting, and What-If Analysis

Financial Functions, data Tables, and Amortization Schedules

Working With Multiple Worksheets and Workbooks

Creating Templates, importing Data, and Working With Smartart, images, and Screenshots

Working With Trendlines, pivot Table Reports, pivotchart Reports, and Slicers

Formula Auditing, data Validation, and Complex Problem Solving

Data Analysis With Power Tools and Creating Macros

User Interfaces, visual Basic for Applications Vba, and Collaboration Features in Excel

Filters

Exam 6: Creating, sorting, and Querying a Table

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?

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?

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?

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.

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?

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.

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 .

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.

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?

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.

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

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

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?

Creating a Worksheet and a Chart

Formulas,functions,and Formatting

Working With Large Worksheets, charting, and What-If Analysis

Financial Functions, data Tables, and Amortization Schedules

Working With Multiple Worksheets and Workbooks

Creating Templates, importing Data, and Working With Smartart, images, and Screenshots

Working With Trendlines, pivot Table Reports, pivotchart Reports, and Slicers

Formula Auditing, data Validation, and Complex Problem Solving

Data Analysis With Power Tools and Creating Macros

User Interfaces, visual Basic for Applications Vba, and Collaboration Features in Excel

Filters