WallStreetMojo

WallStreetMojo

WallStreetMojo

MENUMENU
  • Free Tutorials
  • Certification Courses
  • Excel VBA All in One Bundle
  • Login
Home » Excel, VBA & Power BI » Excel Tutorials » Excel Commands

Excel Commands

List of Top 10 Commands in Excel

Be it engineering, medical, chemistry or any field excel spreadsheet is the common tool for data maintenance. Some of them use for just maintain their database and some others use this tool as a weapon to turn fortune for the respective companies they are working on. So you too can turn things around for yourself as well by learning some of the most useful excel commands.

You can download this Commands in Excel Template here – Commands in Excel Template

#1 VLOOKUP Function to Fetch Data

Having the data in multiple sheets is a common thing in many offices, but to fetch the data from one worksheet to another and also from one workbook to another is a challenge for beginners in excel.

If you have struggled to fetch the data, then VLOOKUP will help you to fetch the data. Assume you have two tables below.

Excel Commands Example 1

In table 1, we have the subject list and their respective scores, and in table 2, we have some subject names, but we don’t have scores for them. So using these subject names in table 2, we need to fetch the data from table 1.

  • Ok, let’s open the VLOOKUP function in E2 cell.

Excel Commands Example 1-1

  • Select the lookup value as a D3 cell.

Excel Commands Example 1-2

  • Select the table array as A3 to B8 and make them absolute reference by pressing the F4 key.

Excel Commands Example 1-3

  • 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 fetch the data.

Excel Commands Example 1-4

  • For the last argument range lookup, select FALSE as the option, or else you can simply enter 0.

Excel Commands Example 1-5

  • Close the bracket and hit the enter key to get the score of Sub 4 and also copy the formula and paste to the below cell as well.

Excel Commands Example 1-6

Here you go, you have learned a formula to fetch values from different tables based on a lookup value.

#2 IF Condition to Do Logical Test

IF condition will be your friend in many situations because of its ability to conduct logical tests. Assume you want to test the scores of students and give the result, below is the data for your reference.

Excel Commands Example 2

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

  • Open IF condition in C2 cell.

Excel Commands Example 2-1

  • 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.

Excel Commands Example 2-2

  • The next argument is value if true, i.e., if the applied logical test is TRUE, then what the value we need is? In this case, if the logical test is TRUE, then we need the result as “Pass.”

Excel Commands Example 2-3

  • So the final part is value if false, i.e., if the applied logical test is FALSE, then we need the result as “Fail.”

Excel Commands Example 2-4

  • Close the bracket and fill the formula to the remaining cells as well.

Excel Commands Example 2-5

So Student A & F scored less than 35, so the result has arrived as FAIL.

#3 CONCATENATE Function to Combine Two or More Values

If you want to combine two or more values from different cells, then we can use the CONCATENATE function in excel. Below is the list of First Name and Last Name.

CONCATENATE Function Example 3

  • Open CONCATENATE function in the C2 cell.

CONCATENATE Function Example 3-1

  • For the first argument, Text 1 select the first name cell, and for text 2, select the last name cell.

CONCATENATE Function Example 3-2

  • Ok, apply the formula to all the cells to get the full name.

CONCATENATE Function Example 3-3

  • If you want space as the first name and last name separator, then use space character in double-quotes after selecting the first name.

CONCATENATE Function Example 3-4

#4 Count Only Numerical Values

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

Count Function Example 4

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

Count Function Example 4-1

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 cell A4 & A6, we have text values, but in cell A8, we have date value. COUNT function treats the date also as a numerical value only.

Note: Date and Time values are treated as numerical values if the formatting is correct; otherwise, it will be treated as text values.

Popular Course in this category
Sale
All in One Excel VBA Bundle (35 Courses with Projects)
4.9 (1,353 ratings)
35+ Courses | 120+ Hours | Full Lifetime Access | Certificate of Completion
View Course

#5 Count All Values

If you want to count all the values in the range, then you need to use the COUNTA function. For the same data, I will apply the COUNTA function and see the count.

Count All Values Example 5

This time we got the count as 8 because the COUNTA function has counted all the cell values.

Count All Values Example 5-1

Note: Both COUNT and COUNTA functions ignore blank cells.

#6 Count Based on Condition

If you want to count based on condition, then you have to use the COUNTIF function. Look at the below data.

Excel Commands Example 6

From this city list, if you want to count how many times “Bangalore” city is there, then open the COUNTIF function.

Excel Commands Example 6-1

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

Excel Commands Example 6-2

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

Excel Commands Example 6-3

Bangalore has appeared 3 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 you want to count the number of characters in the cell, then we need to use the LEN function in excel. LEN function returns the number of characters from the selected cell.

Excel Commands Example 7

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

Excel Commands Example 7-1

Note: Space is also considered as one character.

#8 Convert Negative Value to Positive Value

If you have negative values and you want to convert them to positive, the ABS function will do it for you.

Excel Commands Example 8

#9 Convert All Characters to UPPERCASE Values

If you want to convert all the text values to UPPERCASE, then use the UPPER formula in excel.

Excel Commands Example 9

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

Excel Commands Example 9-1

#10 Find Maximum and Minimum Values

If you want to find maximum and minimum values, then use MAX and MIN functions in excel, respectively.

Find Max & Min Example 10

Things to Remember

  • These are some of the important formulas/commands in excel which are used regularly.
  • We can use these functions in the advanced level as well.
  • There are move advanced formulas in excel which come under advanced level courses.
  • Space is considered as one character.

Recommended Articles

This has been a guide to Excel Commands. Here we discuss the list of top 10 commands in excel along with examples and a downloadable template. You may learn more about excel from the following articles –

  • Break-Even Point in Excel
  • Basic Excel Formulas List
  • Create Custom Functions in Excel
  • Write Formula in Excel
7 Shares
Share
Tweet
Share
All in One Excel VBA Bundle (35 Courses with Projects)
  • 35+ Courses
  • 120+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>
Primary Sidebar
Footer
COMPANY
About
Reviews
Contact
Privacy
Terms of Service
RESOURCES
Blog
Free Courses
Free Tutorials
Investment Banking Tutorials
Financial Modeling Tutorials
Excel Tutorials
Accounting Tutorials
Financial Statement Analysis
COURSES
All Courses
Financial Analyst All in One Course
Investment Banking Course
Financial Modeling Course
Private Equity Course
Venture Capital Course
Excel All in One Course

Copyright © 2021. CFA Institute Does Not Endorse, Promote, Or Warrant The Accuracy Or Quality Of WallStreetMojo. CFA® And Chartered Financial Analyst® Are Registered Trademarks Owned By CFA Institute.
Return to top

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Book Your One Instructor : One Learner Free Class
Let’s Get Started
Please select the batch
Saturday - Sunday 9 am IST to 5 pm IST
Saturday - Sunday 9 am IST to 5 pm IST

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Login

Forgot Password?

WallStreetMojo

Download Commands in Excel Template

New Year Offer - All in One Financial Analyst Bundle (250+ Courses, 40+ Projects) View More