Almost all professionals have used MS Excel in some form or the other. Most haven\u2019t taken up any formal Ms Excel tutorial. Through self-study, they\u2019ve picked up basic excel formulas and features to help them with their day-to-day tasks.<\/p>\n
However, most don\u2019t realise the power that\u2019s available at their finger tips if they\u2019d take the pains to understand some special excel formulas. <\/p>\n
Apart from all the fantastic theoretical concepts that MBA students learn in a classroom, most realise that the lowly MS Excel formulas they\u2019ve ignored so far can be their best friends as they head out into their post MBA jobs. In fact, many quant focussed MBA jobs (like consulting) specifically need advanced excel skills.<\/p>\n
In my post-MBA job, I spent 5 years managing Mergers & Acquisitions (M&A) transactions. In almost all the M&A valuation modelling that I did (some deals valued at hundreds of millions of dollars), there was a heavy dependency on these seemingly simple and mundane, yet powerful formulas.<\/p>\n
But you don\u2019t have to head to business school to learn them. We\u2019ve picked out the 10 best MBA Excel formulas that can save you and your company valuable hours and money – by automating routine tasks, improving efficiency and doing complex calculations that your brain would\u2019ve struggled with.<\/p>\n
As this is an introductory post, instead of trying to replicate the structure and format of an MS Excel Tutorial, we\u2019ll only touch upon the concepts. If any of these formulas catches your fancy, you can always follow up to learn more.<\/p>\n
If this sounds too basic, skip to the next section.<\/p>\n
Let\u2019s consider a simple example to understand what excel formulas are and what they can do. As you\u2019d know, excel worksheets are organised as 2-dimensional grids, with columns (named as \u2018A\u2019, \u2018B\u2019, \u2018C\u2019 etc) and rows (labelled as \u20181\u2019, \u20182\u2019, \u20183\u2019 etc). You can specific each \u2018cell\u2019 with its reference e.g. \u2018A1\u2019, \u2018A2\u2019, \u2018B1\u2019, \u2018B2\u2019<\/p>\n
If you want to add the values of 5 cells in Column A, you could use the formula: <\/p>\n
= A1 + A2 + A3 + A4 + A5<\/p>\n
What if you had a hundred values to add, or a thousand. It\u2019ll get pretty tedious to mention each cell name in the formula. This is where excel formulas come into picture.<\/p>\n
These simple concepts can be used in complex and powerful ways to deal with a numeric and textual data.<\/p>\n
Technically, these are MS excel functions. But most professionals don\u2019t bother about it and refer to them as Excel formulas. So that\u2019s what we\u2019ve done here. Your knowledge and the output you can produce with them is more important than the technical names.<\/p>\n
When God Gates created MS Office, he made MS Excel the king of the numeric world (while MS Word got language). So it\u2019s only fair that you start building your MS Excel skills with the basic numeric operations.<\/p>\n
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:<\/p>\n
=SUM (A1:A5)<\/p>\n
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:<\/p>\n
= SUM (A1:A100)<\/p>\n
While we are on the topic, what goes inside the brackets are called \u2018arguments\u2019. But for this post, to avoid getting it mixed with the literal meaning of that word, we\u2019ll call them as \u2018parameters\u2019.<\/p>\n
In addition to SUM(), the other basic mathemetical functions you could check out are: AVERAGE(), MIN(), MAX(), COUNT(), SUMPRODUCT()<\/p>\n
In the MS Excel world, \u2018String\u2019 refers to \u2018Text\u2019 (as opposed to Numbers). There\u2019s a bunch of excel formulas that allow you to manipulate, edit & build strings.<\/p>\n
Here’s a list of basic excel formulas to manage strings<\/em>. Each of these MS excel functions needs one or more parameters to be specified.<\/p>\n LEN (\u201cMBA Crystal Ball\u201d) will display 16. That\u2019s the number of characters (including spaces) in the string.<\/p>\n Similarly, LEFT (\u201cMBA Crystal Ball\u201d, 3) will display the first 3 characters (\u2018MBA\u2019) of the bigger string.<\/p>\n MID(“MBA Crystal Ball”, 5, 7) starts from position 5 and displays the 7 subsequent characters i.e. \u2018Crystal\u2019.<\/p>\n 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.<\/p>\n Examples of such conditional excel formulas are: SUMIF(), COUNTIF(), AVERAGEIF()<\/p>\n 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\u2019d use this formula: COUNTIF(A1:A100, 710)<\/p>\n 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.<\/p>\n Here\u2019s an example: IF ( A1 = A2, \u201cEqual\u201d, \u201cNot Equal\u201d)<\/p>\n This will compare the contents of 2 cells (in A1 & A2) and display the text value of \u2018Equal\u2019 if the values match, or \u2018Not Equal\u2019 if they don\u2019t.<\/p>\n Here’s the list of basic logical operators: AND, OR, NOT, TRUE, FALSE<\/p>\n You could compare multiple conditions by using AND \/ OR, and then take the appropriate action.<\/p>\n For instance, consider the formula: IF ( AND (A1 = A2, B1 = B2), \u201cEqual\u201d, \u201cNot Equal\u201d)<\/p>\n This will display \u2018Equal\u2019 only if both conditions are met i.e. A1 & A2 have the same values, and B1 & B2 have the same values too.<\/p>\n When you are dealing with a table of data (i.e. the values are spread across multiple columns and rows), the VLOOKUP and HLOOKUP functions can make it easy to search for specific values.<\/p>\n This can be a little difficult to understand. So, out pops another example to help us out.<\/p>\n Consider the following table, which displays the column names (A, B, C, D) and Row numbers (1, 2, 3, 4). <\/center> Formula 1: VLOOKUP (\u201cPooja\u201d, A2:D4, 4)<\/p>\n This will cause excel to first scan the cell range (from A2 to D4), go down to Row 4 (i.e. Pooja\u2019s data) and then pick up the value from the 4th column in the cell range (i.e. GMAT score). The value displayed by the forumula is \u2018680\u2019.<\/p>\n Formula 2: VLOOKUP (\u201cVijay\u201d, A2:D4, 3)<\/p>\n This will scan the same cell range (from A2 to D4), go down to Row 5 (i.e. Vijay\u2019s data) and then pick up the value from the 3rd column in the cell range (i.e. City). The output of this formula is \u2018Mumbai\u2019.<\/p>\n If you had no calendar, no mobile, no way to look at the moon\/stars to find out today\u2019s date, what would you do. You\u2019d open up MS Excel and type the date formula: TODAY()<\/p>\n Your little American genie who likes the idiosyncratic MM\/DD\/YYYY format would tell you it\u2019s 11\/20\/2014 [or whatever date it is].<\/p>\n You could use nested formulas (one formula within another) to extract specific sub-data from this.<\/p>\n MONTH ( TODAY() ) would give you 11. Other date\/time functions to check out: DATE(), HOUR(), MINUTE()<\/p>\n The family of Excel formulas is quite large, complex and often confusing. <\/p>\n Once you\u2019ve 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()<\/p>\n Excel also has several financial formulas: IRR(), PV(), FV(), RATE()<\/p>\n If you are a self-starter, the online excel help that comes with the software is well-structured and user-friendly. Don\u2019t get overwhelmed by the full list of basic and advanced excel functions that span across various categories. In fact, you don\u2019t need an advanced excel formulas pdf file to get started.<\/p>\n
\nLEN(), TRIM(), LEFT(), RIGHT(), FIND(), MID(), REPLACE(), SUBSTITUTE(), CONCATENATE()<\/p>\nExcel Formulas for Conditional Evaluation<\/h3>\n
Excel Formulas for Logical Operations<\/h3>\n
Excel Formulas for Database Operations<\/h3>\n
\n\n\n
\n <\/td>\n A<\/td>\n B<\/td>\n C<\/td>\n D<\/td>\n<\/tr>\n \n 1<\/td>\n Name<\/td>\n Age<\/td>\n City<\/td>\n GMAT<\/td>\n<\/tr>\n \n 2<\/td>\n Raj<\/td>\n 27<\/td>\n Chennai<\/td>\n 730<\/td>\n<\/tr>\n \n 3<\/td>\n Pooja<\/td>\n 25<\/td>\n Delhi<\/td>\n 710<\/td>\n<\/tr>\n \n 4<\/td>\n Vijay<\/td>\n 28<\/td>\n Mumbai<\/td>\n 680<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
\nNow consider the following formulas<\/p>\nExcel Formulas for Date & Time<\/h3>\n
\nDAY ( TODAY() ) would display 20.
\nYEAR ( TODAY() ) shows 2014.
\nWEEKDAY ( TODAY() ) would tell say ‘Sunday’ (or the relevant day of the week).<\/p>\nAdvanced Excel Formulas<\/h3>\n
Online Excel Tutorial vs Classroom Course vs Self-Study<\/h3>\n