Microsoft Excel Spreadsheet Basics and Tips
How To Use Microsoft Excel: Some tips and extra features
Microsoft Excel spreadsheets are relatively easy to use and it can be quite quick to get started and create or edit spreadsheets, but Excel is a very powerful program with many useful, hidden features. I have detailed here a few useful tips and lesser-known features that may improve your experience with this useful tool. I have included some examples of applications for Microsoft Excel too.
Excel Spreadsheet Basics and Tips
Excel Basics and Data Entry - A few hints and tips for speeding up data entry
Entering data into the cells of a spreadsheets simply involes clicking on the cell and typing in a value or a formular e.g. typing the following into cell A6:
will return the sum of the values in each of the cells A1 to A5
but here are few extra tips for data entry:A1 notation is used as standard (column letter, row number) but R1C1 notation is also supported (I shall use the simpler A1 method here)Ctrl+; inserts the current date into the cellCtrl+Shift+; inserts the current time into the cellSelect a range of cells, type in a value then Ctrl + Enter will enter the same value into all selected cellsSelect a range of cells, type in a value then Enter will enter the value and move down one cell in the selected range; Shift+Enter to move upTAB to move right; Shift+TAB leftconditional formatting Format -> conditional formatting (e.g. if value > x display in different colourShape can be used to draw on the sheet (arrows, standard shapes etc)Spreadsheet can be written out in html format for inclusion in a web-pageHyperlinks to web-pages may be used in spreadsheetsDates are represented by a number that starts from Jan 1, 1900
More Excel Tips
More Excel Tips
Naming ObjectsInsert->Name->Create Allows you to name a cell (instead of using A1 or R1C1 notation) - be careful if you use VBA, which doesn't automatically update cell namesDefine Name Similarly names can be assigned numeric values and used as variables (e.g. "=TAXRATE * A1")Define Name Similarly names can be assigned text values and used as variables (e.g. "=COMPANYNAME")names can be assigned to formulae (e.g. "=A1PLUSB1") using the Define Name function
or Formulas?Array Formulae e.g. =A1:A4 * B1:B4 allow actions to be applied on multiple cellsCOUNT, and SUM are useful for counting/adding cellsCOUNTIF, and SUMIF are useful for counting/adding cells which fulfill a certain criterion
e.g. =COUNTIF( A1:A100 , 10 ) returns number of cells with value of 10
=COUNTIF( A1:A100 , <0 ) returns number of cells with negative value
=COUNTIF( A1:A100 , * ) returns number of cells with text
=COUNTIF( A1:A100 , "hello" ) returns number of cells with text "hello"
=COUNTIF( A1:A100 , "????" ) returns number of cells with four letter word
=SUM(IF(ISNUMBER( A1:A100), 1 0)) returns number of numerical values in the range
=SUM(IF(ISERR( A1:A100), 1 0)) returns number of errors cells in the range
=SUM(IF(FREQUENCY( A1:A100 , A1:A100 ) >0 , 1 0)) returns number of unique numeric values in the rangeF4 to cycle through the above options - useful when building tables or arrays
VBA (Visual Basic) Basics
VBA (Visual Basic) Basics
More Microsoft Excel Books
Financial (and Other) Simulations Using Excel
GAUSSIAN distributions and Random Events
One of my favourite uses for Excel is to simulate the financial markets. I make investments based on which way I think a particular asset price will move, but also simulate possible future scenarios, to see what I might make or lose if unexpected outcomes occur. There is a RAND() function that generates an evenly distributed random number between 0 and 1 and also NORMDIST, NORMINV and NORMSDIST functions that returns normal (gaussian) distributions.
So how do you simulate a "Normal" or "Gaussian" random series (e.g. to simulate a "Random Walk" as in the book excellent economics book "A Random Walk Down Wall Street" by Burton Malkiel)
Where MEAN, STDEV are cells or variables defined in the spreedsheet.
I have also seen this approximation used: