DEVELOP KEYBOARD SKILLS & CREATE AND USE SPREADSHEETS

1. DEVELOP KEYBOARD SKILLS & CREATE AND USE SPREADSHEETS BSBITU102 & BSBITU202

2. DIRECTORY • Preparing to use a spreadsheet Slide 4 • Using spreadsheets Slide 5 • Ergonomics Slide 7 • Your workspace Slide 9 • Energy and resource saving techniques Slide 11 • Conserving resources Slide 12 • Legislation Slide 13 • Developing keyboarding skills Slide 14 • Identifying spreadsheet task requirements Slide 23 • Spreadsheet terminology Slide 25 • Shortcut terminology CTRL key Slide 26 • Shortcut terminology F (Function) key Slide 27 • Spreadsheet Slide 28 • Layout of a worksheet Slide 29 • Moving around a worksheet Slide 30 • Ribbon functions Slide 31 • Designing a spreadsheet slide 32

3. DIRECTORY • Spreadsheet functions Slide 33 • Presentation Slide 37 • Saving your worksheet Slide 38 • Formatting Slide 39 • Entering data into a worksheet Slide 42 • Sorting data in a worksheet Slide 47 • Checking for accuracy Slide 48 • Alignment and orientation Slide 49 • Formula Slide 50 • Selecting multiple cells, columns and rows Slide 53 • Cell reference Slide 54 • Relative and absolute cell reference Slide 55 • Overcoming problems with design and production Slide 56 • User manuals and training manuals Slide 57 • Charts Slide 58 • Finalising spreadsheets Slide 68 • Protecting your worksheet slide 77

4. Preparing to use a spreadsheet A spreadsheet is a very useful computer application that makes it easy to carry out repetitive tasks involving numbers. Organisations use spreadsheets to store and calculate numerical data such as financial statements or product pricing information. Setting up a spreadsheet is a simple form of computer programming. • Ergonomics - When you are working at a computer for extended periods each day, your workstation must be comfortable so that you can carry out your tasks efficiently. You need to understand the guidelines relating to workplace safety, your work should be organised to ensure your are not doing the same task for long periods of time. • Energy & Resource Saving Techniques – Computer equipment, printers and photocopy machines use a lot of energy and consume a lot of paper. There are simple techniques that you can follow to save or conserve energy and other resources. Most businesses require staff to follow resource saving procedures you need to be aware of any conservation practices made by your workplace. • Task Requirements – Organisations will have specific requirements regarding how data is stored and the way in which it is displayed and presented. You must be aware of these requirements so that your spreadsheet meets your organisational standards. These requirements can relate to how the data is entered, stored, presented and produced. Once the data has been entered you will need to format it to suit your organisation’s style and presentation requirements. A spreadsheet is easier to read when it has been formatted. • Task requirements may also be specified by your supervisor or the person requesting the information. It is important to follow instructions, as well as clarify requirements. Part of clarifying is to ask questions, you may also check information and presentation of previous spreadsheets as well as presenting a draft prior to completion.

5. USING SPREADSHEETS • In accounting a spreadsheet is a large sheet of paper that displays the financial position of a company. It spreads or shows all financial information, such as costs, income and taxes on a single sheet so that decisions can be made by management. An electronic spreadsheet organises information into columns and rows. The data can then be manipulated by a formula to provide a total (sum). The spreadsheet can present the information in a format to help a decision-maker see the financial “big picture” of an organisation. • Storage – Where you store your spreadsheet depends on who needs the information. When you create a spreadsheet you need to save it immediately. You can save it to your personal hard drive or to a shard drive on your organisations server. A server is a computer that delivers information to other computers linked by a network. You supervisor/manager will advise you where to save your spreadsheet to ensure you are abiding by company protocols. • Speed and Accuracy – When producing documents it is important that you undertake your tasks in a timely and efficient manner. Keeping to timelines is one aspect, the other is using the software appropriately and efficiently to save time and ensure accuracy of data. The benchmark speed for keyboarding will vary depending on the organisation and the skill level of the learner. 40 words per minute is an acceptable benchmark speed, but for a level one learner 30 words per minute is acceptable.

6. USING SPREADSHEETS • Timelines – You will often be required to meet specific deadlines. Your supervisor may tell you what the deadline is for completing set tasks (for example), prepare a draft summary report by 2.00pm, or you may have regular deadlines for common tasks, such as preparing a monthly report. It is important for the efficient running of the business to adhere to these timelines. If for some reason a deadline may not be met, or you have conflicting work priorities, discuss this as soon as possible with your supervisor, do not leave it until the last minute. Your supervisor may be able to assist you by providing suggestions on how this deadline may be met or they may be able to adjust the deadline. Use a diary system and a to-do list to help prioritise your tasks and be aware of timelines required for set tasks. • Shortcuts – the shortcut menu (right click mouse button) will bring up quick commands for undertaking actions. You have also been provided with a list of shortcuts to enable you to undertake actions quicker. Familiarise yourself with the tools available in the ribbons and the groups of tools under each tab. If you feel these should be an easier way of performing a task search the Excel Help facility for assistance. • Although in Excel it is mainly entry of numbers, learning to improve your typing speed and accuracy can help. Learning to touch type may be a way to improve your typing speed. There are lessons you can access on the Web. Go to the following websites to practice touch typing: • http://www.typeonline.co.uk/ or https://www.speedtypingonline.com/typing-tutor

