11. What does AVERAGE function mean in Excel?
As the name suggests, the Average function finds the average, or the arithmetic mean of the numbers.
12. What do you mean by Formula and Function in Excel?
A formula is an expression that calculates the value of a cell and a function is a predefined formula that is already available in Excel. Function performs specific calculations in a particular order based on the specified values called parameters.
13. What is Freeze Panes in MS Excel? What steps do you use to freeze columns?
Freeze panes freezes or locks any row or column on the screen and still, it will be visible on the screen. Even if you scroll your screen horizontally or vertically you will be able to see those locked rows or columns.
In order to freeze columns, select the cell immediately to the right of the column you want to freeze. The next step is to go to the View tab, select the freeze panes command and choose "Freeze Panes".
14. Can you give examples of the perfect spreadsheets you have made?
With this question, the interviewer is trying to understand your past experience with spreadsheets. Before going for the interview, be prepared for this question so that you remember the best examples. He/she would like to know your enthusiasm for quantitative analysis.
Some of the examples where you have used application of Excel would be:
i.) Using Excel spreadsheet to show the data of the engineering training and placement officers across the different states for a Certification program.
ii.) Using IF statements preparing spreadsheets for automating day-to-day tasks.
iii.) Using Excel to track the progress of the project using project management dashboard across multiple work streams.
iv.) Using dashboards to track business metrics.
15. What are the advantages and disadvantages of the Pivot Table?
A pivot table allows the users to summarize or reorganize rows and columns of the data. Some of the advantages and disadvantages of the pivot tables are as follows:
Advantages of pivot table:
i.) A Pivot table provides deep insights into analytics data. It can generate multiple reports with the same collected data within a single file.
ii.) If the pivot table is used in MS Excel, then the tool is able to work with SQL export.
iii.) The data is easier to segment with the help of pivot table.
iv.) You can create instant data whether you program equations directly into the pivot table or rely on formulas.
Disadvantages of pivot table:
i.) The collected data needs to be manually calculated or equations must be manually put which is a time-consuming affair.
ii.) Programs with pivot tables do not provide a dashboard of information that can be easily summarized. There are no automatic updates available.
iii.) Older computers struggle to present the data. If the data is large the equipment crash as the requirements are too extensive for its processing power.
iv.) It may be easy to learn pivot table but it takes time to learn how to present the data in a usable way.
16. Suppose you have two columns with the first and last name of the clients but you want another column with the clients full name, how will you merge that as one?
In order to create a full name tab, you will have to insert a new column into your table. Click on the mouse and choose "Insert" from the context menu. Add a new column with the name "Full Name".
In the cell where you want to have the full name of the client, write the formula: =CONCATENATE (B2, ' ' ,C2). You will get the full name of the client in the cell adjacent to the cell of the first and last name.
B2 and C2 are the addresses of the first name and last name respectively. Note that there is space between the quotation marks ' ' in the formula. It is a separator that will be inserted between the merged names; you can use any other symbol.
17. Name the two macro languages in MS- Excel?
The two macro languages in MS Excel are:
i.) XML
ii.) VBA (Visual Basic Applications)
18. What is the number of data formats available in Excel?
There are eleven data formats available in Excel.
19.To wrap text within a cell what will you do?
In order to wrap text in a cell first, you will have to select the text you want to wrap. Then from the home tab, click on wrap text.
20. Can you put a password to protect your sheet?
To protect and secure your sheet from getting copied or used by others without your permission you can add a password. You need to go to the Menu bar - Select review - Select Protect Sheet-add the Password.
