- Home
- Blog
- Business Analyst
- MS Excel Interview Questions

Do you need excel interview questions and answers? Don’t worry! MindMajix content team has curated the top 60 excel interview questions and answers in a detailed way. This blog will help you know the basic operations and calculations you can perform with spreadsheets. You will also know how to create a pivot chart, columns in pivot tables, and much more. Undoubtedly, all these frequently asked excel interview questions and answers will help you get your job easily.

Rating: 4.6

1563

Excel is a tool that every employee needs to know to manage a large set of data and perform easy calculations. So, if you attend interviews with expertise in excel, that will boost your interview score, undoubtedly. MindMajix content team has framed the top 60 excel interview questions and answers intending to help aspirants to succeed in their interviews.

This blog packs the frequent-asked excel interview questions with answers and suitable images. They will support becoming familiar with excel operations, various advanced uses of spreadsheets, etc., in greater detail. By considering the different levels of learners, the MindMajix content team has included three sections in this blog as follows:

So, based on your expert level, you can directly jump into the section

**What is Microsoft Excel?****What do you mean by cell address?****What is the use of the VLOOKUP function?****What do you mean by pivot table in excel?****What do you mean by macros in excel?****What are the different VBA procedures used in excel?****How would you create a cell dropdown list in excel?****What is the use of freeze panes in excel?****What are basic excel interview questions?****What are the seven basic excel formulas?**

It is an application with which we can arrange and sort data in various ways. We can add, move, delete, name, rename, hide, and show the sheets in excel. It is also known as a spreadsheet.

If you want to enrich your career and become a professional Business Analyst, then enroll in "Business Analyst Training" - This course will help you to achieve excellence in this domain. |

It is the basic unit of an excel spreadsheet. They are rectangular-shaped and store values in numbers, dates, text, etc. In other words, cells are the intersection of columns and rows of an excel sheet. No wonder there are more than 15 billion cells in excel.

The ribbon is the topmost part of an excel sheet that consists of menu items and toolbars. We can show or hide a ribbon.

The cell address is also called a cell reference. The cell address is used to identify a cell in an excel sheet. It is the combination of the cell column alphabet and row number of the sheet. It means that every cell address has a letter followed by a number.

In the Relative reference type, if we copy a formula from one cell to another cell, the cells' position will change, but the formula remains the same. We can use relative referencing if we want to use a single formula for multiple rows.

We can understand relative referencing from the following example. The cell C2 in the sheet has the formula of adding A2 and B2. For this operation, it takes values from cells A2 and B2. While copying the formula is copied to other cells, such as C3 and E4, the formula doesn't change, but the values are taken from A3 and B3 for added results in C3. Similarly, the values are taken from A4 and B4 to get added results in E4.

It refers to a specific address where we cannot see the values of the cell while copying them. It means the references remain the same, which is necessary when working with excel formulas and functions.

Following is the execution order is followed to evaluate a formula.

- Parentheses or brackets
- Exponent
- Multiplication
- Division
- Addition
- Subtraction.

It searches values vertically from the columns of data. It also returns a value from a different column in the same row.

- Compact
- Report
- Tabular

- Number
- Currency
- Date
- Percentage
- Text formats

It is an easy-to-use tool for summarising, calculating, and analyzing data. With pivot tables, we can make comparisons and identify patterns in data effectively.

Related Article: Creating a PowerPivot Data Model in Excel 2013 |

- Grouping
- Value field settings
- Top and bottom filtering
- Slicers and timelines
- Power pivot

- Mathematical and financial functions such as SQRT, RAND ( ), DEGREE, etc.
- Logical functions such as AND, IF, FALSE and TRUE.
- Date and time functions such as DATEVALUE(),NOW(),WEEKDAY()
- Index match functions such as VLOOKUP and INDEX MATCH
- Pivot tables.

- IF function
- AND
- FALSE
- IFERROR
- IFNA
- NOT
- OR
- TRUE

