Spreadsheets for people who don’t already know that they already know how to read them

(new 17 May 2006)

“If you’ve never opened a spreadsheet before think of it as the electronic equivalent of a cardboard filing index card with ruled columns or else as a printed page designed for elementary bookkeeping with only one number or piece of text information (which can be several words) at the intersection of each row and column.”

NB: Worked example to be added

A major point of Windows is that different software applications are designed to work, “look and feel” as much the same as possible so that familiarity with one is transferable to another.

If you have any sort of standard basic entry-level computer software package the chances are it will include a spreadsheet as well as familiar software for writing letters and manuscripts. In Microsoft, this will be “Excel” to accompany the familiar “Word” text software. It is assumed that you have access to Excel as that is much the most common thanks to the global predominance of Microsoft but all other spreadsheets which operate in Windows such as Quattro Pro operate in so similar a manner that it has been the subject of extended court proceedings which may yet be unresolved for ought I know.

If you’ve never opened a spreadsheet before, think of it as providing a computer screen organised just like the old traditional filing index card with its printed lines to which ruled columns have been added or alternatively, printed pages designed for double entry book keeping at the most rudimentary level. Typically, for maritime information the compiler will put all the information for a particular ship in one row of the “card” with the columns representing different types of information. Thus the ship’s identifying number may appear in the first column, its name in the second, its type in the third, tonnage in the fourth and so on.

In its most basic application a spreadsheet is simply an electronic filing index card with the advantage that you can erase and replace a number or word by simply putting the mouse pointer at the intersection of a row and column and overtyping. You can remove, move and insert rows and columns without using scissors and glue on the cardboard and making a right old mess in the process.

If someone has already created one and you just want to read what’s in it then all you need do is open Excel just as you would Word, and use the familiar menus and symbols at the head of the screen to open the file containing the information of interest. In fact, you can simply go to “My Computer” in Windows XP and double-click on the file and Windows will open it in the right software automatically (use Windows Explorer in Windows 98 and whatever the Windows 2000 counterpart is). If someone emails you a spreadsheet you probably only have to double-click on it in the attachment row of the email for it to open automatically in the right software.

Having opened the file, the Page Down, Page Up and arrow keys on your keyboard do exactly what you would expect them to do. Tab will also move you across a row. (Hold these keys down and they will motor around at a rate of knots.) The sliding blue bars on the right hand side and bottom of the screen do just what they do in Word.

Edit > Find (or Ctrl F*) works exactly the same way as it does in Word. However, it will generally help to specify the column/s or row/s of the screen you want searched. This is different from Word but you should not find it difficult.

* On most new but not all old keyboards. If you have this facility there should be “Find” on the front edge of the key, similarly Copy on the C, Cut on the X, Paste on the V, Save on the S and some other commands on some other keys, all to provide global shortcuts in conjunction with the Ctrl key.

The standard spreadsheet layout will have a border round the “index card” part of the screen with numbered rows and capital letters identifying the columns. Just put the mouse pointer on the row number or column letter of the row or column in which you want to search and left click once. The row or column will be highlighted (probably in blue). Just use Find as you would in Word. If you feel confused or actually want to search the entire file use the intersection at the top and left borders of the screen surround to highlight the whole file to be searched. Most often you’ll want to search whichever columns contain ships’ names or numerical identifiers to find a particular ship but the procedure is equally applicable to all.

Unless it is a very small file it will pay to use Find.

Excel spreadsheets can contain more than 65,000 rows and hundreds of columns. However, much over 30 or 40 columns (and perhaps more like 15 initially) and you may have trouble keeping a “map” of the “filing card” in your head. I don’t recommend using a lot of columns but don’t be scared by the number of rows. With the correctly spelled name (or trial and error with the possible spelling variations) or the correct numerical identifier “Find” will find the ship record you want before you can blink. For convenience’s sake, I generally structure my spreadsheet indexes in batches of 50,000 rows as it makes it easier to remember which one something else will be in.

This really is all you need to know into order to read information stored in a spreadsheet.

Even if you only know how to read and write letters on the computer and never learn anything more about spreadsheets, reading information stored in spreadsheets is not denied to you.

All you need is your existing knowledge of Word and what I’ve outlined above.

If you are going to use spreadsheets much, you will find it advantageous to learn some of the additional tricks like temporarily hiding some columns to make it easier to concentrate on others, rearranging the order of the columns for convenience, sorting the rows so that you can group together ships of particular types (rig, place or period of construction etc) in order to compare them, count their numbers or calculate their average size, and other items in the CS section of my menu cover more elaborate applications but you need never know more of any of them in order to be able to read “like a book” spreadsheets that other people have compiled and processed. Some of these are covered in the following section. To hide some columns temporarily select them with the mouse as you would for Find, then select Column > Hide from the Format menu at the head of the screen. To bring them back, select the columns either side of the hidden ones and select Column > Unhide from the Format menu. As long as you do not Save, you cannot do any damage.

