Excel Formulas

Doston niche kuch aise Excel Formulas in Hindi diye gaye hain, jinka upyog aap Excel mei kahi par bhee kar sakte hain. Ye vo Formulas hain jo ki aapko 75% Excel me Master banne ke liye Ready kar denge. In Excel Formulas ko Learn karte he hogi aapki Time ki bachat aur ban jaayein the Ultimate Rockstar apne doston aur colleagues ke beech.

Now, Learn Excel Formulas in Hindi

Sum Formula – Excel Formulas in Hindi

Sabse pehle hum dekhte hain Sum Formula. Ye Generic Formula hai, Jo ki aap sabko aana he chaahiye. Ye kaafi easy hai.

SUM(number1, [number2], …)

Sum Formula-this Function, as the name suggests is used to sum up a group of Numbers in a Particular Column. This is complication free. You can easily find the Sum Function on your screen. The Function Button is there on the Home Tab. All you have to do is, select the part of the column and let the dedicated Button sum up the group of Numbers. For Example:

Excel Formulas in Hindi

Tip 1: By going on Home Tab, Click on AutoSum at the Right hand side
Tip 2: By going in the Formula Bar
Tip 3: By applying the SUM Formula Directly

With these Three tips you can quickly SUM a Column and even Row of Numbers. All you have to do is, select the cell next to the numbers you wish to SUM. 

Related Formulas

Average Formula

Then comes the Average Formula. Jaisa ke aap sabko yaad hee hoga, jab kabhi bhee aapke saamne kuch nos. kaa samuh hota thaa aur uska Average nikalna hota tha, toh Average Formula kaa use karke Average nikaal liya jaata thaa. Usi tarah, Excel in Hindi mei ye Average Formula ka use hota hai.

AVERAGE(number1, [number2], …)

This Formula will guide you through in case you were searching for how to Calculate Average in Excel. It is often used as an Worksheet Function in Excel. And, in this the AVERAGE Formula is considered a formula that could be entered in a cell in Excel. All you have to do is, click on a cell towards the right or below the numbers of which you need to find the Average for. Now go to the Home TAB, Edit Option, click on the AutoSum, click on the AVERAGE and then press enter.

Take an example, if the range is, A1:A20 which contains numbers, the formula would be =AVERAGE(A1:A20) that will return the average of those numbers.

Average Formula - Excel Formulas - Excel Superstar

Average numbers ignore Zero

Aveargeif formula in Excel - Excel Formulas

Tip 1: By going on Home Tab, Click on AutoSum then click on Average at the Right hand side
Tip 2: By going in the Formulas Bar
Tip 3: By applying the Average Formula Directly 

Note: AVERAGE automatically ignores blanks cell and text, but Zero Values are included.

Related Formulas

Count Formula

Count Formula vo excel Formula hai Hindi ka jo ke aapko help karta hai to Count the Number of Numbers in Supply Items.

COUNT(value1, [value2], …)

The most important and used Function in Excel has been SUM and the COUNT Formula. Excel Count function, helps you to find out and count the number or Cells that contains numbers.

Count Formula in Excel - Excel Formulas

Tip 1: By going on Home Tab, Click on AutoSum then click on COUNT Numbers at the Right hand side
Tip 2: By going in the Formulas Bar
Tip 3: By applying the COUNT Formula Directly

Note: The Count Numbers of Cells in range that do not contain numbers, Use SUMPRODUCT Formula

Related Formula

Counta Formula – Excel Formulas in Hindi

COUNTA Function bhe Count Function ke tarah hai par ussei thoda different hai. Ye Excel Formula aapko non-blank cells ki calculation karne mei help karta hai.

COUNTA(value1, [value2], …)

COUNTA FUNCTION: With this Function, Calculate the value, number, text, error values and Logical Values while taking Excel in Hindi. The COUNTA Function does not count the empty cells. You can calculate the value of the Non-blank cells with the help of COUNTA Function.

Counta formula in Excel - Excel Formulas

Note:  COUNTA Function helps in counting non blank cells, the cells might contain numbers, dates, text, logical values of TRUE and FALSE, errors or it could be empty text strings (“”). Any of these could be calculated if you use the COUNTA Formula.

Related Formula

Concatenate Formula – Excel Formulas in Hindi

