Basic Excel
Learning Objectives:
 Why Excel?
 Basic Excel
 Worksheet Functions
What is Excel? Why Excel?
Excel is a spreadsheet program. It is widely used in the industry because they are simple, and also allow you to view data visually.
Since Excel is widely used, it is usually the basic requirement for many jobs.
Here, we will go into the basics of Excel, and how to use them.
Cells
The basic element of spreadsheet is a cell. We often work with multiple cells. Some cells will be data, some cells will be calculation steps and some cells contains results that require calculations.
Another cell in the same sheet
The first thing to learn in excel is how to refer to another cell. There are 2 ways to reference cells:
 A1 method and
 R1C1 method.
The A1 method is more commonly used, since the columns are already labeled with letters, and the rows are labeled by numbers.
However, if your dataset has many columns (more than 26), it might be a good idea to use the R1C1 method.
To calculate the sum of 2 cells A1 and B1, simply type in the equation box:
= A1 + B1
.
Pressing the Enter key will then give you the following result.
This is assuming that both A1 and B1 contain numerical values. Otherwise, you will get a \#VALUE!
Error.
If you place your mouse cursor on the yellow diamond box, it will tell you what error has occurred.
Because A1 contains a numerical value, and B1 contains text, you cannot apply the Sum function, hence Excel raises an error. We then show an example of multiplication as below.
Range, Worksheet and Workbook
We sometimes need to refer to a group of cells for calculation. Range is always rectangular, hence, we just need the top left cell and bottom right cell. For example, A1:B2
implies cells A1, A2, B1, and B2.
To refer to cells in other worksheet in the same workbook (xlsx file), we use the name and exclamation mark. For example, =Sheet1!A1
To refers to cells in the other worksheet in the different workbook, we use squared bracket. For example, =[appl.xlsx]Sheet1!A1
for workbook that is currently open in Excel. Alternatively, ='C:\\document\\[appl.xlsx]'Sheet1!A1
for workbook that is not open.
Copy and Paste: Iteration
Copy and Paste is the most commonly used operation in Excel. It is very powerful tools to perform calculation repetitively. In programming terminology, it is called iteration.
Referencing: Absolute vs. Relative
You can use absolute referencing, or relative referencing. We start by explaining relative referencing.
Say I have the following values for columns A, B, and C, and I want to calculate D as their product. If I simply copy D1 and paste it in D2, by default Excel will take it as relative referencing, and D2 = A2*B2*C2 = 210
.
What if I want to reuse the value of A1, say I want D2 = A1*B2*C2
? We then use the $
sign to indicate absolute referencing. Placing the $
sign in front of the row or column of the cell will tell Excel to always take that particular row or column even when the formula is copied.
Tips: Pressing F2 will change between relative address and absolute address.
We see that D2=A1*B2*C2
even when we copied the formula from D1. Because of the $
in $A$1
, Excel did not use relative referencing for that row/column.
The following example shows how relative address can be used effectively to reduce typing when we create multiplication table. We just need to enter the formula in B2. Then copy and paste the rest of the cells.
A  B  C  

1  x  1  2 
2  1  =$A2*B$1 

3  2 
Naming
Usually, we will label our rows and/or columns, to let the user know what kind of data or variables are in the spreadsheet. This is for convenience so that the user can follow what you are doing easily.
Moreover, we give a name to a cell. In the ribbon, choose FORMULAS tab and then choose to define the name. Then you can refer a cell by its name. This is particularly useful to make your spreadsheet easy to read and understand by other users.
dividend discount model
Let us illustrate the idea of relative address using the dividend discount model(DDM) example. Consider stock which pays out a fixed dividend of $1 per year for the next 10 years. The rate of inflation is going to be constant at 3%
per year. We want to find out how much the future dividend payouts are worth in present values.
The discount factor is equal to 1/1.03
, which is approximately 0.9709. How do we do this calculation efficiently in Excel? We make use of both relative and absolute referencing.
The only thing that changes here is the overall discount factor, which depends on the number of years ahead. Hence we use absolute referencing for the dividend payout amount, and the yearly discount factor, and then multiply the yearly discount factor accordingly with the number of years. Lastly, we sum up the values to get 8.53.
Worksheet Functions
Excel spreadsheet is very convenient because it has a large number of builtin functions that perform calculation without programming. We will learn how to write these functions by yourself in the next class using VBA.
Arithmetic Operators
We first cover some basic arithmetic operators and some basic mathematics functions.
Function  Meaning 

+ 
Addition 
 
Substraction 
* 
Multiplication 
/ 
Division 
^ 
Power 
SQRT()  Square Root 
ABS()  Absolute number 
Basic Math Functions
Function  Meaning 

