Deck 6: Managing Data With Data Tools

Question 1

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

Answer 1

C

Question 2

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

Answer 2

C

Question 3

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.

Answer 3

B

Question 4

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 5

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 6

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 7

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 8

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.

Question 9

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 10

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 11

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 12

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 13

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 14

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 15

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 16

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 17

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 18

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 19

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 20

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 21

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 22

Nisha wants to freeze all rows above row 4 in a worksheet she is working on to keep them visible while scrolling through the rest of the worksheet. Which of the following series of clicks will help her do so?

A) Row 4 > View tab > Windows group > Freeze Panes > Freeze Panes
B) Row 4 > View tab > Windows group > Freeze Panes > Freeze Top Row
C) Row 3 > View tab > Windows group > Freeze Panes > Freeze Top Row > Arrange All
D) Row 3 > View tab > Windows group > Freeze Panes > Freeze First Column > View Side by Side

Question 23

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 24

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

Question 25

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 26

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 27

Ursula wants to freeze the header row so that it remains visible while she scrolls down the worksheet. To do so, she can click the View tab and then click Freeze Top Row.

Question 28

Helen wants to resize slicer buttons to exact dimensions. She selects the slicer, then clicks the Slicer Tools under Options tab. What should she select next to reset the slicer buttons to exact dimensions?

A) Height and Width under Size group
B) Height and Width under Buttons group
C) Format cells under Format in Cells group
D) Filter under Sort & Filter in Editing group

Question 29

Mitchel wants to remove a row of unnecessary data in a table created in MS Excel. What should he do after selecting the row?

A) Right-click and select Insert from the drop-down menu, and pick Table Rows Above.
B) Right-click and select Insert from the drop-down menu, and pick Table Rows Below.
C) Click on the Design tab in the Tools group, and then click Remove Duplicates.
D) Click the arrow next to Delete on the Home tab, in the Cells group, and then click Delete Table Rows.

Question 30

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 31

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 32

Michelle finished a 5 kilometer run in 180th position. The organizers shared an Excel spreadsheet with names of all the participants and the time they took to complete the race. The top 15 finishers are listed in rows 2 to 16. Michelle wants to compare her time against theirs. How can she do so?

A) Select row 17 and click View tab > Window group > Split.
B) Select rows 2 to 16 and click View tab > Window group > Split.
C) Select rows 2 to 16 and click View tab > Window group > New Window > Split.
D) Select row 16 and click View tab > Window group > Split > Arrange All.

Question 33

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 34

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 35

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 36

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 37

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 38

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 39

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 40

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 41

Which of these combination of keys will you click to clear an outline?

A) Worksheet > Data tab > Outline group > Group > AutoOutline
B) Worksheet > Data tab > Outline group > Ungroup > Clear Outline
C) Worksheet > Data tab > Outline group > Group > Group > Rows > OK
D) Worksheet > Data tab > Outline group > Ungroup > Ungroup > Rows > OK

Question 42

Leigh-Ann wants to filter data in a PIvotChart using a slicer. She clicks on the table and then selects Slicer in a particular group on the insert tab. She then ticks the check boxes for the columns that she wants to filter in the Insert Slicers dialog box and clicks OK. What tab did click during the procedure?

A) Links
B) Filters
C) Symbols
D) Sparklines

Question 43

Which of these keys will you hold to select more than one item while inserting a slicer into a PivotTable?

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

Question 44

Grace wants to connect a slicer to two PivotCharts. In which of these dialog boxes can she select the pivot tables she wants to link to the slicer and click OK?

A) Insert Slicers
B) Remove "Product"
C) Report Connections
D) Multi-select "Product"

Question 45

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 46

Sala wants to unfreeze the first column of his worksheet. Which of the following series of clicks will help him do so?

A) View tab > Windows group > Freeze Panes > Unfreeze Panes
B) View tab > Windows group > Freeze Panes > Freeze Top Column > Arrange All
C) View tab > Windows group > Freeze Panes > Unfreeze Panes> Arrange All
D) View tab > Windows group > Freeze Panes > Freeze First Column > Unfreeze Panes

Question 47

Which of these tabs will you select to alter the font as desired while changing the slicer style?

A) Font tab
B) Analyze tab
C) Border tab
D) Slicer Contextual tool tab

Question 48

To view a worksheet without gridlines showing on the screen, which of the following would you uncheck?

A) Gridlines View
B) Gridlines Print
C) Headings View
D) Headings Print

Question 49

How can you remove Split panes in a worksheet you are working on?

A) Click the View tab in the Ribbon, then click the Split button in the Window group.
B) Click the Page Layout tab in the Ribbon, then click the Bring Forward button in the Arrange group.
C) Click the View tab in the Ribbon, then click the Split button and then the Hide button in the Window group.
D) Click the Page layout tab in the Ribbon, then click the Insert Breaks button and choose from the drop down menu in the Page Setup group.

Question 50

Hari wants to connect a slicer to three pivot tables so that he can control the entire dashboard with a single slicer. To do so, he selects the pivot tables to connect a slicer, then selects a cell in any of them, then clicks on three group/tab/button in a series. Next from the "Insert Slicer" dialog box, he selects the column to use as a filter in the slicer and clicks OK. What can be the three clicks he made during the procedure?

A) Analyze tab → Filter → Refresh
B) Analyze tab → Filter → Insert Slicer
C) Analyze tab → Show/Hide → Field List
D) Analyze tab → Show/Hide → Field Button