Create and use spreadsheetsBSBITU202ALearner’s guide

First published 2012ISBN 978-1-74205-833-7 VET (WA) Ministerial Corporation 2012All the Department of Training and Workforce Development materials, regardless of format, areprotected by copyright law.This work is made available under a Creative Commons Attribution-NonCommercial-ShareAlike3.0 Unported (CC BY-NC-SA) licence (unless otherwise specified). For more informationplease visit Under this licence, you maycopy, print, communicate and adapt the material for personal or non-commercial purposes,including educational or organisational use, provided you attribute the Department of Trainingand Workforce Development, Central Institute of Technology and the Building and ConstructionIndustry Training Fund, link to the Creative Commons website named above and license anynew work created incorporating material from this resource under a CC BY-NC-SA Licence.Whilst every effort has been made to ensure the accuracy of the information contained in thispublication, no guarantee can be given that all errors and omissions have been excluded. Noresponsibility for loss occasioned to any person acting or refraining from action as a result ofthe material in this publication can be accepted by the Department of Training and WorkforceDevelopment.Produced in partnership withPublished by and available fromDepartment of Training and Workforce Development1 Prospect Place West Perth WA 6005Tel: (08) 6212 9700 Fax: (08) 9227 8393Email: [email protected]: resource contains units of competency from the BSB07 Business Services TrainingPackage – BSBITU202A Create and use spreadsheets Commonwealth of Australia,used under Creative Commons Attribution-No Derivative Works 3.0 Australia licence, Windows and Excel are trademarks of the Microsoft group of companies.This product contains various images Thinkstock 2012, used under licence. These images areprotected by copyright law and are not to be reproduced or re-used in other materials withoutpermission from the owner of Thinkstock.

ContentsWelcome.7Qualification overview.8Unit overview.8Resources.11Self‑checklist.12About the icons.14Section 1 – Introduction to spreadsheets.15Introduction.15What can spreadsheets do?.15Spreadsheet programs.16Section 2 – Ergonomics.19Introduction.19Setting up your workstation.19Lighting.22Eyes and body.23Section 3 – Conservation of resources.25Introduction.25Conservation techniques.25Section 4 – Drives, files and folders.27Introduction.27Getting organised.27Section 5 – Getting helpIntroduction.33How to get help.33 VET (WA) Ministerial Corporation 2012 BC19323

Create and use spreadsheetsBSBITU202ASection 6 – Toolbars, tool buttons and shortcuts.35Introduction.35Toolbars and tool buttons.35Keyboard shortcuts.37Section 7 – Navigating around a spreadsheet.39Introduction.39Navigating around a spreadsheet.39Section 8 – Entering and editing data.41Introduction.41Entering and editing data.41Selecting cells and ranges of cells.43The undo and redo commands.44Section 9 – Filling, copying, cutting and pasting.47Introduction.47The Fill function.47Copying and pasting.49Cutting and pasting.50Section 10 – Working with workbooks and worksheets.51Introduction.51Setting up worksheets.51Section 11 – Formatting.53Introduction.53Basic formatting.54Section 12 – Formulas.57Introduction.57Basic formulas.574BC1932 VET (WA) Ministerial Corporation 2012

ContentsSection 13 – Working with rows and columns.61Introduction.61Working with rows and columns.61Section 14 – Preparing to print.65Introduction.65Page setup.65Headers and footers.68Printing.70Section 15 – Functions.75Introduction.75Functions.75Section 16 – Formula exercises.77Introduction.77Section 17 – Charts and other inserts.81Introduction.81Charts.82Other inserts.84Annex A – Unit detailsAnnex B – Learning planAnnex C – Assessment planAnnex D – Assessments VET (WA) Ministerial Corporation 2012 BC19325

Create and use spreadsheets6BSBITU202ABC1932 VET (WA) Ministerial Corporation 2012

WelcomeThis learner’s guide will take you through the process of learning how to set up, enterdata into and print simple spreadsheets.Areas of explanation include how to: work safely and comfortably organise your files and folders navigate around a spreadsheet and enter data use formulas to carry out calculations use formatting to enhance the appearance of spreadsheets produce charts from data.This guide will introduce you to the basic principles of electronic spreadsheets. It hasbeen written for people who have little or no knowledge of how spreadsheets are usedor what they can do.However, it does assume that you know your way around a keyboard, have a basicknowledge of how to use menus in a computer program and have a reasonableknowledge of maths (you’ll need this for working out the formulas you’ll be using inyour work).There are many software companies that produce spreadsheet programs, from thevery sophisticated to relatively simple ones available for free. If there are differencesbetween what’s in this guide and the way your spreadsheet program operates, yourlecturer will point them out and perhaps get you to make notes. VET (WA) Ministerial Corporation 2012 BC19327

Create and use spreadsheetsBSBITU202AQualification overviewThis unit of competency, BSBITU202A Create and use spreadsheets, forms part ofCertificate II in Building and Construction (Pathway – Paraprofessional) and is aimed atthose people who are considering a paraprofessional career in the residential buildingindustry (as opposed to the trade sector).The course consists of 12 units of study and a period of work placement. These twocomponents, study and work, will provide you with an introductory background to theparaprofessional side of the residential building industry.To progress further in the industry, beyond this introductory level, you will then need tospecialise in a particular field of study, such as building, estimating, scheduling, draftingor building design. Courses for these careers usually commence at Certificate IV leveland progress through to diploma or even advanced diploma levels at a registeredtraining provider who delivers these programs.Some areas of study, such as architecture, interior design and constructionmanagement, can then be studied further at degree level at a university.Unit overviewThis unit describes the performance outcomes, skills and knowledge required tocorrectly create and use spreadsheets and charts through the use of spreadsheetsoftware.No licensing, legislative, regulatory or certification requirements apply to this unit at thetime of endorsement.Competence in this unit will be demonstrated by:8 designing a minimum of two spreadsheets using cell-based formulas creating charts using relevant data completing assessments that demonstrate your knowledge of the purpose andrange of uses of spreadsheet functions.BC1932 VET (WA) Ministerial Corporation 2012

WelcomeUnit summarySome basic information about this unit of competency is provided below. You can findthe full unit details at Annex A at the back of this guide.Unit titleCreate and use