How to keep lists sorted both horizontally and vertically?

January 27, 2011 9:36 AM

Sorting and keeping track of my research: is Excel the best solution?

I'm working on a large research project that involves reading many books, jotting down major themes, and keeping notes that will jog my memory. I need three columns that I can sort: A is the title; B is the theme or plot element; C is notes. An example might be:


Little Women | Hunger | girls forgo Xmas din
Oliver Twist |Hunger |Porridge
Oliver Twist |England | 19c



But I need to be able to sort them so that horizontal rows always stay together but I can shift the columns at will, so I can look at everything about Little Women at once, if I like, or see all the books that have hunger as a theme/plot point. A D column for author is probably a good idea too.

I think Excel is the best solution--am I right? If so, how can I do this?

Please excuse my ignorance. I usually just keep handwritten notes or lists in Word, but this is too big a project.

posted by pipti to Computers & Internet (13 answers total)

Excel is probably not the absolute best way to do this, but it's almost certainly good enough, and you probably don't want to be learning a database program for something that you don't need it for.

This is very, very easy in excel. Create your column headers. Add rows as you want. when you want to sort by something in particular, click on that column, and then click the "sort" button (you'll have to be more specific about what version of excel you're using if you can't find the sort button).

Depending on how you work, I might add a column for date entered or date read or something like that, which you will be able to sort by in order to get your things back in chronological order of when you entered them (this would help me a lot, but I don't know if that's universal).
posted by brainmouse at 9:49 AM on January 27, 2011

Yes, Excel is good for keeping track of information like this. Personally I use Google Docs because takes less time to load, and it lets me update the database wherever I am.
posted by fix at 9:49 AM on January 27, 2011

Sorting by column in Excel will not change the order of items in rows - so for your example if you sort by Column 2 alphabetically, the row with Oliver Twist | England | 19c would be at the top. You can also specify sub-sorting, for example sort first by Column 2 then sort by Column 1, so that within groups that have England in the second cell, they will be sorted so that items in cell 1 are alphabetical, and so on. Play around with it. It's pretty straightforward.

If you add a header row (ie, in the first row you put Title | Theme | Notes) there's a checkbox to indicate that your data has a header; if you check this box, the first row will not be included in sorting, but will remain at the top of the block of data. This is useful as the columns will then be named in sort order options by the header you entered, rather than the generic Column A, Column B, etc. name Excel uses.
posted by caution live frogs at 9:49 AM on January 27, 2011

Excel should be just fine for what you described.
posted by special-k at 9:49 AM on January 27, 2011

Excel is probably not the absolute best way to do this, but it's almost certainly good enough, and you probably don't want to be learning a database program for something that you don't need it for.

I use databases extensively for my research and that would be overkill here. Stick with Excel or Google docs.
posted by special-k at 9:50 AM on January 27, 2011

It sounds like you want to play around with the Excel Autofilter functions - it is basically like a powerful Sort option so that you can temporarily "filter out" the lines that you're not interested in.
posted by muddgirl at 9:54 AM on January 27, 2011

You mean, to re-sort by column? What you want to do is to create a simple database with only one table -- where the columns are fields (attributes) and the rows are records. I have done this frequently -- in excel you can either use the sort function sort by title or author or theme, and scroll down to what you are interested in (always do this with ALL columns selected, or you can mess stuff up). or you can put in filters -- you select from a list, and all of the other rows are hidden.

If you aren't familiar with databases, this is not a bad solution. It's not as elegant or as powerful as a relational database with multiple tables, but it's much easier to set up, and (due to the flexibility of spreadsheets) much easier to design on the fly.

You will have limitations -- if you want to put in multiple themes for one book, you'll have to keep typing that book in. Whereas in a database, you would have a book table with the book listed once with a unique identifier, and then can link that to multiple rows in a theme table where you have the themes (and page references, etc).

If you were talking thousands or millions of books -- or there were quantitative analyses you wanted to do -- I would say you definitely woul want to create a database But it sounds like you have hundreds of books, and databases are a bugger to set up and work with. When databases work well, they are awesome. but when the program borks (you have to get your queries just right), I often wish I just had a simple spreadsheet. (in fact, even when using a database, I tend to design it on a spreadsheet to start with, then link or import tables into access).
posted by jb at 9:58 AM on January 27, 2011

I agree that Excel is good at this but understand that if you select one column and sort just that column it will ruin everything - Little Women could line up with England | 19C and if you don't notice immediately and undo the sort you will be in trouble. I think more modern versions of Excel warn you before it sorts part of a table and Google Docs does a good job with tracking revisions, but still the threat is there. I think it may be worth learning Access or Filemaker if this is a big project. At the level you will use them they can be similar to Excel (grid view, columns, rows, sorting, etc) but you have a little more protection against corrupting your data.
posted by ChrisHartley at 10:01 AM on January 27, 2011

Excel will work fine for this. The magic touch is the Autofilter option, under Data -> Filter -> Autofilter (in Excel 2003, anyway). That will create a drop-down selector in your header row that lets you select one item out of the column, so that you see only the rows that contain that item. You can also use Autofilter to narrow results down, e.g. display only your Little Women + hunger rows.
posted by Dipsomaniac at 10:04 AM on January 27, 2011

Sorry -- what I meant by a simple database is that your Excel spreadsheet would be your one-table database. As opposed to creating a multi-table database in something like Access.

But be careful when using the sort function. I don't know about your version of Excel (I prefer OpenOffice Calc), but if I use the "sort by" with only one column selected, it can move just the cells in that column -- and next thing I know, Oliver Twist is written by Louisa Alcott. or it will resort only the rows selected. I always click the top left-hand corner to select all columns and rows before sorting.

The auto-sort and filter functions do not have this problem, and sound like they would be ideal for your purposes.
posted by jb at 10:05 AM on January 27, 2011

There is one more thing that you might like to do. Even though an Excel spreadsheet is great for what you are doing, you are still using that program to create a simple database -- and it really help to know the basics of database design.

If you are affiliated with a university, there is a good chance that somewhere there is a short "Intro to databases" workshop you can take. I did a four-day workshop on databases before beginning my research, and it has been invaluable for me. I learned about good practices like having unique identifiers and entryIDs (so I could always resort to the order I entered the data in) and learning to break up my data so that each cell has the smallest unit possible (eg, separating family and personal names into two different cells). I really am still in database kindergarten (or maybe grade 2), but knowing the basics and the whys/theory behind them has really helped me in designing my own spreadsheet-based databases.
posted by jb at 10:14 AM on January 27, 2011

If you have access to Filemaker, it's pretty nice for these kind of small database projects. The key thing it provides over Excel (IMHO) is the ability to make completely custom layouts and ways to provide multiple views of the same data. It's also capable of spitting out Excel files if you nee them.
posted by doctor_negative at 10:18 AM on January 27, 2011

Zotero is a way better solution and also includes citation/bibliographic management. You can use tags to organize your notes, references, etc., which is a much more powerful and versatile organizational structure than the flat quasi-database you're building in Excel.
posted by Jacqueline at 12:40 AM on January 28, 2011