Basic Excel Formulas Guide
Mastering the basic Excel formulas is critical for beginners to become highly proficient in financial analysis. Microsoft Excel is considered the industry standard piece of software in data analysis. Microsoft’s spreadsheet program also happens to be one of the most preferred software by investment bankers and financial analysts in data processing, financial modeling, and presentation. This guide will provide an overview and list of basic Excel functions.
A formula is an expression which calculates the value of a cell. Functions are predefined formulas and are already available in Excel.
For example, cell A3 below contains a formula which adds the value of cell A2 to the value of cell A1.
Basic Terms in Excel:-
1. Formulas
In Excel, a formula is an expression that operates on values in a range of
cells or a cell. For example, =A1+A2+A3, which finds the sum of the
range of values from cell A1 to cell A3.
2. Functions
Functions
are predefined formulas in Excel. They eliminate laborious manual entry
of formulas while giving them human-friendly names. For example:
=SUM(A1:A3). The function sums all the values from A1 to A3.
Five Time-saving Ways to Insert Data into Excel
When analyzing data, there are five common ways of inserting basic Excel formulas. Each strategy comes with its own advantages. Therefore, before diving further into the main formulas, we’ll clarify those methods, so you can create your preferred workflow earlier on.1. Simple insertion: Typing a formula inside the cell
Typing a formula in a cell or the formula bar is the most straightforward method of inserting basic Excel formulas. The process usually starts by typing an equal sign, followed by the name of an Excel function.Excel is quite intelligent in that when you start typing the name of the function, a pop-up function hint will show. It’s from this list you’ll select your preference. However, don’t press the Enter key. Instead, press the Tab key so that you can continue to insert other options. Otherwise, you may find yourself with an invalid name error, often as ‘#NAME?’. To fix it, just re-select the cell, and go to the formula bar to complete your function.
2. Using Insert Function Option from Formulas Tab
If you want full control of your functions insertion, using the Excel Insert Function dialogue box is all you ever need. To achieve this, go to the Formulas tab and select the first menu labeled Insert Function. The dialogue box will contain all the functions you need to complete your financial analysis.3. Selecting a Formula from One of the Groups in Formula Tab
This option is for those who want to delve into their favorite functions quickly. To find this menu, navigate to the Formulas tab and select your preferred group. Click to show a sub-menu filled with a list of functions. From there, you can select your preference. However, if you find your preferred group is not on the tab, click on the More Functions option – it’s probably just hidden there.4. Using AutoSum Option
For quick and everyday tasks, the AutoSum function is your go-to option. So, navigate to the Home tab, in the far-right corner, and click the AutoSum option. Then click the caret to show other hidden formulas. This option is also available in the Formulas tab first option after the Insert Function option.5. Quick Insert: Use Recently Used Tabs
If you find re-typing your most recent formula a monotonous task, then use the Recently Used menu. It’s on the Formulas tab, a third menu option just next to AutoSum.9 Basic Excel Formulas For Your Workflow
Since you’re now able to insert your preferred formulas and function correctly, let’s check some fundamental Excel functions to get you started.1. SUM
The SUM function is the first must-know formula in Excel. It usually aggregates values from a selection of columns or rows from your selected range.The SUM formula does exactly what you would expect. It allows you to add 2 or more numbers together. You can use cell references as well in this formula.=SUM(number1, [number2], …)
Example:
=SUM(B2:G2) – A simple selection that sums the values of a row.
=SUM(A2:A8) – A simple selection that sums the values of a column.
=SUM(A2:A7, A9, A12:A15) – A sophisticated collection that sums values from range A2 to A7, skips A8, adds A9, jumps A10 and A11, then finally adds from A12 to A15.
=SUM(A2:A8)/20 – Shows you can also turn your function into a formula.
2. AVERAGE
The AVERAGE function should remind you of simple averages of data such as the average number of shareholders in a given shareholding pool.=AVERAGE(number1, [number2], …)
Example:
=AVERAGE(B2:B11) – Shows a simple average, also similar to (SUM(B2: B11)/10)
3. COUNT
The COUNT function counts all cells in a given range that contain only numeric values.=COUNT(value1, [value2], …)
Example:
COUNT(A:A) – Counts all values that are numerical in A column. However, you must adjust the range inside the formula to count rows.
COUNT(A1:C1) – Now it can count rows.
4. COUNTA
Like the COUNT function, COUNTA counts all cells in a given rage. However, it counts all cells regardless of type. That is, unlike COUNT that only counts numerics, it also counts dates, times, strings, logical values, errors, empty string, or text.=COUNTA(value1, [value2], …)
Example:
COUNTA(C2:C13) – Counts rows 2 to 13 in column C regardless of type. However, like COUNT, you can’t use the same formula to count rows. You must make an adjustment to the selection inside the brackets – for example, COUNTA(C2:H2) will count columns C to H
5. IF
The IF function is often used when you want to sort your data according to a given logic. The best part of the IF formula is that you can embed formulas and function in it.=IF(logical_test, [value_if_true], [value_if_false])
Example:
=IF(C2<D3, ‘TRUE,’ ‘FALSE’) – Checks if the value at C3 is less than the value at D3. If the logic is true, let the cell value be TRUE, else, FALSE
=IF(SUM(C1:C10) > SUM(D1:D10), SUM(C1:C10), SUM(D1:D10)) – An example of a complex IF logic. First, it sums C1 to C10 and D1 to D10, then it compares the sum. If the sum of C1 to C10 is greater than the sum of D1 to D10, then it makes the value of a cell equal to the sum of C1 to C10. Otherwise, it makes it the SUM of C1 to C10.
6. TRIM
The TRIM function makes sure your functions do not return errors due to unruly spaces. It ensures that all empty spaces are eliminated. Unlike other functions that can operate on a range of cells, TRIM only operates on a single cell. Therefore, it comes with the downside of adding duplicated data in your spreadsheet.=TRIM(text)
Example:
TRIM(A2) – Removes empty spaces in the value in cell A2.
7. MAX & MIN
The MAX and MIN functions help in finding the maximum number and the minimum number in a range of values.=MIN(number1, [number2], …)
Example:
=MIN(B2:C11) – Finds the minimum number between column B from B2 and column C from C2 to row 11 in both columns B and C.
=MAX(number1, [number2], …)
Example:
=MAX(B2:C11) – Similarly, it finds the maximum number between column B from B2 and column C from C2 to row 11 in both columns B and C.
8. VLOOKUP
Formula: =VLOOKUP(lookup_value, table_array, col_index_num, range_lookup)
By
far my most used formula. The official description of what it does:
“Looks for a value in the leftmost column of a table, and then returns a
value in the same row from a column you specify…”. (See the full explanation of VLOOKUP)
Basically, you define a value (the lookup_value) for the formula to
look for. It looks for this value in the leftmost column of a table (the
table_array).
Note: If at all possible use a number for
the lookup_value. This makes it a lot easier to make sure the data you
are getting back is a correct match.
If it finds a match
of the “lookup_value” in the left column of the “table_array” it will
return the value in the column you specify using the “index_num”. The
“index_num” is relative to the left most
column. So, if you have the table_index look in column A and you want
what is returned to be what’s in column B the “index_num” would be 2
because the leftmost column, column A in this case, is the 1st column in
the table array and column B is the 2nd column (hence the 2 for the
index number).
If you want what is in column C to be returned you’d put 3
for the index_num. The “range_lookup” is a TRUE or FALSE value. If you
put TRUE it will give you the closest match.
If you put FALSE it will
only give you an exact match. I only use FALSE when using the VLOOKUP
formula.
Example:
You have 2 lists: 1
with a sales person’s ID and the sales revenue for the quarter. Another
with the sales person’s ID and the sales person’s name. You want to
match up the sales person’s name to the sales person’s revenue numbers
for the quarter. They are all jumbled around so to manually match this,
even for a small number of salesmen would leave room for a high margin
of error and take a lot of time.
The first list goes from A1 to B13. The 2nd list goes from D1 to E25.
In cell C1 I would put the formula =VLOOKUP(B18, $A$1:$B$13, 2, FALSE)
B18 = the lookup_value (the sales person’s ID. This is a number that appears on both lists.)
$A$1:$B$13
= the “table_array”.
This is the area I want the formula to search the
leftmost column (column E in this case) for the “lookup_value”. I went
to F because if it finds match
in column E, I want it to return what’s in column F. (The money signs
are there so that the table_array will stay the same no matter where the
formula is moved or copied to. This is called an absolute reference.)
2 = the index_num. This tells the formula the number of columns away from the left most column to return in case of match.
So, if you find a match between the lookup_value and the leftmost
column of the table array, return what’s in the same row in the 2nd
column of the table (the 1st column is always the leftmost column. It
starts at 1, not 0).
FALSE= tells the formula I want it to only return the value if it’s an exact match.
I
would then copy and paste that formula along all the cells in column C
next to the first list.
This would give me a perfectly aligned list with
the sales person’s ID, sales person’s revenue for the quarter, and the
sales person’s name.
In order to get a nice neat list of Sales Person ID, Sales Person Name,
and Sales Person Revenue all next to each other I used the VLOOKUP
formula to compare 1 list to another.9. CONCATENATE
A fancy word for combining data in 2
(or more) different cells into one cell. This can be done with the
Concatenate excel formula or it can be done by simply putting the & symbol
in between the two cells. If I have “Steve” in cell A1 and “Quatrani”
in cell B1 I could put this formula: =A1&” “&B1 and it would
give me “Steve Quatrani”. (The “ “ puts a space in between what you are combining
with the &). I can use =concatenate(A1, “ “, B1) and it will give me the same thing: “Steve Quatrani”
Finding The Right Excel Formulas For The Job
There
are 316 built in functions in Excel.
You’re not going to sit there and
memorize what all of them do (or at least I hope not!). Luckily Excel
has a built in wizard that helps you find the correct formula for what
you’re looking to do (if there is one).
Click the “fx” next to the formula bar in Excel
This brings up a menu and in there you can type in a description of what
you are trying to do and it will bring up the correct excel formula:
I typed in “remove extra spaces” and it returned the TRIM formula that we went over earlier.
More Excel Formulas
There is so much more that I
use on a regular basis such as Time formulas (NOW, TODAY, MONTH, YEAR,
DAY, etc.), other formulas like AND and OR, along with many others.
The real power comes in combining these functions into complicated excel formulas.
--------------------------------------------------------------------------------------------------------------
"Tally Super Star"