Excel Notes

Table of Contents

1. Excel HOME, Excel Introduction.

2. Excel Get Started, Excel Overview, Excel Syntax, Excel Ranges, Excel Fill, Excel Add Cells.
3. Excel Delete Cells, sheet duplicate, Excel Undo Redo
4. Excel Arithmetic Operators, Excel Parentheses
5. Autofit column width, Autosum shortcut.
6. Average VS Percentage, Decrease Increase Decimal.
7. Excel Formatting
8. Excel Format Painter,Colors, Print, Fonts, Borders, Numbers.
9. Insert row and column, Delete row and column.
10. Convert Data into Table
11. Excel Sort, Filter
12. Excel Conditional Format, Highlight Cell Rules, Top Bottom Rules, Data Bars, Color Scales, Icon Sets, Charts
13. AND, AVERAGE, AVERAGEIF, AVERAGEIFS, CONCAT, COUNT, COUNTA, COUNTBLANK, COUNTIF,
COUNTIFS, IF, IFS, LEFT, LOWER, MAX, MEDIAN, MIN, MODE, NPV, OR, RAND, RIGHT, SUM, SUMIF,
SUMIFS, TRIM.
14. If Formulas
15. Insert charts in excel.
16. Format chart area
17. Home tab all options
18. Find remove duplicates
19. Slicers
20. Rounding of numbers, Autofill,
21. Add or Edit Comments
22. Table And Filters
23. Insert Pictures and Shapes
24. Insert Hyperlinks
25. Create Drop Down list
26. Split test
27. V lookup and H lookup function
28. Pivot Table
 29. Macro
30. Data cleaning, fill blank space, Go To.
31. Index, Match formula.
32. Data Validation.
33. Exact, Find and Search Function.

Excel Home

The Home tab in Excel contains the most commonly used features and commands for formatting and editing your
data. These include options for font styles, alignment, number formatting, cell styles, and editing tools.

Excel Introduction

Excel Introduction covers the basics of Microsoft Excel, a spreadsheet application that allows users to organize,
format, and calculate data with formulas using a system of rows and columns.

Excel Get Started

Excel Get Started includes initial steps such as opening Excel, creating a new workbook, navigating through the
interface, and understanding the ribbon, tabs, and worksheets.

Excel Overview

Excel Overview gives a general understanding of Excel's capabilities, including data entry, formatting, calculations,
chart creation, and data analysis.

Excel Syntax

Excel Syntax refers to the rules and structure for writing formulas and functions in Excel. For example, a basic
formula follows the syntax: =FunctionName(arguments).
Excel Ranges

Excel Ranges are groups of adjacent cells that you can select and manipulate as a single unit. For example, A1:B5
refers to a range of cells from A1 to B5.

Excel Fill

Excel Fill allows you to quickly copy data or extend a data series. For example, dragging the fill handle at the corner
of a cell can copy the cell's content to adjacent cells or continue a series of numbers.

Excel Add Cells

Excel Add Cells lets you insert new cells into an existing worksheet. Right-click on a cell and choose "Insert" to add a
cell, row, or column.

Excel Delete Cells

Excel Delete Cells allows you to remove cells, rows, or columns. Right-click on a cell and choose "Delete" to remove
the selected cell or range.

Sheet Duplicate

Sheet Duplicate lets you create a copy of an existing worksheet within the same workbook. Right-click on the sheet
tab and select "Move or Copy."

Excel Undo Redo

Excel Undo Redo provides options to undo the last action or redo an action that was undone. Use Ctrl + Z for
undo and Ctrl + Y for redo.

Excel Arithmetic Operators

Excel Arithmetic Operators include + (addition), - (subtraction), * (multiplication), / (division), and ^

(exponentiation).

Excel Parentheses

Excel Parentheses are used to change the order of operations in a formula. For example, =(A1+B1)*C1 ensures
that A1 and B1 are added before multiplying by C1.

Autofit Column Width

Autofit Column Width adjusts the width of a column to fit the contents. Double-click the boundary on the right side of
the column header.
Autosum Shortcut

Autosum Shortcut quickly adds a range of cells using the SUM function. Use the Alt + = shortcut to insert the SUM
function.

Average vs Percentage

Average is the sum of a range of values divided by the number of values. Percentage represents a fraction of 100,
often calculated as (part/whole) * 100.

Decrease Increase Decimal