7. ERGONOMICS • Ergonomics is the study of the relationship between people and the tools of their occupation. In particular, ergonomics focuses on the physical interface between the worker and the way he or she uses the tools of their job. A tool such as a computer workstation is said to have good ergonomic design when it can be easily adjusted to fit the user. With good ergonomics, the user does not have to contort their body or perform repetitive movements in ways that could cause discomfort, strain or injury. • Due to the large emphasis on the use of technology in the workplace, one of the most common hazards is the use of the computer. Sitting in front of the computer, looking at the screen and using the keyboard for extended periods of time can result in back pain, sore wrists and hands, stiff neck and shoulders and eyestrain. • This discomfort can be minimised with a well designed and correctly adjusted workstation that supports correct posture. An understanding of office ergonomics principles, good workplace layout and managing tasks to incorporate regular postural breaks is also important. • Your telephone, computer, keypad and files should be in easy reach. Good ergonomics is not having to turn or stretch or contort your body to reach anything. If you continually have to reach or twist and turn whilst you are working you will strain muscles causing discomfort and pain.

8. Ergonomics • Ensure that your workspace is set up to suit you. There is also a range of equipment that can also assist you to ensure your health and safety whilst at the computer. • Ergonomics applies to: • Posture • Prevention of fatigue and injury • Comfort and safety • Environmental factors, such as lighting and noise • Job, workstation and equipment design • Ergonomically-sound work practices and equipment will help employees to: • Be more efficient • Reduce or eliminate work-related injury • Increase job satisfaction

9. Your Workspace Display Monitor: Should be positioned so the distance from the eye to the screen can be adjusted. The centre of the screen should be angled at 15 to 25 degrees below eye level. This will alleviate neck and shoulder pain and strain on your eyes. It should also be tiltable. Chair:Should be easily adjusted, have back support and be fitted with castors (preferable five) Posture: Should be as shown with right angles at the elbow, hip and knee. Your head should be held in a neutral position facing straight ahead with the eyes gazing forward or slightly down. Footrest: This may be needed if your feet do not comfortably reach the floor. Your feet should reach and touch the floor in a flat, relaxed manner Support Items: Support items that can be used to assist you with following the WHS objectives are: Keyboard support tableDocument HoldersAnti Glare Screens Office lighting and room temperature should be comfortable to all.

10. YOUR WORKSPACE • Quick tips to assist you when working at the computer • Your chair should be adjustable and your feet should be flat on the floor. Use a foot rest if they do not reach • When sitting at your desk, sit up straight and avoid leaning forward. • Your thighs should be horizontal with a 90o to 110o angle at the hip • Elbows should be bent at a 90o angle, forearms horizontal with the desk and wrists should be straight. • Monitors should be placed to the side of any light source. Anti-glare screens may be attached to the side of your monitor • Another way that glare may be reduced is by tilting your screen forward • The height of the top of the screen should be just below eye level, this will alleviate shoulder and neck strain • The mouse should be kept as close as possible to the keyboard • Items that are used regularly should be within comfortable reach of both hands to avoid unnecessary twisting and turning. • To avoid sitting for long periods of time, every 30 minutes or so stand up and walk around. Take a break and don’t eat at your desk. • If you use a laptop for a long period of time request a separate keyboard and mouse to alleviate strain on your wrists • Further information – Office Ergonomics • Practice Task 1. • You are to download and complete the checklist on this link and provide a written copy to your trainer.

11. ENERGY AND RESOURCE SAVING TECHNIQUES The overall objective of organisational policies and statutory requirements is to reduce the harmful health and environmental impacts of waste. In order to meet this objective, it is particularly important to: • Reduce the amount of office waste • Encourage staff, suppliers & contractors to minimise the volume of packaging used & recover and recycle packaging where practicable. • Process all waste in accordance with legal requirements and best practice • Provide facilities to recycle waste paper, cardboard, plastic, glass, toner cartridges and cans • Manage heating and air conditioning across the organisation to ensure the most efficient use of energy • Monitor data on energy and resource consumption in all areas • Encourage staff to save energy through activities to raise awareness in the workplace and art home • Promote a “Lights Out Policy” to ensure lights are switched off overnight and when not needed • Monitor, record and audit water consumption in buildings wherever possible • Use water-efficient appliances and equipment • Install and maintain flow restricting devices and more efficient technologies where it is practicable to do so • Ensure all leaks and faults are dealt with in a reasonable time frame

12. Conserving Resources • This is to use resources in such ways to provide the most efficient, economic and environmentally sustainable use. Conserving resources in a green office by using the three “R’s” • OTHER WAYS TO HELP CONSERVE RESOURCES IN THE WORKPLACE ARE: • Switch off power and lights when not in use • Set equipment to power saving mode • Reuse paper for rough drafts, notepaper • Preview documents before printing • Double side print wherever necessary • Use a paper recycling bin for all waste paper • Install power saving light bulbs • Bring your own cup to work • Turn computers, monitors, printers and copiers off at night when not in use. • Keep air conditioning vents clear of blockages and free of dust • Close the door when air conditioning is on • Complete Practice Task 2

