Intermediate Excel Interview Questions for Data Analysis

How does the AND() function work in Excel?

Excel’s AND() function checks to see if a set of rules is true overall. If all the rules are correct, it says TRUE; otherwise, it says FALSE. For example, =AND(A1>1, B1<5) checks if A1 is greater than one and less than 5. It only says TRUE if both are correct.

How can one use Excel to acquire the current time and date?

Excel’s NOW () function is a great way to acquire the current time and date.

What is a Microsoft Excel macro?

A macro in Microsoft Excel is a set of directions or orders that make it easier to do things repeatedly. It’s like recording steps that can be played repeatedly to complete the same job.

What is the LOOKUP in Excel?

The LOOKUP tool in the spreadsheet allows you to find precise or partial matches. The VLOOKUP option allows the user to search for data vertically. The HLOOKUP option works on the horizontal plane.

Can you use multiple data formats in PivotTables?

Yes, Pivot Tables support many data formats. Excel recognizes and applies suitable formatting based on the data type in each field.

How do you build a Pivot Table?

To Create a Pivot Table in Excel:

• Click the Data tab in the Ribbon.
• In the Data Tools group, select the Data Validation button.
• Go to Configurations > Allow>List
• Select an array.

How do you extract unique values in Excel?

To detect unique values in Excel, choose your data range and then navigate to Data > Sort & Filter > Advanced.

Click Data > Data Tools > Remove Duplicates to remove duplicates and retain unique values permanently.

What Is an Excel Dropdown List?

You will get a dropdown list with predefined alternatives when you click on a cell in Excel.

How Do You Use the Name Box Function?

Select the box adjacent to the formula bar in Excel, input the cell reference or range name, and then hit Enter to use the Name Box.

How Do the INDEX and MATCH Functions Work?

The index function returns a value from a range based on the row number.

= INDEX(range, row_number)

The match function returns the relative position of a value in the range.

= MATCH(lookup_value, range, match_type)

Match_types include largest/smallest values that are less than or equal to lookup_value and precise matches.

Can You Explain Conditional Formatting?

You may alter the appearance of cells in Excel according to predefined circumstances using conditional formatting. By highlighting important information and making statistics easier to understand, it helps.

How do you identify duplicate values in an Excel column?

Duplicate values can be highlighted using CONDITIONAL FORMATTING. OR apply the COUNTIF function as seen below. For example, cells D4:D7 store values.

=COUNTIF(D4:D7,D4)

Apply the filter on the column wherein you applied the COUNTIF function and select values greater than 1.

Excel helps data analysts change and look at data to find patterns. It arranges and shows facts so that decisions can be made. It does data cleaning, transformation, report and dashboard creation, and more. Preparing for popular Excel interview questions can help you get the job. Here, you can find a collection of Excel interview questions and some example responses for data analysts.