No member of the maritime history or genealogical communities who can use computers at all need be or feel excluded from the use of research resources stored in spreadsheets by fear of unfamiliar software, embarrassment about admitting ignorance or of having to ask a grandchild to help.

If you want to go further, the absolute basics of adding to a spreadsheet

All you actually have to know in order to contribute to and participate in data recording projects involves only a few simple extensions to the small amount you need to know to just read a spreadsheet. There are many tricks to the trade and variations on ways of doing things that I myself still see in a fresh light after more than fifteen years of intensively using spreadsheets, sometimes for ten hours a day for days on end, but the basics for just adding further information to a spreadsheet are simple and intuitive.

Rule Number 1, which should work wonders for a novice’s confidence, is

● Always work on a copy and you can’t hurt the original

If you start with a file called FRED open it and immediately save it as FRED1 and make your additions to that (Save and Save As are exactly the same as in the Word menu). Follow that with FRED2 and FRED3 etc at frequent intervals and at worst you can only ever lose a small amount of work. When you’ve finished your additions and checked that nothing went awry along the way save the final version and a slightly differently named backup version (eg FRED A and FRED B) carefully and scrap the rest. (Rule Number 2 is to keep your experiments and working stages in a special file folder called “Just Playing”, after your cat or whatever works for you. Then they won’t clutter up the important stuff and you can easily get rid of them all and start afresh at any time.)

To add on the bottom of something existing, just go to the bottom of the worked example and type in the “active cell”, if necessary moving your position with the mouse pointer or arrow keys. Any wise project co-ordinator will have given you a worked example.

To go to the bottom of the existing data, use the mouse on the sliding blue bar at the right side of the screen. (If you can’t see any empty row when you get there just hit Enter.) Alternatively, push the End button on your keyboard and then Home and the bottom right hand corner of the stored data will appear on the screen. Use the down arrow to go down one row and push Home and that will take you to the leftmost cell of the next clear line which should be highlighted in a bold surround as the “active cell”.

This cell is “active” in that if you were to type or delete without selecting another it would take effect in this cell only. Use the mouse pointer to select a cell as “active” or use the arrow and Tab keys to move the “active cell” around the screen.

Another way to get to the bottom of the stored data is to point with the mouse pointer to a cell in any column, hit End and then the down arrow. That will take you straight to the bottom of that column providing there aren’t any blank cells in it. Column cells may well lack information for a particular ship but all should have an entry in the ID and Name columns at very least so End in the ID column and the down arrow key should also get you straight to the starting point for entering fresh data.

To enter information in a cell just type it in and use the Tab key or the right arrow to move across the row or press Enter to go to the next cell down in the same column. Use the Tab key (rather than the right arrow) to move across, entering data in each column as you go then press Enter when you reach the right hand end and the “active cell” should go back to the starting point on the next line which will save you two or three keystrokes to get there. In a big data entry job the cumulative effect of minimising keystrokes is invaluable for minimising fatigue and thereby the error rate and the risk of repetitive strain injury, and enhancing productivity in any case.

To change something you’ve already typed just use the mouse pointer to activate the relevant cell then re-enter the information. To edit only part of an existing entry activate the cell and hit the F2 function key at the top left of the keyboard and you will be able to edit the entry (as you would in Word) rather than replace it entirely. When you have done so, use Tab to move right, Enter to move down.

In explaining how to use Find to find something I indicated how to highlight a row or column. For convenience, I repeat it here. The standard spreadsheet layout has a border round the “index card” part of the screen with numbered rows and capital letters identifying the columns. Just put the mouse pointer on the row number or column letter of the row or column and left click once. The row or column will be highlighted (probably in blue).

To insert a row or column highlight the one below or before which you wish to insert another. Then right-click with the mouse and you’ll get a menu with an Insert option which you select. Clicking on the Insert menu at the top of the screen will give you the same option as will the keyboard’s Alt key and the letter i followed by the letter c (for column). Highlight more than one to insert more than one.

To move a column, insert a blank column then highlight the one you want to move, right-click and select Cut (or select Edit > Cut from the menu options at the top of the screen or use Crtl X), highlight the new empty column and select Paste in the same way and the column should appear in the chosen new location. Go back to the now empty old column, highlight it, right-click and select Delete (or select Delete from the Edit menu at the top of the screen) to make it go away. If this is your first attempt at this procedure or you aren’t confident, remember Rule 1 to always work on a copy and save often (always save before an unfamiliar procedure). You can generate a hundred successive copies in a day if you wish. The computer doesn’t care. Number them upward from 1 and you can’t confuse which is the latest.

You can also move a column to a new blank one by dragging and dropping with the mouse pointer but that may need a bit of practice.

There’s much more to spreadsheets than this but this is really all you have to know in order to read someone else’s spreadsheet in order to take advantage of their work and, secondly, to add something to a worked example in order to participate in developing new information resources. For adding data, as long as some member of a team knows the other things and how to use other software to combine and to double check spreadsheets, the above should be sufficient.

to return to main maritime menu click here or use back arrow to return