Excel Interview Questions and Answers

Excel was developed by the Multinational Technological Company, Microsoft Corporation. It was launched by them in the year 1985. Since then, Excel is prominently known as Microsoft Excel.

• Windows
• macOS
• Android
• iOS

The most important features one would notice in the Microsoft Excel are:

• Graphing tools
• Visual Basic
• Calculations are done using predefined functions
• Pivot tables, etc.

Here are the few characteristics of Microsoft Excel.

• Workbooks, worksheets to arrange the data.
• Rows and columns making work easier.
• Pre-defined functions like count, sum, subtotal, date, etc. make computations easier.
• A supportive hand to microprogramming through the application, Visual Basic.
• Filters, tables, and graphs make data visualization and understanding easy.

• C#
• C++
• Microsoft Foundation Class Library
• .NET Framework

• Excel Workbook – .xlsx
• Excel Macro-Enabled Workbook for code – .xlsm
• Excel Binary Workbook – .xlsb
• Template – .xltx
• Template for code – .xltm
• Excel 97- Excel 2003 Workbook – .xls
• Excel 97- Excel 2003 Template .xlt
• Microsoft Excel 5.0/95 Workbook – .xls
• XML Spreadsheet 2003 – .xml
• XML Data – .xml
• Excel Add-In – .xlam
• Excel 97-2003 Add-In – .xla
• Excel 4.0 Workbook – .xlw
• Works 6.0-9.0 spreadsheet – .xlr, etc.

The Excel Binary Workbook with an extension of .xlsb is considered as the smallest one among all the supportive extensions.

Here are a few ways using which we can reduce the size of Excel.

• Compress the size of images.
• Remove the extra or hidden spreadsheets.
• Remove conditional formatting
• Clear data formatting.
• Clear watches.
• Remove rows and columns that are hidden or not needed.
• Remove unnecessary function usages (formulas).
• Converting the excels of other extensions to the .xlsb (binary) format.

First of all, we need to select the rows and columns which we want to de-format. Then, the path we need to follow to clear the formatting is:

Home -> Editing group -> Clicking the arrow next to clear button -> selecting clear format from the dropdown. These are the commonly asked Excel Interview Questions and Answers for Freshers and Experienced candidates in an Interview.

In Excel, Macro is a code or a set of actions that we use to perform certain tasks repeatedly. In such a case, we record the action or actions as a unit, called macro, to automate the tasking. Now, we can run the tasks numerous times with just a click.

In Microsoft Excel, selecting a function and clicking the F9 key on the keyboard helps in checking the formulas and debugging them, quickly.

In Excel, clicking a simple F12 key on the keyboard pops up a dialog box telling you to save the current file. Simply, It just works exactly like a “Save as” button.

Each basic cell is nothing but the junction where a cell of each row and column meets. It is where we can fill with an element of the data to be stored. Each cell is assigned with a unique address, and as the columns are named as A, B, C… while rows are assigned with labels 1, 2, 3…, by default, the first cell/junction’s address is A1, the next one is B1, and so on…

Excel follows the ‘PEMDAS’ rule, i.e., (We can use BODMAS rule too)

• 1st priority to the Parentheses ‘()’
• 2nd priority to the Exponent ‘^’
• 3rd and equal priority to the Division ‘/’ and the Multiplication ‘*’
• 4th and equal priority to the Addition ‘+’ and the Subtraction ‘-’

In Excel, we can perform the insertion and deletion operations in the following ways

• Shifting cells to the right
• Shifting cells up
• Shifting cells to left
• Shifting cells down
• Inserting/Deleting entire row
• Inserting/Deleting entire column

In excel, the cells can be formatted using the main attributes and their elements such as:

• Number
• Alignment
• Font
• Border
• Fill
• Protection

In Excel, to add a comment to a cell, all you need to do is to give a right-click on the cell and then select the ‘insert the comment’ option. You can always edit or delete the comment you entered.

To make it easy for recognition, the cell in which you add a comment will be marked with a red/purple flag on the top right corner of the cell.

The cell with the red mark in the L column represents that the cell has a comment.

In Microsoft Excel, Ribbon is a header or topmost part that contains a set of buttons and tabs through which you can navigate to perform certain commands. They help in understanding and using the commands easily and quickly, and each component is further divided into a group of commands making up a category.These are the generally asked Excel Interview Questions and Answers for the fresher candidate in the Interview.

Mainly, there are 4 basic components present in the ribbon, namely:

• Tabs
• Dialog launchers
• Groups
• Command Buttons

On one sight, we find 7 tabs in the ribbon. However, each tab has several other commands within. The 7 tabs are:

