Introduction to BASIC SPREADSHEETS

1
Introduction to BASIC SPREADSHEETS

• Tutor - Geoff Cann

Fashion Logistics
2
Logging on to the network

• CtrlAltDel
• Click in Username field and key in your username
• Click in Password field and key in default
  password
• You will be asked to change your password using a
  combination of 3 of the following uppercase,
  lowercase, numbers and symbols e.g. Peckham123 or
  1Moretime
• Make sure your new password is one you will
  remember, key in once to change and again to
  confirm
• You will now be logged onto the network and the
  desktop will appear in about 1 minute.

3
Creating a folder for your work

• To ensure good file management
• Open your My Documents folder from the desktop
• Select FilegtNewgtFolder and name the folder Excel
  Training

4
Copying files
5
This programme will include

• Getting started with Microsoft Excel
• Understanding cell addresses
• Entering text and numeric data
• Adjusting column width and row height
• Inserting and deleting columns and rows
• Using comments to explain cell contents
• Printing Spreadsheets

6
Cont

• Basic functions and formula
• Understanding basic formula concepts
• Using AutoFill
• Using absolute formula
• Formatting worksheets
• Centre and merge text
• Wrap cell contents
• Format numbers
• Borders and shading

7
What is a Spreadsheet?

• A Spreadsheet is a grid of rows and columns.
• It is used for entering text and numbers and for
  carrying out calculations.
• The original spreadsheets where on paper.
• Computerised spreadsheets have a great advantage
  because you can change the numbers and the
  computer will calculate the new results.
• Business people use spreadsheets for recording
  and calculating sales figures, expenses and
  wages, for making invoices, budgets, business
  plans and much more.
• Scientists and engineers use spreadsheets for
  calculations and analysis.
• People at home use spreadsheets for working out
  personal finances and taxes.

8
The Excel Window
Task Pane
Spreadsheet
Scrollbars
9
Data Types

• There are 4 main types of data that you can enter
  into a spreadsheet
• Text or Labels
• Values
• Dates
• Formulae

10
Spreadsheets (Microsoft Excel)

• A formula is used on a spreadsheet to perform a
  calculation using numbers in other cells.
• The results of the calculation is displayed in
  the cell where the formula has been entered.
• A simple formula can be used to add, subtract,
  multiply or divide numbers.
• To carry out these sorts of calculation the
  symbols below are used in a formula
• to add
• - to subtract
• to multiply
• / to divide.
• All formula begin with the sign
• To add numbers in cells A1 and A2 you would use
  the following formula A1A2.
• If you are going to add together more than two
  numbers you would use the SUM function, e.g.
  A1A2A3A4A5 would be SUM(A1A5)

65536 Cells Down256 Cells Across (IV)
11
Simple Formulae
Open Microsoft Excel and key in the following
data
In cell A1 type in 50, in cell B1 type in 10 etc.
To show how the mathematical formulae work, enter
the following examples shown below. Remember to
use the sign before any formulae!
Task
12
Simple Formulae
Check your results against these shown below
You can see the formulae used for each cell by
looking in the formula bar
or by selecting ToolsgtOptionsgtView Formulas or by
pressing Ctrl
13
Using the Undo Command

• Open a new workbook by clicking the New workbook
  button
• Key in the data shown here
• Practice undoing and redoing one action at a
  time
• Practice undoing and redoing multiple
  actions
• Close the workbook without saving

A B C
1 10 5 A1B1
2 20 8 A2-B2
3 30 7 A3/B3
4 40 6 A4B4
Task
14
Entering Data and Formula

• Open a new Excel sheet
• Save As Soap Survey in your Spreadsheets folder
• Enter the information shown on the right.
• Add a formula in cell B8 to total the number of
  viewers from B2 to B7.
• Add the formula AVERAGE(B2B7) in cell B9.

Task
15
Using Page setup

• Page Setup is used to prepare your spreadsheet
  document for printing. Settings can be applied
  using this option to determine how the
  spreadsheet will look on paper
• Click FilegtPage Setup
• Page Tab
• Orientation
• Scaling
• Paper Size
• Print Quality
• First Page Number

16
Using Page setup

