carpentary

naidurupa07

BSBITU314LearnerGuide.pdf

Home >Architecture and Design homework help >carpentary

Learner Guide CPC30211 Certificate III in

Carpentry

Learner name: _______________________________

This Workbook incorporates the following unit: BSBITU314

Design and produce spreadsheets

Learner Activity WorkbookBSBITU314 Design and produce spreadsheets V1 CPC30211 Certificate III in Carpentry

Page 2 of 66

Disclaimer: This work is under copyright and permission is not given to make copies for hire or resale to third parties to use the resources for their own or commercial use. Licenced to: Orange International College CRICOS Provider Code: 03446A RTO No: 41315 Level 6, 416-420 Collins Street Melbourne Victoria 3000

Learner Activity WorkbookBSBITU314 Design and produce spreadsheets V1 CPC30211 Certificate III in Carpentry

Page 3 of 66

Contents Unit of competency .................................................................................................................. 5 Overview/Competency demonstration ........................................................................................ 5 BSBITU314 Design and produce spreadsheets ............................................................................ 5

Plan spreadsheet design ..................................................................................................... 5 Create spreadsheet ............................................................................................................ 5 Produce intermediate-level charts ........................................................................................ 6 Finalise and present spreadsheets ....................................................................................... 6

Learner Activity Workbook Overview .......................................................................................... 7 Section 1 – Select and prepare resources ................................................................................... 9

Adhere to ergonomic, work organisation and occupational health and safety requirements ...... 9 Lighting ............................................................................................................................ 9 Noise ................................................................................................................................ 9 Heat ................................................................................................................................. 9 Cables .............................................................................................................................. 9 Posture ........................................................................................................................... 10 Use energy and resource conservation techniques to minimise wastage ............................... 11 13 Energy Saving Ideas for a Sustainable Workplace .......................................................... 11 What is waste management? ............................................................................................ 14 Life cycle of a product ...................................................................................................... 14 Why is managing waste important? ................................................................................... 14 Identify spreadsheet task requirements in relation to data entry, storage, output and presentation .................................................................................................................... 15

Learning Activities Section 1 .................................................................................................... 16 Section 2 – Plan spreadsheet design ........................................................................................ 19

Ensure spreadsheet design suits purpose, audience and information requirements of task ..... 19 Spreadsheet Design Tips for Microsoft Excel ....................................................................... 19 Ensure spreadsheet design enhances readability and appearance, and meets organisational andtask requirements for style and layout .......................................................................... 21 Formatting to improve readability ...................................................................................... 22 Reading Styles ................................................................................................................. 23 Formatting ...................................................................................................................... 24 Use style sheets and automatic functions to ensure consistency of design and layout ............ 24 Style Sheets .................................................................................................................... 24

Learning Activities Section 2 .................................................................................................... 25 Section 3 – Create spreadsheet ............................................................................................... 26

Ensure data is entered, checked and amended to maintain consistency of design and layout, in accordance with organisational and task requirements ........................................................ 26 Creating a table ............................................................................................................... 28 Creating a data spreadsheet ............................................................................................. 29 How to begin ................................................................................................................... 29 Data to be entered ........................................................................................................... 30 Process ........................................................................................................................... 30 Sizing columns/rows ........................................................................................................ 30 Format spreadsheet using software functions to adjust page and cell layout to meet information requirements, in accordance with organisational style and presentation requirements ................................................................................................................... 31 Adding/deleting columns/rows .......................................................................................... 31 Formatting cells ............................................................................................................... 32 Formatting text ................................................................................................................ 32 Headers/footers ............................................................................................................... 33

Learner Activity WorkbookBSBITU314 Design and produce spreadsheets V1 CPC30211 Certificate III in Carpentry

Page 4 of 66

Formatting the document ................................................................................................. 34 Apply conditional formatting based on text in a cell ............................................................ 34 Quick formatting .............................................................................................................. 34 Advanced formatting ........................................................................................................ 35 Relative & absolute cell references .................................................................................... 35 Relative cell references ..................................................................................................... 35 Absolute cell references .................................................................................................... 36 Using advanced formula ................................................................................................... 36 Ensure formulae are tested and used to confirm output meets task requirements, in consultation with appropriate personnel as required ........................................................... 37 Errors in your formula ...................................................................................................... 37 Totalling wages ............................................................................................................... 37 Use manuals, user documentation and online help to overcome problems with spreadsheet design and production ...................................................................................................... 41

Learning Activities Section 3 .................................................................................................... 42 Section 4 – Produce simple charts ............................................................................................ 44

Select chart type and design that enables valid representation of numerical data, and meets organisational and task requirements ................................................................................ 44 Area chart/graph ............................................................................................................. 44 Column/bar chart ............................................................................................................. 44 Histogram ....................................................................................................................... 45 Line/curve ....................................................................................................................... 45 Pie .................................................................................................................................. 45 Scatterplot ...................................................................................................................... 46 Create charts using appropriate data range in spreadsheet .................................................. 46 Arrange data for charts .................................................................................................... 47 Creating charts ................................................................................................................ 48 Modify chart type and layout using formatting features ....................................................... 48 To switch row and column data......................................................................................... 49 Let's look at how to do this. .............................................................................................. 49 To move a chart .............................................................................................................. 51

Learning Activities Section 4 .................................................................................................... 52 Section 5 – Finalise spreadsheets ............................................................................................. 54

Preview, adjust and print spreadsheet and any accompanying charts, in accordance with task requirements ................................................................................................................... 54 Ensure data input meets designated timelines and organisational requirements for speed and accuracy ......................................................................................................................... 54 Name and store spreadsheet in accordance with organisational requirements and exit application without data loss/damage ................................................................................ 54

Learning Activities Section 5 .................................................................................................... 55 Further Reading ..................................................................................................................... 56 Simulated Learning Activities ................................................................................................... 57 Self-Assessment Questionnaire – Design and produce spreadsheets ........................................... 61 Learning Participation Record .................................................................................................. 64 Unit of competency ................................................................................................................ 64

Learner Activity WorkbookBSBITU314 Design and produce spreadsheets V1 CPC30211 Certificate III in Carpentry

Page 5 of 66

Unit of competency BSBITU314 Design and produce spreadsheets

Overview/Competency demonstration This Workbook covers the following unit of competency:

BSBITU314 Design and produce spreadsheets This unit describes the skills and knowledge required to develop spreadsheets through the use of both cloud-based and non-cloud based spreadsheet applications. It applies to individuals employed in a range of environments who tend to be personally responsible for designing and working with spreadsheets under minimal supervision. These individuals are generally required to have intermediate knowledge and understanding of a number of spreadsheet applications. No licensing, legislative or certification requirements apply to this unit at the time of publication. To demonstrate your competency in this unit you will need to provide evidence of your ability to: Select and prepare resources  Identify spreadsheet task purpose and audience  Identify task requirements in relation to data entry, storage, output, timeline and presentation

format  Select most appropriate application to produce spreadsheet, in accordance with available

