Excel Interview Questions and Answers- Part 2

In Excel, there are three main types of cell references: relative references, absolute references, and mixed references. These references are used to identify the location of a cell or range of cells within a formula.

Answer:

Follow these steps to remove duplicates in Excel:

• Choose the range of cells you want to check for duplicates.
• Go to the “Data” tab & click the “Remove Duplicates” button.
• Choose the columns to check for duplicates or select “Continue with the current selection.”
• Click “OK” to remove the duplicates.

Answer:

To protect cells in Excel, you can use the “Protect Sheet” feature. It allows you to lock specific cells or the entire worksheet to prevent accidental changes. You can also set a password to protect the worksheet and restrict access to it.

Answer:

Conditional formatting allows you to format cells based on specific criteria or rules. To apply conditional formatting in Excel, select the range of cells you want to format, go to the “Home” tab, click on “Conditional Formatting,” and choose the desired formatting rule or create a custom rule.

Answer:

To create a chart or graph in Excel, select the data range you want to include in the chart, go to the “Insert” tab, choose the desired chart type like line, column, pie and follow the prompts to customize the chart’s appearance and layout.

Answer:

A PivotTable is a powerful data summarization tool in Excel. It allows you to analyze, summarize, and manipulate large amounts of data from different perspectives. PivotTables can be used to create reports, generate charts, and perform data analysis.

Answer:

Functions are pre-built formulas in Excel that perform specific calculations or tasks. They are used to simplify complex calculations and provide built-in functionality. Examples of Excel functions include SUM, AVERAGE, IF, VLOOKUP, and COUNT.

Answer:

The COUNT function counts the number of cells that contain numbers within a specified range. The COUNTA function counts the number of cells that are not empty including text, numbers, and errors within a specified range.

Answer:

The SUMIF function is used to calculate the sum of values in a range that meets specific criteria. The syntax is: SUMIF(range, criteria, [sum_range]).

Answer:

The main key difference between formula and function is that former is user define while the latter is an inbuilt feature of MS Excel.

Answer:

In Microsoft Excel, you may encounter various types of errors when working with formulas or data. These errors occur due to a variety of reasons, such as incorrect syntax, referencing errors, division by zero, or problems with the data itself. Here are some common errors you might come across:

• #DIV/0!: This error occurs when you attempt to divide a number by zero or an empty cell.
• #VALUE!: This error typically occurs when a function or formula receives the wrong type of argument or when you use an incorrect data type.
• #REF!: This error indicates that a cell reference is invalid or refers to a cell that has been deleted.
• #NAME?: This error occurs when Excel does not recognize a text or name used in a formula. It could be due to misspelling a function name or using a function that is not available in your version of Excel.
• #NUM!: This error arises when a numeric value in a formula is invalid or impossible to calculate. It can occur if you provide invalid arguments to a function or use functions incorrectly.
• #N/A: It indicates that the value being looked up in a table or a function cannot be found. It can occur when using functions like VLOOKUP or HLOOKUP and the lookup value is not present in the table.
• #NULL!: This error occurs when you use an intersection operator (space) in a formula, but Excel cannot find a valid intersection between the specified ranges.

Answer:

In Excel, the function used to insert the current date and time is called “NOW()”.

The NOW() function is a built-in function that returns the current date and time according to your computer’s system clock. It updates automatically whenever the worksheet is recalculated or when there is a change in the workbook.

Answer:

To merge cells in Excel, follow these steps:

• Select the cells you want to merge. You can select multiple cells by clicking and dragging over them or by holding the Ctrl key and clicking on individual cells.
• Once the cells are selected, go to the “Home” tab on the Excel ribbon.
• Look for the “Alignment” group in the ribbon. It typically contains options like “Wrap Text,” “Merge & Center,” and others.
• Click on the “Merge & Center” button. It has an icon that shows several cells merged into one, with text centered.
• The selected cells will be merged into a single cell, and the contents of the upper-left cell will be centered within the merged cell.

Answer:

• Date
• Currency
• Number
• Text formats
• Percentage

Answer:

• IF Function
• FALSE
• IFNA
• AND
• TRUE
• IFERROR
• NOT
• OR

Answer:

In Microsoft Excel, two different macro languages that are commonly used are Visual Basic for Applications (VBA) and Excel Formula Language (EFL).

• Visual Basic for Applications (VBA): VBA is a powerful programming language that is integrated into Microsoft Office applications, including Excel. It allows users to create macros and automate tasks within Excel. With VBA, you can write custom code to manipulate data, automate repetitive processes, create user forms, and interact with other Office applications.
• Excel Formula Language (EFL): While not a traditional programming language, Excel Formula Language is a built-in macro language within Excel. It allows users to perform calculations, manipulate data, and automate tasks using formulas and functions. Excel formulas are written using a specific syntax and can be combined to create complex calculations and perform various operations

Answer:

In Excel, wildcards are characters that represent unknown or variable values in search criteria. They can be used with various functions, such as FIND, SEARCH, COUNTIF, and SUMIF, to perform flexible and powerful searches and calculations.

Answer:

To apply the same formatting to multiple Excel sheets, you can follow these steps:

• Open your Excel file and navigate to the first sheet that you want to format.
• Apply the desired formatting (e.g., font style, size, color, cell borders, etc.) to the cells, columns, or rows as needed.
• Once you have finished formatting the first sheet, right-click on the sheet tab at the bottom of the Excel window and select “Move or Copy” from the context menu.
• In the “Move or Copy” dialog box, choose the option to “Create a copy” and select the sheet where you want to apply the same formatting. You can also choose to move the sheet instead of creating a copy if that is your intention.
• Click “OK” to create the copy or move the sheet. The copied or moved sheet will now appear next to the original sheet.
• Repeat steps 3-5 for any additional sheets you want to format.

Answer:

The INDEX MATCH function in Microsoft Excel is a powerful combination that enables you to search for a specific value in a table or range and return a corresponding value from another column. It overcomes some of the limitations and complexities of the VLOOKUP function, making it a preferred choice for many Excel users.

Answer:

To convert an Excel file into a PDF file, you can follow these steps:

• Open the Excel file that you want to convert to PDF.
• Click on “File” in the menu bar and select “Save As.”
• In the Save As dialog box, choose the location where you want to save the PDF file.
• In the “Save as type” dropdown menu, select “PDF (*.pdf).”
• Optionally, you can modify the file name if desired.

Click on the “Save” button.