Deck 7: Summarizing Data With Pivottables

Question 1

Which of these functions will you use to find the variance of a population of all of the values to be summarized?

A) Var
B) Varp
C) StDev
D) StDevp

Answer 1

B

Question 2

To hide the PivotTable Field list, right click on any cell of the pivot table, and select Hide Field List option from the context menu.

Answer 2

True

Question 3

Which of these is the default layout for a newly created Pivot table?

A) Compact Form
B) Outline Form
C) Tabular Form
D) Chart Form

Answer 3

A

Question 4

Chastity wants to rename her pivot table from PivotTable1 to ProductData. She clicks the Pivot table then goes to PivotTable Tools > Analyze, and clicks the X text box in the PivotTable group. She then types a new name and presses ENTER. What can X be in the procedure?

A) Chart Name
B) Table Name
C) PivotTable Name
D) Pivot Chart Name

Question 5

Izzy wants to move a pivot table to a new worksheet. Which of these should she select after the following clicks to do so: PivotTable > Analyze tab > PivotTable tools > Move PivotTable > Move PivotTable dialog box > Choose where you want the PivotTable to be placed?

A) New Worksheet
B) Blank Worksheet
C) Fresh Worksheet
D) Create Worksheet

Question 6

One can use the areas section (at the bottom) of the Field List to rearrange fields the way they want by dragging them between the four areas.

Question 7

Alejandro wants to change the color of a Pivot Chart he has created to make it a little more impactful. To do so, he clicks the chart he wants to change, then clicks Chart Styles in the upper right corner next to chart, clicks X and selects the color scheme he wants.

A) Style
B) Color
C) Fonts
D) Effects

Question 8

Luca wants to insert a pivot table. He clicks on a single cell inside the data set, then proceeds to step X, and finally clicks OK in the dialog box that appears after step 2. What can step 2 be?

A) Select a pivot table under Pivot Chart & Pivot Tables in the Charts group on the Charts tab.
B) Click PivotTable on the Insert tab, in the Tables group.
C) Click Recommended PivotTables on the Insert tab, in the Tables group.
D) Select a pivot table under Pivot Chart in the Charts group on the Charts tab.

Question 9

To filter data in a PivotTable, follow the series of selections: A cell in the PivotTable > Analyze > Insert Slicer > Select the fields you want to create slicers for > OK > Select the items you want to show in the PivotTable.

Question 10

A worksheet has the following data in it: Cell range A2 to A10 in column A lists the years from 2010 to 2019. Cell range B2 to B10 in column B lists the types of food items sold during each year: Tomatoes, Oranges, Pears, Pineapples, Potatoes, and Onions. Cell range C2 to C10 in column C lists the sale of each item in each year. If we insert the formula =COUNTIFS("=Onions","B2:B10") in cell A11, it will give us the count of the number of times "Onions" has been listed in the worksheet.

Question 11

Edwin wants to insert a Pivot Chart to summarize raw data. Which of the following combination of clicks/selections/entries should he use to do this?

A) Select Cell > Insert > PivotChart > OK
B) Select Cell > Insert > Recommended PivotTables > OK
C) Select Cell > PivotTable Tools > Analyze > PivotChart > Select a chart > OK
D) Select Cell > Insert > Table > Create Table dialog box > Fill data in Where is the data for your table? box > OK

Question 12

To insert an option button into a worksheet, click on the following series: Developer tab > Insert > Form Controls > Image with a tick inside a rectangular box.

Question 13

Right-click the pivot item, then click Expand to expand a field in a PivotTable.

Question 14

Ingrid wants to change the name associated with a Pivot Chart in Excel. To do so, she right-clicks on the pivot table and then selects X from the popup menu. She then enters the new name for the Pivot Chart in the Name field in the window that appears and clicks

A) Hide Field List
B) Number Format
C) PivotTable Options
D) Value Field Settings

Question 15

To display the PivotTable Field list, right-click any cell in the pivot table and select Show Field List from the menu.

Question 16

Right-click the pivot item, then click Collapse to collapse a field in a PivotTable.

Question 17

Horatio wants to hide field headers in a Pivot table to increase the display of the screen. He clicks on a cell in the pivot table, then clicks on ANALYZE in the ribbon. What should he do next to complete the procedure?

