• 18 general advanced Excel interview questions

1. What does a nested “If” statement do?

2. How is “VLOOKUP” different from “LOOKUP”?
3. How do you disable the automatic sorting function in pivot tables?
4. How do you view specific results in a pivot table?
5. What is meant by “data validation” in Excel?
6. Describe what conditional formatting is in Excel.
7. Describe how to use conditional formatting in Excel.
8. How do you preserve the formatting of pivot tables when refreshing them?
9. Which approach would you use to measure percentages in Excel?
10. How do you create a feature shortcut in Excel?
11. How do you apply an identical format to all workbook sheets in Excel?
12. How do you return to a certain cell in a worksheet?
13. Explain what a “relative cell address” is.
14. Explain the order of operations Excel follows when evaluating a formula.
15. Describe an advantage of using sheet formulas in Excel.
16. How do you build named ranges?
17. Describe how you would protect workbooks, sheets, and cells in Excel.
18. Explain what a variant data type is.

• 5 critical general advanced Excel interview questions and some potential

answers
1. Describe how you would protect workbooks, sheets, and cells in Excel.
Candidates may mention that it’s possible to protect a workbook by setting a password, which the user must
enter to open the workbook.

They may say that it’s possible to protect sheets by navigating to the “Review” tab and selecting “Protect Sheet”.
This prevents users from adding or removing sheets. Candidates might also explain that you must set a password
for the worksheet and choose the permissions that you wish to give to users.

Candidates may also tell you that since cells are locked by default in Excel, they are all protected when you
protect the worksheet. However, it’s possible to choose cells that you don’t want to protect by right-clicking them
and selecting “Format Cells”. From there, navigate to the “Protection” tab and deselect the “Locked” option.
Finally, click “OK”.

2. How do you disable the automatic sorting function in pivot tables?

Do your candidates know how to disable the automatic sorting function? This is done by right-clicking on the pivot
table, selecting “More Sort Options” to open the “Sort” dialog box, clicking “More Options”, and deselecting the
“Sort automatically” option.

3. What is conditional formatting in Excel?

Candidates should know that the conditional formatting feature enables users to format cells that meet certain
criteria. For example, they may note that it’s possible to format a cell to have a bold font if the value the cell
contains is greater than 1,000.

4. Describe an advantage of using sheet formulas in Excel.

Candidates may point out that sheet formulas can not only make calculations but also update the result of a
calculation if you replace a number in a cell that is part of the formula.
5. Explain the order of operations Excel follows when evaluating a formula.
Excel follows the PEMDAS acronym when it calculates a formula. Promising candidates should know that PEMDAS
refers to the following order of operations:

Parentheses

Exponentiation

Multiplication or Division (left to right)

Addition or Subtraction (left to right)

• 23 advanced Excel interview questions related to Visual Basic for

Applications
1. What does “Option Explicit” do in VBA?
2. Name three core modules in VBA.
3. How do you pass arguments in VBA?
4. How is “ThisWorkbook” different from “ActiveWorkbook” in VBA?
5. How are subroutines different from functions in VBA?
6. Explain what user forms are in VBA.
7. Explain what ADO is.
8. Describe some of the ADO objects.
9. Which functions are commonly used in VBA?
10. What does “ByVal” do in VBA?
11. What does “ByRef” do in VBA?
12. How are “ByVal” and “ByRef” different in VBA?
13. Explain how to assign a value to an array.
14. What does it mean to format expressions in VBA?
15. What is the purpose of comments in VBA, and how do you write a comment?
16. Name three ways to correct code in VBA.
17. Explain what type of language VBA is.
18. What is the code window in VBA, and where is it located?
19. Explain the different types of VBA procedures.
20. Explain how variables are declared in VBA for Excel.
21. Explain what loops are in VBA.
22. Explain how to test conditions in VBA.
23. Which shortcut will take you to the VBA editor?

• 5 critical advanced Excel interview questions related to Visual Basic for

Applications and some potential answers
1. What is the code window in VBA, and where is it located?
The VBA code window is a space in which users write code for VBA projects. Do your candidates know that the
VBA code window is located on the right-hand side of the VBA pane? Candidates may mention that the shortcut
key used to view the code window is F7.

2. Explain what loops are in VBA.

Candidates should know that loops can be used in VBA to repeat code blocks until certain conditions are met.
They can also be used to highlight empty dataset rows or strings of text. There are different types of loops in VBA,
including the following:
Do Until

Do While

For Each

For Next

3. Name three core modules in VBA.

Three of the main core modules in VBA are:

Class modules. Users can make new objects and enhance existing objects using class modules.

User forms. User forms are a type of module used for building a graphical user interface (GUI) for an application.

Code modules. Code modules are usually the default modules in which functions and procedures are written.

4. Explain what ADO is.

ADO stands for ActiveX Data Objects. Candidates should know that ADO can help you access data from several
sources. You can retrieve and gather data from all data sources so that it’s located on one platform. Without ADO,
you would have to write the code for every required data source.

5. What is the purpose of comments in VGA, and how do you write a comment?
Do your candidates know that they can write comments in VBA code that describe the code’s logic? Can they
explain that these comments help other users work with the code? Are they aware that VBA comments are
created by placing an apostrophe at the beginning of a line of code?

• 9 advanced Excel interview questions related to macros

1. What are the advantages of macros?
2. How do you delete a macro from a workbook?
3. How do you run a macro automatically when you open a workbook in VBA?
4. How do you save macros?
5. How do you hide macros?
6. Describe four ways to run macros.
7. Is it possible to pull data from other workbooks with macros? How do you do this?
8. How do you assign a macro to a particular button?
9. How do you stop recording a macro?

• 5 critical advanced Excel interview questions related to macros and some

potential answers
1. What are the advantages of macros?
Your candidates may say that macros offer two main advantages:

They save time by automating repetitive tasks

They enhance productivity and accuracy by reducing the need to input commands manually

2. How do you run a macro automatically when you open a workbook in VBA?
In response to this question, candidates may state that you should start by opening the VBA editor with the
shortcut Alt+F11. Then, on the left side of the project explorer, double-click “ThisWorkbook”. Input the code
“Private Sub Workbook_Open()” and hit the enter key. Next, write the recorded code between “Private Sub
Workbook_Open()” and “End Sub”. After you close the VBA editor, save your workbook in Excel Macro-Enabled
Workbook (XLSM) format.
3. How do you save macros?
Candidates may describe two ways to save macros. You may only need your macro for the particular workbook in
which you made it. In this case, just select “File” and then “Save” or “Save As”.

Alternatively, you may need to save a workbook that contains macros and preserve them after you save the
workbook. This requires you to select “No” when asked if you want to continue saving as a macro-free workbook.
Then, save the workbook in XLSM format.

4. How do you hide macros?

Candidates may mention that users can hide macros by first opening the macro that they want to hide. They must
then go to the first line of code, which contains the “Sub” command. At the start of the line, they need to write
the word “Private”, leaving a space between it and the macro’s name. Once the macro is saved, it will be hidden.

5. How do you stop recording a macro?

Are your candidates aware that to stop recording a macro, users must navigate to the “View” tab and select
“Macros”? They must then click on the “Stop Recording” option.
 50 questions to Select the right advanced Excel interview
questions to evaluate your candidates
Since it can be a challenge to hire talent that fits your organization and has the right advanced Excel skills for your
team, remember that skills testing can make the process easier.

Although you may have hundreds of applicants, you can narrow them down with our Advanced Excel skills test.
You should make skills testing your priority and complete it before reviewing resumes.

When you have decided on a shortlist, select the advanced Excel interview questions that suit your interview
process and evaluate your candidates with ease.

1. What is a cell address in Excel?

A cell address is used to identify a particular cell on a worksheet. It is denoted by a combination of the respective
column letter and a row number.

As shown above, the highlighted cell belongs to the column ‘D’ and row 5, so the cell address is read as D5.

