Excel Interview Success: Top 40 Questions and How to Answer Them

Q1: What is a hyperlink? 

A hyperlink is a reference or a link that points to a specific resource, such as a web page, a file, a location within a document, an email address, etc. In Excel, hyperlinks can be used to navigate within the workbook, open external files or web pages, send emails, and more.

Q2: If you need to link a picture to one of the sheets, what option would you use and how would you do that? 

You can use the “Insert Hyperlink” option to link a picture to a specific sheet. Here’s how:

• • Select the picture.
  • Right-click on the picture and choose “Hyperlink” from the context menu.
  • In the “Insert Hyperlink” dialog box, select “Place in This Document” on the left.
  • Choose the desired sheet from the list of available sheets.
  • Click “OK” to confirm.

Q3: What is text-to-columns? 

Text-to-columns is a feature in Excel that allows you to split the content of a single cell/column into multiple cells/columns based on a specified delimiter. This is useful when you have data that needs to be separated into different columns.

Q4: Can you split the data stored in a column into multiple columns? If yes, how? 

Yes, you can use the “Text to Columns” feature to split data stored in a column into multiple columns. Here’s how:

• • Select the column you want to split.
  • Go to the “Data” menu.
  • Click on the “Text to Columns”.
  • Choose the appropriate delimiter (e.g., space, comma, hyphen, etc.) that separates the data.
  • Follow the wizard’s prompts to specify how you want to split the data.
  • Choose the destination cells for the split data.
  • Click “Finish.”

Q5: How to split an Order ID in Excel into three different columns? Assuming your Order IDs are in Column A:

We can use text to columns if there is a delimiter in the Order ID or we can use fixed width if there is a fixed width pattern. Alternatively, we can also use MID, LEFT, or RIGHT functions.

Q6: What is Data Validation? 

Data Validation is a feature in Excel that allows to define rules or restrictions for the type and format of data that can be entered into a cell or a range of cells. It helps prevent data entry errors and ensures consistency in your data.

Q7: Why do we use Data Validation? 

We use Data Validation to control the data that users can enter in specific cells. This helps maintain data integrity, reduce errors, and ensure that the entered data conforms to predefined criteria.

Q8: Explain the steps of creating a drop-down list in Excel.

• • Select the cell where you want to create the drop-down list.
  • Go to the “Data” menu.
  • Click on “Data Validation.”
  • In the “Data Validation” dialog box, select the “Settings” tab.
  • In the “Allow” dropdown, choose “List.”
  • In the “Source” field, either type in the list items separated by commas or select a range of cells that contains the list items.
  • Check the “In-cell dropdown” option.
  • Click “OK.”

Q9: While collecting data from multiple users in Excel, what approach we can follow to solve the data consistency problem?

To ensure consistent data entry for columns like Gender, Geography, Team, and Project, you can use Excel’s Data Validation feature with dropdown lists containing standardized options. This prevents users from entering data in different styles. You can create a reference table with valid options for each column and use Data Validation to enforce these options.

Q10: Explain cell reference. 

Cell references in Excel determine how formulas behave when copied to different cells. The three types are Relative, Absolute, and Mixed.

We can refer to a cell in Excel using three main types of cell references:

• • Relative Reference: A cell reference that adjusts when copied to another cell. For example, if you refer to cell A1 as A1, copying the formula to cell B1 would change the reference to B1.
  • Absolute Reference: A cell reference that doesn’t change when copied to another cell. It’s denoted with a $ symbol. For example, $A$1 will remain the same when copied to other cells.
  • Mixed Reference: A reference that combines relative and absolute references. For example,  $A1 is absolute in column A but relative in row, and A$1 is relative in column but absolute in row.

Q11: What is the difference between Mixed and Absolute references?

A mixed reference contains both relative and absolute elements. In $A1, the column reference is absolute, and the row reference is relative. In A$1, the column reference is relative, and the row reference is absolute.

Q12: What is the importance of Absolute Reference?

Absolute references are crucial when you want to refer to a specific cell or range that shouldn’t change when copied. For instance, when calculating percentages based on a fixed total, using an absolute reference ensures the denominator remains constant.

Q13: Explain MID Function.

The MID function extracts a specific number of characters from a text string, starting at a specified position. Its syntax is MID(text, start_num, num_chars). 

Example: =MID(“Hello, World!”, 8, 5) returns “World”.

Q14: What is VLOOKUP? 

VLOOKUP is a function used to search for a value in the first column of a range and return a corresponding value from another column. Its syntax is VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]).

Q15: What is the difference between VLOOKUP and HLOOKUP?

• VLOOKUP searches vertically within a column, while HLOOKUP searches horizontally within a row. 
• VLOOKUP returns values from a column, and HLOOKUP returns values from a row.

Q16: What is the difference between INDEX MATCH and VLOOKUP?

• • VLOOKUP only looks up values vertically (in a single column) while INDEX MATCH can perform both vertical and horizontal lookups.
  • VLOOKUP requires the lookup value to be in the leftmost column of the lookup table. INDEX MATCH can look up values from any column and does not have this limitation.
  • VLOOKUP returns a value from a specific column in the lookup table. INDEX MATCH can return values from any column in the return range.