resources and organisational policies Plan spreadsheet design  Ensure spreadsheet design suits purpose, audience and information requirements of task  Ensure spreadsheet design enhances readability and appearance, and meets organisational and

task requirements for style and layout  Use available application functions to ensure consistency of design and layout, adhering to

organisational and task requirements Create spreadsheet  Enter data, check and amend to maintain consistency of design and layout, in accordance with

organisational and task requirements  Format spreadsheet using application functions to adjust page and cell layout to meet

information requirements, in accordance with organisational style and presentation requirements

 Ensure formulae are tested and used to confirm output meets task requirements, in consultation with appropriate personnel as required

 Use relevant help functions to overcome intermediate-level issues with spreadsheet design and production

Learner Activity WorkbookBSBITU314 Design and produce spreadsheets V1 CPC30211 Certificate III in Carpentry

Page 6 of 66

Produce intermediate-level charts  Select chart type and design that that offers analysis of numerical data, and meets

organisational and task requirements  Create charts using appropriate data range in spreadsheet  Modify chart type and layout using formatting features, adhering to organisational and task

requirements Finalise and present spreadsheets  Review and edit final spreadsheet and any accompanying charts, and prepare for delivery in

accordance with task requirements  Deliver document to relevant audience within designated timelines and in accordance with

organisational requirements for speed and accuracy  Name and store spreadsheet appropriately in accordance with organisational requirements and

exit application without data loss/damage Prerequisite units: Nil https://training.gov.au/Training/Details/BSBITU314

https://training.gov.au/Training/Details/BSBITU314

Learner Activity WorkbookBSBITU314 Design and produce spreadsheets V1 CPC30211 Certificate III in Carpentry

Page 7 of 66

Learner Activity Workbook Overview The content of this Learner Activity Workbook is designed to monitor your learning to provide ongoing feedback that can be used by your Trainer to improve their teaching, and by you to improve your learning. The Learner Activity Workbook incorporates the following tasks, to help you identify your areas of strength, and target areas of work:

 Learning Activities  Simulated Learning Activities  Self-Assessment Questionnaire This Learner Activity Workbook does not need to be submitted to your Trainer, however the Learning Participation Record’ sheets for this unit of competency are required to be submitted as evidence of participation, at the end of units/cluster.

Learner Activity WorkbookBSBITU314 Design and produce spreadsheets V1 CPC30211 Certificate III in Carpentry

Page 8 of 66

Instructions Learning Activities

 After each section within this Learner Activity Workbook, there is a series of Learning Activities. These activities are useful to consolidate your learning, and to identify any areas in which you may require further learning.

 You must answer all questions in the Learning Activities.  Your answers to these questions will form part of your participation of learning evidence

gathered to determine your readiness for assessment. Simulated Learning Activities  The Simulated Learning Activities are to be completed in a simulated learning environment,

under specific instruction by your Trainer.

 Simulated Learning Activities incorporate case studies, scenarios and lifelike examples, to support positive learning for the Learner to replicate real experiences that occur in a workplace setting. Where applicable, scenarios have forms and templates that are provided.

 To complete the Simulated Learning Activities, the following resources are required: - Simulated Policies and Procedures - Simulated Work plans, drawings and specifications - Learner Activity Workbook

 Where Simulated Activities require specific resources, these will be listed in the Simulated Activity task instructions.

Self-Assessment Questionnaire  At the end of this Workbook there is a Self-Assessment Questionnaire that you must complete

to show that you are ready to be assessed in this unit of competency.

 You must answer all questions in the Self-Assessment Questionnaire.  Your answers to these questions will form part of your participation of learning evidence

gathered to determine your readiness for assessment.

Learner Activity WorkbookBSBITU314 Design and produce spreadsheets V1 CPC30211 Certificate III in Carpentry

Page 9 of 66

Section 1 – Select and prepare resources Adhere to ergonomic, work organisation and occupational health and safety requirements Computers are known to present a number of issues surrounding the area of Occupational Health and Safety. These health and safety issues may include but are not limited to:

 Lighting  Noise  Heat  Cables  Posture  Occupational overuse syndrome Lighting If computers are being used in an area that is poorly lit, it can cause glare and reflections to affect the screen, which can make working on the computer difficult and/or dangerous. To avoid this from happening, the screen must be positioned in a way that stops any light that is entering the room from causing any reflections on the screen. One way of doing this includes, placing the screen so that it neither faces or is in front of a window. Noise Much of the noise caused by information processing equipment is highly irritating and can be, in some cases, the cause of headaches. Some ways to minimise noise from IT equipment are by keeping printers in another room, separate to computers, or by putting it into a special hooded box. This kind of equipment Heat IT equipment can produce a substantial amount of heat. Due to this, many computers and other equipment of the like, have built in fans which are activated to cool down the equipment when it begins to overheat. By doing this, the fan is spreading the heat from the equipment into the surrounding area, which means that the room can become hot if the room isn’t properly ventilated. Cables When looking behind a functioning computer, there will be many cables that are leading to and from the power source, the machine and other devices. Cables can prove to be dangerous, especially in a small room, or in an area where the cables are left lying about haphazardly. If the cables are left in a disorderly manner, and are blocking places where people will be walking, they can become obstacles. The threat of someone accidentally tripping is a major hazard, as well has the electricity that is being conducted by the cables. Some ways to avoid encountering any hazardous incidents with cables are:

 Knowing the location of cables and power points in the area.  Never plug loose cables into a socket whilst power is still being supplied to the system.  Taking care not to trip over any cables that may be encountered.

Learner Activity WorkbookBSBITU314 Design and produce spreadsheets V1 CPC30211 Certificate III in Carpentry

Page 10 of 66

Posture Posture is very important when working with computers, as poor posture can lead to a number of health issues. It is recommended that one uses the most ideal sitting position when doing any kind of desk work. This can be done by:

 Lowering or raising the height of the chair where necessary to ensure that one’s feet are flat on the floor.

 Tilting the angle of the seat slightly forward.  Altering the height of the back rest to ensure that the lower back is being supported.  Placing one’s elbows at their sides, and then bringing forearms up to a right angle position on

the desk.

 Adjusting the keyboard area where required. The diagram below depicts the ideal sitting position when using a computer.

The ideal sitting position for computer operation

Learner Activity WorkbookBSBITU314 Design and produce spreadsheets V1 CPC30211 Certificate III in Carpentry

Page 11 of 66

Use energy and resource conservation techniques to minimise wastage Energy conservation is not only good for the environment, but it also reduces operating costs by a significant amount. Some energy conservation techniques can include:  Utilising double sided paper

 Recycling any paper that has been used, as well as shredded paper  Re-using paper for rough drafts (whilst taking care to follow confidentiality requirements)  Utilising power-save options for IT equipment Read the following from: https://www.alsco.com.au/2017/06/workplace-energy-saving-ideas/ 13 Energy Saving Ideas for a Sustainable Workplace One of the things that make running a business such an adventure is juggling the different types of costs you have to deal with every day. Those can be your employees’ salaries and benefits, as well as the space rentals and utilities. All of these factors can, in one way or another, affect your budget and therefore, should be taken into consideration wisely. One way to be wise about your operation costs is to use energy wisely. While it’s every business owners’ responsibility to think about practical ways to make smart changes around the workplace, it is also important that everyone in the company is involved in this energy saving initiative. Employees should also contribute in whatever way they can and help the company make a difference. Here are a few simple ways you can save energy in the workplace and bring positive changes to your working environment:

