12 Most Important Excel Formulas With Working Tutorials

Excel formulas are some of the most unique features of MS Excel that guarantees to make your life easier in many regards. You can perform complex calculations and automate them across the entire sheet using formulas to chunk down your work hours by a long margin.

You’ll find tons of tutorials that will teach you Excel formulas, but not all of them are accurate and only a few of them actually accommodate Excel beginners. Thus, I’ve put together this article to acquaint you with the most basic and important formulas in a perceivable way.

After going through this article, you’ll realize that the formulas are explained in detail with necessary screenshots, and the Excel file that I’ve used as a sample has been included here only for your convenience.

So download it for practicing purposes or make your own from scratch – our goal is to render you a master in Excel and conquer all the most important formulas this software has to offer along the way.

What Are Excel Formulas?

Excel formulas are basically shortcut methods of getting complex or lengthy calculations done. Based on the data inputted across the cells in a spreadsheet, a formula can generate a result on the preferred cell without having to manually reach that outcome.

Formulas can add, subtract, multiply, divide, filter, sort, find out variables and what not with a few key presses. Let me show you a basic example so that you can understand the capacity and extent of Excel formulas and how they can make your life effortless.

For instance, you are asked to make a summation of the 10 data cells like in the picture. Using a calculator, you can add them separately to reach the result of 611. Or in Excel, you can add the cell numbers one by one using the command on any cell like A2+A3+…..+A11 within brackets next to an equal (=) sign and hit Enter, the same outcome will show up.

Both of these methods are time consuming, and a formula like =SUM() can save your day. What you need to do is, identify what you’re intending to do, pick the proper formula and mark the data cell range. In this case, the range is between A2 to A11.

So, in the cell, you’ll apply the aforementioned formula like =SUM(A2:A11) and press Enter – and that’s it! It’ll do all the addition for you without you having to look at a single number. For such effortlessness and convenience, Excel formulas are widely used everyday in all office spaces.

Excel Formulas: Insertion Methods

There are manual, automatic and semi-automatic ways of inserting formulas in an Excel spreadsheet to make the calculations very easy. Here, I’ve discussed five of the most convenient methods that you can use to apply your formula of choice.

Typing Inside A Cell

The first and foremost formula insertion method is the most basic one where you’ll simply type in the formula inside the desired cell. For some formulas to work properly, you have to keep it adjacent to the target data set, while for most, any cell across the spreadsheet will do you the trick.

