Excel Interview Questions

Excel is one of the key skills which everyone should possess and this is the only reason that you may face some tricky excel questions during your job interview. Here we have listed all the common interview questions that are likely to be asked by the employer in different job interview.

Q.1 What is the process of summing the rows and column numbers quickly in the Excel sheet?
For this, we can use the SUM function. This will provide the total sum of the rows and columns in an Excel worksheet.
Report This Question
Q.2 Describe some of the functions in Excel.

Some of the functions in Excel for manipulating the data are:

1. Math and Financial Functions SQRT, DEGREE, RAND(), GCD

2. Logical Functions IF, AND, FALSE, TRUE

3. Date and Time functions NOW(), DATEVALUE(), WEEKDAY(NOW())

4. Index Match VLOOKUP and INDEX MATCH

5. Pivot tables

Report This Question
Q.3 Do you know about the red triangle present at the top right of a cell?
Yes, the red triangle specifies that some comment is linked with the cell. Take the cursor over it for reading the full comment.
Report This Question
Q.4 Tell the shortcut key for the data filter?
The shortcut key for placing the filter in the data is, Ctrl+Shift+L.
Report This Question
Q.5 How to add comments to a cell?
For adding comments to a cell, select the cell. Then, right-click on it and select the New Comment option. These comments will be visible to all those people with access to the Excel sheet.
Report This Question
Q.6 How to protect workbooks in Excel?
Excel provides three options for protecting workbooks: Firstly, you can set passwords for opening Workbooks Secondly, you can protect sheets from being added, deleted, hidden, or unhidden Thirdly, protecting window sizes or positions from being changed
Report This Question
Q.7 What is the process of applying a single format to all the sheets available in a workbook?

For applying the same format to all the sheets of a workbook:

  • Firstly, right-click on any sheet present in that workbook
  • Secondly, click on the Select All Sheets option Thirdly, format any of the sheets.
  • And then the format will be applied to all the other sheets.
Report This Question
Q.8 Define relative cell addresses.
Whenever you copy formulas in Excel, the addresses of the reference cells get changed automatically in order for matching the position where the formula is copied. This is processed by a system known as Relative Cell Addresses.
Report This Question
Q.9 What if I want to change a column letter or the row number but not both?
For this, you must make use of Mixed Cell Addresses. In this, either the row or column is relative while the other is absolute.
Report This Question
Q.10 Describe the steps for protecting cells of a sheet from being copied?
Firstly, select the cells that you want to protect. Secondly, from the home tab, open the Font window. Thirdly, select Protection from the Protection pane. Then check the Hidden box. After that, click on the Review tab present in the Ribbon. Then, select Protect sheet option. Lastly, specify a password.
Report This Question
Q.11 What is the process of creating Named Ranges?
Firstly, select the area to which you plan to give a name. Secondly, select Formulas from Ribbon. Then, click on Define Name from the Defined Names group Lastly, provide any name of your choice.
Report This Question
Q.12 I want to create dropdown lists in Excel. Can you tell me how I can do this?

For creating dropdown lists:

  • Firstly, click on the Data tab present in the ribbon.
  • Secondly, from the Data Tools group, right-click on Data Validation
  • Thirdly, go to the setting>Allow>List
  • Lastly, select the source list array
Report This Question
Q.13 Explain some of the features of Excel Pivot Tables.
  • Firstly, the pivot table provides the display of the exact data you have to analyze.
  • Also, it helps in providing various angles to view the data.
  • Then, using this you can focus on important details.
  • Next, they can identify different patterns, relationships, data trends, etc.
  • Lastly, they can create instant data with accurate reports.
Report This Question
Q.14 What are the things to remember while creating Pivot Tables?
  • Firstly, before creating a Pivot table, prepare the data in a tabular format.
  • Then, arrange the data into rows and columns
  • Thirdly, the first row should contain a unique heading for each of the columns.
  • Next, the columns must have only one type of data. And, rows must have data for a single recording only. There should be no blank rows.
  • Lastly, the columns should not be completely blank and the data for creating Pivot table should be separate from other data present in the sheet.
Report This Question
Q.15 What if we check the defer Layout Update option present in the PivotTable Fields window?
After checking on this option, we will not be able to see any dynamic changes while interchanging the table fields. By default, this option is unchecked. However, all the changes will occur only after you click on the Update button when you check this box.
Report This Question
Q.16 Can we see the details of the results displayed in a pivot table?
Yes, we can see the results by double-clicking on the value. After this, a new sheet will be created with a new table having details about the factors that have led to that particular result.
Report This Question
Q.17 Describe the process of filtering data using PivotTables?
Excel PivotTables enables you to filter data as per your requirements. For doing this, place the field depending on which you wish to filter out the data. After that, from the pivot table, open the dropdown list present for the field you have placed in the Filter area. Lastly, select the section of your choice.
Report This Question
Q.18 On which rule do formulas in Excel operates?
Formulas in Excel are implemented according to the BODMAS rules. This stands for Brackets Order Division Multiplication Addition and Subtraction. That means, in every formula, brackets are executed first followed by multiplication, division, etc.
Report This Question
Q.19 Define the COUNT functions available in Excel?