1. Switch off artificial lights and use natural light

Artificial lights consume power – natural light is free. So, limit the use of artificial lighting to the dark areas in the workplace that are out of the sun’s reach. Everything else should make use of the natural light instead. If not in use, switch off the lights at meeting rooms, pantry, reception, corridors, or stairs. If there’s nobody in the room for more than a couple of seconds – kill the lights!

2. Choose energy efficient light bulbs

Less energy spent means less money wasted on electricity bills. If your workplace does not get enough natural light during the daytime, you can opt for low wattage lights. You can also replace existing bulbs with CFL or LED lights. They consume less power and last for longer periods of time.

3. Choose laptops over desktops

Laptops typically consume less energy compared to desktops, so keep this in mind when buying your workplace equipment. opt for laptops whenever possible. The monitor size also contributes to the amount of energy consumed. While graphic designers and people from similar lines of work cannot work on small screens, there are a lot of other employees in your company whose work doesn’t depend on the huge monitors

https://www.alsco.com.au/2017/06/workplace-energy-saving-ideas/

Learner Activity WorkbookBSBITU314 Design and produce spreadsheets V1 CPC30211 Certificate III in Carpentry

Page 12 of 66

4. Use hibernation feature on all computers

Advise everyone to put their computers in hibernation mode if they take a break or go to a meeting. This applies to any situation when an employee won’t be using it for a long period of time. The hibernation feature in computers allows users to save existing work as it is and continue working at the exact same point upon return. Employees can set their computers to go into the hibernation mode when they are inactive for several minutes.

5. Use energy saving features of all devices

Make sure that your employees are aware of the energy-saving features of appliances and other electronics like the printers, microwaves, and air conditioners. Most of the modern devices have these options and they are usually very easy to use. In most cases, it is a matter of pressing a button or adjusting a setting feature. Advise everyone to use these features to help cut energy costs.

6. Upgrade all outdated equipment

Old electrical equipment that is no longer working at their maximum efficiency could only draw unnecessary power, costing you more money. It’s best to replace your old office appliances with new certified energy efficient ones. However, make sure that you dispose of your old equipment in a proper way and recycle it. There are a lot of companies that specialise in recycling this type of waste. You just need to find a company that recycles electronic waste near you and call them.

7. Buy energy efficient devices

Energy efficient devices may cost more upfront but they’re going to consume less, saving you more money in the long run. Their price is decreasing, because, nowadays, being energy efficient is becoming a standard that a lot of companies try to keep.

8. Do an energy audit It’s good to check your workplace’s energy consumption and evaluate your overall energy efficiency by doing energy audits. This way, you’ll know whether you are consuming way too much energy or using just enough to sustain the business operations. This is also one way to see if your energy saving efforts are working and paying off. There are energy audit companies you can hire and they can help you identify areas where you can trim down your energy consumption.

9. Switch off equipment when not in use

Just like with lights, make sure that you switch off and plug out all equipment when not in use. This includes air conditioners, coffee vending machines, hand dryers, microwaves, printers, copiers, and scanners during weekends or holidays. Making sure all of your devices are plugged out after working hours as this saves energy and reduces your electricity bill.

Learner Activity WorkbookBSBITU314 Design and produce spreadsheets V1 CPC30211 Certificate III in Carpentry

Page 13 of 66

10. Print only when necessary

Avoid printing files that can be sent via email. There are many ways you can share or store important files today. Modern technology gives you a ton of options, including cloud storage, email clients and even ped drives if you insist. Aside from reducing paper wastage, this also helps cut the total amount of energy consumed by the printer.

11. Control your heating and cooling

Cutting down on air conditioning could mean significant savings for you. Don’t make drastic difference between the temperature outside and the one in your workplace, but keep in mind the conditions of optimum working environment.

12. Consider installing solar panels

Aside from being a great source of renewable energy, solar panels are clean, longer-lasting, and require little maintenance. Some solar panels might cost you a lot initially, but with the amount of energy and money you save, they pay off sooner than you think.

13. Promote sustainability in the workplace

Promote sustainability throughout the company. Explain to you employees why it’s important and in what ways everyone can benefit from it. You can start small by sending out a reminder for everyone to always check if their computers are unplugged before leaving the office at the end of their shifts. The secret to reducing energy consumption in the workplace is getting your employees on board, making small changes in their daily habits at work. If you think about it, improving a business’ profitability can be as simple as being wise with everything you spend money on, like energy.

Learner Activity WorkbookBSBITU314 Design and produce spreadsheets V1 CPC30211 Certificate III in Carpentry

Page 14 of 66

Read the following from: https://www.business.gov.au/risk-management/environmental-impact/waste-management What is waste management? Waste management is all of the activities that handle waste materials, from the time it is made to its disposal. This includes how your business collects, transports, processes, recycles or disposes its waste. Managing what you waste in your business can equate to lost opportunities or profits. Waste management is about being more efficient with raw materials and making the most of each stage of the production process. Life cycle of a product Whether you sell goods or services, everything you produce has a lifecycle. It begins with how you manufacture, distribute, use, and then reuse or recycle that product. Each stage offers the opportunity for you to think about how you use the product and if your processes can be more efficient. Why is managing waste important? The most important reason for proper waste management is to protect the environment and for the health and safety of the population. Certain types of waste can be hazardous and can pollute the environment. Bad waste management practices can also cause land and air pollution which can result in serious medical conditions in humans and animals. Implementing good waste management practices not only helps to protect the environment but can be beneficial to your business.

It can enhance your business’ reputation

Take a busy restaurant as an example. Would you go there if it had garbage piling up, or food scraps left lying around? Or would you support that same restaurant if it had a policy of donating leftover food to a charity for the homeless?

Energy efficiency

Have you thought of how you could use waste products as a combustion fuel for things such as cooking or heating?

Cost savings Managing the waste your business produces can result in valuable materials to reuse. This can save you money while potentially creating new jobs and business opportunities.

Resource recovery

Reducing, reusing and recycling your waste is important for the environment, and it can also be profitable. It decreases the amount of waste for disposal, saves space in landfills, and conserves natural resources.

Legal requirements

There are important systems that determine how to deal with various types of waste. These in turn affect groundwater and air quality.

Workplace safety

Storing and disposing your rubbish in the wrong place can be harmful to employees or customers. If you work with sharp objects, dangerous fumes and chemicals, you will need special procedures for how you dispose of them.

Read more about the National Waste Policy that sets out the direction for Australia's waste management and resource recovery.

