Top 50 Microsoft Excel Interview Q/A

What is Microsoft Excel?
Microsoft Excel is an electronic spreadsheet application developed by Microsoft in 1985. It enables users to store, manage, analyze, and manipulate data using functions and formulas within a grid of rows and columns. It is also compatible with other databases, making it a versatile and time-saving tool.

Define cells.
Cells are the individual intersections where rows and columns meet on an Excel worksheet. They are rectangular spaces in which data can be entered. Each worksheet can contain a vast number of cells, with approximately 17,179,869,184 cells in a single worksheet.

Describe a Spreadsheet.
A spreadsheet, also known as a worksheet, is a collection of cells used for data management in Microsoft Excel. One Excel workbook can contain multiple worksheets. Worksheets can be named to distinguish them and are typically visible at the bottom of the Excel window.

Define Cell Address.
A cell address, also known as a cell number, is the reference for a specific cell in an Excel worksheet. It consists of the column letter and the row number, such as "A1" or "B3," which uniquely identifies a cell.

How to add cells to the spreadsheet?
To add cells in Excel, right-click on the cell where you want to insert cells, and a dialog box will appear. In this dialog box, select the "Insert" option, and another dialog box will pop up, allowing you to choose how you want to add cells. Confirm your choice by clicking 'OK.'

How can a user format a cell in MS Excel?
Cell formatting can be achieved by right-clicking on the cell, selecting the "Format Cells" option, and using the dialog box that appears to customize various aspects, including number format, alignment, font style, borders, cell fill color, and protection.

Describe if a user can add comments to a cell and how.
Yes, users can add comments to a cell in Excel by selecting the cell, right-clicking to bring up a menu, and then choosing the "Insert Comment" option. This allows users to include comments that can be viewed by others who access the file.

How can a user add complete rows and columns to a sheet?
To add entire rows or columns to an Excel sheet, right-click in the desired location and choose the "Insert" option. You can then select the bottom two options for inserting complete rows or columns, as required.

Describe what is a Ribbon in MS Excel.
The Ribbon is a central element in Excel, located at the top of the window. It provides access to various commands and tools for managing, sorting, and visualizing data. The Ribbon consists of different tabs, each offering a set of functions to customize and work with data.

Describe why and how may a user freeze a pane in MS Excel.
Freezing panes in Excel help keep row and column headings visible when scrolling through a large sheet. To freeze panes, select the rows/columns to be frozen, click the "Freeze Panes" option in the "View" tab, and choose from the available options to lock the desired areas in place.

How can a user 'Wrap text' in MS Excel?
To wrap text in Excel, select the cell with the text, and then choose the "Wrap Text" option from the "Home" tab. This will automatically adjust the row height to display the text properly within the cell.

How can the user prevent data from being copied by someone else?
To protect data from being copied into an Excel sheet, users can go to the "Review" tab, choose "Protect Sheet," specify the actions allowed for sheet users, set a password, and confirm the protection criteria.

Define charts in MS Excel and how to employ them.
Charts in Excel are graphical representations of data that make complex information more visually understandable. Users can access charts by going to the "Insert" tab and selecting the desired chart type. They then input their data and customize the chart's appearance.

How can a user sum up numerical values in rows or columns quickly?
To sum numerical values quickly, users can select the desired range of cells and click the "AutoSum" button on the "Home" tab. Excel will automatically calculate the sum of the selected cells.

How can a user apply a single format to all the sheets in a Workbook?
To apply the same format to all sheets in a workbook, right-click on a sheet name, choose the "Select All Sheets" option, make the necessary format changes on one sheet, and the changes will be applied to all sheets in the workbook.

Describe the steps to resize one or multiple columns.
Users can resize columns by selecting the column they wish to adjust and dragging the mouse horizontally to change the column width. For multiple columns, select them while pressing the 'CTRL' key, go to the "Home" tab, choose "Format," and select "Column Width" to input a specific width.

Describe how a user might merge cells in Excel.
Merging cells in Excel is done by selecting the cells to be merged, going to the "Home" tab, and using the "Merge & Center" button. This allows users to merge cells and choose whether to center-align the content.

Mention the steps for highlighting cells with negative values.
To highlight cells with negative values, select the cells, go to the "Home" tab, click on the "Conditional Formatting" menu, choose "Highlight Cells Rules," select "Less than," specify the threshold value (e.g., 0), and set the desired highlight color.

Describe the order of operations used when evaluating formulas in Excel.
Excel follows the order of operations, typically referred to as PEMDAS (Parentheses, Exponentiation, Multiplication and Division, Addition and Subtraction). Formulas are calculated according to this order.

Describe the difference between a function and a formula in Excel.
A function is a predefined operation in Excel that takes specific arguments to perform a calculation. A formula, on the other hand, is a user-defined expression used to calculate a value based on operators, functions, and cell references.

What may be defined as the top functions of MS Excel?
There are numerous functions in Excel, but some of the essential ones that users should be familiar with include VLOOKUP, COUNTIF, SUMIF, IFERROR, INDEX/MATCH, SUMPRODUCT, TEXT, AVERAGE, and LEN/LEFT/RIGHT/MID.

Describe how a user might tackle errors while working with Excel formulas. Errors in Excel formulas can be handled by using functions like ISERROR or IFERROR to identify and manage error values. Conditional formatting can also be used to highlight errors in the worksheet.

Describe the functions that may be implied to get the current date and time in Excel.
Excel provides two functions for retrieving the current date and time: TODAY() for the current date and NOW() for the current date and time.

