Top 50 Microsoft Excel Interview Questions

Microsoft Excel Interview Questions

1. What is Microsoft Excel?

It is an electronic spreadsheet application launched by Microsoft in the year 1985. It authorizes the user to store, manage, analyze, and influence data with the use of functions and formulas using the spreadsheet system broken into rows and columns. It is also compatible with other databases making it an all-around and time-saving application.

2. Define cells?

The crossway where a row and column intersect on a worksheet is referred to as a cell. The rectangular spaces in the excel sheet which take in data are referred to as a cell. There are about 10,48,576 rows x 16,384 columns = 17,17,98,69,184 Arab cells in one worksheet.

3. Describe a Spreadsheet?

Spreadsheets also known as Worksheet are a collection of cells that help in data management. One Workbook may comprise several worksheets. Worksheets may be given a name to distinguish when searching for some particular data. These may be visible at the bottom of the sheet. D

4. Define Cell Address?

The denomination obtained when you click a random cell in the worksheet, which denotes the letter of the column number and the number of the row respectively is referred to as a cell address or cell number. E.g.

5. How to add cells to the spreadsheet?

To do so, just right-click on the cell where you wish to add the cell and the following dialog box shall pop up

In this dialog box select the Insert option and the following dialog box shall pop up, in which you can select your preference of adding the cell and click ‘OK’ to confirm your choice.

6. How can a user format cell in MS Excel?

To format any cell, the user can right-click on the cell and would be shown a drop-down menu. Similar to the one shown in the picture below

From the drop-down menu, the user needs to choose the ‘Format Cells’ option and would be displayed a dialog box as shown below

The first formatting option would be the ‘Number’ tab which allows the user to portray numbers in special types like currency, date, time, percentage, fraction, etc.

The second tab is the ‘Alignment’ tab

Which allows the operator to align, text control, and change the direction in which the text was maybe written.

The next tab is the ‘Font’ tab

which permits the user to set the default printing font style and the one that is displayed on the screen.

The next tab the ‘Border’

Permits the user to change or modify the borders of the cell to be colored, changed, or maybe even removed permanently.

The ‘Fill’ tab

Enables the user to choose distinct colors and styles to fill up the cells.

Finally, last but not least the ‘Protection’ tab allows the operator to lock or hide any chosen cell.

7. Describe if a user can add comments to a cell and how?

A user may add comments to a cell by selecting the cell and right-clicking on the cell which brings a drop-down menu and selecting the comments option as shown in the picture below

The comments would be visible to all the people with whom the file may be shared.

8. How can a user add complete rows and columns to a sheet?

The procedure for doing so is similar to right-clicking in the desired location and choosing the insert option, but unlike the cell option, the user may select the bottom two options for inserting complete rows or columns respectively as displayed below in the insert dialog box.

9. Describe what is a Ribbon in MS Excel?

The ribbon is the most important kit in your arsenal of dealing with data in MS Excel and appears at the top of each spreadsheet. The Ribbon permits the user to gain direct access to multiple commands that aid in managing, sorting, and visualizing data in MS Excel. The Ribbon consists of tabs that allow the user to customize the data as per his/her requirement. Here are a few examples of the ribbon with different tabs and their features:-

10. Describe why and how may a user freeze pane in MS Excel?

The feature of freezing panes in MS Excel helps the user to make headings of rows and columns visible even when scrolling deep in an excel sheet either vertically down or horizontally sideways. To freeze panes in MS Excel:-

1. The user needs to select the rows/columns that need to be frozen
2. Click on the arrow next to the ‘Freeze Panes’ option from the ribbon of the “View” tab and

• Choose from the three options, just as showcased in the picture below

11. How can a user ‘Wrap text’ in MS Excel?

To do so the user may choose the ‘Wrap text’ option from the home tab after selecting the cell in which the user wishes to wrap the text as shown in the series below

12. How can the user prevent data from being copied by someone else?

To protect data being copied from the spreadsheet that you have shared with the users, follow these steps:-

1. Go to the ‘Review’ tab on the Excel ribbon
2. Choose ‘Protect Sheet’
3. Choose the criteria that the user of the sheet is allowed to do
4. Enter the password and press ‘OK’, this is how the criteria box looks like

13. Define charts in MS Excel and how to employ them?

