Introduction
This is the first of three weeks of exercises with EXCEL, the WINDOWS spreadsheet which complements WORD and ACCESS, the relational database management system which you will work with in weeks 7-10. The objective of this week is for you to:

• Understand the basic features and commands of EXCEL.
• Be able to construct a basic worksheet incorporating formulas.

First, what exactly is a spreadsheet? Briefly, as word-processing programs are to words, so spreadsheets are to numbers. Spreadsheets, also known as financial planning packages, can be thought of as electronic analysis paper and have a screen display w hich appears similar to that of graph paper, where each box is called a cell, and each cell is referenced individually by its column letter and row number, e.g. A1 is the top left-hand cell. A spreadsheet is thus a software tool which enables you to store , organise and analyse numerical data. They have a variety of uses: from simple models of household accounts to more complicated statistical analyses of unemployment statistics. The department has long been using them for teaching purposes and, as a conse quence, has a huge range of spreadsheet datasets (known as worksheets) which are available Data files for you to use.

Resources

• Students' Excel.
• Lewis and Lloyd-Jones, Using computers in history: a practical guide, pp. 27-34 on functions of spreadsheet; pp. 40-69 on using EXCEL; pp. 70-85 on charts (EXCEL's terminology for graphs).
• Documentation from the Computing Service: Getting started with EXCEL 5; also (but available as WORD files), Introduction to EXCEL version 5, Practical examples of using Excel 5.0 and Using an Excel spreadsheet to calculate your accounts (Hands-on computing course document), all available for downloading from List of tutorial documents.

Exercise 1: EXCEL basics
If you have never used a spreadsheet before work your way through Getting started with EXCEL 5 to the section on printing but no further for the time being.

At this stage you need to acquire an:

• understanding of the components of a cell address.
• be able to enter data, being clear of the distinction between:

1. Numerical data;
2. Text;
3. Formulas such as =sum(B2:B7) which if inserted in cell B8 would return the total of the cells in the range between B2 and B7.

• be able to copy, move, erase and format cells.
• know basic file management (opening, saving and closing of files), all of which works on exactly the same lines as WORD. NB Also, just as in WORD, when closing down EXCEL do ensure that you save your file to your floppy disk (EXCEL automaticall y adds the .xls extension to the file name you select) and do not take your floppy disk out of drive A: until your EXCEL session has ended and you are back at Program Manager.
• be able to print out a range of cells.

Exercise 2: building a basic worksheet
Build a fictitious worksheet of agricultural output (3 goods, five years), including totals columns and rows and using the =sum command to produce those totals automatically. Your worksheet (assuming Product is entered in cell a1) should look like:
 

Insert data of your choosing in the block of cells B2:F4, and then formulas for column totals in cells B5:F5 and row totals in cells F2:F4. If you follow Students' Excel pp. 10-11 carefully on copying formulas you will need only to write two for mulas (for cell B5 and for G2). Notice as you copy a formula how EXCEL automatically adjusts for the increment of row/column.

Once you have your formula in place you will notice that changing any of the data in B2:F4 has an automatic effect on the totals for that product and that year.

If you have entered data as a mixture of integers and decimals you will notice that the data as displayed can appear rather messy, as below:
 

In the following, we have tidied up the display by highlighting the data cells (including totals) with the mouse and then changing their properties by clicking on the right mouse button, selecting Format cells, Number and 0.00, and then selecting Alignment and Right.
 

A historian looking at this data might want to know what the average was for each product and for each year. Change your worksheet (by inserting a column between 1704 and TOTAL, and a row between Rye and TOTAL) so that it look like this below:
 

In your new column and row use the formula =average to obtain averages (unweighted means). Your first (columbn) formula would be =average(B2:B5) for the average for 1700.

Finally, when you can get access to a printer, print out your worksheet and then save your file to your floppy disk (following the usual rule, Gordon Brown's file would be gobr05.xls).

To IT-MA home page
To Department of Historical Studies home page.

(c)R. Middleton 1997. Last modified 4 September 2000.