The Fill Command



Spreadsheet Manual – Excel 2007

In Alphabetical Order

Centre Worksheet on Page

Click Page Layout + Margins + Custom Margins

Click on [pic] and [pic] + OK

Delete a Row or Column

Sequence: Highlight the row/column you want to delete

Click Home + Delete Cells or Right Click + Delete

Fill Command

Purpose: Copy contents from one cell to a number of cells

Note: (Text, numbers, formulas & functions can all be copied)

Sequence: Highlight the cell you want copied

Grab the bottom right hand corner of cel

Drag its contents to the other cells

(as well as Right, you can also use Up, Down, Left)

Highlight Cells Click and drag mouse over cells to be highlighted

(Adjacent) Note: (the first cell is only outlined and not coloured like the other cells because the contents of the first cell is what is being copied/filled into the remaining cells)

(Non Adjacent) Click and drag over the first cell or range of cells, then and drag over other cells required.

(Highlight Rows & Columns)

Row Click on row number/s eg: [pic]

Column Click on column letter/s eg: [pic]

(Delete Contents of Cell/s)

Highlight cells

Right click + Clear Contents (will also delete any formatting)

Note: ( Entering new data into active cell will overwrite original data)

Note: (Click on [pic] or Ctrl + Z to undo the last actions/s)

(Press Ctrl + Y to redo the last action/s)

Insert a Row or Column

Sequence: Highlight the row/column you want to insert before

Click Home + Insert or Right click + Insert

Merge Cells

Purpose: To cancel the borders in a row/column so headings can be centred in the middle of text

Sequence: Highlight Cells to be merged

Click Home + Format + Format Cells

Click on Alignment then place a tick in Merge Cells

(cancel merged cells)

Highlight the cell to be split

Click Home + Format Cells + Alignment

Click on Merge cells (so the tick disappears) + OK

Worksheets

[pic]

Insert Right click on worksheet

Click Insert + OK

Delete Right Click on worksheet

Click Delete (twice)

Rename Right Click the Sheet then Left click Rename

Type new name for Sheet

Wrap Text in Cell [pic]

Purpose: To keep specified text in one cell by wrapping its contents to the next line

Sequence: Highlight Cell/s

Click Home + Format + Format Cells

Click on Alignment

Tick [pic]

Click OK

Sort Data

Purpose: To sort columns of data into order.

Data can be sorted into:

ascending order A – Z 0 – 9 [pic]

descending order Z – A 9 – 0 [pic]

AND can have up to 3 stages of sorting as shown below:

[pic]

Sequence: Highlight all text to be sorted

Click on Data + Sort

Change 1st order setting as desired

Click Add Level if you require a 2nd/3rd sort order

Change 2nd/3rd order settings as desired

Click OK

Exercise: PTO

Exercise:

1. Input the following table

2. Sort the table by:

➢ Team then by

➢ Last Name then by

➢ First Name

|Wyong Bike Teams |

|Last Name |First Name |Team |

|King |Aron |Devils |

|Harris |Daniel |Raiders |

|Roberts |Jacob |Devils |

|Stackman |Sarah |Angels |

|Baird |Joel |Raiders |

|Campbell |Jonathan |Broncos |

|Burgess |Katerina |Angels |

|Gore |Luke |Devils |

|Bamback |Mathew |Devils |

|Jenkins |Mathew |Raiders |

|Huckstadt |Miranda |Angels |

|Bullen |Sarah |Angels |

|Landers |Christopher |Raiders |

|Brock |Zena |Angels |

Show your teacher when complete.

FORMULAS

Purpose: To calculate simple maths using:

|+ |- |/ |* |% |

|plus |minus |divide |multiply |percentage |

eg: Addition =C12+C13+C14 Add cell C12 through to C14

Subtraction =B9-B5 Subtracts cell B9 from cell B5

Multiplication =A8*C13 Multiplies cell A8 with cell C13

Division =B6/B14 Divides cell B6 by B14

Percentage =A4*15% Finds 15% of value in A4

=A4+A4*15% Finds 15% markup

=A4-A4*15% Finds 15% markdown

ORDER OF OPERATION

It is possible to get two different results for a simple calculation like 3 + 2 * 4 depending on the order in which the operations are carried out. If the operations are done in strictly left to right order the result is 20. If a priority is assigned to multiplication over addition then the answer is 11. to maintain consistent results, the operations are done according to a set of priority rules as outlined in the following table:

|Mathematical Operations – Priority Rules |

|( ) |(7+8+9)/3 = 8 |Operations enclosed in parentheses are always done first |

|* and / |3+4*2 = 11 |Multiplication and division are done next from left to right |

|+ and - |10-4/2 = 8 |Additions and subtractions are done next (last) from left to right |

Exercise 1 - Entering a Formula

To enter a formula in an active cell, first press = (equal sign) to signify that what follows is a formula. Try the following:

| |A |What is your Result |

|1 |=4+2*5 | |

|2 |=(4+2)*5 | |

|3 |=2*4+12/4-2 | |

|4 |=2*(4+12)/4-2 | |

|5 |=2*4+12/(4-2) | |

|6 |=2*(4+12)/(4-2) | |

|7 |=(2*4+12)/4-3 | |

|8 |=(2*4+12)/(4-2) | |

Calculate the above table manually, then check your results using the Spreadsheet.

Exercise 2 – Entering Formulas

Type in the following 4 numbers in their appropriate columns

[pic]

With your teacher, type in the following functions and note the results you get.

Instead of typing A1 or C1, point to the cell & click – this will add the co-ordinates quickly

[pic]

FUNCTIONS

Purpose: More advanced calculations that have been created by Spreadsheets when the use of 2 or more calculations at one time are required.

eg: To work out an average score of:

|Student |Maths |English |

|  |out of 10 |out of 10 |

|Joel |8 |6 |

|Luke |9 |6 |

|Miranda |7 |9 |

|Class average |=(B10+B11+B12)/3 |=AVERAGE(C10:C12) |

| |[formula] |[function] |

Both formula and function works.

Other functions: There are over 200 functions that are used by accountants, teachers, architects, engineers, phychologists and many other professionals, but to understand these functions, you would need to study that particular profession/type of business. These are a few below that we will use.

Addition =SUM(B6:B14) Adds ALL cells from B6 to B14

[pic] Autosum – quick way to use =SUM

It adds cells to left or above. Click twice on

the Autosum icon [pic] on Toolbar

Average =AVERAGE(B6:B14) Finds average of cells B6 to B4

Maximum =MAX(B3:B20) Finds highest value in cells B3 to B20

Minimum =MIN(B3:B20) Finds lowest value in cells B3 to B20

Count =COUNT(A1:A14) Counts cells A1 to A14 that are not empty

Today’s date =TODAY() Shows system date

Exercise: Type in the following Functions and view the results

[pic]

................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download