https://www.business.gov.au/risk-management/environmental-impact/waste-management

https://environment.gov.au/protection/national-waste-policy

Learner Activity WorkbookBSBITU314 Design and produce spreadsheets V1 CPC30211 Certificate III in Carpentry

Page 15 of 66

Identify spreadsheet task requirements in relation to data entry, storage, output and presentation Spreadsheets are electronic worksheets that utilise a scientific calculator in a column and row page format. The spreadsheet calculator can produce averages, totals, percentages and may other statistical and financial calculations. It is also possible to generate many types of graphs which may be used in business and/or presentations via the spreadsheet. Read more at: https://exceljet.net/lessons/how-to-enter-data-in-excel To enter data in Excel, just select a cell and begin typing. You'll see the text appear both in the cell and in the formula bar above. To tell Excel to accept the data you've typed, press enter. The information will be entered immediately, and the cursor will move down one cell. You can also press the tab key instead of the enter key. If you press tab, the cursor will move one cell to the right once the information has been entered. When Excel sees that you are typing into a list, pressing enter at the end of the row will move the cursor down one row and back to the first column. At any time while you are typing you can press the escape key to cancel. This brings Excel back to the state it was in before you started typing. When you want to delete information that has already been entered, just select the cells, and press the delete key.

https://exceljet.net/lessons/how-to-enter-data-in-excel

Learner Activity WorkbookBSBITU314 Design and produce spreadsheets V1 CPC30211 Certificate III in Carpentry

Page 16 of 66

Learning Activities Section 1 1.1 Computers are known to present a number of issues surrounding the area of Occupational Health and Safety. These health and safety issues may include: tick all that apply  Lighting  Noise  Heat  Cables  Posture  Occupational overuse syndrome 1.2 Posture is very important when working with computers, as poor posture can lead to a number of health issues.  True  False 1.3 Some energy conservation techniques can include: identify 4 examples in the table below

Learner Activity WorkbookBSBITU314 Design and produce spreadsheets V1 CPC30211 Certificate III in Carpentry

Page 17 of 66

1.4 What is waste management?

1.5 Spreadsheets are electronic worksheets that utilise a scientific calculator in a column and row page format.  True  False

Learner Activity WorkbookBSBITU314 Design and produce spreadsheets V1 CPC30211 Certificate III in Carpentry

Page 18 of 66

1.6 Complete the table below by numbering the steps for entering data into a spreadsheet.

Number Step

When Excel sees that you are typing into a list, pressing enter at the end of the row will move the cursor down one row and back to the first column.

To enter data in Excel, just select a cell and begin typing. You'll see the text appear both in the cell and in the formula bar above.

You can also press the tab key instead of the enter key. If you press tab, the cursor will move one cell to the right once the information has been entered.

To tell Excel to accept the data you've typed, press enter. The information will be entered immediately, and the cursor will move down one cell.

At any time while you are typing you can press the escape key to cancel. This brings Excel back to the state it was in before you started typing.

When you want to delete information that has already been entered, just select the cells, and press the delete key.

Learner Activity WorkbookBSBITU314 Design and produce spreadsheets V1 CPC30211 Certificate III in Carpentry

Page 19 of 66

Section 2 – Plan spreadsheet design Ensure spreadsheet design suits purpose, audience and information requirements of task When planning to create a spreadsheet, it is important to consider the audience of the spreadsheet and any information requirements that need to be included. A good spreadsheet must have a good design and contain good data in order for it to be effective. Furthermore, the spreadsheet must adhere to the standards that are required by the company, and the spreadsheet design should include a design of the workbook as well as the areas of the worksheet. Read the following from: https://www.officearticles.com/excel/spreadsheet_design_tips_for_microsoft_excel.htm Spreadsheet Design Tips for Microsoft Excel Every well designed spreadsheet has one thing in common: The designer invested time and effort planning and designing the sheet to meet their eventual goals, and the needs of the users. In addition, the designer also made attempts to ensure that the spreadsheet would still be functioning as they intended, long after they were gone. If you want to build a solid, reusable Excel application, the following is a list of steps which you may want to consider before you start creating your spreadsheet.

1. Design the layout of the sheet so it makes sense:

 The sheet should be easy to navigate, and data entry should follow a logical flow

 Consider grouping data entry fields together, instead of making the user jump around to find them. Keep in mind that if worksheet protection is activated, the tab key will take you from one unprotected cell to the next, skipping the protected cells

2. Make it obvious where the user is supposed to enter data:

 Colour the cells where data should be entered (I prefer to use a light green shading, as I find it easy on the eyes)

 Unprotect the data entry cells

3. Name the important cells and ranges:

 Makes your formulas more readable  Makes your formulas self documenting  Saves you from having to update your VBA code every time you

manipulate your worksheet (VBA does not re-index its code when you modify your worksheet)

4. Use formulas wherever possible:

 Ensure that the user never has to do anything manually that can be done by Excel.

https://www.officearticles.com/excel/spreadsheet_design_tips_for_microsoft_excel.htm

Learner Activity WorkbookBSBITU314 Design and produce spreadsheets V1 CPC30211 Certificate III in Carpentry

Page 20 of 66

5. Apply Data Validation:  This can take many forms, but ultimately, it ensures that only valid data is entered

 Preventative data validation methods Cell level data validation ensures that only valid data can be entered directly into a cell

 Restricting values through the use of form controls (see below)

 Reactive data validation methods Cell formatting can make it obvious that something is not rendering correctly

 Formulas with messages can make it obvious that an error has occurred, or that invalid data has been entered

6. Apply protection options:  Remember that cells are protected by default, so you need to unprotect them if you want users to be able to access them once sheet level protection is turned on

 Hiding ranges or sheets can prevent users from seeing or manipulating critical information

 Hiding formulas prevents users from seeing proprietary formulas (see caveat below)

 Consider protecting all your worksheets with or without a password. I frequently protect sheets with no password, as it allows the end users the freedom to change something if absolutely necessary, but makes sure that they don't do it by accident

 If your file should only be used by authorized parties, consider implementing a workbook level password

7. Consider saving your workbook as a template:

 Best if your file is going to be used over and over again, and it is important that it always start fresh and clean every time.

 The file is available by choosing "New" from the file menu.  Opening the file by either the above method, or by double clicking

it from the Windows Explorer creates a copy of the template, so that the user cannot overwrite your file.

Learner Activity WorkbookBSBITU314 Design and produce spreadsheets V1 CPC30211 Certificate III in Carpentry

Page 21 of 66

Ensure spreadsheet design enhances readability and appearance, and meets organisational andtask requirements for style and layout Due to the fact that spreadsheets are a tool for business, one may sometimes need to work with large amounts of data. This can be an issue when presenting the information. The client who is receiving the spreadsheet, may either not have the time or the competency to read through the vast amount of information that has been presented to them. Thus, there are some methods that can make spreadsheets easier to access and read. Every company must have their own standards, which should be followed when generating spreadsheets. These standards may cover:  Company details  Logos

 The size and style of fonts  Headers and footers  Numbering format  Paper sizes  Standard margins  Saving conventions It is critical that the relevant personnel have been approached in regards to clarifying any spreadsheet requirements, and to ensure that any documents that have been created have met the purposes for which that were intended, while following the correct standards.