2. What do you mean by Relative cell referencing and Absolute cell referencing in MS Excel?

Relative cell referencing Absolute cell referencing

In Relative referencing, there is a change when copying a formula Meanwhile, there is no change in Absolute
from one cell to another cell with respect to the destination. cells’ cell referencing when a formula is copied,
address irrespective of the cell’s destination.

This type of referencing is there by default. Relative cell referencing

doesn’t require a dollar sign in the formula. If you don’t want a change in the formula
when it’s copied across cells, then absolute
referencing requires you to add a dollar
 sign before and after the column and row
address.

3. How do you freeze panes in Excel?

Freeze panes keep the rows and columns visible while scrolling through a worksheet. To freeze panes, select the
View tab and go to Freeze Panes.

If you are looking to freeze the first two columns of a dataset, select the 3rd column, and click ‘Freeze Panes’. A
thick grey border indicates this.

4. How can you restrict someone from copying a cell from your worksheet?
1. First, choose the data you want to protect.
2. Hit Ctrl + Shift + F. The Format Cells tab appears. Go to the Protection tab. Check Locked and click OK.

3. Next, go to the Review tab and select Protect Sheet. Enter the password to protect the sheet.

Let’s now move onto our next question on our list of Excel interview questions.

Ge

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

Formula Function

Whereas, a function in Excel is a

predefined calculation which is in-built
The formula is like an equation in Excel, the user types in that. It can be in Excel.
any type of calculation depending on the user’s choice.

Manually typing out a formula every time you need to perform a However, performing calculations
calculation, consumes more time. becomes more comfortable and faster
Ex: = A1+A2+A3 while working with functions.

Ex: = SUM(A1:A3)

Now, let’s head to our next question in our list of Excel interview questions.
6. Mention the order of operations used in Excel while evaluating formulas.
The order of operations in Excel is referred to as PEDMAS. Shown below is the order of precedence while
performing an Excel operation.

• Parentheses
• Exponentiation
• Division/Multiplication
• Addition
• Subtraction

As seen above, first, the data in the parentheses is operated, followed by the exponentiation operation. After
that, it can be either the division or multiplication operations. The result is then added and finally subtracted to
give the final result.

Let’s look at an example of the PEMDAS precedence in the next question on our Excel interview questions list.

7. How will you write the formula for the following? - Multiply the value in cell A1 by 10, add the result by 5, and
divide it by 2.
To write a formula for the above-stated question, we have to follow the PEDMAS Precedence. The correct answer
is ((A1*10)+5)/2.

Answers such as =A1*10+5/2 and =(A1*10)+5/2 are not correct. We must put parentheses brackets after a
particular operation.

The output will look like this:

8. What is the difference between count, counta, and countblank?

The count function is very often used in Excel. Here, let’s look at the difference between count, and it’s variants -
counta and countblank.

1. COUNT
It counts the number of cells that contain numeric values only. Cells that have string values, special characters,
and blank cells will not be counted. Shown below is an example of the count function.
2. COUNTA
It counts the number of cells that contain any form of content. Cells that have string values, special characters,
and numeric values will be counted. However, a blank cell will not be counted. Shown below is an example of the
counta function.

3. COUNTBLANK
As the name suggests, it counts the number of blank cells only. Cells that have content will not be taken into
consideration. Shown below is an example of the countblank function.

Experts!

9. What is the shortcut to add a filter to a table?

The filter mechanism is used when you want to display only specific data from the entire dataset. By doing so,
there is no change being made to the data. The shortcut to add a filter to a table is Ctrl+Shift+L.
10. How do you create a hyperlink in Excel?
Hyperlinks are used to navigate between worksheets and files/websites. To create a hyperlink, the shortcut used
is Ctrl+K.

The ‘Insert Hyperlink’ box appears. Enter the address and the text to display. Here, we are directed to the Amazon
Website.

11. How can we merge multiple cells text strings in a cell?

To merge text strings present in multiple cells into one cell, you can use the CONCATENATE(). Shown below is an
example of the concatenate function.

Another way of combining cell values is by using the “&” operator, as shown below:
Let’s now move onto the next question on our Excel interview questions list.

12. How can you split a column into 2 or more columns?

You can split a column into 2 or more columns by following the below steps:

1. Select the cell that you want to split. Then, navigate to the Data tab, after that, select Text to Columns.

2. Select the delimiter.

3. Choose the column data format and select the destination you want to display the split.

4. The final output will look like below where the text is split into multiple columns.
13. What is the use of VLOOKUP and how do we use it?
The function VLOOKUP in Excel is used to look up information in a table and extract the corresponding data.

Syntax: VLOOKUP (value, table, col_index, [range_lookup])

value - Indicates the data that you are looking for in the first column of a table.

table - Refers to the set of data (table) from which you have to retrieve the above value.

col_index - Refers to the column in the table from where you are to retrieve the value.

range_lookup - FALSE = exact match [optional] TRUE = approximate match (default).

Shown below is an example of the VLOOKUP function. We are to find the Product related to the Customer Name
– “Richard”.

14. How is VLOOKUP different from the LOOKUP function?

VLOOKUP LOOKUP

Meanwhile, the LOOKUP function

enables the user to look for data in a
VLOOKUP lets the user look for a value in the left-most column of a table.
row/column. It returns the value in
It then returns the value in a left-to-right way.
another row/column.
It is not very easy to use as compared to the LOOKUP function.
It is easier and can also be used to
replace the VLOOKUP function.

15. How many report formats are available in Excel?

There are three report formats available in Excel; they are:

Compact Form
Outline Form

Tabular Form

16. How does the IF() function in Excel work?

In Excel, the IF() function performs a logical test. It returns a value if the test evaluates to true and another value if
the test result is false. It returns the value depending on whether the condition is valid for the entire selected
range.

Let’s look at the below example:

As seen above, the IF function returns “Record is Valid” if age is greater than 20, and the salary should be greater
than $40000. Else, it will return “Record is Invalid”. Here the final answer will be “Record is Valid” as the entire
selected range qualifies both the conditions.

17. How do we use the SUMIF() function in Excel?

The SUMIF() function adds the cell values specified by a given condition or criteria. Given below is an example of
the sumif function.

As seen above, the costs corresponding to the years 2010 are added as per the given criteria.

18. Using the COVID data, find the number of days in which the number of deaths in Italy has been greater than
200.

To perform this operation, we can use the COUNTIFS() function. The dataset we will be using is shown below:
The COUNTIFS() function we use is - =COUNTIFS(G2:G35777,"Italy",E2:E35777,">200")

19. What is a Pivot Table?

A pivot table is like a summary table of the dataset that enables you to create reports and analyze trends. They
are useful when you have long rows or columns that hold values you need to track.

To create a pivot table, first, go to the Insert tab and select the ‘PivotTable’ option.

Select the table or the range and choose where you want to place the pivot table.

Drag the fields you wish to show in the pivot table. Here we have created a pivot table using the Coronavirus
data.
20. Create a drop-down list in Excel.
This can be done by using the ‘Data Validation’ option present in the Data tab.

In the example below, we have created a list based on the city column of the dataset.

Join The Ranks of Top-Notch Data Analyst

21. How do we apply advanced filters in Excel?

To apply advanced filters, use the Advanced Filter option present in the Data tab. Select where you want to filter
the table. Choose the ‘list range’ and the ‘criteria range’ that has the conditions based on which you would like to
filter the table.
The below example shows how to apply advanced filters.

22. Using the below-given sales data, highlight those cells where total sales > $5000.
Here, conditional formatting is used to highlight cells based on the criteria.

1. Select ‘Conditional Formatting’ from the home tab and under Highlight Cells Rules, choose ‘Greater Than
option’.
2. Provide the condition and choose the color for the cells to be highlighted.

23. Using the given table, explain how the index-match function works in Excel.
Here, we will write an index-match function to find the city to which Andrew belongs to from the below table.

