Films/TV list

This spreadsheet contains my record of films and TV I’ve seen — fairly complete from around 2005 to present, and reconstructing backwards towards 1986 when I started intermittently tracking that. Also, in the same sheet, is a) my prioritized watchlist of things I’d like to watch or re-watch, and b) gradually growing set of other lists such as film festival and awards winners, i.e. suggestions. All of this is in one sheet so it can be cross-referenced.

Watchlist (in sections ‘top’, ‘watchlist’, and ‘backburned’) takes up the first 530 rows, with some blank space. Items watched, and list items, take up about 1500 further rows.  In the embedded spreadsheet view below, you can see additional rows or columns by selecting the sheet area and then scrolling vertically or horizontally.

The above embedding of the spreadsheet allows just one view of the spreadsheet. If you’d like to get others views, such as sorting it by list or year, you can access the Google Sheets doc at:


1) The default view shows watchlist at top, then items seen, in reverse order of how recently. Then at bottom, items from lists (e.g. film festival / awards) that I haven’t yet seen or put into the watchlist. The watchlist has sub-sections “9-watchlist’-top, ‘8-watchlist’, or ‘backburner’. (those numbers are there just to make it sort as wanted).

If the sheet is not appearing in this sort order, you can restore it with these steps:
a) select column B, “STATUS or DATE SEEN” by clicking on the ‘B’ at its top.
b) in the menus go to Data > Sort sheet by Column B, Z->A.

2) Columns to the far right represent various lists such Cannes Film Festival winners, and note a) whether the row’s item is in that list; and possibly b) some detail, such as rank on that list, year or prize category.

To see the items on a given list, you can sort the sheet on the column.

3) When in default sort order, the watchlist sections will make up rows 2-531. Display of these rows may be turned on or off, for convenience of putting the ‘seen’ items at top instead. Click +/- at top of the sheet’s left margin to show/hide these rows.

4) You can sort the whole sheet by any column value; for example, to see all items sorted by year. To do so,
a) click on the column you wish to sort by.
b) in the menus, go to Data > Sort Sheet by Column, and select either ascending or descending order.

5) You can also sort the sheet using multiple column values; for example to see all items sorted first by country and then by year. To do this:
a) select the whole sheet by clicking on the empty cell at top left of sheet (between the ‘1’ and the ‘A’).
b) in menus, go to Data > Sort Range, and specify the columns and sorting orders you wish to use.

6) For a cleaner view of the list, without menus & formatting bar, go to View > Full Screen.


7) Adding to watchlists:
a) *New* items for any watchlist should be added to a blank row
b) To move items between watchlists, just change the STATUS or DATE SEEN field as needed.
c) To ‘move’ an item from watched to watchlist, copy the row’s contents into a new blank line in watchlist, delete
d) use SHIFT-CMD-v so as to copy only cell values, not format.

8) Items seen multiple times have a separate row for each time. (to preserve the date & diary form). However, in Column A, ‘Duplicate’, I enter ‘1’ for any entries after the first. Then, this column has a filter which by default hides rows with anything but a blank in that field. To get a diary view, ie everything seen, change this filter so duplicate entries ARE shown.

9) Row colors are set automatically using Conditional Formatting formulae. The ranges covered by the formulae seems to get chopped up from cutting and pasting, so I’ve been needing to periodically reset them. Google Sheets apparently does not have a simple way to express range of “entire sheet” in formulae, which is what wanted here.

10) Possible further features:
a) enrichment/completion of data, various ways. e.g.,
b) automated lookup of Wikipedia page, image, for item.
c) lookup & storing of identiers, such as IMDB ID.
d) A way to create designed view pages, e.g. for specific lists or categories.


Leave a Reply