Doston niche kuch aise Excel Formulas 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
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:
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.
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 numbers ignore Zero
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.
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.
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
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.
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.
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.
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.
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.
Notes: When you are Constructing test with IF Formula, You can use any of the following Logical operators
|<>||not equal to|
|>=||Greater than or equal to|
|<=||Less than or equal to|
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.
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.
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.
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.
Excel Course in Hindi
There are Excel Formulas that you must know. Most of the jobs these days require advanced Excel Skills. Pivot Table, Sheet Referencing, HLOOKUP, Vlookup and INDEX MATCH are the Powerful Formulas that you need to learn to be an expert at work. We have the Advanced Excel Course especially designed to make you an expert at Excel. Learn MS Excel In Hindi. See details here.
Table of Content
|✅ Microsoft Add-in Fuzzy lookup in Excel|
|✅ Flash fill in Excel | Data Cleaning|
|✅ Hide a sheet so that no one can easily Unhide in Excel|
|✅ Confidential – Remove “Protect sheet” Password with Notepad|
|✅ Confidential – Remove “Protect workbook” Password with Notepad|
|✅ Making a text magically invisible in Excel|
|✅ OFFSET function in Excel|
|✅ Indirect function in Excel|
|✅ Text to Columns in Excel|