Representation of data into graphs or charts is something that MS Excel allows its users to employ with ease. A user may use different styles of chart formats to make complex data look visually simple and understandable to its viewers.

This feature may be accessed by going to the ‘Insert’ tab of the ribbon and choosing the style of the chart the user wishes to display the data. Below is an example of the same

On selecting the type of chart, the user wishes to utilize, click on ‘OK’ and the data will be showcased in a chart format, like the one shown below

14. How can a user sum up numerical values in rows or columns quickly?

This feature may be accessed by the user with the click of a button, on the ‘Home’ ribbon tab in the editing pane, as mentioned in the below pictures

15. How can a user apply a single format to all the sheets in a Workbook?

To apply the same format in all the sheets of a workbook, the user would need to follow the necessary steps:-

1. Right-click on a sheet name
2. Choose the ‘Select all Sheets’ option
3. Make format changes on any one sheet and it would be applied to the rest of the sheets as well, below is a picture showing the option for selecting all the sheets

16. Describe the steps to resize one or multiple columns?

The easiest way to do this is to select the column you wish to resize and drag the mouse horizontally sideways to adjust the size of the column, for multiple columns whilst pressing the ‘CTRL’ button select the column the user wishes to resize and for all the columns click on the diagonal arrow to the left of column A.

The other way is to:-

• Select your columns
• Go on the Home ribbon
• Go to the cells pane
• Choose Format and select the ‘Column width…’ option
• Feed-in the number and the selected columns would be adjusted

17. Describe how a user might merge cells in Excel?

To do so follow these steps:-

• In the home ribbon under the Alignment pane
• once the user has selected the cells that they wish to merge
• Click on the ‘Merge & Center’ button which will display a drop-down menu
• From the drop-down menu, the user may select:-
• Merge & Center
• Merge Across
• Merge Cells
• Unmerge Cells

The option displayed looks like the picture below

18. Mention the steps for highlighting cells with negative values?

This can be done by adhering to the following steps:-

• The user needs to choose the cells they wish to highlight the negative values
• On the ‘Home’ tab, go to the ‘Styles’ pane
• In the ‘Styles’ pane, click on the down arrow in the ‘Conditional Formatting’ slab
• Choose the ‘Highlight Cells Rules’ option
• Select the ‘Less than’ option
• Feed the value as ‘0’ and the color scheme in which the user wishes the discrepancy to be highlighted.

The following would showcase the following result

19. Describe the order of operations used when evaluating formulas in Excel?

The following id the order used to evaluate formulas in Excel:-

• Parenthesis(Brackets)
• Exponentiation(^)
• Multiplication or Division – both have equal precedence and is evaluated on whichever comes first
• Addition or Subtraction – both hold equal precedence and is evaluated depending on whichever comes first

The easiest way to remember this operation is the abbreviation PEMDAS or BEMDAS, which is the initial letter of each operator in the order of their application.

20. Describe the difference between a function and a formula in Excel?

A function is a pre-established operation in MS Excel that can take a specified number of arguments. Whereas Formula is a user-defined expression used to calculate a value.

An operator may fabricate a complex formula that may have multiple functions integrated into it.

For e.g., =A1+A2 is a formula and =SUM(A1:A10) is a function.

21. What may be defined as the top functions of MS Excel?

This question is often put forward to understand the comfort of the applicant with the MS Excel functions. There are around 450+ functions in MS Excel, but there are a few noteworthy functions, which as an operator of MS Excel one should know about and be fluent with the integration and implications of these functions to manipulate data:-

• VLOOKUP
• COUNTIF
• SUMIF
• IFERROR
• INDEX/MATCH
• SUMPRODUCT
• TEXT
• AVERAGE
• LEN/LEFT/RIGHT/MID
• SUM

22. Describe how may a user tackle errors while working with Excel formulas?

There are multiple ways in which a user may tackle errors in MS Excel:-

1. The user may highlight the errors with the help of ‘Conditional Formatting’ in the styles pane of the Home tab, by choosing the ‘ISERROR’ option within the ‘Conditional Formatting’ pane
2. The user may employ the ‘IFERROR’ function to get a specific value in case the formula sends back an error.

• The user may also utilize the ‘ISERROR’ function to extrapolate a ‘TRUE’ or ‘FALSE’ to validate the presence of an error

23. Describe the functions that may be implied to get the current date and time in Excel?

