Spreadsheets with LibreOffice Calc: A step-by-step tutorial

Many archaeological analyses require examination of datasets that consist of many observations. Human cognitive capacity constrains our ability to effectively manage and synthesize such large datasets. In order to derive insight from them, there are a number of computational tools available–statistical, database, and spreadsheet software. Here, you will learn how to enter, manage, and access data using a spreadsheet.

A spreahsheet is a table of data that can be easily compiled and edited by individual users or small groups of users with few constraints. Spreadsheets are different from databases, which are more structured collections of related data tables that are often edited and accessed by many users (think banking databases). We will not engage with databases here, though they can be quite useful for archaeological research. Rather, we will focus on spreadsheets, which are more flexible and accessible to a wider range of users. Anyone can create and effectively deploy a spreadsheet without any advanced computer training.

There are many spreadsheet software packages to choose from. This tutorial introduces LibreOffice because it is particularly powerful, free, open source, cross-platform, well documented, and has a large user community base. Moreover, LibreOffice resides on your computer–not the cloud–which is essential for archaeologists working in settings without Internet access.

This step-by-step tutorial will take you through the process of creating and using an archaeological spreadsheet with an emphasis on commonly used procedures and tools. The process will be presented using actual archaeological data as examples. Although only basic functions are presented here, they are the foundations of more comlicated calculations and visualizations that can be performed in Calc. However, I recommend such computation using R statistical computing environment for such computations. R is the subject of another tutorial. The power of Calc is in simple data entry and management.

Installation

To complete this tutorial, you must install LibreOffice on your computer. LibreOffice is available for Windows, Mac, and Linux operating systems. Follow the installation instructions provided on the LibreOffice website. Instructions will vary by operating system and version. This tutorial is based on LibreOffice version 7.5.0. This tutorial is furthermore produced on linux Ubuntu 20.04 operating system, which may entail small (but sometimes frustrating) differences for users working with Mac or Windows operating systems.

Open a new spreadsheet

1. Open LibreOffice. Use your favorite method, typically double-click on the LibreOffice icon on your Desktop or other location. I prefer to open a command line terminal and enter libreoffice. This may seem silly, but it is actually the fastest way to open the software once you get used to it. Whatever approach you take, the following screen will appear:
   
2. Click the green table icon that says "Calc Spreadsheet" to open a new spreadsheet document.
3. A blank spreadsheet will appear as a grid with numbered rows and lettered columns. Each box of in the grid is called a cell. When you open the spreadsheet, cell 1A is highlighed and active. It will looks something like this:
   

Enter and save data

1. With cell 1A highlighted, type "site", and hit enter. We've created a column called sites, which will contain archaeological site names.
2. In cell 2A, enter, "A". You can see that we are starting to create a list of sites in column A, starting with site A.
3. Go down column A, iteratively entering the following site names: "B", "C", and "D" into rows 3, 4, and 5, respectively.
4. Let's take a moment to save your document. You can simply click "File" from the File menu bar and select, "Save As". Alternatively, you can use the keyboard shortcut, "Ctrl+s" (holding down the Ctrl key, hit the letter "s"). I highly recommend using this keystroke shortcut and making it a regular habit to use it periodically while drafting a document. Getting into this habit will reduce the chances of catastrophic data loss (though, LibreOffice does have a robust document recovery system).
5. Place your cursor in cell 1B, and enter "longitude". Longitude refers to geographic coordinates, which is a commonly recorded attribute of archaeolgoical sites.
6. Enter the following longitude values: -97.4365, -97.4386, -97.4567, -97.4297
7. Add a third column called latitude, and enter the following values: 21.4645, 21.3567, 21.4610, 21.4895
8. Add the following four additional columns and associated data:
   1. length.m: 980,35,140,42
   2. width.m: 510,60,125,65
   3. structures: 105,2,25,3
   4. period: Late Period, Early Period, Middle Period, Middle Period
9. Note that the ".m" associated with length and width is to remind us that the units are meters.
10. Your spreadsheet should look something like this:
    
    If so, you've successfully created a basic spreadsheet. Have you saved recently? Take a moment to do so (Ctrl+s).

Format cells

1. Notice that the latitude for site C looks different than the rest–it'one digit short because Calc has automatically left out the zero. For most purposes, this is not a problem, but sometimes we want to be explicit that the zero is meaningful. To specify the number of significant digits, we can format the column.
2. To do that, right click on the letter C of Column C, and select Format Cells in the dropdown menu.
3. In the Numbers tab, under Options, set the Decimal places to 4, and click OK. You should now see the zero re-appear in the latitude value of site C.
4. Have you saved recently? Take a moment to do so (Ctrl+s).

Output data

Copy-and-paste

1. Select your data cells for the spreadsheet you just created by left-clicking on cell 1A and dragging to the lower-right most cell in your table.
2. With the data highlighted, copy the data either using the keyboard shortcut, Ctrl+c, or by selecting Edit, Copy from the File menu bar.
3. Open a blank LibreOffice Writer document by selecting File, New, Text document in the File menu bar. A blank word-processing document will appear.
4. Right click in that blank document, and click Paste Special, Paste Special.
5. Select Rich text formatting (RTF) from the list. Your table should appear in the document. It can be formatted to your liking or style-guide instructions. See the LibreOffice Writer tutorial to learn how to format a table in American Antiquity style.
6. Why did we use the RTF format? Why not simply paste the table as is? Indeed, you can do that, but in my experience, all other pasting formats are difficult to work with. RTF offers a clean table that can be easily formatted using standard word processor tools. Nonetheless, feel free to explore other paste options to find one that works well for you.