Here is how you can use the Index-Match function to get the result.
24. How do you find duplicate values in a column?
To find duplicate values in a column, you can either use Conditional Formatting or the COUNTIF() function.

1. Conditional Formatting

First, go to the Home tab, then under Conditional Formatting, select ‘Highlight Cells Rules’. Then choose
‘Duplicate Values’.

Below, we have highlighted the cells in the ‘Name’ column that have been repeated.

2. COUNTIF()

You can write a COUNTIF() function to check if the values in a particular column are repeated.

In the below example, we are fetching the duplicate names using the COUNTIF() function.

25. How can you remove duplicate values in a range of cells?

1. To delete duplicate values in a column, select the highlighted cells, and press the delete button. After deleting
the values, go to the ‘Conditional Formatting’ option present in the Home tab. Choose ‘Clear Rules’ to remove the
rules from the sheet.
2. You can also delete duplicate values by selecting the ‘Remove Duplicates’ option under Data Tools present in
the Data tab.

Moving forward, let’s have a look at the intermediate level of Excel interview questions.

Intermediate Level Excel Interview Questions

26. What are the wildcards available in Excel?

Wildcards only work with text data. Excel has three wildcards.

1. * (Asterisk)

This refers to any number of characters.

The example stated below filters the customers whose name ends with “a”.

For that, we use “*a”.

2. ? (Question mark)

It represents one single character.

The example below shows how to filter a particular customer name.

3. ~ (Tilde)

It is used to identify a wildcard character (~, *, ?) in the text.

In the following example, we are filtering How?* using the tilde (~) symbol.

27. What is Data Validation? Illustrate with an example.

Data Validation restricts the type of values that a user can enter into a particular cell or a range of cells.

In the Data tab, select the ‘Data Validation’ option present under Data Tools.

Select the kind of data validation you want to apply.

In the following example, we have applied data validation to the ‘Name’ column to accept only text values. If you
enter something other than a text, it will throw an error.

28. Given below is a student table. Write a function to add pass/fail to the results column based on the following
criteria.
If student marks > 60 and attendance > 75%, then pass else the student fails.

You can use the IF() function and check with an AND condition to fill in the results column.
29. Calculate your age in years from the current date.
Use the YEARFRAC() or DATEDIF() function to return the number of whole days between start_date and end_date

YEARFRAC()

DATEDIF()

Learn best business analysis techniques by Purdue University, IB and EY experts. Sign-up for our Post Graduate
Program in Business Analysis TODAY!

30. How are nested IF statements used in Excel?

The function IF() can be nested when we have multiple conditions to meet. The FALSE value in the first IF function
is replaced by another IF function to make a further test.

Below, using nested IF statements, we are categorizing results based on the marks.
31. From the below table, find the descriptive statistics of the columns using the Data Analysis ToolPak in Excel.

Add the Analysis ToolPak from Options ----> Add-ins ----> Analysis ToolPak.

Click on the Data Analysis option in the Data tab. Choose Descriptive Statistics.
Below is the summary table for the columns and their respective statistical measures.

32. Using the Coronavirus dataset, create a pivot table to find the total cases in each country belonging to their
respective continents.
First, drag the continent and country columns into rows. After that, drag the cases column on to the values
section.

33. How do you provide Dynamic Range in ‘Data Source’ of Pivot Tables?
Dynamic Range in the data source of pivot tables is used to make your pivot table dynamic to adjust to new data
when refreshed automatically.

Create a Named table to provide a dynamic range. Go to the Insert tab and select Table.
Under Table Design, give a name to the table.

Become job-ready with a globally recognized Business Analyst Certification Course. Sign-up today and enrich your
career.

34. Is it possible to create a Pivot Table using multiple sources of data?

Yes, you can create a pivot table from multiple worksheets. For this, there must be a common row in both the
tables. This will act as the Primary key for the first table and Foreign key for the second table. Create a
relationship between the tables and then build the pivot table.

35. Create a pivot table to find the top three countries from each continent based on the total cases using COVID
data.
Create a pivot table using the coronavirus dataset by dragging sales into values.

Place the continent and country columns into rows.

Filter the table by selecting ‘Top 3’.

Below is the sequence of steps to follow.

Free Course: Introduction to MS Excel

36. How do you create a column in a pivot table?

For this, you have to go to the PivotTable Analyze tab and select ‘Fields, Items & Sets’ option. Under that, you
need to click ‘Calculate Field’ to create a new column.

The Insert Calculated Field box appears. Give a name to the column and insert the formula by selecting the
existing columns from the pivot table. Click Add ----> OK to create the column.
37. How does a Slicer work in Excel?
To filter data in a Pivot table, we can use slicers.

To create a slicer, go to the Insert tab, and select Slicer present under Filter.

Then, select the list of fields for which you want to create slicers.

In the below example, we have created two slicers (months, countries, and territory) to filter the pivot table.

38. Use the coronavirus dataset to find the percentage contribution of each country and continent to the total
cases?
Create the pivot table to show the total cases by country and continent.

Right-click on the sum of cases column and under Show Value As, select “% of Grand Total.”
39. How do you create a pivot chart in Excel?
To create a pivot chart, first, we need to create a pivot table.

Go to the Insert tab next and select the ‘Pivot Chart’ option. Choose a suitable chart to represent your pivot table
data.

Accelerate your career with our Post Graduate Program in Business Analytics in partnership with Carlson School
of Management. Enroll and start learning!
40. What are macros in Excel? Create a macro to automate a task.
Macro is a program that resides within the Excel file. The use of it is to automate repetitive tasks that you would
like to perform in Excel.

To record a macro, you can either go to the Developer tab and click on Record Macro or access it from the View
tab.

Now that we are done with the intermediate level of the Excel interview questions, let’s move on to the advanced
level of Excel interview questions.

Advanced Level Excel Interview Questions

41. What is the What-If Analysis in Excel?

The What-If Analysis in Excel is a powerful tool to perform complex mathematical calculations, experiment with
data, and try out different scenarios.

Consider the following example:

If you get $10,000 worth of sales over the next few months, how much profit can you expect?”
 Such scenarios can be solved using the What-If Analysis.

Go to the Data tab and click on What-If Analysis present under Forecast.

Scenario Manager is used for a comparison of different scenarios.

The Goal Seek performs reverse calculations.

The Data Table is used for sensitivity analysis.

To learn more about how What-If analysis works, click on this link: “IQ video link”

42. What is the difference between a function and a subroutine in VBA?

Functions Subroutines

A function is responsible for returning the value of the task it is Meanwhile, subroutines don’t return
performing. the value of the task it is performing.

They can be recalled from anywhere in

They are called by a variable.
the program, in multiple types.

Functions are used as it is in spreadsheets as formulas. Subroutines are not used directly in
spreadsheets as formulas.

Functions are used to carry out repetitive tasks, and it, in turn, returns a
value.
 Users are required to insert a value in
the desired cell before fetching the
result of the subroutine.

43. What is the difference between ThisWorkbook and ActiveWorkbook in VBA?

ThisWorkbook ActiveWorkbook

As the name suggests, ActiveWorkbook is the

ThisWorkbook indicates the name of the workbook where the code workbook that is presently active from the
is running from. various open workbooks.

44. How will you pass arguments to VBA Function?

Arguments can be passed to a VBA function as a reference or as a value.

Below is an example to illustrate both the usages.

Dim x As Integer

x = 10

MsgBox Triple(x)

MsgBox x

If you run the cells by passing the values as a reference, it will display 40 both the times. When we pass
arguments by reference, we are referencing the original value. The original value of x is changed in the function.

When we pass the arguments by value, we are passing a copy to the function. The original value is not changed.
Hence, the second MsgBox will display the original value 10.
45. How do you find the last row and column in VBA?
To find the last row, use the below lines code in the VBA module:

Sub FindingLastRow()

Dim lastRow As Long

lastRow = ActiveSheet.Cells.SpecialCells(xlLastCell).Row

