Graham's Guide to Software for Archaeologists.

by Graham McElearney.

Down here at 'assemblage central', we rather thought that, although you are most likely reading this through a Web browser of some kind and are therefore sitting at a computer which is hooked up to the Internet, we would provide some overall description of the wide range of software that the modern day archaeologist is expected to use. This ranges from the generic 'office' type software which many of us use from day to day, right through to the more specialised or 'vocational' types of software, such as Computer Aided Design and Geographical Information Systems. As well as describing what these various types of software do, I will try to highlight how they can be of specific use in archaeology. In this edition I will start off with one of the most ubiquitously used software types of all: the spreadsheet.

Spreadsheets -- An Introduction.

Spreadsheets are remarkably common. It could be argued that the emergence of PC based spreadsheet software in the late seventies was one of the major driving forces behind the rapid growth in popularity of 'personal computing' that has ensued since then. So what then is a spreadsheet? Well, the flexibility and range of a spreadsheet makes it a little hard to define in words alone, but broadly speaking, it can be described as a kind of 'electronic ledger book', in which you can store and manipulate (often numerical) data, and which generally makes repetitive calculations easier than with an electronic calculator. That explanation alone, I admit, is a tad abstract, and it is much easier to visualise a spreadsheet by seeing the real thing. Click here to see a screenshot from a typical spreadsheet package.

Please Note: The examples shown here are from Microsoft Excel -- this is not an endorsement in any way, but it is the most common spreadsheet around.

Apart from the standard Windows-style menu and tool-bar along the top of the screen, you will notice that the majority of the screen is dominated by a grid of rows, which are named numerically down the left hand side, and columns, named alphabetically along the top of the 'sheet'. Each one of the boxes formed by these rows and columns is known as a 'cell', and most importantly, each cell has a 'cell reference', which is like a grid co-ordinate describing the location of the cell within the sheet. In the example given, the Monthly Total for September is located in cell F14, that for October in G14 and so on.

So far, our example looks like little more than a glorified bank balance -- this is not so. The real power of spreadsheets is that they can perform operations on the information contained in the cells, and that the content of some cells can be the result of such operations.

In this example, the cell F14 contains the arithmetic total of cells F5, F6, F7, F8 and F9 (if you look in the area below the tool-bars you might notice it says =SUM(F5:F9), which is Excel speak for adding up these five cells and displaying the answer in F14). Now this in itself is a fairly simple calculation, but where the spreadsheet really comes into its own is that the data is dynamic -- if we were to change September's van hire costing to 250, the monthly total would be incremented accordingly.

We can take it many stages further than this; our Staff Costs figure is in this case derived by multiplying the number of staff employed (F8, G8) by the Cost/Excavator cell (D12), so we can rapidly visualise how independently altering the variables of number of staff employed, their rate of pay and the transport costs, all effect the overall Monthly Total. Although this is a very simple example of spreadsheet use, it becomes readily apparent how they can be useful to those who have to repeatedly fiddle about with numbers -- an accountant's dream tool.

More Advanced Functions.

The example above just uses simple calculations involving adding and multiplication. Many real world examples require something more sophisticated. Many spreadsheets now come with a whole suite of numerical functions that can be used in calculations. Of particular use are the basic statistical functions that we use when we come to that horrible moment of truth whereby we try to convert our semi-complete and flawed datasets into what passes for scientific fact. More complex numerical models, for example those predicting population dynamics, can also be created, again giving the kind of dynamic modelling displayed in our example.

Visualising Data.

Another great use for spreadsheets is depicting charts and graphs, as it is often much easier to understand a graphical representation of data than a raw listing of numbers. Creating graphs in a package like Excel is a real cinch, thanks to the 'Chart Wizard'. This basically guides you right through the procedure, asking you what data you want to plot, which data for the x and y axes, chart style etc., and then draws it for you as shown in this second screenshot. Again, as in the first example, the chart is dynamically linked to the data itself, so that if you change the underlying data, the chart automatically updates itself as well.

On the basis of our excavation evidence, we can graphically express the marked increase in popularity of metallic headgear between the Neolithic and Anglo-Saxon periods.

Although we have once again used a very simple example of charting data, with a bit of imagination it can be used to great effect. Survey data for example, if structured correctly, can be used to produce an elevation model using a 3D Surface type chart.

Automating Tasks.

If you're using a spreadsheet a lot, and doing the same basic procedures over and over again, you may find it's worth your while learning how to automate these repetitive jobs. The simplest way to do this in Excel and many other packages, is to record what's known as a 'macro'. A macro is basically a list of commands and functions which the spreadsheet stores for future use and, as such, it is in fact a kind of computer program. You don't however need to know anything about computer programming to make and use your own macros -- all you need to do is tell the spreadsheet to start recording your actions, go ahead and do what you want to automate, and then tell it to stop recording. After giving your macro a name, you can use it any time you want in the future.

For those who aren't afraid of a bit of programming, you can go in and edit your macros to tweak them to your exact needs, or indeed, write them from scratch. The syntax of the actual macro language (the commands that do the actual work) used to be notoriously awkward but, certainly in the case of Excel, have been somewhat simplified and made more compatible with the equivalent macro commands used by its stable-mates Word and Access.

When and When Not to Use a Spreadsheet.

Because spreadsheets are so flexible and can do so many different things, it can be hard to know just when they are appropriate and when not. In summary, I have provided a few Do's and Don'ts:

Excel, Chart Wizard, 3D Surface, Word and Access are all registered trademarks of Microsoft Corporation.

About the Author

Graham McElearney is a Multimedia adviser in the University's Teaching and Learning Development Unit. He specialises in the use of multimedia and Computer Aided Learning in education and, as a sick sort of hobby, is also doing a part-time PhD in the use of geographical information systems and multimedia in archaeology.

© Graham McElearney 1997

Go on, e-mail assemblage today!

© assemblage 1997