Spreadsheet Tutorial

1. Copy the Google Sheets file

IMDB movies with “God” in the title, released 2020–2023, with at least 100 votes: You can use the spreadsheet I created, but if you’d like to know how I did that part, watch the video below.

To copy the spreadsheet I created and work with it in Google Sheets, use this link: copy spreadsheet. If you prefer to download the spreadsheet and work with it in Excel, use this instead: download spreadsheet. Please note that you’ll have to use somewhat different buttons and formulas in Excel.

2. Navigate, sort, and filter

Columns, rows, and cells: You’ll see that each column has a letter identifying it (A, B, C ...) and each row has a number. I’ve used the first row to label the columns with headers such as Title and Year. These help you read the table, but as you work with the spreadsheet, you’ll use the column letters and row numbers to identify the cells. For example, A2 is the title of the first film in the list.
Sorting and Data Types: To sort the spreadsheet by a column, put your mouse over the column letter, and then click the arrow that appears on the right of the column. Choose “Sort Sheet A-Z” or “Sort Sheet Z-A”. The Title column has letters in it (known as a “string”), so it will be sorted alphabetically, whereas the Year column has numbers, so it will be sorted numerically. You can make sure Google Sheets interprets your column type correctly by clicking on the “123” button in the toolbar and changing it to what you need (“Plain Text”, “Number”, “Date”, etc.).
Adding columns: Suppose you want to add your own rating for each movie to classify how relevant it is to your project. Just go over to column H, which is blank, and type Relevance in cell H1. I’d suggest using a rating scale with numbers, such as from 1-5, so that you can sort the column easily.
Filtering: Click on Data > Create filter in the top toolbar. This will let you see only the rows that meet your criteria. Then click on the green lines that appear next to the column name “User Rating”, and choose Filter by Condition > Greater than. In the “Value or Formula” box type 7. Now you will see only films rated better than 7. To clear the filter, click on Data > Remove Filter.

3. Chart

Spreadsheets are great for graphing. Suppose you want to make a graph showing how many of these movies were released in each of the four years.

Select columns to chart: Click and drag on the column letters A and B to select them. Then click in the top toolbar on Insert > Chart.
Edit chart: At first you’ll see a chart showing movie titles on the X-axis and years (as if a number) on the Y-axis. We want a count of the movies, grouped by years.
1. In the chart editor that popped up on the right, underneath the X-axis: Title, click “Aggregate”.
2. Under “Series” click on the three vertical dots to the right of “Year”, then “Remove”.
3. Finally, under X-axis, click on “A1:A64” and change it to “Year”. Now you should have a chart showing how many movies were released each year.
4. You can change many other aspects of the way the chart looks in the Chart editor, and you can click on it to move it around or change its size.

4. Formulas

Spreadsheet formulas can be very powerful, and we can use them to calculate things or to clean up data.

Insert a formula: Any cell can contain a formula, which is indicated by typing an = sign at the beginning of the cell. In cell I2, try typing =A2, then press enter/return. You’ll immediately see the title from A2 appear in the cell.
Do math: Click on cell I2 again, and this time insert =G2*100. It will take the “Metascore” from G2 and multiply it by 100.
Formula functions: Most of the time, formulas use “functions.” These look like a word, followed by the inputs in parentheses (). Different functions require different types of input (text, numbers, dates). Google Sheets will suggest functions to you as you type. If a function takes more than one input, they will be separated by commas (in the English version) or semicolons (in the German version). For example, the function REGEXREPLACE(text, regular_expression, replacement) will have as inputs (1) the cell containing the text you want to replace something in, then (2) a regular expression to find, then (3) what to replace it with.
Replace text using a function: Unfortunately the “Number of Ratings” we copied from IMDB shows numbers like “6.1K” instead of “6100”. We can’t use this as a number for graphing or sorting until we change it. In cell I2, put =REGEXREPLACE(F2,"K","000"). Now you have “28000” by replacing the “K” with “000”.
Copy the formula to all rows: Click the blue circle on the bottom right of cell I2 and drag it all the way down to row 64. Now you’ve copied the formula to the other rows. Google Sheets has automatically changed the row number in your cell reference (F2) for all of the following rows.
Change data type: You might see a “Value” error in I9. This is because Google Sheets sees “400” in F9 as a number, whereas the REGEXREPLACE function in your formula only takes text as input. Click on the F at the top of the column to select the whole column, then click on the “123” button in the toolbar and change the type to “Plain text”.
Adapt the formula: Unfortunately our formula has a problem. In I4 it shows “6.1000” because F4 has “6.1K”. But we can write another formula to take care of that. In J2 put =REGEXREPLACE(I2,"\.(\d)0","$1"). This RegEx says to find a . (remember \ is an escape character) followed by a digit (\d in RegEx), followed by a 0. The () around the digit makes it group 1. In the replacement input, this is $1, meaning, “Just keep group 1, nothing more.” Now copy that formula to the other rows. Row 4 (and the other rows with decimals in Column F) should now show the correct number.

You did it! Once you start using spreadsheets and formulas, nothing can stop you. You might plan a Hollywood wedding on another planet (or even file your taxes).