Top 15 MS Excel Formulas One Must Know

Download ebook

MS Excel is one such app, that has helped almost everyone to excel, in their fields. It is omnipresent, let it field of education, research or finance. Knowing excel has become vital nowdays. Considering the growing usage of ms excel we have listed the top 15 MS Excel formulas one must know.

According to my personal experience, MS excel was introduced to me in the third class and I use it to date. Only the usage has changed. There is no doubt that MS excel formulas are used in almost sector in one or the other form. So, we have listed top 15 MS excel formulas for your better understanding of excel.

But, in spite of the fact that we have been using Excel for ages, how many of us are aware of its complete functions? Yes, we were taught some of the formulas in school. You must have got a hang of a few others who will work as a professional.

But are they enough?

No, so today we will teach the top 15 ms excel formulas if you are a beginner these super easy formulas are must-learn for you. Moreover, even if you are a user these will surely increase your efficiency while working.

Top 15 MS Excel Formulas one must know-

1 SUM

SUM is an abbreviation of summation known as the addition in layman’s language. This formula is used to get a total of selected data.Moreover,this formula works on multiple fields as well.

The formula used is =SUM(A1:A5)

For example= we need to get the total height of given products.

2 MIN and MAX

Minimum and maximum formulas are most used while doing some research or analysis. The minimum formula is used to detect the minimum value in the selected row.

Additionally, same goes with maximum, it shows the maximum value amongst the selected row.

Say, there is a list of 10 countries and their population and you need the least populated country. You can apply the minimum formula.

The formula used is=MIN(A1:A5)

For example-We have a list of products along with heights. Now we need to find out the product with minimum and maximum height.

The formula used for MAX is=MAX(A1:A3)

For example-

3 COUNT

It is a very basic function in Excel.Count formula is simply used to count the numeric cells in the selected field.

Say, there are 10 entries of weight and 2 are left blank, one is NA. If we count cells manually total would be 14 cells in the weight row, but the formula will show 10.

The formula used is =COUNT(A1:A4)

For example-We have a list of products and their height. Let us find out the total number of entries in the height column.

4 Average

As we all know that average is nothing but the sum total divided by the total number of entries. The average formula is mostly used to find per unit, per km, prices etc.

Average is again one of the most commonly used formulas and is used almost in every sector.

It is a mathematical formula, hence can only use on numbers.

The formula used is=AVERAGE(A1:A7)

For example-We have a list of products and their heights. Now we need to find the average of the heights for controlling.

5 CEILING 

This is an extremely interesting formula, widely used when we are dealing with decimals. The ceiling is used when we need the nearest round-off of the given number. It also gives a round-off according to the given multiple.

For example– it to you in simple terms, if we have the number 8.34 and we use this formula to round off to the nearest multiple of 1 it will show 9. But now if we round it off to a multiple of 10 it will show 10.

The formula used is=CEILING(A1,1)

6 FLOORING

Flooring is just the opposite of ceiling, when we apply ceiling it round offs to the higher number, but when we apply it to the floor it round offs to the lower side of the number.

Let us take the above case again, suppose the given number is 8.34 then it will round off to the lower side which is 8.

Remember -the basic rounding-off principles do not apply to this formula.

The formula used is =FLOOR(A1:1)

7 POWER

This formula is a blessing in disguise for those who have been struggling with powers in mathematics. Well, it is time to end your struggle now with a simple formula. As the name suggests power produces the result by displaying the number raised to the power asked for.

Say we, have a number 10 and we want its power raised by 2 times, then the answer displayed would be 100. Well, this was a simple calculation, but you can perform complex calculations with just a click.

The formula used is =POWER(A1,x)

X- denotes the times the power needs to be raised like it was 2 in the above case.

For example-We have a list of products and their quantity. Now we have to find out the power raised 5 times of product B.

8 UPPER, LOWER AND PROPER

We have learnt plenty of formulas that are related to maths. Now it is time to learn some formulas that will make your working on excel using characters with ease.

Firstly Upper is the same command we give to the computers we want characters in uppercase which is the capital form of characters.

Suppose you want to convert your text into capital letters you can use this formula to do that.

Formula used=UPPER(A1,F5)

For example-We are given Names and surnames. We have taken a few names which we have converted into Capital letters, small letters and first letter capital by using the formulas.

