Excel Interview Questions and Answers

Ms Excel is a spreadsheet program and one of the components of the Microsoft Office Suite. MS Excel is productivity software which creates and edits spreadsheets with a file extension of .xlsx or .xls. In it, data is laid out in big tables in a number of columns and rows and shows extreme power and versatility.

As Excel is a spreadsheet program, there are various uses of Microsoft Excel., we can use it to-

• make numbers,
• grids of text
• use specified calculations to perform functions on the numbers
• records income, plan budgets, expenditures, external sources, stock market, feeds, etc

Pull in data and runs the data through the formulas for updating information in real time

• Make a comparison of data easy
• Summarization, organization, and structuring of data is less time consuming
• Ease of building great charts
• Sorting and filtering of the information takes a fraction of seconds
• Visual display of data then using graphs, pie charts

MS Excel has several features like-

• Use graphing tools
• Perform basic calculations
• create macros and pivot table

Formulas are used in MS Excel due to their trait of performing several tasks and operations automatically. Also, the users are allowed to use cell references or type numbers directly into the formulas. This lets the formulas to use whatever data is contained in the reference cells.

Basic formulas in MS Excel are

1. SUM

=SUM(number1, [number2], …)

Example: =SUM(B2:G2)

2. AVERAGE

=AVERAGE(number1, [number2], …)

Example: =AVERAGE(A1:A10)

3. COUNT

=COUNT(value1, [value2], …)

Example: COUNT(A1:C1)

4. COUNTA

=COUNTA(value1, [value2], …)

Example: COUNTA(A:A)

5. IF

=IF(logical_test, [value_if_true], [value_if_false])

Example: =IF(C2

6. TRIM

  =TRIM(text)

Example: TRIM(A4)

7. MIN

=MIN(number1, [number2], …)

Example: =MIN(B2:C11)

8. MAX

=MAX(number1, [number2], …)

Example: =MAX(B2:C11)

Resizing the column requires the row height to be changed to fit into the content. Then, you need to select the rows that you modify. Click Format on the home tab, in Cells. Then, under Cell size, click on AutoFit Row Height.

Freeze panes are used when we need to keep a spreadsheet’s area scrolling to another worksheet’s area, visit View tab. There, you can freeze panes, and split panes for creating window separation of the same worksheet.

Steps of freezing rows and columns-

1. Freezing of the first column

• Select View>Freeze Panes > Freeze First Column

2. Freezing of the first two columns-

• Now, select the third column
• Select View> Freeze Panes> Freeze Panes

To freeze rows and columns, you need to select the right of the columns and below the rows in order to make the screen visible.

Formatting of the cells in MS Excel is done in the following steps-

• Open your MS Excel
• Select a cell or a group of cells to be formatted
• Now, click on Format Cells Window
• Select the formatting options for the cell
• Click on Save

Both relative and absolute references behave indifferently when they are copied and filled to other cells. Change in the corresponding reference takes place with the copying of the formula to another cell. But, on the other hand, absolute references there are no changes here when they are copied.

Formula or syntax of the if-then statement in MS Excel-

IF (Logic_Test, Value_if_True, Value_if_False) where expression to be evaluated is denoted by Logic_Test.

Data formats which are used in Excel are

• .xls existing excel workbook or worksheet
• .xlsx existing excel workbook or worksheet
• .csv comma-separated values text file
• .txt text file

There are 1048576. It was 65,536 rows and 256 columns before Excel 2007 hit the market.

After the year 2007 till Excel 2016= 1048576*16384= 17179869184 cells.

A macro in MS Excel is an automated input sequence for emulating mouse actions or keystrokes. A repetitive series of mouse actions and keyword are to be replaced by the macro. Macro is common in word processing and spreadsheets like MS Word and MS Excel.

Financial modeling in excel is one of the understood skills in finance and is highly values. It has been developed with the objective of combining finance, accounting, business metrics. The combination helps in creating an abstract representation of a company in Excel.

Yes, we can do that. For this, you need to select the cell in which you wish to put the combined data. Type = here in the cell, and then select the first cell which you need to connect. Then type& and quotation marks with enclosed space. Next step is choosing the next cell you wish to combine and press enter.

MS Excel uses a formula where the value of a cell is calculated. A user can write the statement for the formula. There are different forms of references, values and functions and names. A formula is an expression used to calculate a cell’s value.

Functions are code designed available in the MS Excel for calculating specific values and used inside formulas.

Most common errors in MS Excel are division by zero, no value available, NULL, NUM, REF, VALUE.

The syntax of VLOOKUP function which allows the users to find an item’s value in one or another spreadsheet is

VLOOKUP (Lookup Value,Table_Array, Col_Index_Num,[Range_Laookup])

Difference between Count(),Counta(), Countif() are as follows-

Order of operator precedence states that in MS Excel multiplication takes place before addition. But we can force the change in the order by using parentheses.

In order to perform a complex function, there is a standard order of operations in it. The first step is to put the specified portion of the formula in parentheses. For example, we need to add the contents of cell C5 and C6, then multiplying it with the data of B6. The derived formula would be =(C5+C6)*B6.

In spreadsheets of MS Excel, sometimes data from more than one category is available and SUBTOTAL function helps in getting the totals of various columns of data of different categories.

SUBTOTAL = (method, range1, [ange2..range_n])

Yes, there is a shortcut key for AutoSum in Excel. All are required to be done is highlighting all the cells whose data is to be added. After that press Alt and = (together)

Pivot Cache is generated with the creation of the Pivot Table. It is a data source’s replica holding object. Pivot Cache is a part of the workbook and pivot table. Every time any changes are required in the pivot table, pivot cache is used instead of the table.

There is a special type of Excel column chart used to mark the change of the value from its starting position to the end no matter whether it increases or decreases. This Excel column chart is known as Waterfall chart in Excel.

There are some steps for sorting numerically in Excel. The first step is the selection of the cell in the column which you want to sort. Next is selecting the data tab and clicking “ascending command’ for sorting smallest to largest of the digits. Whereas, you can select the descending command to sort the data from the largest to the smallest. This is the simple way of getting the data sorted numerically.

VBA is Visual Basic for Applications is an event-driven programming language developed for the personalized applications containing VB for API. Basically, VBA is one of the programming languages of MS Excel and other office programs. The basic feature of VBA is keeping a check and controlling the functionality of MS Excel and other MS Office applications.

Substitute is used to replace one or more instances of a text string or a character. We should use Excel Substitute function when we know the replaceable text.

On the other hand, we use the Replace function in Excel for changing the characters of a text string in a specified position.

These are the Best Interview Question and will help you get your dream job.