13. Legislation • Over 300 people will injure themselves in the workplace on any average day in Australia. This is why it is important to have a good knowledge of the WHS Policies and Procedures that are in place in your organisation. You are usually informed of these policies and procedures when your induction is carried out on your first day of employment. If you have not been advised of the policies and procedures you should ask your supervisor or manager about this. • To protect the health and safety and welfare of all workers, management and employees must abide by the relevant legislations. These are just a few listed below. • The legislation that regulates safety in the workplace is The Workplace & Health & Safety (WHS) Act • The Privacy Act 1988 ensures that information and data your organisation maintains about its customers is protected. • The Anti Discrimination Act is designed to promote equality of opportunity for everyone by protecting them from unfair discrimination in certain areas of activity and from sexual harassment and certain associated objectionable conduct • The Equal Opportunity Act is designed to ensure that every person can participate freely and equally in areas of public life, such as in the workplace. This legislation is governed by the Anti Discrimination Act. • The Manual Handling Code of Practice provides advice on assessing risks associated with general work station activities. • Complete Practice task 3

14. DEVELOPING KEYBOARDING SKILLS • In a work environment you will be required to perform various tasks, if your workplace is an office environment it will be necessary to use a computer and have some keyboarding skills. Each software application has built-in tools, such as spell check and grammar check to ensure accuracy. However, your workplace will want the best possible outcomes according to your level of responsibility. Workplace requirements might have set procedures for formatting etc. Keyboarding techniques will improve with practice and might only need an initial training course. Learning how to touch type can assist in developing your speed and accuracy. Identify and apply keyboard functions for both alpha and numeric keyboard functions. • The Alphanumeric Keyboard • The alphanumeric keyboard QWERTY Keyboard (shown below) is common to virtually all typewriters and computer keyboards. It is named after the first six keys on the top row of letters.

15. DEVELOPING KEYBOARDING SKILLS The Keyboard • A keyboard is used to enter text (type) on the screen, usually in a program of some sort. If you are using a word processing program you will see a flashing vertical bar (cursor), this indicates where the text will be inserted. As you type text will form in a line across the page, it will continue to do so and automatically go to the next line until the enter key is pressed on the keyboard. • There can be some variation between computers in the keys located around the edge of the keyboard (such as fraction keys, brackets, etc) but the numbers and letters of the alphabet are always located in the same positions. • The letters of the alphabet are located on three long rows in the centre of the keyboard. • The numbers are located in a single row above the letters. They share their position on the keyboard with symbol and sign keys. Most Keyboards also have a number pad which can be turned on and off using the Num Lock key. • To use the symbol on the number keys, you will need to hold the shift key down whilst pressing the required number/symbol key. • Learning to type takes a lot of self-discipline, patience and practice. Aside from practice another key factor to mastering your keyboarding skills is having a good technique. • Correct technique is the most important skill any typist can learn. Speed and accuracy are built around good technique. • Some techniques to consider are: • Memorising the letters by not looking at your hands while typing • Strike the keys with the correct fingers, then return fingers to home keys • Maintain a good posture: feet flat on the floor, back straight, arms close to body and fingers curved. • Proofread and spell check your document prior to printing or submitting

16. DEVELOPING KEYBOARDING SKILLS OPERATING THE KEYBOARD • Touch typing is the ability to use the keyboard without looking at the keys. If you are copying from printed material or notes, you should not be looking at the screen but keeping your eyes on the copy material. • It is most important that you use the correct fingers for all your keyboarding work. Once you have learnt which fingers to use, it is a matter of practice, practice, practice until you are able to key in without looking at the keys. The Guide Keys • The guide keys or home row keys are the keys “a s d f” for the left hand and “; l k j” for the right hand. When you are ready to key in any characters, your fingers should be lightly touching the home row keys so that you get the correct reach. • To begin: • Position your hands and fingers on the keyboard of over the guide keys. • a s d f for the left hand • l k j ; for the right hand • Curl you fingers slightly • Do NOT rest your wrists on the keyboard • Say the letters out loud (quietly) as you type – this will help you remember them

17. DEVELOPING KEYBOARDING SKILLS