• Home
• Insert
• Page Layout
• Formulas
• Data
• Review
• View

The freeze pane helps in locking the cells which may be the headers of the rows or columns or any other data in any of the cells such that, even when we roll down the sheet to a larger extent, the frozen cells are always visible up there displaying on the sheet.

Select the cell which you want to lock up so that it could be visible on the sheet even if you scroll down. Then follow the path:

Select View -> Go to Freeze Panes -> Choose Freeze Panes.

By using the following techniques, we can enable security to Excel.

• Via password to open a workbook
• Against the manipulations being done on the worksheet.
• Protecting the window positions/ sizes from getting modified.

Relative Cell Address is the address or reference of the cell which has been modified and replaced either by using an automatic filling feature or by copying.

In some cases, the cell address should remain unchanged, when the autofill or copy happens. Such an address is called an absolute cell address and ‘$’ is used to keep the cell’s address constant.

To protect the worksheet’s cells from being copied, all you need to do is to go for the menu bar of ‘Review’, click on the Protect Sheet, and then give a password to the worksheet.

File tab -> Options -> Trust Center -> Trust Center Settings -> Macro Settings -> (Choose the selections based on your need) -> Click OK

The path goes as:

Select a range to be named -> formulae from ribbon -> Define name -> Provide the name. The above are the frequently asked Excel Interview Questions and Answers for fresher candidate in and Interview.

• Tabular format
• Compact format
• Report format

Data in Ribbon -> Data Validation -> From Allow dropdown, select List option -> Add the values as per your choice to the list under the source field.

A pivot table can be simply called as a summary table of the data of a table with huge data. It helps you to report and explore the data based on the information given.

Pivot tables help us in keeping a track of the comparisons and statistics like summarize, group, count, total, sum, averages, sort, etc. of the table that contains a huge amount of data. The tables on which a pivot table can be made can be a database or a spreadsheet. The pivot table helps in transposing the data of the table too.

The following are steps one should follow to create a pivot table.

• Select the desired cells in the sheet.
• Click ‘Insert tab’
• Select ‘Pivot Table’

• Values Area
• Rows Area
• Column Area
• Filter Area

Yes. The data in the pivot table gets auto sorted. To stop this, we need to follow the pattern given below.

Dropdown menu of row or column labels -> Go to -> More Sort Options -> Right Click ‘Pivot tables’ -> Select ‘sort menu’ -> Select ‘More Options’ -> Deselect ‘Sort automatically’.

Here are a few of the characteristics of the Pivot Table.

• Easy comparison of the data
• We can make customized proper reports
• We can add links to other data sources
• Numerous mathematical functions can be used to make operations
• Different view analyzation can be done on the data
• Determining the relations and data movements can be done.

The representation of the pivot table in the image format is known as the pivot chart. The information on the pivot table can be represented in the image format as a pie chart or a bar chart or area, etc.

Yes. It is possible. But all those extensible tables should be in the same spreadsheet.Advanced Excel Training in Chennai at FITA provides in-depth training of the Excel concepts under the guidance of real-time professionals.

Yes. It is possible. But all those sheets should be in the same workbook.

A formula is an expression that helps in performing a specific operation upon a cell or a group of ranged cells.

If B1+B2+B3+B4+B5 is the formula taken, it performs the sum operation on the values present in the cells ranging from B1 to B5.

• COUNT
• IF
• SUM
• COUNTIF
• AVERAGE
• VLOOKUP
• SUMIF
• MIN
• MAX
• INDEX and MATCH
• SUMPRODUCT

COUNT function returns the number of numbers present in the cells in the given range.

IF is a conditional function, where you will give a condition and pass a value in a cell or a group of cells to check whether they pass the condition. If they pass the condition, it becomes true and one value will be returned. If they fail at the condition, it becomes false and the other value will be returned.

The AVERAGE function takes the number values in the cells of a given range, calculates their average, and returns the resulting average value.

• Database
• Precedence
• Financial
• Lookup and Reference
• Information
• Math and Trig
• Date and Time
• Logical
• Text

• COUNT
• COUNTA
• COUNTIF
• COUNTBLANK
• COUNTIFS

COUNTIF returns the no.of cells within the range that are satisfying the condition mentioned (Only one condition should be given.) and its syntax is =COUNTIF (range, “condition”) These are the generally asked Excel Interview Questions and Answers for the Freshers and Experienced candidates during the interview.

COUNTIFS is a function just like COUNTIF, but the main difference is, it takes multiple ranges and checks the corresponding conditions with those values. It returns the count of the cells that have met the condition. Syntax is =COUNTIFS ( range1, criteria1, [ range2 ], [ criteria2 ], …)

