Top MS Excel Interview Questions

Top MS Excel Interview Questions

10 mins read27.9K Views Comment
clickHere
Rashmi
Rashmi Karan
Manager - Content
Updated on Dec 19, 2023 14:40 IST

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. From basic functions to advanced data analysis, boost your Excel proficiency and shine in any data-driven role.

MS Excel Interview Questions & Answers

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.

What Are Excel Errors and How To Fix Them?
What Are Excel Errors and How To Fix Them?
Excel is a powerful tool for working with data, but it can also be frustrating when errors occur. While some errors are easy to fix, others can be more difficult...read more
150+ Keyboard Shortcuts In Excel
150+ Keyboard Shortcuts In Excel
Excel users who enter a lot of data with the keyboard can boost their productivity by learning Excel shortcut keys, which allow them to access the program’s commands much faster....read more

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

Ans. The cell address is a combination of a column letter and a row number on a worksheet. The cell address 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 return value of the formula.

First of all, Excel evaluates any expressions in parentheses. As we have seen in mathematical formulae, parentheses override the normal order of operations. It prioritizes 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
What is Filter in Excel?
What is Filter in Excel?
Filter in Excel is a valuable tool to analyze data quickly. In this article, you will learn how to use filters in Excel.
How to Use Excel Filter Shortcut?
How to Use Excel Filter Shortcut?
Excel Filter shortcuts are keyboard combinations that you can use to apply filters to your data quickly and easily. The article will explain different ways to use Excel Filter shortcuts,...read more

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, 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

Difference Between Formula and Function in Excel
Difference Between Formula and Function in Excel
Formula and Function in Excel sound similar and are often confusing. A formula is a user-created expression that combines operators, cell references, and constants to perform customized calculations or data...read more

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. VLOOKUP function searches vertically in columns of data. It 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. You can easily make comparisons and patterns in your data trends using Pivot tables. 

How To Create A Pivot Table In Excel?
How To Create A Pivot Table In Excel?
A pivot table is a powerful tool in Excel that allows you to summarize and analyze large amounts of data. It is essentially a way of reorganizing and manipulating data...read more

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 number of arguments and performs a sum on each. The SUM function 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.

Must Read – Sum Function in Excel

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 to use DataSets and DataTables. 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 has support for 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. This won’t work for .xlsx files since it is 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.
Relative Referencing in Excel – Shiksha Online
Relative Referencing in Excel – Shiksha Online
The article covers the Relative Referencing in Excel, how to create a relative reference, and examples of relative referencing.
How To Remove Duplicates In Excel
How To Remove Duplicates In Excel
While working on spreadsheets, you may come across instances where you will see duplicate values. This may arise for different reasons, like multiple people working on the same document, unification...read more

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. You can double-click on the bottom right corner of the cell to apply a formula to an entire column in Excel.

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

About the Author
author-image
Rashmi Karan
Manager - Content

Rashmi is a postgraduate in Biotechnology with a flair for research-oriented work and has an experience of over 13 years in content creation and social media handling. She has a diversified writing portfolio and aim... Read Full Bio

Comments