Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

Often when I'm working with large data sets, I structure the data in a way where every row equals one specific case. For example, in a psychological experiment, I input the data so that all data points on the same row comes from the same participant.

However, in all spreadsheet software that I've tried out (Excel, Open Office, Libre Office, Google Docs, et cetera), it's possible to sort data for a strict subset of the columns, leaving data points not belonging to this subset stationary. If you're working with a data set where each row represents a specific case, these types of operations can potentially be devastating.

Now, one could argue that the best thing to do here is just to avoid using these types of sorting functions, only applying global sorting functions instead. However, the fact that these sorting functions even exists results in me not trusting my data fully, and at times, I've happened to apply them to my data set by accident (sometimes not even realizing it).

Compare this to a program like for example SPSS. Here, the "one row=one case" philosophy is built into the program itself, meaning that you can't screw it up by applying the wrong sorting function.

So, my question is, are spreadsheet programs inherently unsuitable for "one row=one case" data? Is there any way, in any of the programs that I listed above, to lock the data to its row, or disable the sorting functions that could screw it up?

share|improve this question
A database would be more appropriate. Look into MS Access, or MySQL, etc. – Ƭᴇcʜιᴇ007 Oct 30 '13 at 11:29
not necessarily unsuitable, but you definitely have to be careful to use spreadsheets very very carefully, as an inadvertent gesture on your part could ruin your data. if you do want more control of the data, a database is the best way to go, for a number of reasons, not the least of which being protecting the data from you. All that said, you can certainly do everything you want with a spreadsheet, but they are really more for data analysis than storage. – Frank Thomas Oct 30 '13 at 11:49

No, they are not inherently unsuitable. These programs have been used successfully for years to store and recall data.

Although I believe using a database would be the best solution, I'd like to offer an answer to keep the data integrity within your spreadsheet. Don't sort the data within the sheet.

Most of the applications you mentioned have tools to create pivot tables or report views which allow you to manipulate the data in various ways without changing the data in the table. This would be worth investigating if you do not desire creating a database.

Here are a couple great resources to help you;

Quick start: Create a PivotTable report

Excel Pivot Tables Tutorial : What is a Pivot Table and How to Make one.

share|improve this answer
"Although I believe using a database would be the best solution, I'd like to offer an answer to keep the data integrity within your spreadsheet. Don't sort the data within the sheet." - This is akin to having a car with a big red self-destruct button. Sure, you can just refrain from touching it and making damn sure that you tell all your passengers (collaborators in a Google Docs spreadsheet) not to do it either but I'd prefer not to have it all, especially in a world where people have been trained to push it and believe that it does something useful. – Speldosa Nov 1 '13 at 15:27

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .