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?

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?

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?

How can one filter a PivotTable using a report filter?

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

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?

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

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

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 ?

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

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?

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?

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.

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

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?

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.

Which of these steps will you choose to refresh data in a Pivot Chart?

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?

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?