Secondly, we have lower, this is just the opposite of upper. It is used to convert text into small letters.

Formula used=LOWER(A2,A6)

For example-

Lastly, we have proper, it is also very useful when you are preparing a datasheet, it converts the first character of the cell into capital letters. Suppose you want to write proper nouns.

The Formula used is=PROPER(A1:A3)

For example-

9 TODAY

This again a very easy yet useful formula in excel. we use it when we want to change the date, day, month, timing etc.

There is a separate formula for each one of them-

• MONTH 

The formula used is=MONTH(TODAY)

You will get the current month updated in your system.

• DAY

The formula used is=DAY(TODAY)

You will get the current day updated in your system.

• YEAR

The formula used is=YEAR(TODAY)

You will get the current year updated in your system.

10 DATEIF

So, this is a special formula which is used specifically on dates. One can use it while he or she is performing research, creating school calendars, personnel schedules, sales records or any other type of analytics.

This formula displays the difference between the given dates. The difference could be in terms of days, weeks or years. Just the abbreviation to use in formula changes.

Say, you want to know the days between 2nd April and 3rd May,the formula will automatically display 31  as a result.

The formula used =DATEIF(A2,A3,”d”)

D is used for days,

For example -here is a list of people with their birth and death years. We need to find how many years the first person lived.

11 IF

It is a tool that can be used both on characters and numerics.IF is widely used to draw inferences from the given data, the data gets separated into dedicated categories. This tool is also extremely while doing analysis, like if you have data on national income and you want to know who is eligible to pay tax. You just have to set the limit in the formula and you will be eligible and not eligible written in front of their incomes.

The formula used=IF (H6<Category 1, category 2)

Let us take above case than formula would be =IF(G4<1,50,000,” Eligible, Not Eligible”)

For Example-We have a list of students along with their marks scored in the tenth class. On the basis of which stream would be allotted to them by the school. So, a teacher needs to find which student is allotted which stream.

12 CONCATENATE

This formula combines the text written in different cells and displays it in the next line. It is used when we need headlines or lengthy topics in between sheets or you need to show some kind of bifurcation.

As an illustration, let us assume that we wrote the last Miss India in one cell and names in another cell. After you have completed the list you want a heading then you can combine both cells using this formula. The result would be the last Miss India names.

The Formula used is=CONCATENATE(A1,” “,B1)

For example– We have a list of names and surnames. We wish to write names and surnames in the same cell now.

13 COUNTIF

Countif is a combination of formula count and if. The count function counts the number of cells and separates the data on basis of condition. Now we have a merger of both.

This formula counts the number of cells in which the given condition is present. It is extremely useful when you have huge data and you need the number of the data meeting particular criteria.

As an illustartion,let us take the same case that we discussed in the IF formula. Once we have found the eligible and non-eligible taxpayers, we can now apply this formula to get the total number of eligible or non-eligible ones.

Note-(It is not always necessary that we use it after applying the IF formula. Mostly the data is segregated into different categories, you can apply the formula directly in that case.)

The formula used is=COUNTIF(G2:G65),”x”)

For example-Now, we have students with their marks and their allocated stream. The teacher wants to know how many students have got science.

Earlier we learnt single formulas, now we raised the standard a little high by teaching you a few combined formulas. These formulas might be a little tricky to understand but extremely useful. They are widely used in economics, financial and clinical and other research-based organisations. Here the data dealt with is more complex with several entries to be considered. In this scenario formulas like these are of great help.

14 SUMIF

Till now you must have got the basic concept of IF, now by this formula you can get some of the data meeting the particular condition mentioned.

So, let us take the same case mentioned previously, now the income tax officers want to know the total bifurcate income of non-taxpayers from total income. They can easily use this formula. The result shown will display summed income of the non-taxpayer.

Further, these conditions can be filtered more for specific results, suppose now tax officers need the total income of non-taxpayers who are younger than 20. This can also be done.

The formula used =SUMIF(X1,”x2”,X3)

(Where X1 is the cell in which condition is,x2 is the condition,x3 is the cell in which numeric data of which sum is to be taken.)

For example-Again, we will take the same example as taken above. Now teacher needs to find the total marks of students in the science stream.

15 LEN