1. COUNT 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, …)

2. COUNTA This counts the number of cells in a given range that are not empty. SYNTAX: COUNT(value1, [value2], …)

3. COUNTIF This function counts the number of cells that comply with a given condition. SYNTAX: COUNTIF(range, criteria)

4. COUNTBLANK This counts all the blank cells in a given range. SYNTAX: COUNTBLANK(range)

5. COUNTIFS This is a special function that enables you to specify a set of conditions in order to count them. SYNTAX: COUNTIFS(criteria_range1,range1,[criteria_range2, criteria2], …)

Report This Question
Q.20 What is the formula for calculating the percentage in Excel?

Percentages refer to ratios that are calculated as a fraction of 100. The percentage can be defined as:

Percentage = (Part/ Whole) x 100

Report This Question
Q.21 What is the formula for calculating compound interest in Excel?
For calculating compound interest in Excel, you can use the FV function. Where, FV returns the future value of an investment based on the periodic, constant interest rate and payments. Formula: FV(rate, nper, pmt, pv, type)
Report This Question
Q.22 Explain the working of the VLOOKUP function.
The VLOOKUP function, in Excel, lookup a value and starts to look for the same in the leftmost column. After finding the first occurrence of the given lookup value, VLOOKUP begins to move right. That is in the row where the value was found. Then, it moves until the column number is defined by the user and returns the desired value. This function is used for matching the exact and approximate lookup values. However, in this, the default match is an approximate match. Formula: VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]) In this, lookup_value provides the value to be looked out for table_index is the range from where the data is to be taken col_index_num defines the column from which you want to fetch the value range_lookup is a logical value that is TRUE or FALSE
Report This Question
Q.23 What is the process for performing a horizontal lookup in Excel?
For performing a horizontal lookup, the HLOOKUP function is used. Formula: HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup]) In this, lookup_value provides the value to be looked out for table_index is the range from where the data is to be taken row_index_num defines the row from which you want to fetch the value range_lookup is a logical value i.e TRUE or FALSE
Report This Question
Q.24 Define What-If Analysis.
What-If Analysis can be considered as a technique for performing changes to one or more formulas present in the cells in order for seeing how it affects the result of those formulas in the worksheet. There are three types of What-If Analysis tools in excel: Scenarios Data Tables Goal Seek
Report This Question
Q.25 I want to change the size of rows and columns. Explain the methods.
The width of columns and height of rows can be modified in a few different ways: Firstly, using the format button. The Format button in the cells group under the Home tab can also be used for adjusting widths and heights. Just select the cells that need to be adjusted, then click on the Format button and select either Row Height or Column Width. And, type in your number, and the cells will adjust. Secondly, dragging the border. Take the cursor over the border between the column or row labels. Then click and drag the border to widen or narrow the size of each cell. The columns to the right or rows below will adjust for making room for your changes.
Report This Question
Q.26 I want to copy the cells but do not want the address to change. How will I do it?
For this, we can use Absolute Cell Addresses. This will command excel to no to change addresses when you copy formulas. However, the row and column addresses are not changed when you use Absolute Cell References and remain the same.
Report This Question
Q.27 What is the process of creating a hyperlink?
For navigating between worksheets and files/websites we use hyperlinks. For creating a hyperlink, use the shortcut, Ctrl+K. Then, the box for ‘Insert Hyperlink’ appears. After that, enter the address and the text that should be shown.
Report This Question
Q.28 What are the benefits of the spreadsheet formula?
The formula makes it easy for measuring the numbers in an Excel sheet. This automatically calculates the number that has been replaced by another number or digit. Moreover, it is used for promoting complex calculations.
Report This Question
Q.29 Define 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]) On the other hand, the REPLACE function swaps part of the text string with another set of text. Syntax: REPLACE(oldText, startNumber, NumberCharacters, newText)
Report This Question
Q.30 Define AND function in Excel?
AND function is a type of logical function that returns the output as true or false. However, the AND function will examine at least one mathematical expression located in another cell in the spreadsheet. So, if you want to see the output of more than one cell in a single cell, then use AND function.
Report This Question
Q.31 How to stop automatic sorting in Pivot Table?
  • This can be done by navigating to the More Sort Options.
  • Then, right-click on Pivot Tables
  •  After that, select Sort Menu and begin with More Options.
  • Now, we have to uncheck the Sort automatically every time the report is updated.