MsgBox (lastRow)

End Sub

To find the last column, use the below lines code in the VBA module:

Sub FindingLastColumn()

Dim lastRow As Long

lastColumn = ActiveSheet.Cells.SpecialCells(xlLastCell).Column

MsgBox (lastColumn)

End Sub

Want to learn the latest business analysis skills? Sign-up for our Post Graduate Program in Business Analytics and
gain the skills to excel in the business world. Start learning TODAY!

46. How do we check whether a file exists or not in a specified location?

Sub CheckFileExists()

Dim strFileName As String

Dim strFileExists As String

strFileName = “File location\file_name.xlsx”

strFileExists = Dir(strFileName)

If strFileExists = “” Then

MsgBox “The selected file doesn't exist”

Else

MsgBox “The selected file exists”

End If

End Sub

47. Explain how to debug a VBA code?

To debug a VBA code line by line, you can use the F8 key. You can also create a breakpoint to terminate the
execution wherever you want.
The execution will start from the beginning of the code, and every time you press F8, it will execute the next line
and continue until the end of the code. The yellow arrow and the highlighted line tells you the current point to
execution.

48. Write a VBA function to calculate the area of a rectangle.

Function Area(Length As Double, Optional Width As Variant)

If IsMissing(Width) Then

Area = Length * Length

Else

Area = Length * Width

End If

End Function

49. Write a VBA function to check if a number is a prime number or not.

Sub Prime()

Dim divisors As Integer, number As Long, i As Long

divisors = 0

number = InputBox(“Enter a number”)

For i = 1 To number

If number Mod i = 0 Then

divisors = divisors + 1

End If

Next i

If divisors = 2 Then

MsgBox number & “ is a prime number”

Else

MsgBox number & “ is not a prime number”

End If

End Sub

Learn the latest tools, work on real-world projects, and attend Masterclasses from IBM and EY experts. Join
our Master's program today.

50. Write a VBA code to create a bar chart with the given data.
Consider the below data that has two features. You can use the lines of code below to create a bar chart.

Once you have run the above VBA code lines, below is the bar chart you will get.
 29 MS Excel Interview Questions
1) What is Microsoft Excel?
Microsoft Excel is an electronic worksheet or spreadsheet application which is used for organizing,
storing, and manipulating and analyzing data. It is developed by Microsoft.

2) What are cells?

The area where data is stored is known as cell.

3) Does each cell have unique address?

Yes, each cell has a unique address depends on the row and column value of the cell.

4) How can you add cells, rows or columns in Excel?

If you want to add a cell, row or column in Excel, right click the cell you want to add to and after that
select insert from the cell menu. The insert menu makes you able to add a cell, a column or a row and to
shift the cells affected by the additional cell right or down.

5) How would you format a cell? What are the options?

A cell can be formatted by using the format cells options. There are 6 format cells options:

Number

Alignment

Font

Border

Fill

Protection

6) What is the use of comment? How to add comments to a cell?

Comments are used for a lot of reasons:

Comments are used to clarify the purpose of the cells.

Comments are used to clarify a formula used in the cell.

Comments are used to leave notes for others users about a cell.

To add a comment: Right click the cell and choose insert comment from the cell menu. Type your
comment.

7) What does the red triangle indicate at the top right hand corner of the cell?
The red triangle at the top right hand corner of a cell indicates that there is a comment linked to the
particular cell. If you put your cursor on it, it will show the comment.
8) How would you add comments to a cell?
To add a comment to a cell, you right click the cell and choose insert comment from the cell menu. Type
your comment in the comment area provided. A red triangle at the top right hand corner of a cell
indicates that there is a comment linked to that particular cell. To remove a comment from a cell, right
lick the cell and then select delete comment from the cell menu.

9) What are charts in MS Excel?

Charts are used to enable graphical representation of the data in Excel. A user can use any chart type,
including column, bar, line, pie, scatter, etc. by selecting an option from Insert tab?s Chart group.

10) What is Freeze Panes in MS-Excel?

Freeze Panes are used to lock any row or column. The locked row or column will be visible on the screen
even after we scroll the sheet vertically or horizontally.

11) Which are the different workbook protection types in Excel?

There are three ways to protect a workbook in Excel:

Password protection for opening a workbook

Protection for adding, deleting, hiding and unhiding sheets

Protection from changing size or position of windows.

12) What is the difference among COUNT, COUNTA, COUNTIF and COUNTBLANK in Ms-Excel?
COUNT is used to count cells containing numbers, dates, etc. any value stored as number excluding
blanks.

COUNTA or Count All is used to count any cell value containing numbers, text, logical values, etc. any
type of value excluding blanks.

COUNTBLANK count blank cells or cells with an empty string.

COUNTIF and COUNTIFS count cells matching a certain criteria.

13) What is Ribbon?

The ribbon specifies an area which runs along the top of the application and contains menu items and
toolbars available in Excel. The ribbon has various tabs that contain groups of commands for use in the
application.

14) Is it possible to hide or show the ribbon?

You can hide or show (minimize or maximize) the ribbon by pressing CNTRL F1.
15) How to prevent someone from copying the cell from your worksheet?
If you want to protect your worksheet from being copied, go into Menu bar > Review > Protect sheet >
Password.

By entering password you can prevent your worksheet from getting copied.

16) How to sum up the rows and column number quickly in the Excel sheet?
The SUM function is used to get the total sum of the rows and columns, in an excel worksheet.

17) How can you resize the column?

There are two ways to resize a column:

To change the width of one column, drag the boundary on the right side of the column until you find
your desirable width.

Select the Format from the home tab, and in Format, select the AUTOFIT COLUMN WIDTH under cell
section. Click on this to change the cell size.

18) What are the several report formats in Excel?

There are three report formats in Excel:

Compact

Report

Tabular

19) Is it possible to make Pivot table using multiple sources of data?

If the multiple sources are different worksheets from the same workbook, then you can use these
multiple sources of data to make Pivot table.

20) How can you check whether the Pivot table is modified or not?
To check whether the Pivot table is modified or not, you should use the "PivotTableUpdate" in worksheet
containing the pivot table.

21) What does the IF function in Excel?

IF function is used in Excel to check whether certain conditions are true or false. If the condition is true
then it will give the result accordingly and if the condition is false the result or output will be different.

22) What filter should we use, if you want more than two conditions or if you want to analyze the list using
database function?
You should use "Advanced Criteria Filter" to analyze the list or test more than two conditions.
23) What are the advantages of using formula in Excel sheet?
Formula makes it easy to calculate the numbers in Excel sheet. It also calculates automatically the
number replaced by another number or digit. It is used to make complex calculations easy.

24) What is the order of sequence of operating mathematical operation in Excel?

The order of sequence is written as BEDMAS:

Brackets

Exponents

Division

Multiplication

Addition

Subtraction

25) What is the use of LOOK UP function in MS Excel?

The LOOK UP function is used to return a value from an array.

26) What is a Macro in Excel? How to create an Excel Macro?

Excel Macro is the set of instructions that is recorded by users for repetition purposes. It is created by the
users for repetitive instructions and functions they perform on a regular basis.

How would you reduce the file size? What is the easiest way to reduce the file size?

You can use the following steps to reduce the file size:

Find the last cell that contains data in the sheet. Delete all rows and columns after this cell.

To delete the rows, press the key Shift+Space then press Ctrl+Shift+Down on your keyboard.

Rows will get selected till the last row. Press Ctrl+- on the keyboard to delete the blank rows.

To delete the column, Press the key Ctrl+Space then press Ctrl+Shift+Right Arrow key on your keyboard.

Columns will get selected till the last row.

Press Ctrl+- on the keyboard to delete the blank columns.

27) How many rows and columns are there in Microsoft Excel 2003 and later versions?
You can see the number of columns, rows, cells for Microsoft Excel version 2003 and later versions in the
following table:

Excel Versions Rows Columns Total Cells

MS Excel 2003 65536 256 16777216