COUNTA is a COUNT function that returns the total no.of filled (non-empty) cells that are present in the range of cells placed in the function as a parameter Its syntax is as =COUNTA (val1, [val2], [val3] …)

COUNTBLANK is a COUNT function that works exactly opposite to the work done by the COUNTA function. It returns the count of the empty cells present in the given range of cells. Its syntax is =COUNTBLANK (range)

Flash fill makes the cells autofill by comparing the pattern of the given or already filled up data in the cells before. It helps you to combine or extract or transform the data based on the patterns.

In Excel, select the cell in which you want to insert a hyperlink. One way to create it is, click on the Insert tab and then click on Hyperlink. The other way is to click Ctrl+K and give the link address.

There are three wildcards present in Excel. They are:

• Tilde (~)
• Question mark(?)
• Asterisk (*)

A function is a predefined/built-in operation that can take a declared number of arguments/range. While the formula is an expression defined by the user/built-in to perform a certain computation or a task. Formula: =A5+A7, Function: =SUM(A1:A5).

UDF is the abbreviation for User Defined Function, which means a function that is defined by the user with his logic on his own, to get the desired output. It is created using VBA.

VBA is the abbreviation of Visual Basic for Application, is a programming language that is used in Excel, to create Macros, message box, workbook, ranging objects, etc.

As per 2019’s version,

• The number of rows = 1,048,576 with a height of 409 points
• The number of columns = 16,384 with a column width of 255 characters.

These are the commonly asked Excel Interview Questions for freshers & experienced candidates in an interview.

Select the targeted row -> Right-click -> Insert -> Insert row. There you will see a new row inserted just above the row you have targeted, which means that the new row has taken the place of your target, successfully.

The following are the charts you will find in Excel.

• Column Chart
• Line Chart
• Pie Chart
• Doughnut Chart
• Bar Chart
• Area Chart
• XY (Scatter) Chart
• Bubble Chart
• Stock Chart
• Surface Chart
• Radar Chart
• Combo Chart
• Gantt chart

A Spreadsheet is a single working sheet, which is an intersection of rows and columns where each intersected portion is considered as a cell. So, simply, Spreadsheet is a huge group of orderly aligned cells in which you can place the data, where each cell is assigned with a unique address.

This function is to replace the old data in a cell with new data.

Its syntax is SUBSTITUTE(text, oldText, newText, [instanceNumber])

Formulas are also used to perform calculations in spreadsheets. Some of the formulas are like add, sub, mul, div, average, etc are performed on your data. All you need to do is to select the cells and perform the function upon them like fx=A1+A2.

• For Next Loop
• Do While Loop
• Do Until Loop
• For Each Next Loop

Wrapping the text helps in avoiding the text going out of the cell, like, it stops the overflow of the text. It helps in fitting the text in a single cell, where the height of the cell can be flexible.

Select the cell -> Home tab -> Click the ‘Wrap text’ option in the Alignment group for once.

Wrap text is a toggle button, which means that if you click it twice, it will come back to its place.

Select the cells -> Home tab -> Click ‘Merge and Center’ option in the Alignment group.

But the ‘Merge and center’ option isn’t much ideal as it doesn’t sort the resulting cells properly. So, to avoid this, to get the things in the right way, here is one more awesome way.

Select the cells -> Right click -> Format cells -> Alignment tab -> Horizontal list -> Center Across Selection -> Ok.

‘Format Painter’ is used to copy the presentation or format from a cell and then apply the same format on another cell or a group or range of cells. The above are the Important Excel Interview Questions and Answers that are asked to a candidate in an Interview.

If you want to clear all the styles like font, color, etc. taking the text into its plain form, you should use the ‘Clear Formats’ option.

Select the cells -> Home tab -> editing group ->clear drop down -> Clear Formats.

If we want to delete the formatting, contents, hyperlinks, comments, etc from the cells we have chosen in the sheet, we have to use the ‘Clear All’ option.

Select the cells -> Home tab -> editing group ->clear drop down -> Clear All.

Conditional formatting is a format that allows you to do a manipulation on the content of the cell based on the condition given. For example, if you select a group of cells and given a condition that the font-color of the data in the cells which are having value>15 should be changed to red, the values that satisfy the condition will be turned to red color.

We can achieve this using conditional formatting.

Select a range of cells which you need to check -> Home tab -> Conditional Formatting option -> Highlight cell rules -> ‘Less Than’ option -> Specify the value as ‘0’ in the ‘Less than’ dialog box generated and then, formatting is done.

