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

13-09-20 1

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

13-09-20 2

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

13-09-20 3 Ways to Move Around Worksheet

n Use the scroll bars located at the bottom and right edge

• f the worksheet

n Use the arrow keys on the keyboard – When holding Ctrl key, they move to the last cell containing text

• r 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

13-09-20 4

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

• f 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

1)

State purpose of Worksheet

2)

Decide what input values are needed (input area)

3)

Decide what outputs are needed (output area)

4)

Assign worksheet inputs and results into columns and rows (consider labeling)

5)

Enter the labels, values and formulas in Excel

6)

Format the numerical values in the worksheet

7)

Format descriptive titles and labels (bold, font size, colour, etc)

8)

Document workseet thoroughly

9)

Save document and prepare printouts

13-09-20 5

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

13-09-20 6

General Accounting Practice

n Uses two decimals and places dollar signs only

• n 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

13-09-20 7

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

13-09-20 8

Statistical Functions

n Average n Maximum n Minimum n Count n CountA n If