The below-mentioned functions may be employed:-

1. This function returns the current date value without taking any arguments– TODAY()
2. This function returns the current date and time value without taking any arguments – NOW()

Another point for the user to keep in mind that date and time are coded as numbers in Excel, so addition and subtraction may be made to these.

24. Which formula can the user employ to find out the length of a text string in a cell?

The user may use the ‘LEN’ function to investigate the length of the text string in a cell

25. Describe the Pivot Tables?

These are statistical tables that reduce data to it is the bare minimum. The dial down may display fields such as sums, sales, etc. which pivot tables can showcase in a smarter & simpler manner.

Pivot table features are as follows:-

1. A precise showcase of data that the user wishes to analyze
2. Provide numerous perspectives of viewing the data

• Provide the user with attention to crucial facts

1. Comparison of data is imperative
2. Pivot tables are capable of detecting patterns, trends, etc
3. Provides instant fabrication of data

• Precise reports
• Pivot chart’s foundational block

Microsoft Excel Interview Questions

26. Describe the process of providing a dynamic range in “Data Source” of pivot tables?

This can be achieved by providing or creating a ‘Named Range’ using the offset function and use the name to base the pivot table.

27. How can the user make a Pivot table with numerous sources of data?

This is possible only if the multiple sources of data are from different worksheets of the same workbook.

28. Name the event employed to check if any modification was made in the Pivot Table?

To make this inquiry, all that the user needs to do is utilize the ‘PivotTableUpdate’ event in the worksheet comprising the pivot table.

29. Describe the process to how can the user disable automatic sorting in pivot tables?

The user may follow these steps:-

• Got to ‘More Sort Options’
• Right-click on ‘Pivot Tables’
• Choose the ‘Sort Menu’
• Pick ‘More Options’
• Ditch the ‘Sort automatically’ option

30. How can the operator stop the pivot table from losing the column width after refreshing?

The user may stop the loss of format in pivot tables post refreshing by changing the options for the pivot table. To accomplish this, the user under the ‘Pivot Tables Option’ needs to:-

• Switch on the ‘Enable Preserve Formatting’ and
• Switch off the ‘AutoFormat’ option’s

31. How can a user calculate percentage in Excel?

Percentages are the ratios that are calculated as a fraction of 100. In literal terms,

Percentage = (Part/Whole) x 100

In MS-Excel to get a %age value, the user would:-

1. First, need to employ the formula =(Part/Whole) into the necessary fields
2. Then to implicate it as a %age the user needs to right-click on the selected cells and choosing the ‘Format cells…’ option
3. The user then needs to go to the ‘Number’ tab in the dialog box
4. Choose ‘Percentage’ and click on ‘OK’
5. On applying this format, the data will be showcased in the % format.

Below is a pictorial representation of the process involved.

Another way of doing this:-

• After entering the percentile formula
• The user may go on the ‘Home’ ribbon
• In the home ribbon, go to the ‘Number’ pane
• Choose ‘%’ after selecting the cells the user intends on employing the format

32. Can a user calculate Compound Interest in Excel?

Yes, to calculate interest in Excel the user can make use of the ‘FV’ function which has certain arguments that need to be fed in the function to get the desired result.

So, it showcases as

=FV(rate, nper, pmt, [pv], [type])

To find the rate = interest rate/compound years,

nper = Years*compound year

pmt = any value (including zero)

Below is an example of computing compound interest

33. Can the user find averages in excel?

The user may use the =AVERAGE function to get the average for a set of numbers.

Please find the below-displayed example

34. Describe the ‘LOOKUP’ function?

The ‘LOOKUP’ function is generally employed to summon a value from an array of data.

35. Describe the VLOOKUP function in Excel?

It is a function that permits the operator to summon data from a given range. The letter V in VLOOKUP stands for vertical and implies that the data needs to be structured vertically. This function comes in handy when looking for a single piece of data from a pool of data.

36. Describe the functioning of the VLOOKUP function?

This function works by picking up the data displayed in the default argument from the left side of the screen and then moving towards the data showcased in the same field towards the right in the same row where the argument was found to match the remaining arguments fed in the VLOOKUP function. The function showcases the following arguments

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

Where

