Things that you should learn to do with a spreadsheet editor

Years of experience have convinced us that you don’t need a more sophisticated tool than a spreadsheet editor (i.e. Excel or Calc, the Open/Libreoffice equivalent) if you want to properly store (input) and analyze (categorize and create visualizations and calculation based on) historical data for a research project. For a PhD dissertation, say, or for any other project that is personal or involves just a few collagues. (Sharing data with the general public is a different matter)

So you won’t find advice on this blog about database managers or the TEI. Nothing wrong with those, and please do use them if you know how to (you’ll still have to export .csv files if you want to quantify). What we just say is that learning them is not necessary to quantify properly, and that they are more difficult to learn, in our experience, than the proper use of a spreadsheet editor. (We discuss this with more details in Chapter 3) So here is a list of functions that you should learn to use in order to store and analyze your data using a spreadsheet editor. This is the more practical complement to our “ten commandments of inputting data.” (those are the core of our book, in our view, and they seem to have helped a lot of users of the French version)

We don’t offer here specific tutorials of our own. We hopefully will do it in the future for some functions that are a bit more complicated, or that are used in a peculiar way in humanistic/social scientific research. So expect to find, at some point, tailor-made tutorials for sampling, pivot tables, and VLOOKUP somewhere on this blog. And leave comments if you can’t wait, we’ll try to hurry up (we can also use specific queries as good examples for others). At this point, our goal is more modest: we list here the (English) names of functions that are very useful, and explain briefly why they are and why you should learn them. To actually learn them, you can find many tutorials (including videos) online. So in fact, we are listing words (in italics) that you should learn about.

  • one of the things that makes spreadsheet editors easy to learn is that the contextual menus are often quite good. You get them by right-clicking (PC) or Ctrl+clicking (Mac).
  • if you cannot properly see your data/navigate in the dataset, think not only of zooming in and out but freeze panes (this allows you to always keep in sight the names of the people/places/books that you study, and the names of your vairables); change the width of columns or the height of rows – you will need to select an entire column/row; hide/unhide columns.
  • a very useful function for the management of datasets is that you can very easily insert a new column (for example, to create a categorization next to your input column).
  • if you want to create IDs easily (one of our commandments), you can increment numbers (i.e. automatically fill the cell in row 1 with 1, the cell in row 2 with 2, etc.) just using your mouse.
  • if you have not followed our commandment of maximum segmentation of the data, and you realize that you have several different things in the same column, perhaps there is hope if you were consistent and systematically put the same character (like : or ; or / or whatever, or even a blank space) between the different things. You can try to use text to columns (with the option delimited) to segment your data into several columns.
  • filter looks like a nice function, useful to perform “queries”, i.e. “please only show me the rows referring to novels published by women in the 19th century”. But don’t forget that filter does not automatically correct your spelling mistakes. We try to rather use sort for queries and especially as a preliminary step for categorization. As a result, we almost never use filter.
  • sort is one of the key functions that you need to master. Fortunately, it is quite intuitive. But before using it, triple-check that

a/ changing the order of the rows won’t create any loss of information. If the order in which you inputted rows is significant (e.g. there was some kind of statutory or geographical order in your source), you need an actual data column denoting this order. Create a column “order” and automatically fill it using increment, before sorting the data.

b/ you have selected all the dataset (all rows and all columns). A partial sort will destroy your data. Normally, you’ll get a warning message before the catastrophe, but it’s better to select all before.

  • another very useful tool for categorization is the copying of the content of cells by simple movements or clicks of the mouse, allowing you to quickly fill many cells. (see for example here for Excel, but the same applies to Calc)
  • pivot table will create contingency tables for you. In addition, if you run it on just one column, it will give you a preliminary idea of what you have inputted – very useful for categorization. If you run it, more classically, to check the correlations between two variables (columns) after categorization, it will not just produce numbers and percentages. If you double-click on a cell in the pivot table – say, the one that counts 87 female writers of poetry, or the one with just 5 male writers of drama – ta-dah, you’ll get a new tab with an extract of your dataset. This tab presents only the 87, or only the 5. That’s a query for you.
    Pivot tables can be a bit cumbersome at the beginning – they require a lot of clicking around to give you row percentages, for example. But once you have learned where to click, it becomes one of the greatest functions in your spreadsheet editor. I just discovered with pleasure that Miriam Posner, a sophisticated discussant of “data,” who offers tutorials on “more complicated stuff” such as network and text analysis, also teaches the basics of spreadsheets. You can find her simple and efficient tutorial on pivot tables here (for Excel, but the basics are similar in Calc). [more pointers to good tutorials will be added]
  • when you want to know what’s really in the cell of your spreadsheet editor, look not at the cell but at the formula bar. Sometimes, the contents are different: you have a formula in the cell, but it is the result that is ostensibly displayed.
  • in the formula bar, you can type functions. For example, you can subtract the numbers in one column for the numbers in another and automatically get, say, ages from a subtraction between dates (remember not to just the date format, it almost never works). Functions can also calculate more complicated things, such as means or medians, and generally serve many purposes.
  • when you are satisfied with the results of a function, you should not keep it written in your cell, but permanently replace it with the result. This is done using paste special.
  • the CONCATENATE() function does the reverse of text to columns. It allows you to create one column that takes its text from several previous columns, say first name+last name, or number+street+town. Knowing that this is extremely simple to do (much more simple than text to columns), you have no excuse not to maximally segment information when you input data.
  • the RAND() function generates (reasonably) random numbers. It is very useful for sampling. You might think that RANDBETWEEN() is better if you deal with files numbered, say, from 23,023 to 50,728 (think of the 20th-century welfare state). In fact, it is not the case, because RANDBETWEEN() will tend to draw the same files several times. The best way to go is often to use RAND() to produce arbitrary random numbers from 0 to 1; then to paste special to stabilize this result; then to sort on the random numbers (you get a random sorting; your sample will be the first rows). We know that put like this, this sounds complicated. At some point, we’ll give you an example spreadsheet!
  • the VLOOKUP() function allows your spreadsheet editor to work like a relational database manager. This means that you can have information on two types of entities (books and authors, say; or organizations and members) stored in two different tabs (the first has one row per book, the second one row per authors). If you have IDs for the two types of entities, you will always be able to reorganize your data so that characteristics of the authors appears on the rows for books, or vice versa. VLOOKUP() makes it possible to join tables in Excel or Calc. We know that put like this, this sounds complicated. At some point, we’ll give you an example spreadsheet!

Author: Claire Lemercier

CNRS research professor of history in Paris / Directrice de recherche au CNRS en histoire, au Centre de sociologie des organisations

One thought on “Things that you should learn to do with a spreadsheet editor”

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.