BITM Exam III

Which of the following file extensions does not support macros?

.xlsm, .xlsb, .xltm, .xlsx X;

Which nested function would create an array of the top four salaries in a dataset where the salaries are in the range D5:D100?

=SEQUENCE(TOPFOUR(D5:D100)), =DMAX(UNIQUE(4),D4:D100), =LARGE(D5:D100,SEQUENCE(4)) X, =LIST(D5:D100,MAX(4));

Which function correctly adds data in cell D25 across multiple worksheets?

=SUM(Monday,Friday:D25), =Monday:D25+Friday:D25, =COUNT(Mon:D25,Tues:D25,Wed:D25,Thurs:D25, Fri:D25), =SUM(Monday:Friday!D25) X;

Which of the following is a properly formatted DAX function?

=SUM([Sales]) X, =Sum(A1:A10), =SUM(QTR1!A1:QTR4!A1), =SUM($A$1:$A$10);

What does a purple triangle in the upper-right corner of a cell indicate?

A circular reference, A hidden comment, A locked cell, A note X;

What does a red triangle in the upper right-hand corner of a cell indicate?

A hidden comment, A circular reference, A locked cell, A note X;

You can use the IFERROR function to avoid displaying an error for which type of error?

A logic error using the wrong cell in multiplication, which is indicated by a green triangle in the corner of the cell., An IFS function that does not contain an exhaustive set of logical tests or a default logical test TRUE, resulting in a #N/A error. X, A COUNTIFS function where one or both criteria are not met, resulting in an error displaying 0., A formula where the value 0 is divided by 10.;

You use Trace Precedents for a cell containing a formula. What indicates an error in the precedents used?

A red tracer arrow displays between the dependent and precedent cells., A warning message box displays onscreen., A blue tracer arrow points to the cell containing the error. X, A Watch Window displays on the right side of the screen.;

A dataset contains a list of customers from several states. What function should you use to create an array that lists each state regardless of how many times it occurs in the dataset?

ARRAY, INDEX, UNIQUE X, SEQUENCE;

You are entering the conditions in a criteria range to perform an advanced filter. You want to find records in which the state abbreviation is CA or WA. Where do you enter these conditions?

As CA or WA in the cell immediately below the STATE label, As CA and WA in the cell immediately below the STATE label, In two cells on the same row with CA below the STATE label and WA below another label X, CA below the STATE label and WA below the CA condition;

Which of the following would cause a run-time error?

Closing a template without saving., Running a macro on a protected worksheet., Saving with the incorrect file type., Adding a comment to a procedure. X;

Which tool should be used to locate low contrast formatting that could make worksheet cells difficult to read?

Compatibility Checker, Accessibility Checker X, Document Inspector, Document Manager;

Which function identifies the lowest value in a database column based on specified criteria located in a criteria range?

DMIN, DMAX, MINIFS, MIN X;

You have a workbook that contains sales data where each sales rep's data is stored on a separate worksheet. Which task is the least likely to be done while the worksheets are grouped?

Enter specific values for the first sales rep., Fill the sales categories across the worksheets., Format the column and row labels at the same time., Format the values with an appropriate number style. X;

Which of the following is automatically created by Excel when using Power Pivot?

Explicit measure, Implicit measure X, KPI, Calculated column;

What Power Add-in is best for visualizing geospatial data?

Get & Transform, Power Pivot, Power View, 3D Maps (Power Map) X;

Which feature should be used to add data with a similar structure to an existing table in Power Query?

Inner join, Merge, Append X, Full join;

Where can you search and download Office templates?

Template gallery, Excel Options, Design tab, Insert tab X;

Column A contains numbers such as 2024001. You enter 2024-001 in the adjoining cell in column B. What feature can you use to quickly complete the text pattern down column B?

Transpose, Text to Columns, Paste Special, Flash Fill X;

The INDIRECT function is designed to obtain data from a variety of locations. Which situation would not produce the desired results?

Using =INDIRECT(B3) to retrieve the value from cell B3., Using =INDIRECT(MondaySales) to create an array of values stored in a range named MondaySales., Using INDIRECT to use data entry that matches a worksheet name, concatenated with a text string representing a cell address, to return a value on another worksheet., Using INDIRECT where the argument contains a cell reference where that cell contains the results of the ADDRESS function. X;

Which data validation alert style should you use to enable a user to enter invalid data but indicate the data does not meet the allowed conditions?

