EXCEL INTERVIEW QUESTIONS AND ANSWERS 2020

 

Most Commonly Asked Excel Interview Questions

 

Knowledge of Excel is one of the most valued skill an organization demands. Lacking Excel skills can lead to lesser chances of clearing an interview. There are a lot of ways by which the candidates can improve their knowledge about Excel and prepare for the Excel Interview Questions. There are numerous kinds of questions that the candidate can prepare for. To begin with, Excel software has a lot of benefits and is a special program meant for the purpose of documentation of information along with the analysis. It comprises of a lot of rows and columns. The point where the rows and columns in excel meet is known as cell. The cell carries a single point of data or the information in a single piece. This makes the user to locate the data easily in Excel. This also proves to be beneficial in gathering information from altering of data.

 

Now let’s dive into the basics of Excel,

 

Basic Excel Interview Questions with Answers

 

The Excel Interview Questions that a candidate should stay prepared for are:

 

1. How can you define Microsoft Excel briefly?

Microsoft Excel is a software or application in the computer in the format of a spreadsheet which lets the user to store data by converting it into the form of table. The following software was innovated by Microsoft and has now become one of the most necessary software. The following can be utilized on numerous operating systems like Windows, macOS, IOS and Android.

Necessary features that Microsoft Excel carries are:

• The following software carries graphics tools
• The following software carries inbuilt functions like SUM, COUNTIF, DATE and others.
• The following software lets the user to make proper analysis of information by tables, filters and charts.
• The following software features the availability of VBA or the Visual Basic Application
• It also has flexible worksheet operations along with flexible worksheet operations
• The following also lets the data validation in a simple manner

 

2. What can a person understand from cells in an Excel sheet?

Cell is the portion in the Excel which witnesses the intersections of columns and rows in a table. The place where the data needs to be inserted is known as a cell. The amount of cells present in a single excel sheet are 1,048 x 16,384.

This is one of the most commonly asked Excel Interview Questions.

3. How can you explain spreadsheet?

The following can be termed as the gathering or the cluster of cells which allow the user to manage or arrange the data in a proper manner. There can be more than one worksheet in a one workbook. The sheets can be checked at the lower part of the window with the names by which they are assigned.

 

4. How can you define cell address?

In an excel sheet, we can say that the cell address can be termed as the address which the user can get by joining the number of rows and columns alphabet. In Microsoft Excel, each and every cell will carry another particular cell address.

 

5. Can cells be added?

Definitely, a lot of new cells can be added to an Excel Sheet. There is a method by which we can add cells anywhere we want to. For this, the user has to select the option of adding a new cell and then choose the cell where the user wants new cells to be inserted. The user has to then click on the insert option to end the task.

There is a probability that the user can face these kinds of Excel Interview Questions in his interview. He is required to prepare for the following properly.

 

6. Can Microsoft Excel cells be formatted? If you agree with this fact, then say how?

Definitely, it is very easy to format the cells in Excel. The user can make use of the commands available in the Font group of the Home tab. The user can find a lot of options there. The options are:

• Name: the following option lets the formatting cells to mould into any category like accounting, date, percentage, currency, etc.
• Alignment: the following option lets the user to control texts and it also helps to set the direction according to the choice of the user.
• Font: the following option allows the user to use fonts of different size and colors and also lets the user to use fonts of any size.
• Border: the following option allows the user to alter the borders of the cell. The following option can also change the color of the border and remove it entirely.
• Fill: the following option lets the user to choose the color according to his choice. The user is also allowed to choose any style he wants to cover up the whole cell.
• Protection: the following cell allows the user to hide cells or lock them.

This is one of the most frequently asked and crucial Excel Interview Questions.

 

7. Can comments be added to a cell? If yes, then how?

Absolutely, we can add comments to a cell. We can do this by choosing the option of New Comment after selecting the entire cell. The user can find the following after by clicking the right mouse button after selecting the cell. The users who have the access to Excel sheet will be able to find the following option.

8. Is it possible to add new rows and columns to the Excel sheet?

