Spreadsheet

I talk a lot about the Film Spreadsheet I use on my podcast. It’s integral to my own cinematic life and in doing many of the podcast episodes. But because of how complex and detailed it is, I usually shy away from explaining much about it. It’s not that I don’t think people would understand what I’m talking about, quite the contrary; it’s that I think it’d be mostly boring to listen to. The most in depth I typically go is during my Statistics episodes where I go through the effects a particular film has on the players involved. As of the time of this writing, I haven’t yet recorded the Spreadsheet Deep Dive, but that will be a special episode that explains all the ins and outs as I go through each aspect of the spreadsheet from start to finish. Some people understand things better when they see it, some when they hear it, so this is a visual representation and interpretation of what my spreadsheet is, how it works, and what information it yields. You can find accompanying images one level deeper in this section that provide helpful visual aids.

Before I go into the pages that make up my spreadsheet, I want to provide a helpful rubric that shows the formatting style for most of the content displayed in the accompanying images. I use a wide range of methods to show visually that a film, person, or detail is categorized in a meaningful way. Note that this does not refer to the colors that distinguish the months on the Films page.

Formatting Rubric:

  • Colors: Any cell that has a shaded background.
    • Pink: This indicates that a film is an animated film and an actor is purely a voice role (but not a narrator).
      • Pink w/ Diagonal Green Lines: This indicates that a film is an animated documentary.
      • Pink w/ Purple Lines: This indicates that a film is a foreign language, animated film.
    • Green: This indicates that a film is primarily a foreign language film and an actor is themselves (including a dramatized version).
      • Green w/ Purple Lines: This indicates that a film is a foreign language documentary.
    • Light Purple: This indicates that a film is primarily in a foreign language.
    • Black: This indicates that an actor is deceased.
    • Golden: This indicates that an actor’s role is as the narrator of the film, typically in voice only.
    • Orange: This indicates that a person is male.
    • Dark Purple: This indicates that a person is female.
    • Golden Dots: This indicates that a film won or was nominated for an Academy Award for this particular section.
      • Sparse Golden Dots: This indicates that the person in reference was nominated for an Academy Award.
        • Films: Films are not designated this way when only nominated for a Best Picture/Feature/Short category.
        • Directors: Nominations for the category of Best Director.
        • Writers: Nominations for the categories of Best Original Screenplay, Best Adapted Screenplay, Best Story, etc.
        • Actors: Nominations for the categories of Best Actor, Best Actress, Best Supporting Actor, or Best Supporting Actress.
        • Names: In certain circumstances, a person is given an Honorary Academy Award. In this instance, their name is given sparse golden dots: Hayao Miyazaki’s name is treated this way as both a writer and director.
      • Thick Golden Dots: This indicates that the person/film in reference won an Academy Award.
        • Films: Wins for the categories of Best Picture, Best Foreign Language Feature, Best Documentary Feature, Best Live Action Short, Best Animated Short, Best Documentary Short, etc. This is only applied on the Films page.
        • Directors: Wins for the category of Best Director.
        • Writers: Wins for the categories of Best Original Screenplay, Best Adapted Screenplay, Best Story, etc.
        • Actors: Wins for the categories of Best Actor, Best Actress, Best Supporting Actor, or Best Supporting Actress.
  • Film Ratings: When I refer to the score I give a film, that puts them in a tier on my spreadsheet.
    • 100
      • All films rated 100 are formatted with two underlines and in bold. (Represented with one underline.)
    • 90-99
      • All films rated 90-99 are formatted with one underline and in italics.
    • 80-89
      • All films rated 80-89 are formatted with one underline.
    • 70-79
      • All films rated 70-79 are formatted in bold.
    • 60-69
      • All films rated 60-69 are formatted in italics. 
    • 50-59
      • All films rated 50-59 are formatted normally.
    • 25-49
      • All films rated 25-49 are formatted with a strike-through.
    • 0-24
      • All films rated 0-24 are formatted with a strike through and in red.

To start, I’m going to break things down into 12 sections, one for each page on my primary spreadsheet.

  1. Series
  2. Films
  3. Directors
  4. Writers
  5. Actors
  6. Genre
  7. Academy Awards
  8. Best Pictures
  9. Year
  10. CoFA
  11. Country
  12. Top 300

Each page has its own purpose and serves to create a complete portfolio of the many films I’ve seen in my life. We’ll take things one step at a time.

Series:

This is a very simple page of the spreadsheet. The intent is to determine and reveal what the best series (trilogies, sequels, cinematic universes) is by average rating of the films that comprise them. For example, if you look at the Series visual aid, you’ll see that Richard Linklater’s Before series is the highest ranked. It has an average rating of 98 across three films. The length column indicates how long it would take to watch all films in that series from start to finish, in this case 4.88 hours. Currently, the longest series is the MCU, which clocks in at 36.93 hours (with the release of Thor: Ragnarok). It also features the largest quantity of films in a series: 24. These numbers includes the Marvel One Shots in its calculations.

This page of the spreadsheet grabs its values from the Films page, which I indicate by having two columns at the end of the Films page to describe what series/world a film takes place in. Thor: Ragnarok qualifies as both a Thor franchise film and an MCU franchise film. This is also the first place you’ll see many of my formatting edits that help visually draw your eyes to the types of films you’re looking for. Use the rubric for reference.

Films:

This is the primary page of the spreadsheet. This page outlines every film I’ve ever seen, how many times I’ve seen it, when the last time I saw it was, its length in minutes, the year it was released (typically as per Letterboxd), my own single sentence synopsis, a rating out of 100, the RT score, and what series/world the film is a part of. Currently, you can see that there are two columns at the end marked Director and Writer. Those are just for me to know what films I’ve found that data from and added it to the spreadsheet. When I finally have everything current, those columns will disappear. There is also a column further to the right that identifies films that I haven’t found or don’t exist on Letterboxd to the best of my knowledge.

There are a lot of formatting things going on for the films, differentiating documentaries, animated films, and foreign language films from the rest. Each month has its own formatting, as well. The following columns are formatted to show graded color scales: Year Last Seen, Year Released, My Rating, and RT Score.

You’ll also notice, and this was on the Series page, that there will be a second row at the top of most pages with additional statistics. Despite this becoming a sorting nightmare for the longest time, it is a convenient header with the top two rows frozen. Underneath the Film heading, you’ll see the current number of individual films I’ve seen. Then there’s the total number of times I’ve watched a film in total. The number beneath the Month heading is how many full 24 hour days I’ve spent watching movies, approximately. Next is the average day of the week I’ve seen each movie the last time, followed by the same thing for years. Then the average length of all films I’ve seen, and the average release year for every film I’ve seen. On the other side of the synopsis column, you’ll see my own average rating for every film and the average RT score for every film (sans films without a rating, represented with a -1).

Directors/Writers:

Two of three pages devoted to those that help build the films we see (with the hope that I can add more pages like this in the future for composer, cinematographers, etc). This page lists every director/writer that has directed/written a single film that I’ve seen, with differentiation made between male and female.

Columns:

  • Rank
    • Formatted
    • Displays the corresponding director/writer’s ranking by score when compared to the rest of the scores on this page.
  • Country
    • Displays the country (or state) of birth/nationality for each corresponding director/writer.
  • Director/Writer
    • Name of each director/writer.
  • Count
    • Formatted
    • Number of films I’ve seen with that name attached as a director/writer.
  • Avg.
    • Formatted
    • Average rating of each film I’ve seen with the corresponding name attached as a director/writer.
  • 100s through 0-24
    • Formatted
    • Number of films I’ve seen with that name attached as a director/writer that are rated in their representative tiers.
  • AN
    • Formatted
    • Number of films I’ve seen where the corresponding director/writer was nominated for an Academy Award.
  • AA
    • Formatted
    • Number of films I’ve seen where the corresponding director/writer won an Academy Award.
  • Value
    • Formatted
    • Sum value based on the number of films in each tier.
  • Score
    • Formatted
    • Sum of Value, AA, half of AN, and a percentage of Avg. based on Count.
  • Best Movie, etc.
    • A horizontal list of films attributed to the corresponding director/writer in order from best to worst.

Again, you’ll notice the second row shows additional statistics. For these columns it shows an average value in that column: Count, Avg., 100s through 0-24, AN, AA, and Value. The number underneath the Rank header is the average score. The number underneath the Director/Writer header is the sum total of directors/writers. Then, following the second row out to the right, you’ll see how many directors have directed X films.

Actors:

In most ways, the Actors page is just like the page for Writers and Directors. There’s really only one big difference between the two: dates of birth. Every actor’s date of birth is placed in front of their name. This helps me not only find my Top 10 MonthBorn actors lists easily, but have a quick reference for age of each actor. When a date is shaded black, that means the person is deceased (this is not 100% accurate, I’m sure there are some I’m missing). I then attach a comment with the date of death and the age the actor was at that time. Everything else is identical and be interpreted with the information under the Director/Writer section by substituting Actor in their place.

Genre:

