Reference no: EM133399396
Questions
1. How can you most easily create a Pivot Table from external data if you cannot import the data directly as an Excel table?
Convert the data to your most recent version of Excel, then email the table to yourself and create the table from there.
Use Insert on the Ribbon to create a Pivot Table, click Use and external data source > Choose connection, and then click Add.
Use Insert on the Ribbon to create a Pivot Table, click Use an external data source > Choose connection, and then navigate to the data source.
Insert a Pivot Table from Insert on the Ribbon, click Use an external data source, and then click New Worksheet.
2. Since Ribbon no longer provides commands for consolidating data, how can you access the Pivot Table and PivotChart Wizard?
Press Ctrl, then P, and then W.
Press Alt, then D, and then P.
Press Alt+P simultaneously, and then press D.
Press Ctrl, then D, and then P.
3. How would you prevent Excel from applying more than one conditional format to a PivotTable cell?
Check the Stop If True box next to the rule in the Conditional Formatting Rules Manager.
No action is needed -- this is the default behavior.
Go to the Excel Options dialog box, display the Formatting page, and check the Apply single conditional format only box.
In the Conditional Formatting Rules Manager, right-click the rule and then click Apply This Rule Only.
4. If you do not see the controls to pivot a PivotTable, which interface element should you display?
the PivotTable Analyze contextual tab on the Ribbon
the +/- buttons
the Field List task pane
the field headers
5. For most business data sets, which type of trendline will you add to a PivotChart?
geometric
multi-linear
exponential
linear
6. You can change your PivotTable's configuration so that subtotals _____.
are not displayed at all
appear above each subgroup
all of these answers
appear below each subgroup
7. Once you create a macro for your Purchase Order Pivot Table, how do you save the macro?
Ensure the file name has a .xlsx extension, then click Save.
Use Save As, then in Save as Type select Visual Basic Editor.
Ensure the file name includes Macro in the name, then click Save.
Use Save As, then in Save as Type select Excel Macro-Enabled Workbook.
8. Which summary operation would you use to find the largest value in a PivotTable's data area?
MAX
CEILING
AVERAGE
MIN
9. How would you arrange your PivotTable to show the quantity sold for each product and each product's contribution to total quantity sold?
Use two copies of the same field and change Show Values As to % of Column Total.
Drag the Unit Price field to Values in the Field List area.
Use two copies of the same field and dragging Quantity to Values in the Field List area.
Drag the Quantity field to Values in the Field List area.
10. To avoid errors, what is the first step you should take after you start recording a PivotTable macro?
Remove all formatting from the PivotTable.
Remove all fields from the data area.
Clear the PivotTable.
Create a new PivotTable on a new worksheet.
10. If you do not see the controls to pivot a PivotTable, which interface element should you display?
Disable all macros without notifications.
Disable all macros with notifications.
Enable all macros.
Disable all macros, except digitally signed macros.
11. What is the main disadvantage to summarizing more than one data field in a PivotTable?
The PivotTable can become too wide to fit on one screen.
You can't use subtotals when summarizing more than one data field.
A PivotTable can only display one data field at a time.
You can't use grand totals when summarizing more than one data field.
12. When you are creating relationships between tables, the column that contains unique values for each row is known as the _____.
data model
related table
foreign key
index
13. When you create a formula by using = then clicking on a value cell in a Pivot Table, which action are you taking?
You are using the value shown in the cell in the formula you are defining.
You are using the range of cells in the Pivot Table in the formula you are defining.
You are using a shortcut instead of typing out the cell references by column and row numbers.
You are using the underlying data in the formula you are defining.
14. When you used a data model to create a Pivot Table, you receive some errors. Which is the likely reason?
Your Pivot Table was created from an Excel table.
Your Pivot Table contains empty cells.
Your relationships were not properly defined.
Your Pivot Table did not update changes to your Excel table.
15. Which function is used to refer to a cell in a PivotTable?
GETDATA
GETPIVOTDATA
GETPIVOT
PIVOTDATA
16. Which custom sort order (custom list) is built into Excel?
names of seasons
three-letter abbreviations of month names
U.S. regions
U.S. state names
17. Your Pivot Table has the products you purchase in Column A, and Sum of Total Purchases in Column B. Which mistake do users often make when creating filters?
filtering in Sum of Total Purchases
filtering by Begins With
filtering by the values associated with the products
filtering by using Value Filters