Mr Martin Lesson 1 overview of spreadsheets amp the key terms Mr Martin Open Microsoft excel Turn your chair around and look at the board Mr Martin 2013 Mr Martin 2013 httpswwwyoutubecomwatchv05xfGNLA5W0 ID: 686033
Download Presentation The PPT/PDF document "The wonderful world of Spreadsheets" is the property of its rightful owner. Permission is granted to download and print the materials on this web site for personal, non-commercial use only, and to display it on your personal computer provided you do not modify the materials and that you retain all copyright notices contained in the materials. By downloading content from our website, you accept the terms of this agreement.
Slide1
The wonderful world of Spreadsheets
Mr MartinSlide2
Lesson 1 – overview of spreadsheets & the key terms
Mr MartinSlide3
Open Microsoft excel
Turn your chair around and look at the board
Mr Martin, 2013Slide4
Mr Martin, 2013
https://www.youtube.com/watch?v=05xfGNLA5W0Slide5
Spreadsheets are powerful
________
that are able to process numbers and
____.
They are used extensively in statistics, accounting and sales professions.
Spreadsheets can be used for
______
keeping, predictions and
_______.
Spreadsheets use a collection of rows and _______, which look like a grid or extensive table. The rows are the _______ elements and the columns are the ________ elements. The intersection of a row and a column is known as a _______.
What are spreadsheets?
Vertical, Columns, Cell, Programs, Record, Data, Charting, Horizontal
Vertical
Cell
Record
Programs
Data
Columns
Horizontal
Charting
Mr Martin, 2013Slide6
What could we use spreadsheets for?
Class Brainstorm:
Lists (shopping lists, contact lists)
Accounting (budgets)
Time Sheets
Databases
Chart creation (GANTT)
Mr Martin, 2013Slide7
What do they look like?
Mr Martin, 2013Slide8
Key Terms
Row
Column
Cell
Cell Address
Mr Martin, 2013Slide9
Key Terms
Row – Horizontal (sideways)
Column – Vertical (up and down)
Cell – Where a row and column intersect
Cell Address – Coordinates of the Cell (Column Letter + Row Number)
Mr Martin, 2013Slide10
Row
Column
Cell
Cell Address
Mr Martin, 2013Slide11
Row
Column
Cell
Cell Address
Mr Martin, 2013Slide12
Mr Martin, 2013Slide13
X
Question 1 - What is the Cell Address of the selected square?
Mr Martin, 2013Slide14
X
Active Cell
Data Entry
Question 2 - What is the Cell Address of the selected square?
Mr Martin, 2013Slide15
Spreadsheet Battleships
Mr Martin, 2013Slide16
Skills in playing spreadsheet battleships
Changing column width
Changing row
height
Adding a border
Merging cells
Colouring
cells
Entering data
Familiarising yourself with excelMr Martin, 2013Slide17
Select all
Step 1
Mr Martin, 2013Slide18
Hold the line and drag
Step 2
Mr Martin, 2013Slide19
Hold the line and drag
Step 3
Mr Martin, 2013Slide20
Select the cells from B3:M14
Step 4
Mr Martin, 2013Slide21
Add a boarder
(all borders)
Step 5
Mr Martin, 2013Slide22
Add letter coordinates
(row)
Add number coordinates
(column)
Step 6
Mr Martin, 2013Slide23
Create your ships below the grid
Mr Martin, 2013Slide24
Make your ships
Step 7
Mr Martin, 2013Slide25
Merge the cells for your ships
Step 8
Mr Martin, 2013Slide26
Colour
your ships
Step 9
Mr Martin, 2013Slide27
Copy & Paste
Step 10
Mr Martin, 2013
Select everything you have done by clicking from the top left hand corner and dragging until the bottom right hand corner
Click the cell where you would like paste your battleship gridSlide28
Test out your spreadsheet battleships
Mr Martin, 2013Slide29
Print out your sheet (landscape) for your folio
Mr Martin, 2013Slide30
Lesson 2of the wonderful world of spreadsheets
Mr MartinSlide31
Step 1. Log on to your computer then turn and face meSlide32
Changing column width -
Changing
row height
–
Adding a border –
Merging cells -
Colouring cells -
Entering data –
Mr Martin, 2013
Skills we learnt last lessonSlide33
This week: calculations
What might we want to calculate with a spreadsheet?
What are our basic calculations? (think of a calculator)
Plus
Minus
Multiplication
Division Slide34
What might we like to do with some data?
For example.
If we rent 5 videos and they cost $5 each
What if we had store credit of $30Slide35
Mr Martin, 2013
What we will be doing this lessonSlide36
Mr Martin, 2013Slide37
The importance of the Cell address
Think back to our spreadsheet battleships
If you put in the wrong coordinates you miss
Same with formulas - if you put in the wrong coordinates for your formula it will be wrongSlide38
Activity 1 – 7 times tables
Open Microsoft Excel
Type the numbers 1-12 down
column A
Type the number 7 down
column
B
Click on cell
C1
and type the followingRepeat until the whole column is completeMr Martin, 2013Slide39
Mr Martin, 2013
Mr Martin, 2013Slide40
Formulas in excel
* is the multiply symbol EG.
D5*D6
/ is the divide by symbol EG. =A3/A4
+ is the plus symbol EG. =B4+C4+D4+E4
- is the subtract symbol EG. =F10-F9
All
formulas need to start with
==Mr Martin, 2013Slide41
Formula format
=A1+A2+A3+A4+A5+A6+A7
or
=sum(A1+A2+A3+A4+A5+A6+A7)
or
=sum(A1:A7)
Mr Martin, 2013Slide42
Mr Martin, 2013
Activity 2 – Formatting the dataSlide43
Activity 2 – Formatting the data
Insert a cell left to column B and type the symbol X
Insert a cell left to column
D
and type the symbol
=
Insert some borders around your data
Shade the rows an alternate
colour
Copy table into a Microsoft Word documentMr Martin, 2013Slide44
How to take from excel and insert into word
Mr Martin, 2013
Select everything you have done by clicking from the top left hand corner and dragging until the bottom right hand corner
Click the Microsoft word document you would like to paste and right clickSlide45
SAVE
Create a folder in your students drive named ‘spreadsheets’
This is where you will be saving all your work with spreadsheets
Save your excel document –
seventimestable
Save your word document –
seventimestable
8177>student home>Slide46
Lesson 3
Mr MartinSlide47
Saving
You need to follow this path to save you work
8177>student
home>Slide48
Last lesson:
We created a times table document using a simple formula
Rules for formulas?
Mr Martin, 2013Slide49
Formulas in excel
* is the multiply symbol EG.
D5*D6
/ is the divide by symbol EG. =A3/A4
+ is the plus symbol EG. =B4+C4+D4+E4
- is the subtract symbol EG. =F10-F9
(All
formulas need to start with
=)=Mr Martin, 2013Slide50
Taking the average
What is the average?
How do we get an average?
Mr Martin, 2013Slide51
Table displayed on board and entering agesSlide52
Activity 3 – Data entry
Open a new Microsoft Excel
Copy down off the board the
ages of everybody in your class
Type first name down column A
Type their age down column B
Mr Martin, 2013
Name
age
Bob34Etc.
Etc.Slide53
Data
What can we do with this data?
Note – data that is in merged cells cant be changed into tables on macs
Mr Martin, 2013Slide54
Mr Martin, 2013Slide55
Autosum functions
Sum –
Average –
Count numbers –
Max –
Min -Slide56
Autosum functions
Sum – grabs the nearest range
ie
. A1:A5 and adds together
Average -
grabs the nearest range
ie
. A1:A5 and adds
together and divides by the number of cells
Count numbers – Tells you how many cells have values (data entered)Max – Maximum number in the rangeMin - Minimum number in the rangeSlide57
Activity 4 – Fill in this table
Note – you will need to create this table first
Mr Martin, 2013Slide58
Activity 5 – Fill in this table
Note – you will need to create this table first
Mr Martin, 2013Slide59
Percentages
A/B*100=
=B2/B3*100Slide60
Activity 6 – Convert the Data to a table
Insert > Charts
Mr Martin, 2013Slide61
Mr Martin, 2013Slide62
Why do we use graphs and tables?
Reasons why?
Entering the correct formulas with cell addresses so that if you change the data the percentages and tables/graphs change with it
Mr Martin, 2013Slide63
Types of Graphs
Mr Martin, 2013Slide64
Column Graphs
Mr Martin, 2013Slide65
Line graph
Mr Martin, 2013Slide66
Pie graph or doughnut chart
Mr Martin, 2013Slide67
Scatter graph or bubble chart
Mr Martin, 2013Slide68
Bar chart
Mr Martin, 2013Slide69
Reading/interpreting graphs
Mr Martin, 2013Slide70
Reading/interpreting graphs
Mr Martin, 2013Slide71
If you are having trouble with the format of your data you may need to change the data type
FOR EXAMPLE - A mobile phone number entered into excel will not show the first 0
0411949333 will display as 411949333 unless changed to TEXT format
Mr Martin, 2013Slide72
Demonstration on converting data to tables
Mr MartinSlide73
Tur
n around and watch
DemonstrationSlide74
Important things to remember
Your version of Excel wont convert data that is in a merged cell into a table
You will need to format the cells to tell it what is in there. What type of DATA it is
eg
. Percentage, text, numberSlide75
IMPORTANT: When dealing with different data types
eg
. Percentages you need to format cellsSlide76
Convert this data into a table
Activity 1)
Create a pie/donut graph which represents
68% YES
&
32% NO
Activity 2)
Create a pie/donut graph which represents
40% YES
, 30% NO & 10% MAYBEActivity 3) Create a column graph representing bowling game averages with Barry on 10, William on 25, Mark on 50 & Johnny on 125Slide77
Activity 1Slide78
Activity 2Slide79
Activity 3Slide80
Activity 4
Create a graph using the data we collected on everybody’s favourite colour (on whiteboard)Slide81
Design Project
Mr MartinSlide82
Design project – Holiday budget
You are to design a spreadsheet that acts as a budget for a family holiday for 4 people (2 adults, 2 children)
The total budget for the holiday is $5,000 and you must stay 5 nights
You must include;
Transport,
Accommodation,
Food,
Activities, Entertainment & extra spending
You will need to present your holiday budget as a report in a word document.
In your report you must include the tables and graphs from your excel document AND screenshots from the internet of the places you are staying, eating, being entertained.The following graphs must be generated from your information – Pie graph showing where your expenses are going to (Transport, Accommodation, Food, Activities, Entertainment, Shopping)Column graph showing how much your spending each day
You must state which day you spend the most money and how much you spent and which day you spend the least money and how much you spent. You must also state your average spend per day.
Mr Martin, 2013Slide83
Marking Criteria
Basic
(
0-3)
Satisfactory
(4-6)
Excellent
(7-10)Researches using the internet and takes screen shots of website infoFulfill design brief ($5000, 4 people, 5 nights)
Uses formulas in excel document accurately to calculate expensesCreates tables and graphs
in Microsoft excel and formats them correctly
Exports tables and graphs into Microsoft Word
Creates the design briefs specified graphs (pie, column)
Total
60
Mr Martin, 2013Slide84
Monday & Tuesday
Finish Design brief
Create your tables (based on the example I handed out)
Fill in your findings from the internet
Insert formulas to create an accurate budget
Create chartsSlide85
To DO..
Make sure you have a Microsoft Excel document named ‘(
yourname
)
holidayplanner
’
Finish Design Brief (most of us had a finished design brief last lesson)
Enter Requirements (number of people, specifics, nights accommodations, total budget)
Start a table with estimated cost & estimated money remaining
Start your accommodation table with the same headings as your example sheet(Place of accommodation, cost per night, length of stay, total, total accommodation budget)While on the internet researching you must take a screen grab of the internet site and insert it into your holidayplanner spreadsheetSlide86
To DO..
Make sure you have a Microsoft Excel document named ‘(
yourname
)
holidayplanner
’
Finish Design Brief (most of us had a finished design brief last lesson)
Enter Requirements (number of people, specifics, nights accommodations, total budget)
Start a table with estimated cost & estimated money remaining
Start your accommodation table with the same headings as your example sheet(Place of accommodation, cost per night, length of stay, total, total accommodation budget)While on the internet researching you must take a screen grab of the internet site and insert it into your holidayplanner spreadsheetSlide87
Today..
Accommodation
Travel
Food
Activities & Entertainment
Shopping Money
Estimated totals
Graphs
.
.
.
.
.
.
.Slide88
Internet Research
By the end of this lesson you need to have researched:
(MUST INCLUDE SCREEN SHOTS OF EACH & URL –
DEMONSTRATE
)
W
here you are staying
How you are going to get there (airplane, bus, train, hire car)
What you are eating (breakfast, lunch, dinner)
What activities you are doing (theme parks, bowling, movies, etc.)Slide89
Lessons until project is due
1
2
3
4
5
6
Mon
Tues
Wed
Mon
Mon
Wed
Accommodation
Lesson 1
Internet research for accommodation
Calculations for the total 5 nights accommodation
Take a screen shot for your report
Inserting a screen shot into your report
Travel
Lesson 2
Internet research for travel
Calculations for the total travel
Take a screen shot for your report
Inserting a screen shot into your report
Food
Lesson 3
Internet research for food
Calculations for the total 5 days/nights food
Take a screen shot for your report
Inserting a screen shot into your report
Activities & entertainment
Lesson 4
Internet research for activities & entertainment
Calculations for all activities
Take a screen shot for your report
Inserting a screen shot into your report
Shopping money
Lesson 5
Consist of left over money
Calculations as to who gets what
Formatting the report/testing
Lesson 6
Make it look presentable
Make sure pictures are correct
Make sure all formulas work
Slide90
Lessons until project is due
1
2
3
4
5
6
Mon
Tues
Wed
Mon
Mon
Wed
Accommodation
Lesson 1
Internet research for accommodation
Calculations for the total 5 nights accommodation
Take a screen shot for your report
Inserting a screen shot into your report
Travel
Lesson 2
Internet research for travel
Calculations for the total travel
Take a screen shot for your report
Inserting a screen shot into your report
Food
Lesson 3
Internet research for food
Calculations for the total 5 days/nights food
Take a screen shot for your report
Inserting a screen shot into your report
Activities & entertainment
Lesson 4
Internet research for activities & entertainment
Calculations for all activities
Take a screen shot for your report
Inserting a screen shot into your report
Shopping money
Lesson 5
Consist of left over money
Calculations as to who gets what
Formatting the report/testing
Lesson 6
Make it look presentable
Make sure pictures are correct
Make sure all formulas work
Slide91
Lessons until project is due
1
2
3
4
5
6
Mon
Tues
Wed
Mon
Mon
Wed
Accommodation
Lesson 1
Internet research for accommodation
Calculations for the total 5 nights accommodation
Take a screen shot for your report
Inserting a screen shot into your report
Travel
Lesson 2
Internet research for travel
Calculations for the total travel
Take a screen shot for your report
Inserting a screen shot into your report
Food
Lesson 3
Internet research for food
Calculations for the total 5 days/nights food
Take a screen shot for your report
Inserting a screen shot into your report
Activities & entertainment
Lesson 4
Internet research for activities & entertainment
Calculations for all activities
Take a screen shot for your report
Inserting a screen shot into your report
Shopping money
Lesson 5
Consist of left over money
Calculations as to who gets what
Formatting the report/testing
Lesson 6
Make it look presentable
Make sure pictures are correct
Make sure all formulas work
Slide92
Lessons until project is due
1
2
3
4
5
6
Mon
Tues
Wed
Mon
Mon
Wed
Accommodation
Lesson 1
Internet research for accommodation
Calculations for the total 5 nights accommodation
Take a screen shot for your report
Inserting a screen shot into your report
Travel
Lesson 2
Internet research for travel
Calculations for the total travel
Take a screen shot for your report
Inserting a screen shot into your report
Food
Lesson 3
Internet research for food
Calculations for the total 5 days/nights food
Take a screen shot for your report
Inserting a screen shot into your report
Activities & entertainment
Lesson 4
Internet research for activities & entertainment
Calculations for all activities
Take a screen shot for your report
Inserting a screen shot into your report
Shopping money
Lesson 5
Consist of left over money
Calculations as to who gets what
Formatting the report/testing
Lesson 6
Make it look presentable
Make sure pictures are correct
Make sure all formulas work
Slide93
Lessons until project is due
1
2
3
4
5
6
Mon
Tues
Wed
Mon
Mon
Wed
Accommodation
Lesson 1
Internet research for accommodation
Calculations for the total 5 nights accommodation
Take a screen shot for your report
Inserting a screen shot into your report
Travel
Lesson 2
Internet research for travel
Calculations for the total travel
Take a screen shot for your report
Inserting a screen shot into your report
Food
Lesson 3
Internet research for food
Calculations for the total 5 days/nights food
Take a screen shot for your report
Inserting a screen shot into your report
Activities & entertainment
Lesson 4
Internet research for activities & entertainment
Calculations for all activities
Take a screen shot for your report
Inserting a screen shot into your report
Shopping money
Lesson 5
Consist of left over money
Calculations as to who gets what
Formatting the report/testing
Lesson 6
Make it look presentable
Make sure pictures are correct
Make sure all formulas work
Slide94
Lessons until project is due
1
2
3
4
5
6
Mon
Tues
Wed
Mon
Mon
Wed
Accommodation
Lesson 1
Internet research for accommodation
Calculations for the total 5 nights accommodation
Take a screen shot for your report
Inserting a screen shot into your report
Travel
Lesson 2
Internet research for travel
Calculations for the total travel
Take a screen shot for your report
Inserting a screen shot into your report
Food
Lesson 3
Internet research for food
Calculations for the total 5 days/nights food
Take a screen shot for your report
Inserting a screen shot into your report
Activities & entertainment
Lesson 4
Internet research for activities & entertainment
Calculations for all activities
Take a screen shot for your report
Inserting a screen shot into your report
Shopping money
Lesson 5
Consist of left over money
Calculations as to who gets what
Formatting the report/testing
Lesson 6
Make it look presentable
Make sure pictures are correct
Make sure all formulas work
DUE DATE: Wednesday 30
th
OCT