Warning X, Stop, Lock Cell, Failure;

Which of the following statements is true about macro security?

When you add a Macro-Enabled Workbook to the Trust Center, you must enable the content of that file each time you open it., Setting your Trust Center options to include files in a specific folder and then saving macro-enabled files in that folder allows you to open those files with the content enabled. X, Set macro security options on the Developer tab to Secured., Macro-enabled files cannot contain viruses.;

A worksheet contains over 400 rows. The first 10 rows contain input data

and functions containing summary statistics are in the last 10 rows. What is the most efficient method to see the input area and summary statistics at the same time without having to scroll back and forth between sections?, Open multiple windows of the same workbook and use the Arrange Windows command to tile the windows., Display a Watch Window for the input section as you view the summary statistics section., Use the Split command and adjust one pane to see the input section and another pane to see the summary statistics. X, Select the summary statistics and use the Freeze Panes option.;

A spreadsheet enables the user to enter a customer ID

and you want to create a function that identifies the position within the list. Which function should you use?, ARRAY, XMATCH X, INDEX, ADDRESS;

You want to combine text from three columns

but sometimes cells in the second column may be empty. Which function enables you to ignore empty cells without creating extra spaces?, TEXTJOIN, CONCAT X, JOINCELLS, COMBINE;

What combination is most effective to look up two inputs

identify a position horizontally and a position vertically, and then return a value from an array at the intersection of those positions?, ADDRESS functions nested inside an INDEX function, FILTER functions nested inside an XMATCH function, XMATCH functions nested inside an INDEX function X, ADDRESS and IFS functions nested inside an INDIRECT function;

Which join type should be used to merge two tables

keeping only matching records from both sources?, Outer join, Inner join X, Left outer join, Right outer join;

A workbook contains quarterly worksheets that store columns of data for the respective months. You created a summary worksheet where you want to display summary data for all 12 months

one month per column. What is the most efficient method to create this summary?, Create relationships among the worksheets and create a PivotTable., Insert individual formulas with 3-D references to all the cells on all of the worksheets., Use the Consolidate tool, selecting both top row and left column options. X, Insert the SUM function with 3-D references to consolidate data into one column.;

When creating a 3D Map

what is the best way to view your data from different perspectives over time?, Add a Layer, Add Scenes X, Add Dimensions, Create a Custom Region;

You want to display a worksheet within a workbook on a virtual conference call. The workbook contains several worksheets containing confidential data. To prevent yourself from accidentally displaying the confidential data

what should you do with those worksheets?, Delete the worksheets., Add password protection and lock the cells from being changed., Move them to another workbook permanently., Hide the worksheets. X;

In Windows 10

where would you save the template so it is available in the available templates list in Backstage view?, C:\Users\User_name\Libraries\Documents, Submit it to Office.com, C:\Users\Username\Documents\Custom Office Templates X, C:\Users\Downloads;

Cell C5 in the Summary worksheet contains the label January Sales. In this cell

you created a hyperlink to a cell in the January worksheet, but you did not customize the ScreenTip. What ScreenTip displays when you point to cell C5 in the Summary worksheet?, January Sales X, Workbook name, worksheet name, cell reference, and clicking instructions, Create New Document, E-mail Address;

In which programming language are Excel macros written?

Java, C++, VBA X, SQL;

A column contains phone numbers such as (203) 555-1234. What function should you use to extract the last four digits?

LEFT, RIGHT X, MID, LEN;

You want to make sure employees enter data only in particular cells and do not change or delete other data. What is the first step?

Lock cells that you do not want the users to change., Protect the worksheet with a password. X, Use the Mark as Final option., Unlock cells that will be used for data entry.;

Which of the following is not a benefit of using macros?

Macros can be used for repetitive tasks., Macros perform tasks quickly., Macros can be saved and reused in different worksheets., Macros cannot contain viruses. X;

Which of the following is a special character that is used to separate data in a text file?

Position holder, Delimiter X, Column spacer, Start tag;

Which of the following tools can be used to aggregate relational data from multiple sources?

Power Pivot X, 3D Maps (Power Map), Get & Transform, PivotTables;

Which of the following Power BI tools is best suited for editing data before import?

Power Pivot, 3D Maps (Power Map), Power Query Editor X, PivotTables;

What tool should be used to remove fields and format data when importing?

Power Pivot, Power Query Editor X, 3D Maps (Power Map), Relationship builder;