Advanced Spreadsheets

• Formatting


  Lets us use colour and other formatting to make our spreadsheet easier to understand
• The value stored (e.g. 3.456) may be different to the way that it is displayed (e.g. £3.46) - which lets us keep the accuracy but also display data in a helpful way
• Conditional formatting


  Lets us format cells depending on some kind of condition/value/calculation, and means that the formatting can change depending on the calculations being done
• Formatting will always be correct, with no need for any manual updating
• Conditional formatting is widespread for drawing attention to particular items based on some kind of rule
• Functions


  Routines that take information and return a single calculated answer
• Examples of typical functions

  • AVERAGE
  • MIN
  • MAX
• IF function


  Allows a choice between two courses of action e.g. =IF(score>60, "Well done", "You need to try harder")
• VLOOKUP function


  Uses a supplied value to find a row in a table, then uses information from another column on that row
• COUNT functions


  • COUNT
  • COUNTIF
• RANK function


  Looks at a range of cells and automatically works out which order they should be in
• Relative cell reference


  e.g. A1
• Absolute cell reference


  e.g. $A$1
• Cell naming


  Giving a cell a name, e.g. 'tax_rate', so formulas can use the name instead
• Cell naming also gives the extra benefit that you can tell the spreadsheet to jump to the cell with that name, making navigation easier
• Validation


  Using rules to check that entered data is sensible (easy for a computer)
• Verification


  Checking that data is actually correct (hard for a computer)
• Form controls


  Widgets like check boxes, radio buttons, drop down lists, sliders
• Form controls can make spreadsheets easier to use, more visual, and more like an application than a spreadsheet
• Graphs & charts


  • Neater
  • Drawn without error
  • Can update data without redrawing
  • Can tweak presentation to best illustrate the point
• Pie charts


  Good for showing proportions and ratios, poor at showing the scale of numbers
• Line graphs


  Good for showing trends over time
• Scatter graphs


  Best to show the correlation between things
• Whisker graphs


  Good for showing tolerances or things that have a range that changes over time
• Graphs don't have to start from zero, you can change the start & end points for each axis, how these are labelled, etc.
• Macros


  Give you a way to do a whole series of steps at the press of a button
• Simple uses of macros


  • Typing out a long name or address
  • Navigation buttons to jump to different parts of the spreadsheet
  • Applying particular filters or sort orders to sets of data
• There are usually many ways that problems could be solved with spreadsheets, and there is almost always someone who has done something similar before