Top MS Excel Interview Questions

Microsoft Excel is an indispensable tool that empowers professionals to manage, analyze, and visualize data with unparalleled efficiency. As businesses seek professionals who can harness the full potential of MS Excel, job interviews often include rigorous assessments of one’s Excel skills. Master your Excel interview with confidence using our curated list of top questions and answers. From basic functions to advanced data analysis, boost your Excel proficiency and shine in any data-driven role.

Top Excel Interview Questions and Answers

We have listed some of the most popular Excel interview questions and answers to help you with your next job interview.

1. What are spreadsheets?

Ans. Spreadsheets are computer applications that help to arrange, calculate and sort data efficiently.

As shown below, there is one worksheet (‘Sheet1’), and we can also see a ‘+’ sign at the bottom. This denotes we can add a new sheet. We can add, delete, rename, hide, show, and perform other operations on sheets. By default, worksheets are added as Sheet1, Sheet2, etc. We can easily rename those sheets as required.

2. What do you understand by a cell address in Excel? 

Ans. A cell address is a combination of a column letter and a row number on a worksheet. It identifies a cell on the sheet. 

For example, in Image 1, C6 refers to the cell at the intersection of column C and row 6. Similarly, in Image 2, D3 refers to the intersection of column D and row 3, and so on. 

3. What is the order of operations used in MS Excel while evaluating formulas?

Ans. MS Excel follows a standard math protocol to evaluate a formula. This protocol is called “order of operations” – PEMDAS –

• Parentheses
• Exponents
• Multiplication
• Division
• Addition
• Subtraction

MS Excel also applies some customization to handle the formula syntax. The order in which MS Excel performs calculations can affect the formula's return value.

First, Excel evaluates expressions in parentheses. As we have seen in mathematical formulae, parentheses override the normal order of operations, prioritizing certain operations.

Next, Excel resolves cell references like A1 (cell address). It evaluates range references like A1:A10, making them arrays of values.  It also performs range operations like a union (comma) and an intersection (space).

Next, Excel performs –

• Exponentiation 
• Negation 
• % conversions
• Multiplication and division
• Addition and subtraction
• Concatenation 
• Logical operators

4. What do Relative cell referencing and Absolute cell referencing in MS Excel mean?

Ans. When creating a formula, we use cell references instead of values. This way, when we change cell values, the result of the formula also changes. There are two types of references –

Relative reference in Excel

Relative references refer to the location of the cell, that is, the row and column where the cell that contains a value or a formula is located. When we take that same cell to move it to another cell, it is worth the redundancy; this will reference new cells based on their location.

Therefore, we can say that Excel predetermines relative references.

Absolute reference in Excel

The absolute reference represents a specific address. It always refers to the same cell and does not allow them to be modified when copying.

Absolute references are important because they remain fixed, which is very good when working with Excel functions or formulas.

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

Ans. We can protect the cells of a worksheet from being copied by  –

Navigating to the ‘Review’ Menu Bar => Protect Sheet and then provide the password

You can also protect specific sections you don’t want to be disturbed.

If someone who tries to edit the protected cells and doesn’t enter the password will receive the following error.

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

Ans. 

A formula is any statement or equation used in the calculation. The user designs these formulae. These simple or complex formulas always start with an = (equal to) sign.

Examples – 

=C4+2

=2+3

=A1+C4

=B3+B8-(4*3)+5 [see screenshot]

A function is a pre-defined code that performs calculations and is a part of the formula.

Examples –

=SUM(B9+B10)

