Overview of productivity applications

• Word processing and associated tools (e.g. Word)
  (templates, spell check, grammar check, thesaurus, mail merge, speech and handwriting recognition)
• Desktop publishing and associated tools (e.g. Adobe PageMaker and InDesign, QuarkXPress)
  (word processor, page layout, graphic design, electronic publishing, PDF)
• Spreadsheet and associated tools (e.g. Excel)
  (automatic recalculation, formulas, charts)
• Financial management (e.g. Quicken)
  (more specialized than spreadsheets with focus on accounting)
• Mathematical and statistical processing (e.g. Mathematica, Maple, SAS, SPSS, Minitab)
• Graphic design (e.g. PhotoShop, PaintShopPro) -- see chap 6 notes
• Computer Aided design -- CAD/CAM (e.g. AutoDesk) -- see chap 6 notes
• Presentation software (e.g. PowerPoint) -- see chap 6 notes
• Audio and video processing(e.g. ??) -- see chap 6 notes

Focus on Spreadsheets

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 (did you ever have a Magic 8 Ball? See www.thisfunsforyou.com/htdocs/flash/8ball.php)
• 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
• word processing 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 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 textual label, 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's Insert then click Function to see list).  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.
• it is automatically recalculated any time the value changes in a cell that it references
• Formula replication is easy
• Copy and paste of formulas have special property
  • Cell references (labels) 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)", sum of A1 through A5.
  • Copy A6 then paste over B6
  • B6 will contain formula "=SUM(B1:B5)"
  • Paste over C6
  • C6 will contain formula "=SUM(C1:C5)"
  • etc.
  • In most cases, this is desirable and very handy!
• Can have absolute cell references that do not change when copied/pasted (use $, as in $A$1)
• 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.
  • Names can also be given to formulas and values that exist outside of cells. From the Insert menu, highlight Name then select Define.... In the dialog that appears, type the name into the Names in workbook: box, and the formula or value into the Refers to: box.
• 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)
• macros
• use for repeated tasks
• stored command sequences (programs!)
• you can record, save and run your own
• Excel macros recorded in Visual Basic
• hiding place for some viruses
• 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, click File then Open, then pull down the "Files of Type" list
• in Excel, click File then Save As, then pull down the "Save as Type" list
• 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