ArtsAutosBooksBusinessEducationEntertainmentFamilyFashionFoodGamesGenderHealthHolidaysHomeHubPagesPersonal FinancePetsPoliticsReligionSportsTechnologyTravel

Microsoft Excel Spreadsheet Basics and Tips

Updated on April 3, 2014

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 cell

Ctrl+Shift+; inserts the current time into the cell

Select a range of cells, type in a value then Ctrl + Enter will enter the same value into all selected cells

Select 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 left

conditional formatting Format -> conditional formatting (e.g. if value > x display in different colour

Shape 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-page

Hyperlinks to web-pages may be used in spreadsheets

Dates are represented by a number that starts from Jan 1, 1900

More Excel Tips

More Excel Tips

Relative cell References:A1 column letter and row number update when copied: (e.g. paste in adjacent cell and it becomes A2 horizontaly or B1 vertically)

Absolute cell References:$A$1 column letter and row number DO NOT update when copied: (e.g. paste in adjacent cell and it remains the same)

Row Absolute cell References:A$1

Column Absolute cell References:$A1

F4 to cycle through the above options - useful when building tables or arrays

Access other Sheets =Sheet2!A1Accesses data from cell A1 in Sheet2

Access other Workbooks =[another-file.xls]Sheet1!A1Accesses data from cell A1 in Sheet1 or workbook in file another-file.xls

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 names

Define 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 cells

COUNT, and SUM are useful for counting/adding cells

COUNTIF, 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

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

Alt + F11 Opens the VBE (Visual Basic Editor)

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

Please Leave Some Feedback

    0 of 8192 characters used
    Post Comment

    • Jonathan Harrison profile image

      Jonathan Harrison 3 years ago

      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.

    • profile image

      bryan896 3 years ago

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

    • profile image

      anonymous 5 years ago

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

    • profile image

      anonymous 5 years ago

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

    • profile image

      JennySui 7 years ago

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

    • profile image

      anonymous 7 years ago

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

    • Sylvestermouse profile image

      Cynthia Sylvestermouse 7 years ago from United States

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

    • ZenandChic profile image

      Patricia 7 years ago

      Lots of info for using excel. Thanks!

    • Laniann profile image

      Laniann 7 years ago

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

    • RuthCoffee profile image

      RuthCoffee 7 years ago

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