- HubPages
*»* - Technology

# 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:

=SUM(A1:A5)

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 Objects

Insert->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## Formulae

### 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 criterione.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

More Examples:

=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 range

F4 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)

=NORMINV(RAND(),MEAN,STDEV)

Where MEAN, STDEV are cells or variables defined in the spreedsheet.

I have also seen this approximation used:

=SQRT(-2*LN(1-RAND()))*COS(RAND()*2*PI()))*STDEV*MEAN

## A Random Walk Down Wall Street

## Please Leave Some Feedback

I always forget how many hidden gems Excel has. Have been using it for years but just for basic stuff. Reading over this lens brought back all my training from years ago.

I have been using for many years, however these tips have refreshed my memory.

I haven't use the Excel spreadsheet before, might have to give it a try. :)

I haven't use the Excel spreadsheet before, might have to give it a try. :)

I have been using microsoft excel for 6 years. This lens is useful for newbies.

Very good information on Microsoft Excel. I love this program and use it a lot.

Great lens!!! I have been using Excel for years and I didn't know some of these shortcuts! Thanks

Lots of info for using excel. Thanks!

Just the other day I entered some info into an Excel spreadsheet. So, these tips will be useful.

Good info, I haven't used Excel in several years now...I would need to brush up!

10