all questions by writing down the correct answer among the options given in the question below. Question 1 Marilyn added a few calculated fields and items to a PivotTable and wants to document the calculations on a separate worksheet. She can click the Fields, Items, & Sets button, and then click _____. Document Calculations List Formulas Show Formulas Formula Sheet Question 2 When you freeze the top row of a worksheet, what part of the worksheet can you scroll? all rows below the top row columns starting with column C rows starting with row 4 only the lower-right pane of the worksheet Question 3 To delete a defined name, open theName Manager dialog box, click the defined name, and then click Deleted. True False Question 4 To illustrate different data levels using small graphics representing values, which of the following can you use? icon sets data bars Highlight Cells Rules color scales Question 5 Allison wants to sort the Reservation data in a PivotTable by days of the week, Sunday through Monday. What does Allison need to create? a calculated field a custom list a new date field a relationship Question 6 Which of the following can help you quickly format a cell range with labels in the left column and top row, and totals in the bottom row? table style cell style font style number format Question 7 Marcos has a PivotTable that shows revenue by quarter. He wants to calculate the differences between the revenue for one quarter and another. What can he do? Ungroup the date field. Create a calculated item. Create a measure. Change the summary function to SUMGROUP. Question 8 To outline data, select the rows or columns you want to group, and then click the Group button on the Data tab. True False Question 9 Bruno has a table with four fields and dozens of records. To make the fields easier to distinguish, he can apply filtered columns. True False Question 10 A calculated field is a user-defined formula for calculations across one or more fields. True False Question 11 Mia inserted a plain table in a worksheet. She wants to change the black borders and white fill to make the table more attractive and easier to use. What is the quickest way for her to change the appearance of the table? Change the shading and border colors of the cells. Filter the table. Change the table style. Convert the table to a range. Question 12 Adele wants to filter a customers table to show customers in Denver or customers with invoices over $2500. What type of filter should she use? Number AutoFilter Advanced filter Text with wildcard filter Date AutoFilter Question 13 To use defined names in existing formulas, click the Define Name arrow, and then click _____. Get Defined Names Apply Names Use Defined Names Named Ranges Question 14 In the Expenses workbook, Cassie defined the range D10:G10 with the name RentExpenses. Which of the following formulas can replace the formula =SUM(Expenses!D10:G10)? '=SUM([Expenses]!Rent), =SUM(RentExpenses) =SUM([RentExpenses], D10:G10) =SUM(D10:G10) Question 15 To create a workbook containing text, formulas, macros, and formatting that you use repeatedly, you create a _____. master model template standard Question 16 Which of the following buttons on the Home tab can you use to insert a row in a table? Format as Table Insert & Merge Insert Add Row Question 17 Which of the following PivotTable layouts displays all fields in separate columns and subtotal rows at the bottom of each group, similar to a table? Compact Outline Tabular Condense Question 18 Which of the following is an unacceptable name for an Excel table? Employee_Tbl _SalesTable (Customers) ProductTable Question 19 What does the Highlight Duplicate Values conditional formatting rule do when you apply it to a range? It highlights duplicate values in the range. It deletes duplicate values from the range. It converts duplicate values so they are unique. It moves the duplicate values to another area of the worksheet. Question 20 Kylie created a table with a Skill Number column that contains values from 400 to 500. What type of filter can she use to select records with skill numbers less than 450? Text filter Top Values filter Number filter Form filter Question 21 When you arrange windows in a Vertical or Side by Side layout, you can use synchronized _____ to more easily compare the two worksheets. grouping updating inking scrolling Question 22 Which of the following functions would you use to find the column location of specified text? MATCH INDEX VLOOKUP HLOOKUP Question 23 When Micah opens a workbook containing external references, a dialog box appears indicating the workbook contains links to external sources. Micah trusts the linked data, so he can click theTrust button to update the data. True False Question 24 When you select a cell in a worksheet and then click the Split button on the View tab, how many panes appear in the worksheet window? six two four three Question 25 Tamara wants to count the number of values in the Rating column that are greater than 7. Which of the following functions should she use? DCOUNT DCOUNTA COUNTIF COUNTAIF Question 26 Joe wants to format several worksheets at the same time. What is the easiest way for him to perform this task? Create a custom view of the worksheets. Group the worksheets. View the worksheets side by side. Link the worksheets. Question 27 Which of the following is the default name assigned to the first PivotTable in a workbook? Table1 New PivotTable PivotTable1 Pivot1 Question 28 You use the Values area to limit a PivotChart to values that satisfy a specified criteria. True False Question 29 Before Barry inserts new formulas in the range A5:F5, he wants to clear the cell contents. How can he do so? Drag the fill handle from cell A5 to cell F5. Select the range and then press DELETE. Select the range and then click the Clear Formats button. Select the range and then click Clear on the AutoFill Options menu. Question 30 When you create a calculated item for a field in a PivotTable, it appears as a new item within the field. True False Question 31 Juanita lists expenses in a PivotTable field named Expenses. She wants to filter the PivotTable to display data only for expenses greater than $1,000. What type of filter should she use? Manual Date Label Value Question 32 A _____ is text or an image you click to open a webpage or file. 3-D reference Hyperlink ScreenTip Template Question 33 If a worksheet arranges lookup values in rows rather than columns, which function is best for retrieving data from the lookup table? VLOOKUP HLOOKUP INDEX MATCH Question 34 A(n) _____ groups and summarizes data in a concise format of rows and columns. Area chart PivotTable Slicer Filter Question 35 Cedric has a table listing customer data, including the date of their purchase. He can sort the Date data in descending order to quickly find customers with recent purchases. True False Question 36 Helen wants to resize slicer buttons to exact dimensions. What can she use to do so? Slicer Settings button in the Slicer group Height and Width boxes in the Buttons group Slicer Styles gallery Slicer Size box in the Slicer group Question 37 Pam inserted links to data in another workbook, but now wants to replace the links with data and calculated values. She can break the links to the other workbook. True False Question 38 The scores of a student in two subjects are inserted in cells B2 and C2. The passing score for each subject is 60. Which of the following formulas returns TRUE if at least one score is greater than or equal to 60, or else it returns FALSE? =IF(B2>=60, C2>=60) =OR(B2>=60, C2>=60) =AND(B2>=60, C2>=60) =NOT(OR(B2>=60, C2>=60)) Question 39 The Remove Duplicates tool locates and deletes records that are duplicated across more than one field. True False Question 40 An Excel table contains columns named Salary and Commission. Which of the following formulas uses a structural reference to sum the values in the Salary through Commission fields? =SUM(Salary:Commission) =SUM(Salary!Commission) =SUM([Salary]:[Commission]) =SUM("Salary":"Commission") Question 41 Excel limits you to one calculated field per PivotTable. True False Question 42 A workbook template has which of the following file extensions? .xltx .xls .xlsx .xlst Question 43 Evelyn wants to work on different parts of a workbook at the same time by displaying worksheets in separate windows. Which option on the View tab should she select? Arrange All New Window Zoom Page Break Preview Question 44 Which of these is the default layout for a newly created Pivot table? Compact Form Outline Form Tabular Form Chart Form Question 45 Roberta added a calculated field to the Values area of a PivotTable, where it appears as "Sum of ORDERS." Where can she change the text displayed for this field? Number Format dialog box Calculated Field Format dialog box Value Field Settings dialog box PivotTable Options dialog box Question 46 When you open a trusted workbook containing external links, you can update the workbook with the latest data. True False Question 47 To ungroup worksheets after grouping them and working on them simultaneously, right-click any worksheet tab in the group and click Ungroup Sheets. True False Question 48 What happens when you click the sheet tab of a worksheet not included in a worksheet group? You add the worksheet to the group. You create a new group of the first and last worksheets. You ungroup the worksheets. The worksheet grouping does not change. Question 49 Edwin wants to insert a PivotChart to summarize sales data. On which of the following does he need to base the new PivotChart? an existing PivotTable another PivotChart a column or bar chart on another worksheet a slicer on the same worksheet Question 50 If a PivotTable field is expanded, you can click the minus button to collapse the field and hide field details. True False