Concatenate ka arth he jodna hai. Toh Concatenate Excel in Hindi mei ka vo Formula hai jo ki do yaa ussei jyaada cell references ko Jodne mei aapki help karega.

CONCATENATE(text1, [text2], …)

If you plan to join or combine two or more strings, numbers or cell references together you can join it by using CONCATENATE Function. It can join around 30 items together. This Function helps you to combine numbers, text from different cells into one cells.

Concatenate Formula in Excel - Excel Formulas

IF Formula

IF Formula vo Excel Formula hai jo kee aapko Sahi ya galat Value i.e Truth or False Value or Results nikaalne mei Help karta hai.

IF(logical_test, [value_if_true], [value_if_false])

Supposedly, the IF Function is considered to be good to perform a Logical Test. And then this Function of Excel returns one Value for true Results and the other Value for False. To bring out the information of Truth and False depending upon the Parameter put forward in the variable Box. The fact is, IF Function could be combined with other Logical Functions as well, such as AND and OR.

NestedIF – Excel Formulas in Hindi

You might call it Nested IF and there are many who term it as Nested IF Statement. If the comparison is to be done for more than three IF Functions that would be needed to put up together. This is used basically when there are more than one IF Function. It helps in testing more Conditions so that there is a possibility to obtain further output and More Results.

You have to make sure, that Each If the statement has been carefully “nested” inside the other statement. And ensure that the logic applied is absolutely correct and in check.

IF Formula in Hindi - Excel Formulas

Notes: When you are Constructing test with IF Formula, You can use any of the following Logical operators

Comparison OperatorMeaning
=equal to
<>not equal to
>Greater than
<Less than
>=Greater than or equal to
<=Less than or equal to

Related Formula

CountIF Formula – Excel Formulas in Hindi

Jab COUNT IF ke baat aati hai, toh bol sakte hain, ye Count Function in Excel sei thoda alag hai. COUNT IF Advanced Excel Hindi Formula hai jo ki aapko Excel in Hindi mei saare cell Count karne mei help karta hai.

CountIF(range, criteria)

COUNTIF Formula is the Advanced Excel Formula. This has great uses of Conditional Functions. The COUNTIF Formula will help you to Count all the cells. To find out the solution of your question. COUNTIF Function does not Add up the Matching Values from the cells. What it does is, it Pulls and shows you the set. You can consider the Example of, counting cells that is equal to or greater than 21 (age) to find out the no. of bottles of champagne you need for a client event. So, you can use COUNTIF as the advanced solution here.

CountIF Formula in Excel- Excel Formulas

Notes: In COUNTIF Function, the values can be supplied directly in the form of Criteria. The text has to be enclosed in the double quotations i.e. (“”). Whereas, in a situation where you’ve already got a criteria in the another cell. In this case, the Cell address can be supplied as the Criteria without quotations. You can see the same in the above example. 

Related Formula

VLookup Formula – Excel Formulas in Hindi

VLOOKUP, Doston ye mera favorite formula hai. Agar aapne VLOOKUP Formula in Hindi seekh liya toh maano aapne almost poora Excel in Hindi Seekh liya hai. VLOOKUP is that Excel Formula jo ke aapko aisi jaankaari dhoondhne mei madad karta hai jo ki Big Chunk of Data ke beech mei dhoondhna impossible hota hai.

VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

VLOOKUP Function is used when there is a large set of Data. It becomes difficult to look out for any kind of information that has been missing. In order to pull out the piece of Information that you’ve been looking for, you can use the VLOOKUP Function. VLOOKUP will look out and pick up the exact piece of Information from the large segment of Data. And this Data is automatically picked and Put in the newly built Table.

All you have to do is, go to the Function Option. You can enter the VLOOKUP Formula by finding it in the list or by using the Insert Function. With the insert Function, you can enter the VLOOKUP easily. VLOOKUP FORMULA in Hindi ko Doston jara dhyaan sei seekhein. 

Vlookup Formula - Excel Formulas in Hindi

Sumif Formula – Excel Formulas in Hindi

SUMIF Formula aapko help karega us data ko add karne mei jo ki aapke Criteria ko match karta hai. Learn Excel in Hindi

SUMIF(range, criteria, [sum_range])

