Frequently Asked Excel Interview Questions With Answers For 2023
Table of content:
- Top Excel Interview Questions
- Importance of MS Excel in various professions
- Tips for handling basic Excel interview questions well
Microsoft Excel is Microsoft's software program that utilizes spreadsheets to organize data and numbers. It is one software that is widely used around the world with applications ranging from simple college projects to complicated financial reports. From simple calculations to handling complex data, Excel provides all-around support. Along with this, it saves one from the manual calculation and helps users to collect, collate and analyze data.
The USP of this software is that it is easy to use once the user has understood its various applications. It is available for most operating systems. Since the use of Excel has become so important in today's scenario, many hiring managers expect the applicants to know this software well. The knowledge of Excel would allow you to get several job opportunities in the competitive market.
To assist you in ace any job hiring process, we have brought you the best MS Excel interview questions and answers.
MS Excel interview questions and answers would help you to know your aptitude to use the features of the software. As discussed below in the article, based on their analytical role, many professions require basic or advanced skills in Excel. Some professionals even go for advanced Excel courses to upskill themselves. Since manual calculation is a thing of the past, being proficient in Excel will help you get the job you desire. Following is a list of commonly asked Excel interview questions. These will provide you with an idea of what you must know about MS Excel interview questions and answers. Starting with the most basic one.
Top Excel Interview Questions
Q1. What is a spreadsheet?
This is a starter question to know if you are aware of the basics of Excel or not. In MS Excel, a spreadsheet is a digital ledger sheet for the storage of data. It comprises tables of intersecting rows and columns in Excel. The rectangular boxes that occur at the intersection are called cells. So spreadsheets contain a range of cells and thousands of rows and columns. They provide a base to enter a large range of data both qualitative and quantitative. The "Row Labels" and "Column Labels" are used to drag and drop a field.
A spreadsheet, also called a worksheet and is utilized to arrange formulas, functions and arrange numbers, and data into rows and columns. The number of worksheets in a workbook can be more than one. The data entered in the spreadsheet can be operated mathematically.
Q2. What are the different data formats in Excel is another basic Excel interview questions?
To answer these MS Excel interview questions, you should know about a few things.
MS Excel supports different types of data formats on the Excel sheet. The following are the types of data formats:
- Text Format: This type of format includes text and alphanumeric strings. For example such as DEF678. A text string can also consist of punctuation marks and symbols.
- Number Format: There are different formats available that work with numbers such as fractions, fractions, etc.
- Date Format: Dates are stored as numbers in Excel. For example, 01-02-2022 would be saved as a specific numerical value in Excel.
- Accounting / Currency Format: This format adds currency signs and decimal places to the number entered. This is majorly applied in accounting or finance.
- Percentage Format: Excel allows numbers to be formatted and shown as percentages. So 0.2 can be shown as 20% if the percentage format is applied to it.
Q3. Another important MS Office interview questions is: What Excel data formats have you used before?
In these Excel questions and answers, you can use the knowledge of the above question.
As discussed in the above question, there are five data formats used in Excel. Hence, through this question, the interviewer wants to know if you have first-hand knowledge of data formats. Based on your role in the previous work, you can answer this basic Excel question precisely and also explain the data formats to the panel.
Q4. You can also be asked this advanced Excel interview questions, What do you mean by a ribbon in Excel?
This is a basic Excel interview questions that you are expected to be aware of. Ribbon is a row of icons and tabs present at the top of the Excel window. It allows you to quickly search and use commands for the completion of a specific task. It is a complex toolbar that first appeared in Excel 2007. Later Microsoft added a ribbon that can be personalized in Excel 2010. Ribbon is made up of some basic components which are mentioned below:
- Ribbon tab: It contains multiple commands which are subdivided into groups.
- Ribbon group: It is a set of similar commands performed during a large task.
- Dialog launcher: It is a small arrow present in the lower-right corner of a group that brings up more related commands.
- Command button: It is the button that you click to perform a specific action.
Q5. One of the interesting MS Excel interview questions is: Explain how you wrap text in Excel.
You can use this for this Excel interview questions and answers.
This feature in Excel prevents text from overflowing out of the cell into the next blank cell. On applying the wrap text feature, one can make all the text snuggled up in one single cell even though the cell size might change a bit. So, all the cell values are neatly arranged in the table. In order to wrap text in Excel, you have to follow the steps below:
- Select the cell whose data you want to wrap,
- Go to the Home Tab and press the Wrap text option in the alignment group.
- The text will get wrapped into one cell without spilling into the next blank cell. It is important to note that since the wrap button is a toggle button, clicking it again will unwrap the text.
Q6. How do you merge cells in Excel is another MS Office interview questions?
This MS Office interview questions has two steps.
You may come across many situations while working on Excel where you would need to merge the cell. In order to do so, you will have to follow the steps below.
- First, select the cells that need to be merged
- Second, go to the Home tab and press the ‘Merge and Center’ option in the alignment group.
Q7. Tell us about macro in Microsoft Excel is one of the basic Excel questions you should prepare yourself for.
You can use this to answer this basic Excel interview questions.
Repetitive tasks often require a macro. Hence, if you are applying for a job that needs you to do a repetitive task, you should be expecting this question from the interviewer. For your information, the macro is used for automating tasks in Excel. The advantage of using this function is that it helps users to automate repetitive instructions which makes work easier and faster. So basically macro is a set of actions that one can record, name, save, and run several times as per ones need.
Q8. The most basic Excel interview questions is: Name some Microsoft Excel functions.
Excel contains many categories of functions. It has more than 100 built-in functions. Excel also offers custom functions that enable the user to add new functions. Some important ones are logical functions, average functions, date functions, count functions, statistics functions, look-up and search functions, mathematical functions, etc.
Since these are too many in number, it is least likely that the interviewer will expect you to know all of the functions. However, you must be prepared to name and explain a few.
For example Excel Rank function- can be used to rank a number against a list of numbers and returns a relative rank of the number against the list of numbers; the Excel Row function can be used to get the row number of a cell reference; Excel And function can be used to check multiple conditions; Excel Date Value function- it converts the date from text format to a serial number that is recognized by Excel.
Q9. What are the uses of a pivot table is another MS Office interview questions you could be asked.
The basic questions on the pivot table can be answered like this. A pivot table is used to summarize, group, sort, count or reorganize the data stored in a table. It helps to transform columns into rows in Excel and vice versa. Pivot tables are specifically useful when working with large amounts of data that are tedious to calculate manually. Hence, the pivot table helps to perform sums, ranges, averages, etc. It also arranges the information in a simple and meaningful layout thus highlighting the key values. You can also link numerous slicers to numerous pivot tables. A pivot chart is different from a standard chart as the latter is directly tied to worksheet cells.
An application of the pivot table could be in a departmental store. The owner makes a list of products sold in an Excel spreadsheet. If he/she wants to know which of the items got sold in good quantity in a specific financial quarter, a pivot table could be used. One can even create pivot charts using a pivot table. The following are some advantages of pivot tables:
- They help to display the exact data that you want to analyze or work with.
- The Pivot table helps in focusing on important details.
- Sorting in pivot tables is a convenient task.
- They can detect different patterns and trends.
- The five various perspectives to view the data.
- They make the comparison of data easy.
- The Pivot table helps in creating instant data and precise reports.
Q10. In Excel how can you highlight cells with negative values?
This function becomes very important when dealing with data related to growth, finances, profit-loss, etc. The following are the steps involved:
- Select the cells where you want to highlight the cells with a negative value.
- Go to the Home tab on the menu bar and click on the Conditional Formatting option.
- Select Highlight Cell Rules and press on the ‘Less Than’ option.
- In the ‘Less Than’ special dialogue box, specify the value below which the formatting must be applied.
- Click on OK in the dialogue box.
Q11. Mention the charts available in Microsoft Excel can be asked as the advanced Excel interview questions.
Excel charts are used to create a graphical representation of data showing trends and relationships. This is done to make the information easy, accessible, and understandable. The different chart types include a bar, line, pie charts, scatter, and column charts. Each chart can be used for representing different types of data as per the needs of the person. The design of the charts can also be customized as per the choice of the user.
Q12. What are the different types of errors that one can encounter in Excel?
There are six types of errors in Excel:
- #VALUE! Error: It occurs when an incorrect data type is there in the formula.
- #REF! Error: This is called the reference error and it comes up when the reference in the formula is no longer valid.
- #DIV/0! Error: This error comes up when a number is divided by 0. This is also called a division error.
- #NAME Error: A misspelled function leads to this type of error.
- #N/A Error: It is known as the ‘Value Not Available’ error. This type of error will come up when you use a lookup formula and can’t find the value. It is thus called Value Not Available error.
- #NUM Error: It comes up when a user tries to calculate a very large value in Excel. For example, =123 raised to the power 456 will return a number error.
Q13. How can you highlight cells with errors in them?
While dealing with a large amount of data, it is quite common that an error can creep in. In this case, a cell or a range of cells with errors can be highlighted in Excel. It can be done using conditional formatting. Following are the steps to highlight the cells with errors:
- Select the data where you want to highlight the cells containing errors.
- Go to the Home tab and click on the Conditional Formatting option.
- After you finish the conditional formatting process, press New Rule.
- In the New Formatting Rule dialog box, go and select the ‘Use a formula to determine which cells to format’ option.
- In the formula field, enter =ISERROR(A10), where A10 is the active cell in the selection.
- Click on the Format button and select the color to be used to highlight the cells.
- Click on OK.
Using this feature, the cells containing errors will be highlighted in the color of your choice.
Q14. Is there a way to protect workbooks in Excel as among the most important advanced interview questions and answers?
The interviewer may ask this question to know if you are aware of the basic features of Excel. Many times, data needs to be kept secure. The good thing is that Excel provides ways to secure your workbooks. Workbooks can be secured in the following ways:
- The most common way is to keep passwords to open workbooks
- There is also an option to protect sheets from being deleted, accessed, or hidden.
- One can also protect position or window sizes from being changed or modified.
Q15. Can you apply a single format to all the sheets present in a workbook?
Applying a single format to all sheets present in a workbook is possible in Excel. The SHEET function can be used for this.
Following are the steps required for the same:
- First Right-click on any sheet that is there in the workbook
- After that click on the Select All Sheets option
- Format any one of the sheets
- The format applied to one sheet that you formatted will be all the other sheets
Q16. The candidates must prepare for this MS Office interview questions: Define Relative Cell Address.
The basic Excel questions about relative cell addresses are quite common in interviews.
Before we learn what is a relative cell address, let's look into cell addresses. A cell address is a combination of a row number and a column letter on a worksheet.
As per the definition, relative cell references are the basic cell referencing functions that change or adjust when copied. Thus, whenever you copy a formula in Excel, the addresses of the reference cells get changed accordingly automatically to become the best match of the formula that was copied. This is performed by a system called Relative Cell Address. Absolute referencing is different from relative referencing. You must not confuse it with absolute cell references which means the cell referencing in which the row and column are made constant by the addition of the dollar symbol before the name of the column or row number.
Q17. What does the VLOOKUP function do?
In Excel, the VLOOKUP stands for Vertical lookup. It is a function that makes Excel search for a specific value in a column to return a value from a different column into the same row. However, in the absence of exact matches in the lookup_value, the VLOOKUP function fetches an approximate value from the given cell range. It is a little like a database function. The interesting part is that the VLOOKUP function of Excel can be used for more than one table as well.
Q 18. Can we rearrange cells in Excel?
Excel is flexible, convenient, and easy-to-use software. It does provide the option to rearrange cells by insertion and deletion. This can be done in the following ways:
- Shifting cells to the right
- Shifting cells down
- Shifting cells to the left
- Shifting cells up
- Inserting/Deleting an entire row
- Inserting/Deleting an entire column
Hence, using any of the above ways, the cells of a spreadsheet can be rearranged.
Q 19. Explain freezing panes in Microsoft Excel.
This question would be easy for someone who has worked extensively on MS Excel. While working with MS Excel, many times there are situations where there is a need to see the column header or rows. Hence, to avoid a large extent of scrolling, MS Excel provides a feature where we can freeze the panes. This means it freezes the row or column headings so that it is always displayed while you navigate around the spreadsheet. This can be done by selecting the View Tab and then clicking on the Freeze Panes option. From the drop-down menu select the Freeze Panes/Freeze Top Row/Freeze First Column option as per the need. You can also 'unfreeze' the panes by following the same steps and selecting the Unfreeze Panes/Top Row/First Column option.
Q20. How can you create dropdown lists in Excel?
This is another basic yet commonly asked Excel interview question. Dropdown lists can be created by following the steps given below:
- Click on the Data tab located in the ribbon
- From Data Tools, select Data Validation
- In the dialog box select Allow to List
- Finally, Click on Source, type the text that is wanted in the drop-down list, and click OK.
- A dropdown list will be created.
Q21. Can you combine the text from multiple cells using a formula?
Texts from different cells can be combined in Excel using the following three methods:
- TEXTJOIN function: However this may not be available in all editions of MS Excel
- CONCATENATE function: To combine values in B1 and B2, one can use the formula =CONCATENATE(A1, A2), and the data from the two cells will combine.
- Ampersand (&) operator: This function is similar to concatenate. To use this function, you can use the formula=B1&B2 to combine the text in B1 and B2 cells.
Q22. Differentiate between absolute and relative cell references?
This is an important question that you must be prepared with a correct answer to. The difference between the two is that relative references are cell references that change when you copy and paste the formula that has the references. On the other hand, absolute references are those that remain the same and don’t change when a formula that has references is copied and pasted the formula that has the references.
Q23. State the difference between COUNT and COUNTIF in Excel
COUNT Function is used to count cells containing numbers, dates, etc. any value stored as a number excluding blanks.
COUNTIF and COUNTIFS count cells matching certain criteria.
Q24. What is the process for adding a hyperlink?
Adding a hyperlink is a part of important Excel questions and answers.
Hyperlink refers to a link to another part of the document or another document altogether.
It is extremely easy to add a hyperlink in an Excel sheet. The steps are as follows-
- Select the cell, press Control + K (keyboard shortcut) or right-click on the cell and choose the hyperlink drop-down
- A dialog box opens up where you can mention the URL required.
Q25. How match function and index work together?
The match function returns a relative position of. value in a range.
Q26 Name the two macro languages in Microsoft Excel.
The two macro languages in Microsoft Excel are VBA (Visual Basic for Applications) and XLM.
Q27 What are the file formats which are used to save an MS Excel file?
Some of the formats used for saving an MS Excel file include .csv, .xls, .xlsm, .xla, and .xlb.
Q28 What is the use of COUNTA or Count All?
It is used to count any cell value containing numbers, logical values, text, etc.
Q29 Describe the SHEET formula.
The SHEET formula is used to return the number of the sheet. It is done in such a way that the second sheet tab would return a value to 2.
Importance of MS Excel in various professions
- Financial Analysts: They are expected to understand MS Excel in detail as they their work involves researching, mathematical thinking, making statistical tables, and analyzing and making recommendations on financial data. Hence, MS Excel is a mandatory skill for financial analysts.
- Administrative Assistants: They perform tasks like minor generating reports, creating client databases, minor bookkeeping, organizing document systems, finding duplicate values in data, and maintaining computer records–all of which involve Excel to a certain extent. Apart from finding duplicate values, they also use Excel for automatic sorting.
- Retail Store Managers: Excel is an important software for them as they have to analyze sales trends, make annual budgets and manage expenditures.
- Business Analysts: Excel is one of the best tools for business analysts as they have to deal with a lot of data, find an approximate match between various values, and keep track of the latest business trends for analysis. Excel is taught at an advanced level in programs such as the post-graduate programs in Business Analysis.
- Accountants: Accountants deal with complex calculations, formatting, auditing, formatting, formulas, and analysis, and Excel helps them carry out all these activities. Hence, your MS Excel skills must be fine in case you are opting for a career in accounting.
Apart from these professions, MS Excel is an excellent tool for people involved with different jobs such as a doctor (to manage patients' histories), teachers (to sort student's marks and keep a tab on their performance), lawyers( to keep the case details), writers (to make a table of contents, etc.) and anyone who deals with any sort of data.
Tips for handling basic Excel interview questions well
Excel might be something that we all have used at some point of time in our lives. However, using it for professional purposes requires a different acumen. Hence, it is recommended that you upgrade your skills before attending your job interview. You may go in for a course in Excel to make your level advanced.
Some online courses give you a good hang of the basic features of the software. Terms such as one variable data table and two variable data table, country columns, and string values must be brushed up well. Practicing Excel before the interview will certainly help you to brush up on your skills. During the interview, if you are asked general questions about Excel, don't forget to mention its popular features like lookup functions, pivot tables, macros, and other such functions.
If you already have professional experience with Excel, you must go ahead and share the same during the interview. In fact, you can cite examples from your previous work where Excel helped you to solve some problems, make a report, analyze some trends, etc. Sometimes there can be some advanced Excel interview questions that may confuse you. You may be asked something that you are not good at or may not be aware of, let's say what is an XML file (fresh .xls file) or volatile functions of Excel. In this case, maintain your calm and politely tell the panel that since you have never used the feature you are not aware of it. However, you would love to learn about it if given a chance.
You may also like to read:
Login to continue reading
And access exclusive content, personalized recommendations, and career-boosting opportunities.
Comments
Add comment