Developing e-learning content



© Copyright, 2013 by TAFE NSW - North Coast Institute

|Date last saved: 2 July 2013 by Amanda Walker |Version: 1.2 |# of Pages = 35 |

Copyright of this material is reserved to the Crown in the right of the State of New South Wales. Reproduction or transmittal in whole, or in part, other than in accordance with the provisions of the Copyright Act, is prohibited without written authority of TAFE NSW - North Coast Institute.

Disclaimer: In compiling the information contained within, and accessed through, this document ("Information") DET has used its best endeavours to ensure that the Information is correct and current at the time of publication but takes no responsibility for any error, omission or defect therein. To the extent permitted by law, DET and its employees, agents and consultants exclude all liability for any loss or damage (including indirect, special or consequential loss or damage) arising from the use of, or reliance on, the Information whether or not caused by any negligent act or omission. If any law prohibits the exclusion of such liability, DET limits its liability to the extent permitted by law, to the re-supply of the Information.

Third party sites/links disclaimer: This document may contain website contains links to third party sites. DET is not responsible for the condition or the content of those sites as they are not under DET's control. The link(s) are provided solely for your convenience and do not indicate, expressly or impliedly, any endorsement of the site(s) or the products or services provided there. You access those sites and use their products and services solely at your own risk.

Acknowledgements:

Cover page images

|Image |Attribution |

|[pic] |Image: 'Dunedin ICT Internship Speed Dating 2010' by Samuel Mann |

| | |

| |Licence: Under a Creative Commons Attribution 2.0 |

|[pic] |Image: 'What keeps us connected...' by cell105 |

| | |

| |Licence: Under a Creative Commons Attribution 2.0 |

|[pic] |Image: 'Servers' by JohnSeb |

| | |

| |Licence: ShareAlike 2.0 Generic (CC BY-SA 2.0) |

Table of Contents

Unit Notes 1

ICAICT203AOperate application software packages 1

Topic 3 – Spreadsheet Software 1

Topic 3 - Spreadsheets Software 5

Using the Unit Notes 5

Introduction 5

What is a Spreadsheet software package? 6

3.1 Select spreadsheet software appropriate to perform activity 6

Types of application packages 6

What is an integrated package? 7

Features of a spreadsheet package 7

Activity SP1.1: Selecting spreadsheet software 8

3.2 Identify document purpose and audience 8

Activity SP2.1: Document Audience and Purpose 9

3.3 Simple Formulas and Functions 9

Data types 9

Data entry 10

Understanding the main features of a spreadsheet 10

Use simple formula 11

Use operators 12

The order of operations 13

Use simple functions 13

Conditional Logic - IF 17

Conditional Summation 17

Lookup tables 18

Use absolute reference 21

Activity SP3.1: Formulas and Functions 22

3.4 Spreadsheet settings 24

Format text and numbers 24

Cell alignment 25

Page layout 25

Sort 26

Graphics 26

Creating charts 26

Built-in Help resources 30

Activity SP4.1: Spreadsheet settings 31

3.5 Naming and storing of documents in appropriate directories and printing 33

File Names 33

Storage 33

Versioning 33

Different file formats 33

Printing 34

Summary 34

Appendix 35

Topic 3 - Spreadsheets Software

These notes explore the use of spreadsheet software for business and mathematical applications. They will cover performing basic operations, including creating and formatting spreadsheet data, incorporating charts and objects, customising and printing spreadsheets. Also covered will be the revision of file management and naming conventions.

Using the Unit Notes

Icons and symbols are used throughout the guide to provide quick visual references.

They indicate the following:

|Icon |Meaning |Icon |Meaning |

|[pic] |ACTIVITY: An activity is listed to be | |ACTIVITY: A Learning activity requiring |

| |completed | |some physical action |

|[pic] |WWW: A web link is listed | |REFLECTION: A point is to be considered and|

| | | |thought about more deeply |

|[pic] |IMPORTANT: A pivotal point is detailed |[pic] |SEARCH: A particular item / book etc. needs|

| | | |to be found and applied |

Introduction

These notes will help you to develop your knowledge and skills to use spreadsheets to meet organisational needs. This will involve an understanding of:

• Data types and data entry

• Main features of spreadsheet worksheet

• Formatting text and numbers

• Using formulas, operators, and simple functions

• Using absolute reference

• Creating charts

• Design guidelines

While every effort has been made to keep the notes generic it has been important to use explanatory examples using a spreadsheet. For this purpose only Microsoft Excel has been used for examples. Most other spreadsheet applications are capable of performing all of these activities.

