Online Tutorials & Training Materials | STechies.com
Register Login

Advance Microsoft Excel Interview Questions and Answers

|| 0

Advance Microsoft Excel Interview Questions and Answers
Stechies

What is Microsoft Excel?

Microsoft Excel is commercial software that works like a spreadsheet application for performing calculations, developing graphs, pivot tables, financial accounting, statistical analysis, and financial modeling operations which helps in customer relationship management.

What does the dollar sign mean in Excel?

In Excel, a dollar sign denotes absolute cell references. That means even if the formula is moved or copied to another cell, the original cell location is not changed.

Note: dollar sign in excel is also used to indicate a currency format 

What is a pivot table in Excel?

A pivot table is used to summarize the different statistics of a large set of data in a spreadsheet. Here the information like maximum values, minimum values, the averages, total valuations, etc. is represented using groups to perform analysis properly.

How to split cells in excel?

The steps to split the contents of a cell are given below:

  • Choose the cells that have to be split.
  • Click on Text to Columns in the Data Tools options inside the Data tab.
  • After the Convert Text to Column Wizard Appears, click on Delimited and Click on Next.
  • Choose from the list of delimiters that represent the areas where the content has to be split. Click Next.
  • Inside Column Data Format, choose the format for the columns. Click Finish to complete the process.

Differentiate between Lookup() and Vlookup() in Excel?

The primary difference between the LOOKUP() function and the VLOOKUP() function is the way they search for data in a table.

The LOOKUP() function searches for data in a row or column and returns the data from another column or row. VLOOKUP() only searches data vertically in the table and returns the result.

LOOKUP() allows the user to search data throughout the table, but VLOOKUP() only searches in a left to right manner. Apart from this, LOOKUP() is easier to audit than VLOOKUP().

What does #value mean in excel?

The #VALUE represents an incorrect type of operator or the wrong type of function argument. The user can encounter this error if a mathematical calculation is performed for which the data is in more than one cell. The formula may have been typed incorrectly or if the cells are referenced wrongly during the operation.

What is NPER in excel?

The NPER is a function used in Excel for financial analysis for calculating the number of loan periods or for a particular investment. It can be used for determining the loan payment periods, based on the rate of interest, the amount, and the periodic payment quantity. The syntax for:

NPER is =NPER (rate, pmt, pv, [fv], [type]).

What are the Sparklines in Excel?   

A Sparkline is a small chart in a spreadsheet cell that is used for representing the data in a visual form. It can be used to identify the changes in data trends and variation in the data set that takes place over time.

How to prepare MIS reports in Excel?

MIS reporting can be performed in several ways in Excel. Graphs, pivot tables, charts sparklines can be used to present the data in the MIS spreadsheet.

Certain steps can be taken for successful reporting:

  • Collect the raw data required for the report and remove the unnecessary data from it like duplicate data, incorrect information etc.
  • Remove the unwanted spaces in the spreadsheet using the TRIM() function.
  • Converting the values stored as text to numeric values using the VALUE() function.
  • Use the LEN() function to calculate the length of the numeric values to ensure the accuracy of data.

How to split a cell in Excel?

The steps to split the contents of a cell are given below:

  • Choose the cells that have to be split.
  • Click on Text to Columns in the Data Tools options inside the Data tab.
  • After the Convert Text to Column Wizard appears, click on Delimited and Click on Next.
  • Choose from the list of delimiters that represent the areas where the content has to be split. Click Next.
  • Inside Column Data Format, choose the format for the columns. Click Finish to complete the process.

How to create a Tally XML file from Excel?

The steps to create Tally XML file from excel are as follows:

  • Go to an online portal for converting Excel to Tally files.
  • Download the Excel template.
  • In the excel template, the tally data has to be provided.
  • After the data has been uploaded here, generate the XML file.
  • Download the Tally XML file.
  • Select Import Data option in Tally. Enter the path where the XML file has been downloaded.
  • The process will be completed.

How to normalize data in excel?   

The steps to normalize data in excel are as follows:

  • Open a spreadsheet and type in the data to be normalized in the first cell A1.
  • In the cell C1, type in =AVERAGE(A1: AX), where AX means the last cell in the column. This is used for calculating the Average.
  • In the cell C2, type in =STDEV.S(A1:AX), where AX means the last cell in the column. This is used for calculating the Standard deviation of your data.
  • Click on the first cell in column B and type STANDARDIZE()A1, C$1, C$2). After that, the normalized form of cell A1 will be shown in cell B1.
  • Normalize the remaining cells.

What is the fill handle in excel?

A fill handle in Excel is a feature represented by a small box at the bottom right corner of each cell. It allows the extension of a certain series of dates, numbers, text up to the required number of cells.

For example, if 1 is entered in cell B1 and 2 is entered in cell B2 then the series can be extended up to the desired number of cells by selecting both the cells and dragging the fill handle.

What is a label in Excel?

A label in Excel is used to describe the data in a particular row or column around it. All the text describing values and numbers are referred to as labels.

How to enable macros in excel?

Steps to enable macros are:

  • Go to Macro Security in Developer
  • Alternatively, go to File and then to Options. Click on Trust Center Settings.
  • Click on Macro settings tab in the left side.
  • Select the macro setting of choice to enable it.

How to add watermark in Excel?

Steps to add watermark are:

  • Go to the 'Header and Footer' option in the Insert tab.
  • Click on Header and choose Picture.
  • After inserting the image click outside the header to see the watermark appear.

How to convert notepad to Excel?

A text file can be converted to an excel file by using the Text Import Wizard. The new file should be saved with an extension of xlsx and xls.

What is goal seek in excel?

Goal seek lets the user adjust a value used inside a formula for achieving a goal.

Explain COUNT, COUNTA, COUNTIF and COUNTBLANK in Excel.

  • COUNT Used for counting numerical values in cells excluding the blanks.
  • COUNTA – Used for counting any cell values except blanks.
  • COUNTIF – Used for counting cells that match a condition.
  • COUNTBLANK – Used for counting blank cells.

Explain What-if analysis in excel.

What-if analysis lets the user change the values of certain cells to understand their effect on other formulas in the document.