A formula is an equation we can design to calculate the excel sheet. A function can be a part of a formula. At the same time, a function is the prebuilt code of excel that we can use to make calculations.

Related Article: Creating a Power View Report in Excel |

An excel array formula can process many values instead of a single value. The array formula evaluates all the values in an array and performs multiple operations based on the conditions expressed in the formula.

We can use the SUMIF function to sum values in a range. But the essential thing is that they must meet specific criteria.

XLM and VBA are the two languages used in excel. Here, VBA stands for Visual Basic Applications.

- Text displays a string or group of characters within a cell.
- value2 is used to represent the underlying value of the cell as an empty cell, error, string, or number.
- value is used to show formatted currency if the cell is formatted as currency.

Excel comes with five different count functions as follows:

- COUNT: by using this function, we can count the total number of cells that have numbers.
- COUNTBLANK: by using this function, we can count the blank cells
- COUNTIF: by using this function, we can count the cells that meet a specified criterion.
- COUNTIFS: by using this function, we can count cells that meet two or more criteria.

Charts give a graphical representation of the data. Excel offers different chart types such as Columns, lines, bars, scatter, pie, and many more.

- Asterisk, question mark, and tilde are the three wildcards used in excel.
- Asterisk represents 0 or more characters. For example, ex* represents Excel, expertise, and so on.
- The question mark represents any one character. For instance, R?ain means Rain or Ruin.
- Tilde is used to identify a wild card.

A macro is essentially an action or set of steps that we can use multiple times. We can create a macro, record it, save, name it, and execute it. Macros are mainly used for performing iterative operations. It means that we can use macros for executing repetitive functions and instructions. Note that we can edit macros as we wish to make minor changes.

We can use freeze zones to lock any row or column. Not only that, we can lock a group of rows and columns. Once it is locked, we can view the locked rows and columns even if we scroll the page down and horizontally.

We can use the IF function to make a logic test. Using the IF function, we can check whether a condition is true. When the state is true, there will be output. If not, there will be another output.

In nested IF statements, one IF function has another IF function inside of it. Nested IF statements are used to test multiple criteria.

- First, go to ‘PivotTable Analyse’ in the pivot table, then click on the ‘Fields, Items, and Sets’ tab.
- Select ‘Calculated Field’ from the dropdown list
- An ‘Insert Calculated Field’ window will open up. This is where we can enter the name of the column and formula.
- Finally, click OK and ADD. as a result, you will add a new column to the pivot table.

Slicers of software filters are used to filter a data from a large amount of data. This feature of Excel allows you to understand the details of the extracted information. Note that slicers are one-click software used to filter data very quickly.

Related Article: Import Excel into MySQL |

- First, choose a cell in your spreadsheet
- Choose PivotTable Tools>Analyse>PivotChart
- Then, select a chart and click OK.

- We can use the count formula
- We can perform a sum operation
- We can perform VLOOKUP operations
- we can create constants using the excel name feature

Yes, we can provide a dynamic range in the following way.

- First, we must create a named range using the offset function
- Then, base the pivot table using the named range.

- Click the ‘more sort options’
- Right-click on ‘Pivot Tables’
- Choose ‘sort menu’
- Choose ‘More Options’
- Now, deselect ‘sort automatically’

- We can use passwords to open or modify an excel file or workbook
- We can mark a file as final. After that, no one can change the file.
- We can use a digital signature to access a file.

We can use the WEEKDAY function to find the weekday for a given date. The function's syntax is as follows:

WEEKDAY (Serial_number,[return_type])

Essentially, it is a logical function with which we can test multiple conditions. The output of the AND function will be either true or false.

The formula for the AND function is given as follows;

“= AND (logical1,[logical2]….)

Here, logical 1 and 2 represent the various conditions.

If we use a formula in a cell, we can add cell references in the arguments of the formula. It allows getting data from other cells of the worksheet for performing calculations using the formula.

**For adding comments**

- Right-click the cell and insert the comment
- Or else, you can press shift +F2
- Now enter your comments
- Click outside the cell to close the comment box.

