Microsoft Excel for Beginner

MICROSOFT EXCEL FOR BEGINNER

Hello everyone, Today’s Blog is to understand Basic Excel Functions. The most important tool in the industry today is Excel, around 70-80% of industry relay of Excel for day to day Analysis/Work. One who is willing to Earn, I hope this blog will help you achieve it.

Lets jump on to the topic, few points to note:

  • In Excel, Text/String is aligned to left and Number/Numeric is aligned to the right
  • Understand the formula properly, to learn advance excel you should know the basic formula. This Blog will cover all basic and mostly used formula in day-to-day activities.
  • Excel formula starts with = ( Equal to), or else what you enter will be considered as text.
  • OUTPUT of all the functions is provided at the end of the Blog, but I would suggest parallel execute each and every step and see at the end if your able to get the right output

List of Function

1.CONCATENATE

2.LENGTH

3.TEXT

4.LEFT

5.RIGHT

6.MID

7.SUMIFS

8.COUNTA

9.COUNTBLANK

10.VLOOKUP

11.IF

In the below screenshot, you will find Column B ( Series) which is Text Column and is left-aligned, whereas Column E ( Profit in $) is Numeric Column which is right-aligned.

Before we begin would like to make a note that, to explain Basic Excel I have taken dummy data for the purpose of understanding and not the actual data.

Understanding Data is very important before you apply any formula, for that you should know, what is the objective? & then it will be easy to get insights out of data, in simple terms to get correct output first know your data correctly.

Data: To understand Excel, I have taken the example of Online Streaming Channel, the respective Series they show, and a few statistics on the number of users who watched that particular series, Rating of that series ( 1 lowest – 5 highest) & Profit Earned out of each series.Simple data and easy to understand.

Functions:

  • CONCATENATE
    • Formula: =CONCATENATE( TEXT1,TEXT2,TEXT3,.,TEXTN)
    • Output: Output will be Text.
    • Purpose: This function can be used to create a unique identifier or if you have name divided into 3 parts like First, Middle & Last Name, you can use concatenate function and get the full name and so on.

  • LENGTH
    • Formula: =LEN(TEXT)
    • Output: Output will be Numeric.
    • Purpose: To find the length of a particular String/Text.

  • TEXT
    • Formula: =TEXT(Value,Format_text), Where Value= Cell reference which you want to convert to text & Format_text= the format of text. In the below screenshot, I have converted a cell value to text, but if I want to enter a few more zeros in front of the value 200 then, the formula: TEXT(C10, “00000”) & Output:00200.
    • Output: Output will be String/Text.
    • Purpose: You might encounter a few cases like when you enter the account number in the system it might be Number but in some other system you want to enter it as String, in that case, the use of this formula is of big help.

  • LEFT
    • Formula:= LEFT(TEXT,NUM_CHAR)
    • Output: Trimmed String as output.
    • Purpose: A Full Name contains 3 parts First, Middle & Last name, if you want to retrieve only FirstName, then you will use Left Function.

  • RIGHT
    • Formula: =RIGHT(TEXT,NUM_CHAR)
    • Output: Trimmed String as output.
    • Purpose: A Full Name contains 3 parts First, Middle & Last name, if you want to retrieve LastName, then you will use Right Function.

  • MID
    • Formula: =MID(TEXT,START_NUM,NUM_CHAR)
    • Output: Centre Trimmed String.
    • Purpose: A Full Name contains 3 parts First, Middle & Last name, if you want to retrieve MiddleName, then you will use MID Function.

  • SUMIFS
    • Formula:=SUMIFS(SUM_RANGE, CRITERIA_RANGE1, CRITERIA1, CRITERIA_RANGE2, CRITERIA2..), where SUM_RANGE=the column on which we want to calculate the summation based on condition, CRITERIA_RANGE1= on which column we want to apply the condition, CRITERIA1= Specify the condition and so on. In our below example we have applied only one condition, you can apply multiple conditions as well.
    • Output: Output is Numeric, based on multiple conditions
    • Purpose: To find a total summation, based on conditions, it’s a very powerful function & will be used most often.

  • COUNTA
    • Formula: =COUNTA(VALUE1,VALUE2,….)
    • Output: Total count of values present in a range.
    • Purpose: When you have large Data( 1 million rows ) and you want to know, how many of them are Filled, this function will be very useful in that case.

  • COUNTBLANK
    • Formula: =COUNTBLANK(VALUE1,VALUE2,…)
    • Output: Total count of values present in a range.
    • Purpose: When you have large Data( 1 million rows ) and you want to know, how many of them are Empty, this function will be very useful in that case.

  • VLOOKUP
    • Formula: =VLOOKUP(LOOKUP_VALUE, TABLE_ARRAY, COL_INDEX_NUM,[RANGE_LOOKUP]), Where LOOKUP_VALUE=The Value which you want to Search, TABLE_ARRAY= From which table you want to search the value, COL_INDEX_NUM= which column you want to give as output, RANGE_LOOKUP= you the that you want an exact match or not. Note: The LOOKUP_VALUE that you want to search TABLE_ARRAY, should be present in the first column on the TABLE_ARRAY.
    • Output: The COL_INDEX_NUM will be returned as output.
    • Purpose: If you know the name of a person and you have huge data available & you know which column will have the Phone number of that person, you can easily retrieve it from the VLOOKUP function.

  • IF
    • Formula: =IF(LOGICAL_TEXT,[VALUE_IF_TRUE],[VALUE_IF_FALSE])
    • Output: Returns a value based on condition.
    • Purpose: This function can be used widely, and below is a simple example of the same.

OUTPUT OF ALL ABOVE FUNCTIONS

CONCLUSION

This was Excel basic, if you have command over basic functions then you can try nested functions, which are very useful in the advance calculation.

The most powerful feature of excel is PIVOT, will cover in separate Blog, if you have any query in above examples, kindly visit my YOU-TUBE channel(INDIANBLOGOFFICIAL) I have explained all of this in detail. Get in touch with me, in the Right bottom corner I have provided my social media link. To get notification of my new Blog kindly Subscribe (on right top Corner) & will notify you once I post new Blog on Advance excel.

If you do not have a Licensed Microsoft Excel version then you might not be able to leverage the advanced features of excel, Click below image to get Excel for Cheaper Rate.

Hope you all liked my Blog , keep reading my Blog and Stay Safe, Stay Healthy.Do not forget to Subscribe.

6 Replies to “Microsoft Excel for Beginner”

+ Leave a Comment