• lookup_value denotes the value in demand
• table_array denotes the range from where the data needs to be summoned
• col_index_num identifies the column from which the user intends the value to be raised
• [range_lookup] is a plausible TRUE or FALSE value, where TRUE searches for the closest match and FALSE searches for the exact match

Please find the below-mentioned example below showcasing the VLOOKUP feature to lookup the city of the buyer for order ID number 102

37. Describe the ‘What if analysis in Excel?

The ‘What if the analysis is the technique of performing modifications to one or more formulas present in the cells to witness how the alterations to those formulas cause an effect in the rest of the sheet. There are 3 types of ‘What if’ tools in Excel:-

1. Data tables
2. Scenario’s
3. Goal seek

Data Tables and scenarios take a set of inputs to check for potential results. Data Tables can work with just 1 or 2 variables but may accept several different values for every one of those variables. Whereas Scenarios may work with many variables but are limited to a maximum of 32 values.

38. Differentiate between SUBSTITUTE and REPLACE functions in Excel?

The REPLACE function switches part of the text string with another set of text. =REPLACE(old_text, start_num, num_chars, new_text)

The SUBSTITUTE function substitutes one or more instances of old text with the new text in a string.

=SUBSTITUTE(text, old_text, new_text, [instance_num])

39. Differentiate between COUNT, COUNTIF, COUNTA, and COUNTBLANK in Excel?

COUNT is the function in MS Excel utilized to count the cells which hold numeric data minus the blanks in the specified selection.

COUNTIF & COUNTIFS are functions for counting cells that hold numeric data with arguments in the specified selection.

COUNTA is also known as CountAll is a function used to count any cell with numeric data in the sheet and

COUNTBLANK is the function used to count the cells with empty strings or blank cells.

40. Describe the ‘IF’ function in Excel?

The ‘IF’ function is performed to execute a logic test. The function is responsible for checking whether the specified condition is TRUE or FALSE. If the search meets the true criteria the result would be shown accordingly, but if the search does not meet the criteria then the results would not match the arguments.

41. Describe Volatile functions?

These functions are responsible for recalculating the worksheet every time there are changes made to the worksheet. If the user is operating with nominal or little data it would not be bothersome, but if the user is dealing with large amounts of data then these functions may substantially increase the run time of these functions and the load time of the results which have conspired due to the changes made in the worksheet.

Here are a few examples:-

• Highly volatile – TODAY(), RAND(), NOW()
• Almost volatile – OFFSET(), CELL(), INDIRECT(), INFO()

42. How can a user quickly switch between worksheets without the use of the mouse?

The operator may use the ‘CTRL + PAGE DOWN’ command if he/she is on the first sheet and the command ‘CTRL + PAGE UP’ if on the last sheet of the workbook.

43. How can a user determine the day of the week according to the date?

The function which allows users to get information about the day of the week according to the date is the ‘WEEKDAY’ function.

44. Describe the benefits of employing formulas in a worksheet?

Using formulas in excel sheets is not only the best way to compute numbers but because of volatile functions, it recalculates the sheet every time any value which is part of the initial calculation is changed. The use of formulas not only eases the calculation part but enhances the efficiency of the way tasks are handled and completed with the use of MS Excel.

45. How can the operator create shortcuts for Excel functions?

The ‘Quick Access Toolbar’ above the home button can be customized to showcase the most frequently used Excel functions.

46. Which filter may an operator use if he/she wishes to analyze a list using database functions?

The user may use the ‘Advanced Criteria Filter’ in the list or if more than two conditions need to be tested out.

47. How can a user return to a specific area of a worksheet?

The quickest way is to type the cell address in the ‘Name Box’.

48. Describe the benefit of cell referencing during the calculation?

On the fabrication of a formula for a specific function, the user may direct Excel to the specific location of the data for which the formula is being entered. The referring of the cell consisting of the data is known as ‘Cell Referencing’

49. Shortcut for auto-sum of rows/columns?

The shortcut is employed by coming to the end of the field of data and pressing the ‘ALT + =’ signs, as shown in the picture below

50. How can a user remove duplicate entries in a dataset?

To employ this task, the user needs to select the data set that he/she wishes to check and then:-

• Go to the Data ribbon
• In the data tools pane
• Choose the ‘Remove duplicates’ option
• Ensure that the ‘My data has headers’ option is checked if that is the case
• Select the column from which the duplicates need to be removed
• Click on ‘OK