MS Excel 2007 1048576 16384 17179869184

MS Excel 2010 1048576 16384 17179869184

MS Excel 2013 1048576 16384 17179869184

28) What is the syntax of Vlookup?

Vlookup Syntax:

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

29) Is it possible to make pivot table using multiple sources of data?

Yes. It is possible by using data modeling technique.
 Top Excel Interview Questions and Answers
To help you with your next job interview, we have listed some of the most popular excel interview questions and
answers.

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, and so on. We can easily rename those sheets as required.

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 too,
parentheses essentially 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

4. What is meant by Relative cell referencing and Absolute cell referencing in MS Excel?
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. That is, it always refers to the same cell and therefore does
not allow them to be modified when copying.

The importance of absolute references is that 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 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 formulas
can be simple or complex and 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, you can select the cell or the text that 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. There are 3 wildcards in Excel that can ve 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 get any word with India at the beginning, followed by
different characters (such as Indian, Indiana). If you have to look for India” exclusively, use ~.
Hence, the search string will be india~*. ~ is used to ensure 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])

11. What are Pivot Tables in Excel?

Ans. Pivot tables are a handy tool for calculating, summarizing, and analyzing data. You can easily make
comparisons and patterns in your data trends using Pivot tables.

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.

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 have to 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 also 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, to stop Excel from automatically converting certain
text values to dates, you can put an ‘=’ before the double quotes. This step forces the data to be text.

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

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.
18. How to create a string or formula containing double quotes in Excel?
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 apply 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 down 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.
 40 Microsoft Excel Interview Questions and Answers (2023)
Here are Microsoft Excel interview questions and answers for fresher as well as experienced candidates to get
their dream job.

1) What is Microsoft Excel?

Microsoft Excel is an electronic spreadsheet application that enables users to store, organize, calculate and
manipulate the data with formulas using a spreadsheet system broken up by rows and columns. It also provides
the flexibility to use an external database to do analysis, make reports, etc. thus saving lots of time.

2) What is ribbon?
Ribbon refers to the topmost area of the application that contains menu items and toolbars available in MS-Excel.
Ribbon can be shown/hidden using CTRL+F1. The ribbon runs on the top of the application and is the replacement
for the toolbars and menus. The ribbons have various tabs on the top, and each tab has its own group of
commands.

3) Explain Spreadsheet and its Basics.

Spreadsheet can be compared to a paper ledger sheet. It consists of rows and columns and their intersection
called cells.

4) How many data formats are available in Excel? Name some of them.
Eleven data formats are available in Microsoft Excel for data Storage. Example:

Number – Stores data as a number

Currency – Stores data in the form of currency
Date – Data is stored as dates
Percentage – Stores numbers as a percentage
Text Formats – Stores data as string of texts

5) Specify the order of operations used for evaluating formulas in Excel.

The order of operations in Microsoft Excel is same as in standard mathematics. It’s defined by the term “PEMDAS”
or “BEDMAS”.

Parentheses or Brackets

Exponent

Multiplication
Division

Addition

Subtraction

6) How can you wrap the text within a cell?

You must select the text you want to wrap, and then click wrap text from the home tab and you can wrap the text
within a cell.

7) Explain Macro in MS-Excel.

Macros are used for iterating over a group of tasks. Users can create macros for their customized repetitive
functions and instructions. Macros can be either written or recorded depending on the user.

8) Which are the two macro languages in MS-Excel?

XLM and VBA (Visual Basic Applications). Earlier versions of Excel used XLM. VBA was introduced in Excel 5 and
mostly used now.

9) Is it possible to prevent someone from copying the cell from your

worksheet?
Yes, it is possible. To protect your worksheet from getting copied, you need to go into Menu bar >Review >
Protect Sheet > Password. By entering a password, you can secure your sheet from getting copied by others.

10) What are charts in MS-Excel?

To enable graphical representation of the data in Excel, charts are provided. A user can use any chart type,
including column, bar, line, pie, scatter, etc. by selecting an option from Insert tab’s Chart group.

11) How can you sum up the Rows and Column number quickly in the Excel
sheet?
By using SUM function, you can get the total sum of the rows and columns, in an Excel worksheet.

12) Explain few useful functions in Excel.

Following are the functions available in Excel for manipulating the data:

Math and Financial Functions – SQRT, DEGREE, RAND(), GCD

Logical Functions – IF, AND, FALSE, TRUE
Date and Time functions – NOW(), DATEVALUE(), WEEKDAY(NOW())
Index Match – VLOOKUP and INDEX MATCH
Pivot tables

13) What does a red triangle at the top right of a cell indicate?
The red triangle indicates that some comment is associated with the cell. Hover the mouse over it, and you can
read the full comment.

14) How can you add a new Excel worksheet?

To add a new Excel worksheet, you should insert worksheet tab at the bottom of the screen.

15) What is the use of NameBox in MS-Excel?

Name Box is used to return to a particular area of the worksheet by typing the range name or cell address in the
name box.

16) How can you resize the column?

To resize the column, you should change the width of one column and then drag the boundary on the right side of
the column heading till the width you want. The other way of doing it is to select the Format from the home tab,
and in Format you have to select AUTOFIT COLUMN WIDTH under cell section. On clicking on this, the cell size will
get formatted.

17) Explain pivot tables and its uses.

A pivot table is a tool that allows for quick summarization of large data. It automatically performs a sort, count,
total or average of the data stored in the spreadsheet and displays result in another spreadsheet. It saves a lot of
time. Allows to link external data sources to our Excel.

18) What are three report formats that are available in Excel?
Following are the types of report formats

Compact

Report

Tabular
19) How would you provide a Dynamic range in “Data Source” of Pivot
Tables?
To provide a dynamic range in “Data Source” of Pivot tables, first, create a named range using offset function and
base the pivot table using a named range created in the first step.

20) Is it possible to make Pivot table using multiple sources of data?

If the multiple sources are different worksheets, from the same workbook, then it is possible to make Pivot table
using multiple sources of data.

21) Which event do you use to check whether the Pivot Table is modified or
not?
To check whether the pivot table is modified or not we use “PivotTableUpdate” in worksheet containing the pivot
table.

22) How can you disable automatic sorting in pivot tables?

To disable automatic sorting in pivot tables:

Go To > More Sort Options > Right Click ‘Pivot tables’ > Select ‘sort menu’ > select ‘More Options’ > deselect ‘Sort
automatically’.

23) What is Freeze Panes in MS-Excel?

To lock any row or column, freeze panes is used. The locked row or column will be visible on the screen even after
we scroll the sheet vertically or horizontally.

24) What could you do to stop the pivot table from loosing the column
width upon refreshing?
Format loss in a pivot table can be stopped simply by changing the pivot table options. Under the “Pivot Table
Options” turn on the “Enable Preserve Formatting” and disable “Auto Format” option.

25) Explain workbook protection types in Excel.

Excel provides three ways to protect a workbook:

Password protection for opening a workbook

Protection for adding, deleting, hiding and unhiding sheets

Protection from changing size or position of windows.

26) Explain the difference between SUBSTITUTE and REPLACE function in
MS-Excel?
The SUBSTITUTE function substitutes one or more instances of old text with the new text in a string.

Syntax: SUBSTITUTE(text, oldText, newText, [instanceNumber])

Example: Let text at A2 be Guru99,Guru99

SUBSTITUTE(A2,”9″,”8″,1) =>Guru89,Guru99

SUBSTITUTE(A2,”9″,”8″,2) =>Guru88,Guru99

SUBSTITUTE(A2,”9″,”9″) =>Guru99,Guru99

The REPLACE function swaps part of the text string with another set of text.

Syntax: REPLACE(oldText, startNumber, NumberCharacters, newText)

Example: Let text at A2 be Guru99

REPLACE(A2,5,1,”00″) =>Guru009

27) Difference between COUNT, COUNTA, COUNTIF and COUNTBLANK in

