MOS - Microsoft Office Specialist Excel: Formulas and Functions Questions and Answers

Question 1

A financial analyst needs to calculate the total sales for a specific product, 'Gadget', from a sales report. The product names are in column C (C2:C100) and the corresponding sales amounts are in column D (D2:D100). Which of the following formulas will correctly calculate the total sales for only the product 'Gadget'?

Accepted Answer

=SUMIF(C2:C100,"Gadget",D2:D100)

Answer

The SUMIF function is designed to sum values in a range that meet a specific criterion. In this case, it sums the values in the range D2:D100 only where the corresponding cell in the range C2:C100 is equal to "Gadget".

Question 2

You have a formula in cell C1: `=A1+B1`. You use the fill handle to drag this formula down to cell C2. What will the formula in cell C2 be?

Accepted Answer

=A2+B2

Answer

By default, Excel uses relative cell references. When a formula with relative references (like A1 and B1) is copied to another cell, the references adjust relative to the new location. Copying the formula one row down changes the row numbers from 1 to 2, resulting in `=A2+B2`.

Question 3

A project manager is creating a task list. They have the task name in column A and the due date in column B. They want to create a combined text string in column C that reads 'Task Name - Due: [Date]'. For cell C2, with 'Design Mockup' in A2 and '3/15/2026' in B2, which function is best suited to join these elements with a separator?

Accepted Answer

=CONCATENATE(A2," - Due: ",B2)

Answer

The CONCATENATE function (or its modern replacement CONCAT, or the ampersand '&' operator) is used to join several text strings into one string. It allows for the combination of cell values and literal text strings, such as ' - Due: ', to create the desired output.

Question 4

To prevent a cell reference from changing when a formula is copied, you must use an absolute reference. Which of the following is a syntactically correct absolute reference to cell B5?

Accepted Answer

$B$5

Answer

An absolute cell reference in Excel is created by placing a dollar sign ($) before both the column letter and the row number. This 'locks' the reference, so it does not change when the formula is copied or filled into other cells.

Question 5

A user needs to find the correct department for an employee by looking up their Employee ID in a table. The table array is in the range A1:D50, with Employee IDs in column A and Department names in column C. Which formula correctly finds the department for the Employee ID located in cell F2?

Accepted Answer

=VLOOKUP(F2, A1:D50, 3, FALSE)

Answer

The VLOOKUP function searches for a value in the first column of a table array and returns a value in the same row from a specified column. The syntax is VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]). Here, 3 is the correct column index number for column C, and FALSE specifies an exact match.

MOS - Microsoft Office Specialist Certification Practice Test

MOS - Microsoft Office Specialist Certification Practice Test

MOS - Microsoft Office Specialist Excel: Formulas and Functions Questions and Answers