NCIXXXXXX



-1498600-1232535INFORMATION TECHNOLOGYOperate a Spreadsheet ApplicationUnit No: ICAU1130BResource BookletUsing Microsoft Office Excel 20072009 AUTHOR Karen DATE 5/11/2009TABLE OF CONTENTS TOC \o "1-3" \h \z \u Using safe work practices PAGEREF _Toc190005565 \h 1Ergonomic requirements PAGEREF _Toc190005566 \h 1Chair PAGEREF _Toc190005567 \h 1Desk PAGEREF _Toc190005568 \h 1Computer PAGEREF _Toc190005569 \h 1Work organisation PAGEREF _Toc190005570 \h 1Work area PAGEREF _Toc190005571 \h 1Rest periods PAGEREF _Toc190005572 \h 1Exercise breaks PAGEREF _Toc190005573 \h 1Conservation techniques PAGEREF _Toc190005574 \h 1Paper wastage PAGEREF _Toc190005575 \h 1Recycling PAGEREF _Toc190005576 \h 1Energy and power use PAGEREF _Toc190005577 \h 1Understanding Excel PAGEREF _Toc190005578 \h 1Elements of the Worksheet PAGEREF _Toc190005579 \h 1Spreadsheet Terminology PAGEREF _Toc190005580 \h 1Working with Information PAGEREF _Toc190005581 \h 1Navigating around the Worksheet PAGEREF _Toc190005582 \h 1Enter data into the worksheet PAGEREF _Toc190005583 \h 1Enter Text PAGEREF _Toc190005584 \h 1Enter Numbers PAGEREF _Toc190005585 \h 1Enter Dates and Times PAGEREF _Toc190005586 \h 1Select Cells and enter data PAGEREF _Toc190005587 \h 1Enter the same data in a selection of cells PAGEREF _Toc190005588 \h 1Edit spreadsheet cells PAGEREF _Toc190005589 \h 1Modify an entry PAGEREF _Toc190005590 \h 1Insert, delete, or replace cell contents PAGEREF _Toc190005591 \h 1Cancel or undo edits PAGEREF _Toc190005592 \h 1Clear cells of contents or formats PAGEREF _Toc190005593 \h 1Formatting a Workbook PAGEREF _Toc190005594 \h 1Insert and delete columns PAGEREF _Toc190005595 \h 1Insert or delete rows PAGEREF _Toc190005596 \h 1Insert and delete multiple columns and rows PAGEREF _Toc190005597 \h 1Hide Rows and Columns PAGEREF _Toc190005598 \h 1Display hidden rows or columns PAGEREF _Toc190005599 \h 1To display the first hidden row or column on a worksheet PAGEREF _Toc190005600 \h 1Display all hidden rows and columns at once PAGEREF _Toc190005601 \h 1Widening Columns PAGEREF _Toc190005602 \h 1Formatting Text PAGEREF _Toc190005603 \h 1Add borders - Font Group PAGEREF _Toc190005604 \h 1Fill cells with colour - Font group PAGEREF _Toc190005605 \h 1Text alignment - Alignment group PAGEREF _Toc190005606 \h 1Format numbers and dates - Number group PAGEREF _Toc190005607 \h 1Format Painter PAGEREF _Toc190005608 \h 1Autoformat PAGEREF _Toc190005609 \h 1Sorting data PAGEREF _Toc190005610 \h 1Find and replace text PAGEREF _Toc190005611 \h 1Excel Help PAGEREF _Toc190005612 \h 1Auto Fill PAGEREF _Toc190005613 \h 1Printing PAGEREF _Toc190005614 \h 1Print Preview PAGEREF _Toc190005615 \h 1Print the active worksheet PAGEREF _Toc190005616 \h 1Print the same area each time PAGEREF _Toc190005617 \h 1Print a highlighted area of the spreadsheet PAGEREF _Toc190005618 \h 1Setting Worksheet Page Options PAGEREF _Toc190005619 \h 1Change Margins PAGEREF _Toc190005620 \h 1Centre Worksheet on Page PAGEREF _Toc190005621 \h 1Change Orientation PAGEREF _Toc190005622 \h 1Fit Worksheet on one Page PAGEREF _Toc190005623 \h 1Gridlines and Row and Column Headings PAGEREF _Toc190005624 \h 1Headers and/or Footers PAGEREF _Toc190005625 \h 1Close headers and footers - Return to Normal View PAGEREF _Toc190005626 \h 1Simple Functions and Formulas PAGEREF _Toc190005627 \h 1Formula Examples PAGEREF _Toc190005628 \h 1Formula Construction PAGEREF _Toc190005629 \h 1Formula Copying PAGEREF _Toc190005630 \h 1Formula Viewing PAGEREF _Toc190005631 \h 1Common formula errors PAGEREF _Toc190005632 \h 1Function Construction PAGEREF _Toc190005633 \h 1Most commonly used Functions PAGEREF _Toc190005634 \h 1Excel references PAGEREF _Toc190005635 \h 1Relative referencing PAGEREF _Toc190005636 \h 1Absolute referencing PAGEREF _Toc190005637 \h 1Charts PAGEREF _Toc190005638 \h 1Create a Chart PAGEREF _Toc190005639 \h 1Create a Quick Chart on Default Settings PAGEREF _Toc190005640 \h 1Chart Elements PAGEREF _Toc190005641 \h 1Select chart elements PAGEREF _Toc190005642 \h 1Chart Terminologies PAGEREF _Toc190005643 \h 1Change a Chart’s Location PAGEREF _Toc190005644 \h 1Set your Chart Preference as the Default PAGEREF _Toc190005645 \h 1Change the Chart’s layout and format PAGEREF _Toc190005646 \h 1Design Tab PAGEREF _Toc190005647 \h 1Layout Tab PAGEREF _Toc190005648 \h 1Format Tab PAGEREF _Toc190005649 \h 1Headers and Footers for Separate Charts PAGEREF _Toc190005650 \h 1Saving Charts PAGEREF _Toc190005651 \h 1Notes PAGEREF _Toc190005652 \h 14900295224155Using safe work practices Ergonomic requirementsWorkspace, furniture and equipment are adjusted to suit the ergonomic requirements of the userChairChair height should be set so that feet are flat on the floor (where a footrest has not been provided) and thighs are horizontal. The backrest should provide firm lower back support so adjustment up or down, and/or backwards or forwards may need to be made until comfortable. Armrests should not interfere with performance of general tasks. DeskThe height of the desk or chair should be adjusted so that the surface of the desk is at elbow height (when sitting). There should be plenty of room for legs below the desk surface Personal and stationery items should be arranged for easy access, to minimise twisting and bending. ComputerThe monitor should be positioned after adjustments have been made to the desk or chair. It is recommended that the top of the screen be level with the eyes and be positioned about 50cm away from the body when seated. The keyboard should be placed on the desk, as close to the user as possible. Allow room for it to be moved away when not in use. The angle of the keyboard can be adjusted by altering the supports underneath. The mouse should be positioned next to the keyboard on the preferred side. Wrist should be straight whilst using the mouse with the desk supporting the wrist and not the arm. Work organisationWork areaWork area should be kept uncluttered. Desks should only have on them what is really needed. Trays should be used for sorting documents, and any documents that are finished with or will not be needed for some time, should be filed away. Rest periodsIt is important to have frequent breaks away from the workstation. The recommended interval is ten minutes for each hour worked in front of a computer. If unable to take this time out, work tasks should be varied. For example, phone calls could be made, filing or other work related tasks could be done for a few minutes. Exercise breaksExercises should be done at regular intervals. Exercises for office workers can include head rolls, shoulder rolls, wrist stretch, back arching, foot rotation and even eye exercises. Conservation techniquesEnergy and resource conservation techniques are used to minimise wastage in accordance with organisational and statutory requirementsPaper wastageProofread and edit documents on screen before printing Don't print more pages than needed, use the "print range" function of software to only print those pages which have been edited Print on both sides of your paper where possible Use scrap paper from printed documents no longer needed. Write on the back for informal notes or memos Use the duplex facility of the photocopier. RecyclingPaper should be saved for recycling where possible - if the information is confidential, it can be shredded before disposal into a recycling container Use recycled paper products wherever possible Reuse office products such as folders, envelopes and packaging materials. Energy and power useUse the "power save" feature of your printer, if available Switch off lights and equipment when not required. Understanding ExcelA spreadsheet is an online version of an accountant's worksheet, which can automatically do most of the calculating (and recalculating) for you. You can do budgets, analyse data, generate sorted lists, create charts or keep track of your grades. Excel allows you to create spreadsheets much like paper ledgers that can perform automatic calculations. Each Excel file is a workbook that can hold many worksheets. The worksheet is a grid of columns (designated by letters) and rows (designated by numbers). The letters and numbers of the columns and rows (called labels) are displayed in gray buttons across the top and left side of the worksheet. The intersection of a column and a row is called a cell. Each cell on the spreadsheet has a cell address that is the column letter and the row number. Cells can contain text, numbers, or mathematical formulas. Elements of the WorksheetFormula BarQuick Access ToolbarThe RibbonHorizontal Scroll BarVertical Scroll BarExpand Formula BarName BoxZoomWorksheet TabsActive CellPage Layout ViewInsert New Worksheet TabPage Break PreviewRow LabelsColumn LabelsNormal ViewSpreadsheet TerminologyWorksheet The tab of a workbook in which you enter and manipulate data. It is organised into a grid containing vertical columns and horizontal rows.RowsRun horizontally across spreadsheet and are numbered 1, 2, 3, 4ColumnsRun vertically down the spreadsheet and are labelled A, B, C, DCellThe intersection of a row and a column is called a cell. Cells are identified by cell Coordinates ie, where column and row meet A1, B6.Active Cell The cell that you are working in. It is identified by a dark line around the border of the cell.Cell Range A range of cell references that can be given a name to simplify formulas and are easier to remember than cell co-ordinates. Range names can easily be found with Edit/Go To or Name Box. They are separated with a colon A1:B6. A range can be one cell or multiple cells.OperatorsSymbols used in formulas to perform mathematical functions./ for division, * for multiplication, + for addition, - for subtraction(Parenthesis change the order of the way in which formula is calculated ie. 3 + (2 * 2) = 7, (3 + 2) * 2 = 10. TextUsually describe a name or product description. For example, headings, notes, explanatory text, notations descriptive text. By default, labels are left aligned. Numeric ValuesNumbers typed directly into a spreadsheet cell. For example: numbers, dates or times, numeric characters such as $, commas and decimal points. Values are used in arithmetic functions and formulas. By default, values are right aligned. If you wanted values as text they must be preceded by a single quotation mark. If used in a formula use double quotation marksFormulas/FunctionsA formula is a worksheet entry that performs a calculation. A Function is an equation that calculates a new value from existing values. They always begin with an equal sign (=). Formulas are displayed in the Formula bar and the result displayed in the cell in which you are working. Functions are formulas that add, find max, min and average of numeric values. For example: SUM, AVG, MIN, MAX.TemplatesA skeleton Worksheet with titles, headings and formulae. New spreadsheets are created on existing templates. Templates save time by having required formats established. New data only needs to be entered. Working with InformationNavigating around the WorksheetTo cell A1Ctrl HomeBottom right cell of working areaCtrl EndBeginning of current rowHomeOne screen up or downPage up/Page downOne cell downEnterOne cell rightTabOne cell up, down, right or leftDirection keys on keyboard (left, right, up, down arrows)Home Tab, Editing Group, Find & Select Button, Goto [F5].Takes you to cell reference indicated in reference box. Enter data into the worksheet3338195144145Enter TextClick in the required cell.Type data and press [ENTER] or [TAB] or ARROW KEYS. You can also click the tick on the Formula Bar. To abandon an entry, press [ESC] or click the cross on the Formula Bar.Note: To enter data on a new line within a cell, enter a line break by pressing ALT+ENTER. If entering large amounts of data, expand the formula bar by clicking on the button to the right of the formula bar.Enter NumbersClick in the required cell.Type data and press [ENTER] or [TAB] or ARROW KEYS. You can also click the tick on the Formula Bar. To abandon an entry, press [ESC] or click the cross on the Formula Bar.Note: When you type figures in the spreadsheet DO NOT type in dollar signs, spaces or commas: Example: 45670 NOT $45,670 or 45 670 or 45,670. You format the cells later.Enter Dates and TimesTo enter a date, use a slash mark or a hyphen to separate the parts of a date; for example, type 9/5/2002 or 5-Sep-2002. To enter a time that is based on the 12-hour clock, enter the time followed by a space, and then type a or p after the time; for example, 9:00 p. Otherwise, Excel enters the time as AM. Note: To enter the current date press CTRL+;. To enter the current time, press CTRL+SHIFT+; (semicolon).Select Cells and enter dataTo selectDo thisA single cellClick the cell, or press the arrow keys to move to the cell.A large range of cellsClick the first cell in the range, and then hold down SHIFT while you click the last cell in the range. You can scroll to make the last cell visible.You can also select the first cell in the range, and then press F8 to extend the selection by using the arrow keys. To stop extending the selection, press F8 again.All cells on a worksheet281305048260Click the Select All button. To select the entire worksheet, you can also press CTRL+A. ?Note???If the worksheet contains data, CTRL+A selects the current region. Pressing CTRL+A a second time selects the entire worksheet.Nonadjacent cells or cell rangesSelect the first cell or range of cells, and then hold down CTRL while you select the other cells or ranges. An entire row or column2252980-8349615Click the row or column heading. ? Row heading?Column headingEnter the same data in a selection of cellsSelect the cells you want the data entered into.In the active cell, type the data, and then press CTRL + [ENTER]. To cancel a selection of cells, click any cell on the worksheet.Note:You can also enter the same data into several cells by using the to automatically fill data in worksheet cells.Edit spreadsheet cellsModify an entryDouble click directly in the cell to be modified [F2].or click in formula bar Use direction keys and [BACKSPACE] key to make necessary modifications.Press [ENTER].Insert, delete, or replace cell contentsTo insert characters, click in the cell where you want to insert them, and then type the new characters. To delete characters, click in the cell where you want to delete them, and then press [BACKSPACE], or select the characters and then press [DELETE]. To replace specific characters, select them and then type the new characters. To turn on Overtype mode so that existing characters are replaced by new characters while you type, press [INSERT]. Cancel or undo editsBefore you press [ENTER ] or [TAB], you can press [ESC] to cancel any edits that you made to the cell contents -OR- press CTRL+Z, -OR- click the Undo button on the Quick Access Toolbar.Clear cells of contents or formatsYou can clear cells to remove the cell contents (formulas and data), formats (including number formats, conditional formats, and borders), and any attached comments. The cleared cells remain as blank or unformatted cells on the worksheet.Click the Home tab, Editing groupClick the arrow next to the Clear button , and then do one of the following: To clear all contents, formats, and comments that are contained in the selected cells, click Clear All. To clear only the formats that are applied to the selected cells, click Clear Formats. To clear only the contents in the selected cells, leaving any formats and comments in place, click Clear Contents. To clear any comments that are attached to the selected cells, click Clear Comments.Formatting a WorkbookInsert and delete columnsTo insert a column, click a cell or select a column immediately to the right of where you want to insert a new column. Click on the Home tab, Cells group.Click the arrow on InsertClick Insert Sheet Columns. To delete a column, click a cell in the column or select a column. Click on the Home tab, Cells group.Click the arrow on Delete. Click Delete Sheet Columns. To insert or delete another column after you've done one, press [F4]. Insert or delete rowsTo insert a row, click a cell or select a row immediately below where you want to insert a new row. Click on the Home tab, Cells group.Click the arrow on InsertClick Insert Sheet Rows. To delete a row, click a cell in the row or select a row. Click on the Home tab, Cells group.Click the arrow on Delete. Click Delete Sheet rows. To insert or delete another column after you've done one, press [F4]. Insert and delete multiple columns and rowsSelect the required number of columns/rows you want to insert/delete.Click on the Home tab, Cells group.Select your options.Note: You can also use your right-mouse to insert and delete rows and columns. This is useful when working with multiple rows and columns.5195570517525Hide Rows and ColumnsSelect the row/s or column/s to be hiddenClick on the Home tab, Cells groupClick Format. Under Visibility, point to Hide & Unhide, and then click Hide Rows or Hide Columns. - OR-Under Cell Size, click Row Height or Column Width, and then type 0 in the Row Height or Column Width box.Note: You can also right-click a row or column (or a selection of multiple rows or columns), and then click Hide.5347970202565Display hidden rows or columnsSelect rows above/below the rows you want to unhide or the columns to the left and right of the columns you want to unhide.On the Home tab, Cells group, click Format. Under Visibility, point to Hide & Unhide, and then click Unhide Rows or Unhide Columns -OR- Under Cell Size, click Row Height or Column Width, and then type the value that you want in the Row Height or Column Width box.To display the first hidden row or column on a worksheetSelect it by typing A1 in the Name Box next to the formula barOn the Home tab, Cells group, click Format. Under Cell Size, click Row Height or Column Width, and then type the value that you want in the Row Height or Column Width box. You can also select it by using the Go To dialog box. On the Home tab, under Editing, click Find & Select, and then click Go To. In the Reference box, type A1, and then click OK.Note: You can also right-click the selection of visible rows and columns that surround the hidden rows and columns, and then click Unhide.Display all hidden rows and columns at onceSelect all the cells in the Spreadsheet.On the Home tab, in the Cells group, click Format. Under Visibility, point to Hide & Unhide, and then click Unhide Rows or Unhide Columns -OR- Under Cell Size, click Row Height or Column Width, and then type the value that you want in the Row Height or Column Width box.Widening Columns522414586995There are a number of methods you can use to adjust the column width.Click the cell for which you want to change the column width. Click on the Home tab, Cells group,Click Format. Under Cell Size, do one of the following: To fit all text in the cell, click AutoFit Column Width. To specify a larger column width, click Column Width, and then type the width that you want in the Column width box -OR-4614545393700Click and hold down mouse on the vertical line shown between column headings and drag it to required size -OR- double clicking on the line border will choose the best fit based on the largest cell entry in the column.Formatting Text There are a number of tools available on the Home Tab to format your workbook.Add borders - Font GroupSelect cells to draw a border around.Select the Border button.Select required border – you can choose the line style, colour, and cell borders to apply. Fill cells with colour - Font group176657095250Select cells to fill.Select the Fill button and choose your colour.Text alignment - Alignment group There are several options for aligning text in a cell. Firstly, select the text and use the appropriate buttons to format text.272859560960indent/decrease indent of text 493839514605align text using the left, right, centre, justify, top or bottom vertical and horizontal alignments. rotate text to any angle .wrap text within a cell .merge and centre text .Format numbers and dates - Number group5309870182245Excel has a variety of different ways that cells containing numbers and dates can be formatted. Click on the General drop down arrow.Choose your number format.Click the More Number Formats button for more format options.50241200To access further formatting options you can click on the Dialogue Box Launcher located on the bottom right-hand side of each group.5119370339090Format PainterFormat Painter can be used to quickly copy formatting from one cell to another.Select the cell/s with the formatting to be copied.Click on the Home Tab, Clipboard group.Click on the Format Painter button.Drag over the cell/s to receive the formatting.Click back on Format Painter button or press [ESC] to finish.Note: Double clicking on the Format Painter button enables the formatting to be copied to other text many times. Press [ESC] when finished.AutoformatYou can quickly format your worksheet data by applying a predefined table style. However, when you apply a predefined table style, a Microsoft Office Excel 2007 table is automatically inserted for the selected data. If you don't want to work with your data in a table, you can convert the table to a regular range while keeping the table style formatting that you applied.3366770241300On the worksheet, select a range of cells that you want to format by applying a predefined table style. Click on the Home tab, Styles group.Click Format as Table. Under Light, Medium, or Dark, click the table style that you want to use. Click anywhere in the table. This displays the Table Tools, adding the Design tab.On the Design tab, Tools group.Click Convert to Range. You can also right-click the table, click Table, click Convert to Range.3909695601980Sorting data521462092710Click in the table you want to sort. Click on the Home tab, Editing group.Click Sort & Filter. Choose Ascending or Descending.Find and replace textClick on the Home tab, Editing group.Click Replace. Click the Replace tab. In the Find what box, type the text that you want to search for. In the Replace with box, type the replacement text. Click Find Next. To replace an occurrence of the text, click Replace. After you click Replace, Office Word 2007 moves to the next occurrence of the text. To replace all occurrences of the text, click Replace All. Note: To cancel a replacement in progress, press ESC.Excel HelpWays to access help in Excel 2007 are as follows:1923415635Click the question mark in the top right hand corner of the Word screen or use the F1 shortcut. Type your search string in the Search box. To customise your search click the drop down arrow to the right of the search box.4976495264160Click the Table of Contents button () on the Help toolbar. Click the Help icon on the top right-hand side of dialogue boxes, if available. Not all dialogue boxes have this option.Use manuals or research forums, internet sites and PC magazines.Auto FillExcel’s Auto Fill feature makes it quick and easy to enter a series of data into a range of cells. Note: You can also use the Fill button in the Editing group on the Home tab.Type the initial data for the seriesSelect the cell/s containing this data.Move the mouse pointer to the bottom right corner of the selection called the fill handle, where the mouse pointer becomes a cross.Drag the selection to generate the data required.2165350509905Tip: Hold down Ctrl while extending a single number (eg 1) to generate an incrementing result (ie 2, 3 4, …). This option will also work when you want to prevent incrementing text.Printing There are many different options that can be chosen to print your spreadsheet, depending on what is required. Before printing it is wise to preview the intended print selection, so that any formatting adjustments can be made at this point. Print Preview5405120160020Click the worksheet or select the worksheets that you want to preview. Click the Microsoft Office Button , click the arrow next to Print, and then click Print Preview. Keyboard shortcut??You can also press [CTRL+F2].To preview the next and previous pages, on the Print Preview tab, in the Preview group, click Next Page and Previous Page. To make page setup changes, on the Print Preview tab, in the Print group, click Page Setup, and then select the options that you want on the Page, Margins, Header/Footer, or Sheet tabs of the Page Setup dialog box. Note:You can also add the Print Preview button to the Quick Access Toolbar.Print the active worksheetClick on the Microsoft Office Button.Click on Print.In the Print What Section.Click on the Active Worksheet/s radio button.Print the same area each timeSelect the cells you want to print and set this as Print Area. Once a Print Area is defined, Excel will only print cells included in the Print Area until it is deleted or redefined.Select cells to include in Print Area.Click on Page Layout tab, Page Setup group.Click on Print Area button.Click on Set Print Area.To clear the Print Area, click on Clear Print Area.Print a highlighted area of the spreadsheetSelect area to print.Click on the Microsoft Office Button .Click on Print.In the Print What Section.Click on the Selection radio button and OK.Setting Worksheet Page OptionsChange MarginsClick on the Page Layout tab, Page Setup group.Click Margins. Make your changes.Centre Worksheet on PageClick on the Page Layout tab, Page Setup group.Click Margins, Custom Margins.Under Center on page, select the Horizontally or Vertically check box.Change OrientationClick on the Page Layout tab, Page Setup groupClick Orientation. Select your orientation.Fit Worksheet on one PageClick on the Page Layout tab, Scale to Fit groupMake adjustments in the Scale: box. You can also click on the Page Setup Dialogue Box Launcher and make your adjustments in the Fit to box.502031064770Gridlines and Row and Column HeadingsClick on the Page Layout tab, Sheet Options groupCheck or uncheck Gridlines and Headings View check boxes. You can also click on the Page Setup Dialogue Box Launcher.Headers and/or FootersClick the worksheet to which you want to add headers or footers, or that contains headers or footers that you want to change. 462788066040On the Insert tab, in the Text group, click Header & Footer. Click the left, center, or right header or footer text box.Choose your header and footer information or type in the text you want. You can use predefined headers and footers by clicking on Header and Footer buttons.You can also click on the Page Layout tab Page Setup group, click the Dialog Box Launcher next to Page Setup. On the Header/Footer tab, click Custom Header or Custom Footer. Close headers and footers - Return to Normal View4900295-309245On the View tab, in the Workbook Views group, click Normal - OR- click on the Normal view button on the Status bar. Simple Functions and FormulasA formula entered into a cell will perform a calculation using data in other cells. A formula always starts with an = sign and can include mathematical operators:Operator Symbols+Addition/Division-Subtraction*Multiplication% Percent^ ExponentialFormula ExamplesAddition=C10+C13Adds the contents of C10:C13Subtraction=C12-C14Subtracts cell C14 from cell C12Multiplication=B5*BI2Multiplies cell B5 by B12Division=BS/B12Divides cell B5 by B12Percentage=A4*.15 or 15%Find 15% of the value in A4Note: Excel calculates its numeric data in this order. Brackets change the order of calculation ie which part of formula is calculated first, for example =(A1+B12)*C3 or A1+(B2*C3)BODMASBrackets of division multiplication addition and subtraction() of / * + Formula ConstructionSelect the cell to contain the formula.Enter = sign.Type in the formula.[Enter].Formula CopyingPosition cursor on cell where the formula is to be copied from15300Position pointer on FILL HANDLE (small box on lower right corner)When the cursor changes to a cross, drag fill handle down or across the cells where the formula is to be copied to.Note:If you double click on the fill handle when copying cells down it will fill all adjacent cells. This does not work for filling cells horizontally placed in the grid.Formula ViewingPress Ctrl ~ (Tilde symbol, situated next to 1 on keyboard). Press again to turn off).Common formula errors Formula errors can result in error values as well as cause unintended results. #VALUE!The formula uses a wrong type of operand or argument. Check to see that you are not performing math operations on labels or that arguments of functions that need to be numeric are not referring to cells containing labels.#DIV/0!You are trying to divide be zero. Correct the divisor. If the divisor is a cell reference, check that it is not empty.#NAME?Occurs when Excel does not recognise text in a formula.If you used a name you defined, check its spelling. You can avoid this error by selecting a name in the Name Box.If you typed in a function, check its spelling or verify that such a function exists.If you are performing operations on text, enclose it in double quotes “ “.#N/AOccurs when a value is not available to a function or formula. Click the cell that displays the error, click the button that appears, and then click Trace Error if it appears. #REF!A cell reference is not valid. This happens if you deleted cells referred to in the formula or pasted moved cells on cells referred to in the formula. You will need to re-enter the formula.#NULL!Occurs when you specify an intersection of two areas that do not intersect. The intersection operator is a space between references.Make sure you are using a correct range operator ie colon (:) or comma (,) to separate two ranges. Change cell ranges so they intersect.Function ConstructionA function is a predefined formula which needs you to specify values (through cell references). Examples are: FunctionDefined=SUM(range of cells)returns the sum of the selected cells=AVERAGE (range of cells)returns the average of the selected cells=MAX(range of cells)returns the highest value of the selected cells=MIN(range of cells)returns the lowest value of the selected cells=COUNT(range of cells)returns the number of values of the selected cellsYou can enter a function into a spreadsheet the following ways:4038600339725Type the function directly into a cell and auto complete will help you choose your function. Use the AutoSum button on the Home tab, Editing group ... Use the AutoSum button on the Formulas tab, Function Library group.Use the fx button on the Formulas tab, Function Library group.Use the fx button on the formula bar.Most commonly used FunctionsFunction DescriptionSUMCalculates the sum of the numbers.Syntax: =SUM(number1,number2)AVERAGECalculates the average of the numbers.Syntax: =AVERAGE(numberl, number2,...)MAX or MINCalculates the maximum or minimum of the numbers.Syntax: =MAX(numberl, number2,...)Syntax: =MIN(number 1, number2, ...)COUNTCalculates how many numbers are in the values or cellreferences supplied. Blank or text cells are ignored.Syntax: =COUNT(number 1, number2, ...)TODAYDisplays today's date based on the computer's clock.Syntax: =TODAY0NOWDisplays today's date and time based on the computer's clock.Syntax: =NOW0Excel referencesA reference identifies a cell or a range of cells on a worksheet and tells Microsoft Excel where to look for the values or data you want to use in a formula. 4724400650240Relative referencingWhen you create a formula in a spreadsheet, references to other cells are usually expressed in terms of relative position. For instance if the formula =A3 is entered into cell B5, the cell referred to is found two cells above and one to the left. If the formula in cell B5 is copied down one row into cell B6, the formula will adjust automatically to refer to the cell which is two cells above and one to the left i.e. A4Absolute referencing4509770497840If, as the user, you require copied formula always to refer to exactly the same cell, use absolute referencing. To prevent Excel from changing the value of a column or row when copying, precede it with a $ sign. Using the example to the right, if the formula in cell B5 is written as =$A$3, is copied down one cell, the $ prevents the formula changing. Thus the formula which is copied into cell B6 is still =$A$3To change a cell reference from relative to absolute, select the reference in the formula bar and press the F4 key.On occasions it is necessary to allow only the row or the column to change but not both. The table below shows the combinations of relative and absolute references which are possible and their effect when being copied.Both column and row changeColumn does not change but row changesColumn changes but Row does not changeBoth Column and Row remain unchanged=B5=$B5=B$5=$B$5ChartsCharts are visually appealing and make it easy for users to see comparisons, patterns, and trends in data. For instance, rather than having to analyse several columns of worksheet numbers, you can see at a glance whether sales are falling or rising over quarterly periods, or how the actual sales compare to the projected sales.There are many different chart types available such as column, bar line and pie to name a few. Charts can be customized to suite your requirements and can be stored in the current workbook as a separate chart sheet -OR- as an embedded chart on a worksheet alongside the relevant data.4195445766445Create a ChartSelect the cells that contain the data that you want to use for the chart. Click on the Insert tab, Charts groupDo one of the following: Click the chart type, and then click a chart subtype that you want to use. To see all available chart types, click a chart type, and then click All Chart Types to display the Insert Chart dialog box, click the arrows to scroll through all available chart types and charts subtypes, and then click the ones that you want to use.Create a Quick Chart on Default Settings When you select your data and press [F11] (chart is displayed on a separate sheet) or ALT+[F1] (chart is embedded).Chart ElementsPlot AreaX AxisGridlinesLegendCategory Horizontal LabelsY AxisData SeriesSelect chart elementsOn a chart, click the chart element that you want to select - OR- select a chart The element that you select will be clearly marked with selection handles.4147820268605Click a chart. The Chart Tools Tab appears.Click on the Format tab Current Selection group Click the arrow next to the Chart Elements box.Click the chart element that you want to work with -OR- click the element in the Chart -Or- right-mouse click on the chart element. Chart TerminologiesAxis A 2-D chart has 2 axes: X category axis (normally horizontal)Y axis (normally vertical). A 3-D chart has 3 axes: X and Y (horizontal surface) Z is the vertical axis.Categories: Label names of items to be presented by the chart. (Usually on the X axis).Data Series: Range of cells (values) to be plotted for a specific category. For example, all numbers in one row or one column of data.Legend: Key used to identify various chart series. If series labels are included in the worksheet selection they will be used in the legend, otherwise the legend will display Series 1, Series 2 etc.Change a Chart’s LocationBy default, the chart is placed on the worksheet as an embedded chart. To place the chart on a sheet of its own:5071745187325Click the embedded chart or the chart sheet to select it and to display the chart tools. Click on the Design tab, Location group.Click Move Chart. Under Choose where you want the chart to be placed, click New sheetGive the Sheet.Click OK.Note: If you decide to embed the Chart after putting it in a new sheet you follow the above steps to embed the chart again.Set your Chart Preference as the DefaultIf you use a specific chart type frequently when you create a chart, you may want to set that chart type as the default chart type. Select the cells that contain the data that you want to use for the chart. Click on the Insert tab, Charts group.Select the chart type and click on All Chart Types.Select your chart and chart subtypeClick Set as Default Chart. Change the Chart’s layout and formatWhen working with a Chart (ie the chart is selected), the chart tools become available and the Design, Layout, and Format tabs are displayed. You can use the commands on these tabs to modify the chart so that it presents the data the way that you want. Design TabLayout TabFormat TabHeaders and Footers for Separate ChartsWhen creating Charts on separate sheets, Headers and Footers must be added, even though you have already put them on the worksheet. Each new sheet you edit requires its own Headers and Footers.Saving ChartsCharts are automatically saved when the Spreadsheet is saved and any change in the worksheet results in a change to the Chart.Notes ................
................

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

Google Online Preview   Download