Decrease/Increase Decimal changes the number of decimal places displayed in a cell. Use the buttons in the Home
tab under the Number group.

Excel Formatting

Excel Formatting includes options to change the appearance of cells, such as font size, color, borders, and cell
styles.

Excel Format Painter

Excel Format Painter copies formatting from one cell and applies it to another. Select the cell with the desired
formatting, click the Format Painter, then select the target cell.

Colors, Print, Fonts, Borders, Numbers

These are options under the Home tab for customizing cell appearance. Colors change the background or font color,
Print settings adjust how the worksheet is printed, Fonts modify the text style, Borders add lines around cells, and
Numbers change how numeric values are displayed.

Insert Row and Column

Insert Row and Column allows you to add new rows or columns to a worksheet. Right-click a row or column header
and select "Insert."

Delete Row and Column

Delete Row and Column removes existing rows or columns. Right-click a row or column header and select "Delete."

Convert Data into Table

Convert Data into Table transforms a range of cells into a formatted table with features like sorting and filtering.
Select the range and use Ctrl + T to create a table.
Excel Sort, Filter

Excel Sort organizes data in ascending or descending order. Filter allows you to display only the rows that meet
certain criteria.

Excel Conditional Format

Excel Conditional Format changes cell formatting based on specific conditions. For example, highlight cells that are
above a certain value.

Highlight Cell Rules

Highlight Cell Rules is a type of conditional formatting that highlights cells based on rules, such as cells greater than
a specified value.

Top Bottom Rules

Top Bottom Rules highlight the highest or lowest values in a range. For example, highlight the top 10% of scores in a
list.

Data Bars, Color Scales, Icon Sets

These are types of conditional formatting:

● Data Bars add a bar in the cell to visually represent the value.
● Color Scales use different colors to represent the range of values.
● Icon Sets display icons to represent different values.

Charts

Charts are graphical representations of data. Common types include bar charts, line charts, pie charts, and scatter
plots.

Excel Functions (AND, AVERAGE, etc.)

Here are explanations for each function mentioned:

AND: Returns TRUE if all arguments are TRUE.

=AND(A1>10, B1<20)

AVERAGE: Calculates the average of a range.

=AVERAGE(A1:A10)

AVERAGEIF: Calculates the average of a range based on a condition.

=AVERAGEIF(A1:A10, ">10")

AVERAGEIFS: Calculates the average based on multiple conditions.

=AVERAGEIFS(A1:A10, B1:B10, ">10", C1:C10, "<20")

CONCAT: Combines multiple text strings.

=CONCAT(A1, B1)

COUNT: Counts the number of numeric cells in a range.

=COUNT(A1:A10)

COUNTA: Counts the number of non-blank cells.

=COUNTA(A1:A10)

COUNTBLANK: Counts the number of blank cells.

=COUNTBLANK(A1:A10)

COUNTIF: Counts cells that meet a specific condition.

=COUNTIF(A1:A10, ">10")

●
COUNTIFS: Counts cells based on multiple conditions.

=COUNTIFS(A1:A10, ">10", B1:B10, "<20")

IF: Returns one value if a condition is TRUE and another if FALSE.

=IF(A1>10, "Yes", "No")

IFS: Returns a value based on multiple conditions.

=IFS(A1>10, "High", A1>5, "Medium", TRUE, "Low")

LEFT: Extracts a specified number of characters from the left.

=LEFT(A1, 3)

LOWER: Converts text to lowercase.

=LOWER(A1)

MAX: Returns the maximum value in a range.

=MAX(A1:A10)

MEDIAN: Returns the median value in a range.

=MEDIAN(A1:A10)

MIN: Returns the minimum value in a range.

=MIN(A1:A10)
 ●

MODE: Returns the most frequent value in a range.

=MODE(A1:A10)

NPV: Calculates the net present value of an investment.

=NPV(rate, value1, value2, ...)

OR: Returns TRUE if any argument is TRUE.

=OR(A1>10, B1<20)

RAND: Returns a random number between 0 and 1.

=RAND()

RIGHT: Extracts a specified number of characters from the right.

=RIGHT(A1, 3)

SUM: Adds up a range of numbers.

=SUM(A1:A10)

SUMIF: Adds up cells based on a condition.

=SUMIF(A1:A10, ">10")

SUMIFS: Adds up cells based on multiple conditions.

=SUMIFS(A1:A10, B1:B10, ">10", C1:C10, "<20")

 ●

TRIM: Removes extra spaces from text.