Learner Activity WorkbookBSBITU314 Design and produce spreadsheets V1 CPC30211 Certificate III in Carpentry

Page 22 of 66

Read the following from: https://auroramarketing.com.au/formatting/ Formatting to improve readability Importantly, not all documents should be formatted the same. And sometimes not even all documents to the same client should be formatted the same. The style of formatting of the documents should match the style of the client. If a client is delivering a landmark, iconic project and is using a visionary tone and style, then our formatting should be similarly visionary and exciting. Whereas if a client is very dry and technical, looking for a straight-forward functional solution, then our formatting should be focussed on communicating technical and functional excellence. Essentially, we need to match the document style to the specific client because what appeals to one client will repel another. And taking this concept one step further, different documents in each tender may call for different formatting. Some documents may be visionary and exciting, such as the executive summary, whereas other documents may be technical and functional, such as management plans. The style of formatting should match the style of the content. As a baseline, these documents typically include:

 A clear heading structure with a clear hierarchy of content  An easy-to-read font, in 10 or 11 point for body text and maybe a little smaller in tables  Good line spacing, ideally about 1.2 line spacing and extra spacing above and below paragraphs  Spacious margins, ideally 2 cms left, right and bottom, and 4 cms at top depending on header  Appropriate use of dot points and numbered lists

 Appropriate use of tables, graphics and photos  In-text bolding to emphasise key words and phrases  Pull out quotes to reiterate key messages.

https://auroramarketing.com.au/formatting/

Learner Activity WorkbookBSBITU314 Design and produce spreadsheets V1 CPC30211 Certificate III in Carpentry

Page 23 of 66

Read the following from: https://auroramarketing.com.au/formatting/ Reading Styles Not everyone reads in the same way. In fact, there are four types of readers:

A scanner Reads a document searching for specific information. They use quick eye movements, not necessarily in a linear fashion, with their eyes wandering until they find the information they are looking for. They know precisely what they are looking for – such as a name, date, symbol, formula or phrase – and know when they have found it. Very little information is processed into long-term memory or even for immediate understanding because the objective is simply finding the specific information.

A skimmer Reads a document to get an overview of the topic. They read headings, introductions, first paragraphs of each section and summary sections, and they look at pictures, diagrams, tables, and phrases in bold or pullouts. Once they have their overview, they read more deeply in certain sections, possibly until they have read the full document. They do not necessarily read from start to finish.

Intensive reading

Means reading the whole document and looking ‘inside’ the content. This type of reader notes linguistic or semantic details, identifies key vocabulary, makes notes and draws their own diagrams. They read the document carefully and thoroughly, again and again.

Extensive reading

Goes beyond the single document and reads extensively for enhanced understanding. This type of reader enjoys the learning and discovery of the topic and goes beyond with their own additional reading and research. They read the content in depth, stop to clarify what they don’t understand, do further research to clarify items, discuss with others what they have read, and do their own thorough note taking and summarising of content. This style of reading is very effective, but very time consuming.

https://auroramarketing.com.au/formatting/

Learner Activity WorkbookBSBITU314 Design and produce spreadsheets V1 CPC30211 Certificate III in Carpentry

Page 24 of 66

Read the following from: http://www.cse.unsw.edu.au/~en1811/06s1/labs/wk4/excel-basics.html Formatting In spreadsheets, formatting means not only the layout, colours, font, typeface and size of characters, etc. but also the way data are presented on the worksheet. Although data are always represented (encoded) in the same way inside Excel, the same representation can be displayed on the worksheet in different forms. For example, the number 75 could be just that, a plain number; or it could be a number of dollars $75.00; or it could be a percentage 75%, and so on. Use style sheets and automatic functions to ensure consistency of design and layout Style Sheets Many companies have a style sheet, which is a document that is used as a guide for when documents are being created. There are many different types of operations and functions that occur automatically, when using spreadsheet software. These operations and functions happen in order to make the process of producing documents easier. Some of these functions include:  Auto date  Auto correct

 Auto format  Auto text  Default settings  Headers and footers

http://www.cse.unsw.edu.au/~en1811/06s1/labs/wk4/excel-basics.html

Learner Activity WorkbookBSBITU314 Design and produce spreadsheets V1 CPC30211 Certificate III in Carpentry

Page 25 of 66

Learning Activities Section 2 2.1 When planning to create a spreadsheet, it is important to consider the audience of the spreadsheet and any information requirements that need to be included. A good spreadsheet must have a good design and contain good data in order for it to be effective.  True  False 2.2 Not everyone reads in the same way. In fact, there are four types of readers: What are they?

2.3 There are many different types of operations and functions that occur automatically, when using spreadsheet software. Some of these functions could include: tick all that apply  Auto date  Auto correct  Auto format  Auto text  Default settings  Headers and footers

Learner Activity WorkbookBSBITU314 Design and produce spreadsheets V1 CPC30211 Certificate III in Carpentry

Page 26 of 66

Section 3 – Create spreadsheet Ensure data is entered, checked and amended to maintain consistency of design and layout, in accordance with organisational and task requirements The process of entering data into a spreadsheet/database is called data entry, and can be performed by either a person or a computer. Data includes any information that has been collated for referencing/analytical purposes. In order to perform computerised data entry, once can use programs such as Excel spreadsheet, which can be done via the Microsoft Excel program. The following examples of data entry will be performed via Microsoft Excel 2013. Any current versions of the program may have some slight differences. Upon opening a blank spreadsheet, this is what it will look like (if not similar). When you open your blank spreadsheet it will look similar to the one on the following page.

Learner Activity WorkbookBSBITU314 Design and produce spreadsheets V1 CPC30211 Certificate III in Carpentry

Page 27 of 66

Learner Activity Workbook BSBITU314 Design and produce spreadsheets V1 CPC30211 Certificate III in Carpentry

Page 28 of 66

Creating a table The process for inserting a table is as follows:

 Select INSERT on the toolbar at the top of the page.

 Then select TABLE, which is located just below (when prompted, select mytable has headers)

There will now be a table that is one cell wide, and two cells deep. This will be to small for the demonstration, so the table will need to be made bigger. To make a 6 x 8 table, the small arrow in the bottom left corner needs to be dragged out to the size that is needed.

Learner Activity Workbook BSBITU314 Design and produce spreadsheets V1 CPC30211 Certificate III in Carpentry

Page 29 of 66

Creating a data spreadsheet To create a detailed spreadsheet of the financial activities of a construction company, one will need to use formulae, functions, formatting and data entry, among other aspects of Microsoft Excel. How to begin It is important to start with a brand new spreadsheet. One should explore the different menus and menu items.

