Robert Half Assessment Microsoft Excel Proficiency Questions and Answers

Question 1

A financial analyst needs to summarize a large dataset of sales transactions. The goal is to create an interactive report that allows for quick reorganization of data to view total sales by region, by product category, and by quarter. Which of the following Excel features is best suited for this task?

Accepted Answer

PivotTable

Answer

A PivotTable is an interactive tool specifically designed to summarize, analyze, explore, and present large amounts of data. It allows users to easily drag and drop fields to change the data's orientation, group it by different categories (like region or quarter), and perform calculations (like sum of sales) without altering the source data. Conditional Formatting changes cell appearance based on rules, Data Validation restricts data entry, and VLOOKUP retrieves specific data from a table, none of which provide the interactive summarization required.

Question 2

You are cleaning a dataset and want to highlight all cells in column C that contain a value greater than 500. Which Excel feature would you use to accomplish this automatically?

Accepted Answer

Conditional Formatting

Answer

Conditional Formatting allows you to apply specific formatting, such as changing the cell color, to cells that meet certain criteria. In this case, you would create a rule to highlight cells with values greater than 500. While Filter can hide rows that don't meet the criteria, it doesn't change the formatting of the cells themselves. Sort arranges data, and Chart visualizes it.

Question 3

What is the primary difference between the VLOOKUP and HLOOKUP functions in Excel?

Accepted Answer

VLOOKUP searches for a value in the first column of a table array, while HLOOKUP searches in the first row.

Answer

The 'V' in VLOOKUP stands for Vertical, and it searches for a value in the leftmost column of a data array and returns a value in the same row from a specified column. The 'H' in HLOOKUP stands for Horizontal, and it searches for a value in the top row of a data array and returns a value in the same column from a specified row. Both functions can handle text and numbers and can perform both exact and approximate matches.

Question 4

In the formula `=INDEX(A1:D10, 5, 3)`, what does the function return?

Accepted Answer

The value from the cell at the intersection of the 5th row and 3rd column within the range A1:D10.

Answer

The INDEX function returns a value or the reference to a value from within a table or range. The syntax is `INDEX(array, row_num, [column_num])`. In this case, the array is A1:D10, the `row_num` is 5, and the `column_num` is 3. Therefore, the formula will return the value from the cell located in the 5th row and 3rd column of the specified range, which is cell C5.

Question 5

A user wants to combine the text from cell A2 ('John') and cell B2 ('Smith') into cell C2 to display 'John Smith'. Which of the following formulas would achieve this correctly, including the space between the first and last name?

Accepted Answer

=CONCATENATE(A2, " ", B2)

Answer

The CONCATENATE function (or the `&` operator) is used to join two or more text strings into one string. To include a space between the names, a space character " " must be included as a separate argument within the function. The formula `=CONCATENATE(A2, " ", B2)` correctly joins the value of A2, a space, and the value of B2. The `+` operator and SUM function are for numerical calculations, and JOIN is not a standard Excel function for this purpose.

Robert Half Assessment Test Practice Test

Robert Half Assessment Test Practice Test

Robert Half Assessment Microsoft Excel Proficiency Questions and Answers