=TRIM(A1)

If Formulas

If Formulas use the IF function to create conditional logic in calculations. For example:

=IF(A1>10, "High", "Low")

Insert Charts in Excel

Insert Charts in Excel involves selecting your data, going to the Insert tab, and choosing a chart type to visually
represent your data.

Format Chart Area

Format Chart Area includes customizing the appearance of the chart, such as changing colors, fonts, and adding
titles or labels.

Home Tab All Options

The Home Tab contains options for clipboard actions (cut, copy, paste), font formatting, alignment, number formatting,
styles, cells (insert, delete, format), and editing (find, replace, select).

Find Remove Duplicates

Find Remove Duplicates helps identify and remove duplicate values in a range. Use the Data tab and click "Remove
Duplicates."

Slicers

Slicers are visual tools to filter data in PivotTables and tables. Insert a slicer from the Insert tab and use it to filter data
easily.

Rounding of Numbers

Rounding of Numbers can be done using functions like ROUND, ROUNDUP, and ROUNDDOWN.
=ROUND(A1, 2) // Rounds A1 to 2 decimal places

Autofill

Autofill allows you to quickly fill cells with a series or pattern. Drag the fill handle to extend a sequence of numbers,
dates, or formulas.

Add or Edit Comments

Add or Edit Comments by right-clicking a cell and selecting "Insert Comment" or "Edit Comment." Comments can
provide additional context or notes.

Table and Filters

Table and Filters enhance data management. Convert a range to a table (Ctrl + T), and use filters to display
specific data.

Insert Pictures and Shapes

Insert Pictures and Shapes involves using the Insert tab to add images and geometric shapes to your worksheet for
visual enhancement.

Insert Hyperlinks

Insert Hyperlinks by selecting a cell and using Ctrl + K to link to other documents, web pages, or locations within
the workbook.

Create Drop Down List

Create a Drop Down List using Data Validation. Select a range, go to the Data tab, and choose Data Validation to
create a list of options.

Split Text

Split Text can be done using the Text to Columns feature under the Data tab. It separates text based on a delimiter
(e.g., comma, space).

VLOOKUP and HLOOKUP Function

VLOOKUP searches for a value in the first column of a range and returns a value in the same row from a specified
column.
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

HLOOKUP searches for a value in the first row and returns a value in the same column from a specified row.

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

Pivot Table

Pivot Table allows you to summarize and analyze data. Insert a PivotTable from the Insert tab and drag fields to the
rows, columns, and values areas.

Macro

Macro automates repetitive tasks. Record a macro using the Developer tab and assign it to a button or keyboard
shortcut.

Data Cleaning

Data Cleaning involves removing duplicates, correcting errors, and filling blank spaces to prepare data for analysis.
Use tools like Go To Special to identify and fix issues.

Index, Match Formula

Index returns the value of a cell in a specified row and column.

=INDEX(array, row_num, [column_num])

Match returns the relative position of a value in a range.

=MATCH(lookup_value, lookup_array, [match_type])

Combined, they provide a powerful lookup solution.

=INDEX(A1:A10, MATCH(lookup_value, B1:B10, 0))

Data Validation

Data Validation restricts the type of data that can be entered in a cell. Use the Data tab to set rules and criteria for cell
entries.

Exact, Find and Search Function

Exact checks if two text strings are exactly the same.

=EXACT(A1, B1)

Find locates a substring within a text string and is case-sensitive.

=FIND("text", A1)

Search locates a substring within a text string and is not case-sensitive.

=SEARCH("text", A1)

These topics provide a comprehensive understanding of Excel's features and capabilities. If you need more detailed
examples or explanations for specific topics, feel free to ask!
 These are the some important questions in excel

1. What are the different data types in Excel?

Answer: Excel primarily supports the following data types:

● Number: Numeric values.

● Text: Alphanumeric strings.
● Date/Time: Date and time values.
● Boolean: TRUE/FALSE values.
● Error: Error values like #DIV/0!, #N/A, etc.

2. How do you use the VLOOKUP function?

Answer: The VLOOKUP function searches for a value in the first column of a table and returns a value in the same row
from a specified column.

Syntax:

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

Example:

=VLOOKUP("John", A2:C10, 3, FALSE)

Explanation: This formula looks for "John" in the first column of the range A2:C10 and returns the value from the
third column of the same row.

3. How do you apply conditional formatting in Excel?