Ms-Excel.
COUNT is used to count cells containing numbers, dates, etc. any value stored as number excluding blanks.

COUNTA or Count All is used to count any cell value containing numbers, text, logical values, etc. any type of
value excluding blanks.

COUNTBLANK count blank cells or cells with an empty string.

COUNTIF and COUNTIFS count cells matching a certain criteria.

28) What is IF function in Excel?

To perform the logic test IF function is performed. It checks whether certain conditions is true or false. If the
condition is true, then it will give result accordingly if the condition is false then the result or out-put will be
different.

Example: For example, you select the cell, and you want to display that cell as “Greater than five,” when value is
true (=5 or 5) and “less than five” when value is false (<5). For that by using IF condition you can display result.

=IF (Logical test, value if true, value if false)

=IF (A1>5, “Greater than five, “Less than five”)

29) Can we create shortcuts to Excel functions?

Yes. ‘Quick Access Toolbar’ above the home button can be customized to display most frequently used shortcuts.
30) What is the use of LOOKUP function in Excel?
In Microsoft Excel, the LOOKUP function returns a value from a range or an array.

31) How can you apply the same formatting to every sheet in a workbook in
MS-Excel?
Right Click ‘Worksheet tab’ > Choose ‘Select All Sheets’. Now any formatting done will be applied to the whole
workbook. To apply to a particular group of sheets, select only those sheets that need formatting.

32) What are left, right, fill and distributed alignments?

Left /Right alignment align the text to left and right most of the cell.

Fill as the name suggests, fill the cell with same text repetitively.

Distributed, spread the text across the width of the cell.

33) To move to the previous worksheet and next sheet, what keys will you
press?
To move to the previous worksheet, you will use the keys Ctrl + PgUp, and to move to the next sheet you will use
keys Ctrl + PgDown.

34) What filter will you use, if you want more than two conditions or if you
want to analyze the list using database function?
You will use Advanced Criteria Filter, to analyze the list or if more than two conditions should be tested.

35) What is the quick way to return to a particular area of a worksheet?

The quick way to return to a specific area of the worksheet is by using name box. You can type the cell address or
range name in name box to return to a specific area of a worksheet.

36) Which function is used to determine the day of the week for a date?
WEEKDAY () returns the day of the week for a particular date counting from Sunday.

Example: Let date at A1 be 12/30/2016

WEEKDAY(A1,1) =>6
37) What is the benefit of using formula in Excel sheet?
Calculating the numbers in Excel sheet, not only help you to give the final ‘sum up’ of the number but, it also
calculates automatically the number replaced by another number or digit. Through Excel sheet, the complex
calculations become easy like payroll deduction or averaging the student’s result.

38) What is the “What If” condition in Excel formulas?

The “What If” condition is used to change the data in Microsoft Excel formulas to give different answers.

Example: You are buying a new car and want to calculate the exact amount of tax that will be levied on it then you
can use the “What If” function. For instance, there are three cells A4,B4, and C4. First cell says about the amount,
the second cell will tell about the percentage (7.5%) of tax and the final cell will calculate the exact amount of tax.

39) How can you disable the automating sorting in pivot tables?
To disable the automating sorting in pivot tables,

Go to > “More Sort Options”> Right Click “Pivot table” > Select “Sort” menu > Select “More Options” > Deselect
the “Sort automatically when the report is created.”

40) What is the AND function does in Excel?

Like IF function, AND function also does the logical function. To check whether the output will be true or false the
AND function will evaluate at least one mathematical expression located in another cell in the spreadsheet. If you
want to see the output of more than one cells in a single cell, it is possible by using AND function.

Example: If you have two cells, A1 and A2, and the value you put in those two cells are >5 and you want result
should display as ‘TRUE’ in cell B1 if value>5, and ‘False’ if any of those values<5. You can use AND function to do
that.

41) How cell reference is useful in the calculation?

In order to avoid writing the data again and again for calculating purpose, cell reference is used. When you write
any formula, for specific function, you need to direct Excel the specific location of that data. This location is
referred as, cell reference. So, every time a new value added to the cell, the cell will calculate according to the
reference cell formula.
 General Questions:
Q1) Explain MS Excel in brief.
Microsoft Excel is a spreadsheet or a computer application that allows the storage of data in the form of a table.
Excel was developed by Microsoft and can be used on various operating systems such as Windows, macOS, IOS
and Android.

Some of the important features of MS Excel are:

Availability of Graphing tools

Built-in functions such as SUM, DATE, COUNTIF, etc

Allows data analysis through tables, charts, filters, etc

The availability of Visual Basic for Application (VBA)

Flexible workbook and worksheet operations

Allows easy data validation

Q2) What do you mean by cells in an Excel sheet?

The area which falls at the intersection of a column and a row where the information is to be inserted is known as
a cell. There are a total of 1,048,576 x 16,384 cells present in a single excel sheet.

Q3) Explain what is a spreadsheet?

Spreadsheets are a collection of cells that help you manage the data. A single workbook may have more than one
worksheet. You can see all the sheets at the bottom of the window, along with the names that you have given
them. Take a look at the image below:
Q4) What do you mean by cell address?
The cell address of an Excel sheet refers to the address that is obtained by the combination of the Row number
and the Column alphabet. Each cell of an MS Excel sheet will have a distinct cell address.

Q5) Can you add cells?

Yes, you can insert new cells into a sheet. To add a new cell, simply select the cell where you want to insert it and
then select the Insert option. you will see the following window:

Select the desired option and then click on OK.

Q6) Can you format MS Excel cells? If yes, then how?

Yes, MS Excel cells can be formatted. In order to format these cells, you can use the commands present in the
Font group of the Home tab. When you open the Font window, you will see the following options:

Name Description

Allows formatting cells to be of any type such as

Number
currency, accounting, date, percentage, etc

Alignment Allows text control, alignment and setting its direction

Font Enables various fonts, styles, sizes, colors, etc

Allows cell borders to be changed, removed, colored,

Border
etc

Enables you to choose different colors and styles to fill

Fill
up the cell

Protection Allows you to lock or hide cells

Q7) Can you add comments to a cell?

Yes, comments can be added. To add comments to a cell, select the cell, right-click on it and then select the New
Comment option. These comments will be visible to all those people who have access to the Excel sheet.
Q8) Can you add new rows and columns to an Excel sheet?
Yes, you can add rows and columns to an Excel sheet. To add new rows and columns select the place where you
intend to add them and right-click on it. Then select the Insert option from where you can choose to select an
entire row or column.

Q9) What is Ribbon and where does it appear?

The Ribbon is basically your key interface with Excel and it appears at the top of the Excel window. It allows users
to access many of the most important commands directly. It consists of many tabs such as File, Home, View,
Insert, etc. You can also customize the ribbon to suit your preferences. To customize the Ribbon, right-click on it
and select the “Customize the Ribbon” option. You will see the following window:

You can select or unselect any option of your choice from here.

Q10) How do you freeze panes in Excel?

MS Excel allows you to freeze panes that will help you see the headings of the rows and the columns even if scroll
down a long way on the sheet. To Freeze Panes in Excel, follow the given steps:

First, select the Rows and Columns you wish to freeze

Then, select Freeze Pane present in the View tab

Here, you will see the following three options to selectively freeze the rows and columns as shown in the image
below:

Q11) How do you add a Note to a cell?

To add a Note, select the cell and right-click on the same. then select the New Note option and type in any note
that you wish to. In case you want to delete the Note, follow the same procedure and select the Delete Note
option. Notes are indicated by a red triangle at the top-right corner of the cell.
Q12) Can you protect workbooks in Excel?
Yes, workbooks can be protected. Excel provides three options for this:

Passwords can be set to open Workbooks

You can protect sheets from being added, deleted, hidden or unhidden

Protecting window sizes or positions from being changed

Q13) How do you apply a single format to all the sheets present in a workbook?
To apply the same format to all the sheets of a workbook, follow the given steps:

Right-click on any sheet present in that workbook

Then, click on the Select All Sheets option

Format any of the sheets and you will see that the format has been applied to all the other sheets as well

Q14) What do you understand by Relative Cell Addresses?

Whenever you copy formulas in Excel, the addresses of the reference cells get modified automatically in order to
match the position where the formula is copied. This is done by a system that is called Relative Cell Addresses.

EXAMPLE:

Take a look at the image below where I have written the formula in C9 and copying the same formula to C10. As
you can see, C10 shows the sum of A10 and B10, unlike A9 and B9.
Q15) In case you don’t want to modify the cell addresses when they are copied, what should you do?
If you do not want Excel to change the addresses when you copy formulas, you must make use of Absolute Cell
Addresses. When you use Absolute Cell References, the row and the column addresses do not get modified and
remain the same.

Example:

For absolute referencing, you will need to use the $ sign before column and row number. Take a look at the
example shown in the given image:

Q16) What will you do if you want to change either the column letter or the row number but not both?
To do this, you must make use of Mixed Cell Addresses where either the row or column is relative while the other
is absolute.

EXAMPLE:

Take a look at the image below where the columns hold relative referencing while the rows are absolute.
Therefore, the values that are to be added in C9 are 5 and 5 since the column letter is the same as in the original
formula and hence the result.
Q17) Can you protect cells of a sheet from being copied?
Yes, you can do it by protecting the required cells or the complete sheet. In order to do this, follow the given
steps:

Select the cells that you want to protect

Open up the Font window from the Home tab

From the Protection pane, select Protection and then check the Hidden box

Click on Review tab present in the Ribbon, and then select Protect sheet option (Excel will not hide the required
cells unless you do this)

Specify a password (This will help you in unprotecting the sheet later)

Q18) How do you create Named Ranges?

To create named ranges, follow the given steps:

Select the area to which you intend to give a name

From Ribbon, select Formulas

Click on Define Name from Defined Names group

Give any name of your choice

Q19) What are macros?

Excel allows you to automate the tasks you do regularly by recording them into macros. So, a macro is an action
or a set of them that you can perform n number of times. For example, if you have to record the sales of each
item at the end of the day, you can create a macro that will automatically calculate the sales, profits, loss, etc and
use the same for the future instead of manually calculating it every day.

Q20) How do you create dropdown lists in Excel?

To create dropdown lists, follow the given steps:

Click on Data tab present in the ribbon

Then, from the Data Tools group, click on Data Validation

Navigate to Settings>Allow>List

Select the source list array

Pivot Tables and Pivot charts:

Q21) Explain Pivot tables along with their features?
Pivot Tables are statistical tables that condense data of those tables that have extensive information. The
summary can be based on any field such as sales, averages, sums, etc that the pivot table represents in a simple
and intelligent manner.

Features:
Some of the features of Excel Pivot Tables are as follows:
Allow the display of exact data you have to analyze

Provide various angles to view the data

Allow you to focus on important details

Comparison of data is very handy

Pivot tables can detect different patterns, relationships, data trends, etc

They can create instant data

Accurate reports

Serve the base for Pivot charts

Q22) How do you create Pivot Tables?

In order to create a Pivot table, you will first need to prepare the data in a tabular format. Keep the following
points in mind while preparing the data:

Arrange the data into rows and columns

The first row should contain unique heading for each of the columns

The columns should have only one type of data

Rows must have data for a single recording only

No blank rows

Columns should not be completely blank

The data for creating Pivot table should be separate from other data present in the sheet

For example, let’s create a Pivot chart for the table shown in the image below:
To create a Pivot table, select the table and click on the Insert tab. then select Pivot Table command and you will
see the following window:
Specify where you intend to create the table and then click on OK. Once this is done you will see that an empty
pivot table has been created. Also, PivotTables Fields pane will open that will help you configure the Pivot table.
Take a look at the image below where I have created a Pivot Table:

Q23) What are Pivot charts in MS Excel?

MS Excel charts are data visualization tools that help you visualize data in various ways. These charts can be of
any type such as Bar, Pie, Area, Line, Doughnut, etc.

Now, if you wish to create a pivot chart for the table, select any cell from the table and then from the Insert tab,
choose the Pivot Chart option.

Choose any chart of your preference and click on OK. You can also format these charts respectively.

Q24) Can you create Pivot tables using multiple tables?

Yes, you can create Pivot tables using more than one base table. To do this, follow the given steps:

Press Alt+D and then press P to open up the PivotTable Wizard

Then select Multiple consolidation ranges option and click on Next and you will see another dialog

Select I will create the page fields option and click on Next

In the next window, you will need to add all the required ranges

Once that is done, click on Next

Specify the region where you want to create the table and then click on Finish

You will see the pivot table has been created by merging both the tables.

Q25) What happens when you check the Defer Layout Update option present in the PivotTable Fields
window?
In case you check this option, you will not see dynamic changes while interchanging the table fields. By default,
this option is off or unchecked. All the changes will appear only after you click on the Update button when you
check this box.

Q26) Can you create a pivot table using tables from different worksheets?
If both the sheets are from the same workbook, you can create a pivot table for tables from different sheets as
well. To create a pivot table from two different sheets, follow the same steps as shown in Q24 and when you
specify the tables, go to the respective sheet and select the tables you intend to merge.

Q27) Is it possible to see the details of the results displayed in a pivot table?
Yes, it is possible to see the details of the results shown by the pivot tables in Excel. In order to see the details for
any result, double-click on the value and you will see that a new sheet has been created with a new table having
details about the factors that have led to that particular result.

Q28) How are PivotTables used to filter data?

Excel PivotTables allow you to filter data according to your requirements. To do this, place the field based on
which you wish to filter out the data. Then from the pivot table, open the dropdown list present for the field you
have placed in the Filter area and select the section of your choice.

As you can see, I have filtered the data for Chicago.

Q29) How do you change the value field to show some other result other than the Sum?
In order to change the value field to show results other than the Sum, right-click on the Sum of Amount values
and then click on Value Field Settings. Here is the dialog box that you will see

From here, you can select any value of your choice and then click on OK.

Q30) How to stop automatic sorting in PivotTables?

Excel automatically sorts the data present in the Pivot Tables. In case you do not want Excel to do this, open the
dropdown menu fro the Row Labels or the Column Labels, and then click on More Sort Options. You will see the
Sort dialog box opening. Click on More Options and unselect the Sort automatically option.

Formulas and Functions:

Q31) What do you understand by Excel functions?

Functions, in Excel, are used to perform specific tasks. Excel has many built-in functions that are used to calculate
results of various formulas thereby helping in time conservation. Also, these functions make it very easy to
execute formulas which would have been difficult to manually write down.

Q32) What are the various categories of functions available in Excel?

Functions in Excel are categorized as follows:
Catagory Important Formulas

Date & Time DAY, DATE, MONTH, etc

Financial ACCINTM, DOLLARDE, ACCINT, etc

Math & Trig SUM, SUMIF, PRODUCT, SIN, COS, etc

Statistical AVERAGE, COUNT, COUNTIF, MAX, MIN, etc

Lookup & Reference COLUMN, HLOOKUP, ROW, VLOOKUP, CHOOSE, etc

Database DAVERAGE, DCOUNT, DMIN, DMAX, etc

Text BAHTTEXT, DOLLAR, LOWER, UPPER, etc

Logical AND, OR, NOT, IF, TRUE, FALSE, etc

Information INFO, ERROR.TYPE, TYPE, ISERROR, etc

Engineering COMPLEX, CONVERT, DELTA, OCT2BIN, etc

Cube CUBESET, CUBENUMBER, CUBEVALUE, etc

Compatibility PERCENTILE, RANK, VAR, MODE, etc

Web ENCODEURL, FILTERXML, WEBSERVICE

Q33) What is the operator precedence of formulas in Excel?