Q17: In column A we have “Customer Names”.  We need to find the number of blank cells in “Customer Name” column. How can we find the number of missing names: 

We can use the COUNTBLANK function to find the number of blank cells in the “Customer Name” column. The formula would be something like =COUNTBLANK(Sheet1!A:A).

Q18: What is the use of CONCATENATE Function?

CONCATENATE is used to combine multiple text strings into one. For example, =CONCATENATE(“Hello”, ” “, “World”) returns “Hello World”.

Q19: How can we find a substring from a string?

We can use functions like LEFT, RIGHT, and MID to extract substrings from a string. For instance, =MID(A1, 3, 5) would extract characters 3 to 7 from cell A1.

Q20: What is Conditional Formatting? 

Conditional Formatting allows us to format cells based on certain conditions. For example, we can highlight cells with values above a threshold, making data interpretation easier.

Q21: What is a Heatmap? 

A heatmap is a visual representation of data where individual values are represented by colors. In Excel, we can create a heatmap using Conditional Formatting to visually highlight data trends.

Q22: What is a Nested Function? 

A nested function is when one function is used as an argument within another function. For instance, using the SUM function within an IF function is a nested function.

Q23: Give me an example of the TEXT Function.

=TEXT(A1, “dd-mmm-yyyy”) formats the date in cell A1 as “dd-mmm-yyyy”.

Q24: What is NETWORDAYS Function? 

NETWORDAYS calculates the number of working days between two dates, excluding weekends and specified holidays.

Q25: What is the difference between DAYS360 and NETWORKDAYS function? 

DAYS360 calculates the difference between two dates based on a 360-day year, ignoring months and years. NETWORKDAYS calculates working days between two dates.

Q26: Give me an example of a nested function.

Example of a Nested Function: =IF(A1>10, SUM(B1:B10), 0) is a nested function where the SUM function is used within an IF function.

Another example: =INDEX(dataRange, MATCH(lookup_value, range,0), MATCH(lookup_value, range,0))

Q27: You have a date in cell A1. Which function you will use to extract the full month name from Date?

We can use the TEXT function.  =TEXT(A1, “mmmm”) where A1 contains a date, returns the full month name.

Q28: What is the difference between COUNT and COUNTA functions?

COUNT function counts cells containing numbers while COUNTA counts cells containing any data (text, numbers, dates, etc.).

Q29: How can we find the second-highest score from column A?

 We can use LARGE function. It returns the value based on rank input. So we can write =LARGE(Sheet1!A:A, 2) returns the second-largest value from the “Score” column.

Q30: What is the difference between SUMIFS and SUMIF?

SUMIF sums values based on a single condition, while SUMIFS sums values based on single or multiple conditions.

Q31: Can you explain the SUBTOTAL function?

SUBTOTAL function calculates a subtotal for a range of data, while allowing you to specify which type of calculation to perform (e.g., SUM, AVERAGE, COUNT) and whether hidden rows should be included or not. We can select the appropriate function number based on the need.

Q32: Explain the SUMPRODUCT function. 

SUMPRODUCT multiplies corresponding values in multiple arrays and then sums those products.

Q33: What is the significance of 0 or FALSE in VLOOKUP? 

Using 0 or FALSE as the [range_lookup] argument in VLOOKUP ensures an exact match is required. Using 1 or TRUE allows approximate matches.

Q34:Explain OFFSET function.

OFFSET returns a reference to a range that is a specified number of rows and columns away from a given reference (starting) point.

The syntax of the OFFSET function is:

=OFFSET(reference, rows, cols, [height], [width])

The OFFSET function is often used in combination with other functions like SUM, AVERAGE, or COUNT to perform calculations on dynamic ranges.

Q35: What is the use of a Dual-axis or Secondary-axis chart? 

A dual-axis or secondary axis is used when two different data series with distinct scales need to be displayed on the same chart, preventing one series from dominating the visualization.

Q36: Explain the Calculated field and its usage.

A calculated field is a field that you create in a Pivot Table by performing calculations on existing fields. It’s useful when you need to perform calculations that are not directly available in the source data.

Q37: What is a Slicer? 

A slicer is a visual control element that allows you to filter data in a Pivot Table or Pivot Chart by selecting items from a list.

Q38: How do you create a Pivot Table, and why would you use one?

To create a PivotTable, select the data range, go to the “Insert” tab, and click “PivotTable.” PivotTables are used to summarize and analyze data, enabling us to quickly generate insights by grouping, filtering, and calculating data based on various criteria.

Q39: How would you remove duplicate values from a dataset?

We can use the “Remove Duplicates” feature under the “Data” menu. Select the data range, go to “Data -> Remove Duplicates,” and choose the columns to consider for duplicates. This helps in cleaning up data and keeps only unique values/records.

Q40: What is the purpose of the TRIM function?

The TRIM function removes leading and trailing spaces from text.