• Page Setup is used to prepare your spreadsheet
  document for printing. Settings can be applied
  using this option to determine how the
  spreadsheet will look on paper
• Margins Tab
• Top, Bottom, Left Right
• Header Footer
• Centre on Page

17
Using Page setup

• Page Setup is used to prepare your spreadsheet
  document for printing. Settings can be applied
  using this option to determine how the
  spreadsheet will look on paper
• Header/Footer Tab
• Custom Header
• Custom Footer

18
Using Page setup

• Page Setup is used to prepare your spreadsheet
  document for printing. Settings can be applied
  using this option to determine how the
  spreadsheet will look on paper
• Sheet Tab
• Print Area
• Print Titles
• Print
• Page Order

19
Using Page setup

• Open the spreadsheet workbook Staff Rota (copied
  earlier into youre My Documents/Excel folder)
• Access Page Setup and set the following options
• Change the orientation to landscape
• Select the option to fit the spreadsheet to 1
  page wide by 1 page tall
• Centre on page horizontally and vertically
• Apply a header by clicking on Custom Header and
  keying in your name in the left box
• Apply a footer by clicking on the black drop down
  arrow and selecting the filename
• Select the option to Print the Gridlines

Task
20
Previewing a Spreadsheet

• Before printing a spreadsheet, it is advisable to
  check the setup of the page and then preview it
• Print Preview lets you check what your
  spreadsheet will look like when it is printed
• Click FilegtPrint Preview or
• Click the Print Preview button on the Toolbar
• The workbook is displayed exactly as it will be
  printed on paper
• To save the changes or to return to normal view
  close the print preview by selecting the Close
  button on the toolbar

Task
21
Selecting Cells and Ranges

• Single Cell
• Range of Cells
• Rows and Columns
• Sequential Rows or Columns
• Non-sequential Rows and Columns

22
Inserting Rows or Columns

• Additional rows and columns can be added by using
  the following method
• To insert additional columns, click the column
  where the new column is to be inserted. Select
  Insert from the menu bar. Click Columns
• The whole worksheet is adjusted to accommodate
  the new column being inserted
• The same principle is applied to inserting
  additional rows, i.e. click to select the row
  where it is to be inserted. Select Insert, Rows
  from the menu bar
• The new row will appear above the rows that were
  selected, with all the other rows having then
  been adjusted

23
Inserting Rows or Columns

1. Amend the Staff Rota workbook, insert columns and
   rows in the following positions
2. Insert 1 row between J.Rhodes and C.Allen
3. Insert 1 row between D.Jones and E.Hill
4. Insert 1 column between the Staff Names and Shift
   columns
5. Add the following data into the spreadsheet.
   Ensure the correct data is inserted into the
   correct cell reference

Task
24
Using the Sum Function
Task
In cell H5 key in the formula Sum(C5G5) In cell
H6 key in the formula Sum(D5G5) Etc
Save and Close the Staff Rotas Spreadsheet
25
Formatting Cells

• Numbers
• Text
• Currency
• Date
• Percentage
• Text Orientation
• Alignment
• Adding Borders to Cells

26
Formatting Cells
27
Assessment Task

1. Delete the Row for staff member Hill
2. Insert a column between Shift and Monday
   headed Zones
3. Change column headed Total to Total Hours
4. Add 2 new columns to the right of Total Hours
   headed Rate per Hour and Total Salary
5. Key in the Zones and Rates data
6. Format Rates and Total Salary to currency
7. Add a formula to calculate the Total Salary
   and replicate for all staff(Total Hours Rates
   per Hour)
8. Format the Weekdays to display at a 45 degree
   angle
9. Centre and Merge Staff Rotas and Hours
10. Rename Worksheet Sheet1 to Sept 04

28
(No Transcript)
29
Charts Graphs
1. Highlight the cells A1 to B7 2. Click on the
Chart Wizard button on the toolbar

• Excel includes facilities for representing
  information in the form of a graph or a chart.
• The more common types of charts and graphs that
  are used are bar charts, pie charts and line
  graphs.
• A Chart Wizard gives step-by-step help when
  drawing a chart or graph.

3. Choose the type of graph or chart you want to
use 4. Click next until the chart is produced 5.
Copy paste the chart into a Word Document
30
Inserting Rows or Columns