Organising data in a spreadsheet

1
Organising data in a spreadsheet

• Module B2 Sessions 9 and 10

2
Contents

• Where we are
• Slides 1-18 Overview of Chapter 1 of the SSC
  Excel Guide (Practical 1).
• What is to come
• Slides 19-23 Preview of Chapter 2 of the SSC
  Excel Guide (Practical 1).
• Slides 24-33 Overview of what has been learnt
• Slides 34-36 Preview of Chapter 3 of the SSC
  Excel Guide (Practical 2).
• Slides 36-41 Overview of what has been learnt
• Slides 42-48 Introducing Practical 3
• Slide 49 Reviewing Practical 3

3
So far

• Steps once you have the data
• Session 1
• Tables and graphs in Excel
• Once the data are well organised
• These sessions
• The skills to organise the data
• So it is ready for analysis
• This often takes as much time as the analysis
  itself
• Excel has tools to help
• They are introduced here

4
What is assumed

• Excel guide Chapter 1.
• See the following slides.
• Check this is all familiar.
• The guide can be used as a reference.
• Review quickly if necessary.
• If you need a more thorough review this must be
  done in your own time.

5
The Windows desktop (1.2) Do you recognise
everything?
6
Mouse Operations (1.2)
7
Practicing using the mouse (1.2)
8
Windows Help (1.2)
Help window
Help command
9
A Typical Excel Window (1.3)
Menu bar
Toolbar
Column headings
Formula bar
Active cell
Row headings
Worksheet
Scroll bars
Sheets, making up a workbook
10
The Open Files Dialogue (1.4)
11
Open Workbook in Excel (1.4)
Worksheet navigation buttons
Other sheets in the workbook
Active sheet
12
Paste and paste Special

• For the next few slides get on your computers and
  follow along.
• Open the excel workbook SSC Introduction to
  handling data in Excel
• Use your Excel guide as a reference, reading the
  instructions carefully. Chapter One, Section 1.5

13
Popup menu for basic operations on active sheet
(1.5)
14
Selecting parts of a worksheet (1.5)
The cells that were dragged
The part where you used CTRL and then dragged
15
The Paste Special dialogue (1.5)
16
Linking information across worksheets (1.5)
This cell is linked and not just copied as the
number 23
17
Moving a range of cells (1.5)
Tooltip to show destination
Pointer is an arrow
Border of new destination
18
The Save As dialogue (1.5)
Folder in which the file will be saved
New name for the file
19
Handling Data

• In Excel

20
Chapter 2 Data Manipulation

• This chapter looks at the features of excel
  needed to organise data prior to analysis.
• Data sets are often large and messy, organising
  them can be a bigger job than analysing the data.
  
• Excel has a number of useful tools to make this
  job as quick and easy as possible.

21
Data Manipulation cont

• Good Practice
• You must always try to keep worksheets simple, to
  give them names and to clearly show what they
  contain.
• This will make it much easier when you need to
  find a particular data set or table of results.

22
What to expect

• In chapter 2 you will learn
• How to efficiently structure your data.
• How to create and protect master copies of you
  data.
• How to create duplicate working copies.
• How to sort your data for easier checking.
• How to select subsets (Filtering).
• How to name cell ranges.

23
Practical 1

• Now go and complete Chapter 2 of the Excel guide.
• Read each point and complete the exercise below
  when applicable.

24
How did it go?

• The next few slides are screen captures taken
  from the work you have just done.
• Go through them as a class making sure that you
  understood how to use each tool and its purpose.

25
Data from the CBS survey is in list form
(2.2)What are the features of a list?What are
the benefits of having your data in a list?
26
Master Sheets and Working Sheets(2.3-2.4)

• This is a crucial step in your organisation
  process and should always be the first thing that
  you do.
• Creating your Master Sheet keeps your data SAFE!
• Creating your Working Sheet gives you FREEDOM to
  make mistakes!

