1 / 46

Mastering Spreadsheet Functions: A Comprehensive Guide to Data Management and Analysis

This guide explores the essentials of spreadsheets, covering array structures, data sorting, formula creation, and cell formatting. Learn how to manage rows and columns effectively, apply common functions like SUM and AVERAGE, and utilize features like conditional formatting and charts. Discover absolute and relative referencing to enhance your computations and enhance readability through formatting options. Whether for personal use or professional tasks, this resource equips you with the skills needed to efficiently handle various data management challenges.

odessa
Télécharger la présentation

Mastering Spreadsheet Functions: A Comprehensive Guide to Data Management and Analysis

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. An Array of Cells • Spreadsheet is an array of cells • Lines are part of GUI (delineate cells) • Some entries appear to “straddle” cells

  2. Sorting the Data • Sorting is common operation • First, highlight cells

  3. Sorting the Data • Sorting under “Data” tab • Order can be ascending or descending

  4. Sorting the Data • Sorting algorithm orders by first letter • Each cell entry is “atomic”

  5. Adding More Data to the List • Can format entries as in word processors • italics, bold, fontstyles, font sizes,justification, colored text and so on • Formatting ops under “Home” tab

  6. Naming Rows and Columns • Naming scheme • Columns use letters • Rows use numbers

  7. Naming Rows and Columns • Allows us to refer to a • whole column by letter • entire row by number

  8. Naming Rows and Columns • Reference block of cells (cell range) • E.g., “B2:D7”

  9. Headings • Often give headings for rows and columns

  10. Common Spreadsheet Operations

  11. Computing with Spreadsheets • Usu. process numerical data • Numerical data is usu. associated with text

  12. Writing a Formula • Formulas start with “=” • “= F2 * 0.621” • Operators: +, -, *, /, ^

  13. Writing a Formula

  14. Writing a Formula • Formulas are recalculated when referenced cells change in value • H2 holds a formula, not text or a number • “= F2 x 0.641”

  15. Repeating a Formula • Would like to repeat computation down column • Enter them like first • Copy/Paste • Filling

  16. Copy/Paste • Select cell (^C) • Highlight destination range • Paste (^V)

  17. Copy/Paste • Note updated references • Original cell: F2 * 0.621 • Next cell: F3 * 0.621 • …

  18. Filling • Filling is another way to copy info. • Note “fill handle” in lower-right corner

  19. Filling • When handle is grabbed, it becomes a + • “Pull” handle down column (or across row)

  20. Transforming Formulas: Relative Versus Absolute • Spreadsheet automatically transforms formulas as it pastes/fills them • Two types of cell references • Relative (F2) • H2 = F2 * 0.621 • Absolute ($F$2) • H2: = $F$2 * 0.621

  21. Relative Versus Absolute

  22. Relative Versus Absolute • Absolute references aren’t adjusted • Four cases for references • F2 • $F2 • F$2 • $F$2

  23. Cell Formats • Readability • E.g., # digits

  24. Typical Formatting GUI

  25. Cell Formats • GUI options • Type of info (Category) • Number • # decimal digits • “1000s” separator • Display of negatives

  26. Functions • Common summary operations • sum • average • max • =max(J2:J7) • min

  27. Functions • fx: insert function • Computation value inherits formatting of cell • Dragged fn brings its formatting with it

  28. Filling Hidden Columns • Hidden columns between G and J • Fns still present

  29. Charts • Graphical representation • Select range • Insert tab • Various chart types

  30. Charts • Chart types • Headings detected • Legend • Horizonal axis • Value range determined

  31. Daily Spreadsheets • Use of spreadsheets • Performance in exercise program • Budgets • Record of favorite team’s successes • Save records generated while online banking • Address books

  32. Solving a Problem of Personal Interest • Scenario • Time Zone Cheat Sheet • Internet chatting with friends

  33. Series Fill • Handling of days, dates, and times • Adding 1 to Sunday results in Monday • Adding 1 to January 31 results in February 1 • Adding 1 to 11:00 pm produces 12:00 am • Fill handle or “Fill Series”

  34. Time Zone Cheat Sheet

  35. Getting Started

  36. Solving a Problem of Personal Interest • Scenario • Paying Off Loan • 5% interest rate • To decide how much to borrow, create a spreadsheet of monthly payments required for different amounts borrowed for different times

  37. Paying Off a Loan Initial Setup

  38. Paying Off a Loan • “Payment” (PMT) computation • Inputs • Monthly interest Rate • Number of payments (Nper) • Present value or the amount of the loan (Pv)

  39. PMT GUI

  40. Conditional Formatting • Conditional formatting • Format dependent on criteria • Home tab

  41. Tab-Delimited Data • Import foreign data • Tab-delimited text M# Name Major 1111 Homer Simpson Nuclear Eng. 2222 Lisa Simpson Music • Can copy and paste into spreadsheet

  42. Tab-Delimited Data • Copying an HTML table • Depends on browser

  43. Arranging Columns • Manipulating rows and columns • To re-arrange columns • Insert a new empty column • Cut and paste from old to new

  44. Summary • Array of cells • Cell referencing • Absolute, relative, ranges • Data types • Formulas • Formatting

More Related