**For adding notes**

- Right-click the cell and insert new notes
- Enter your details in the notes
- Click outside of the cell.

- #DIV/0
- #NAME?
- #N/A
- #NUM!
- #NULL!
- #VALUE!
- #REF!
- ####
- Circular reference

This function is used for columns of data and not for horizontal data. It is essential to note that hiding a row while performing SUBTOTAL won't affect the process.

Sub procedures, as well as function procedures, are the two procedures used in excel.

These tools can apply several sets of values in more than one formula. As a result, we can explore different results in the end. We can perform experiments with data, complex mathematical calculations, etc.

Functions | Subroutine |

They return values after performing a task | They don’t usually return a value after performing a task |

They are used as formulas | They are not directly used as formulas |

They perform repetitive tasks | Every time we need to insert inputs in the cells |

This workbook denotes the name of the workbook in which the code is running. On the other hand, ActiveWorkbook indicates the workbook that is currently active.

- Choose the range that you want to name.
- Now click formulas, then go to create from the selection
- Choose the checkboxes in the ‘Create names from selection’ dialog box
- Choose the top row if the table has a header row at the top.
- f you have a top row and column header, choose the top row and left column options.
- Finally, click OK.

- First, choose the cells that need to contain lists
- Click DATA>Data Validation on the ribbon
- Then, set Allow to List in the dialog box
- Click source and then type the text or numbers you need to add to the dropdown list.
- Lastly, click OK.

- First, control+click the sheet tabs for selecting the sheets.
- The selected sheets will turn into white color.
- If you make changes in any of the selected sheets, it will be reflected in all other sheets.
- Again, double-click the selected sheets to unselect them once the format changes are over.

- First, click the cell for which we want to create a link
- Click ‘Link’ in the ‘Links’ group on the ‘insert’ tab.
- Then, click the existing file or web page under the ‘Link to’ option.
- Click the current folder and select the file we want to link
- If we want to link a web page, we need to click 'browsed pages' to choose a web page.

- Locate your cursor on the top of the column that you want to move
- Highlight the column
- Hold the shift button and move the column right or left
- Release the click in the mouse once you move to the location where you want to move the column.

It is shown to represent a comment associated with that cell. When we move the mouse over the symbol, it will show the comment.

INDEX MATCH is essentially a combination of the INDEX function and the MATCH function. When these two functions are combined, they return a value resulting from these two functions. Note that INDEX returns a value based on the column and row number, whereas MATCH returns a value based on the position of a cell.

- Choose the part of the excel sheet that you want to convert into excel
- Click the file ‘menu’ and go to the ‘Export’ option
- Then click create pdf and then click options to make settings of the pdf file
- Click ‘OK’ and name the file.

- Select the cells for which you apply the transpose function
- As transpose is an array formula, you need to select the exact number of cells
- Now, you can enter the formula “=TRANSPOSE(A1:F5)”
- You need to press Ctrl+Shift+Enter together
- Finally, the data will be transposed.

We can pass an argument to the VBA function as a value or reference.

Consider the following VBA Code.

If we pass arguments as a reference, it will display 30. The original value of x will be changed.

If we give arguments as values, it will show 10, and the original value is unchanged.

First, select the text in a cell for which we want to create a hyperlink. Then, press Ctrl+K. Choose the existing file from the folders or enter the web page link. Press OK to create the hyperlink.

- Select the data in the excel sheet
- Click the data tab and go to Sort & Filter
- Click the Advanced button
- A pop-up will open
- Choose either filter or copy to another location
- Click OK, finally.

- Choose the cell in the pivot table
- Go to PivotTable analyze and choose insert slicer
- Choose the fields and click OK
- Then, arrange the size and position of the slicers on the table
- Choose the items that you wish to show in the pivot table

We can double-click on the cell's bottom right corner containing the formula. Suppose the formula is in the first cell of the column, we can use the following method.

- Select the complete column by clicking the column header
- Press Ctrl+Space
- Use Ctrl+D to fill the consecutive cells.