Last but not least this formula helps in finding the total characters in the selected cell.It counts the total characters while considering the spaces and special characters.

Say the cell has email [email protected] ,the result would be 18

So, we have explained some beneficial MS excel formulas for you, all the formulas are according to beginner. If you really wish to upgrade your Ms excel skills, you must enrol yourself in an excel course. They will help you become a pro in this application. It surely enhances your excel skills pulse you would get trained in analytics, data management, and tools to create business solutions.

Although, you can boost your existing by undertaking such courses and if you are a beginner .Moreover, if you are looking job in this field then this course will assist you in career building as well.

Henry Harvin too provides a MS excel course, whose details are provided as follows-

About Henry Harvin

a)Henry Harvin is an unparalleled institute when it comes to training and certifications.

b) It has an experience of more than a decade in the transfer of first-class education.

c)There are 11+  offices situated in the USA, UAE, and India.

d)All the staff is strongly dedicated to providing top-notch skills and intellect to the aspirants.

e)It has successfully trained and placed 3,00,000+ students all over the world.

      Recognitions And Accreditations

• It is affiliated with the American Association of EFL, MSME, UKAF, UKcert, ministry of corporate affairs, ISO 29990:2010, and project management institute(PMI).
•   Winner of Top Corporate Training Award, Game-Based Learning Company of the under 40 Business World Award.

          Why Henry Harvin- 

• Training + Projects + Internship + Certification + Placement + E-Learning + Masterclass + Hackathons + Gold Membership( all in one box)
• 24 hours of classes, which are totally self-paced.
• Ranked number 1 in India by bestcoursenews.com.
• They will refund your 100% fee if you are unsatisfied with the course.
• All the experts are certified in the respective subjects that they teach.
•  More than 10 live projects are given during the course to give practical experience.
• 100% practical-oriented teaching.
• Fully active placement cell for assistance and career growth.

• Teaching add-on skills like resume building soft skills,12+ Bootcamp sessions. 
• Placement drives are held after completion of the course customarily.
• All the course material is available online with a 24*7 helpdesk.
• A separate LMS account is allotted to every student so that they can access study material, coordinate with instructors and submit assignments.
• The instructors are very cooperative and solve every query and doubt with ease.
• Chance to get hired by companies like Walmart,HP and Maruti Suzuki.
• Upgrade your excel skills to the next level and get certified by the hallmark of CAEP.

Course Duration-24 hours

Course Fees-Rs 7500

Ratings-4.8**

For more detailed information you visit the Henry Harvins website-

Advantages of learning Ms Excel Formulas –

1. Important Component while doing a job –

Recently a survey was conducted of the working class in which the importance of excel skills in the job was accessed.

More than 90% said that MS excel formulas are “very important” while performing their job.

2. User-friendly-

There are a number of times when you want to give a specific command in excel but you do not know how to do it. formulas bridge the gap between the user and the system, by simplifying the commands.

3 Increases Accuracy- 

There are numerous mathematical formulas which ease the calculation part and the results produced are 100% accurate. This helps in creating error-free spreadsheets.

4 Adds a star to your productivity-

If you are good at excel you can work more efficiently. The chances of getting errors reduces. The work becomes more organised and you can achieve your goals timely. These all ultimately lead to higher productivity.

5 Gives a clear view-When your need to create reports or perform analyse, spreadsheets can become a hassle. Application of formulas gives a crystal view by giving you the output you need. You do need to go through the entire mass of data to infer.

Conclusion

You would not be astonished to know the fact that over 750 million people use MS excel formulas throughout the world. This statement speaks it all. Ms excel formulas bags have several advantages, so if you get a hand on them, they will definitely help you thrive. One of the most special things about excel is that it has a huge audience. right from class third till old age, everyone is using excel nowadays. Keeping this in mind we have shortlisted quite easy yet useful, top 15 ms excel formulas that one must know.

Lastly, we hope that you must have learnt something from this page today. If you are a keen learner who wishes to upgrade his or her skills in MS excel, do join a nice course. There are many course providers like Henry Harvin.

So, keep learning and keep prospering.

Recommended Reads

1)Top 25 Microsoft Excel Formulas you Should Know

2)10 Best Advanced Excel Books: 2023

3)15 Best Advanced Excel Course in Delhi: 2023 [Updated]

                                                         FAQs

Download ebook