Learner Activity Workbook BSBITU314 Design and produce spreadsheets V1 CPC30211 Certificate III in Carpentry

Page 30 of 66

Data to be entered Employees: John ($19 p/h, 20 hours), Jeff ($19 p/h, 15 hours), Cathy (22 p/h, 10 hours), Aaron ($22 p/h 38 hours), Rebecca ($15 p/h, 20 hours). Don’t type in the dollar sign. Process To start, name your worksheet “Construction site” by right clicking the "Sheet1" label at the bottom left of your screen and choosing rename. Type in “Construction site” and press enter. Sizing columns/rows In order to have excel recognise the data in column B as currency, B2 to B7 needs to be selected (by clicking on B2 and dragging down to B7, and then releasing the left button on the mouse). On the home tab, in the number section, select the dollar symbol. It will then put a dollar sign in front of the numbers, recognising them as currency.

Learner Activity Workbook BSBITU314 Design and produce spreadsheets V1 CPC30211 Certificate III in Carpentry

Page 31 of 66

Format spreadsheet using software functions to adjust page and cell layout to meet information requirements, in accordance with organisational style and presentation requirements When one is formatting, they are arranging the data, size, colours etc. of a document for output process. This is what the formatting process is, and therefore may include the alteration of:

 The alignment on the page.

 The efficiency of the formulae.

 Any enhancements to format – borders, patterns and colours.

 Any enhancements to text.

 Headers/footers.

 The use of absolute and relative cell addresses.

 The use of cell addresses in formulae.

Adding/deleting columns/rows If any rows or columns need to be added to a table and/or spreadsheet, one must right-click on the area that contains the column letter or the row number. Then, the action that one would like to complete needs to be selected.

Learner Activity Workbook BSBITU314 Design and produce spreadsheets V1 CPC30211 Certificate III in Carpentry

Page 32 of 66

Formatting cells Cell formats allow you to change the way cell data appears in the spreadsheet. It is vital to keep in mind that it only alters the way the data is presented, and does not make any alterations to the data itself. Previously, the use of a currency button was demonstrated. To access other formatting options for cells, right click on the cell, or highlight and right click on the cells that one wants to format, and select the Format Cells option from the dropdown menu.

Learner Activity Workbook BSBITU314 Design and produce spreadsheets V1 CPC30211 Certificate III in Carpentry

Page 33 of 66

Formatting text It is possible to format the text of the spreadsheet in order to alter the way it looks, or to make it easier to follow. There are pre-set themes that have predefined font families, colour schemes and text fill effects, and they can be applied to the whole spreadsheet. Headers/footers Headers and footers are the lines of text that are printed at the top (the header) of the bottom (the footer) of each page in a spreadsheet. The headers and footers contain descriptive text, including titles, dates and/or page numbers. They can be used in order to add information to the spreadsheet that is being printed.

Learner Activity Workbook BSBITU314 Design and produce spreadsheets V1 CPC30211 Certificate III in Carpentry

Page 34 of 66

Formatting the document If the spreadsheet isn’t aesthetically pleasing, this can be changed easily in excel. However, the audience of the document must be considered first, as it is important to determine whether the spreadsheet is being viewed on screen or on paper. This is must be considered as this will highly affect the layout choices of the document. If the document is intended to be viewed on screen, the following options are considered to be good elements of design:  Aligning the text in the cells to the left, and the numbers to the right  Using restraint with colours, as they can overpower the spreadsheet

 Zebra striping – which is having a light background colour on every second line. This will make it easier to follow data across a row

 Allowing plenty of space to surround the data on the spreadsheet. This allows the document to look less cluttered.

Apply conditional formatting based on text in a cell Conditional formatting is useful for highlighting text that one may want to stand out on a spreadsheet that is large and has many repeated entries. Some things that one may want to stand out include:  Company names  The names of important people  Important data Quick formatting For this demonstration, conditional formatting will be used to highlight ‘Timber framing’ (which, for the purposes of the example, will be a word that occurs frequently throughout the document) with a green background and dark green text. By doing this, the word will stand out from the other text, which will highlight what the focus for the audience is. To apply conditional formatting, select the first cell in the range, and then drag to the last cell. On the home ribbon, select Conditional Formatting > Highlight Cells Rules > Select the cells (i.e. part of the spreadsheet) that you want to apply conditional formatting to by clicking the first cell in the range, and then drag to the last cell. On the Home ribbon, click Conditional Formatting > Highlight Cells Rules > Text that Contains. In the Text that Contains box, on the left, enter the text you want highlighted. On the right, select the colour format for the text, and then click OK.

Learner Activity Workbook BSBITU314 Design and produce spreadsheets V1 CPC30211 Certificate III in Carpentry

Page 35 of 66

Advanced formatting Conditional formatting is also great for highlighting specific information in your spreadsheet:

 Data between a certain range of numbers or greater or less than a nominated value

 Data that is above or below average or in the top or bottom 10%

Relative & absolute cell references Excel utilises two forms of cell references to create formulas, and relative cell references is one of them. Both of the formulas have their own purpose, and can be used in different situations. Relative cell references Relative cell reference is the most commonly used type of formula for excel spreadsheets. It is based on cell references which adjust and change when copied, or when using autofill. Example: =SUM(B2:B9), as shown below, changes to =SUM(C2:C9) when copied across to the next cell using autofill (covered earlier).

Learner Activity Workbook BSBITU314 Design and produce spreadsheets V1 CPC30211 Certificate III in Carpentry

Page 36 of 66

Absolute cell references In some cases, cell reference must remain the same when copied or when using autofill. Dollar signs are used to hold a column and/or a reference constant. This could occur in the instance where one wishes to make sure that a cell doesn’t change if it is being copied to other cells using autofill. Example: Typically, construction workers and those who work on site are not paid based on commissions, as it is not applicable to the type of work that they do. However, for the purpose of this example, commission for sales will be used. When calculating commissions for sales staff, you would not want cell B10 to change when copying the formula down from the first salesperson since they are all being paid at the rate set down in cell B10 – 10%. You want both the column and the row to remain the same to refer to that exact cell. A common use of this type of reference is when you are referring to a price or percentage rate in a list to do your calculations. Note - by using $B$10 in the formula, neither changes when copied.

Using advanced formula A powerful feature of spreadsheets is their ability to perform a wide range of calculations. Excel’s enormous range of mathematical functions is quite easy to use. These range from simple totals and averages, to very complex probability, financial and engineering formula.

Learner Activity Workbook BSBITU314 Design and produce spreadsheets V1 CPC30211 Certificate III in Carpentry

Page 37 of 66