=AVERAGE(B2:B11

7. How do you create a hyperlink in Excel?

Ans. To create a Hyperlink in Excel, select the cell or text you want to hyperlink and use CTRL + K. Mention the address in the dialogue box and click OK.

The cell is now hyperlinked.

8. How do we apply advanced filters in Excel?

Ans. To apply the advanced filter, you need to 

• Select the data set 
• Go to Data –> Sort & Filter –> Advanced 
• Locate the Advanced Filter dialogue box, and select ‘Copy to another location.’ 
• Click OK.

9. Can you name the wildcards in Excel?

Ans. 3 wildcards in Excel can be used in formulas.

Asterisk (*) – 0 or more characters. For example, Ex* could mean Excel, Extra, Expertise, etc.

Question mark (?) – Represents any 1 character. For example, R?ain may mean Rain or Ruin.

Tilde (~) – Used to identify a wildcard character (~, *, ?). For example, If you need to find the exact phrase India* in a list. If you use India* as the search string, you may initially get any word with India, followed by different characters (such as Indian, Indiana). If you have to look for India” exclusively, use ~. 

Hence, the search string will be india~*. ~ ensures that the spreadsheet reads the following character as is and not as a wildcard.

10. What is the Vlookup function in Excel?

Ans. The VLOOKUP function searches vertically in columns of data and returns a value from a different column in the same row.

Formula –

=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

To understand VLOOKUP in Excel, read our blog.

11. What are Pivot Tables in Excel?

Ans. Pivot tables are handy for calculating, summarizing, and analyzing data. They allow you to easily make comparisons and patterns in your data trends. 

12. How does a Slicer work in Excel?

Ans. Slicers are used in Excel to filter Pivot Tables quickly. You can even connect multiple slicers to multiple pivot tables.

To insert a slicer in the Pivot Tables, do the following –

1. Click on any cell inside the pivot table

2. Go to Analyze ==> Filter ==> Insert Slicer

I chose the Number of Vehicles, for which Excel created a slicer for the selected field in the pivot chart.

13. Explain the SUM and SUMIF functions.

Ans. The SUM function takes n arguments and performs a sum on each. It adds up all the numbers in the defined cell range. For example, =SUM(A1:G1) will add the numbers in the range A1 to G1.

The SUMIF function only performs the sum if a certain condition is met. Therefore, the SUM and SUMIF functions are almost identical except for the presence of criteria in SUMIF. For example, =SUMIF(E1:G1, ‘<10), will sum the numbers in the range E1 to G1 that are less than 10.

14. What are the different types of COUNT functions in Excel?

Ans. There are 5 types of COUNT functions available in Excel.

COUNT: COUNT is used to count cells that contain numbers

COUNTA: COUNTA is used to count cells that have values

COUNTBLANK: COUNTBLANK is used to count blank cells.

COUNTIF: COUNTIF is used to count cells that meet specified criteria.

COUNTIFS: COUNTIF is used when we must enter more than one criterion.

15. How do I create an Excel (.XLS and .XLSX) file in C# without installing Microsoft Office?

Ans. If you want to create an Excel file in C# without installing Microsoft Office, use ExcelLibrary. It’s a free, simple, and open-source library on Google Code. It is a port of the PHP ExcelWriter. It has a DataSetHelper that helps you use Data Sets and Data Tables. ExcelLibrary works for the older Excel format (.xls files), and they plan to add support for newer 2007/2010 formats.

Another option is EPPlus. It is compatible with Excel 2007/2010 format files (.xlsx files) and has NPOI. EPPlus also supports Pivot Tables.

16. How will you stop Excel from automatically converting certain text values to dates?

Ans. It is a widespread issue faced by Excel users. However, you can put an ‘=’ before the double quotes to stop Excel from automatically converting certain text values to dates. This step forces the data to be text.

eg. =”2022-10-06″,=”more text”

We hope these Excel interview questions and answers were helpful. We will keep on adding new questions to this list.

Until then, keep learning!

17. Can we crack the password on an Excel VBA Project?

Ans. Yes, we can crack the password on an Excel VBA Project using the .xls format spreadsheet. However, this won’t work for .xlsx files since they are a secure format.

Here is how we can do it-

Swap the password entry in the file using a hex editor. Below is the step-by-step process –

• Create a new .xls file.
• In the VBA part, set a simple password such as abcd
• Save the file and exit
• Check the file size
• Open the file with a hex editor
• Copy the lines starting with the following keys:

CMG=….

DPB=…

GC=…

• Now we need to back up the Excel file for which we don’t know the VBA password for
• Open the file with the hex editor
• Paste the above-copied lines from the dummy file
• Save the Excel file and exit
• Open the Excel file in which we need to check the VBA code. The password will be abcd.

18. How can a string or formula in Excel containing double quotes be created?

How can I construct the following string in an Excel formula:
Maurine “The Slayer” Rickard

Ans. An easy way to do it is by escaping with an additional double quote. When you escape a character, you tell Excel to treat the “character as text.

=”Maurine “”The Slayer”” Rickard”

You can also use the CHAR function

= “Maurine ” & CHAR(34) & “Slayer” & CHAR(34) & ” Rickard”

19. What is the shortcut to applying a formula to an entire column in Excel?

Ans. In Excel, you can double-click on the bottom right corner of a cell to apply a formula to an entire column.

OR

If the cell already has the formula, you can apply it as follows:

• Select the cell containing the formula and press CTRL+SHIFT+DOWN to select the rest of the column
• Press CTRL+D
• Use CTRL+UP to return up

OR

If the formula is in the first cell of a column –

• Select the entire column by clicking the column header or any cell in the column
• Press CTRL+SPACE
• Fill the consecutive cells using CTRL+D

20. What is the difference between .text, .value, and .value2?

Ans –

• .Text represents the string displayed within a cell. Using .Text can give you error like ####
• .Value2 presents the underlying value of the cell in the form of an empty cell, string, error, number (double) or boolean)
• .Value is similar to .Value2, only difference is that if the cell was formatted as currency or date it gives a VBA currency or VBA date.

It is better to use .Value2 than .Value or .Text because the latter two may not give you the real value from the cell and are slower.

I hope this article on MS Excel interview questions was helpful.

Top Trending Articles in MS Excel:

Most Useful Excel Formulas | Min Max Functions in Excel | Average Functions in Excel | Introduction to MS Excel | Financial Modelling in Excel | MS Excel interview questions | Sum Function in Excel | Trim Function in Excel | Pivot Table in Excel | Percentage in Excel | Vlookup in Excel | Median Function in Excel | Types of Charts in Excel | Count Function in Excel | MS Excel Vs. Google Sheet | Remove Duplicates in Excel | Create Graph in Excel