List Of Top 10 Commands In Excel With Examples

List of Top 10 Commands in Excel

#1 VLOOKUP Function to Fetch Data

The data in multiple sheets are common in many offices, but fetching the data from one worksheet to another and from one workbook to another is a challenge for beginners in Excel.

If you have struggled to fetch the data, VLOOKUPVLOOKUPThe VLOOKUP excel function searches for a particular value and returns a corresponding match based on a unique identifier. A unique identifier is uniquely associated with all the records of the database. For instance, employee ID, student roll number, customer contact number, seller email address, etc., are unique identifiers. read more will help you bring the data. For example, assume you have two tables below.

#2 IF Condition to Do Logical Test

The Excel IF condition can be your friend in many situations because of its ability to conduct logical tests. For example, assume you want to test the scores of students and give the result. Below is the data for your reference.

First, let us open the VLOOKUP function in the E2 cell. Then, select the LOOKUP value as a D3 cell. Next, we must select the table array as A3 to B8 and press the F4 key to make them an absolute reference. Column Index Number is from the selected table array from which column you need to fetch the data. So, in this case, from the second column, we need to bring the data. For the last argument range, LOOKUP, we must select FALSE as the option, or else we can enter. Close the bracket and press the Enter key to get the score of Sub 4. Also, copy the formula and paste it to the below cell. You have learned a formula to fetch values from different tables based on a LOOKUP value.

You have learned a formula to fetch values from different tables based on a LOOKUP value.

In the above table, we have students’ scores from the examination. So we need to arrive at the result as either “PASS” or “FAIL” based on these scores. So to reach these results criteria, if the score is >=35, the result should be “PASS” or else “FAIL.”

We must first open the IF condition in the C2 cell.
The first argument is logical to test.So, in this example, we need to do the logical test of whether the score is >=35, select the score cell B2, and apply the logical test as B2 >= 35.
The next argument is value if true. If the applied logical test is “TRUE,” what is the value we need? If the logical test is “TRUE,” we need the result as “Pass.”
So, the final part is value if false.If the applied logical test is “FALSE,” then we need the result as “Fail.”
Now, close the bracket, and we also need to fill the formula to the remaining cells.

So, students A and F scored less than 35. Therefore, the result has arrived as “FAIL.”

#3 CONCATENATE Function to Combine Two or More Values

If we want to combine two or more values from different cells, we can use the CONCATENATE function in excelCONCATENATE Function In ExcelThe CONCATENATE function in Excel helps the user concatenate or join two or more cell values which may be in the form of characters, strings or numbers.read more. For example, below is the “First Name” list and “Last Name.”

First, we need to open the CONCATENATE function in the C2 cell.
For the first argument, “Text 1,“ select the “First Name” cell, and for “Text 2,” choose the “Last Name” cell.
Then, we need to apply the formula to all the cells to get the full name.
If you want space as the “First Name” and “Last Name” separator, we can use the space character in double-quotes after selecting the first name.

#4 Count Only Numerical Values

If we want to count only numerical values from the range, you need to use the COUNT function in Excel. Take a look at the below data.

From the above table, we need to count only numerical values. For this, we can use the COUNT function.

The result of the COUNT function is 6. The total count of cells is 8, but we have got the count of numerical values as 6. In cells A4 and A6, we have text values, but in cell A8, we have date values. The Excel COUNT function treats the date also as a numerical value only.

Note: “Date” and “Time” values are numerical values if the formatting is correct. Otherwise, they will be treated as “text” values.

#5 Count All Values

If we want to count all the values in the range, we need to use the COUNTA functionUse The COUNTA FunctionThe COUNTA function is an inbuilt statistical excel function that counts the number of non-blank cells (not empty) in a cell range or the cell reference. For example, cells A1 and A3 contain values but, cell A2 is empty. The formula “=COUNTA(A1,A2,A3)” returns 2. read more. We will apply the COUNTA function for the same data and see the count.

We got the count as 8 because the COUNTA function has counted all the cell values.

Note: Both the COUNT and COUNTA functions ignore blank cells.

#6 Count Based on Condition

If we want to count based on condition, we can use the COUNTIF functionUse The COUNTIF FunctionThe COUNTIF function in Excel counts the number of cells within a range based on pre-defined criteria. It is used to count cells that include dates, numbers, or text. For example, COUNTIF(A1:A10,”Trump”) will count the number of cells within the range A1:A10 that contain the text “Trump” read more. For example, look at the below data.

From this “City List,” if we want to count how many times “Bangalore” city is mentioned, we must open the COUNTIF function.

The first argument is “RANGE,” so we need to select the range of values from A2 to B9.

The second argument is “Criteria,” i.e., what you want to count, i.e., “Bangalore.

Bangalore has appeared three times in the range A2 to B9, so the COUNTIF function returns 3 as the count.

#7 Count Number of Characters in the Cell

If we want to count the number of characters in the cell, we need to use the LEN function in excelLEN Function In ExcelThe Len function returns the length of a given string. It calculates the number of characters in a given string as input. It is a text function in Excel as well as an inbuilt function that can be accessed by typing =LEN( and entering a string as input.read moreel. The LEN function returns the number of characters from the selected cell.

“Excel” has 5 characters, so the result is 5.

Note: Space is also considered as one character.

#8 Convert Negative Value to Positive Value

If we have negative values and want to convert them to positive ones, the ABS functionABS FunctionABS Excel function or Absolute function is used to calculate the absolute value of a given number. The negative numbers given as input are changed to positive numbers and if the argument provided to this function is positive, it remains unchanged.read more can do it for us.

#9 Convert All Characters to UPPERCASE Values

If we want to convert all the text values to UPPERCASE, we can use the UPPER formula in excelUPPER Formula In ExcelUppercase function in Excel is used to convert lowercase text to uppercase.read more.

And if we want to convert all the text values to LOWERCASE values, then use the LOWER formula.

#10 Find Maximum and Minimum Values

If we want to find maximum and minimum values, we may use MAX and MIN functions in excelMIN Functions In ExcelIn Excel, the MIN function is categorized as a statistical function. It finds and returns the minimum value from a given set of data/array.read more, respectively.

Things to Remember

These are some of the important formulas/commands in excel which are used regularly.We can also use these functions at the advanced level.There are more advanced formulas in Excel which come under advanced level courses.Space is considered one character.