SUMIF Function is the Advanced Excel Formula.  This has great uses of Conditional Functions. SUMIF Function helps you find out a certain set of information that you’ve been searching for or which matches your criteria. The criteria contain the Wizard Box and this Wizard Box Contains the Sum Range Tab, range Tab, and the Criteria Tab. Well, Range Tab is the area you’ve been looking for. Criteria Tab helps you in finding the Cell. And with the SUM Range Tab, you’d be able to make additions. You’d be able to add the Data that matches your Criteria.

Sumif Formulas - Excel Formulas in Hindi

Maximum Formula – Excel Formulas in Hindi

Max function is used to find the maximum or the largest value in the cells. This function helps us to locate where the maximum value is located in the spreadsheet. Learn Excel in Hindi

MAX(number1, [number2], …)

In the image below we need to find the Maximum amount to be paid to Client. To find this we will enter the Max formula in the empty cell and enter the Excel Formulas.

MAX formula in Excel

The answer is 195,800.

For more detailed information regarding Max Function.

Related Formula

Upper Formula – Excel Formulas in Hindi

Upper formula is used when we want convert the text of different cases into a uniform Upper form. This formula can be used for either entire cell or for a particular text in the Excel Sheet. Learn Excel in Hindi

The Upper Formula is to be entered as

UPPER(text)

Here we convert the text in the cell B3 i.e. Kolkata into KOLKATA. For converting the text of the cell in the Upper Case we will use the Upper Formula as seen in the Formula Bar. Press Enter. The Result is seen. The text in the cell is converted into the Upper Case. “KOLKATA”

Upper Formula in Excel

Lower Formula – Excel Formulas in Hindi

Lower Formula is one of the formula from the Excel Formulas list. This formula is opposite of the Upper Formula explained earlier. The purpose of this formula is to convert the text from upper or proper case to Lower case. Learn Excel in Hindi

The Lower Formula is to be entered as

LOWER(text)

Taking the same example as that of Upper Formula. Here we have text in uppercase in cell B9 and we want to convert into Lowercase. Enter the formulas as seen in the image and Press Enter. The uppercase text is converted into lowercase text. 

Lower Formula in Excel

Trim Formula – Excel Formulas in Hindi

Trim formula is used to clean your data by removing leading, trailing and extra spaces between the words. The Trim function does not remove the spacing between the words. Trim function is the simplest as well as utmost important function from the Excel Formulas. Learn Excel in Hindi

The Trim Formula is to be entered as

TRIM(text)

In the example below, we will use the Trim function that will help to clean our data. In the first example there is more space between the name and the surname. Using the rim function you can clear the extra spacing between the two.

You can check whether your data is Trim or not by using the Len function. Once you use the Len function before and after using the Trim function you will come to know the difference. Enter the Trim Formula and Press Enter. The result are right in front.

Trim Formula in Excel

Len Formula – Excel Formulas in Hindi

Len formula is one of those Excel Formula which is used to count the length of the cell. Or to count the number of characters entered in the cell. The main purpose of this formula is to know whether the data inputted is up to the mark. Learn Excel in Hindi

The Len Formula is to be entered as

LEN(text)

Here we will calculate the length of the cell. In other words, we say how many characters does the cell contains. In the cell B4 which shows 4789203 number and we want to know the length of the cell.

Enter the Formula as shown in the image and Press Enter.

len formula in Excel

Left-Right-Mid Formula | Excel Formulas in Hindi

Left formula is used to substring the data from the left. There are three Excel Formulas in which you can substring your data and Left formula is one of those. In the Left Formula, you extract the data starting from the left of the cell specified. You enter the start number as the second argument which mean till where you want to extract your number. Learn Excel in Hindi

The Left Formula is to be entered as

LEFT(text, [num_chars])

The Right Formula is used to Substring the data from the right. The Right formula works exactly the same as the Left formula. The only difference between the two is here you select the number from the end. Right formula is one of those formula from the substring Excel Formulas list. Learn Excel in Hindi

The Right Formula is to be entered as

RIGHT(text, [num_chars])

Mid Formula returns the middle substring from the data. Mid Formula too is one from the various Excel Formula used to sub–strings the data from the cell. Mid formula extract data not from the left or from the right but from the middle.
Here, there are 3 arguments required compared to the 2 which was required in Left, Right Formula. Learn Excel in Hindi

The Mid Formula is to be entered as

MID(text, start_num, num_chars)