Create a Movie Collection Database in Excel

Movie Collection Database in Excel

For a simple database, Excel can do a pretty good job of organizing and reporting your data. This example shows a movie collection database in Excel, but you could set up something similar to keep track of books, sales orders, or almost anything else.

In this database you can store movie data, then create a list of movies for a specific category or actor.

The Data Entry Sheet

On the data entry sheet, named Movies List, there’s a table with seven columns.

Movies database data entry

A unique ID number is entered for each movie, then the title, and other information about the movie.

The database was named MovieList, using a dynamic range. In Excel 2007 you could use an Excel Table instead, and in Excel 2003 you could use an Excel List.

The Movie Selector Sheet

On the report sheet, named Select Movies, there are two drop down lists – one for Category and one for Actor. Select from either of those, and code automatically runs, to filter a list of movies that match your selection.

The yellow cells are named (SelCat and SelActor), and the block of green cells is a range named ExtractMovies.

MoviesDb02

To see the code, right-click the Select Movies sheet tab, and click View Code.

The Criteria Sheet

The code runs an Advanced Filter, just as you could do manually. There’s a criteria area for each type of filter, stored on the CriteriaSel sheet.

If you select a Category from the drop down list, the Category criteria range is used for the Advanced Filter. If you select an actor, the Actor criteria range is used.

MoviesDb03

The Lists Sheet

The final sheet in the workbook stores the lists that are used in the yellow drop down cells. These are pivot tables, based on the MovieList database. The pivot tables are automatically refreshed when the workbook opens, and when you deactivate the data entry sheet.

MoviesDb04

Use the Database

To use the database, enter movie information on the Movies List sheet. Then, select a category or actor, and a report is created on Select Movies sheet, with all the movies that meet the selected criteria.

The criteria sheet and Lists sheet are automatically updated, so no changes need to be made to those sheets.

Download the Sample File

To see the code and test the sample file, you can download the Excel movies database file. It’s in Excel 2003 format (xls), and the file is zipped.

The file contains macros, so you’ll have to enable those to make the file work.
______________

8 thoughts on “Create a Movie Collection Database in Excel”

  1. How about a simple name list with numbered columns, having the number columns fixed while the name columns between them are sortable alphabetically in-place?

    For example, I have a folder that holds 208 discs and it came with an index sheet with six columns and 35 rows. (The last two places in column 6 are blank.)

    I made a simple table with 12 columns and 35 rows. Odd columns contain the numbers. Even columns contain the disc names.

    I want to be able to alphabetize the names in the even columns while leaving the numbers in the odd columns alone. Excel 2003 won’t sort dis-continuous selections. When I select all the cells in use and sort, it scrambles up the number columns.

  2. I really am not that good at excel. But I think I will give this one a try. Thanks for sharing this!

  3. Hi i wonder if you can help me, I absolutely love this database but i want to add a few more columns such as format (dvd, bluray), we buy, we sell, instore credit. how do i go about doing this with out spoiling the database.

  4. Could you let me know how you do this sheet ( Step by step 1,2,3 , …. ) i tried to make it again and i couldn’t !! 🙁
    Thanks for your attention

    1. I’m a noob… I like the concept of this template to use as a physician search tool, but I’m running into issues since I’m certain screwing up the code somewhere. anyone able to walk me through this a bit?

  5. I wish to create a form where I can combine titles of films released in the UK from 1930 to 2000; detailing: Title-Director-Date of Release; but accessed through personal choice of title, example, date films were releases, which film was released on a given date and directors of each film, accessible by director only as well

Leave a Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.