1 / 11

Spreadsheet concepts

Discover the fundamental concepts of Excel spreadsheets, such as cells, formulas, formatting data, and more. Maximize your efficiency.

bryanp
Download Presentation

Spreadsheet concepts

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. Spreadsheet concepts • not a word processor, not just a single text-based workspace • workspace composed of rows and columns • Cell = intersection of a row and column • Cell can contain: • Constants ( values do not change unless you edit them) • Text (labels) • Number (numeric values) • Date/time • Formulas (values change if you change the contents of reference) • calculated result based on reference to one or more cells

  2. Concepts (con’t) • Excel worksheet: single page workspace • rows 1 thru 65,536 (216 = 17 bits) [9 bit x 17 bit addr] • columns A thru Z, AA..AZ, thru IA..IV (256=28) • Excel workbook: • file containing one or more worksheets • References: (used in formulas) • Relative address (A1, J23) adjusts to changes • Absolute reference ($A$1, $J23) is static addr

  3. Terms to know: • worksheet vs workbook • Excel’s workspace (window) includes: -title bar -menu bar -toolbar(s): standard, formatting, chart, etc… -name box -formula bar -worksheet window: -frame, cells, tabs, scroll bars -status bar

  4. Terms, etc… (cont): • cell • cell address = • column letter, row number • current (or active) cell • sheet tab • navigation: • Ctrl + Home = makes A1 the current cell • End,  = lower right corner of worksheet (IV65536) • End,  = last row in worksheet (row 65536) • F5 (GoTo) = type in cell address • Esc = to quit current operation without changing values

  5. Concepts to know… • Entering/ displaying/ modifying data – • display cell vs. formula bar value • columns or rows (height, width, auto-fit, hide, unhide) • adding/deleting rows/columns/worksheets • selecting rows/columns/cells • select or drag with mouse • shift+ for range vs. ctrl+ for non-contiguous cells • notice status bar and name box during selection process • shortcut keys: ctrl + [cut=x / copy=c / paste=v] • AutoFill – use fill handle to complete a series • tools\options…\custom lists – to add new lists

  6. Concepts (con’t)… • Format data – (by cell, row, column, selection) • “Format\Cells…” • Number – • general, currency/accounting, date/time, text, custom… • decimal places, profit (loss), color, special formats • Alignment – align, wrap, merge, shrink • Font – (same as other GUI apps) • Border – apply styles by cell or selection • Patterns – cell color, shading, patterns • Protection – lock or hide selection (must protect sheet to implement) • labels (text) • wrap text to fit in narrow column • merge adjacent cells to span several columns

  7. Concepts (con’t)… • Format groups of cells…to enhance the look • Add borders to enhance data areas • Add color, bold, italics, etc… • Add useful titles • Use font variations • Text: • merge cells to group columns with a heading • Numbers: • align numbers to right • use special number formats • apply different format to subtotals/totals • Insert page breaks

  8. Concepts (con’t)… • Format the worksheet/workbook… • “File\PageSetup…” -- (same as Setup… in print preview) • Page – allows print size adjustment of document by % • Margins – set to actual value or drag to adjust on print preview • Header/Footer – use automatic functions or customize • Sheet – • set print area • set row and column headers to repeat on secondary pages • turn on gridlines, etc… • direction of overflow pages • “File\Print Preview” • Zoom • Setup… • Page break preview

  9. Formula Concepts -- • Formula… =function(cells or cell range) • all formulas begin with “=“ equal sign • built-in “functions” (predefined formulas in excel) • financial, statistical, date/time, text, etc… • contain relative and or absolute references to cells • (F4 to switch through relative/absolute references) • four types or operators: • Arithmetic ( ) ^ * / + - order of precedence • Reference – indicates a selection or range of cells… (comma, colon) • =SUM(B4:F4) =SUM(B4,M6) • Comparison – tests relationship between two items… = < > • IF (C3<D6, “Yes”, “No”) • Concatenation – joins text entries into one… & (ampersand) • =C4&C3

  10. Formula Concepts (con’t) -- • 3 ways to enter formulas – • Type an equation into a cell • when equations are simple • Type-and-point in cell • when cells are easily visible • Formula wizard • when formula is complex or unfamiliar • Copy formulas with Ctrl+c ; paste with Ctrl+v • Or use fill tool (AutoFill) to replicate across cells • Displaying formulas instead of results – • “tools\options\view”…. check windows-options\formulas

  11. ** Homework assignment ** due June 30th to review in class • Read your e-mail on osprey • Excel2000 Essentials, • Project 5, Challenge Exercise (page 132-134) Challenge exercise # 1-4 • Project 6, Challenge Exercise (page 161-162) Challenge exercise # 1-4 • The files you need are • XL1-0505.xls and XL1-0603.xls

More Related