Now that we’re past the human element, we have one of the more interesting pages. The Genre page displays much more than it lets on. Here you see every film represented again with its release year and rating. It’s followed by a wealth of genres: Action, Adventure, Animation, Comedy, Crime, Documentary, Drama, Fantasy, Foreign Language, Horror, Musical, Mystery, Romance, Science Fiction, Short, Sport, Thriller, and Western. I’ve adjusted the categories over time, breaking Action/Adventure and Fantasy/SciFi apart, while adding Crime. There are other genres and more specific genres that I often think warrant inclusion, but I try to limit myself only to the genres that are bluntly obvious (animation) or used on Letterboxd/IMDb, which is where I typically pull my genre information.

Masquerading as genres, I also have columns for Disney and Pixar films, strictly their animated works. These are categories that are simply too big to use in the Series category, and thus are relegated to makeshift “genres.” The column after the genres is used to determine the Bechdel test of a film. This is a test that was instituted based on the comic strip Dykes to Watch Out For by Alison Bechdel. In it, one woman tells another that she’ll only see a movie that passes this rule she has. And it’s represented on my spreadsheet as follows:

  • Zero (black): This film had less than two named female characters.
  • One (red): This film had at least two named female characters.
  • Two (yellow): This film had at least two named female characters that talk to each other.
  • Three (green): This film had at least two named female characters that talk to each other about something besides men.

It’s unsurprising just how few films actually pass the third level of this test. This column helps put things in perspective and will hopefully be greener with time.

The next column is the MPAA rating for a film: G, PG, PG-13, or R. In the case where a film has no MPAA rating, it’s consider NR (Not Rated). Some films qualify as UR (Unrated) depending on their edition. Older films had different groupings of ratings. Going back far enough drops the MPAA ratings we know today altogether. If a film is Approved, it’s shown as Ap. Films that aren’t approved are generally represented as Not Rated. There are also a few films that received an X or NC-17 rating, though these are far less common.

The next two columns determine if the corresponding film appears in the list of the IMDb Top 250 or my own Top 300. The number represents its placement, as most recently determined.

In the second row on this page, you’ll see the averages for each film that qualifies as the corresponding genre. There is also the average level on the Bechdel test that each film hits, the number of films that currently have MPAA ratings assigned to them (from my own data mining), the average rating of the films on the IMDb 250, and the average rating of the films on my Top 300.

Academy Awards:

 This page is fairly straightforward, but poses a very unique issue to the spreadsheet. Once again, you see every film title again, followed by the film’s rating and release year. However, the release year isn’t always accurate. Some films come out in 2015, but qualify and are nominated for the 2016 Academy Awards. In these instances, I use the qualifying year to display. Occasionally, a film is qualified for the year prior to its release, or two years after its release. There are also circumstances where a film is nominated at multiple ceremonies. The Battle of Algiers was nominated for Best Foreign Language Film at the 1967 Academy Awards (although it was a 1966 film). It was then later nominated for Best Picture and Best Director at the 1969 Academy Awards (still a 1966 film). Or, there’s Limelight, which is a 1952 film that wasn’t nominated for an Academy Award until the 1972 Academy Awards, winning Best Score.

When a film ends up nominated in multiple years, it’s put in this page twice (with a comment added to the cell displaying the number of films to represent that addition). When a film is nominated in a year it wasn’t released, I put a comment on the cell with the year to show the actual release year.

In the next column, it displays the number of competitive Oscars that a film was nominated for. Due to the formula I use, this cell needs to be modified at times to reflect winning an Honorary or Special Achievement Oscar. It also requires modification when a film is nominated multiple times in the same category, usually an acting category or for multiple Original Songs.

Next is how many Academy Awards a film won, competitive or not. To the right, each Oscar category has its own column, to some degree. Over the years, the Academy has changed the wording of categories, added and removed categories, and I’ve tried to consolidate them as best as I can. For example, Assistant Director has its own category, but is no longer an active one. The same with Original Story and Dance Choreography. However, some have been combined, like Original Screenplay was formerly called Best Screenplay and Adapted Screenplay was Story and Screenplay. This particular combination is at times problematic, and requires a mild overhaul when I find the time. Other combinations like Dramatic Score, Musical/Comedy Score, and Music (Scoring) are all now simply Original Score. Cinematography and Production Design were separated by Color and Black and White once upon a time, but now are condensed into a single column. The very last column is to visually distinguish an Honorary Oscar with a comment explaining why the film received it.

The second row averages the amount of Nominations and Oscars awarded across every film, then the average rating of the films that won each category, not the ones nominated.

Best Pictures:

This page displays itself uniquely. Rather than have the films listed down one side with the criteria they satisfy marked off to the right, this page sorts information purely by year. It’s used to display the best films from each year according to four categories: Best Film, Best Animated Film, Best Documentary Film, and Best Foreign Language Film. It further breaks those categories down into two distinctions, the film represented by the Academy Awards in that category, and the one represented by my spreadsheet in that category. Each film has its corresponding rating from me next to it.

In the second row on this page, you’ll see the average rating of each film in each category. All film titles are displayed for the Oscar winners whether or not I’ve seen them, while years where I haven’t seen a qualifying film are left blank for their respective categories. For years where the Academy had no category for a particular column or, in the case of pre-1928 years, did not exist, those areas are filled in with blue graph paper lines, while my own viewings are still shown.

Year:

Perhaps the page stuffed with the most information, the Year page has raw data based on the year the films were released. The Year column is exactly what it sounds like. The Films column is how many films from that year I’ve seen. The Films Seen column is how many films were last seen in that year, a small but important distinction. Following that are the average rating and RT score for the films released in that year. Next are the total number of Oscars Wins and Nominations I’ve seen from that year. This is an important qualification that those categories do not represent the number of films nominated for or winning an Oscar. For example, if there are 24 categories, there are 24 winners. The Oscar Wins column is about how many of those wins are represented by the films I’ve seen. If that number spits out a 24, then I’ve seen every film that won an Oscar that year. The same goes for nominations, but it’s far less consistent. In 2016, the Academy gave out 124 nominations across all films. If the number in the Oscar Noms category was less than 124, then there would be at least 1 film I haven’t seen that was nominated for an Oscar that year. I’ve gone through the years and added comments to the cells in both columns displaying the maximum number in each case so that I’ll know when I hit them.

Next are the number of Actors Born in the corresponding year, using the data from the Actors page. Next is the number of films from that year that show up in my Top 300 films list. The next 18 columns represent how many films from each genre appear in each year, followed by the same information for Disney and Pixar films. A bold border separates that section from the percentage of films receiving each level on the Bechdel Test. If all films are assigned values for this statistics, the numbers would add up to 100%. On the other side of the next bold border are the number of films from each MPAA rating released that year. All of the categories mentioned in this paragraph are conditionally formatted by content.

This page is also formatted as a table, so at the bottom, there is a total row. For the Oscar Wins and Oscar Noms columns, I’ve found the number equivalent to seeing every Oscar Winning/Nominated film and added it as a comment for those cells. The Average Rating and Tomato Meter columns have Averages instead of Sums. All of the Genre columns have Sums. All of the Bechdel Test columns have Averages. All of the Ratings columns have Sums. This page is formatted as a table because of it’s limited amount of grand scale editing.

CoFA:

 This is the Circle of Film Awards page. It displays the film title, rating, and year of release if that film is currently/permanently nominated for any category. Films that fall off the list completely are removed. It’s set up similarly to the Academy Awards page, only with different categories. One main exception is that instead of using 1 for a winner and 0 for a nomination, I have all the nominations ranked from 1 to 5. This will show you how close a film/performance/technical aspect was to winning. Just like the Academy Awards page, the second row at the top averages for the films that won each category. Due to the small amount of films currently on display, this page is also set up as a table, but unlike the Year page, it does not have a Total row.

Country:

This is where the country of origin shown on the Director/Writer/Actor pages comes in to play. Broken up into groups of four columns, each job is displayed with statistics for how many people from that area are represented, how many people from that area are in the top 100 for that job, the sum of Oscar wins and half of the Oscar Nominations by people from that area, and the average score of people from that area. While each state has its own row, there is also a row for the entire USA.

On the far right side, there are four columns that take the totals for each area across all jobs. This will add the same person multiple times if they have appeared as an Actor, Director, and Writer on various films. It also displays how many times that area is represented in the top 100s for all jobs, the total Oscar value across all jobs, and finally average score across all jobs. While I think most people wouldn’t be surprised to learn that New York and California are where most filmmakers and actors and writers do their work and live, it’s still surprising how many were actually born there.

Top 300:

This is the least orderly page on the spreadsheet. It has a lot of data, restricted to my Top 300 films as designated by the Genre page. All of that data is then displayed in a graph fitting the needs of the data. This includes the genres represented by a vertical bar graph, Bechdel Test levels in a pie chart, MPAA Rating in a horizontal bar graph, and Decades represented in a vertical bar graph. Additionally, there are two charts and one larger chart that display my ratings as distributed across every film I’ve seen, broken down by individual score and also grouped into convenient sets.

Comments are closed.