We can achieve this using conditional formatting.

Select a range of cells which you need to check -> Home tab -> Conditional Formatting option -> Highlight cell rules -> ‘’Duplicate Values’ option.

We mainly find 6 different errors in Excel. They are:

• #N/A Error
• #DIV/0! Error
• #VALUE! Error
• #REF! Error
• #NAME Error
• #NUM Error

We can achieve this using conditional formatting.

Select a range of cells which you need to check -> Home tab -> Conditional Formatting option -> New Rule -> New Formatting dialog box pops -> Select ‘Use a formula to determine which cells to format’ option -> enter =ISERROR(A1) in the formula field (If, In the selection, A1 is the active cell) -> Format -> Specify the color -> Ok.

Yes. We can.

The first way is to change the font color into white which makes the text appear as if it is invisible.

The best way is to follow this format: Select the cell -> Ctrl+1 -> Opens the Format Cells dialog box -> Custom option -> Type ;;; in the custom field. That’s it!

It is one of the six major errors we encounter often in Excel. It is that “Value Not Available” Error that occurs when you use a lookup formula that can’t find a value thus making the value not available.

#DIV/0! Error is an error that occurs when we divide any number with 0. This is known as Division error and occurs as the division of any number with zero results undefined as output.

#VALUE! Error is an error that occurs when we use a wrong or incorrect data type in the computing expression which can be a formula o a function.

#REF! Error occurs when the address or the reference of the formula is no longer valid. This happens when the reference of the cell or cells doesn’t exist anymore which might be due to their deletion in the meanwhile from the worksheet.

The occurrence of this #NAME Error happens when you misspell the name of the function.

Number error occurs when to try and attempt to calculate or make the computations using very large numbers. For example, =2963^437 returns a number error.

We can achieve this (highlighting the cells which contain errors) using the ISERROR function in conditional formatting. Applying this function on cell results in the value ‘TRUE’ if there is an error, else, it returns ‘FALSE’. IFNA function helps in tackling the #N/A Error.

• TODAY()
• NOW()

We can combine the text from the number of cells using three functions, namely:

• TEXTJOIN function
• CONCATENATE function
• The ampersand (&) operator

We can find the length of the text in a cell using the LEN function, which is a built-in function that returns the length of the string. All you need to do is to select the cell and apply the formula =LEN(Address of the cell) on it.

The syntax of the VLOOKUP function is:

=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

This can be achieved by using the TRIM function, whose syntax is =TRIM(cell_address). However, it won’t remove a single space between words and can’t be able to remove nonprinting characters such as line breaks. The above are the frequently asked Excel Interview Questions and Answers for the Freshers and Experienced candidate in an Interview.

This can be achieved using the combination of TRIM and CLEAR functions together. The syntax is =TRIM(CLEAN(cell_address))

• Can’t be used if the lookup value is on the rightmost column. It should always be on the leftmost one.
• It would give us a wrong computation value or result if we do manipulation on columns like adding or deleting the columns.
• It makes the workbook go slow when we go for the usage of large data sets.

It is used to get the computations done on the group of cells to get the variety of subtotals in the form of AVERAGE, MAX, STDEV, MIN, COUNT, etc based on the need. It will give us the update of the result from visible cells only, automatically.

A volatile function is a function that helps in recalculating the formula repeatedly, especially when the changes are made on the worksheet. However, due to the repeated computations, the workbook will be slowed down when this function is called.

• RAND(), NOW(), TODAY() are considered as the highly volatile functions.
• OFFSET(), CELL(), INDIRECT(), INFO() e are considered as almost highly volatile functions.

We can convert Excel into a PDF in the following ways.

• Through Excel feature
• Through offline external software
• Through Excel online converters

Here are those lists of short-cuts.

• Undo – Ctrl+Z
• Redo – Ctrl+y
• Bold – Ctrl+B

Here are those lists of short-cuts.

• To group the rows or columns – Alt+Shift+right arrow
• To ungroup the rows /columns – Alt+Shift+left arrow

The HLOOKUP function looks up for the value, horizontally, starting from the topmost or the first row, and keeps on going in the downward direction of the sheet.

The syntax is: HLOOKUP(lookup_value,table_arry,row_index_num,[range_lookup])

In Excel, Instead of using the address (reference) of the cell especially, we can directly use the table name or column name as the references. Such references are called Structured References.

Based on the date wise records, we can divide the group dates into the following segments in Excel:

• Years
• Quarters
• Months
• Weeks
• Days
• Hours / Minutes / Seconds