Excel Interview Questions
Excel is spreadsheet application software developed by Microsoft. it is part of the Microsoft office suite. Excel is used to store, organize, calculate and manipulate data. Excel uses a spreadsheet that is formed by horizontal rows and vertical columns to organize data and perform calculations on it.
The intersection of row and column is called a cell. A cell contains data as a text, numerical value, or formula. Excel is a little tough to learn as compared to other Microsoft software such as MS Word or MS PowerPoint. But if you want a good job then you must be good at excel. Odyssey was the code name given to excel in the early stage of development it was released on September 30, 1985, for the first time.
Top 50+Excel Interview Question Answer
If you want to do a desk job in any government or private office then the first requirement is good command of excel. Excel is used widely in private and government organizations to maintain data and organize data. If you are applying for such a desk job then in an interview you have to face questions related to Excel. In this article, we are going to discuss some questions and answers related to excel which will be helpful to you to crack an interview.
Frequently Asked Interview Questions and Answers related to excel.
Ans: Microsoft Excel is a spreadsheet application software that helps to store, organize, analyze, calculate and manipulate data.
Ans: The intersection of a vertical column and horizontal row is called a cell. The cell is the place where we store data in a spreadsheet.
Ans: 1,048,576 rows
Ans: 16,354 columns
Ans: In Excel 2016 there are 17,179,869,184 cells
Ans: A single workbook can have 255 sheets
Ans: We can reduce the file size in Excel by deleting the empty row and column in your spreadsheet. Its procedure is as follows.
1) First select the last cell in the sheet that contains data. Delete all the rows and columns after this cell till the end to reduce file size.
2) Press shift+space bar then press Ctrl+shit +down arrow to select all empty rows till the end then press Ctrl+- to delete the selected rows.
3) Similarly delete all blank columns press the shift +space bar then press the Ctrl+Shift+Right arrow to select all blank columns till the end. After selecting all empty columns press Ctrl+- to delete the selected column from the sheet.
Ans: The cell address is formed by row number and column alphabet and it is unique for every cell no two cells can have the same cell address.
Ans: Freeze panes are used to lock a specific column or row.
Ans: Yes we can close all open excel files at the same time.
Ans: The latest new features of Excel are as follows:
• Power pivot
Ans: By pressing the Esc key.
Ans: you can minimize the workbook by pressing ‘Ctrl+F9’.
Ans: Advanced filter is used to analyze the list employed with the database function.
Ans: Ctrl+pgDown shortcut key is used to move to the next sheet.
Ans: Ctrl+PgUp shortcut key is used to move to the previous sheet.
Ans: The syntax of SUBSTITUTE function is ‘SUBSTITUTE(text,oldText,newText,[Instance Number]).
Ans: The Syntax of REPLACE function is ‘REPLACE(old Text,startNumber,NumberCharacters,new Text)
Ans: you can check pivot table modification by using the event Pivot table update.
Ans: Visual basic Application and XLM
Ans: The process to remove duplicates from the data set is as follows.
1) Select the Data first
2) Select the Data tab
3) Click on Remove Duplicates.
Ans: Paste Special Dialog b Box and Transpose function are two ways to transpose data set in Excel.
Ans: The different types of Data filters available in excel are Data filter, Text filter, and Number filter.
Ans: Stephen Few
Ans: Slicer in the pivot table is the new feature of Excel available in version 2010. This feature enables the user to filter data while selecting one or more options in the slicer box
Ans: Format Painter is used to copy the format of the cell, text, image, etc, and apply it elsewhere.
Ans: The different type of Data formats available in Excel are as follow.
Ans: Comments are used to write formulas or notes for the cell.
Ans: The steps to add comments in excel are as follows:
1. Right-Click on the cell.
2. Click on the “ review tab”
3. Select “comment”
4. Type your comment in the comment box.
Ans: The different chart available in excel is as follows.
1. Pie chart
2. Bar Chart
3. Scatter Chart
4. Lie chart
Ans: The area present at the top of the application which consists of toolbar and menu items is known as a ribbon. The different types of tabs present on the ribbon include a set of commands to use in excel.
Ans: To protect the sheet with the password you have to follow the following steps:
Step 1: Click on the Review tab under it select protect worksheet
Step 2: Type the password in the password box.
Step 3: After entering the password click on OK and re-enter the password to confirm
Click on ok again.
Ans: The sum function is used to add values present in the selected cell range.
Ans: The different types of report formats available in excel are Report, Compact, and Tabular.
Ans: IF function
Ans: The lookup Function is used when you need to look in a single row or column and find a value from the same position in the second row or column.
Ans: To delete a column we have to press “Ctrl+-“.
Ans: The Sytax of VLookup function is VLOOKUP(lookup_value,table_array,col_index_num,[range_lookup]).
Ans: #REF!, #DIV/0!,#NUM,#N/A, #Name, and #Value are some of the errors displayed in Microsoft excel.
Ans: The order of operations while evaluating formulas in excel is according to the acronym PEMDAS.
P stands for Parenthesis/Brackets
E Stands for Exponentiation
M Stands for Multiplication
D stands for Division
S stands for Subtraction
Ans: The function LEN is used to get the length of the string in Excel.
Ans: Volatile functions are used to recalculate the formula repeatedly after modifications are performed in the worksheet
Ans: TODAY(),NOW(),RAND(), INDIRECT(),OFFSET(),INFO() and CELL() are some volatile formulas
Ans: For find, we have to use the Ctrl+F key, and for replace we have to use Ctrl+H.
Ans: We can open Spellcheck by pressing shortcut key F7.
Ans: The shortcut key to perform auto-sum on rows and columns is ‘ALT=’.
Ans: The shortcut key Ctrl+N is used to open a new workbook.
Ans: The different sections of the pivot table are the Filter area, Column area, Values area,, and Row area.
Ans: In MS Excel average can be found using the AVERAGE function.
Ans: if in MS Excel you repeat some set of activities again and again then you do not need to repeat the same task again you can record the set of actions one-time using a macro feature of MS excel then whenever you need to repeat that task you just have to run its previously recorded micro and that task will be executed.
Ans: The steps of merging the cell in MS Excel are given below.
Step 1: Select the cell you want to merge.
Step 2: Click on the Home tab.
Step 3: Click of Merge and Centre option under the Alignment group.
Ans: Conditional formatting is one of the most useful features of MS Excel when it comes to data analysis. Using this function you can format cell according to some logical condition for exam marks of student are present in the column then you won’t score greater than 100 in red font and below 100 in green font then you can do this using conditional formatting option.
Ans: The TODAY and NOW functions are used to insert the current date and time respectively in MS Excel.
Ans: Shortcut key SHIFT+F2 is used to insert a comment on the selected cell.
Ans: The count function counts the number of cells selected.
Ans: The main difference between formula and function is that formula is user define and function is an inbuilt feature of MS Excel. For instance =B1+B2 is formula.
Ans =SUM(B1:B2) is a function.
Ans: The different Error in MS Excel and their reason are given as follow.
1) #N/A Error: it occurs when a value is not available.
2) #Div/0! Error. When the number is divided by 0.
3) #VALUE! Error: This error occurs when the data type is incorrect in the formula.
4) #REF! Error: This error occurs reference formula is not valid.
5) #NAME ERROR: it is the result of misspelled function
6) #NUM ERROR: When you calculate a significant number then you will encounter this error.
Ans: MAX function is used to select the larger value from the selected range of cells.
Ans: MIN function is used to select the smallest value from the selected range of cells.