Ensure formulae are tested and used to confirm output meets task requirements, in consultation with appropriate personnel as required One of the primary purposes of a spreadsheet is to automate calculations, which is when the cells are used to perform and calculate operations based on the values that are in the other cells. Each time a change is made to the value of a cell, the spreadsheet recalculates the other values in the other cells. A formula is used to represent an expression, which, one entered into the cell, allows the spreadsheet to perform automatic calculations based on values in other cells and to display the result. Errors in your formula In some cases, there may be time where you type in a formula that won't work or Excel may alert you that there is an error in the formula. For instance, rather than typing =SUM(F1,F@), the cell will display this: #NAME? Totalling wages Add a fourth column to the spreadsheet called “Total Wages”, this column will total up each staff member wages and will give a grand total of all the wages in the construction site. First, add the heading for “Total Wages” and adjust the size of the column to fit the text.

Learner Activity Workbook BSBITU314 Design and produce spreadsheets V1 CPC30211 Certificate III in Carpentry

Page 38 of 66

Next, calculate the total weekly wages by multiplying column B with Column C. This can be done in 4 ways:

 In cell D2 you can type  =(B2*C2) - *symbol means multiply ( X ), or  =PRODUCT(B2,C2), or  =PRODUCT(B2:C2) Then, type in a description of the function that is desired, in this case one can type "multiply" or “product” in the search box at the top (it will then search for functions that can do that) and select the appropriate option. Click the OK button. The following screen will appear. Note that Excel has already guessed that it is intended to multiply cells B2 and C2. If Excel hadn’t selected the correct cells, one can type the cells or cell range one wants.

Learner Activity Workbook BSBITU314 Design and produce spreadsheets V1 CPC30211 Certificate III in Carpentry

Page 39 of 66

Often one will find that it is a long and tedious process to type out the same equation over and over. The following shortcut will apply the same equation but to the row below (so instead of being =PRODUCT(B2,C2) the new equation will read =PRODUCT(B3,C3) and so on). To do this, select the cell that has the equation one wishes to copy (in our case D2). Then, use the cursor to grab the small box in the bottom right corner of the cell. Like so: Drag the cursor down to D6 and release the mouse button, and Excel will auto fill the remaining cells (Click on each cell, and you will see a different version of the same equation in each of the cells displayed in the formula bar).

Learner Activity Workbook BSBITU314 Design and produce spreadsheets V1 CPC30211 Certificate III in Carpentry

Page 40 of 66

The next step will be to total the “Total Wages” column. This can be done in a number of different ways: Select cell range D2:D6 by clicking and dragging across them with your mouse. Click the Autosum button (not the pull down arrow). Excel will enter the formula automatically in the cell below your selection (D7). The sheet will need to be totalled as was previously done. Now total the Extra Site Expenses in cell B9 using Autosum as done previously. The new Sheet should look like this:

Learner Activity Workbook BSBITU314 Design and produce spreadsheets V1 CPC30211 Certificate III in Carpentry

Page 41 of 66

Next, add the total hours to cell B11 and the total wages to cell B 13 from the other 2 sheets created earlier. To do this, one must:

 Select the first cell under Total hours and enter =SUM(  Next click the Construction site tab *The formula bar will look like this: Next, highlight C2, C3, C4, C5 and C6 from the Construction site sheet Now type , Next select the Admin staff tab Next highlight C2, C3, C4, C5 and C6 from the Admin staff sheet Now type a close bracket symbol ) Now press the Enter key This will finish the equation and give you the total. The final equation will look like this: Now complete the same process but for the Total wages column in the Admin and Construction tabs (ensure you highlight D2:D6 in both tabs). Use manuals, user documentation and online help to overcome problems with spreadsheet design and production There is a multitude of resources that can be utilised for obtaining assistance with creating and editing spreadsheets. The easiest to follow are the in-client help documents, which can be located by accessing the help button – this is in the file tab on the excel toolbar. These documents provide a helpful outline as to how to utilise the spreadsheet program, and also provides troubleshooting solutions when necessary. Alternatively, a better option in some cases can be to seek assistance via the internet as there are many different sites that can provide the desired information, especially with very specific issues that can be encountered whilst developing spreadsheets.

Learner Activity Workbook BSBITU314 Design and produce spreadsheets V1 CPC30211 Certificate III in Carpentry

Page 42 of 66

Learning Activities Section 3 3.1 The process of entering data into a spreadsheet/database is called data entry, and can be performed by either a person or a computer.  True  False 3.2 In order to perform computerised data entry, once can use programs such as Excel spreadsheet, which can be done via the Microsoft Excel program.  True  False 3.3 When one is formatting, they are arranging the data, size, colours etc. of a document for output process. This is what the formatting process is, and therefore may include the alteration of: tick all that apply  The alignment on the page.  The efficiency of the formulae.  Any enhancements to format – borders, patterns and colours.  Any enhancements to text.  Headers/footers.  The use of absolute and relative cell addresses.  The use of cell addresses in formulae. 3.4 Conditional formatting is useful, for what?

Learner Activity Workbook BSBITU314 Design and produce spreadsheets V1 CPC30211 Certificate III in Carpentry

Page 43 of 66

3.5 There is a multitude of resources that can be utilised for obtaining assistance with creating and editing spreadsheets. The easiest to follow are the in-client help documents, which can be located by accessing the help button – this is in the file tab on the excel toolbar.  True  False 3.6 In some cases, there may be time where you type in a formula that won't work or Excel may alert you that there is an error in the formula. For instance, rather than typing =SUM(F1,F@), the cell will display this: #NAME?  True  False

Learner Activity Workbook BSBITU314 Design and produce spreadsheets V1 CPC30211 Certificate III in Carpentry

Page 44 of 66

Section 4 – Produce simple charts Select chart type and design that enables valid representation of numerical data, and meets organisational and task requirements A very helpful feature of electronic spreadsheets is the ability to convert numerical data into charts and graphs. This is typically done in order to present numerical data in a more visual way. Commonly used charts/graphs for numerical data are:

 Area charts  Column/bar charts  Histograms  Line/curve graphs  Pie charts  Scatterplots Area chart/graph An area chart is used to display numerical data over time (or any other dimension) as well as cumulative totals and how each element represents part of the whole. Column/bar chart The column chart is a great tool for presenting data over a nominal or interval scale. Its best use though is for comparisons of a few different elements over a scale, with each element represented separately from the others.

Learner Activity Workbook BSBITU314 Design and produce spreadsheets V1 CPC30211 Certificate III in Carpentry

Page 45 of 66

Histogram Histograms are the perfect charts for creating large comparisons between multiple elements. Each element will have its own column representing the value that it has in the current chart. Line/curve Line charts are used for multiple purposes such as:  To display long data rows  To interpolate between data points  To extrapolate beyond known data values (forecast)  To compare different graphs

 To find and compare trends (changes over time)  To recognize correlations and co-variations between variables

Pie Pie charts are widely used many different fields of business, including the construction industry. The pie chart is best for conveying approximate relations at singular points in time. However, it must be known that the pie chart can never represent a value beyond 100%. It should also be noted that it is harder for people to estimate angles, so exact comparisons aren’t a good use.

Learner Activity Workbook BSBITU314 Design and produce spreadsheets V1 CPC30211 Certificate III in Carpentry

Page 46 of 66