18. DEVELOPING KEYBOARDING SKILLS Positioning your fingers • Your left hand fingers will rest on A,S,D,F while your right hand fingers rest on J,K,L,; These are your Home keys. On a Numerical keyboard the Home keys are across the centre, 4; 5; 6; +. This is where your fingers begin as you reach for other keys and then return to them. In other words, your fingers have a home and when they are finished striking a key they return. Use your right thumb when spacing. • To activate the upper characters on a key you must select and hold the Shift key along with the selected key. • The following fingers are used to strike different keys: • Little Finger Left Hand - Home Key A, also used for Ctrl; Shift; Z; Tab; Q; ~/`; !/1 • Second Finger Left Hand - Home Key S, also used for Alt; X; W; @/2 • Middle Finger Left Hand – Home Key D, also used for C; E; #/3 • Pointing Finger Left Hand – Home Key F, also used for V; B; G; R; T; $/4; %/5 • Little Finger Right Hand – Home Key ;, also used for ?//; :; ‘/”; Ctrl; Shift; Enter; P; {/[; }/];|/\; )/0; _/-; +/=; Backspace • Little Finger Right Hand - Numerical Keyboard – Enter; +; - • Second Finger Right Hand – Home Key L; also used for Alt; >/.; O; (/9 • Second Finger Right Hand – Numerical Keyboard – .; 3; 6; 9; * • Middle Finger Right Hand – Home Key K; also used for </,; I; */8 • Middle Finger Right Hand – Numerical Keyboard – 2; 5; 8; /5 • Pointing Finger Right Hand – Home Key J; also used for N; M; H; Y; &/7; ^/6 • Pointing Finger Right Hand – Numerical Keyboard – 0; 1; 4; 7; Num Lock • Thumb Either Hand – Space Bar • To practice using these keys click on the link, open and save the document and complete the Activities.

19. DEVELOPING KEYBOARDING SKILLS Using the Mouse • Rest your hand on the mouse so that your forefinger and middle finer are resting on the left mouse button and right mouse button, respectively Your thumb, ring and little finger will rest on the sides of the mouse, and your palm will rest on the remaining portion of the top of the mouse No need to grip the mouse tightly, hold loosely so that you feel comfortable. Rest your wrist on the table. • The mouse should be placed on a flat surface or mouse pad, buttons facing up. The mouse pointer or cursor position moves when the mouse is moved across a flat surface. • Mouse buttons and operation • Click – press the left mouse button once with your forefinger, which is resting on the button. This will move the mouse pointer or text cursor to that location on the page. • Double-Click – rapidly click the left mouse button twice. This is often done to select/highlight a word or open a programme. • Click and Drag – To drag means to hold down the left mouse button down while moving the mouse. An example would be to highlight or select text. Click (single click, left button) where you want to begin, drag the cursor to the end of the desired text. Release the mouse button. The text is now selected. To select one word, double click on it. • Drag and Drop – some items can be moved by clicking on the item, holding down the mouse button and dragging the item to a new location. • Right-Click – click the right mouse button once to display extra functions. A new menu list of options will appear. • Hover – holding the mouse over an icon or toolbar button without clicking will bring up s short explanation of its function.

20. DEVELOPING KEYBOARDING SKILLS Scrolling • Scrolling means moving up and down or across within a webpage or another computer window. This is done by using the mouse to move the scroll bars on the side and bottom of the screen. • You can also use the scrolling wheel on top of the mouse to move up and down the screen.

21. DEVELOPING KEYBOARDING SKILLS Keys and Functions Some keys on the keyboard have specific functions which you may not be familiar with • ALT Key – This is used together with another key so that the key does something different from what it usually does. For example - Ctrl-Alt-Del - will close an application or turn off your computer or Alt-Tab- will change between windows that are open on the desktop • Arrow Keys - There are four arrow keys. These are used to move your cursor up, down, left and right. • Backspace – This key is used to move one space backwards in your document or on some computers to delete. • Caps Lock – This key is used to produce capital letters. • CTRL Key – This is used in combination with other keys to perform particular operations. For example CTRL + C will copy your selection and CTRL + V will paste. Further operation shortcuts can be viewed in the Shortcut Terminology slide. • Delete Key (Del) – If you wish to remove a word or image from your document you would select what you wish to remove by highlighting the word or image and then select the Delete key. This will remove your selection from your document. • End Key – By using this key with the CTRL button it will take you to the end of your document. • Enter Key – This key is used to move the cursor down to start a new line. • Escape (Esc)– This key allows the user to stop an action, leave a program or return to a previous menu. • Function Keys (F) – These keys are situated at the top of the keyboard and are programmed to perform a particular operation. A list of these functions may be found on the Shortcut Terminology slide. • Home Key – By using this key with the CTRL button it will take you to the start of your document.

22. DEVELOPING KEYBOARDING SKILLS Keys and Functions • Number Lock (Num Lock) – When this key is selected it allows you to insert numbers into your document. • Page Up – When this key is selected it will take you back one page. • Page Down – When this key is selected it will take you forward one page. • Print Screen (PrtScn) – When this key is selected it will copy information that is showing on the screen • Shift Key -There are two Shift keys – one at each end of the bottom row of letters. The Shift keys are used to capitalise letters. • Space Bar – This is the long narrow bar at the bottom of the keyboard. When this key is pressed it will insert a space between your words when you are typing. • Star Key (*) – This key is used to represent times by in formulas. • TAB Key – This button is used to move several spaces at one time to a specified position on your document. • Windows Image – The windows key along with the TAB Key will display all screens that are open on your computer. Keyboard Skills Practice Task 4: • Once you are confident that you have developed your keyboarding and touch typing skills you are to go to the following link and complete a speed and accuracy test. Once you have reach 30 words per minute with a 98% accuracy you are to print screen your results and forward them to your trainer.

23. Identifying Spreadsheet Task Requirements • Microsoft Excel is a spreadsheet package that allows you to organise data, make simple and complex calculations and analyse information. Charts can be easily generated for use in reports. Excel replaces pen and paper using formulae with accurate results. A spreadsheet looks like a large grid and is divided in columns and rows. Each column is represented by a letter and a row by a number. At the meeting point of each column and row is a cell, each cell has a cell reference number. An excel file is like a workbook and so it may contain several sheets just like pages in a book. These sheets are initially named Sheet 1/ Sheet 2/ Sheet 3 ect. You may change this by double clicking on the name and retyping your title. When you are designing your worksheet you may need to ask several questions to ensure that your design meets organisational standards and the needs of the task at hand. • Some points that you may consider when planning your design : • The purpose of the spreadsheet • Information that needs to be included • Headings needed to explain the information in the spreadsheet • The best layout for the information – rows or columns.

24. Identifying Spreadsheet Task Requirements • Some specifications might include: • Common features such as shading all input cells • Adding a print date and spreadsheet version number in the body and footer of the spreadsheet • Removing all worksheets not in use • Renaming the default worksheet name (instead of Sheet 1, Sheet 2 ect) • Guidelines or Procedures might include: • Spreadsheet development according to user requirement specifications • Documentation of spreadsheet formulas and additional functionality • User testing of additional spreadsheet functions • Spreadsheet copies printed with final layout and formulas displayed • A meeting to consult with relevant personnel will clarify these tasks and determine the type of input, design and layout required

25. SPREADSHEET TERMINOLOGY

26. SHORTCUT TERMINOLOGY – CTRL KEY • CTRL + A TO SELECT ALL • CTRL + B TO MAKE SELECTED CELLS BOLD • CTRL + C TO COPY SELECTED CELLS • CTRL + F TO ACTIVATE THE FIND FUNCTION • CTRL + H TO REPLACE TEXT • CTRL + I TO MAKE SELECTED CELLS ITALIC • CTRL + N TO CREATE A NEW DOCUMENT • CTRL + O TO OPEN A DOCUMENT • CTRL + P TO PRINT A DOCUMENT • CTRL + S TO SAVE A DOCUMENT • CTRL + U TOGGLES THE UNDERLINE FEATURE ON/OFF • CTRL + V TO PASTE SELECTED CELLS • CTRL + W CLOSES A DOCUMENT • CTRL + X TO CUT SELECTED CELLS • CTRL + Y REDO THE UNDO AND/OR REPEAT ANY PREVIOUS STEP • CTRL + Z UNDO THE LAST STEP • CTRL + ~ TO VIEW YOUR FORMULAE • CTRL + Home – TO TAKE YOU TO THE START OF YOUR DOCUMENT • CTRL + End – TO TAKE YOU TO THE END OF YOUR DOCUMENT • WHEN YOU PRESS THE ALT KEY IT WILL TURN ON A SPECIAL SHORTCUT FOR THE TAB LEVEL (HOME, INSERT, PAGE LAYOUT ECT)

27. SHORTCUT TERMINOLOGY – F (FUNCTION) KEY • F1 – displays the Excel help task pane. • F2 - Edit the active cell and put the insertion point at the end of its contents. • F3 - Displays the Paste Name dialog box. Available only if names have been defined in the workbook • F4 - Repeats the last command or action, if possible. When a cell reference or range is selected in a formula, F4 cycles through all the various combinations of absolute and relative references • F5 - Displays the Go To dialog box • F6 - Switches between the worksheet, ribbon, task pane and Zoom controls. In a worksheet that has been split, F6 includes the split panes when switching between panes and the ribbon area • F7 – Opens the Spelling dialog box to check spelling in the active worksheet or selected range • F8 - Turns extend mode on or off. In extend mode, Extended Selection appears in the status line, and the arrow keys extend the selection • F9 - Calculates all worksheets in all open workbooks • F10 – Turns on key tips on and off. (Pressing Alt does the same thing) • F11 - Creates a chart of the data in the current range in a separate Chart Sheet • F12 – Displays the Save As dialog box • There are many more shortcuts using a combination of keys. These can be located in Excel Help.

28. SPREADSHEET • Before you start entering data you should be familiar with the layout of a spreadsheet. • A spreadsheet is a grid of columns and row. It has numbers down the side and capital letters across the top. There are other commands located above and below the grid. You will use these in every spreadsheet you create so it is important you understand what they are and how to use them. The items include: • Spreadsheet work area • Title Bar • Office button and Ribbon • The Formula Bar • The Active Sheet

29. Layout of a worksheet COLUMN ROW HEADER HEADING

30. MOVING AROUND THE WORKSHEETComplete Practice Task 5

31. RIBBON functions • RIBBON & QUICK ACCESS TOOLBAR: This is a panel that houses command buttons and icons. It organises functions in sets of tabs and groups together relevant commands. Not all commands are displayed, you may have to click on the arrow in the bottom right corner to expand the selection. • HOME TAB: This includes: Clipboard – cut, copy, paste & format painter functions, Font – includes font, font sizes, colour ect, Alignment –Buttons to align text to left, right, centre, justified, Merge & Centre cells. Number - formatting features such as percent style, comma style, decimal places. Styles – here you can apply either a predefined style or create your own to the cells. Cells - provides buttons to insert, delete and format cells. Editing – This function includes Auto Sum, Fill, Clear, Sort and filter, find and select functions. • FILE TAB: Displays options such as: New (Predefined Spreadsheets) Options, General (Allows you to personalise your work environment with the mini toolbar, colour schemes, default options for new workbooks, customise sort and fill sequences user name and allow you to access the Live Preview feature. The Live Preview features allows you to preview the results of applying design and formatting changes as you move the mouse) Formulas (allows you to modify calculation options, working with formulas, error checking. Proofing (Allows you to personalise how excel corrects and formats your text. Your can customise auto correction settings and have excel ignore certain words or errors in a document through the customer dictionaries, Sort (Allows you to sort your information into an order of your choice.) Advanced (allows you to specify options for editing, copying, pasting, printing, displaying, formulas, calculations & other general settings) Customize (allows you to add features to the Ribbon or Quick Access Toolbar. If there are tools that you are utilising frequently you can add these to the Quick Access Toolbar) Complete Practice Task 6:

32. DESIGNING A SPREADSHEET • The spreadsheet should clearly highlight key information by using titles for columns and rows, and formatting such as shading, borders and font style. Important information, such as the results, should not get lost amongst other things such as the data that has been entered. Numbers and text should be aligned within cells in the way that is easiest to read. • Spreadsheet styles and layouts – These should protect your company’s image. Many organisations use templates to assist in maintaining a standard appearance. These may have font formatting, headers and footers, logos, formulas and functions and the title text already there. It may then be a case of entering names, addresses or summary figures, or altering parts of the content. For example, if you are required to produce a monthly report of sales figures, the only difference each month will be the financial data. The formula and overall appearance will be the same. • In Excel there is an option to apply cell styles to the spreadsheet. The use of styles will add to the appearance and overall presentation of the information. • Complete Practice Task 7

33. Spreadsheet functions • Spreadsheet work area – A spreadsheet is called a worksheet. Multiple spreadsheets are called a workbook. A spreadsheet is divided into columns and rows. The intersection of each column and row is called a cell. The current cell is shown by a highlighted rectangle. This is the cell that the data can be entered into. To move to another cell, click into the required cell. Each cell has a name. The name is simply the letter above the cell and the number to its left. The cell name is called a cell reference, like a grid reference on a map. • Title Bar – This holds the name of the workbook. It has the standard minimise, restore and close functions of most applications. • The Formula Bar – Displays the current cell reference you are working in. As you become more experienced with spreadsheets, you will use the Formula Bar to make calculations. • The Active Sheet – Is located in the bottom left-hand corner of the spreadsheet. By default, each new workbook contains three worksheets. It is possible to add or delete worksheets. • To Name your Worksheet – • Double click on the Sheet number at the bottom of the sheet - this will then be highlighted • type the name specific to the worksheet. Complete Practice Task 8:

34. SPREADSHEET FUNCTIONS • Headers and Footers – Used to record common information into the margin of the report. The header is at the top of the page, the footer at the bottom. Headers and footers are automatically repeated onto all pages of the spreadsheet report. Headers and footers may include the company name, logo, the name of the report, creation date of the report and the file reference information. • To include a Header or Footer – On the ribbon select Insert, select Header & Footer. • Header will be displayed and you will enter the desired information, • To add a Footer, In theNavigationpane, select Go to Footer. This will switch to the Footer. • Headings – In a spreadsheet are the letters above the columns and the numbers beside the rows. Headings are useful when working with the spreadsheet as they give you an understanding of where you are located with the spreadsheet. Some people prefer to work without the headings displayed. Headings can be turned on and off by selecting the Page Layout tab and checking or unchecking the Heading View box. You can also choose whether to print headings. Displaying or printing headings is useful when you are checking that information has been accurately entered, in particular for errors with formulas and functions. • Headings on Headings off

35. SPREADSHEET FUNCTIONS A function is a formula that is built into the spreadsheet. Functions can save a lot of effort by doing quite complex calculations in one cell. Excel provide functions to do a variety of common calculations, such as SUM or AVERAGE. For example, =SUM(B5:B7) adds all the numbers in the range of cells from B5 to B7. Functions can be used in Excel to perform a variety of mathematical, statistical and financial calculations to help you to analyse information. Your ribbon also has built in function that allow you to quickly select your options noted above. To use these functions you could highlight your selected cells and then select your function from the drop down menu as shown. Complete Practice Task 9:

36. SPREADSHEET FUNCTIONS • Restricting access to parts of the spreadsheet - It is possible to limit the sort of data and the range of data that can be entered into a spreadsheet; for example, only putting numbers in and not letters. An unprotected spreadsheet will have many places where someone could enter data in the wrong place, or change a formula and ruin the spreadsheet. You can put restrictions on what parts of the spreadsheet people can change. • Importing and exporting data – You may wish to bring data into your spreadsheet from another application such as Microsoft Access. This is called importing data. The following tools, located in the Data Tab under External Data Grouping are used to import data. • You would need to import data if the information you require is stored in a customer database or you need to import contact details of the customer. Exporting Excel data to another application from Excel can be done by copying and pasting the required information. You may need to do this if the data needs to be analysed or presented in a different way. • Macros – Are used to make it easy to do common tasks, for example, if you regularly insert the company logo into your spreadsheet, instead of going through all the steps to insert the logo, you can design a macro with a short-cut keyboard command. All you need to do then is use the keyboard command and your logo is automatically inserted. Macros may also be assigned to graphics within an Excel spreadsheet

37. Presentation • You want your spreadsheet to be clear and easy to understand and to conform to organisational requirements. Templates are used by organisations to make it easy to produce standard layouts. The templates may already have standard worksheet formatting such as shading, font formatting, headers and footer, logos, column and row headings and predefined formulas. • You may just have to enter the data or alter only parts of the layout. By having a consistent layout for documents it boosts the image and reputation of the business. • You can make data in your spreadsheet more noticeable by using borders and shading. A border will alter the appearance of the cell wall and shading will alter the colour of the cell. From the Home tab, select Font to access the Format Cells dialog box, and then select the Fill tab. • You may be given guidelines or specific instructions on how to produce spreadsheets for particular purposes. If you are, it is important to follow them. This could relate to the content of the spreadsheet or the format and layout of the spreadsheet. Clarify instructions if needed and ask questions if you an unsure of what to do. If you have an idea on how the content or format of a document could be improved speak to your supervisor or the person who allocated the task. It is also important to clarify task requirements to ensure you use the correct format and information based on end-user requirements and your organisational standards. • Complete Practice Task 10:

38. SAVING YOUR WORKSHEET • As soon as you enter information to your worksheet your should save this to a folder and continually save your information as you are working. Doing this can save you a lot of heartache, if the computer crashes, power runs out or any other unforeseen events occur. You would also save this before you print or close the file. Where you store the spreadsheet depends on who needs the information. • To Save A Workbook for the first time: • From the File tab – select File Save As- Then select the location that you want to save the file to, normally Computer, Drive, Folder, • To Save A Workbook that has already been created and you have either edited or added information to it - • You may either click the Save button or Control S. It is a good idea to set your computer to Auto Save – which will automatically save your work every 5 – 10 minutes. But to be on the safe side Save your work regularly. • To Close your Workbook – • Always save first (better safe than sorry) and then X top right hand corner.

39. formatting • Formatting a spreadsheet means to present the information in the most easily understandable way. You can do things to make text and numbers bold or italics, by underlining or by using shading and highlighting. The choices are very similar to those in a word processor. You can also adjust the column width or row height and adjust the alignment of the text. You can choose how numbers are presented; for instance in currency format, as decimals or in date format. • To format numbers – click on the numbers tab • To format alignment – click on the alignment tab • To format font – click on the font tab • To format a title heading across a worksheet – Highlight the cells where you wish to place your heading on the worksheet, then select Merge & Centre in the Alignment Tab. This will merge all of your cells and you can from here alter the font, size and colour if you wish. Note: you are only able to merge one row at a time. Trying to merge more will result in loss of data • To Insert a Column: Select the Column on the right hand side of where you wish to insert your extra column, Right click, Insert. • To Alter The Height Of A Row: As previously stated with the exception of placing your mouse between the row numbers. • To Insert A Row: Select the row below where you wish to insert your extra row, Right click, Insert. • To Delete a Column or Row: Select the Column/Row, Right click, Delete. • Complete Practice Task 11 and 12

40. formatting • INSERT AUTOMATIC DATE OR TIME: • The current date or time can be inserted automatically into a cell or spreadsheet. The date and time used are taken from the computer’s system clock. • To insert current date – Select a cell and press CTRL+; • To insert current time – Select a cell and press CTRL+SHIFT+; • To insert current date and time – Select a cell and press CTRL+;then SPACE then CTRL+SHIFT+; • To insert the date or time that is automatically updated – Select a cell and enter =today( ) for the date and =now( ) for the time. A space must be inserted between the brackets. The today and now functions only when the worksheet is calculated. For example (F9 or the function key is pressed) or saved and re-opened. They are not updated continuously. • Complete Practice Task 13

41. Formatting – PAGE LAYOUT • Adding Borders and Shading – You can make the data in your spreadsheet more noticeable by using borders and shading. A border will alter the appearance of the cell wall and shading will alter the colour of the cell. The following steps will show you how to do this: Fill - • From the Home tab, select Font to access the Format Cells dialog box. • Select the Fill Tab • Select your preferred colour and click OK. Borders – • From the Home tab, select Font to access the Format Cells dialog box • Select the Border tab • Select the Preset Border that you require • Select OK • To view your formatting, select the Office Button, then select Print and Print Preview • To return to your spreadsheet Select ClosePrint Preview • With data, it is easier to read if your borders are displayed. If you do not have any borders there is no lines displayed on your spreadsheet, just the data. • Complete Practice Task 14

42. ENTERING DATA INTO A WORKSHEET • Data entered into Excel worksheets consists of text, numbers or dates/times. • Examples of the types of data that could be entered are Text – names, addresses and product information. Numbers – quantities, customer numbers, pricing and sales figures. Dates/times – date of sale, date of entry and monthly date ranges. • When entering data, Excel treats the data differently, depending on whether it is text, numbers or dates/times. Data must be entered, checked and adjusted as per the organisational and/or task requirements. An example would be the way in which an organisation would like to display dates, it could be: 6 Oct 17, so if the date was formatted as 06/10/2017 it would need to be amended. • To do this – From the Ribbon, select the drop down menu (bottom right corner), select the format required, select ok. Your date will then be altered to 06/10/2017

43. ENTERING DATA INTO A WORKSHEET • To enter data into Excel, the cell must be activated. Entering data into a blank cell only requires you to select the cell, type the data and press Enter on the keyboard. The data will now be contained in the cell At some stage you will have to alter the data you have entered, this could be due to up-to-date- figures or just typing incorrect information. To change this data just follow these simple steps: • Click into the cell where the change is required, this will then be highlighted • Re-type the data as required • Either tab or click outside the cell. • The changes have been made • Save the worksheet to save the changes Or • Click once in the cell you wish to edit; text will then be visible in the formula bar, you can click on the formula bar and make the required changes. • Text Entry – Each cell can contain approximately 32,000 characters. In most cases this is more than adequate for the data that each cell contains. When you enter text you will notice that after you have entered the data, it is automatically aligned to the left of the cell. You will also notice that if there is not data in the adjacent cell, the text will overflow across adjacent cells. If however, there is data in the adjacent cell, the text appears to shorten by cutting off parts. You may think that the data is lost, however, it is still contained in the cell. If you select the cell again, you will notice that the formula bar shows the full content of the cell. To ensure the printed and onscreen version of the worksheet shows the full content of the cell you will need to adjust the width of the cell, and/or may need to “wrap” within the cell parameters.

44. ENTERING DATA INTO A WORKSHEET • Number entry – When entering numbers, the automatic alignment will be to the right of the cell. In most cases, when entering numbers, if the number is longer than the cell, the width of the cell will automatically adjust. The number of characters for a value is limited to 15. Sometimes numbers will not appear as you expect when they are too long for the cell, adjust the width of the cell to correct the problem. • Date and time entries – These entries will align to the right of the cell. When entering the data, date and time formatting is applied to the cell. In Microsoft Excel, it does try to match the formatting with the way the information is typed in, 01/04/2017 or 1 Aug 2017. However, if there has been a previous date and time format in the cell, the information will appear in the previous format. Sometimes the date and time doesn’t appear as you expect, and in its place is a row of the symbol #, 1 Aug 2017/########. If this occurs you will need to adjust the width of the cell. • To Adjust the Column Width: To adjust the width of your cell you can either double click on the right hand side of the top cell, this will automatically change the width to the longest word. You can place your mouse on the column heading between the two cells, your mouse will change to a + sign and then you drag to the required width. Or you can wrap text in a cell by using the wrap-text tool which is located on the Home tab. • Complete Practice Task 15

45. ENTERING DATA INTO A WORKSHEET - AUTOFILL • Sometimes you will need to create sequence labels, for example the months of the year or figures in a sequence. Rather than typing in every name or number Excel can Fill these for you. The following steps will show you how: • Enter the first three labels – for example, January, February, March • Highlight the three cells with the data • A small black square will be displayed in the bottom right hand corner • Place your cursor on the square a + sign will appear, drag this across to the desired cell • You will see that the months will be filled to December

46. ENTERING DATA INTO A WORKSHEET - AUTOFILL • This can also be done with figures. Follow the same steps to autofill information. • Enter the first three figures • Highlight the cells • Select the small black square • A + sign will appear and drag this to the desired cell • You will see how Excel recognises the sequence that you have entered and follows it when auto filling. • Complete Practice Task 16

47. SORTING DATA IN A WORKSHEET • Sorting Data Alphabetically – Often data will need to be sorted. Some examples of sorting data include sorting into customers surname’s alphabetically, sorting in date order or sorting by a reference number. To sort the data alphabetically, select the Sort & Filter tool from the Home tab, and then select Sort A to Z. You do not need to select the data, just make sure the cursor is in the column you want to sort. Excel recognises adjoined columns and rows and sorts appropriately. Excel also recognises label headings and does not include these in the sort. If, however, there are blank rows or columns, Excel will not include these in the sort. If this is the case you will need to select the data to be sorted first and then undertake the sort.

48. Checking for accuracy • Accuracy – You must always check your spreadsheet for accuracy. This may include double-checking with your supervisor to make sure the data you have entered is correct. As well as checking values that are entered, you must also make sure that the labels you have entered are spelt correctly and that you have formatted all data to suit organisational requirements. • Proofreading – Proofreading is the process of carefully reviewing a document for any mistakes. To proofread a spreadsheet you need to check for errors in values and labels. To check values you can compare the values you have entered with the values you were given. Make sure you have not made any errors while you have entered the data and that it is in the correct place. To check errors in labels you can use the spellcheck function under the Review tab. This will check for words that you may have misspelt but will not assist you with product or customers names. You will need to manually check these for accuracy. • Checking Calculations – When using formulas and functions you need to make sure that the result you get is the result you want. If you are working under tight deadlines it is easy to make mistakes, such as subtracting one cell from another instead of adding them. Always test your formulas and functions before using them in your spreadsheet. You can do this by using a calculator to work out the result. When you have designed a formula and used it in your spreadsheet you can check the result again the result your calculator has given you. You should also check for consistency of information against the original data and check that cell references, mathematical operators (symbols) and functions used are correct. Sometimes it is as simple as asking a colleague or supervisor to check your information in draft form before you complete the final version. • If you identify errors you should amend the document and conduct a final proof-read prior to printing to ensure your document meets organisational standards and is presented in a professional manner.

49. Alignment and orientation • Alignment – The automatic default alignment for printing a spreadsheet is that the data will be aligned to the left and top of the page in line with margins. If the report is small it is preferable to centre the data on the page both vertically and horizontally. To do this first Print Preview the spreadsheet to check what the information will look like before it is printed. Select, Page Setup tool, a dialogue box will appear, select Margin tab. Select the option for Center on Page – both horizontally and vertically. The display in the box will show how the changes will appear on the spreadsheet. Select OK. When printed the data will now be aligned both vertically and horizontally on the page • Orientation – When printing the spreadsheet, the default setting is to print in Portrait style. In the workplace, however, it is common for Excel reports to be printed in Landscape as reports may contain more data that spreads across the page rather than down the page. To alter the orientation of your report for the Page Layout tab, select, Orientation, select Landscape.

50. FORMULA • Excel is used to calculate a variety of data; for example, in financial reports, sales statistics and stock levels. Routine or simple calculations are often performed to calculate data. An Excel calculation always starts with an = sign and can include any of the following mathematical operators • Formula used to add two or more numbers together SUM • Auto Sum indicated by • Addition indicated by + • Subtraction indicated by – • Multiplication indicated by * • Division indicated by / • Percentage % • Group of cells indicated by () Brackets • The range between two cells : • The most commonly occurring value in a list of numbers - MODE • The mathematical rule of BODMAS is applied in Excel calculations. That is, Excel calculates information in brackets first, then division, then multiplication, then addition then subtraction • Your cell may look like this =Sum(C2+C3) • TO TYPE IN A FORMULA: • Select the cell you require for the result • Enter the = (equal) sign • Type in your instruction. For example Sum, Minimum, Maximum, Average • Enter left bracket ( • Type in the first number (cell reference or click on the cell) • Type in the arithmetic operator (+ - * /) • Type in the next number (cell reference) or click on the cell. • Press the enter key.