Answer: Conditional formatting changes the appearance of cells based on specific conditions.

Example:

1. Select the range (e.g., A1:A10).

2. Go to Home > Conditional Formatting.
3. Choose a rule type (e.g., "Highlight Cell Rules" > "Greater Than").
4. Enter the condition value (e.g., 50) and select a format.
4. What is the difference between COUNT and COUNTA?

Answer:

● COUNT counts the number of cells that contain numbers.

● COUNTA counts the number of non-empty cells.

Example:

=COUNT(A1:A10) // Counts numeric cells

=COUNTA(A1:A10) // Counts all non-empty cells

5. How can you remove duplicates in a dataset?

Answer:

1. Select the data range.

2. Go to Data > Remove Duplicates.
3. Select the columns to check for duplicates and click OK.

6. What is the use of the IF function in Excel?

Answer: The IF function performs a logical test and returns one value if true, and another if false.

Syntax:

=IF(logical_test, value_if_true, value_if_false)

Example:

=IF(A1 > 10, "Pass", "Fail")

Explanation: If the value in A1 is greater than 10, the formula returns "Pass"; otherwise, it returns "Fail".

7. Explain how to use PivotTables.

Answer: PivotTables summarize and analyze large datasets.

Steps:
 1. Select the data range.
2. Go to Insert > PivotTable.
3. Choose the data range and location for the PivotTable.
4. Drag fields to the Rows, Columns, Values, and Filters areas.

8. How do you concatenate strings in Excel?

Answer: You can use the CONCAT or & operator to combine strings.

Example:

=CONCAT(A1, " ", B1)

or

=A1 & " " & B1

Explanation: Combines the values in A1 and B1 with a space between them.

9. How do you use the SUMIF function?

Answer: The SUMIF function adds values based on a condition.

Syntax:

=SUMIF(range, criteria, [sum_range])

Example:

=SUMIF(A1:A10, ">10", B1:B10)

Explanation: Adds the values in B1:B10 where the corresponding value in A1:A10 is greater than 10.

10. How do you create a drop-down list in Excel?

Answer:

1. Select the cell or range.

2. Go to Data > Data Validation.
 3. In the "Allow" box, select "List".
4. Enter the list items separated by commas or refer to a cell range.

11. What is the use of the MATCH function?

Answer: The MATCH function searches for a value in a range and returns the relative position of the value within the
range.

Syntax:

=MATCH(lookup_value, lookup_array, [match_type])

Example:

=MATCH("Apple", A1:A10, 0)

Explanation: Returns the position of "Apple" in the range A1:A10.

12. How do you create a PivotChart?

Answer:

1. Create a PivotTable.
2. Select any cell in the PivotTable.
3. Go to PivotTable Analyze > PivotChart.
4. Choose a chart type and click OK.

13. Explain the INDEX function.

Answer: The INDEX function returns the value of a cell in a specified row and column of a range.

Syntax:

=INDEX(array, row_num, [column_num])

Example:

=INDEX(A1:C10, 2, 3)
Explanation: Returns the value in the second row and third column of the range A1:C10.

14. How do you use the OFFSET function?

Answer: The OFFSET function returns a reference to a range that is a specified number of rows and columns from a
starting cell.

Syntax:

=OFFSET(reference, rows, cols, [height], [width])

Example:

=OFFSET(A1, 2, 3, 1, 1)

Explanation: Returns the value that is 2 rows down and 3 columns to the right of A1.

15. How do you use Excel to perform a linear regression?

Answer:

1. Select the data range.

2. Go to Insert > Scatter chart.
3. Right-click on the data series and select "Add Trendline".
4. Choose "Linear" and display the equation on the chart.

16. Explain the XLOOKUP function.

Answer: The XLOOKUP function searches a range or array and returns an item corresponding to the first match it
finds. If a match doesn’t exist, then XLOOKUP can return the closest (approximate) match.

Syntax:

=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode],

[search_mode])

Example:

=XLOOKUP("John", A2:A10, B2:B10, "Not Found")

Explanation: Searches for "John" in the range A2:A10 and returns the corresponding value from B2:B10. If "John" is
not found, it returns "Not Found".

17. How do you remove spaces in Excel using a formula?

Answer: Use the TRIM function to remove extra spaces from text.

Syntax:

=TRIM(text)

Example:

=TRIM(A1)

Explanation: Removes all leading and trailing spaces, and extra spaces between words in A1.

