The modern world revolves around data, and in order to keep up with the times, one must be able to manage data and deal with tables. MS Excel is used to manage data and deal with tables.
In this blog, we will discuss Excel Interview Questions, which will give you an in-depth understanding and will aid with your interview preparation.
Excel Interview Questions for Freshers
1. What is a cell address in Excel?
Ans: Questions related to cell address are very important if we consider excel interview questions.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.
Cell Address
2. What is Freeze Panes in MS-Excel?
Ans: This is one of the important excel interview questions. 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.
Freeze Panes
3. What is the difference between count, counta, and countblank?
Ans: Here’s the difference between count, counta and countblank function.
count: Calculate the total number of cells containing only numeric values. Cells with string values, special characters, or blank cells will be ignored.
counta: Calculate the total number of cell containing any content. Blank cells will be ignored.
countblank: It only counts the amount of blank cells, as the name implies. Content-filled cells will not be taken into account.
4. What is the shortcut to add a filter to a table?
Ans: When you wish to display only specified data from the complete dataset, you utilise the filter method. There is no change to the data as a result of this. Ctrl+Shift+L is a keyboard shortcut for adding a filter to a table.
5. How do you create a hyperlink in Excel?
Ans: Hyperlinks are used to link worksheets and files/websites together. Ctrl+K is the shortcut for making a hyperlink. The box titled "Insert Hyperlink" appears. Fill in the address and the text that will be displayed.
The above dialog box will appear after clicking CTRL+K.
6. What is Ribbon?
Ans: The ribbon is a section of the application that runs along the top and contains all of the menu buttons and toolbars available in Excel. The ribbon is divided into tabs, each of which has a set of commands for usage in the software.
7. How many report formats are available in Excel?
Ans: There are 3 report formats that are available in Excel.
Compact Form
Outline Form
Tabular Form
8. 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.
9. Is it possible to create a Pivot Table using multiple sources of data?
Ans: Yes, a pivot table may be made from several sheets. There must be a common row in both tables for this to work. This will be the first table's primary key and the second table's foreign key. Create a link between the tables before constructing the pivot table.
10. How many data formats are available in Excel?
Ans: This is a very important question considering excel interview questions. In Excel, there are 11 different types of data formats.
Number: It is commonly used for displaying numbers.
Currency: Currency is a term that refers to monetary formats in general.
Accounting: The currency symbols and decimal points are aligned in a column.
Date: Date and time serial numbers are displayed as data values.
Time: Date and time serial numbers are displayed as date values in time formats.
Percentage: Multiply the cell value by 100 and display the result as a percentage.
Fraction: They show the values of the cells as a fraction.
Scientific: The cell values are displayed in a scientific manner.
Text: Text format cells are handled as text even if they contain a number; the cell is presented precisely as it was entered.
Special: They're particularly useful for keeping track of list and database values.
Custom: Using one of the existing codes as a starting point, type the number format code.
Excel Intermediate Interview Questions
11. What is the IF function in Excel?
Ans: It determines whether or not specific criteria are true. If the condition is true, the result or output will be as expected. If the condition is false, the result or output will be different. For example,
In B8 Column, 70 is printed as the condition B7 > 50 results in true.
12. Which function is used to determine the day of the week for a date?
Ans: We can use the WEEKDAY() function to determine the day of the week. It returns the day of the week and starts counting the day from Sunday.
13. What is the AND function in Excel?
Ans: The AND function, like the IF function, performs a logical function and it is important considering excel interview questions. The AND function will analyze at least one mathematical expression in another cell in the spreadsheet to determine whether the output will be true or false. It is possible to observe the output of multiple cells in a single cell by using the AND function.
14. How is cell reference useful in the calculation?
Ans: Considering excel interview questions cell reference is one of the important question. Cell reference is used to prevent writing the data several times for calculation purposes. When you build a formula for a specific function, you must tell Excel where the data is stored. Cell reference is the name given to this site. As a result, whenever a new value is entered into the cell, the cell calculates using the reference cell formula.
15. What do you mean by cells in an Excel sheet?
Ans: A cell is the area that lies at the intersection of a column and a row where the information is to be entered. In a single excel sheet, there are a total of 1,048,576 x 16,384 cells.
16. What are macros?
Ans: Considering excel interview questions macros are very important. By storing routine tasks into macros, Excel allows you to automate them. A macro is an action (or a series of actions) that can be repeated an unlimited number of times. For example, rather than manually calculating sales, profits, and losses for each item at the end of the day, you may construct a macro that will compute these automatically and use it in the future.
17. What do you understand about Excel functions?
Ans: In Excel, functions are utilized to accomplish certain tasks. Excel offers a number of built-in functions that can be used to calculate the results of various formulas, Hence saving time. Furthermore, these functions make it very simple to perform formulas that would have been difficult to write down manually.
18. Explain SUM and SUMIF functions.
Ans: Questions related to functions have their own place in excel interview questions. Lets discuss SUM and SUMIF function one by one.
SUM: The SUM function calculates the sum of all the values that are passed to it as parameters. Example:
SUM() function Example
SUMIF: This function is used to find the sum of values that met certain conditions.
Example:
Example of SUMIF()
19. How do you calculate the percentage in Excel?
Ans: As we all know, percentages are ratios that are calculated as a fraction of a hundred. Example:
Percentage
20. How can you resize the column?
Ans: A column can be resized in one of two ways:
To modify the width of a column, drag the right-hand column's boundary until you reach the desired width.
Pick Format from the main tab, and then select AUTOFIT COLUMN WIDTH from the cell section of Format. To modify the cell size, click this. Example
Excel Interview Questions for Experienced
21. How would you format a cell? What are the options?
Ans: The format cells options can be used to format a cell. There are six different ways to format cells:
Number
Alignment
Font
Border
Fill
Protection
22. What is the use of comment?
Ans: Comments are used for a variety of purposes, including:
The purpose of the cells is clarified via comments.
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. How would you add comments to a cell?
Ans: You may add a comment to a cell by right-clicking it and selecting new comment from the menu. Fill in the comment box with your thoughts. A red triangle in the top right corner of a cell shows that the cell has a comment associated with it. To erase a comment from a cell, right-click it and choose delete comment from the menu.
24. What are charts in MS Excel?
Ans: 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.
25. Explain what a Spreadsheet is.
Ans: Spreadsheets are cell compositions that help you manage your data. There could be multiple worksheets in a single workbook. At the bottom of the window, you can see all of the sheets as well as the names you gave them.
26. Explain the concept of data tables in Excel and their purpose.
Ans: Data tables are used to perform sensitivity analysis by calculating multiple results based on different input values. They enable users to explore various scenarios quickly. They help observe how changes in inputs affect calculated outputs, such as in financial modeling or what-if analysis.
27. How can you use the CONCATENATE or CONCAT functions to merge data in Excel?
Ans: CONCATENATE and CONCAT functions combine text or values from multiple cells into a single cell. CONCATENATE is an older function, while CONCAT is its newer version available in Excel 2016 and later. Both functions are used to create concatenated strings by specifying the cells or values to be combined.
28. How can you use the Data Analysis Toolpak in Excel?
Ans: The Data Analysis Toolpak is an add-in in Excel that provides various advanced data analysis tools. This include regression analysis, histogram generation, moving averages, and more. It enhances Excel's capabilities for statistical analysis and enables users to perform complex data analysis tasks easily.
29. Explain the concept of data tables in Excel and their purpose.
Ans: Data tables are used to perform sensitivity analysis by calculating multiple results based on different input values. They enable users to explore various scenarios quickly and observe how changes in inputs affect calculated outputs, such as in financial modeling or what-if analysis.
30. What is VBA (Visual Basic for Applications) editor in Excel?
Ans: The VBA editor allows customization and automation in Excel using Visual Basic programming. It enables you to create and modify macros, write custom functions, interact with other applications, and perform advanced data manipulation and analysis beyond Excel's built-in capabilities.
Frequently Asked Questions
How do I prepare for an Excel interview?
Review basic and intermediate functions, practice PivotTables and data analysis, learn keyboard shortcuts, and be prepared to use Excel to solve real-world problems to be asked by interviewer when preparing for an Excel interview.
What are the 3 functions of Excel?
The three primary functions of Excel are Data Entry and Storage, Data Analysis, and Reporting and Visualization. These primary functions are used for effective data communication, decision making, sharing information, and data presentation.
What are the basic Excel interview questions for Excel?
The basic interview questions for Excel are:
1. What is Excel used for? 2. How do you insert a new column in Excel? 3. Explain the difference between a relative reference and an absolute reference in Excel. 4. How can you apply conditional formatting in Excel? 5. What is a pivot table, and how is it used in Excel?
What are the 5 basic functions of Excel?
The five basic functions of Excel are:
1. SUM: Adds up a range of cells. 2. AVERAGE: Calculates the average value of a range of cells. 3. COUNT: Counts the number of cells in a range. 4. MAX: Returns the highest value in a range of cells. 5. MIN: Returns the lowest value in a range of cells.
How to explain Excel skills in an interview?
In an interview, highlight proficiency in Excel by discussing experience with data analysis, formula creation, pivot tables, VLOOKUP, data visualization, and familiarity with Excel functions for efficiency and accuracy.
What are the 7 basic Excel formulas?
The 7 basic Excel formulas include SUM, AVERAGE, MAX, MIN, COUNT, IF, and VLOOKUP. These formulas cover common tasks such as calculating totals, averages, finding the highest and lowest values, counting cells, performing logical tests, and searching for values.
How to lock cells in Excel?
To lock cells in Excel, select the cells to lock, right-click, choose "Format Cells," go to the "Protection" tab, uncheck "Locked," protect the worksheet, and enable cell protection. Locked cells cannot be edited without unprotecting the worksheet.
Conclusion
In this article, we have extensively discussed Excel Interview Questions. These excel interview questions are very important considering new jobs requirements. We hope that this blog has helped you enhance your knowledge about Excel and if you would like to learn more, check out our other articles on our website.
38+ registered 