Top 50 Excel Interview Questions 2023
Beginner to Advanced Guide
Explain MS Excel briefly:
MS Excel is a spreadsheet application developed by Microsoft in 1985. It allows users to store data in tabular form and is compatible with various operating systems such as Windows, macOS, and Android. Excel provides built-in functions like SUM and DATE for calculations, supports data validation, and allows for data analysis through charts and graphs.
What are cells in MS Excel?
Cells in MS Excel are the intersections of rows and columns within a spreadsheet. They serve as the fundamental units for storing and displaying data. An Excel sheet can contain a vast number of cells, with a total of 1,048,576 rows and 16,384 columns in a single sheet.
What is a cell address?
A cell address is a unique reference used to locate a specific cell in an Excel spreadsheet. It is composed of a combination of a column letter and a row number, such as "A1." This address system allows users to precisely identify and work with individual cells.
What is the difference between Relative cell referencing and Absolute cell referencing?
Relative Cell Referencing: In this type of referencing, cell references change when copied to other cells, adjusting according to their relative positions.
Absolute Cell Referencing: Absolute cell references do not change when copied to other cells. To make a cell reference absolute, you add a dollar sign ($) before and after the column and row references.
How can you add cells in Excel?
To add cells in Excel, select the cell where you want to insert new cells, then go to the "Insert" option in the menu. This allows you to insert new rows or columns, effectively expanding the spreadsheet.
How can you format MS Excel cells?
You can format MS Excel cells using the options in the "Font" group found on the "Home" tab. These formatting options include settings for font, alignment, borders, fill color, and protection, among others.
What is the difference between a Formula and a function in MS Excel?
Formula: A formula in Excel is like an equation that allows users to perform various calculations or operations. Formulas require manual input and are time-consuming.
Function: A function in Excel is a pre-defined calculation that is built into the software. Functions are less time-consuming and more user-friendly, as they provide ready-made solutions for common calculations.
How to add comments in Excel?
To add comments to a cell in Excel, select the cell, right-click on it, and choose the "New Comment" option. This allows you to attach comments that are visible to anyone with access to the Excel sheet.
What is Ribbon?
The Ribbon is the top interface in Excel, providing access to various commands and functions. It consists of multiple tabs, such as "File," "Home," "View," and "Insert." The Ribbon is a central element for interacting with Excel's features.
How do you freeze panes in Excel?
To freeze panes in Excel, select the rows and columns you wish to keep visible. Then, go to the "View" tab and select "Freeze Panes." You can choose from options like "Freeze Panes," "Freeze Top Row," and "Freeze First Column" to control what remains visible as you scroll through the spreadsheet.
What is the order of operations used while evaluating a Formula in Excel?
The order of operations in Excel, known as PEDMAS, stands for:
Division and Multiplication (left to right)
Addition and Subtraction (left to right)
This order governs the sequence in which Excel performs calculations.
What is the difference between Count, Counta, and Countblank?
Count: Counts the number of cells containing numeric values.
Counta: Counts the number of cells with any form of content, including text, characters, and numeric values.
Countblank: Counts the number of empty or blank cells.
How do you create a hyperlink in Excel?
To create a hyperlink in Excel, use the shortcut Ctrl+K. This opens the "Insert Hyperlink" dialog box, where you can specify the address and the text to be displayed in the hyperlink.
How do you add a note in Excel?
To add a note in Excel, select the cell, right-click, and choose the "New Note" option. You can type your note, and it will be indicated by a red triangle at the top-right corner of the cell.
How do you protect a cell from being copied?
To protect a cell from being copied, select the cells you want to protect, open the "Font" window from the "Home" tab, choose "Protection" in the Protection pane, check the "Hidden" box, go to the "Review" tab, and select "Protect Sheet" while specifying a password for security.
What are macros?
Macros are a set of actions in Excel that allow you to automate repetitive tasks. You can record a series of actions and execute them as a macro to save time and effort.
How do you create named ranges in Excel?
To create named ranges in Excel, select the range you want to name, go to the "Formulas" tab, click "Define Name" in the Defined Names group, and provide a name for the selected range.
How do you create drop-down lists in Excel?
To create drop-down lists in Excel, go to the "Data" tab, choose "Data Validation" from the Data Tools group, navigate to "Settings," select "List" under "Allow," and specify the source list array.
What is a Pivot Table?
A Pivot Table is a powerful tool in Excel for data summarization and analysis. It condenses large datasets, allows for quick data comparison, identifies patterns, and provides a flexible way to view and analyze data from various angles.
What are the features of a Pivot Table?
Pivot Tables offer features like data analysis, quick data comparison, pattern detection, and the ability to focus on critical details. They provide a dynamic and interactive way to present and explore data.
How do you create Pivot Tables in Excel?
To create a Pivot Table in Excel, you need to:
Organize your data in a tabular form.
Ensure that there are unique column headings in the first row.
Make sure columns contain consistent data types.
Eliminate any blank rows or columns.
Ensure the data for the Pivot Table does not overlap with other data in the sheet.
What are Pivot Charts in Excel?
Pivot Charts in Excel are visual representations of data that allow for in-depth analysis through various types of graphs and layouts. They are particularly useful for presenting and exploring large datasets.
What is the difference between VLOOKUP and LOOKUP functions?
VLOOKUP: Searches for a value in the leftmost column of a table and returns a value from the same row but in a specified column.
LOOKUP: Searches for data in a row or column and returns data from another row or column. It is a more versatile function that can replace VLOOKUP in some cases.
What is Data Validation?
Data Validation in Excel is a feature that controls the type of data entered in a cell. It restricts the values that users can input into a specific cell, helping maintain data integrity.
How do you calculate your age from today's date in Excel?
To calculate your age in Excel, you can use the YEARFRAC function by providing the start and end dates. This function returns the fractional number of years between the two dates, which can be used to determine your age.
What are the different Functions available in Excel?
Excel offers a wide range of functions, including those for date and time, logical operations, statistics, math and trigonometry, and more. Some examples include DAY, DATE, AND, OR, AVERAGE, SUM, and PRODUCT.
How do you calculate the product of two numbers in Excel?
You can calculate the product of two numbers in Excel using the PRODUCT function. Simply type "=PRODUCT" and input the two numbers you want to multiply, and Excel will display the result.
How do you calculate the percentage of two numbers in Excel?
To calculate the percentage of two numbers, you can select the destination cell, type an equation like "A1/A2," and then format the cell as a percentage by selecting the "%" symbol from the "Home" tab.
How do you find the average of numbers in Excel?
To find the average of numbers in Excel, you can use the AVERAGE function, which calculates the arithmetic mean of a range of numbers.
How do you fetch the current date in Excel?
To obtain the current date in Excel, you can use the TODAY function, which returns the current date in the standard Excel date format.
How to add filters in Excel?
To add filters in Excel, select a cell within the data range, go to the "Data" tab, and click on the "Filter" option. This enables filter functionality for your data, allowing you to sort and filter it.
How do you find the sum of two numbers in Excel?
You can calculate the sum of two numbers in Excel using the SUM function, which adds up a range of numbers.
How many report formats are there in Excel?
There are three report formats in Excel: Compact report format, Tabular report format, and Outline report format. These formats are available in the Report Layout menu and determine how data is presented.
How does the IF function work in Excel?
The IF function in Excel performs a logical test and allows you to make comparisons between a value and an expected outcome. It returns one result if the comparison is true and another if it's false.
How does the index-match function work in Excel?
The INDEX-MATCH function in Excel combines two functions. The INDEX function retrieves an item from a specific position in a list, and the MATCH function finds the position of a value within the list. Together, they provide a flexible way to look up and retrieve data.
How do you find duplicate values in a table using Conditional Formatting?
To find duplicate values in a table using Conditional Formatting, you can go to the "Home" tab, select "Conditional Formatting," choose "Highlight Cells Rules," and then select "Duplicate Values." Excel will highlight the duplicate values for you.
How can you remove duplicate values in a range of cells in Excel?
To remove duplicate values from a range of cells, you can select the cells with duplicates, press the "Delete" key to remove them, and then go to the "Conditional Formatting" option on the "Home" tab to clear the formatting rules.
How can you protect workbooks in Excel?
You can protect workbooks in Excel in various ways, including setting a password to open workbooks, protecting sheets from being added or deleted, and ensuring window sizes remain fixed.
What are Relative cell addresses in Excel?
Relative cell addresses in Excel are the default cell references. They consist of a column name and a row number without the dollar sign ($). When you copy formulas in Excel, the addresses of relative cells adjust automatically, making them dependent on their relative positions.
What are the wildcards available in Excel?
In Excel, there are three wildcards for working with text data:
Asterisk ():* represents any number of characters.
Question mark (?): Represents a single character.
Tilde (~): Identifies a wildcard character in the text.
Did you find this article valuable?
Support Amrutha D by becoming a sponsor. Any amount is appreciated!