MS Excel interview Questions with Answers:-
1. Explain 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 the 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. Describe the Spreadsheet and its Basics.
The 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 a string of texts
5. Specify the order of operations used for evaluating formulas in Excel.
The order of operations in Microsoft Excel is the 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 clickwrap text from the home tab and you can wrap the text within a cell.
7. Define 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 the charts in MS-Excel?
To enable the 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 the SUM function, you can get the total sum of the rows and columns, in an Excel worksheet.
12. Explain a 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 a 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 linking 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 a Pivot table using multiple sources of data?
If the multiple sources are different worksheets, from the same workbook, then it is possible to make a 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 are 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 losing 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.
MS EXCEL Questions Pdf Free Download::
26. What does a lookup function searches in the MS Excel?
The Microsoft Excel LOOKUP function returns a value from a range (one row or one column) or from an array. There are 2 different syntaxes for the LOOKUP function:
*LOOKUP FUNCTION (SYNTAX #1)
-In Syntax #1, the LOOKUP function searches for a value in the lookup_range and returns the value in the result_range that is in the same position.
-The syntax for the Microsoft Excel LOOKUP function is:
LOOKUP( value, lookup_range, [result_range] )
*LOOKUP FUNCTION (SYNTAX #2)
-In Syntax #2, the LOOKUP function searches for the value in the first row or column of the array and returns the corresponding value in the last row or column of the array.
-The syntax for the Microsoft Excel LOOKUP function is:
LOOKUP( value, array )
27. How can we sum up the rows and column number quickly in the excel sheet?
We can sum up the rows and column number quickly in the excel sheet by the Excel SUM Function. The SUM function provides a quick and easy way to carry out this task in an Excel worksheet.The syntax for the SUM function is:=SUM ( Number1, Number2, … Number255 )
28. What is Macro in Excel?
A macro can be defined as the recording of a series of tasks. It’s the simplest form of automation – show a software program the steps you follow to get something done, and the software will follow along. When used right, macros can save you hours by automating simple, repetitive tasks. Marcos in Excel is written in Excel VBA (Visual Basic for Applications). This is a version of Visual Basic (a prominent Microsoft programming language) developed specifically for use in Office-like applications.
29. How can we create an Excel Macro?
To create Excel Macro :
Step-1 Turn on the Developer tab.
Step-2 Right-click anywhere on the ribbon and then click Customize the Ribbon.
Step-3 Under Customize the Ribbon, on the right side of the dialog box, select Main tabs (if necessary).
Step-4 Check the Developer check box. Click OK.
Step-5 On the Developer tab, in the Code group, click Visual Basic.
Step-6 If needed, in the Visual Basic Editor, on the Insert menu, click Module.
Step-7 In the code window of the module, type or copy the macro code that you want to use.
Step-8 To run the macro from the module window, press F5.
30. How can you resize the column and rows?
The width of columns and the height of rows can be adjusted to better fit your data in a few different ways.
Use the format button- The Format button in the cells group under the Home tab can also be used to adjust widths and heights. Simply select the cells that need to be adjusted, then click on the Format button and choose either Row Height or Column Width. Type in your desired number, and the cells will adjust.
Drag the border- Hover your arrow 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 to make room for your changes.
You can make several columns or rows the same width or height. Select your desired columns or rows by clicking on the labels while holding down either the shift key or control key. Then use any of the methods described above, and all of the columns will be made the same width or rows the same height.
31. What is the quick way to return to a specific area of the spreadsheet?
By typing in Name Box you can return to a specific area of the spreadsheet in a quick way.
32. Tell me about the benefit of using the formula in Excel?
Formulas in spreadsheet programs such as Excel are used to perform calculations on values entered and stored in the program. Excel’s main function is to interlink cells, through the use of formulas, so that the formula cell changes when data is inputted into linked cells. The best way to store information in spreadsheets is through the use of formulas. Formulas can range from basic mathematical operations such as addition and subtraction-to complex engineering and statistical calculations.
33. How can you format data in Excel?
To format data in Excel you can:
Use column labels to identify data
Use cell borders to distinguish data
Extend data formats and formulas
Avoid leading or trailing spaces to avoid errors
Use an Excel table format to work with related data
34. What is the function of sum if in Microsoft Excel?
You would use it when you only want to total some values from a list. So say you had values in A1 to A15 and you only wanted to total up the ones that were over 100, you could use a formula like this:
=SUMIF(A1: A15,”>100″)
Say you had the names of people in A1 to A15, and some of the names were there more than once, and you had values in B1 to B15. Then say you want to add values that are beside only one particular name that occurs a few times in the list and that name was John. You could do that with this formula:=SUMIF(A1: A15,” John”, B1: B15)
So anywhere John is found in the cells from A1 to A15, it will total the corresponding values in B1 to B15.
35. How can you combine different chart types into your Excel spreadsheet?
Excel offers a wide range of chart types: Line Charts, Column Charts, Area Charts, Bar Charts, Scatter Charts, and Pie Charts, to name but a few. You can even mix different types on a single chart by assigning different chart types to different series on the chart. These mixtures are called Combination Charts, and Excel provides a small number of these on the Custom Types tab of the Chart Type dialog box. To combine different charts:
Step-1 Enter or copy/paste your data into an Excel worksheet.
Step-2 Select the data that you want to be included in the chart. Open the Insert tab on the Excel ribbon, and click on the arrow under Column in the Charts section to expand the selection panel. Choose the column chart type that you want to use.
Step-3 Next, right-click on the column representing the data that you want to convert to a line.
Step-4 In the Change Chart Type window that appears, select what type of chart you want to use for this variable. Click OK to continue.
36. How can you change the color and font of the Worksheet tabs?
To change the color of the worksheets:
Step-1 Right-click on the worksheet tab you want to change. Excel displays a Context menu.
Step-2 Choose Tab Color from the Context menu. Excel displays the Format Tab Color dialog box.
Step-3 Select a color to use for the tab.
Step-4 Click on OK.
To change the font :
Step-1 Minimize all open applications by pressing Windows button + M and right-click the desktop.
Step-2 Select Properties, and then select the Appearance tab.
Step-3 Click Advanced (skip this step if using Windows 98).
Step-4 From the Item dropdown list, choose Scrollbar.
Step-5 Change the Size and click OK twice.
37. How to protect the Macros you create so people cannot see or alter them?
To protect the Macros:
In the Visual Basic Editor, go to Tools > VBAProject properties, lock the project for viewing, and enter a password.
38. How to perform lock operation in a cell?
To lock the cells:
Step-1 Select all cells.
Step-2 Right-click, and then click Format Cells.
Step-3 On the Protection tab, uncheck the Locked check box and click OK.
Step-4 Right-click cell(you want to lock), and then click Format Cells.
Step-5 On the Protection tab, check the Locked check box and click Ok. Now your cell will be is locked.
39. How can convert existing Excel worksheet data and charts to HTML document?
You can convert existing Excel worksheet data and charts to HTML document by using the Internet Assistant Wizard. With the Internet Assistant Wizard, you can leverage existing Microsoft Excel spreadsheet data to create and distribute documents that can be viewed online with popular browsers. By clicking a series of buttons in the wizard, you can convert spreadsheet data to HTML format either as a separate Web document or into an existing Web document.
40. How are array formulas useful?
Array formulas may be Excel’s most powerful feature for summarizing data. You can use array formulas to do complex tasks, such as:
Count the number of characters that are contained in a range of cells.
Sum only numbers that meet certain conditions, such as the lowest values in a range of numbers that fall between an upper and lower boundary.
Sum every nth value in a range of values.
Counting Values Based on Multiple Criteria.
Multiple Sums Simultaneously
Counting Unique Values
41. What is the difference between SUBSTITUTE and REPLACE in Excel?
In simple words, the Replace function replaces part of a text string, based on the number of characters you specify, with a different text string. The Substitute function substitutes new text for old text in a text string. You use Substitute when you want to replace specific text in a text string and you use Replace when you want to replace any text that occurs in a specific location in a text string.
42. What is the difference between COUNT and COUNTA in Excel?
The COUNT function is generally used to count a range of cells containing numbers or dates excluding blanks.COUNTA, on the other hand, will count everything numbers, dates, text or a range containing a mixture of these items, but does not count blank cells. COUNTA stands for the count all. Although you can use COUNTA to generate the number of clients or employees in a list, it counts each entry, not unique entries. To count unique entries you would have to use the SUM function together with the COUNTIF function.
43. How do I find a feature that I used in Excel 2003 or Excel 2007?
Microsoft has developed an interactive referencing guide to help you find your favorite Excel 2003 or Excel 2007 commands in Excel 2010. With this guide, you can point your mouse to an Excel 2003 or Excel 2007 menu or button and see where that feature is now located. You can also use the HELP feature to find out how to do a specific task in Excel 2010.
44. How can you protect formulas for being deleted or changed?
To protect formulas :
Step-1 Select the whole worksheet with Ctrl + A, and right click, choose Format Cells from the context menu.
Step-2 And a Format Cells dialog box will pop out. Click Protection, and unchecked the Locked option. Click OK. The whole worksheet has been blocked.
Step-3 Then click Home > Find & Select > Go To Special, and a Go To Special dialog box will appear. Check Formulas from Select option, and then click OK.
Step-4 All of the cells which contain formulas will be selected.
Step-5 Then go to lock the select cells. Right click on the selected cells, and choose Format Cells from the context menu, and a Format Cells dialog box will display. Click Protection, check the Locked checkbox.
Step-6 And then click Review > Protect Sheet, and a Protect Sheet dialog box will pop out, you can enter the password in the Password to unprotect sheet box.
45. The process of identifying specific rows and columns so that certain columns and rows are always visible on the screen is called?
The answer is Freezing panes. When you freeze panes, you keep specific rows or columns visible when you scroll in the worksheet. For example, you might want to keep row and column labels visible as you scroll. A solid line indicates that row 1 is frozen to keep column labels in place when you scroll.
46. How to use VLook up?
VLOOKUP is a great tool for pulling data from tables, but it has a handicap: it can only work with one criterion for matching information. If there are multiple rows in your sheet with the same information, you’ll only get the first one. If you need to use two or more conditions to match a specific piece of data, you’re out of luck. Fortunately, Excel has a pair of functions called INDEX and MATCH that can help produce the same results as VLOOKUP with multiple criteria.
VLOOKUP() Function Syntax-The VLOOKUP() function has the following syntax:
=VLOOKUP(lookup_value, lookup_table_range, column, exact)
VLOOKUP() works by taking the value you are looking up (lookup_value) and looking for it in the first column of the table you are searching in (lookup_table_range).
Once the function finds the matching value in the lookup_table_range, it then reads across that row the table to the column you chose and returns the value it finds there.
47. How to apply the same formatting and data to every sheet in a workbook in excel?
To apply the same formatting to every sheet in a workbook click CTRL+ click the tabs of the sheets that you want to group together, and the grouped tabs will all turn white.
(Normally, only the current sheet has a white tab, while the others are gray.) While sheets are grouped, anything you enter in one sheet also gets entered into the others. After formatting grouped sheets, and perhaps entering the same headings in each, remember to click on the tabs to ungroup them, so that you don’t accidentally insert or remove data in multiple sheets when you insert or remove data in one.
48. Is there any way to create shortcuts to Excel functions?
Yes. Just above the HOME button is the QUICK ACCESS TOOLBAR. You can customize this toolbar to display the shortcuts that you most frequently use from the list provided.
If you click on the small down arrow on the edge of the toolbar, a list of the most common shortcuts is displayed. You can choose shortcuts from this list or if you want a more in-depth selection, click on the MORE COMMANDS option. You can then add more shortcuts from the window that pops up.
49. Is cell reference is useful in the calculation? If yes, then how?
Yes, cell reference is very useful in the calculation. Most of the time in Excel, you will be using a cell’s address in the formula. This is called using a cell reference. The advantage of using cell references is that you can change a value in a referenced cell and the formula will automatically recalculate. Using cell references in your formulas
will make sure the values in your formulas are accurate.
50. What is the role of AND function in excel?
One common use for the AND function is to expand the usefulness of other functions that perform logical tests. For example, the IF function performs a logical test and then returns one value if the test evaluates to TRUE and another value if the test evaluates to FALSE. By using the AND function as the logical_test argument of the IF function, you can test many different conditions instead of just one.
The AND function syntax has the following arguments:
*logical1 Required. The first condition that you want to test that can evaluate to either TRUE or FALSE.
*logical2 Optional. Additional conditions that you want to test that can evaluate to either TRUE or FALSE, up to a maximum of 255 conditions.