It is possible to add new rows and columns to an Excel sheet. A user can add new rows and columns by clicking the right mouse button on the page after selecting the place where the following needs to be added.  The user then needs to select the option of insert which will allow them to select a whole column or a row.

 

9. How can you define ribbon and where to locate it?

The main interface of the user with Excel is known as Ribbon. The following can be found on the top of the Excel window. The following interface lets the user to gain access to numerous crucial commands directly. The following features various tabs like File, View, Insert, Home and all. The user is also allowed to configure the ribbon according to his requirements. The user can do the following by clicking the right mouse button after selecting the option of “customize the ribbon”.

 

10. How can panes be frozen in Excel?

The user can use MS Excel to freeze panes which would allow the user to find the headings of the columns as well as the rows if he scrolls a long way down the sheet. The panes in Excel can be frozen by the following ways:

• Selecting the columns and rows which require freezing.
• The user then needs to select the option of freeze panes in the view tab.
• The user will then find three options to freeze the columns and rows which include “freeze panes”, “freeze top row” and “freeze first column”.

 

11. How can a note be added to the cell?

The user is required to add select the cell in which a note is to be added and click the right mouse button on the following cell. The user then needs to select the option of New Note and type any note according to his choice. The user can click on the delete note option if he seeks to delete the following note. The user can detect the notes by a red triangle at the uppermost corner of the cell.

 

12. How can workbooks be protected in Excel?

It is not difficult to protect workbooks in the cell. The user can find three options for doing the following. They are:

• The user can set up passwords to open the workbooks.
• The user can also protect sheets from being added up or being deleted. The sheets can also be prevented from being hidden or unhidden.
• The user can protect the size of the window or the position of the windows from being changed.

 

13. How can a single format be applied to the sheets present in a workbook?

It is very easy to apply same format to all the sheets of a workbook. The user can do this by the following ways:

• The user can click the right mouse button on any sheet of the workbook according to his choice.
• The user then needs to click on the option of “select all sheets”.
• The user now becomes capable of formatting any sheet according to his choice. The user can mark that the same format automatically gets applied to all the other sheets of the workbook.

 

14. What idea do we make about Relative Cell Addresses when we hear about that?

Whenever formulas in Excel are copied, the reference cells automatically get modified. They modify in order to match the position where the formulas are copied. The system responsible for doing the following is called as Relative Cell Address.

This is one of the Excel Interview Questions which cannot be neglected.

 

15. What the user needs to do in a situation where the cell addresses are copied but the user doesn’t need to modify them?

If the user doesn’t wish to modify the cell addresses and still copy the formulas, then he needs to utilize the Absolute Cell Addresses. When the Absolute Cell References are used the addresses of rows and columns do not get modified and they remain the same.

 

1. If the user doesn’t need to both row and column but only one of them, then what is the thing they need to do?

If the user wishes to use the change only the row or the column, then he has to utilize the Mixed Cell Addresses. The following allows the user to change a particular column or row but this cannot help them to change both of them at a time. This is one of the important questions they need to practice for the Excel Interview Questions.

 

1. Is it possible to protect the cells of a sheet from being copied?

Absolutely, it is possible to save the cells of the sheets from being copied. The user can even protect the cells of the sheet along with the columns from being copied. The user has to do this by following these steps:

• The user needs to first choose the cells they want to prevent from being copied.
• The user then needs to open the Font window which he can find in the Home Tab.
• The user has to then choose the protection option by going to the Protection Pane and then check the Hidden Box.
• The user then needs to go to the Review Tab which is present in the Ribbon. He then needs to select the option of Protect Sheet which will hide the cells of Excel.
• The last thing the user needs to do is to specify a password which would allow the user to unprotect the sheet afterwards.

 

1. How can named ranges be created?

It is not difficult to create named ranges. To create named ranges, the user needs to follow the following steps:

The user needs to select the particular area in which he wishes to put a name.

The user then needs to choose the “Formulas” option from the Ribbon.

The user then needs to choose the option of “Define Name” from the option of Defined Names.

The user then has to give any name which he wishes to.

 

1. How can we define macros?