For detailed and up to the minute instructions on your spreadsheet application, visit the manufacturers website or any other many other tutorial websites on the internet.

What is a Spreadsheet software package?

Spreadsheets can be used anywhere there is a need to sort, organise, calculate, and redisplay data — so they are typically used for invoices, budgets, inventories and timesheets in an organisation. A spreadsheet can summarise information from many sources in one place and present the information in an easy-to-read way. It organises data, such as numeric values like dollars and cents, into columns and rows. A spreadsheet can also be used to perform calculations with the data, like adding up the numbers in a column to give a total.

3.1 Select spreadsheet software appropriate to perform activity

There are many spreadsheet software packages in the market place today. Common packages include Apple iWork, Microsoft Excel, and Open Office.

Which package you chose to use will depend on a few factors. The hardware and operating system platform on which you are working is a critical aspect. Are you running an Apple or Windows operating system? Are you looking for an integrated package or a standalone package? Another main deciding factor is price, while proprietary software is purchased; openware software is free to download from the internet.

Then, in terms of business efficiencies and of good practice, employees need to be able to competently and confidently use the most appropriate software to meet business needs. Your choice will not only depend on the ease of use (most effective use of time) but also what features each package contains to best meet the business needs.

Lastly, the compatibility with others should always be kept in mind – what will be the best software package for now and into the future. What is the most compatible package for our business and partners? If you share documents within your business and with your partners and customers – what packages are they using and will the package you choose be compatible with them?

Types of application packages

Software applications are available in a variety of types. Each business should consider which type of package best suits their requirements. The choices are divided into three broad categories:

1. proprietary software (sometimes referred to as ‘off the shelf’ software) which is sold through retail outlets

2. freeware/openware which is made available free of charge over the Internet

3. custom software (known also as ‘in-house’ software) which is written specifically for the business by either employees or contracted programmers

A spreadsheet is not commonly written as custom software as there are many packages already available on the market.

Within each of these categories applications can be available as ‘stand-alone’ programs or they can be part of an integrated package.

What is an integrated package?

An integrated package is a software package that contains a suite (several applications whose data is compatible). These packages usually include word processing, spreadsheet, presentation and database applications. The most well-known examples are:

• Apple iWork:

• Microsoft Office:

• Open Office:

Information on these packages can be found at the relevant website

Features of a spreadsheet package

Professional spreadsheet packages are capable of performing many intricate tasks. Most of these tasks are common across the packages. It is important to consider the most important features for your business and then select the application that best supports these needs. Here is a list of some of the features you would expect to find in most spreadsheet packages:

• Addition

• Conditional logic

• Conditional summation

• Division

• Lookup

• Multiplication

• Subtraction

• Summation

• Charts

• Font and formatting tools

• Graphics

• Objects

• Sorting

• Help

Activity SP1.1: Selecting spreadsheet software

You are working at a company called Pensline who manufacture and sell specialised corporate branded stationery including pens and paper supplies. To test and prove their products the company has always used pens and paper for customer orders and letters using pre-printed order books and letterhead paper. The need has arisen for Pensline to implement computer generated invoices, stock listings, stocktake results, sales and purchasing data.

The company is using Windows based personal computers with the latest version of the Windows Operating System loaded. The company shares documents with its business partner Paperline and supplier InkIcons who use Microsoft packages. They have recently started using the Microsoft Word package for their company documents.

You have been asked to investigate spreadsheet software packages on the market and recommend an appropriate package for Pensline to implement.

1. Using the internet or alternative source, research three common spreadsheet software packages. List the packages found and the company website.

2. Recommend a package that will be able to:

a. Create charts

b. Do summation, multiplication and division

c. Perform inbuilt functions to complete subtotals

d. Format numbers into currency and percentage

e. Format fonts, colours and borders

f. Be compatible with their operating system and other software

g. Be compatible with their partners and suppliers

3.2 Identify document purpose and audience

When creating and editing spreadsheet documents it is important to have an understanding of the purpose of the document so you can ensure its effectiveness. Is the purpose of the document to provide information like a list, record information, calculate percentages, analyse data, display trends and information in charts, place an order, send an invoice or statement, or something else?

It is vitally important to be able to answer this question for your document before you begin. The purpose of the document will dictate what type information needs to be included, the design, formatting and layout, and the security and storage of the document.