Scatterplot Scatterplots are graphs that represent values as points in the graph where data resides. There are 2 variations of scatterplots, one dimensional and two dimensional plots. One-dimensional scatterplot: Data point are drawn above a baseline (as in column/bar charts). Here the data points are not connected but remain isolated data points. Two-dimensional scatter plot: Shows correlation between two data sets. This chart type has two dependent variables: One is plotted along the X axis, the other along the Y axis; the independent variable is the intersection of both dependent variables, realized as a data point in the diagram.

Create charts using appropriate data range in spreadsheet After the correct chart has been chosen, select the data that needs to be turned into a input into that particular type of chart/graph. It's really important to select the correct range for the data. To create a chart, select at least one cell in a range of data (a set of cells). Do one of the following:  If the chart data is in a continuous range of cells, select all the cells in that range. The chart will

include all the data in the range

 If the data isn't in a continuous range, one may need to use Excel's hide feature to hide columns or rows that isn’t to be included

Learner Activity Workbook BSBITU314 Design and produce spreadsheets V1 CPC30211 Certificate III in Carpentry

Page 47 of 66

Arrange data for charts Excel can recommend charts for use. The charts it suggests depend on the data is arranged in the worksheet. One may also have some choices of charts in mind beforehand. Either way, this table lists the best ways to arrange the data for a given chart.

FOR THIS CHART ARRANGE THE DATA

Column, bar, line, area, surface, or radar chart

In columns or rows, like this:

Pie chart

This chart uses one set of values (called a data series).

In one column or row, and one column or row of labels, like this:

Doughnut chart

This chart can use one or more data series

In multiple columns or rows of data, and one column or row of labels, like this:

XY (scatter) or bubble chart In columns, placing your x values in the first column and your y values and bubble sizes in the next two columns, like this:

Learner Activity Workbook BSBITU314 Design and produce spreadsheets V1 CPC30211 Certificate III in Carpentry

Page 48 of 66

Creating charts Knowing what the right chart to use though, is made easier by following organisational guidelines as well as selecting the right chart for the data that is being presented. Now we will make a Bar Graph representing the data on the spreadsheet. To do this one needs to:  Highlight the data is needed in the graph (for this chart it will be the titles Day and Extra site

expenses and the raw data (weekdays and figures), not the totals  Click INSERT to display the Insert tab  Click the Recommended Charts button to display the graph options dialog box (see below)  Click the graph that best represent the data that is being used. In this case, the clustered

column chart was chosen.

Modify chart type and layout using formatting features Once the chart has been inserted into the spreadsheet you can now format the layout and even change the type of chart that it is by right clicking on the chart and the appropriate section of the drop down box.

Learner Activity Workbook BSBITU314 Design and produce spreadsheets V1 CPC30211 Certificate III in Carpentry

Page 49 of 66

To switch row and column data Sometimes, one may want to change the way charts group the data. For example, in the chart below, the extra site expenses data are grouped by day, with columns for each day of the week. However, it is possible switch the rows and columns so the chart will group the data by day, with columns for each daily costing. In both cases, the chart contains the same data—it's just organized differently. Let's look at how to do this.

 Select the chart that is to be modified.

 From the Design tab, select the Switch Row/Column command.

The rows and columns will be switched. In this example, the data is now grouped by cost, with columns for each day.

Learner Activity Workbook BSBITU314 Design and produce spreadsheets V1 CPC30211 Certificate III in Carpentry

Page 50 of 66

To change the chart type If the data isn’t found to be well suited to a certain chart, it's easy to switch to a new chart type. In this example, the column chart will be changed to a line graph. From the Design tab, click the Change Chart Type command. The Change Chart Type dialog box will appear. Select a new chart type and layout, then click OK. In this example, we'll choose a Line chart. The selected chart type will appear. In this example, the Line chart makes it easier to see trends in the expenses over the week.

Learner Activity Workbook BSBITU314 Design and produce spreadsheets V1 CPC30211 Certificate III in Carpentry

Page 51 of 66

To move a chart Whenever a new chart is inserted, it will appear as an object on the same worksheet that contains its source data. Alternatively, the chart can be moved to a new worksheet to help keep the data organised.  Select the chart that is to be moved.

 Click the Design tab, then select the Move Chart command. The Move Chart dialog box will appear. Select the desired location for the chart. In this example, it will be moved to a new sheet which will create a new worksheet. Click OK.

Learner Activity Workbook BSBITU314 Design and produce spreadsheets V1 CPC30211 Certificate III in Carpentry

Page 52 of 66

Learning Activities Section 4 4.1 Commonly used charts/graphs for numerical data are: list 6 examples

4.2 Once the chart has been inserted into the spreadsheet what happens next?

Learner Activity Workbook BSBITU314 Design and produce spreadsheets V1 CPC30211 Certificate III in Carpentry

Page 53 of 66

4.3 Explain how to switch row and column data in excel

Learner Activity Workbook BSBITU314 Design and produce spreadsheets V1 CPC30211 Certificate III in Carpentry

Page 54 of 66

Section 5 – Finalise spreadsheets

Preview, adjust and print spreadsheet and any accompanying charts, in accordance with task requirements

When a spreadsheet has been completed, it will need to be previewed and checked over to see if there are any mistakes that may have missed. This is an important step as the work needs to look professional, so the extra care taken when finalising the spreadsheet will help this.

Ensure data input meets designated timelines and organisational requirements for speed and accuracy Almost all of the tasks that will be required, will have set timelines in which the work must be completed.

These designated timelines are there to ensure that the maximum amount of work is completed in the allotted time. Designated timelines may include:

 Organisational timeline e.g. Financial requirements  Timeline agreed with internal/external client  Timeline agreed with supervisor/person requiring spreadsheet

Name and store spreadsheet in accordance with organisational requirements and exit application without data loss/damage Once the work has fully been completed, one must save their work to the required format as defined by the organisational guidelines of the company that the spreadsheet is being made for and/or by.

Information about how to store spreadsheets can include:

 Authorised access  File naming conventions  Filing locations  Organisational policy for backing up files  Organisational policy for filing hard copies of spreadsheets  Security

 Storage in electronic folders/sub-folders  Storage on CD-ROM, USB, tape back-up, server

Learner Activity Workbook BSBITU314 Design and produce spreadsheets V1 CPC30211 Certificate III in Carpentry

Page 55 of 66

Learning Activities Section 5 5.1 When a spreadsheet has been completed, it will need to be previewed and checked over to see if there are any mistakes that may have missed.  True  False 5.2 Designated timelines may include: list 3 examples below

5.3 Information about how to store spreadsheets could include: tick all that apply  Authorised access  File naming conventions  Filing locations  Organisational policy for backing up files  Organisational policy for filing hard copies of spreadsheets  Security  Storage in electronic folders/sub-folders  Storage on CD-ROM, USB, tape back-up, server

Learner Activity Workbook BSBITU314 Design and produce spreadsheets V1 CPC30211 Certificate III in Carpentry

Page 56 of 66