Excel Interview Questions and Answers- Part 3

To insert a new worksheet, right-click on an existing worksheet tab and select “Insert” from the context menu. Choose the type of worksheet you want to insert and click “OK.”

Answer:

To freeze panes, select the row below and column to the right of the area you want to freeze. Then, go to the “View” tab, click on “Freeze Panes,” and choose the desired option.

Answer:

To protect a worksheet, go to the “Review” tab, click on “Protect Sheet,” and set a password if required. You can specify what actions users can perform on the protected sheet.

Answer:

To build named ranges in Excel, you can follow these steps:

• Select the range of cells that you want to name. This could be a single column, row, or a rectangular block of cells.
• Click on the “Formulas” tab in the Excel ribbon.
• In the “Defined Names” group, click on the “Name Manager” button. This will open the Name Manager dialog box.
• In the Name Manager dialog box, click on the “New” button. This will open the New Name dialog box.
• In the “Name” field, enter the name you want to assign to the selected range. Make sure the name is meaningful and easy to remember.
• In the “Refers to” field, you should see the range reference for the cells you previously selected. If it’s not already filled in, you can manually enter the range reference or click on the “Collapse Dialog” button and then select the desired range on the worksheet.
• Optionally, you can add a comment in the “Comment” field to provide additional information about the named range.
• Click on the “OK” button to create the named range.

Answer:

In Excel, a variant data type refers to a type of data that can hold different types of values. Unlike other specific data types in Excel, such as numbers or text, a variant data type can be used to store various kinds of data, including numbers, text, dates, Boolean values (True/False), and even error values.

Answer:

A “relative cell address” refers to the location of a cell within a spreadsheet or worksheet, expressed in relation to the position of another cell. In spreadsheet applications like Microsoft Excel or Google Sheets, each cell is identified by a unique combination of a column letter and a row number.

Answer:

A relative cell address is typically used when referencing cells in formulas or functions. When a formula or function is entered into a cell, it can reference other cells to perform calculations or retrieve data. The relative cell address allows the formula or function to adjust its references automatically when copied or moved to different cells.

Answer:

In Microsoft Excel, the code window for writing VBA (Visual Basic for Applications) code is located within the Visual Basic Editor (VBE).

Answer:

In VBA (Visual Basic for Applications), there are several core modules that provide essential functionality. Below are three important modules commonly used in VBA programming:

• Code modules
• Class modules
• User forms

Answer:

The purpose of Flash Fill is to save time and effort when working with large datasets. It analyzes the patterns in your data and automatically fills in values in a column based on the existing data.

Answer:

A Name Box in Microsoft Excel is a feature that facilitates you to easily navigate to a specific cell or range of cells within a worksheet. It is located at the top-left corner of the Excel window, just above the column headers. It displays the address of the currently selected cell or range, such as “A1” or “B2:C5.” You can also use the Name Box to enter or edit the name of a defined range or a named cell in Excel.

Answer:

Performing a what-if analysis in a spreadsheet typically involves using formulas, functions, and data tables to explore different scenarios and understand the impact of changing variables on the results. Here’s a general approach to performing a what-if analysis in a spreadsheet:

• Define your objectives
• Set up your spreadsheet
• Identify the variables
• Use formulas and functions
• Set up data tables
• Analyse the results
• Refine and iterate

Answer:

In Microsoft Excel, the AND and OR functions are logical functions that allow you to evaluate multiple conditions and return a result based on the outcome of those conditions.

Answer:

In Microsoft Excel, you can use the WEEKDAY function to determine if a day is a weekday or a weekend day. The WEEKDAY function returns a number representing the day of the week for a given date, where Sunday is considered the first day of the week (represented by 1) and Saturday is the last day of the week (represented by 7).

Answer:

To outline a group of cells in Microsoft Excel, you can use the built-in outlining feature that allows you to collapse or expand rows or columns, making it easier to manage and view large sets of data. Here’s how you can outline a group of cells:

1. Select the range of cells that you want to outline. You can do this by clicking and dragging the mouse over the desired cells.
2. Once the cells are selected, go to the “Data” tab on the Excel ribbon.
3. In the “Outline” group, you’ll find two buttons: “Group” and “Ungroup.” Click on the “Group” button.
4. Excel will automatically add a collapsible outline to the left side of the selected rows or above the selected columns, depending on your selection. You will see small symbols indicating the outline level.
5. To collapse or expand the outline, use the symbols. Clicking the “-” symbol collapses the group, hiding the details within it, while clicking the “+” symbol expands the group, showing the hidden details.

Answer:

To lock cell references in a formula in Microsoft Excel, you can use the dollar sign ($) to create absolute references. Absolute references prevent the cell references from changing when you copy or fill the formula to other cells.

Answer:

The SUBTOTAL function is a powerful tool in spreadsheet software, commonly used in programs like Microsoft Excel or Google Sheets. It calculates a subtotal of a range of values based on specified criteria or operations. Below are some key points regarding the significance of the SUBTOTAL function:

• Automatic exclusion of subtotals
• Enhanced data analysis
• Dynamic range inclusion
• Conditional calculations

Answer:

Cross-tabulation, also known as a pivot table in Excel, is a powerful tool used for data analysis and summarization. It allows you to examine the relationships between two or more variables in a dataset and provides a structured way to analyze and present the data.

Answer:

To add notes to a cell in Microsoft Excel, follow these steps:

• Open your Excel worksheet and navigate to the desired cell where you want to add the note.
• Right-click on the cell and select “Insert Comment” from the context menu. Alternatively, you can select the cell and go to the “Review” tab in the Excel ribbon, then click on the “New Comment” button.
• A small text box will appear next to the selected cell, where you can enter your note or comment. Type the desired text into the comment box.
• Once you have entered your note, you can resize the comment box by clicking and dragging the edges to make it larger or smaller.
• By default, the comment will be displayed when you hover over the cell with the mouse cursor. If you want the comment to always be visible, right-click on the comment box, select “Show/Hide Comment,” and the comment will stay visible on the cell.
• To edit the comment, right-click on the cell, select “Edit Comment” from the context menu, and make the necessary changes.
• To delete the comment, right-click on the cell, select “Delete Comment” from the context menu, and the comment will be removed.

Answer:

From Excel 2007 onwards (Excel 2007, 2010, 2013, 2016, 2019, and Microsoft 365), the maximum number of rows increased to 1,048,576, while the number of columns remained at 256. This expansion allows for a total of 268,435,456 cells in a single worksheet.