Which formula can the user employ to find out the length of a text string in a cell?
The user can use the 'LEN' function to determine the length of a text string in a cell.

Describe the Pivot Tables.
Pivot tables are powerful tools in Excel for summarizing and analyzing large datasets. They allow users to create customized views of data, perform calculations, detect patterns, and generate concise reports.

Describe the process of providing a dynamic range in the "Data Source" of pivot tables.
Users can define a dynamic range for a pivot table's data source by creating a Named Range using the OFFSET function and then specifying that Named Range when setting up the pivot table.

How can the user make a Pivot table with numerous sources of data?
To create a Pivot table with data from multiple sources, the sources must be located in different worksheets within the same workbook. Users can consolidate the data from these sheets into one Pivot table.

Name the event employed to check if any modification was made in the Pivot Table.
To check if any modifications were made to a Pivot Table, users can utilize the 'PivotTableUpdate' event in the worksheet that contains the Pivot Table.

Describe the process of how can the user disable automatic sorting in pivot tables.
To disable automatic sorting in pivot tables, users can go to 'More Sort Options' and deselect the 'Sort automatically' option in the 'Sort Menu' under 'Pivot Tables.'

How can the operator stop the pivot table from losing the column width after refreshing?
To prevent the pivot table from losing column widths after refreshing, users should enable the 'Preserve Formatting' option and disable the 'AutoFormat' option in the Pivot Table Options.

How can a user calculate percentages in Excel?
Users can calculate percentages in Excel by using the formula (Part/Whole) * 100. They need to divide the part by the whole, and then multiply the result by 100 to obtain the percentage.

Can a user calculate Compound Interest in Excel?
Yes, users can calculate compound interest in Excel using the 'FV' (Future Value) function with appropriate arguments for rate, number of periods, payment, present value, and type.

Can the user find averages in Excel?
Yes, users can find the average of a set of numbers in Excel by using the 'AVERAGE' function, which calculates the mean of the given values.

Describe the 'LOOKUP' function.
The 'LOOKUP' function in Excel is used to retrieve a value from an array of data based on a specified search criterion. It searches for the closest match in the data array.

Describe the VLOOKUP function in Excel.
VLOOKUP is a function in Excel used to search for a specific value in a vertical column. It is often used to extract data from a table based on a unique identifier.

Describe the functioning of the VLOOKUP function.
The VLOOKUP function in Excel searches for a specified value in the first column of a table and returns a corresponding value from a specified column within that table. Users need to provide the lookup value, the table array, and the column index number and specify whether they want an approximate or exact match.

Describe 'What if analysis in Excel?
'What if analysis' in Excel involves making changes to formulas or variables to observe their impact on the entire worksheet. Excel offers tools like Data Tables, Scenarios, and Goal Seek to perform such analyses.

Differentiate between SUBSTITUTE and REPLACE functions in Excel?
The REPLACE function is used to replace a portion of text within a cell with new text, while the SUBSTITUTE function is used to replace specific instances of old text with new text in a cell.

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

• COUNT counts numeric values in a range excluding blank cells.

• COUNTIF counts cells that meet specific criteria within a range.

• COUNTA counts all non-empty cells in a range, including text and numbers.

• COUNTBLANK counts the number of blank cells in a range.

Describe the 'IF' function in Excel.
The 'IF' function in Excel is used for logical testing. It evaluates a specified condition and returns one value if the condition is true and another value if it's false. It is commonly used for decision-making in formulas.

Describe Volatile functions.
Volatile functions in Excel are functions that recalculate every time there is any change made in the worksheet. They can impact performance, especially in large datasets. Examples include TODAY(), RAND(), OFFSET(), and others.

How can a user quickly switch between worksheets without the use of the mouse?
To quickly switch between worksheets without the mouse, users can press 'CTRL + PAGE DOWN' to move to the next sheet and 'CTRL + PAGE UP' to go to the previous sheet.

How can a user determine the day of the week according to the date?
To determine the day of the week from a date in Excel, users can use the 'WEEKDAY' function, which returns a number corresponding to the day of the week.

Describe the benefits of employing formulas in a worksheet.
Formulas in Excel allow for automated calculations and dynamic updates. They enhance the efficiency of tasks, as they can be used to perform complex calculations and are especially useful when working with large datasets.

How can the operator create shortcuts for Excel functions?
Users can create shortcuts for Excel functions by customizing the 'Quick Access Toolbar,' which is located above the Home tab. It allows users to add frequently used functions for quick access.

Which filter may an operator use if he/she wishes to analyze a list using database functions?
To analyze a list using database functions, users can use the 'Advanced Criteria Filter.' This filter is suitable for more complex filtering requirements.

How can a user return to a specific area of a worksheet?
Users can return to a specific area of a worksheet by typing the cell address in the 'Name Box' located in the upper left corner of the Excel window.

Describe the benefit of cell referencing during the calculation.
Cell referencing in Excel allows users to create dynamic and interactive worksheets. Instead of using fixed values, referencing cells ensures that formulas update automatically when referenced cells change, making the worksheet more versatile.

Shortcut for auto-sum of rows/columns?
The shortcut for auto-summing rows or columns is 'ALT + =.' This quickly calculates the sum of the selected range of cells.

How can a user remove duplicate entries in a dataset?
To remove duplicate entries in a dataset, select the data, go to the 'Data' tab, click on the 'Remove duplicates' option, choose the relevant column, and click 'OK' after specifying options like having headers.