Apco 1p01 Working within a Spreadsheet Creating a Worksheet What - PDF document

13-09-20 Apco 1p01 – Working within a Spreadsheet Creating a Worksheet What is a Spreadsheet? n Spreadsheet is a tool to organize numerical data n Spreadsheet, sheet and worksheet are terms that are used interchangeably n Used in business today for: – Budgets – Cash flow analysis – Business plans – Projections – What-if analysis Advantages of Electronic Spreadsheets n Accuracy of calculating numbers n Ease of making changes and corrections n Speed in creating the spreadsheet n Ability to prepare and present an attractive report n Ease of creating graphs and charts using the data in the worksheet n Ability to make business decisions n Access to internet and ability to retrieve data into your worksheet n Multiple users can share and edit the same file 1

13-09-20 Workbook n A workbook contains a collection of worksheets saved as one file n Only one worksheet is visible when Excel is started n The worksheets are identified by the tabs at the bottom of the sheet n Clicking on a tab will access a different worksheet in the Workbook The Worksheet n Comprised of a grid of horizontal rows and vertical columns n Column – letters A-Z, AA-AZ, BA-BZ,… – maximum of 256 columns n Row – numbered from 1 to 65,536 n Cell – the place where a row and a column intersect – maximum of 16.7 million cells in a worksheet – active cell is identified by the dark border surrounding it n Cell Reference – formed by the letter of the column followed by the number of the row. Eg E27 Parts of the Excel Sheet n Title Bar – identifies the file name of the document n Menu Bar – displays all of the various commands that are used to interact with Excel n Control Buttons – used to control viewing, minimizing and closing Excel n Toolbars – Standard – used for common commands such as Save, Printing, Copying, etc… – Formatting – used to quickly format the worksheet – Formula – used to enter of change values in cells 2

13-09-20 Ways to Move Around Worksheet n Use the scroll bars located at the bottom and right edge of the worksheet n Use the arrow keys on the keyboard – When holding Ctrl key, they move to the last cell containing text or data in the direction of the arrow n Use Ctrl-Home to return to the first cell of the worksheet (A1) n Use the Go To command to go to a specific cell n Use Tab to move a column to the right; Use the Enter key to move to a row below Selecting Ranges of Cells n A range of cells is identified by its first and last cells n A colon is place between cell reference of the first cell and the cell reference of the last cell n Eg – A1:C3 would include cells A1, A2, A3, B1, B2, B3, C1, C2, C3 n When range of cells selected, bold border surrounds the entire range and the range is shaded n In a range of cells, the active cell is the cell that is not shaded n Can select an entire column or row by clicking on either the column heading or the row heading Formula Bar Comprised of 5 parts: n Name box – displays active cell when a cell entry is being made; name of a function as a formula is being entered n Cancel box – used to cancel a cell entry as its being entered; cell returns to its original contents n Enter box – used to accept the entry as completed n Insert Function button – used to insert function commands n Entry Area – shows what is being entered into the formula; entry also displays in the active cell 3

13-09-20 Status Bar Modes Status Bar indicates the working condition of Excel n Ready Mode – when Excel is waiting for entries to be made – formula bar is inactive – Status bar reads ‘ Ready ’ n Enter Mode – When entering info in a cell for the first time – Formula bar is active, cancel and enter boxes display – Status bar reads ‘ Enter ’ n Edit Mode – When you have returned to a cell to edit the contents – Formula bar is active, cancel and enter boxes display – Status bar reads ‘ Edit ’ Entering Numbers in Excel n Excel aligns numbers by default at the right edge of a cell n Type a minus sign as the first char to indicate a negative number n Add other symbols later, such as comma or dollar sign by formatting a group of cells n Edit numbers exactly as you would edit text Effective Workbook and Worksheet Design State purpose of Worksheet 1) Decide what input values are needed (input area) 2) Decide what outputs are needed (output area) 3) Assign worksheet inputs and results into columns and 4) rows (consider labeling) Enter the labels, values and formulas in Excel 5) Format the numerical values in the worksheet 6) Format descriptive titles and labels (bold, font size, 7) colour, etc) Document workseet thoroughly 8) Save document and prepare printouts 9) 4

13-09-20 Show Functions n AutoSum button n Indenting button n Border button Number Style Buttons n Currency Style button ($) – places a dollar sign at the left edge of the cell and adds two decimal places n Percent Style button (%) – converts the number to a percentage and adds a percent sign after the number n Comma Style button (,) – places commas needed in large numbers and adds two decimal places n Increase Decimal button – increases the number of decimal places in a number by one n Decrease Decimal button – decreases the number of decimal places in a number by one Demonstrate n Long Cell Entries (#####) n AutoFit Selection 5

13-09-20 General Accounting Practice n Uses two decimals and places dollar signs only on the first line of a column of money amounts and in the total n Dollar signs and decimals align n Underlines are used to indicated cells that are to be added n Bold or double underline appears below the total cell or row Formulas in Excel n Formula – a sequence of instructions used to compute a mathematical problem n Mathematical Operators in Excel – Add (+) – Subtract (-) – Multiply (*) – Division (/) – Exponentiation (^) n When using formulas in Excel, use cell references rather than absolute values n When non-adjacent cells are added, the cell references will be seperated by a comma – Eg = SUM(A1,C1,F1) Error Messages in Excel n #N/A – “ no value is available ” n #NAME? – cannot recognize text in a formula n #NULL! – intersection of two areas is included in formula but areas do not intersect n #REF! – displays when reference is made to an invalid cell n #VALUE! – displays when wrong type of formula is entered n #DIV/0 – when a value is divided by zero n #NUM! – invalid numeric values in a formula or function n ##### - column is not wide enough for all of the numbers to fit 6

13-09-20 Copying n Copying Data – Destination must match the size and the shape of the copy area – Cell contents, formulas, and formats from the copy area are also copied to the new location – All cell contents previously in the destination will be deleted – When using cut-and-paste method, copy area remains active, allowing for cell contents to be pasted to more than one destination – When cells are copied, they are placed in the Office Clipboard. They can then be copied to any Microsoft program n Copying Formatting – Can copy format from one cell to another by using the Format Painter button (located on Standard toolbar) Paste Special Choices n All – pastes the entire cell contents (formulas, formats and numbers) n Formulas – pastes only the formula n Values – pastes only the values of a cell (ie not underlying formula) n Formats – pastes only the formats of a cell n All except borders – when a cell contains a border, the border is not copied n Column widths – pastes only the column widths n Formulas and number formats – pastes both the formulas and the formats n Values and number formats – pastes the number values and formats Types of Cell References n Relative Cell Reference – When cells are moved or copied, all cell contents are also moved; cell references in a formula automatically adjust to reflect that new location – Eg formula at cell D1 is = SUM (A1:C1). If this formula is copied to F5 then the formula at F5 would be = SUM (C5:E5) since the row moved by 2 and the column moved by 5 n Absolute Cell Reference – When a specific cell must be used in a formula, an absolute cell reference must be used – Identified by dollar signs in the cell reference in front of the row and column reference – eg $B$1 n Mixed Cell References – Contain an absolute and relative element in is cell identification – Eg - $A$1 + B1 + C1 – $A1 + $B1 + $C1 – A$1 + B$1 + C$1 7

13-09-20 Statistical Functions n Average n Maximum n Minimum n Count n CountA n If 8