VIRGINIA MONTECINO

Montecino's CS 103 Course Page


Lecture 5
 Spreadsheets

Microsoft EXCEL - An electronic spreadsheet that operates on Windows machines.  It is also bundled as part of the Microsoft Office software package, which includes MS Word, Power Point, and Access.  It is an electronic version of a worksheet or accountant's ledge.

Some basic functions:
create formulas,
numeric calculations 
list compiler
create charts

Workbook - contains related worksheets and charts
Each workbook contains Three (3) blank worksheets
Sheet 1, Sheet 2, Sheet 3

Each "sheet" looks similar to ledger paper.

User can:
- can add and delete workbook sections
- copy information from one worksheet section to another
- summarize data in the entire workbook.

The "sheet" is divided into vertical columns and horizontal rows.

Columns are labeled from A to Z, then AA to AZ and so on.

Rows are numbered : 1, 2, 3, and so on

Each cell has an "address, " which coincides with  its position in a row and column.  For example
the cell B12 label means that the cell is in column B and on Row 12.

cell pointer - dark rectangle that indicates the active cell

Values - numbers, formula, functions
Labels - names to identify data (can contain text, numerical information - such as dates, times , addresses not used in calculations. Put an exclamation mark (!) in front of a number if you want to use the number as a label name. This prevents the number from being mistaken for a number that should be calculated. Ex: !100%

Format Cells - general, text, number, currency, date, time, percent, fraction, etc.  Can format numbers w. specific decimal places.  Can click right mouse button to format cells. 

Other formatting options: See format on menu bar.

Title bar - displays program name and active filename

Standard menu bar - standard Windows functions, with Excel-specific functions added.

Standard toolbar - displays"save," " print," " copy/paste,"  ....

Formatting tool bar - displays "bold," "italics," "underline," ...

Formula bar - appears below tool - includes Name box, formula buttons

Sheet tabs - left bottom active (sheet you are working in) or inactive sheet . Right click sheet tab to rename sheet.

Freeze Panes - to keep rows and columns visible when you have to scroll through worksheets with many columns and rows. Select columns or rows -> Window -> Freeze panes

Zoom - magnify or reduce display.  Select View -> zoom

Charts
Though all chart creation is not this straightforward, you can create a chart from your data this way:
Select the cells you want to appear in the chart (include column and row labels if you want them to appear) . Click Chart Wizard (bar chart icon) on menu bar.  Follow instructions.

Horizontal Axis - X
Vertical Axis - Y
Legend - a "box" that identifies the data categories in your chart through patterns or colors

Absolute Cell Reference-  Absolute formula reference formulas calculate ONLY the values in  specific cells.  Even if you move the formula it will still refer to the specified cells. Absolute cell references have a dollar sign ($) in front of the column and row identifier.  For example:

The formula looks like this:  $A$1+$B$1

The absolute cell reference above tells the Excel program to calculate the sum of Column A, Row 1 and Column B, Row 1.

Relative Cell Reference- Relative cell reference formulas calculate based on the same relationship, which can be applied to different cells.  The formula in a relative cell reference can be copied and applied to other cells.

Formula - an equation that analyzes data. 

Function - is a predefined formula
See fx symbol on formula bar. You can select from predetermined formulas, such as SUM, AVERAGE.

Example of a SUM function -  =SUM(B2:B8) is the formula which commands the program to add/sum the values in Column B, rows 2 through 8. Formulas and functions begin with an equal (=) sign.

What-if function - You can insert different values to see a "what-if" scenario.  For example, you would use this function if you want to calculate a possible increase in sales to see a projected profit. You must use absolute cell references to accomplish this analysis. See pp B14-15 in Microsoft Office 97 workbook. 

If-then function - Returns a value or comment if one condition you specify is true and another is false.  For example, take this formula: 
=IF(E3>80, "Good Participation", "Poor Participation")

If good participation in a group project is a grade of  80 or above, this formula, applied to a group of students' grades could target who has good participation and who has poor participation.  The terms "Good Participation" or "Poor Participation" would appear in relation to the student's grade.
 

PRINT- some basic options

Print preview - File -> Print Preview
Print gridlines - File -> Page Setup -> sheet -> gridlines
Print selection - Highlight print area - File -> Print Area -> Set print area
Landscape/ portrait - File -> Page Setup -> page -> Select portrait or landscape
Print size - File -> Page Setup -> page -> Select scaling, then adjust %

Save Excel worksheet data and Excel charts as HTML documents.
Select the "Save as HTML" option under File menu.  If this option does not appear in your personal software program , you need to load the Internet Assistant option from your Excel or Office disk.  Be sure the name you give the file fits UNIX standard for Web pages (no spaces in name, for example).  Be sure the file has a ".htm" or ".html" extension.

Worksheets are saved in html table format. Charts are saved separately, with a .gif extension.  You will have to use WSftp, or another file transfer protocol program, to load the spreadsheet file (with the .htm or .html extension) and the chart file (with the .gif) extension into your Web space.

The html tags which embed the chart location in the html document is accomplished in the "save as html" procedure.

Charts can be tricky to size for the Web.  Formatting doesn't always come out the way you expect.  Experiment with option in the "save as" html procedure.
 
 

back to top

Montecino's CS 103 Course Page
Montecino's Home Page

Email montecin@gmu.edu