A) Click on Clear in the Actions group.
B) Click on +/- Buttons in the Show group.
C) Click on Field Headers in the Show group.
D) Click on Fields, Items & Sets in the Calculations group.

Question 18

Jin wants to insert a recommended pivot table. He clicks a single cell inside the data set, then proceeds to step X and finally clicks OK in the dialog box that appears after step 2. What can step 2 be?

A) Select a pivot table under Pivot Chart & Pivot Tables in the Charts group on the Charts tab.
B) Click PivotTable on the Insert tab, in the Tables group.
C) Click Recommended PivotTables on the Insert tab, in the Tables group.
D) Select a pivot table under Pivot Chart in the Charts group on the Charts tab.

Question 19

To clear all the data from a PivotChart, click the PivotChart. Then on the Analyze tab, in the Data group, click Clear, and then click Clear All.

Question 20

To insert a scroll bar (form control) into a worksheet, click the Developer tab > Insert > Form Controls > Button labeled as "A."

Question 21

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.5
C) $12.50
D) 1002

Question 22

One can rearrange fields in the PivotTable by clicking the field name in one of the areas, and then selecting Move Up command to move the field up one position in the area.

Question 23

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 24

To add fields to a PivotTable, one can use the field section of the Field List and tick the box next to field names to place them in the default area of the Field List.

Question 25

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

Question 26

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 27

To see the data source for the selected chart, click the Design tab, then under PivotChart Tools click Select Data to open the Select Data Source window.

Question 28

Salena has created a chart to show the sale of products of her firm in different cities during different years. The chart has only one series with all the columns of the same color. The Year and Location fields are both in the Rows area. Now, she wants to create columns with different colors, for which she will have to move one of the fields into the Column area in the PivotTable Field List. To do so, Salena can follow the following series of clicks/ entries/ selections to do so: Pivot Chart > Pivot Chart Field window > Drag the Year field from the Axis box (Categories) to the Legend (Series) box. This will create a series for each city, and show the city's columns in a different color.

Question 29

To use more than one filters per field, one can right-click a cell in the pivot table, and click PivotTable Options, then click the Layout & Format tab. Finally, under Filters, add a check mark to "Allow multiple filters per field" and click OK.

Question 30

Xan wants to change the number format in a PivotTable as well a PivotChart. To do so, she can right-click on a cell in the value field in the pivot table, then click Value Field Settings, and then the Number Format button in the popup menu, then select the formatting that she wants in the Format Cells dialog box and finally click OK.

Question 31

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 32

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 33

To remove a field, in the PivotTable Field List clear the check box of the field you want to remove In the Choose fields to add to report box.

Question 34

A worksheet has the following data in it: Cell range A2 to A10 in column A lists the categories of food items in a store: Vegetables and Fruits. Cell range B2 to B10 in column B lists the types of food items for each category: Cucumbers, Corn, Pears, Lettuce, Potatoes, and Scallions. Cell range C2 to C10 in column C lists the sale of each item during the month. If we insert the formula =SUMIFS(A2:A10,B2:B10,=P* ) in cell A11, it will give us the total sale of products that begin with the letter P.

Question 35

To add a calculated field to a PivotTable, select a cell in the pivot table, then under the PivotTable Tools tab, click the Analyze tab. In the Calculations group, click Fields, Items, & Sets, and then click Calculated Field. Enter a name for the calculated field, type a formula in the Formula box, then click Add to save the calculated field, and click Close.

Question 36

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 37

Katrin has created a new table with only data for sale of products in the East region of her company. She wants to change the pivot table showing the sale of products in both East and West regions of the country to use that source, so that it can be sent to a manager in the West region, without revealing the East region's data. To do so, she can follow the following series of clicks/ entries/ selections: Cell > PivotTable Tools tab > Data group > Top section of the Change Data Source command > Change PivotTable Data Source dialog box > Select a new data source range > OK.

Question 38

To select the Row Labels in a PivotChart, point to the bottom border of a Row Label heading and click to select the row labels for that field when the pointer changes to a thick black arrow.

Question 39

When we add fields to the Column Labels area, they appear as headings on the left of the table.