MS Excel Formulas Every Professional Must Know

This is an introductory post, instead of trying to replicate the structure and format of an MS Excel Tutorial, we’ll only touch upon the concepts. If any of these formulas catches your fancy, you can always follow up to learn more.

What are MS Excel formulas?

If this sounds too basic, skip to the next section.

Let’s consider a simple example to understand what excel formulas are and what they can do. As you’d know, excel worksheets are organised as 2-dimensional grids, with columns (named as ‘A’, ‘B’, ‘C’ etc) and rows (labelled as ‘1’, ‘2’, ‘3’ etc). You can specific each ‘cell’ with its reference e.g. ‘A1’, ‘A2’, ‘B1’, ‘B2’

If you want to add the values of 5 cells in Column A, you could use the formula:

= A1 + A2 + A3 + A4 + A5

What if you had a hundred values to add, or a thousand. It’ll get pretty tedious to mention each cell name in the formula. This is where excel formulas come into picture.

These simple concepts can be used in complex and powerful ways to deal with a numeric and textual data.

Technically, these are MS excel functions. But most professionals don’t bother about it and refer to them as Excel formulas. So that’s what we’ve done here. Your knowledge and the output you can produce with them is more important than the technical names.

MS Excel Formulas for Numeric / Mathematical Operations

When Gates created MS Office, he made MS Excel the king of the numeric world (while MS Word got language). So it’s only fair that you start building your MS Excel skills with the basic numeric operations.

In the example that we talked about earlier (where you needed to add 5 numbers), instead of the earlier approach of adding 5 cell references, you could simply use a function:

=SUM (A1:A5)

If you want to add the values in the first 100 cells in Column A, instead of the long-winded approach (=A1+A2+…..+A99+A100), all you need to do is change the formula to:

= SUM (A1:A100)

While we are on the topic, what goes inside the brackets are called ‘arguments’. But for this post, to avoid getting it mixed with the literal meaning of that word, we’ll call them as ‘parameters’.

In addition to SUM(), the other basic mathemetical functions you could check out are: AVERAGE(), MIN(), MAX(), COUNT(), SUMPRODUCT()

MS Excel Formulas for String Operations

In the MS Excel world, ‘String’ refers to ‘Text’ (as opposed to Numbers). There’s a bunch of excel formulas that allow you to manipulate, edit & build strings.

Here’s a list of basic excel formulas to manage strings.

LEN(), TRIM(), LEFT(), RIGHT(), FIND(), MID(), REPLACE(), SUBSTITUTE(), CONCATENATE()

Each of these MS excel functions needs one or more parameters to be specified.

LEN (“MBA Crystal Ball”) will display 16. That’s the number of characters (including spaces) in the string.

Similarly, LEFT (“MBA Crystal Ball”, 3) will display the first 3 characters (‘MBA’) of the bigger string.

MID(“MBA Crystal Ball”, 5, 7) starts from position 5 and displays the 7 subsequent characters i.e. ‘Crystal’.

Excel Formulas for Conditional Evaluation

Some of the mathematical functions allow you to add a filter condition. Instead of saying, add ALL the elements in the following range of cells, only add a subset that meets the following condition.

Examples of such conditional excel formulas are: SUMIF(), COUNTIF(), AVERAGEIF()

For instance, in a table of MBA applicants which has a column filled with GMAT scores, if you want to find out the number of candidates who scored 710, you’d use this formula: COUNTIF(A1:A100, 710)

In the family of conditional Excel formulas, the IF statement deserves a special mention. It allows you to test a condition and take an action based on whether the condition is true or false.

Here’s an example: IF ( A1 = A2, “Equal”, “Not Equal”)

This will compare the contents of 2 cells (in A1 & A2) and display the text value of ‘Equal’ if the values match, or ‘Not Equal’ if they don’t.

Excel Formulas for Logical Operations

Here’s the list of basic logical operators: AND, OR, NOT, TRUE, FALSE

You could compare multiple conditions by using AND / OR, and then take the appropriate action.

For instance, consider the formula: IF ( AND (A1 = A2, B1 = B2), “Equal”, “Not Equal”)

This will display ‘Equal’ only if both conditions are met i.e. A1 & A2 have the same values, and B1 & B2 have the same values too.

Excel Formulas for Date & Time

If you had no calendar, no mobile, no way to look at the moon/stars to find out today’s date, what would you do. You’d open up MS Excel and type the date formula: TODAY()

Your little American genie who likes the idiosyncratic MM/DD/YYYY format would tell you it’s 11/20/2014 [or whatever date it is].

You could use nested formulas (one formula within another) to extract specific sub-data from this.

MONTH ( TODAY() ) would give you 11.

DAY ( TODAY() ) would display 20.

YEAR ( TODAY() ) shows 2014.

WEEKDAY ( TODAY() ) would tell say ‘Sunday’ (or the relevant day of the week).

Other date/time functions to check out: DATE(), HOUR(), MINUTE()

Advanced Excel Formulas

The family of Excel formulas is quite large, complex and often confusing.

Once you’ve mastered the basic functions listed earlier, you could check out a few statistical formulas that tend to be used more often than the others: PERCENTILE(), STDEV(), RANK(), GROWTH()

If you want to become indispensable and Excel GURU in your office, attend our monthly hands-on Excel training.

Call us on 08062468296 or send an email to [email protected] and let us discuss how we can help you to improve your Excel skills.