18. How do you calculate the average of a range that meets specific criteria using
AVERAGEIF?

Answer: The AVERAGEIF function calculates the average of cells that meet a specified condition.

Syntax:

=AVERAGEIF(range, criteria, [average_range])

Example:

=AVERAGEIF(A1:A10, ">10", B1:B10)

Explanation: Calculates the average of values in B1:B10 where the corresponding values in A1:A10 are greater
than 10.

19. What is the use of the SUMPRODUCT function?

Answer: The SUMPRODUCT function multiplies corresponding elements in the given arrays and then sums the
products.

Syntax:
=SUMPRODUCT(array1, [array2], [array3], ...)

Example:

=SUMPRODUCT(A1:A10, B1:B10)

Explanation: Multiplies each value in A1:A10 by the corresponding value in B1:B10 and then sums the results.

20. How do you use the TEXT function to format numbers?

Answer: The TEXT function formats a number and converts it to text according to a specified format.

Syntax:

=TEXT(value, format_text)

Example:

=TEXT(A1, "0.00")

Explanation: Converts the number in A1 to text with two decimal places.

21. How do you protect a worksheet?

Answer:

1. Go to Review > Protect Sheet.

2. Choose the actions you want to allow and set a password.

22. How do you use the CHOOSE function?

Answer: The CHOOSE function returns a value from a list of values based on an index number.

Syntax:

=CHOOSE(index_num, value1, [value2], ...)

Example:
=CHOOSE(2, "Red", "Blue", "Green")

Explanation: Returns "Blue" because it is the second item in the list.

23. How do you create a dynamic named range?

Answer:

1. Go to Formulas > Define Name.

2. Enter a name and use a formula like =OFFSET(Sheet1!$A$1, 0, 0, COUNTA(Sheet1!$A:$A), 1) to
define the range.

24. How do you perform a SUMIFS calculation?

Answer: The SUMIFS function adds cells based on multiple criteria.

Syntax:

=SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)

Example:

=SUMIFS(B1:B10, A1:A10, ">10", C1:C10, "<20")

Explanation: Sums the values in B1:B10 where A1:A10 is greater than 10 and C1:C10 is less than 20.

25. How do you use the INDIRECT function?

Answer: The INDIRECT function returns the reference specified by a text string.

Syntax:

=INDIRECT(ref_text, [a1])

Example:

=INDIRECT("A1")

Explanation: Returns the value in cell A1.

These are some of the most common Excel interview questions. Practicing these concepts and functions will help you
become proficient in Excel and perform well in interviews. If you need more examples or further explanations on any
topic, feel free to ask!

Beginner Level Excel Interview Questions

We will start with a set of Excel interview questions aimed at the beginners.

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 Meanwhile, there is no change in

copying a formula from one cell to another cell Absolute cell referencing when a
with respect to the destination. cells’ address formula is copied, 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 If you don’t want a change in the

formula. 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.

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

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

Manually typing out a formula every time you need

to perform a calculation, consumes more time. However, performing
calculations becomes more
Ex: = A1+A2+A3 comfortable and faster 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.
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

VLOOKUP lets the user look for a value in the Meanwhile, the LOOKUP function
left-most column of a table. It then returns the enables the user to look for data
value in a left-to-right way. in a row/column. It returns the
value in 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:

1. Compact Form
2. Outline Form
3. 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.

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.

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.

1. To create a slicer, go to the Insert tab, and select Slicer present under Filter.
2. 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?

1. Create the pivot table to show the total cases by country and continent.
2. 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 Meanwhile, subroutines don’t

the task it is performing. return the value of the task it is
performing.
 They are called by a variable. They can be recalled from
anywhere in the program, in
multiple types.

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

formulas. in spreadsheets as formulas.

Functions are used to carry out repetitive tasks, Users are required to insert a
and it, in turn, returns a value. 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
 ThisWorkbook indicates the name of the workbook As the name suggests,
where the code is running from. ActiveWorkbook is the workbook
that is presently active from the
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.
So, those were the 50 Excel interview questions that can help you crack your Excel interviews and help you
in bagging your dream job.

Conclusion

Now that you know the various Excel interview questions that can be asked in an interview, you can
prepare by referring to the given answers for each of these Excel interview questions. Here, we had a look
at a plethora of Excel interview questions based on different levels of difficulty. Practicing Excel regularly
and going through these Excel interview questions will keep you prepared for any question that is thrown
at you.