We can define macros by saying that it is the recorder which allows the user to automate the tasks done on a regular basis in Excel by recording them in the following. It can also be called as an action or a cluster of actions which the user can perform numerous times. For instance, if it is necessary for the user to maintain a record of the cells on a regular basis at the end of the day, the user can make a macro to evaluate the cells automatically along with the profits and losses on regular basis. This recorded data can be used afterwards in case of queries instead of evaluating the data manually each and every day.

 

20. How can dropdown lists be created in Excel?

There are some steps which the user needs to follow to create dropdown lists. Those steps are:

• The user needs to click on the Data Tab which can be found in the Ribbon.
• The user then needs to choose the option of Data Validation by going to the “Data Tools group”.
• The user then needs to move to the settings option and click on “Allow” and then “List”.
• The last thing the user needs to do is to create an array of the source list.

 

Excel Interview Questions based on Pivot Charts and Pivot Tables

 

21. What are pivot tables and what are their features?

We can define Pivot Tables by saying that they are statistical tables which include extensive information in their condense data. The information in the table can be of any field such as averages, sales, totals, etc. All such information is represented in a pivot table in a very simple manner. The user also needs to represent all these information in a wise and planned manner. The features of Pivot Table are:

• The following allows the display of the particular data which the user needs to analyze.
• The following table gives numerous angles to the user to check the data.
• The following table allows the user to lay emphasis on the necessary details.
• It makes the data comparison extremely easy.
• The following also helps to detect numerous patterns, data trends, relationships and all.
• These tables can create data instantly.
• The following tables provide accurate data.
• The following tables build the base for Pivot Charts.

 

22. In what ways can Pivot Tables be created?

The user needs to arrange the data in a tabular form before creating a Pivot Table. There are certain points which the user needs to keep in mind when he prepares the data.

• The user needs to arrange the information in the form of columns and rows.
• The user has to keep in mind that there should be a unique heading in the first row of each column.
• There should be only one type of data in each column.
• The rows must feature data for single recording only.
• There should be no blank rows in the following table.
• The must be no blank columns in the following table.
• The data kept for creating the pivot table needs to be kept aside from the rest of the data present in the Excel sheet.

This is one of the important questions which are likely to come in the Excel Interview Questions.

 

23. How can Pivot Charts in Excel be defined?

We can define Pivot Charts as data visualization tools which helps the user to visualize the data in numerous ways. The Pivot Charts can be of any format such as Pie chart, Bar graph, Line, Doughnut and all. When the user wishes to make a pivot chart for the table, he needs to choose any cell from the table according to his choice and then needs to choose the Pivot Chart option from the Insert Tab. The user will then find a lot of options. The user needs to choose an option of his choice and then click OK to execute the following. The Pivot Charts can be easily formatted.

This is one of the easiest and important questions which the candidate might come across while preparing for the Excel Interview Questions.

 

24. Can Pivot Tables be created using numerous tables? If yes, then how?

Definitely, Pivot Tables can be created using numerous tables. There are certain steps which the user needs to follow to create Pivot Tables. Those steps are:

• The user needs to press “Alt+D” after going to Excel and then press “P” which would open the Pivot Table Wizard.
• The user then needs to choose the option of “Multiple Consolidation ranges” and then choose the “Next” option. The user can then find another dialogue box below.
• The user then needs to choose the option of “I will create the fields” and then click “Next”.
• The user is required to add ranges which are essential, in the next window.
• The user is required to click on the “Next” option after doing all the other things.
• The user needs to choose the particular area in which he needs to create the table and then choose the “Finish” option.
• Finally, the user can find that the Pivot Table is created by merging two or more tables together.

 

25. What are the consequences of checking the option of “Defer Layout Update” which is present in the Pivot Table Fields window?

When the user checks the option of “Defer Layout Update”, he doesn’t find any kind of drastic change when he alters the table fields within themselves. Generally, the following option remains unchecked or off. The drastic change will only take place after the user chooses the Update button while checking the following.

 

26. Is it possible to create Pivot Tables using tables of different worksheets? 

Continue Reading