Important Excel Interview Questions (2022)
Introduction
Job interviews that require some kind of data tracking or basic calculations are even more difficult, as MS-Excel Knowledge would be required. In the industry, the use of Excel has increased, and people manage their data in Excel to analyze market trends.
So here are some frequently asked Excel interview questions for data analysts and Excel Interview Questions that the interviewers frequently ask:
- Excel Basic Interview Questions
- Middle-level Ms Excel Interview Questions
- Advanced Excel Interview Questions
1. EXCEL BASIC INTERVIEW QUESTIONS
Ques 1: What is Microsoft Excel?
Microsoft Excel is a spreadsheet or program for a computer that allows data to be stored in a table format. Microsoft built Excel, which can be used on different operating systems such as Windows, macOS, IOS, and Android.
Ques 2: What are cells?
Thousands of rectangles, which are called cells, are composed of each worksheet. The intersection of a row and a column is a cell.
Ques 3: Explain the Spreadsheet and its Fundamentals.
It is possible to equate a spreadsheet to a paper ledger sheet. It consists of rows and columns, called cells, and their intersections.
Ques 4: How many Excel data formats are available? Name any of those.
There are eleven data formats in Microsoft Excel for Data Storage:
- Number-Store information as a number
- Currency-Stores data in currency type
Ques 5: How do you use Excel to add cells, rows, or columns?
If you want to add a cell, row, or column in Excel, right-click the cell that you want to add, and then from the cell menu, pick Insert. The Insert menu allows you to add a cell, column, or row and move the cells affected by the additional cell to the right or the bottom.
Ques 6: What’s in Excel for a cell address?
A cell address is used to identify a specific cell on a worksheet. A combination of the corresponding letter of the column and a row number denotes it.
Ques 7: Can you add annotations to a cell?
Yeah, there can be added comments. Select the cell, right-click it and then select the New Comment option to add comments to the cell. These comments will be available for all those who have access to the Excel sheet.
Ques 8: What is the shortcut to the Microsoft Excel 2013 data filter?
The shortcut key to place the filter in the data is Ans Ctrl Shift L.
Ques 9: What do you mean in MS Excel by ‘Relative cell referencing’ and ‘Absolute cell referencing?
When copying a formula from one cell to another cell about the destination, there is a shift in relative referencing. Cells’ Address Address. Meanwhile, when a formula is copied, there is no change in Absolute cell referencing, irrespective of the destination of the cell. By design, this type of referencing exists.
Ques 10: What is and where does the Ribbon appear?
Basically, the Ribbon is the main Excel interface and appears at the top of the Excel window. It enables users to directly access several of the most significant commands. It is made up of several tabs, such as Register, Home, Display, Insert, etc.
Ques 11: How will a cell be formatted? What options are there?
You can format a cell by using the cell formatting options. There are 6 formatting options for cells:
- Number
- Alignment
- Font
- Border
- Fill
- Protection
Ques 12: How many Excel report formats are open, and what are their names of them?
We have three available formats in Excel:
- Compact
- Report
- Tabular
Ques 13: How is it possible to wrap text within a cell?
You need to pick the text that you want to wrap, and then from the home tab, press Wrap Text, and you can wrap the text within a cell.
Ques 14: Can you secure your Excel workbooks?
Yes. Excel offers three options for this:
- You should set passwords to open workbooks,
- You can protect sheets from adding, removing, hiding, or unhidden sheets.
- Protecting from the alteration of window sizes or positions
Ques 15: What is the difference between MS Excel functions and formulas?
The formula is a statement to be measured which is written by the user (user-created). Values, cell references, fixed names, and functions can be found in a formula. Functions are built-in MS Excel codes, such as IF, COUNT, Number, AVERAGE, and so on.
Ques 16: What does the red triangle indicate in the cell’s upper right corner?
In the top right corner of a cell, the red triangle shows that there is a statement connected to the same cell. It will reveal the comment if you place your cursor on it.
2. MIDDLE-LEVEL MS EXCEL INTERVIEW QUESTIONS
Ques 17: What are the MS Excel charts?
To allow the graphical representation of the data in Excel, charts are used. By selecting a choice from the Insert tab, a user can use any chart type, including column, bar, line, pie, scatter, etc.
Ques 18: In MS-Excel, Describe the Macro.
To iterate over a set of tasks, macros are used. For their customized repetitive functions and instructions, users can build macros. Macros can, depending on the user, be either written or registered.
Ques 19: How do you apply a single format to all the sheets in a workbook that are present?
Follow the steps given to apply the same format to all the sheets of a workbook:
- Right-click on any sheet in that workbook that exists
- Then, click on the option to Select All Sheets
- Format any of the sheets; you can see that all the other sheets have been formatted.
Ques 20: What is a Cell Relative Address?
The Relative Cell Address in Microsoft Excel is a form of cell reference that is changed and replaced when the AutoFill function is being used or while being copied.
Ques 21: When analyzing formulas, note the order of operations used in Excel.
The order of operations is referred to as PEMDAS in Excel. The order of precedence is shown below when running an Excel operation.
- Parentheses
- Exponentiation
- Division/Multiplication
- Addition
- Subtraction
Ques 22: What is an Excel Drop Down List?
In the Source field, go to Data in the Ribbon => Select Data Validation => Select List from the Allow drop-down => Add the values that you want to add to the list.
Ques 23: What’s the advantage of using the Excel sheet formula?
Not only does calculating numbers in the Excel sheet allow you to provide the final total of the number, but it also calculates the number replaced by another number or digit automatically.
Ques 24: If you don’t want to change the addresses of the cells as they are copied, what do you do?
You must make use of Absolute Cell Addresses if you do not want Excel to change addresses when you copy formulas. The row and column addresses are unchanged when you use Absolute Cell References and remain the same.
Ques 25: What is the use of the box with the name?
The name box is located in the left-most corner of the Excel document. Typically, we use the name box to search the cell related to the active cell, but it also has many other applications.
Ques 26: How will you write the following formula? – Multiply by 10 the value in cell A1, add 5 to the result, and divide by 2.
We have to follow the PEMDAS Precedent to write a formula for the above-stated query. ((A1*10) 5)/2 is the right answer.
Ques 27: Can multiple data sources be used to render Pivot Tables?
If multiple sources are separate worksheets from the same workbook, then you can create a Pivot table by using these multiple data sources.
Ques 28: Explain the pivot tables and their implementations.
A pivot table is a tool that enables large data to be easily summarized. It automatically performs the type, count, sum, or average of the data stored in the spreadsheet, resulting in another spreadsheet.
Ques 29: How are you going to build Named Ranges?
To build named ranges, follow the steps provided:
- Choose the region to which you want to give a name
- Pick Formulas from the Ribbon
- Click Define Name from the category of Established Names
- Send any of your chosen names
Ques 30: What does VLOOKUP do?
To locate the data in a huge spreadsheet, VLOOKUP is used by searching for the value in another worksheet. We should have common values in both data to use the lookup function.
Ques 31: In Excel, how do you build a hyperlink?
Navigation between worksheets and files/websites uses hyperlinks. The shortcut used is Ctrl K to construct a hyperlink. The box for ‘Insert Hyperlink’ appears. Enter the address and the text that should be shown.
Ques 32: What are the benefits of using the Excel spreadsheet formula?
The formula makes it easy to measure the numbers in an Excel sheet. It also automatically calculates the number that another number or digit has replaced. It is used to promote complex calculations.
Ques 33: How can you have a Dynamic range in the “Data Source” of Pivot Tables?
First, create a named range using the offset function and base the pivot table using the named range generated in the first step to provide the dynamic range in the Data Source of Pivot Tables.
Ques 34: In Excel, how do you build dropdown lists?
Follow the steps given to build dropdown lists:
- Click on the Data tab in the ribbon section.
- Then, click the Data Validation button in the Data Tools group.
- Navigate to Configurations>Allow>List
- Select an array for the source list
3. ADVANCED EXCEL INTERVIEW QUESTIONS
Ques 35: What is the purpose of Nested IF?
We can use the IF function 7 times, which is called the Nested IF function, because we have several conditions to satisfy.
Ques 36: How is VLOOKUP different from the feature of LOOKUP?
VLOOKUP lets the user look for a value in the left-most column of a table. Then the value is returned in a left-to-right fashion. Compared to the LOOKUP feature, it is not very easy to use. The LOOKUP feature, meanwhile, allows the user to search for data in a row or column.
Ques 37: How can you disable automatic sorting in pivot tables?
Go To > More Sort Options > Right Click’ Pivot tables’> Select ‘sort menu’> select ‘More Options’> deselect ‘Sort automatically.’
Ques 38: Can I see the specifics of the results shown in the pivot table?
Yeah. Double-click on the value to see the specifics of each outcome, and you can see that a new sheet has been generated with a new table containing details of the factors that have contributed to that particular result.
Ques 39: How will advanced filters be used?
We can extract a unique list of items using Advanced Filters or a particular item from various worksheets. We may assume that the Advanced Filter is an advanced Auto Filter variant.
Ques 40: What’s Data Validating?
Data Validation limits the type of values that can be inserted into a single cell or a set of cells by a consumer.
Ques 41: In Excel, what is the operator precedence of formulas?
Formulas are executed according to BODMAS rules in Excel. That means that brackets are first executed (if they are present) in any formula, followed by multiplication, division, etc.
Ques 42: What is and how to use conditional formatting?
Conditional formatting is a method that allows us based on a few conditions, to highlight cells or ranges, and that formatting is often based on values or text that can be automatically updated.
Ques 43: How do you prevent loosening the column width while refreshing the pivot table?
In a pivot table, format loss can be prevented simply by adjusting the options of the pivot table. Switch on “Pivot Table Options” under the “Enable Preserve Formatting” and disable the “AutoFormat” function.
Ques 44: How can you measure an Excel percentage?
Select the cell destination cell to display the percentage
- Then, type a “=” sign
- Type in A1/ A2, then hit the Enter key
- Click on the Home tab, select the % symbol from the numbers group
Ques 45: What is the difference between a function and a subroutine in VBA?
It is the duty of a function to return the value of the mission it performs. Subroutines, meanwhile, do not return the importance of the assignment they perform. Functions are used as formulas, as they are in spreadsheets. Subroutines are not used as formulas directly inside spreadsheets.
Ques 46: Can we build Excel feature shortcuts?
Yeah. It is possible to configure the ‘Easy Access Toolbar’ above the home button to display the most commonly used shortcuts.
Ques 47: How do you apply the same formatting in MS Excel to every sheet in a workbook?
Right-click’ Tab Worksheet’> Choose All Sheets Pick’. Now the whole workbook will be added to every editing completed. To submit to a specific group of sheets, choose only those sheets that need to be formatted.
Ques 48: What are alignments for left, right, fill, and distributed?
- The left/right alignment aligns much of the cell with text to the left and right.
- Fill the cell with the same text repetitively, as the name suggests.
- Distributed, spread the text over the cell width.
Ques 49: What’s the distinction in VBA between ThisWorkbook and ActiveWorkbook?
ThisWorkbook provides the name of the workbook from which the code is running. ActiveWorkbook is, as the name implies, the workbook that is actually active in the numerous open workbooks.
Ques 50: What is the easy way to return to a specific worksheet area?
Using the name box is a fast way to return to a particular section of the worksheet. To return to a particular area of a worksheet, you can type the cell address or name of the range in the name box.
Conclusion
Excel Interview Questions provide the chance to prove you are highly familiar with the different Excel applications used in businesses worldwide. Because Excel’s features and advantages are appealing, this feature is commonly used across industries. Thus, Excel interview questions are part of several interviews for positions from IT to accounting.
The above-mentioned Excel Interview Questions will surely help you prepare for your next interview.
If you are interested in making it big in the world of data and evolving as a Future Leader, you may consider our Integrated Program in Business Analytics, a 10-month online program in collaboration with IIM Indore!