COUNT(range)  Counts the number of cells in a range that contain numbers 
SUM(range)  Adds all the numbers in a range of cells 
PRODUCT(range)  Multiplies all the numbers given as arguments 
SUMPRODUCT(range1, range2)  Sum of products of the first elements in both ranges, the second elements,…. 
Basic Statistic Functions
Function  Meaning 

MIN(range)  Returns the smallest number in a set of values. Ignores logical values and text 
MAX(range)  Returns the largest number in a set of values. Ignores logical values and text 
MEDIAN(range)  Returns the median, or the number in the middle of the set of given numbers 
VAR.P(range)  Calculates population variance 
VAR.S(range)  Calculates sample variance 
STDEV.P(range)  Calculate population standard deviation 
STDEV.S(range)  Calculate sample standard deviation 
The following example shows AVERAGE
is used to calculate simple moving average.
A  B  C  

1  n  5  
2  beta  =2/(A1+1) 

3  Date  Price  SMA(n) 
4  02May16  19  
…  
7  06May16  30  =AVERAGE(B3:B7) 
8  07May16  56 
The following example shows AVERAGE
and recursive formula is used to calculate exponential moving average. Note that initial EMA is SMA. And the recursive formula is $$EMA_{t}(n) = \beta P_{t}(n) + (1\beta) EMA_{t1}(n)$$
where $\beta=2/(n+1)$.
A  B  C  

1  n  5  
2  beta  =2/(A1+1) 

3  Date  Price  EMA(n) 
4  02May16  19  
…  
7  06May16  30  =AVERAGE(B3:B7) 
8  07May16  56  =$B$2*B9+(1$B$2)*C8 
Advanced Statistic Functions
Function  Meaning 

COVARIANCE.P(range1 ,range2)  Calculate population covariance using data in range 1 and range 2 
COVARIANCE.S(range1, range2)  Calculate sample covariance using data in range 1 and range 2 
INTERCEPT(yrange,xrange)  Returns the intercept of simple linear regression of y on x 
SLOPE(yrange, xrange)  Returns the slope of simple linear regression of y on x 
STEYX(yrange, xrange)  Returns the standard error of simple linear regression of y on x 
RAND()  Returns a random number greater than or equal to 0 and less than 1, evenly distributed 
RANDBETWEEN(integer1, integer2)  Returns a random number between integer1 and integer 2 where integer1 is less than integer2 
NORM.INV(probability, mean, standard deviation)  Returns the inverse of the normal cumulative distribution at the given probability under the specified mean and standard deviation. 
How to generate normal distribution? We will use a method called inversion. Use RAND
to generate uniform distribution and then use NORM.INV
is to generate a normal variable.
Lookup
Lookup functions allow you to get data from another table. Two basic lookup functions are VLOOKUP
or HLOOKUP
. They help to locate particular cells in table. Using MATCH
and INDEX
together allows us to have a flexible vlookup and hlookup, when we have 2 different types of data.
For example, we could use =INDEX(range1, MATCH(key, range2))
.
Function  Meaning 

VLOOKUP(key, range, column, FALSE)  Looks for key in the leftmost column of a table (given by the range), and then returns a value in the same row from a column you specify. 
HLOOKUP(key, range, column, FALSE)  Looks for key in the top row of a table (given by the range) and returns the value in the same column from a row you specify. 
MATCH(key, range)  This helps you search for a particular value within a dataset. The output returned is the relative position of the value in the dataset, for example, D8. The key is the value that you want to find, and the range is the range of data that you are searching for. 
INDEX(range, index)  Returns a value or reference of the cell at the intersection of a particular row and column, in a given range. The output will be the content of that particular cell at that index. 
The following example shows how vlookup can be used to merge two tables.
A  B  C  D  

1  Stock  Price  Stock  Volume 
2  Apple  10  IBM  10 
3  IBM  20  20  
4  30  Apple  30  
5  
6  Stock  Price  Volume  
7  Apple  =VLOOKUP(A7,$A$1:$B$4,2,FALSE) 
=VLOOKUP(A7,$C$1:$D$4,2,FALSE) 

8  IBM  
9 
The following example shows how index and match can be used to merge two tables.
A  B  C  D  

1  Stock  Price  Stock  Volume 
2  Apple  10  IBM  10 
3  IBM  20  20  
4  30  Apple  30  
5  
6  Stock  Price  Volume  
7  Apple  =INDEX(B2:B4,MATCH(A7, A2:A4,0)) 
=INDEX(D2:D4, MATCH(A7,C2:C4,0)) 