When you understand the purpose of the document you then need to think about who will be using this document – what is the audience? Is it an internal document only for staff? Is it for financial staff, office and administration staff? Do you need to apply security or protection to the formulas so they are not accidentally deleted? Is it for the management team of the business? Is it solely for data entry or analysis?

Again, this information will assist in the design of the document, the presentation, the security and complexity.

Even though you may have answered these questions and are confident that you know what the purpose and audience is, it is always a good idea to have a colleague or client confirm your thoughts. This way you can be sure your final spreadsheet has a good chance of achieving it’s desired purpose and the intended audience understands the content.

Activity SP2.1: Document Audience and Purpose

In the table below, write down a purpose and audience for each document type.

|Document Type |Purpose |Audience |

|Stock on hand spreadsheet | | |

|Invoice |e.g. will need to calculate the total invoice | |

| |price based on number of items purchased and unit | |

| |price per item | |

|Statement | | |

|Financial report | | |

|Team sport results | | |

3.3 Simple Formulas and Functions

Data types

Most spreadsheets can handle the following types of data:

• numbers

• dates

• times

• text

Numbers

Numbers can have different formats. On a computer, whole numbers are represented in a different way to decimals. In spreadsheets we often work with even more specialised numbers like time of day and money amounts. Spreadsheets are specifically designed to handle each of these different types of numbers appropriately.

Dates

Dates contain numbers and we commonly use different ways of representing them. For example:

• 5/9/2007

• 5-9-2007

• 5-Sep-2007

• September 5 2007

While we have little trouble with these variations, a computer program would. Most spreadsheet programs require you to use just a few basic alternatives. To add to the difficulty, USA dates are different to Australian date formats. In Australia 5/9/2004 means September 5th, while in the USA it means 9th of May.

Times

Times are also tricky. We can be using a 12-hour clock or a 24-hour clock. The spreadsheet needs to know which is being used; otherwise calculations based on time could be incorrect.

Text

Text is more straightforward and most spreadsheets will treat any combination of characters with numeric and non-numeric characters as text. For example, a telephone number like: 9123 456 is likely to be treated as text rather than a number because it contains a space.

Data entry

There are a number of methods of data entry. When creating a spreadsheet document from scratch you can start with a blank document and create your own spreadsheet from the keyboard. Alternatively, you can import data from an existing spreadsheet.

For more information and tutorials on how to do this, there are documents you can download from the internet. You should also use the built-in Help resources in your software to guide you through the steps in detail.

Understanding the main features of a spreadsheet

Here are the main features that a spreadsheet package may contain, in terms of the way in which the spreadsheet file is set up. You’ll need to investigate if your package works a little differently to the examples below for the Microsoft Excel spreadsheet package.

Worksheets

A worksheet is a single sheet in the spreadsheet used to list and analyse data. You can enter and edit data on several worksheets simultaneously and perform calculations based on data from multiple worksheets.

Workbook

In Microsoft Excel, a workbook is the file in which you work and store your data. Each workbook can contain many worksheets and you can organise various kinds of related information in a single file.

Sheet tabs

The names of the worksheets appear on tabs at the bottom of a workbook window. To move from sheet to sheet, click the sheet tabs.

Features of a worksheet

The screen shot below shows the main features of an open worksheet:

[pic]

Figure 1 Microsoft Excel Spreadsheet window

Use simple formula

Formulas calculate values in a specific order. Typically, you might want to add together all the values in a column or row of your spreadsheet. This is usually done by entering a formula that enables the addition (+) operation to be applied to a range of cells. Because the cells are each identified by a unique name (row number and column letter) it is easy to specify in the formula which cells are to have the calculation applied to them.

The description below is based on the spreadsheet package Microsoft Excel, but most spreadsheets use the same system for representing formulas and ranges of cells.

In most spreadsheet packages a formula always begins with an equal sign: =. The equal sign tells the spreadsheet program that the following characters make up a formula. Following the equal sign are the elements to be calculated (the cells containing the numbers for the calculation, or operands), which are separated by calculation operators (+, -, * multiply or / divide).

The spreadsheet program calculates the formula from left to right, according to a specific order for each operator in the formula. For example:

=B2+C2-D2

This entry will calculate the contents of cell B2 added to the contents of cell C2 from which the contents of cell D2 will be subtracted.

You can change the order of operations by using parentheses (round brackets). After the formula is typed into the cell, the calculation executes immediately, the numeric value of the answer is displayed in the cell, and the formula itself is visible in the formula bar.

In the example below, the parentheses around the first part of the formula force the spreadsheet program to calculate B4+B5 first and then divide the result C3:

=(B4+B5)/C3

The result of any formula will change when the contents of any of its cell references change. For example, if a value in any of the following cells changes, the result of the formula =B2+C2+D2 also changes.

Make sure you know how your spreadsheet package represents formulas and cell ranges and how it may be different to examples given above.

Use operators

Operators are actions that are applied to a value or values. Operators are usually represented by a symbol. For example, + (plus, or add) is an operator that is usually applied to two values: 3+4 produces a new value 7. You are probably familiar with the basic arithmetic operators.

Spreadsheets use the usual arithmetic operators but introduce several of their own to represent things like cell ranges. For example:

D5:D20

represents the cells from D5 to D20, as formulas can also refer to names of cells or to ranges (groups) of cells.

Other operators you’ll find within a spreadsheet are represented as:

|Operator |Description |

|% |percent |

|^ |Exponentiation (raised to the power of) |

|* |multiplication |

|/ |division |

|+ |addition |

|- |subtraction |

The order of operations

You may not be so familiar with the idea that when several operators appear in the same expression, for example:

4 + 3*5

The operations will have precedence. This means that the operators with highest precedence are performed first. In the example above, multiply (*) has a higher precedence that plus (+). So the value of 4+3*5 is 19 and not 35.

An easy rule to remember when evaluating arithmetic expressions, that is, how the order in which a calculation is performed, is BODMAS:

Brackets ()

Of of

Division /

Multiplication *

Addition +

Subtraction -

If you are in any doubt about how a formula you write will be calculated, put brackets around the parts you want performed first.

If you are not using Microsoft Excel, check the order in which operations are performed (operator precedence) in your spreadsheet package to check.

Use simple functions

Functions are predefined formulas that perform calculations by using specific values called arguments. For example, the SUM function adds values or ranges of cells.

Arguments can include numbers, text or cell references. The argument you designate must produce a valid value for that argument.

The structure of a function is as follows:

1. begins with the function name

2. followed by an opening parenthesis

3. then the arguments for the function separated by commas

4. then a closing parenthesis.

For example:

[pic]

Figure 2 Structure of a function

If the function starts a formula, type an equal sign (=) before the function name. As you create a formula that contains a function, the Formula Palette will assist you.

Most spreadsheets will have the following useful functions:

• SUM – adds the values of all of the cells in the range

• MAX – finds the largest number in the range of cells

• MIN – finds the smallest number in the range of cells

• COUNT – counts the total number of cells in the range

• AVERAGE – adds the values of each cell in the range and the divides by the number of cells in the range.

SUM

The most frequently used function is the SUM function, which is used to add the numbers in a range of cells. The syntax (the specific way of expressing a SUM) is:

SUM(number1,number2,etc)

number1,number2,etc can include the numbers for which you want the total value or sum. The items in the parentheses ( ) are arguments.

Numbers, logical values, and text representations of numbers that you type directly into the list of arguments are counted. See the first and second examples below.

Arguments that are error values, or text that cannot be translated into numbers, cause errors.

Examples

This formula equals 5

[pic]

Figure 3 Simple SUM

If the range of cells E1:E5 (cell E1 down to cell E5) contains the following numbers:

[pic]

Figure 4 Another SUM

SUM(E1:E3) equals 50

And

[pic]

Figure 5 SUM with cells references and numbers

SUM(E2:E5,15) equals 150.

MAX

This function returns the largest value (or number) in a set of values. The syntax used to express this is:

MAX(number1,number2,etc)

number1,number2,etc can include 1 to 30 arguments for which you want to find the maximum (highest) value.

Example

If the range of cells E1:E5 (cell E1 through cell E5) contains the following numbers, then:

[pic]

MAX(E1:E5) equals 27.

MIN

MIN returns the smallest value in a set of values.

Example

If E1:E5 contains the same numbers then:

[pic]

MIN(A1:A5) equals 2.

COUNT

This function counts the number of cells that contain numbers within the list of arguments. Use the function COUNT to get the number of entries in a number field in a range of numbers. The syntax for COUNT is:

COUNT(value1,value2,etc)

value1,value2,etc are the arguments that can contain or refer to a variety of different types of data, but only numbers are counted.

Arguments that are numbers, dates, or text representations of numbers are counted; arguments that are error values or text that cannot be translated into numbers are ignored.

Example

|[pic] |For this list: |

| |COUNT(A1:A7) equals 3 |

| |COUNT(A4:A7) equals 2. |

AVERAGE

AVERAGE returns the average of the group of numbers. The syntax used is:

AVERAGE(number1,number2,etc)

number1,number2,etc are the arguments for which you want the average. The arguments must be numbers or text that can be translated into numbers.

Example

If the range of cells E1:E5 contains the following numbers, then:

[pic]

AVERAGE(A1:A5) equals 11.

Conditional Logic - IF

In some instances you will want to analyse information based on set criteria. For instance, if a student receives a mark greater than 50, then they Pass, otherwise they will Fail. This situation requires the use of the function IF.

The IF function will returns one value if a condition you specify evaluates to TRUE and another value if it evaluates to FALSE.

The syntax for the IF function is:

IF(logical_test,value_if_true,value_if_false)

Example

IF(A10>50, “Pass”,”Fail”)

Logical_test    is any value or expression that can be evaluated to TRUE or FALSE. For example, A10>50 is a logical expression; if the value in cell A10 is greater than 50, the expression evaluates to TRUE. Otherwise, the expression evaluates to FALSE.

Value_if_true    is the value that is returned if logical_test is TRUE. For example, in the above if A10 is 70 then the logical_test argument will return TRUE, then the IF function displays the text "Pass".

Value_if_false    is the value that is returned if logical_test is FALSE. For example, in the above if the value in A10 is 40 then the logical_test argument will return FALSE, then the IF function displays the text "Fail".

Conditional Summation

Taking the IF function further, there is a function call SUMIF, that will complete a summation based on an IF function type criteria. As an example, only add up the sales for a particular salesperson.

The syntax for the SUMIF function is:

SUMIF(range, criteria, [sum range])

Example: Add up the sales for John

[pic]

Figure 6 SUMIF in Excel

Range    is the range of cells to be evaluated. In this example the range is the names of the salespeople.

Criteria    is the condition in the form of a number, expression, or text that defines which cells will be added. In this example the text to look for is John.

Sum_range    are the actual cells to sum. If omitted, the cells in the range will be used. In our example, it will sum up the sales values.

The answer is 19.

Lookup tables

A lookup is a very powerful and useful feature of a spreadsheet — it can reduce keyboard entry time, thus reducing errors and saving time and effort.

A lookup table is used to find one piece of information that is based on another piece of information. A lookup table consists of a column or row of ascending values called compare values and corresponding data for each compare value.

In Microsoft Excel, two of the inbuilt lookup functions are:

• VLOOKUP

• HLOOKUP.

The value for the first column in VLOOKUP or the top row in the HLOOKUP can be text, numbers or logical values. Entries in the first column (or in the top row) must be placed in ascending order and are not case-sensitive. If the exact match cannot be found VLOOKUP or HLOOKUP returns the value for the largest that is less than or equal to it.

VLOOKUP function

The powerful VLOOKUP function (vertical lookup, which searches in columns) looks up a value in a table and returns a corresponding value. The VLOOKUP function has three arguments:

1. The value to lookup.

2. The range of cells containing the lookup table.

3. The column number within the table that contains the required result.

[pic]

Figure 7 Excel file with VLOOKUP example

Lookup_value: The value to be found in the first highlighted column or row of the table is usually a cell reference.

Table_array: The table of information where the data is looked up. It can be a range of cell addresses and absolute referencing or a named range.

Col_index_num: The column or row number in the table from which the result is to be obtained.

Naming a cell range

A name can be defined for a range of cells and then be used in a formula.

[pic]

Figure 8 Excel define name example

Define a Range Name in Excel

1. Highlight the required cell range.

2. Select Formulas tab and Defined Names group. Select Define Name.

3. Enter the desired name (no spaces are allowed for named ranges, so you should use underscore ( _ ).

4. Click OK.

Example of a name in a formula:

VLOOKUP(A5, PRODUCTS, 2)

Note: An absolute reference or table name should be used when specifying the table so that the addresses do not change if the function is copied to other cells on the spreadsheet.

The IF function

The IF function allows you to make a decision based on two possibilities. What if there are more possibilities? For example, in the case of student results recorded, and there are four possible outcomes for any student’s grade: A, B, P (Pass), or F (Fail). The best function to use in this case is one of the Lookup functions.

Here is more detail for the student grades example:

|Mark |Grade |

|Less than 50 |F |

|Greater than or equal 50 and less than 70 |P |

|Between 70 and 82 inclusive |B |

|83 or more |A |

We could try doing this with a ‘nested’ IF statement but it would become very complex and easy to make a mistake. It would look like this:

=IF(F4 ................
................

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

Google Online Preview   Download