Excel

 

 

Download a 42page 'Getting Started with Excel97' file

to Advice ..... to Index

What's a Spreadsheet

Range Names (Naming locations)

Decisions ('what if')

Formats(how it looks)

Programming Excel

Tips

 

What is a Spreadsheet ???

A spreadsheet is similar to a very large page divided into columns and rows.

At the intersection of a column and row is a cell

 

The shaded cell below is named after Column C, Row 2: C2

 

A

B

C

D

E

F

1

 

 

 

 

 

 

2

 

 

 

 

 

 

3

 

 

 

 

 

 

 

 

 Into this cell and any cell can be placed text, numbers and formulas, thus:

 

 

A

B

C

D

E

F

1

 A607W

 Joe

 

25.34 

£120.00 

 

2

 

 

 

 

 

 

3

 +E1*4

 

 

 

 

 

A1 is part letters, part numbers - Alphanumeric - classed as text.

D1 is numeric and E1 is another form of numeric (currency).

A3 is a formula, the cell can show the formula (as above) or show the result, with the formula hidden behind it in the cell (the default).

 

 

A

B

C

D

E

F

1

 A607W

 Joe

 

25.34

£120.00 

 

2

 

 

 

33.67

 

 

3

 +E1*4

 

 

1,234.00

 

 

4

 

 

 

11,293.01

 

 

 

To produce the Total shown in D4 all you need to do is place the cursor in that cell

and double-click the sigma tool

  on the toolbar.

 

Auto Recalculation

If all you could do were enter data into a sheet, then it would not be a computer spreadheet. The spreadsheet however can calculate by:

 

a. Allowing auto totalling of lists, and placement of hundreds of other formulae.

 

b. Automatically recalculating when the content of any cell is changed.

 

D4 above contains a Total, changing the value of D1, D2 or D3 will automatically recalculate and show the new Total.

 

Exercise 1

 

 

A

B

C

D

E

F

1

 A607W

 Joe

 

25.34

£120.00 

 

2

 

 

 

33.67

 

 

3

 

 

 

1,234.00

 

 

4

 

 

 

11,293.01

 

 

 

 

 

 

 

 

 

Decisions ('What if')

The Sum formula used in the previous section, was not really a formula but a Function.

Click in the cell where the total is. Ensure that you formula bar is visible (if not use the View menu to display it) .

In the formula bar under the toolbars you will see

=Sum(D1:D3)

 

This was placed there when you clicked the Sigma tool.

You could have typed this directly into the cell without use of the Sigma tool.

Another function you can type in (or use the Excel function (Fx) tool on the toolbar) is one that will make a decision for you and thus introduces some intelligence into the spreadsheet. It is called the IF function. If a certain condition applies do a certain action, if not do a different action.

This is it's syntax:

=If(Condition,True,False)

If (A6=10, do this, if not do this)

The syntax in detail

=If(<Location><operator><location or value>, <location/operator/value - if True>, <location/operator/value - if False>).

Where:Location = Cell Address..eg: A6

Where:Operator is any of = + - < > = / .....etc

Showing syntax is complex so here's an example to simplify it:

Example 1:

Assuming the content of Cell A6 = 10 and the content of A7 =12 then

=If(A6<10,A7*2,0) This will result in 0 because A6 is not less than 10

If you change the content of A6 to 9

=If(A6<10,A7*2,0) This will result in 24 because A6 is less than 10

 

Exercise 2

 If you can't get this to work the first time around, don't worry - keep trying - eventually you WILL get it - and it will be extremely useful for you in the future.

 There are many more Functions which will help you to place intelligence into the sheet - I'll leave it to you to investigate these.

  

 

 

 

 

 

 

 

 

 

Tips

 Tip1

Save it before you lose it

As soon as you have brought in your spreadsheet and even though the sheet is blank, save it with a recognisable name to an appropriate folder. By doing this, you can do quick saves (File, Save) as you work on the sheet - and thus avoid the most common mistake of all - working for hours on something without saving and then for some reason, losing the lot.

Tip2

Worrying first about the content - then how it looks

After saving the your new sheet with a name & location - DON'T WORRY TOO MUCH ABOUT the look or form of the sheet - just concentrate on getting what you want from your head to the cells. Worrying about the format at this stage can make you forget what you want to do.

Before placing formulas however - the format is very important and cannot be left otherwise the results might not be what you expect. To decide how your new sheet will look and display results, see Formats

 

Programming Tips: Getting started & simple programs

If you are interested in programming in Excel then see Programming Excel - Visual Basic for Applications (VBA)

 

 

 

 

 

 

Range Names (Naming locations)

You can name a single cell (a range with only one cell) or many cells ( e.g: B1 to B6 , or B1:B6)

With the range named, you can perform actions on that range or quickly go to that range:

 

Example:

  • Click in cell C4 (C4 is selected)
  • Click in the Name Box which is below the Font Name box.
  • (C4 in Name Box is selected) as shown here:

Type a name into the Name Box e.g: Home1

 

Press Enter:

This will associate the name Home1 with cell C4

Now use Edit, Go To, select the range name Home1 in the Go To box....click OK

.......the cursor will very quickly relocate to that cell  

 

To delete a range name:

Insert, Name, Define, <click on the name>, Delete, OK

 

 Getting Started with Excel97 - (42 page html document)

 If you don't care to save the web pages from this site in order to learn Excel, then here is a 42 page booklet on 'Getting Started with Excel'. To save downloading time is has been zipped up. The file is excelgs1.zip and is 350KB zipped (470KB unzipped).

Download instruction:

Click the excelgs1.zip in the download box below, chose Save to disk, select a temporary folder (or My Documents) to download the zip file to, allow to download (on a 56K modem this should take about 2-3 minutes). When downloaded is completed, you can close the your browser and pull out of the Internet. To see how to unzip and use the file see below before downloading

DOWNLOAD

excelgs1.zip

 

Unzipping the file on your computer:

Next, will be the use of Winzip (if you haven't got Winzip click here download and unzip it, then run setup). Using My Computer or Windows Explorer, locate the zip file (excelgs1.zip) and click it (double click in Win95). This will start Winzip.

In Winzip, click the Extract button (to unzip the file) and decided into which folder you want the 98 files from the zip file, to be unzipped to. Note: It would be better to firstly use My Computer to create a new folder calling it for example ' Excel Lesson1 '

 

Viewing the file on your computer:

Close Winzip, use My Computer /or within Word97, locate gs3(1).htm and open it. This is an HTML file, a web page. If you open it via My Computer it will automatically run Internet Explorer to view the web page. If you open it via Word97, it will be converted and you can view it as it, or use File, Web Page Preview, to view it in Internet Explorer.

Finally in the file there are links (usually in blue), these hyperlinks allow you to jump to another place in the 42 page web document. You can use the Web toolbar's back arrow, to go back to the original location.

This 42 html document is better than buying an expensive 200-400 page book on Excel, but although is gives easy to follow instructions on Excel Basics covering many different areas you will need know, it is not an advanced document.

Comments:

I hope that you find this and the use of my web-site for your study of Excel, worthwhile. If you have any comments to improve this aspect of the site, email me at michael_small@lineone.net (no file attachments unless previously agreed between us)