We will use the advanced criteria filter to analyze the list. We can use this filter to test more than two conditions.

Cross tabulation or cross tab is the statistical method applied for quantitative analysis. We can use this method to summarise large datasets. This approach helps to identify the essential data from the large data. We can use cross-tabulation to analyze the relationship between different variables.

Basic excel interview questions cover cells, formulas, cell referencing, data formats, functions, and report formats. You must prepare questions and answers relevant to these topics so you can quickly attend basic excel interview questions.

- Aggregate
- Sum
- Count
- IF
- VLOOKUP

The main thing is that you must have hands-on experience while learning excel concepts. Therefore, you can quickly answer your excel interview questions with practical examples.

If you want to crack an excel interview easily, you must be familiar with the basic operations of excel. Moreover, you must be familiar with functions, filters, and report formats. It will benefit you if you are an expert in macros, pivot tables, and subroutines.

- SUM
- AVERAGE
- SUMIFS
- IF
- COUNT
- COUNTIFS
- VLOOKUP.

VLOOKUP stands for vertical lookup. It helps to find a specified value vertically in the table. Also, we can use VLOOKUP to find the exact match as well as the approximate match from a table in an excel sheet.

There are 10, 48,576 rows and 16,384 rows in an excel sheet.

Essentially, PivotTable is nothing but a statistical tool that summarises the data in an excel sheet. It manages a large set of data efficiently to generate desired reports. With PivotTables, we can perform sum, range, average, as well as outliers. For example, we can count sales by department, compare the sale of a product with another product, and so on.

**Be clear with basic excel operations:**You must be very clear with excel basic operations such as creating sheets, and tables, adding rows and columns, etc. And you must be thorough in using filters and formulas. This is because they will help save time enormously—no wonder every employer encourages saving time and enhancing productivity.**Show up your expertise:**When questions come relevant to the advanced use of excel, take the chance. It will impress interviewers. You show up your expertise in brief.**Quote real-time examples:**While answering questions, it is essential to add real-time examples of the excel application. You can quote how you have used excel for past projects and works – not everything in detail, but how you used excel and the outcomes.**Be Honest:**If you don’t know the answer to any complicated question, please openly say 'Don't know' instead of giving irrelevant answers, wasting interviewers' time. At the same time, note down the questions carefully to learn them later. It will show your commitment to learning new and unknown concepts.

Excel is one of the basic skills that every employee must have. Suppose you are an expert in excel, you can definitely contribute to the productivity of any organization. We hope this blog helped you by providing the top 60 excel interview questions and answers. If you go through online Bussiness Analyst training in addition to learning the excel interview questions, it will improve your hands-on experience and expertise to high levels. MindMajix offers training courses on excel concepts for aspirants who wish to become experts in excel application

Course Schedule

Name | Dates | |
---|---|---|

Business Analyst Training | Aug 10 to Aug 25 | View Details |

Business Analyst Training | Aug 13 to Aug 28 | View Details |

Business Analyst Training | Aug 17 to Sep 01 | View Details |

Business Analyst Training | Aug 20 to Sep 04 | View Details |

Last updated: 14 Apr 2023

About Author

Madhuri is a Senior Content Creator at MindMajix. She has written about a range of different topics on various technologies, which include, Splunk, Tensorflow, Selenium, and CEH. She spends most of her time researching on technology, and startups. Connect with her via LinkedIn and Twitter .

read less

Business Analyst Articles

- Business Analysis Framework
- Business Analyst Interview Questions
- Business Analyst Tutorial
- ELK Stack Tutorial
- Kibana Tutorial
- Top 10 Business Analytics Tools For Business
- What Is Business Analytics?
- Product Analyst Interview Questions
- Goldman Sachs Interview Questions
- Deloitte Interview Questions
- Uber Interview Questions
- Accenture Interview Questions
- EY Interview Questions
- Mphasis Interview Questions
- Business Analyst Projects and Use Cases

Table of Contents