Things to do with Spreadsheets:
- accounts
- text handling - especially indices and bibliographies
- numerical data-handling, pattern spotting and theory-testing
- graphs and charts
- surface mapping and GIS
- non-narrative texts
- flowcharts and concept maps
- timetables and project planning
- scientific modelling
- form design - inc. 'writing frames' for classroom use
- puzzles and quizzes (including 'self-marking' ones)
- generating reports
- simple databases and preparation of data for database or statisical programs
- designs for decorative work - mosaics and cross-stitch patterns
- ... and more I haven't thought of ...
In addition, the advanced features of Excel allow you to build in hypertext facilities and comment/review features.
Using Excel's Advanced Features
This page contains links to 'web-download' a series of Excel Spreadsheets. The first set are concerned with using the built-in functions of Excel (formulae, conditionals etc).
- Alternative Reality Kit: A science application designed to explore projectile motion
- Magic Square: An exercise for you to try using conditional statements
- Lulworth Cove: Topographical Modelling with Excel This is a demonstration of how to take an OS Map excerpt and generate surface maps from the a dataset of contour interpolations and spot heights. Every square in the grid represents a 200m x 200m square, and I tried to gauge the height at the centre of the square. The map has been inserted as a 'background' - you can do this from Format->Sheet in the menubar.
- Diagnostic Assessment for Teachers with Excel
Using Excel Macros
The second set include VBA macros. When you download them you will be given the option of 'opening' or 'saving to disk'. Save them to disk if you want to potter around with them. When you are asked if you wish to 'enable macros' select 'Yes'.
Do make sure that you are looking at the files with the full version of Excel rather than the 'Excel Viewer'. In the latter case, you will be able to use the macros, but will not be able to 'step in' to them to inspect the VBA code.
-
Macro Demonstration 1: Automatic
form-filling: this macro uses the 'Auto_Run' facility so that it will
be called every time the sheet is opened. It is made up of three main
sections:
- Some recorded content, placing text in boxes and formatting it
- A conditional test - has the users filled it in previously? (Coded by hand in VBA)
- Two dialogue boxes which prompt the user for input and push it into cells.
Finally there is a reminder to save work using a 'Message Box' and the macro finishes until next time the sheet is opened. Note: You are probably better off 'saving' this rather than 'opening' it if you have a slowish internet connection. This is becuase the Auto_Run may operate before you see the spreadsheet - the macro (txt only) loads opens faster than the graphically complex spreadsheet.
- Macro Demonstration 2: Streamlining the charting process: In this case, I recorded a macro while selecting a bar chart from the 'chart wizard'. I then spliced this onto a piece of code so that any selected area would be turned into a bar chart (if possible). This is not 'user-proofed' as it stands - how could you make sure that the user must have selected appropriate data?
- Macro Demonstration 3: Automating a process: here I set up a chart first with a blank table to be filled in. I found the VBA function which acts like a 'wait' command in Logo and used it to pop up an input box at regular intervals. This could be used if you wanted to prompt children to collect data during a science experiment, for example.You could combine this with the previous example so that once they have entered the final set of data and confirmed it, an appropriate chart is automatically drawn.