What you need to do is, identify your data range and select the cell where you want your result to be produced.
Then, pick the formula that’ll generate your target mode of calculation and enter it there.
Type in the data range within the brackets if needs be. You can also type in your formula, input the opening bracket ‘(’ and while in the typing mode, select the range of data you want your formula to be applied with your mouse on instead of typing it out.
Hit Enter, and that’ll do the job!

This way, you can effortlessly insert a formula without having to ponder over anything else.

Insert Function

If you still haven’t memorized all the formulas, no need to worry! You can gain access to the list of formulas through the Insert Function button. It’s placed right next to the Formula Bar on the left side as the ‘fx’ icon written in cursive.

There’s another way you can access the Insert Function. It’s located in the Formulas tab at the very top. Among many Excel shortcuts, the formula function shortcut is Shift+F3 for both Mac and Windows.

Once you have your data range in hand and the target cell selected, click on the ‘fx’ button to open up the menu for all the formulas.
These formulas are divided categorically with a search option on top where you can input a brief description of what you want and Excel will suggest relevant formula options.
When you’ve figured out which one you need, select that and hit OK and it will transfer you to the data range selection option.
Having the Number1 box highlighted, select the data range that you want the result of. Press the OK button to have your formula properly applied!

In such a manner, you’ll be able to have a guideline for applying formulas to your preferred data set and achieve desired outcome.

Typing In Formula Bar

Typing the formula in the formula bar is the classic way of going about applying formulas in Excel. It’s almost the same as writing the formula in the cell, but the formula bar helps out a lot.

It’s a wide area that shows you the entire formula that you just typed out so that you can cross-check it properly without making any mistakes.

[Tip: if you have a cell that’s already been applied with a formula and generated a result, selecting that cell will put that formula on display in the Formula Bar]

Selecting From Formulas Tab

If you want to explore the formulas based on categories ranging between Financial, Logical, Text, Date & Time, Lookup & Reference, Math & Trigonometry and some other formulas, you can go to the Formulas tab sitting next to Page Layout on top.

Make sure that your data to be formulated is placed on the Excel sheet.
Then, go to the Formulas tab and select the category of formula you want to apply.
At the drop down, click on your formula.
This will lead you to selecting the spreadsheet area for your data range.
Mark that range and hit Enter.

And that’s it! This is how you can use the Formulas tab to insert Excel formulas to your calculation or sorting.

Using AutoSum

The AutoSum feature contains a few of the most frequently used Excel functions that automates the formula insertion method. It has Sum, Average, Count Numbers, Min, Max formulas built-in to the system with the option to go further into the More Functions option.

Note: There are significant differences between formulas and functions in Excel, so learn each of them individually to not have them mixed up!

12 Excel Formulas To Upgrade Your Game!

On the internet you’ll find tons of Excel formulas that are mentioned by experts as the most vital ones. Truly, they are valuable on their own accord, but the 12 formulas I’m introducing you to are like stepping stones into the complex world of Excel that you can’t do without.

SUM

The =SUM() formula basically sums up the cells you mention or cell area that you select as your range. For instance, you have data across D2 cell to D6 cell and you want to sum them up. What you’ll need to do is broken down right here:

Select the cell where you want your calculated result to appear.
Start typing, write =SUM(D2:D6). Here, D2 is the first data cell of the range and D6 is the last one, the ‘:’ in between them tells Excel to pick up all the data from D2 to D6.
Hit Enter and you’ll get your outcome!

There’s something about this formula that many entry level Excel users don’t know is that it can not only be used for summation, but also for deduction, multiplication and division. You need to place your command within the brackets to generate the result. Here’s an example:

Type in =SUM(D5-D2) and hit Enter, it’ll deduce the value of D2 from the value of D5. It goes the same with the ‘*’ or ‘/’ calculations.

IF

The IF formula is probably the most intriguing thing that Excel has to offer you. You can go beyond the extents of your logical imagination to generate outcomes that would otherwise take you a long time to accumulate. Let me break down the formula first.

=IF(logical_test, [value_if_true], [value_if_false]); meaning, the logical test can be if a number is higher or lower than something. We’ve used (A2>50) as the logical test here.
If the value is true, you can add a number or text to show up, like (A2>50, “PASS”). So, if the value is over 50, the PASS text will appear.
In case the value is not true, you have the option to set that rule as well like (A2>50, “PASS”, “FAIL”). The fail is the ‘otherwise’ text that’ll show if the value doesn’t match the criteria.
So the final formula becomes like: =IF(A2>50, “PASS”, “FAIL”) [make sure to keep the outcome texts within “inverted commas”]

You can go for complex IF formulas as well if you want to make your work easier and refine the outcome. Suppose you’re working on the same grade sheet and want to grade students automatically based on their marks. Follow my lead if that’s the case –

You’ll have to keep on setting a series of IF formulas one after one to have them applied to the entire grade sheet. Let me show you my custom formula.
=IF(C2>80, “A”, IF(C2>70, “B”, IF(C2>60, “C”, IF(C2>50, “D”, IF(C2<50, “F”)))))
It means, if a student obtains over 80 marks, they’ll be graded A. So on, ones bringing under 50 will be graded as F.
Using just one line of formula, you can directly grade your entire document within just a few seconds!

I’ll suggest you to keep on practicing the IF formula to gain better depth inside its abilities. Frequent uses will land you unique approaches toward complex problems that are very much attainable without much effort!

MAX & MIN

MAX & MIN formulas are very basic and straightforward. What they do is, they find out the maximum and minimum numbers within the data set range. The MAX formula identifies the maximum value and the MIN formula does the same for the minimum value.

To get the maximum number, select a cell and type in =MAX().

Inside the brackets and hit Enter to get the maximum value
For the minimum value, type =MIN() and place the range inside before pressing Enter and finding out the outcome.

You can easily get easy data outputs like this using this pair of formulas and find direct results!

AVERAGE

Suppose you have a large set of data which you need to average. Instead of adding them and dividing them by their total number, you can simply use the =AVERAGE() formula.

Begin by selecting your desired outcome cell and typing out =AVERAGE().
Within the brackets, mention the cells of which you are demanding the average of.
For instance, you can use commas to select individual cells like =AVERAGE(A3,A2,A5).
Or you can select a range much like the SUM formula this way =AVERAGE(A2:A11).
In the end, press Enter to generate the result.

This is the fastest method of getting an average from Excel data.

COUNT

Suppose, you’re in need of counting the number of data cells there are within your given spreadsheet. Instead of counting them individually, you can simply input the =COUNT() formula to get an instant result.

Select the target cell and type in your target data range.
Example: =COUNT(A2:A11).
Once satisfied, press Enter to receive the total number of data values within that range.

The outcome is always precise and accurate, so don’t go counting every value one by one and let this Excel formula take care of your needs.

Percentage

Calculating percentage has always been a tough job, not because of the difficulty, but because of the amount of time it consumes. Unlike the rest of the formulas, calculating percentage doesn’t have a standalone formula, rather it’s done by a series of known formulas that I’ve mentioned.

I’m going to teach you how you can organize your spreadsheet, be that a mark sheet or a business calculation. This example shows a mark sheet of a student and its percentage calculation formula.

Start by inputting your data, the total value and the fraction value. Let’s say, the total value is the Max Marks and fraction value is the Marks Obtained.
Then, shift to the Percentage column and input the =SUM(D2/C2) formula to get the percentage of marks in fractions. Spread it down the column till the Overall.
Highlight the cell and press Ctrl+Shift+% (use command instead of Ctrl in Mac).
Press Enter to get the percentage result! Now spread this formula across the Percentage column to receive every percentage including the total.

Like this, you can easily generate, calculate and change data even after final calculation, and this Excel formula will automatically update the results for you!

DATEDIF

If you find yourself in a dilemma to calculate the age or difference between two dates, this is the perfect formula that you can implement. To apply this, you have to –

Select the target cell for your output and type in =DATEDIF(A2,B2,”y”) and press Enter.
A2 here is the Initial Date or Date of Birth, B2 here is the Current Date or the Target Date.
The “y” within the brackets dictate the type of your difference. “d” stands for day, “m” stands for month and “y” stands for year. You get to choose what sort of difference you want.

It’s a handy formula that saves time in complicated scenarios.

COUNTIF

The COUNTIF formula functions as a minor extension to the IF formula. What it does is, it takes a conditional IF and generates the output only in terms of how many times a certain string has been repeated within the data range.

For instance, you are given a table with tennis grand slam winners up to 2018 and asked to find out how many times Roger Federer has won a grand slam. What you’ll need to do is –

Select the cell where you want your output and apply the COUNTIF formula
Type in: =COUNTIF(range, criteria); meaning: =COUNTIF(A2:E144, “Roger Federer”)
When you’ll hit Enter, you’ll see that Excel has done all the counting for you and answer that Roger Federer has won 20 grand slams that’s inputted into the list.

More complex calculations can be made easy using this COUNTIF formula and using IF conditions the counting process will become more precise and easy.

TRIM

At times, you’ll notice that after applying a formula over a range of data, the outcome is showing an error and not generating any results. There can be many reasons for that, but one of them is having extra spaces that mess up with strings, and the TRIM formula can correct that mess.

Keep your untrimmed data on one column
On your target column, type in =TRIM(A2), or the name of your target cell and press Enter
You’ll get an untrimmed result with single spaces between each word having the unnecessary ones easily removed

At the end of this article, learn how to spread a formula over a column to automatically generate the rest of the results using the same formula.

LEFT, MID, & RIGHT

The LEFT, MID & RIGHT are individual formulas, but they function so similarly that they’re being addressed together here. What they do is, they take a target text string and separate the left, middle or right portion of that text string from the rest. Let’s look into an example:

For LEFT, insert =LEFT(target cell, number of characters). This way, you can start from the left and take your desired number of characters to be extracted into your selected cell.
For RIGHT, type in =RIGHT() with same attributes, only this time it’ll extract data starting from right.
For MID, input =MID(target cell, after how many characters the selection will start, number of characters). It means, for the MID I have to additionally count the number of characters to skip before isolation my target amount of characters from the middle.

It’s a really straightforward way of extracting precise data from text strings across a spreadsheet.

VLOOKUP

VLOOKUP is the short form of Vertical Lookup. It does what it sounds like – it searches for a set criteria against a selected cell value to easily find out certain information about that cell from your desired column or information area.

The VLOOKUP final formula looks like something like this, =VLOOKUP(D8,A13:B21,2,FALSE)
Formula: =VLOOKUP()
Lookup value: D8, this is the info against which you’ll be searching for data vertically, here it’s the name of a specific race
Table array: A13:B21, this is where our desired information is sitting next to the D8 value, here the array contains the race names against the date they were held
Column Index Number: 2, the number of column of the desired info, the column in which the race dates are kept
Range lookup: TRUE or FALSE, TRUE means if it matches approximately, Excel will still forward it whereas FALSE means there has to be an exact match

This way, you can cross-search data vertically and place them in your target area for a better-organized and easy-going database approach.

Randomize

Randomize comes with two different commands: =RAND() & =RANDBETWEEN(). Each of them has its particular purpose. Let me explain –

=RAND() formula generates a number which is less than 1 and is greater than or equal to 0. With every interaction to the spreadsheet, this number changes randomly, and if it’s spread across an array then all of the numbers will change in a similar way.

=RANDBETWEEN(bottom, top) requires two values within the brackets. The bottom is the lowest value of the range while the top is the highest. If you input this way: =RANDBETWEEN(0,100) then it’ll generate a random number between these two.

For an unbiased randomly generated number, be that in fraction or in full, the Randomize formulas tend to solve a lot of puzzles that require unbiased, randomly generated numbers.

How To Spread Formula Across The Column?

You’ve seen me insert a formula in a single cell and have it spread across all the other cells with properly executed results. Did you think I was going to leave you to dry by sparing you this trick? Not at all!

Let’s see how you can input an Excel formula against a single cell value and apply it for all the rest of the data in the same way to cut down on time!

Firstly, have your data set available to you and choose your preferred cell to input the formula of your choice.

After doing so, notice at the bottom right corner, there’s a tiny dot upon which the mouse cursor turns into a small plus.

Click that, drag it down till the last cell of your desired outcome area and release it

It’ll have spread that same formula in the same way to all the data cells accurately!

This saves a lot of time and effort to apply a single formula adaptively to all the target data cells in a miniscule amount of time.

Benefits Of Using Excel Formulas

In hindsight, the benefits of Excel formulas might not seem like much, but in reality, the positive aspects to such application goes further than we can perceive.

Firstly, as mentioned before, Excel formulas make your calculation much easier and faster. It’s going to cut down on your time and automate your process to render you an efficient worker in the office.
Another benefit of using formulas in Excel is that, if a number or value within the data range encompassed by the formula changes after application, the result will upgrade itself automatically.Suppose, you have calculated 6+2+2=10 using the SUM formula. Now, if you replace the 6 with a 5, the result of 10 will automatically be updated into a 9, which would be the result for 5+2+2. In large spreadsheets, it saves a lot of trouble and cuts down on time.
Lastly, financial formulas such as PMT, PPMT, IPMT, and IRR will help you significantly while calculating or generating financial sheets or yearly payment accumulation.

So you can see, Excel formula benefits are something to be appreciated and should be implemented in our daily task to make our lives undemanding.

Thoughts

Now that you’ve reached the end of my Excel formulas article, I’ll leave you with one piece of advice that you can follow along for the rest of your Excel journey, and that is – practice, practice and practice.

There’s no alternative to putting these formulas to use at every instance, otherwise you won’t be able to remember them accurately. Also, frequently using them will make it a habit out of you and take you to unique solutions that you might never have thought of before.

Lastly, you’ll find a link to my Excel sample sheet with each of the formulas applied accurately. Study it, endorse it and implement it in your office tasks to take some pressure off your shoulders!