Deck 11: Exploring Pivottable Design

Question 1

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

Answer 1

True

Question 2

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?

A) Step 3: Select the item you want to explore in the Explore box and then click Drill Down.
B) Step 3: Select the item you want to explore in the Explore box and then click Drill Up.
C) Step 2: Select the item you want to drill up on and then click Drill Up.
D) Step 4: Click OK to complete the procedure.

Answer 2

B

Question 3

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 ?

A) Icon Sets
B) Color Scales
C) Top/Bottom Rules
D) Highlight Cells Rules

Answer 3

B

Question 4

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

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

Question 5

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) Calculations > AutoSum
B) Home > View> Calculation Area
C) PivotTable Fields > (Drag a field into) Values
D) Power Pivot > Calculations > Measures > New Measure

Question 6

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

A) Name text box
B) Fields lists box
C) Formula text box
D) Calculated Field dialog box

Question 7

How can one filter a PivotTable using a report filter?

A) Click on the drop down arrow for a Report Filter in the pivot table and Click (All).
B) Click the Report Filter's drop-down arrow, then click on an item to select an item in the list and click OK.
C) Right-click a cell in the pivot table, and click Pivot Table Options, then set the 'Display Fields in Report Filter Area' to 'Down, Then Over' On the Layout & Format tab.
D) Right-click a cell in the pivot table, and click Pivot Table Options, then set the 'Display Fields in Report Filter Area' to Over, Then Down' On the Layout & Format tab.

Question 8

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?

A) Home
B) Insert
C) Formulas
D) Developer

Question 9

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

A) File > Open > Open Dialog box > Text files > Locate and double-click the text file you want to open > Finish
B) File > Save As > Save As dialog box > Save As type box > click Text (Tab delimited) or CSV (Comma delimited) > Browse to location where you want to save the new text file > Save.
C) Cell to insert data from the text file >Data tab > Get External Data > From Text/CSV > Locate and double-click the text file that you want to import > Finish > Import data dialog box > Where do you want to put the data? > New Worksheet > OK
D) Cell to insert data from the text file >Data tab > Get External Data > From Text/CSV > Locate and double-click the text file that you want to import > Finish > Import data dialog box > Properties > Select formatting, and layout options for the imported data > Ok

Question 10

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?

A) Name text box
B) Fields lists box
C) Formula text box
D) Calculated Field dialog box

Question 11

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?

A) Ctrl + values > Group
B) Group > Analyze > Field Settings > Change Custom Name > OK
C) Right-click an item in the group > Ungroup
D) Right-click value > Group > Grouping box > Edit values in Starting at and Ending at checkboxes > Under By, select a time period > OK

Question 12

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) Add to Data Model
B) Update All
C) Measures
D) KPIs

Question 13

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

A) Right-click on the Pivot Chart and select Refresh
B) Click on the Pivot Chart > PivotTable Tools > Analyze > Refresh arrow > Refresh Status
C) Click on the Pivot Chart > PivotTable Tools > Analyze > Refresh arrow > Cancel Refresh
D) Click Analyze > Options > Layout & Format tab > Check Autofit column widths on update and Preserve cell formatting on update boxes

Question 14

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.

Question 15

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.

Question 16

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?

A) A-Design tab, B-Data group
B) A-Developer tab, B-Code group
C) A-Analyze tab, B-Calculations group
D) A-Design tab, B-Table Style Options group

Question 17

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

Question 18

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.

Question 19

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) Alt + F9
B) Alt +F5
C) Ctrl + K
D) Shift +Tab

Question 20

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.

Question 21

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

Question 22

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.