Exam 11: Exploring Pivottable Design

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.

Which of these series of clicks will you select to import a text file by opening it in Excel?

After drilling down, Esteban wants to drill up to analyze summary data. To do so, he follows the following steps: 1. Select the item to drill up on in the PivotTable hierarchy you have drilled into. 2. Click the Quick Explore button that appears in the lower-right corner of the selection. 3. Use the Quick Explore button to reach the data you want. Which step did he miss while following this procedure?

Johan wants to create a PivotTable using Power Pivot. During the procedure, to add a Table to PowerPivot, he selects a cell in a Table and clicks Power Pivot tab > Tables group > X. What is X in the procedure?

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.

If Power View is enabled, one can create a new Power View report page by clicking Insert tab > Power View button.

How can one filter a PivotTable using a report filter?

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 these steps will you choose to refresh data in a Pivot Chart?

In which of these boxes will you enter the name of the new row or column that you want to show the calculated field in a PivotChart?

Nolan wants to group data in a Pivot table to analyze a subset of data. Which of these series of clicks/ selections/ entries should he follow to do so?

Portia wants to create a calculated measure in Excel. To do this, she creates a PivotChart report using the data stored in an Analysis Services cube. Next, she clicks on a tab "A" and then in group "B," she chooses OLAP Tools > MDX Calculated Measure to open the New Calculated Measure dialog box. What can A and B be in the procedure?

On which of the following tabs of the Power Pivot window will you select Refresh All under Refresh command to refresh all Power Pivot tables?

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.

Amin wants to extract data from a Pivot table he has created for items sold in the East region of his company in a year. The table contains the following entries: Cells A1, B1, A3 and B3 have the headings "Region," "East", "Items" and "Sum" in them respectively. Cells A4 to A6 has the names of Items: A3 sheets, A4 sheets and A5 sheets in them, respectively. Cells B4 to B6 had their respective selling prices. Cells B7 has the title "Grand Total" and cell C7 has the grand total of all selling prices in the table. To calculate the total for the item, A4 sheets, Amin can use the formula: =GETPIVOTDATA("Total","Product","A4 sheets").

Marcos wants to refresh the data in a Pivot table manually. He clicks on the PivotTable to show the PivotTable Tools on the ribbon. Which combination of keys should he press next to complete the procedure?

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

Which of the following series of clicks will you follow after clicking on a column to create a measure in the Power Pivot Window using AutoSum?

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

To add a title to a Power View report, one should click on the placeholder "Click here to add a title" at the top of the Power View.