Lecture outlines Spreadsheet Lectures COMP1900 - Semester 1 2002.

Lecture 1 - Covers

What are they? - basically a two dimensional table of ROWS & COLUMNS. Used in paper form for a long time represented typically by a budget (month columns and budget item rows and the intersection being amounts of money (for each item for that month)).

An EXCEL spreadsheet can have up to 65,536 ROWS and 256 COLUMNS.

Advantages Easy to create, Accuracy, automatic-recalculation.

Uses Budgets, what-if analysis.

History Visicalc created by Daniel Bricklin in 1978 & became the PCs "killer" application.

Files, Workbooks & Worksheets - saving & opening worksheets - EXCEL files have an xls extension (although this isn't necessary on a Macintosh you should still give all files an extension). Each EXCEL file is called a Workbook and each Workbook can consist of up to 256 Worksheets. Spreadsheets are then created, typically on a single worksheet, (note the default number of worksheets created in a new Workbook is 3). NOTE also that sheets should be renamed to something appropriate (holding down the CONTROL key simply click on the sheet tab & use the rename option).

Files & Folders - Remember to create a new FOLDER (appropriately named) as a place to keep all your spreadsheets (or you can use the Spreadsheets folder created during the first Lab).

Cells and their addresses - The intersection of a COLUMN & ROW is called a CELL. Each cell has an address made up of a Column Address (A, B, C, .... , IV) and a Row Address (1, 2, 3, ......., 65,536), examples D7, BT789, the column after BZ is CA.

Ranges - selecting & moving ranges (1) select All button, (2) selecting a column, (3) selecting a row

(4) selecting a single range

(i) drag mouse from top left to bottom right, OR

(ii) select top left cell, hold down SHIFT key then click on bottom right cell

(iii) hold down SHIFT key and press arrow keys to extend selection

(5) multiple ranges

(i) Select first range (see (4))

(ii) Hold down APPLE key

(iii) Select second range

Moving around a spreadsheet

(i) APPLE HOME returns you to cell A1

(ii) APPLE END takes you to the bottom right hand corner where data has been entered

(iii) Arrow Keys & 'page up' & 'page down'

To see the cell number of a particular Column - starting from A (and while holding down the SHIFT key) move the Left or Right Arrow key, the cell number is displayed in a Yellow Box near the current cursor position.

Lecture 2 - Covers

Cell contents - Labels(text up to 32,000 characters), numbers, date/time, formula (later versions of EXCEL now have Post Codes & Telephone numbers as special cell content types)

Adjusting row & column sizes - (1) show via mouse (2) Format\Row\Height (13 units standard) and Format\Column\Width (10 units standard). (3) a double click to the right of a column heading sets the width to largest entry.

Autofill - entering a series (1) Fill first cell with Month/Day then copy across (2) Sometimes you need to enter two values for EXCEL to work out what you want

Summing Figures/Formula - Relative v Absolute cell references (Page 120- 123 of the brick)

Linking - to other sheets to other workbooks

Calculations - some useful functions (Formula Bar - ie the contents after the = sign showing the formula inside a cell)

UNDO or EXIT (don't save)

Visual enhancement - formatting cells & their contents, merging cells (Format\Cells\Alignment tab\check Merge Cells)

Sorting Data - Data\Sort

Searching for specific data - Edit\Find (& replace)

Printing a spreadsheet - (1) always do a Print Preview before printing (saves trees)

Getting Help - F1 context sensitive help

Lecture 3 Part 1 - Covers - This material not covered in the Office Brick

(a) Data Validation

Data Validation	Restrict type of data users can enter	Display
	Yes	Input Message - Display helpful messages when cell is selected PLUS Error Alert - message indicating, as a minimum, what the valid types of data are.
	No (Settings - Allow any value)	Input Message - Display helpful messages when cell is selected

Select cell (or cells) then from the Menu bar select DATA\VALIDATION

Allow
Any Value
Whole Number or Decimal	Restrict cell values to Whole Numbers or Decimal via a number of options such as "between, not between, equal to, etc.)
Date or Time	Restrict cell values to Date or Time via a number of options such as "between, not between, equal to, etc.) eg between =TODAY()-30 and =TODAY() restricts dates which can be entered to the last 30 days
List	Can enter a list separated by commas or an area of a spreadsheet
Custom	Enter a formula to restrict totals etc to some limit.

(b) Conditional Formatting.

Lecture 3 Part 2 - Covers

Charts - many types Column, Bar & Line charts; Pie Charts or Scatter Diagrams

Pages 136 - 140 Brick

Making a chart - again multiple ways this can be done:-

(i) From the Menu bar choose INSERT\CHART

(ii) From the Tool bar choose the Chart Wizard icon

(1) First select the data (including Column & Row headings)

(2) next using (i) or (ii) above start the Chart Wizard

(3) this starts a four step process to create a chart and add it to your spreadsheet

NOTE - ALWAYS ADD CHARTS TO A NEW SHEET

(3.1) Choose a chart type. This is often the most difficult and you may need to experiment with a number of chart types to achieve something which shows your data to the best advantage. Use "Press and Hold to view Sample" to see your data using the selected chart type.

(3.2) Source Data - If you selected data before starting the Chart Wizard then this step is mainly a pass-through step. However choosing between Rows & Columns under Series in the Data Range panel may change the view of your data to better advantage.

(3.3) Chart Options -

Titles Always give the chart a title and label an axis which is not obvious.

Axes In most cases this needs no changes to defaults, however there may be a rare

advantage in removing label information.

Gridlines Add or remove Gridlines, however rarely requires changes to defaults.

Legend Show Legend or change its default location.

Data Labels Defaults fine. If changed ensure Axes details removed.

Data Table So far have never found a need to use this option.

(3.4) Chart Location - PICK "as object in another sheet".

(3.5) View the result. You may want to resize the chart or make other changes via the Menu bar CHART options.