Comma separated value and Excel formats

1. Select File, Save a Copy from the File menu bar.

   Although you could also do this using the "Save As" option, it's often preferable to use Save a Copy so that your Calc (ods) document remains open for additional editing. Moreover, a csv file will not save any special formatting or formulas that you might want to access later (more on formulas below).

2. In the pop-up window, select the directory where you would like to store you file.
3. From the dropdown menu in the lower right, select Text CSV (.csv).
4. Type a name in the Name entry box. This will default to the same name as your Calc file, which you can simply accept. Make sure that the extension, ".ods" does not appear in the Name. If so, delete.
5. Click the Save button in the top right corner of the pop-up window.
6. Another pop-up window, titled Export Text File, will appear. Make sure it has the following settings:
   • Character set: Unicode (UTF-8)
   • Field delimiter: ,
   • String delimiter: "
   • Uncheck all check boxes.
7. Click OK. You've now output a csv file.
8. Use your computer's file browser to navigate to the csv file you just created.
9. Right click on your csv file, choose Open With Other Application, and open with Text Editor. Note that this step will differ by operating system. You are looking to open the file with a basic text editor such as Notepad.
10. You should see a simple comma-separated data file that looks like this (again, details will vary):
    
11. There is nothing more to do here. This just shows you what a csv file is and helps you understand why its so flexible for the purpose of data sharing.
12. Close your csv file.

Calculations

Summing

1. Type, stats, in cell 7A. This is simply for reference.
2. Click once on cell 6F, which is just below your last structures value.
3. Type =sum(
4. Click and drag across cells 2F to 5F (all of the structures values).
5. Type ) and Enter.
6. You should see a new value appear, 135, which is the sum of the longitude values.

   Double click on your sum cell, 6F. You should see you value change to your formular. If you ever need to edit a formula, this is one place you could do that. We will not make any changes here, so just hit your escape key (Esc) for now.

Averaging

1. Click once on cell 6B, which is just below your last longitude value.
2. Type =average(
3. Click and drag across cells 2A to 5A (all of the longitude values).
4. Type ) and Enter.
5. You should see a new value appear, -97.44037, which is the average of the longitude values.
6. Notice that this new value has added an extra decimal place, which may reflect a degree of false precision given that the longitude data are resolved to four decimal places. Fix this by following the earlier instruction on formatting cells.
7. While you could reproduce the above steps to find the average of the latitude values, here's a convenient shortcut: single click on cell 6B (the cell with your average longitude). You will see a thick black line highlighting the cell, and a handle (small square) on the lower right. Click and drag than handle into cell 6C. You should now see the average of the latitude values.
8. Note that there are many other basic statistical functions that can be easily performed in this way–minimum (min), maximum (max), standard deviation (std), median (median), and more. Feel free to experiment, and consult the LibreOffice help or web as needed.

Arithmetic

1. Add a new column after the width.m column by right clicking on column F and selecting Insert Columns Before.
2. Enter "area.m2" into cell 2F for our column header. The "m2" indicates that the units are square meters.
3. Select cell 2F.
4. Type =
5. Click on cell 2D, which is your first length.m value.
6. Type, *
7. Click on cell 2E, which is your first width.m value.
8. Enter. You should now see the calculation result.
9. Again, you could repeat this for each row to estimate the area for the other sites. However, you can use the shortcut described about by clicking and dragging the selection handle on cell 2B to cell 2D. The area will be calculated for all sites.
10. Now that you found the site area for your five sites, add a forumula to cell 6F that calculates average site area.
11. Note that there are many other basic arithmetic functions that can be easily performed in this way–addition (+), subtraction (-), division (/), raising to a power (^), and more. Feel free to experiment, and consult the LibreOffice help or web as needed.
12. Have you saved recently? Take a moment to do so (Ctrl+s).

Sorting

1. Click and drag your cursor to highlight cells 1A to 5H. Warning: When sorting data tables, always remember to always select all relvant cells. If you sort data without doing so, you will indpently sort data and thus lose information. For example, if you sort the site size column here without highlighting the other fields, and you save the result, the next time you open your file, you will not know which site size values belong to which sites. I have seen massive data collection efforts completely upended because of this mistake, and this is one reason to consider using relational databases, which have safeguards against such mistakes. But spreadsheets still have their place. Just be aware of this problem and consider yourself warned.
2. Select Data, Sort from the File menu bar.
3. In the Sort pop-up menu, open the Options tab.
4. Make sure that "Range contains column heads" is checked.
5. Go back to the Sort Criteria tab.
6. Select area.m2 from the Sort Key 1 dropdown, and check Descending.
7. Click OK. You should now see your sites organized from largest at the top to smallest at the bottom.
8. That concludes your work with the basic.ods file. Save your file and close the file.

Import data