8  IBM  
9 
Cell Referencing Function
The following cell referencing functions are very useful to make the code clean and tidy. Offset
moves the cell reference to another cell or range of cells. Indirect
convert text to cell reference and Address
convert cell reference to text.
Function  Meaning 

OFFSET(cell, row, column,height and width)  Returns a reference to a range that is a given number of rows and columns from a given reference. Cell refers to the reference cell that you are taking reference from. For row, positive values will mean cells downwards from the reference cell, and negative values will mean cells above the reference cell. For column, positive values will mean cells to the right of the reference cell, while negative values will mean cells to the left of the reference cell. 
INDIRECT(address string)  Returns the reference specified by a text string. One example would be INDIRECT("A1") , which returns the contents of the cell A1. 
ADDRESS(row, column)  Creates a cell reference as text, given specified row and column numbers. For example, ADDRESS(2, 7) will return $G$2 as output. 
Combination of INDIRECT
and ADDRESS
is useful if you do not want to type out the address string, or you have very large datasets. We can use INDIRECT(ADDRESS(1,2))
.
The following example shows offset can be used to make spreadsheet customization. We consider Kday momentum where K allow the user to input. While OFFSET
is rather simple, we need to take care the case when momentum cannot be calculate in the first K days.
A  B  C  

1  K  5  
2  Date  Price  KMomentum 
3  02May16  10  =IF(COUNT($A$3:A3)>=$B$1+1,B3OFFSET(B3,$B$1), "") 
4  03May16  9  
5  04May16  8 
The following sample shows more advanced use of OFFSET
. We consider nday simple moving average where n allows the user to input. We only start the calculation when on the nth day.
A  B  C  

1  n  5  
2  Date  Price  SMA(n) 
4  02May16  10  =IF(COUNT($A$3:A3)>=$B$1,average(Offset(B3,0,0,$B1$)), "") 
5  03May16  9  
6  04May16  8 
The following sample shows more advanced use of OFFSET
. We consider nday simple moving average where n allows the user to input. We only start the calculation of EMA when on the nth day. The first EMA is SMA. Then the rest is by the updating recursive formula.
A  B  C  

1  n  5  
2  beta  =2/(A1+1) 

3  Date  Price  EMA(n) 
4  02May16  19  =IF(COUNT($A$4:A4)=$B$1,average(Offset(B4,0,0,$B1$)),IF(COUNT($A$4:A4)>$B$1,=$B$2*B4+(1$B$2)*C3, "") 
5  03May16 
Hyperlink
The following function refers the external link. Hyperlink
connects to external file or internet address.
Function  Meaning 

HYPERLINK(location, text)  Creates a shortcut or jump that opens a document stored on your hard drive, a network server, or on the internet. For location, you could use places on your computer such as Desktop or C:, or use internet address such as http://www.google.com . The text will be shown as a hyperlink, for example, you could put Click to open Google search' . 
Text Function
The following shows some basic text functions. Len
is to find the length. Trim
and Clean
is to clean up the text. Concatenate
is to combine text.
Function  Meaning 

LEN(text)  Short form for length. Returns the number of characters in a text string. For example, LEN(apple) returns 5. 
TRIM(text)  Removes all spaces from a text string except for single spaces between words. 
CLEAN(text)  Removes all nonprintable characters from text. 
CONCATENATE(text1, text2, …)  Joins all text strings into one text string. For example, CONCATENATE("I am", "a boy", ...) returns I ama boy . Note that space is not automatically added between text. 
There are some advanced text functions: Left
, Right
and Mid
extract part of text. Find
and Search
are to look for string and Substitute
is to replace text.
Function  Meaning 

LEFT(text, number)  Returns the specified number of characters from the left of the text string. For example, LEFT("testing", 2) will return you te . 
RIGHT (text, number)  Returns the specified number of characters from the right of the text string. For example, RIGHT("testing", 2) will return you ng . 
MID (text, start position, number)  Returns the specified number of characters from the start position of the text string. For example, MID("testing", 3,2) will return you st 
FIND(text, target)  Returns the starting position of one text string within the target text string. For example, FIND("apple", "I like apple") returns 8. Note that FIND is casesensitive, meaning that uppercase letters are not the same as lowercase letters. For example, FIND("APPLE", "I like apple") returns an error. 
SEARCH(text, target)  Same as FIND but not casesensitive. For example For example, SEARCH("APPLE", "I like apple") returns 8 
SUBSTITUTE(target, new text, old text)  Replaces existing text with new text in a text string. For example, SUBSTITUTE("I like apple", "apple", "banana") returns I like banana . 