Excel Mock Questions - Learn DA-DS Course in Hindi

Excel Mock Interview Questions

Top 30+ Excel Interview Questions and Answers (Latest 2024)

1. What is a cell address in Excel?

A cell address is an address that is used to identify a specific cell in the sheet. A combination of the relevant column letter and a row number is used to denote it.

Ex: B2

2. What do you mean by Relative cell referencing and Absolute cell referencing in MS Excel?

A relative reference (e.g., A1) changes when the formula is copied to another cell, while an absolute reference (e.g., $A$1) remains constant.

3. How do you freeze panes in Excel?

Freeze panes keep the rows and columns visible while scrolling through a worksheet.

Any row or column can be locked using Freeze Panes. Even if we scroll the sheet vertically or horizontally, the locked row or column will remain visible on the screen.
You can find the freeze pane by clicking on the view panel.

4. How can you restrict someone from copying a cell from your worksheet?

1. First, choose the data you want to protect.

2. Hit Ctrl + Shift + F. The Format Cells tab appears. Go to the Protection tab. Check Locked and click OK.

3. Next, go to the Review tab and select Protect Sheet. Enter the password to protect the sheet.

5. How is a Formula different from a Function in Excel?

Formula	Function
The formula is like an equation in Excel, the user types in that. It can be any type of calculation depending on the user’s choice.	Whereas, a function in Excel is a predefined calculation which is in-built in Excel.
Manually typing out a formula every time you need to perform a calculation, consumes more time. Ex: = A1+A2+A3	However, performing calculations becomes more comfortable and faster while working with functions. Ex: = SUM(A1:A3)

6. Mention the order of operations used in Excel while evaluating formulas.

The order of operations in Excel is referred to as PEDMAS. Shown below is the order of precedence while performing an Excel operation.

Parentheses
Exponentiation
Division/Multiplication
Addition
Subtraction

7. How will you write the formula for the following? – Multiply the value in cell A1 by 10, add the result by 5, and divide it by 2.

we have to follow the PEDMAS Precedence. The correct answer is ((A1*10)+5)/2.

Answers such as =A1*10+5/2 and =(A1*10)+5/2 are not correct. We must put parentheses brackets after a particular operation.

8. What is the difference between count, counta, and countblank?

1. COUNT

It counts the number of cells that contain numeric values only. Cells that have string values, special characters, and blank cells will not be counted.

2. COUNTA

It counts the number of cells that contain any form of content. Cells that have string values, special characters, and numeric values will be counted. However, a blank cell will not be counted.

3. COUNTBLANK

It counts the number of blank cells only. Cells that have content will not be counted.

9. What is the shortcut to add a filter to a table?

The filter option is used when you want to display only specific data from the entire dataset. The shortcut key to add a filter to a table is Ctrl+Shift+L.

10. How do you create a hyperlink in Excel?

Hyperlinks are used to navigate between worksheets and files/websites. To create a hyperlink, the shortcut used is Ctrl+K.

The ‘Insert Hyperlink’ box appears. Enter the address and the text to display.

11. How can we merge multiple cells text strings in a cell?

To merge text strings present in multiple cells into one cell, you can use the CONCATENATE().

Another way of combining cell values is by using the “&” operator.

12.What function can you use to find the current date and time?

NOW()

13. What is the function to calculate the number of cells that meet a specific condition?

=COUNTIF(range, condition)

14. What is the shortcut to open a new Excel workbook?

Ctrl + N

15. How many report formats are available in Excel?

Compact Form
Outline Form
Tabular Form

16. What are the wildcards in Excel?

Ans: There are 3 wildcards available in Excel.

Asterisk (*): This wildcard can be used to match any character. For example, “Ni*” can be matched with”Ninja “,”Nick “,”Nice “, and many more starting with “Ni”.
Question Mark (?): This is used to match any single character. For example, “Ninj?” will match with “Ninja”.
Tilde (~): It’s used to find a wildcard character (*, ?, ~) in a block of text.

17. How do we use the SUMIF() function in Excel?

The SUMIF() function adds the cell values specified by a given condition or criteria.

18. How do you bold text in Excel?

Ctrl + B
This will toggle the bold formatting on and off for the selected cell or text.

19. What is a Pivot Table?

A Pivot Table in Excel is a powerful tool that allows you to summarize, analyze, and present data in a flexible and interactive way

20. How do you delete a row in Excel?

Right-click the row number and select “Delete”.

21. What are macros?

A macro in Excel is a way to automate repetitive tasks by recording a sequence of actions you perform in a spreadsheet. Macros are written in VBA (Visual Basic for Applications), which is the programming language used for Excel automation.

22. What is the use of comment?

Comments are used to explain a formula that has been used in a cell.
Comments are intended to leave notes on a cell for other users.

23. What are charts in MS Excel?

In Excel, charts are used to visualize data in a graphical format. By selecting an item from the Insert tab’s Chart group, a user can create any chart type, including column, bar, line, pie, scatter, and so on.

24. What are the 5 basic functions of Excel?

The five basic functions of Excel are:

SUM: Adds up a range of cells.
AVERAGE: Calculates the average value of a range of cells.
COUNT: Counts the number of cells in a range.
MAX: Returns the highest value in a range of cells.
MIN: Returns the lowest value in a range of cells.

25. How do you remove duplicates in a dataset?

Select the data, go to the “Data” tab, and click “Remove Duplicates”.

26. What is the formula to round a number to the nearest integer?

=ROUND(number, 0)

27. What function can you use to find the exact position of a text string within another text?

=FIND(find_text, within_text)

28. How do you wrap text within a cell?

Select the cell, go to the “Home” tab, and click “Wrap Text”.

29. How do you calculate the difference between two dates in Excel?

=DATEDIF(start_date, end_date, "unit") (e.g., “d” for days, “m” for months, “y” for years).

30. What is the use of the IF function?

The IF function is used to perform a logical test and return one value if true and another if false.
- Syntax: =IF(logical_test, value_if_true, value_if_false)
- Example: =IF(A2 >= 50, "Pass", "Fail") returns “Pass” if A1 is greater than or equal to 50, otherwise “Fail”.

31. How do you use the VLOOKUP function?

VLOOKUP is used to search for a value in the first column of a table and return a value in the same row from a specified column.
- Syntax: =VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
- Example: =VLOOKUP(A2, B2:D10, 2, FALSE) – This searches for the value in cell A2 in the first column of the range B2:D10 and returns the corresponding value from the second column.