1 / 23

What is a Spreadsheet?

What is a Spreadsheet?. A spreadsheet consists of the following items A worksheet divided into rows and columns 256 vertical columns & 65,535 horizontal rows Columns identified by letters (A, B, C, and so on) Rows are numbered (1, 2, 3, …) Cells  An intersection of a row and column

duman
Download Presentation

What is a Spreadsheet?

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. What is a Spreadsheet? • A spreadsheet consists of the following items • A worksheet divided into rows and columns • 256 vertical columns & 65,535 horizontal rows • Columns identified by letters (A, B, C, and so on) • Rows are numbered (1, 2, 3, …) • Cells  An intersection of a row and column • Identified by their column letters followed by their row numbers • Numbers • Labels • Formulas

  2. Anatomy of the Window Workbook name Ribbon Menu bar Title bar Formula bar Active cell Column headings Row heading Gridlines Scroll bars Status Bar Current sheet Sheet tabs

  3. Entering Information into a cell • Select cell where you want to type data • Type a number, label, or formula • Do any of the following • Press Enter • Click Green Check Mark next to formula bar • Press an arrow key to select a different cell • Click a different cell

  4. Excel Concepts • Cell reference  how we refer to a specific cell. • A10 or Data!A10 or [Book2.xls]Sheet1!A10 or C:\MyData\ [Book2.xls]Sheet1!A10 • Range  A rectangular group of one or more cells • Referenced by top left cell separated from bottom right reference by a colon.

  5. More Concepts • Selection • Range of cells, highlighted • Fill handle • Formatting • The manner in which a cell entry is displayed can be changed by formatting the cell. • Font, background, number of decimals, scientific notation, horizontal and vertical positioning, borders

  6. Examples • Filling in a Series of Numbers or a list of dates • Entering and Copying a Formula • All formulas begin with (=) • 3 + 4 * 2  11 or 14? • Refer to values in cells • Copy vs. Move formula

  7. Arithmetic Operator Order • Negation (-) • Exponentiation (^) • Multiplication and division (*,/) • Addition and subtraction (+,-)

  8. Formatting your worksheet • Manually formatting your cells • Number • Alignment • Font • Boarder • Patterns • Protection

  9. More Formatting • Conditional Formatting • Removing Formatting • Eraser too (Under editing)Clear Formats • Adjusting column widths and row heights • With the mouse

  10. Common Formula Calculations

  11. Using cell references in formulas • Referencing a single cell • =B5+B6 • AutoSum Σ • Sum, Average, Count, Max, Min • Referencing two or more cells • Contiguous range =SUM(D3:D5) • Noncontiguous range SUM(D3,G5,X7)

  12. Copying formulas • Excel changes the formula cell reference automatically when for each row or column • Ctrl+C then Ctrl+V • Editing a formula • Ctrl+` to show all formulas • Absolute, Relative, & Mixed References • $A$1, $A1, A$1, A1

  13. Naming cells and ranges • Name box • Jumping to a named cell (downward-pointing arrow) • Acts as an absolute reference

  14. Picking a Function to Use • Entering a Function Directly • Example =Sin(A1), =Pi(), =Degrees(A1), =Radians(A1) • Insert function button • Function Palette • Entering Formulas by Pointing

  15. Finding where a formula gets its data • Formula Auditing toolbar • Tracing Precendents • Tracing Dependents • Adding Comments

  16. Printing a Worksheet • Using Print Preview • Printing part of a worksheet • Highlight cells that you want to print • Page LayoutPrint AreaSet Print Area • Page Setup • Under print preview • Portrait, Landscape, Fit to … • Margins • Header/Footer

  17. Paste Special • RAND() • Paste Special

  18. Modifying Worksheets • Insert/Delete rows and columns • Link worksheets w/ formulas • Split a worksheet (under view tab)

  19. Formulas • Editing a formula • Ctrl+` to show all formulas • Absolute, Relative, & Mixed References • $A$1, $A1, A$1, A1 • Using named cells in formulas (names are absolute references)

  20. Naming Cells using labels • InsertNameCreate • Names are not case sensitive

  21. Picking a Function to Use Commas separate each argument • Insert function button • Function Palette • Function Arguments • =Pi() • =COS(Pi()) =SQRT(A2/2) • =ROUND(A2,2) • =SUM(A1:A10) or =SUM(A1:A10,B3,B4) • Entering Formulas by Pointing

  22. Trig Functions • Trig functions (always in radians) • RADIANS, DEGREES, SIN, COS, ATAN, … • Exponential Functions • EXP, LN, LOG • Rounding Functions • ABS, CEILING, EVEN, FLOOR, INT, MROUND, ODD, ROUND, ROUNDDOWN, ROUNDUP, TRUNC

  23. Errors in Formulas • ######  Column is too narrow • #DIV/0!  Formula divides a number by zero. Black cells have a value of zero • #NAME?  Formula containes a function name or cell that Excel does not recognize • #REF!  Formula refers to a cell that is not valid • #VALUE!  Formula refers to a cell that Excel cannot use in calculation • Circular Reference  Formula refers to the cell containing the formula

More Related