Formulas in Excel are executed according to the BODMAS rules. BODMAS, as many of us know, stands for
Brackets Order Division Multiplication Addition and Subtraction. That means, in every formula, brackets are
executed first (if they are present) followed by multiplication, division, etc

Q34) Explain SUM and SUMIF functions.

SUM: The SUM function is used to calculate the sum of all the values that are specified as a parameter to it. The
syntax of this function is as follows:

SUM(number1, number2, …)

EXAMPLE:
As you can see in the image, the SUM function is calculating the total price for all the vegetables.

SUMIF: This function is used to calculate the sum of values that comply with a given condition.

SYNTAX:

SUMIF(range, criteria, [sum_range])

where,

range specifies the range of cells to be evaluated

criteria provides the condition to be met

sum_range is optional and provides the actual cells to be summed up

Q35) What are the different types of COUNT functions available in Excel?
Excel provides five types of COUNT functions i.e COUNT, COUNTA, COUNTBLANK, COUNTIF, and COUNTIFS.

The COUNT returns the total number of cells that have numbers in the range that is specified to it as a parameter.

SYNTAX:

COUNT(value1, value2, …)

The COUNT function is used to calculate the number of cells having numerical data in them.

COUNTA: Counts the number of cells in a given range that are not empty.

SYNTAX:

COUNT(value1, [value2], …)

The functionality of the COUNTA function that returns the number of cells that are not between A4 and B10.
COUNTIF: This function counts the number of cells that comply to a given condition.

SYNTAX:

COUNTIF(range, criteria)

The COUNTIF function is used to calculate the number of cells that have the name.

COUNTBLANK: Counts all the blank cells in a given range.

SYNTAX:

COUNTBLANK(range)

COUNTIFS: This is a special function that allows you to specify a set of conditions in order to count them.

SYNTAX:

COUNTIFS(criteria_range1,range1,[criteria_range2, criteria2], …)

Q36) How do you calculate the percentage in Excel?

Percentages, as we all know, are ratios that are calculated as a fraction of 100. Mathematically, the percentage
can be defined as follows:

Percentage = (Part/ Whole) x 100

In Excel, the percentage can be calculated in a similar manner.

Here are the steps followed in order to obtain the result:

Select the cell destination cell to display the percentage

Then, type a “=” sign

Type in A1/ A2 then hit the Enter key

Click on Home tab, select % symbol from the numbers group

Q37) Explain how to calculate compound interest in Excel?

To calculate compound interest in Excel, you can use the FV function. FV returns the future value of an
investment based on the periodic, constant interest rate and payments.

SYNTAX:

FV(rate, nper, pmt, pv, type)

To find the rate, the number of periods are used to divide the annual rate (annual rate/ periods). nper is obtained
by multiplying the no. of years (term) with the periods (term * periods). Periodic payment (pmt) can be any value
(including zero).

Q38) How do you find averages in MS Excel?

Average can be calculated using the AVERAGE function.

SYNTAX:

AVERAGE(number1, number2, …)

Q39) What is VLOOKUP in Excel?

VLOOKUP is a function present in Excel used to lookup and bring forth data from a given range. V in VLOOKUP
stands for Vertical and to use this function, data should be arranged vertically. VLOOKUP is very useful when you
have to find some piece of data from a huge amount of data.

Q40) How does the VLOOKUP function work?

The VLOOKUP function, in Excel, a lookup value and begins to look for the same in the leftmost column. When it
finds the first occurrence of the given lookup value, VLOOKUP starts to move right i.e in the row where the value
was found. It goes on until the column number specified by the user and returns the desired value. This function
is used to match exact and approximate lookup values. However, the default match is an approximate match.

Syntax:
VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

here,

lookup_value gives the value to be looked out for

table_index is the range from where the data is to be taken

col_index_num specifies the column from which you want to fetch the value

range_lookup is a logical value i.e TRUE or FALSE (TRUE will find the closest match; FALSE checks for exact match)

Q41) Explain the exact match with an example.

For an exact match, set the range_lookup value as FALSE.

EXAMPLE:

In case you want to look for the designation of an employee, follow the given steps:

Select the destination cell and type “=”

Use VLOOKUP

Specify the lookup_value (Here, it is the ID) along with the other parameters

Set range_lookup value to FALSE

The function will be: =VLOOKUP(104, A1: D8, 3, FALSE)

---VLOOKUP has returned the designation of the employee having 104 as his ID.

Q42) Explain the approximate match with an example.

For an approximate match, VLOOKUP will fetch values when there are no exact matches of the given
loopup_value. For an approximate match, set the range_lookup value to TRUE. Remember that the table must be
sorted in ascending order for VLOOKUP to do an approximate match. So here, VLOOKUP basically starts to look
for an approximate match of the given lookup value and the, stops at a value that is next largest of the given
lookup value. It then moves into that row to return the value from the column that has been specified.

Follow the same steps specified for exact match

For the range_lookup value, use TRUE

The function will be: =VLOOKUP(55, A12: C15, 3, TRUE)

The lookup value is 55 and the next largest of the lookup value present in the first column is 40. Hence, the output
is ‘Second Class’.

Q43) Can you use VLOOKUP for multiple tables?

Yes, you can use VLOOKUP for multiple tables as well. In case you have two lookup tables, create named ranges
for each table, and then use the IF function to select between each table based on some given condition.
Click here to know more about this.

Q44) How do you perform a horizontal lookup in Excel?

To perform a horizontal lookup, you will have to make use of the HLOOKUP function.

SYNTAX:

HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])

here,

lookup_value gives the value to be looked out for

table_index is the range from where the data is to be taken

row_index_num specifies the row from which you want to fetch the value

range_lookup is a logical value i.e TRUE or FALSE (TRUE will find the closest match; FALSE checks for exact match)

Q45) How will you fetch the current date in Excel?

You can make use of the TODAY function. This function will return the current date in the MS Excel date format.

SYNTAX:

TODAY()

Q46) How does the AND function work?

The AND function in Excel is used to whether a given condition or a set of conditions is TRUE or not. In case all the
conditions are satisfied, this function will return a boolean TRUE.

SYNTAX:

AND(logical1, [logical2], …)

where,

logical1, logical2, … are conditions from 1-255 that you want to check

Q47) What is the What If Analysis?

What If Analysis is the technique of performing changes to one or more formulas present in the cells in order to
see how it affects the result of those formulas in the worksheet. Excel provides three types of What If Analysis
tools:

Scenarios

Goal Seek
Data Tables

Scenarios and Data Tables take a set of inputs to check for the potential results. Scenarios can work with many
variables but input values can be at the max 32. Data tables, on the other hand, work with just one or two
variables but can accept many distinct values for each of those variables.

Goal Seek, in contrast to Scenarios and Data Tables, takes the outputs and determines the possible inputs for the
same.

Q48) Can you create shortcuts for most frequently used formulas?
Yes, you can do it by customizing the Quick Access Toolbar. To customize it, right-click anywhere on the Quick
Access Toolbar and select the Customize Quick Access Toolbar option. You will see the following window:

From here, select Formulas and then choose any formula that you wish to create a shortcut for.

Q49) What is the difference between formulas and functions in Excel?

Formulas are that are defined by the user that is used to calculate some results. Formulas either be simple or
complex and they can consist of values, functions, defined names, etc.

A function, on the other hand, is a built-in piece of code that is used to perform some particular action. Excel
provides a huge number of built-in functions such as SUM, PRODUCT, IF, SUMIF, COUNT, etc.

Q50) How do you use wildcards with VLOOKUP?

Wildcards can be used when you are not sure of the exact lookup value. In order to use wildcards in Excel, you
should make use of the “*” symbol.

For example, in the table that you see in the image below, if you enter “erg” and then use wildcards with it,
VLOOKUP will fetch the output that corresponds to “Sergio”.
This brings us to the end of this article on Excel Interview Questions. I hope you are clear with all that has been
shared with you. Make sure you practice as much as possible and revert your experience.