Frequently Asked Questions – Microsoft Excel Essay
Frequently Asked Questions – Microsoft Excel
Question: What is the utility of the If function in Excel?
Answer: If function is used to get output with some condition. The resultant value will depend upon whether the condition is evaluated to be TRUE or FALSE.
The syntax for the If function is:
If( condition, value_if_true, value_if_false )
condition is the value that you want to test.
value_if_true is the value that is returned if condition evaluates to TRUE.
value_if_false is the value that is return if condition evaluates to FALSE.
Question: What is the function of VLOOKUP?
Answer: The VLOOKUP function is used to search for specified data in the first column of a table of data. Once the specified data is found, it will return a result, on the same row, a specified number of columns from the first column. The V in VLOOKUP stands for “Vertical.”
The syntax for VLOOKUP is:
Lookup_value is the value to be found in the first column of the array.
It can be a value, a reference, or a text string.
Table_array is the table of information in which data is looked up. Use a reference to a range or a range name, such as Database or List.
Col_index_num is the column number in table_array from which the matching value must be returned.
Range_lookup is a logical value that specifies whether you want VLOOKUP to find an exact match or an approximate match.
Question: What is the function of HLOOKUP?
Answer: The H in HLOOKUP stands for “Horizontal.” This function looks for a value in the top row of a table or an array of values, and then returns a value in the same column from a row which has been specified in the table or array. HLOOKUP is used to compare values which are located in a row across the top of a table of data, and you want to look down a specified number of rows.
Question: Is it possible to determine the day of the week for a particular date?
Answer: Yes, and there are several ways to do so in Excel. The most common way is to use the date function WEEKDAY. Assume that cell A1 contains a date value. The formula below utilizing the WEEKDAY function, will return an integer between 1 and 7 (1 for Sunday, 2 for Monday, and so on).
If you want to see words rather than integers, modify the formula as follows:
=CHOOSE(WEEKDAY(A1), “Sunday”, “Monday”, “Tuesday”, “Wednesday”, “Thursday”, “Friday”, “Saturday”)
Question: The Auto Filter supports only two criteria. Can we use more than two criteria at the same time with some other function?
Answer: Auto Filter is a very popular option to use up to two criteria. In case, more than two criteria are required to be used, Excel’s Advanced Filter can be utilized. You can employ customized formulas in Advanced Filter. A criteria range can also be used which allows for more complex criteria to be filtered.
Question: Can we prevent Save As in Excel?
Answer: The code below can be used to stop any users saving a Workbook as another name. The code must be placed in the Private Module of the Workbook Object (ThisWorkbook). The fastest way to get there is to right click on the Excel icon, top left next to File and select View Code. It is in here you should place the code below and then save the Workbook. So long as the Workbook is opened with macros enabled the code will fire anytime any user tries to use Save As.
Private Sub Workbook_BeforeSave _
(ByVal SaveAsUI As Boolean, Cancel As Boolean)
If SaveAsUI = True Then Cancel = True
Question: How can we change the font color based on the value in the cell ?
Answer: If you wish to change the color of the font based on the value in a cell, you will need to apply conditional formatting. To do this, first select the cell (or multiple cells in rows and columns) that you wish to apply the formatting to.
Under the Format menu, select Conditional Formatting.
When the Conditional Formatting window appears, enter the first condition.
Next, we need to select what formatting to apply when this condition is met. To do this, click on the Format button.
When the Format Cells window appears, select the formatting conditions that you wish to apply. Then click on the OK button.
If you wish to apply more than one condition, you will need to click on the Add button and repeat the same procedure as explained above.
Question: What is the best utilization of SUMIF function?
Answer: In Excel, SUMIF function can be used to check whether the cells meet the specified condition before summing them up. Syntax:
This would SUM all numeric cells in the range B1:B10 where the corresponding row in A1:A10 was greater than 20.
Question: Can we use Excel VBA to send emails from within Excel?
Answer: Excel VBA allows us to send emails from within Excel and even we can send the Workbook as an attachment. The SendMail Method is very easy to use and will send any specified Excel Workbook as an attachment to specified recipients. As the code below specifies the Active Workbook the code is best stored in your Personal.xls
Recipients:=”[email protected]”, _
Subject:=”Try Me ” & Format(Date, “dd/mmm/yy”)
Question: In most cases, we sort rows by columns, but is it possible to sort columns by rows?
Answer: The following procedure sorts the order of columns.
Click a cell in the list you want to sort.
On the Data menu, click Sort.
Under Orientation, click Sort left to right, and then click OK.
In the Sort by and Then by boxes, click the rows you want to sort.
Microsoft Excel. (2002). Microsoft Corporation.
Cite this Frequently Asked Questions – Microsoft Excel Essay
Frequently Asked Questions – Microsoft Excel Essay. (2017, Jan 15). Retrieved from https://graduateway.com/frequently-asked-questions-microsoft-excel/