EXCEL

1. EXCEL TUTORIAL 1 Getting Started with Excel

2. Understanding Spreadsheets Spreadsheet – A collection of text and numbers laid out in a rectangular grid

3. Exploring the Excel Window • Workbook – spreadsheet file • Workbook window – where contents of a workbook are shown • Active workbook – workbook currently being used • Sheets – make up a workbook • Worksheet – contains data in rows and columns • Chart sheet – contains an Excel chart

4. Excel Window Continued - Sheet name identifies each sheet Sheet tabs – where sheet name is displayed, lower left corner of workbook window Active sheet – currently on the window and sheet tab is white

5. Layout of Workbook • Columns – • Run vertically on the worksheet • Column headings begin with a letter • Rows – • Run horizontally on the worksheet • Row headings begin with a number • Cell – intersection of a Column and a Row • Cell is identified by a cell reference • Ex: B6

6. Layout - continued • Active Cell – Cell you are working in • Outlined with a thick border • Name Box – displays the cell reference for the active cell appears

7. Navigating a Worksheet ↑, ↓, ←, → Up, down, left, or right one cell Home To column A of the current row Ctrl + Home To cell A1 Ctrl + End To the last cell in the worksheet that contains data Enter Down one row or start next row of data Shift + Enter Up one row Tab One column to the right Shift + Tab One column to the left Page Up, Page Down Up or down one screen Ctrl + Page Up, To the previous or next sheet Ctrl + Page Down in the workbook

8. Entering Text, Numbers, and Dates in Cells • Data entry appears in two locations • Within the active cell • Within the formula bar • Formula bar – • Displays the contents of the active cell • Shows formulas used to create calculated values

9. Continued • Three categories of data • Text data – combination of letters, numbers and some symbols • Number data – numerical value used in a mathematical calculation • Date and time data – common formats for date and time • Text – left-aligned in cells • Numbers, dates, and times are right-aligned in cells

10. Entering Text Select cell to make active Key data Press tab key to move to next cell Press enter to move down to next row If text is larger than a cell, text spills into next cell if empty If cell contains data, data is cut off or truncating (complete text is entered, but not displayed)

11. Entering Multiple Lines of Text Within a Cell Click cell to make active Type first line of text Press Alt + Enter key and type text Called Wrapping Text within a cell

12. Entering Dates • Different standard formats for dates • 4/6/2010 • 4/6/10 • 4-6-2010 • April 6, 2010 • 6-Apr-10

13. Entering Numbers Can be integers, decimals, or negatives Can include Percentage (%) or currency ($) symbols Numbers are right-aligned in the cell If the number exceeds the cell width, ##### appears for the truncated numeric value Increase column width to see entire number

14. Working with Columns and Rows • Columns and rows come with a default width and height • Column and row size may be changed to accommodate text entered into them • Column width and height – determined by number of characters the column can contain. Measured in • Pixel – single point on a computer monitor or printout • Points – font size of characters. • 1 point = 1/72 of an inch

15. Inserting a Column or Row Inserting a new column – shifts the existing columns to the right and inserts the new column to the left Inserting a new row – shifts the existing rows down and the new row is inserted above The inserted rows are the same width and height and the adjacent column or row Insert button is found on the Home Tab in the Cells group Right click a column or row heading and click Insert on the short-cut menu

16. Deleting and Clearing a Row or Column • 2 ways to remove data • Clearing – Removes the data but leaves the blank cells • To clear contents – right click on column or cell and choose clear contents from short-cut menu • Deleting – Removes both the data and the cells • To delete rows or columns – use the delete button in the Cells group on the Home tab

17. Working with Cells and Cell Ranges Session 1.2

18. Working with Cells and Cell Ranges • Cell range or range – a groups of cells • Adjacent range – single rectangle block of cells • EX: A1:G5 (Range Reference) • Nonadjacent range – consists of two or more distinct adjacent ranges • EX: A1:A5;F1:G5 (Range Reference) • Cell Ranges can be selected with the mouse • Hold down the CTRL key to select nonadjacent ranges

19. Moving and Copying a Cell Range Drag and Drop Select the cell or range you want to move or copy Move mouse pointer over border of the selection until the pointer changes shape Click border and drag to new location to move To copy (click border, hold down the Ctrl key, and drag to new location) and release OR Home tab, Clipboard group, click Cut or Copy Button, select new cell, click Paste button

20. Working with Formulas • Calculations are added to a worksheet using formulas and functions • Formula – an expression that returns a value • Formulas begin with an equal sign (=) • Operators (arithmetic operators) performs addition, subtraction, multiplication, division, and exponentiation • EX: = A2 + A4

21. Formulas - continued • Order of precedence – determines the sequence in which operators are applied • Parenthesis • Exponentiation • Multiplication • Division • Addition • Subtraction • Operators with the same level – begin at the leftmost side

22. Entering a Formula Click the cell in which you want the formula to appear Type = and an expression that calculates a value using cell references and arithmetic operators Press the Enter key or press the Tab key to complete the formula

23. Copying and Pasting Formulas Excel adjust the formula’s cell references to reflect the new location of the formula in the worksheet Does not copy the value of the cell

24. Introducing Functions • Function – A named operation that returns a value • Used to simplify formulas • 300 different functions • Use the Sum button (also called AutoSum) • Use Sum Button to insert a function (Σ) • SUM • AVERAGE • COUNT • MINIMUM VALUE • MAXIMUM VALUE

25. Working with a Worksheet • Workbooks come with three worksheets • Worksheets are separate pages of a workbook • Labeled as Sheet 1, Sheet 2, Sheet 3 on the sheet tabs • Worksheets can be added or deleted

26. Working with a Worksheet - continued • To insert a new worksheet • Right-click a sheet tab • Click insert on the shortcut menu • Select a sheet type • Click the OK button • Inserted to the LEFT of the active sheet

27. Working with Worksheets - continued • To delete a worksheet • Right-click the sheet tab, click delete on the Shortcut menu OR • Click delete button arrow in the Cells group on the Home tab, then click Delete Sheet.

28. Renaming a Worksheet Double-click the sheet tab Type a new name for the sheet Press the enter key Sheet names no longer than 31 characters including spaces

29. Moving and Copying a Worksheet Most important worksheets first (leftmost sheet tabs) Least important worksheets at the end (rightmost sheet tabs) To move, click and drag the sheet tab to a new location To copy, hold Ctrl key as you drag the sheet tab to new location

30. Editing your Work • Editing mode – edit the contents of a cell • 3 ways to enter editing mode • Double-clicking the cell • Selecting the cell and pressing the F2 key • Selecting the cell and click in the formula bar

31. Changing Worksheet Views • 3 Ways • Normal View – shows content of the worksheet • Page Layout View – Shows how the page will appear on the page or pages sent to the printer • Page Break Preview – Displays the location of the different page breaks within the worksheet

32. Working with Portrait and Landscape Orientation Portrait Orientation – Page is taller than it is wide Landscape Orientation – the page is wider than it is taller

33. Change Orientation Page Layout Tab Page setup group, Orientation button, click Landscape

34. Viewing and Printing Formulas • Switch to Formula View • Displays formula instead of values • Click the Ctrl + ~ (tilde key) • Scaling – Forces the contents to fit on a single page