Excel

• Excel

• Excel Tutorial on wikibooks
• Working with Formulas

• To sort by column (e.g. student no)
  1. Select all
  2. Data - Sort - Sort by column

• If two separate lists (like CA1 and EC1), need to sort by Column B to combine into one list:
  1. Get rid of header lines (different format, no column B)
  2. Click any cell
  3. Data - Sort - Sort by Column B

• To print spreadsheet with gridlines:
  • Page Layout - Gridlines - Print

Wide content

• To have wide content in a cell:
  • Discussion
  • Put

     ="" 

    in the column next door. Then the cell never overwrites it.

• If part of cell contents is hidden: Select cell - Wrap text

• If some cells span multiple columns:
  • Select cell - Home - Merge & Center - Unmerge cells

Formulae

• Formulas
  • Formula is something like:
    =(E80*0.25)+(F80*0.75)
  • round to n decimal places:
    =round(E2,n)

• Count passes/fails in a column
  • Say column is mark out of 2
  • Count number of people who scored 2, something like:

     =countif(f:f,"2") 

  • countif
  • "range" of just one column F is F:F

• Green triangle
  • Means formula error
  • Select all - Warning icon - Dropdown - Ignore errors

OpenOffice Calc

• OpenOffice wiki
• OpenOffice.org Calc
• Creating formulas

• To sort by column:
  1. Select all (Ctrl-A)
  2. Data - Sort by - column x - then by - column y

• To print spreadsheet with gridlines:
  • Format - Page - Sheet - Print - Grid
• To print portrait/landscape:
  • Format - Page - Page - Orientation

Wide content

• To have wide content in a cell:
  • Format - Cells - Alignment - Horizontal - Filled

Formulae

• Formulae in OpenOffice - Type them into formula input field at top, not into cell itself.

• round to n decimal places (note semi-colon):
  =round(E2;n)