Report This Question
Q.32 What type of file formats are used for saving a Microsoft Excel file?
Some of the file formats for saving Microsoft Excel files are: csv, xlsm, xls, xla, xlb.
Report This Question
Q.33 I want to see the current date in Microsoft Excel. Can you tell the formula?
For getting the current date use, = TODAY () function.
Report This Question
Q.34 Explain the types of text alignments in Excel.
Firstly, Left /Right alignment. This aligns the text to the left and right of most of the cells. Secondly, Fill. This fills the cell with the same text repetitively. Lastly, Distributed. This spreads the text across the width of the cell.
Report This Question
Q.35 What is the purpose of Microsoft Excel?
Microsoft Excel refers to an electronic spreadsheet application that allows users for storing, organizing, calculating, and manipulating data with formulas using a spreadsheet system divided into rows and columns. This provides the flexibility for using an external database to do analysis and make reports, by saving time.
Report This Question
Q.36 What do you understand about ribbon?
Ribbon refers to the topmost area of the application that consists of menu items and toolbars in MS-Excel. This can be shown/hidden using the shortcut, CTRL+F1. Further, the ribbon runs on the top of the application and is the replacement for the toolbars and menus. And, they contain various tabs on the top, and each tab has its own group of commands.
Report This Question
Q.37 How will you add two cells (A1 and A2) together?
=A1 + A2
Report This Question
Q.38 What is Spreadsheet?
The spreadsheet can be compared to a paper ledger sheet that contains rows and columns and their intersection called cells. This is used for arranging formulas, functions and sort numbers and data in rows and columns. Moreover, they can be operated mathematically and the number of worksheets in a workbook can be more than one.
Report This Question
Q.39 What are the advantage of using a spreadsheet?

1. calculations can be done automatically.

2. changing data automatically updates calculations.

3. more flexibility

Report This Question
Q.40 Name the types of data formats in Excel.

Some of the data formats in excel are:

  • The number for storing data as a number Currency for storing data in the form of currency
  • Date in which the data is stored as dates The percentage for storing numbers as a percentage
  • Text Formats for stirring data as a string of texts
Report This Question
Q.41 What is Cell?
The intersection of a row and column is called cell.
Report This Question
Q.42 Why do we use the VLOOKUP function?
VLOOKUP helps in finding the data in a large spreadsheet using the lookup value in another spreadsheet. This is an important function that provides valuable insights and relationships between the data set.
Report This Question
Q.43 What is the default file folder in Excel?
My Documents is the default file folder in Excel.
Report This Question
Q.44 What is the use of the freeze pane in Microsoft Excel?
The freeze pane enables the locking of the column or row headers. This allows you to keep the row and column heading locked while scrolling up and down to the other parts of the worksheet. Moreover, these frames do not scroll and the other areas will not have any effect. But, you cannot freeze columns and rows if your worksheet is protected. For freezing a pane, select the column header or row header and click on the 'View' tab. After that, select the 'Freeze Panes' option and choose an option according to your requirement.
Report This Question
Q.45 What does the VLOOKUP function do?
The VLOOKUP function  helps find the related records
Report This Question
Q.46 What are pivot tables in Excel?
Pivot tables provide an interactive view of a large data set. This basically summarizes, reorganizes, aggregates, counts, groups, sorts, and computes an extensive data set. Moreover, it extracts important information from complex and vast data sets.
Report This Question
Q.47 Is it possible to code using the VBA language?
Yes, we can code using the VBA language. For example, almost every company requires a new report and the steps for creating a report remained the same. Using a macro, we can manipulate the data, generate a report, and displayed the insights in the chart form.
Report This Question
Q.48 What are charts in Excel?
Charts help in displaying information in an easy-to-digest format. They provide a graphical representation of data. These charts can be of any type such as Bar, Pie, Area, Line, Doughnut, etc.
Report This Question
Q.49 Define conditional formatting in MS Excel.
Conditional formatting helps in applying a format to a single cell or range of cells depending on the specific criteria. This can create trends and patterns in your data to look more visible.
Report This Question
Q.50 What do you understand about absolute cell reference in excel?
Absolute cell reference refers to a locked reference which ensures that the rows and columns will not change on copying the cell. For this, we can add a '$' symbol in front of the row and column number. And, when the cell is $B$2, both rows and columns remain unchanged on copying. But if it is $B2, it means that the column remains unchanged, but the row changes on copying. Similarly, for B$2, the column changes, but the row remains unchanged on copying.
Report This Question
Q.51 Is it possible to link a cell to different files and websites on the internet?
Yes, we can link a cell to different files and websites on the internet using the hyperlink feature of Excel.
Report This Question
Q.52 What is the process for wrapping the text inside a cell?
For this, firstly, select the text you want to wrap, and then click wrap text from the home tab. Then, you can wrap the text within a cell.
Report This Question
Q.53 What are Macro in MS-Excel?
Macros are used for repeating over a group of tasks. Users can create macros for their customized repetitive functions and instructions. Further, they can be either written or recorded depending on the user.
Report This Question
Q.54 Name the macro languages in MS-Excel?
The macro languages in Excel are XLM and VBA (Visual Basic Applications). In earlier versions of Excel, only XLM was used.
Report This Question
Q.55 How to stop someone from copying the cell from your worksheet?
For protecting your worksheet from getting copied, go into the Menu bar. Then click, Review. After that, select, Protect Sheet and then click on Password. You can enter a password for securing your sheet from getting copied by others.
Report This Question
Get industry recognized certification

Get Govt. Certified

Know More
Get Govt. Certified Take Test
 For Support