Question-Explain difference between function and formula in MS-Excel?
Function-A function is a standalone piece of code designed to calculate specific values and are used inside formulas.
Formula-A formula may be a function or combination of functions written by the user to be calculated.
Question-How do you split a column into 2 or more columns?
Text to column option in Data tab could be used to split the column into 2 or more columns.
Question-What is a Dashboard?
Dashboard is used to present important information through graphical representation. It is helpful in presenting huge data in a single computer screen so it can be monitored with a glance and some useful insights could be deduced from it.
Question-Define Vlookup and how to use it?
Vlookup is a lookup function used to find the data in the large spreadsheet by lookup value in another worksheet. To use the Vlookup function, we should have common values in both data which acts like unique key or primary key.
Syntax=VLOOKUP(lookup_value,table_array,col_index_num,[range_lookup])
Question-Explain Pivot table and why it is important?
Pivot table allows quick summarizing of large data. We could aggregate large numbers and do analysis in a very quick and efficient way. Pivot table takes care of several scenarios in a quicker way and could be customized rapidly to suit our requirements. Why do we use it?
- Pivot table gives us flexibility and analytical power
- It is a time saver source in Excel
- Making a dynamic pivot chart
- Could be linked to external data also and hence gives flexibility.
Question-Find day of the week for a particular date?
We could use Weekday function in excel to calculate day of the week. Example: In cell A1, we have entered today’s date and we want to return the weekday counting from Sunday. Steps to Follow
Enter the formula in Cell B1 WEEKDAY(A1,1) ,second argument (1) determines to what day excel will assign the value,press Enter.Formula will return 3, it means today is 3rd day of the week.
Question-What is nested function in excel?
As the name suggests, a function inside another function is called nested function.
Question-Formulas in Excel start with?
=(equal to sign)
Question-How do you write cell reference for cell range of A2 to H12?
A2:H12
Question-How do you enter current date and time in a cell?
=NOW() function will give us current date and time. If we just want to enter current time, use keyboard shortcut Ctrl+Shift+semicolon key.
Press Ctrl+Semicolon to enter current date in a cell in excel.
Question-How do you auto fit the width of column?
By Double clicking on column right border on column header
Question-By default Excel provides 3 worksheets. You need only two of them, how will you delete the third one?
Right click on Sheet Tab of third sheet and choose Delete from menu
Question-What are comments, how do you add comments to a cell?
Comments are added to any cell to provide any additional information; they are used for a variety of reasons. You can add comments to a cell to clarify the purpose of the cell, to clarify a formula used in the cell, or to leaves notes for other users about a cell.
To add a comment to a cell, right click on the cell and choose insert comment from the cell menu. Type your comment in the comment area provided. A red triangle at the top right hand corner of a cell indicates that there is a comment linked to that particular cell. To remove a comment from a cell, right lick the cell and then select delete comment from the cell menu.
Question-What does a red triangle at the top right of a cell denotes?
A red triangle in the cell indicates the comment associated with the cell. If you place mouse on it, it will show the comment.
Question-Define worksheets and how do you add or remove worksheets in excel?
A worksheet is a single page or sheet within a workbook in Excel also known as tab. When you start a new blank workbook, Excel automatically adds three blank worksheets.
To add a worksheet to the workbook you can click the insert worksheet tab at the bottom of the workbook. You can also insert a sheet using the SHIFT key and then pressing F11.
Question-What is absolute reference and relative reference in excel formula?
Relative cell references is default in excel, means the references change dynamically when they are copied and pasted in different places. e.g. if a formula with reference to cell A1 is copied and pasted one row down and two column right, the new reference will point to cell C2.
However, absolute cell references do not change when we copy the formula and paste them in any cell.
They will have dollar sign “$” in the reference. if a formula that refers to $A$1 is copied and pasted one row down and one two column right, the new reference will still point to cell A1 as both the row and column are locked. If a reference to cell $A1 is copied and pasted one row down and two column to the right, the new reference will point to cell C2 — only the row number will change, because the column letter is locked b6y dollar sign.
Question-In what format the data is stored when you insert an excel file into a Word document?
Stored in a word table
Question-How do you format the value of a cell?
Right click the cell and select Format Cells or Press Ctrl+1 to open the format cells menu and select the desired format from the menu on the left side.
Question-Can we hide the ribbon in excel?
Yes, we can hide the excel ribbon by pressing Ctrl+F1.
Question-What is Excel Macro and why do we use it?
Excel Macro is a program or set of instructions that is written or recorded to perform some task.
It helps user to automate some repetitive and lengthy task that is done on a regular basis.
