Spreadsheet application Software

What is a spreadsheet?

• method for recording, calculating, and visualizing numeric values
• basic spreadsheet is 2-dimensional rectangular grid
• values can be charted and graphed in a variety of ways

Historical significance

• developed in late 1970's by Harvard student Dan Bricklin
• VisiCalc for Apple II was the first
• Lotus 1-2-3 was the most famous
• Excel is now the most widely used
• revolutionized business methods
• revolutionized decision-making
• responsible for business world adoption of PCs
• benefited decision makers (who approve computer purchases)
• operations once slow and clumsy now quick and simple
• inspired creation of new uses
• spreadsheet software was available only on PCs, at a time when PCs were generally used by hobbyists
• word processing was evolutionary by comparison
• benefited mainly clerical staff
• productivity gains modest by comparison

What are spreadsheets used for?

• keeping budgets
• other accounting functions
• recording and analyzing experimental data
• visualization of data
• charting and predicting trends
• answering "what if?" questions
• comparing alternatives
• simple databases
• scheduling
• grade sheets
• your imagination is the limit!

Example spreadsheets (Excel 2003) you can download and play with

• (right-click on the link, then select "Save target as...")
• XYdata.xls (simple data collection, chart with visual curve fit)
• BudgetWithCharts.xls (simple month-by-month budget, many different charts)
• Budget3D.xls (same budget with worksheet for each month, no charts)
• GradeSheet.xls (fictitious grade sheet)
• GradeSheet3D.xls (fictitious grade sheet with worksheets for components)

More details about spreadsheets

• each file is called a workbook
• each grid is called a worksheet
• each column identified by letter (A, B, C, ...)
• each row identified by number (1, 2, 3, ...)
• each box in grid is called a cell
• each cell identified by combined column-row (A1, A2, B1, ...)
• virtually unlimited number of rows (65536) and columns (256, A through IV)
• each cell may contain either
• a numeric value,
• a text label that describes something but is not involved in calculations, or
• a formula for calculating a value or determining a label
• cell range is denoted by upper-left-cell : lower-right-cell.  Example B3:D7 is range of all cells which in the grid formed by columns B through D and rows 3 through 7. In other words, cells B3, B4, B5, B6, B7, C3, C4, C5, C6, C7, D3, D4, D5, D6, D7.
• the formula is a very powerful construct
• always starts with "="
• it may reference other cells.  Example:  =B8 means the contents of this cell are the same as the contents of cell B8.
• it may include arithmetic operators (+, -, *,/,^).  Example: =2*D7+C5 takes the contents of cell D7, doubles it and adds the contents of cell C5.
• it may perform predefined functions (sum, average, ... and many others). To find functions, go to Excel 2007 Formula tab.  Example:  =average(A1:A20) is the average of the values stored in cell range A1 through A20.  Example: =sum(B3:C4)*3 is three times the sum of cells B3, B4, C3 and C4.
• Almost all functions require one or more arguments. An argument is a value input to the function.
• Analogy to mathematical function: f(x) = 3 * x is a mathematical function having the argument x.
• a formula is automatically recalculated any time a cell it references changes
• Automatic recalculation has a ripple effect, because a formula can reference a cell that itself contains a formula
• The IF function is very powerful. The cell value will be conditional. It takes three arguments:
  1. a condition, which is an expression whose value is eithe TRUE or FALSE
  2. the value/formula if the condition is TRUE
  3. the value/formula is the condition is FALSE
• Formula copying is easy
• Copy and paste of formulas have special property
  • Cell references (cell name used in formula/function) are interpreted as relative cell references
  • this means relative to cell position of formula
  • The copied cell reference is adjusted automatically upon pasting
  • example:
  • Columns A through C contain values in rows 1 through 5
  • Cell A6 contains formula "=SUM(A1:A5)"
  • Copy cell A6 then paste onto cell B6
  • B6 will contain formula "=SUM(B1:B5)"
  • Paste onto cell C6
  • C6 will contain formula "=SUM(C1:C5)"
  • etc.
  • Relative to A6, A1 is "5 cells to the left" and that is the interpretation
  • In most cases, this is desirable and very handy!
• Can have absolute cell references that do not change when copied/pasted. Put a $ in front of both the row and column, e.g. $A$1
• You can convert between relative and absolute in Excel by selecting the cell reference and pressing F4
• Formula copying can also be done using the fill handle at lower-right corner of a cell. Select cell to be copied, move mouse to corner, drag handle over pasting range. A little quicker than copy-paste
• Multiple worksheets
• Tabs provided to name and switch between worksheets within a file
• More can be created
• Refer to cell(s) on different worksheet by specifying worksheet name then "!" in cell reference.  Example:  scores!B4 refers to cell B4 in worksheet "scores".  Example:  data!$C$13 is absolute reference to cell C13 in worksheet "data".  Example: rates!$A$1:$A$5 is absolute reference to cells A1 through A5 in worksheet "rates".
• Possibility for 3-D spreadsheet
• A cell (or range of cells) can have an associated name.
  • Select the cell or range, and type the name into the Name Box (located just above the Column A heading).
  • The name must be unique in the workbook and becomes an absolute reference usable from anyhwere without having to give its worksheet name.
• Variety of 2-D and 3-D charting methods
• pie charts
• bar charts
• line charts
• scatter plots
• area charts
• more...
• can use 3-D chart for 2-D data (better visualization)
• choose charting method intelligently
• should be appropriate to what the values represent
• should be appropriate to intended audience
• can be used to exaggerate (e.g. choice of x-y axis scale)
• can read and save data in variety of formats
• older versions of the spreadsheet program
• formats used by other well-known spreadsheet programs
• HTML format (for the web)
• plain text format
• in Excel 2007, highlight Save As, then look at the list of file types
• alternative ways of doing things
• there are multiple different ways to do almost anything
• cell can be referred to by row and column numbers, e.g. B5 and R5C2 are equivalent
• can specify many formulas by either typing or using toolbar and cell selection