27
Sort dialogue Data sorted on household
size(2.5)
28
The filtered data (2.6)
29
Applying a custom filter Options in custom
filter dialogue box (2.6)
30
Adding a comment to a cell (2.7)
31
Using the name of an array (2.7)
32
Creating names from a selected array (2.7)
33
Features, queries and concerns

• What was new, that you liked?
• What was difficult?
• What do you need to practice more?

34
Chapter 3Performing Claculations

• This chapter outlines how Excel can be used to do
  simple and complex calculations with small and
  large set of data, it has 2 parts
• How to use excels formula bar to perform simple
  arithmetic calculations (3.2).
• How to use excel as a column calculator (3.3-3.5).

35
What to expect

• In Chapter 3 you will learn
• How to do basic calculations in the formula bar.
• How to use excel to do arithmetics and
  comparisons using cell addresses.
• How to use excel to do arithmetics and
  comparisons using column names.
• How to use excels inbuilt functions, these can
  save you time and safeguard against human error.

36
Practical 2

• Now go and complete Chapter 3 of the Excel guide.
• Read each point and complete the exercises below
  when applicable.

37
How did it go?

• The next few slides are screen captures taken
  from the work you have just done.
• Go through them as a class making sure that you
  understood how to use each tool and its purpose.

38
Entering a logical calculation (3.2)
39
Using columns for calculations (3.4)
40
Functions Dialogue Box (3.5)
Keep exploring the program. There are a multitude
of functions available. If you do not know they
exist you will be stuck do them by hand! Its
worth a few minutes.
41
Features, queries and concerns

• What was new, that you liked?
• What was difficult?
• What do you need to practice more?

42
Applying the new tools

• The new tools in Excel
• Enable data to be organised ready for the
  analysis
• Organising the data is a key step
• And time must be allowed for this step
• Once the data are organised
• The ideas in Sessions 4 and 5
• Show how they can be analysed to produce tables
• And Sessions 6 and 7
• Showed how to produce graphs

43
Case study hourly sunshine data

• The data are needed for the solar cooking project
• Introduced in Module B1
• We need to know
• The proportion of days with sufficient morning
  sun
• And afternoon sun
• For cooking lunch and dinner
• And for pasteurising water
• The hourly data were supplied
• In Excel
• By the Botswana Met Service
• And have to be organised for the analysis

44
Hourly data as supplied 1978 - 1997
Top of data file Almost a list
Data in 1/10 of an hour
But each year is separate. The year is not
included in the list And there are gaps between
each year
45
Stages in the organisation process

• Copy to a new sheet called data
• As supplied
• But in a list
• With another column giving the year number
• Make a third sheet called working
• Data in fractions of an hour
• Then totalled to give morning sun
• And afternoon sun
• To be ready for the analysis
• This is what you repeat
• In practical 3

46
The data as supplied but in a list
With a year column added
47
The data ready for analysis
The key variable for the analysis Sunshine hours
from 9am to 1pm
If gt3hrs then OK for cooking. gt 1hr
for pasteurising water
48
Practical 3

• Now that you have these tools at you disposal try
  them on these data.
• Keep your guide for reference.
• Complete Practical 3.
• Note the new problems that always occur
• with real data
• but not in textbook datasets!

49
Features, queries and concerns

• What was new that you liked?
• What concerns did you have?
• Did you resolve the challenge
• An IF statement is
• IF(B3gt1,O2,)

50
Can you now

• Explain the importance of a list
• for statistical uses of Excel.
• Use special features,
• like sorting and filtering.
• Understand how names are used in Excel.
• Perform simple arithmetic calculations
• using Excels formula bar.
• Use Excel as a column calculator
• Explain the data organisation stage
• that precedes an analysis
• Use the Excel guide
• with the demonstrations - for self study

51
Learning Software

• It is hard to learn software through lectures as
  practice is the most effective way of developing
  the necessary skills.
• Most software will come with a tutorial guide
  and (possibly) some demonstrations as you have
  used here.
• Use the skills you learnt today to work
  independently with these type of resources (i.e.
  the guide and demonstrations).

52
The next session looks at even more resources
with Excel And now the sunshine data are
organised. They are analysed in a later session