How to Build an Avails Tracker in Excel That Actually Scales

If you manage rights for more than a handful of titles across multiple territories, you have almost certainly watched a spreadsheet avails tracker collapse under its own weight. It starts innocently enough: a few columns for US and UK rights, some date ranges, maybe a color or two to indicate status. Then someone adds territories for LATAM. Then APAC. Then you need separate columns for SVOD, TVOD, EST, Free TV, Pay TV, and theatrical windows. Before long you are staring at a 200-column sheet with inconsistent country codes (is it “UK” or “GB”?), dates in three different formats, and no way to quickly answer the question a buyer actually asked: “What do you have available in France for SVOD starting Q3?”

This tutorial builds an avails tracker that holds up. It uses structured sheets, data validation, and VBA macros to keep territory codes clean, highlight expiring rights automatically, and filter the grid on demand. You will need Excel 2016 or later with macros enabled.

Step 1: Set Up the Title Master Sheet

Create a new workbook and rename the first sheet to TitleMaster. Set up the following columns starting in row 1:

Column	Header	Notes
A	Title	Full title name
B	EIDR	EIDR content ID (e.g., 10.5240/XXXX-XXXX-XXXX-XXXX-XXXX-X)
C	Genre	Primary genre
D	Year	Release year (4-digit number)
E	Rights Holder	Licensor or rights owner entity
F	Status	Active, Expired, or Pending

Format row 1 as bold with a light blue background. Freeze the top row (View > Freeze Panes > Freeze Top Row). This sheet is your single source of truth for title metadata. Every other sheet will reference it.

Step 2: Build the Avails Grid

Create a second sheet called AvailsGrid. This is where territory-by-window availability lives. Structure it as follows:

Column	Header
A	Title
B	Territory
C	Window Type
D	Start Date
E	End Date
F	Exclusivity
G	Status

Each row represents one rights window for one title in one territory. This flat structure is far more scalable than the territory-as-columns approach. A catalog of 500 titles across 40 territories and 5 window types will produce up to 100,000 rows, which Excel handles without issue. Add Data Validation to column C with a list: SVOD,TVOD,EST,Free TV,Pay TV,Theatrical. Add Data Validation to column F with a list: Exclusive,Non-Exclusive. Add Data Validation to column G with a list: Available,Licensed,Holdback,Expired.

Step 3: Territory Code Validation Macro

Create a third sheet called Ref_Territories and populate column A with valid ISO 3166-1 alpha-2 codes (US, GB, FR, DE, JP, AU, BR, MX, IN, KR, etc.). Name the range ValidTerritories.

Now open the VBA editor (Alt+F11), insert a new module, and paste this macro:

Sub ValidateTerritoryCode()
    Dim ws As Worksheet
    Dim refWs As Worksheet
    Dim lastRow As Long
    Dim i As Long
    Dim territoryCode As String
    Dim validCodes As Collection
    Dim refLastRow As Long
    Dim errorCount As Long

    Set ws = ThisWorkbook.Sheets("AvailsGrid")
    Set refWs = ThisWorkbook.Sheets("Ref_Territories")

    ' Build a collection of valid codes for fast lookup
    Set validCodes = New Collection
    refLastRow = refWs.Cells(refWs.Rows.Count, "A").End(xlUp).Row
    For i = 1 To refLastRow
        On Error Resume Next
        validCodes.Add refWs.Cells(i, 1).Value, refWs.Cells(i, 1).Value
        On Error GoTo 0
    Next i

    ' Validate each territory code in the AvailsGrid
    lastRow = ws.Cells(ws.Rows.Count, "B").End(xlUp).Row
    errorCount = 0

    For i = 2 To lastRow
        territoryCode = UCase(Trim(ws.Cells(i, 2).Value))
        ws.Cells(i, 2).Interior.ColorIndex = xlNone ' Reset formatting

        If Len(territoryCode) = 0 Then
            GoTo NextRow
        End If

        ' Check if territory code exists in valid codes
        Dim found As Boolean
        found = False
        On Error Resume Next
        Dim testVal As Variant
        testVal = validCodes(territoryCode)
        If Err.Number = 0 Then found = True
        Err.Clear
        On Error GoTo 0

        If Not found Then
            ws.Cells(i, 2).Interior.Color = RGB(255, 200, 200) ' Light red
            errorCount = errorCount + 1
        Else
            ws.Cells(i, 2).Value = territoryCode ' Normalize to uppercase
        End If
NextRow:
    Next i

    If errorCount > 0 Then
        MsgBox errorCount & " invalid territory code(s) found and highlighted in red.", vbExclamation
    Else
        MsgBox "All territory codes are valid.", vbInformation
    End If
End Sub

This macro reads every territory code in the AvailsGrid, checks it against your reference list, highlights invalid entries in red, and normalizes valid codes to uppercase.

Step 4: Expiry Highlighting Macro

This macro scans the End Date column and applies color coding based on how close each rights window is to expiring:

Sub HighlightExpiries()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim i As Long
    Dim endDate As Date
    Dim daysLeft As Long

    Set ws = ThisWorkbook.Sheets("AvailsGrid")
    lastRow = ws.Cells(ws.Rows.Count, "E").End(xlUp).Row

    For i = 2 To lastRow
        ' Reset formatting on the end date cell
        ws.Cells(i, 5).Interior.ColorIndex = xlNone
        ws.Cells(i, 5).Font.Bold = False

        If IsDate(ws.Cells(i, 5).Value) Then
            endDate = CDate(ws.Cells(i, 5).Value)
            daysLeft = endDate - Date

            If daysLeft < 0 Then
                ' Already expired - red background, bold text
                ws.Cells(i, 5).Interior.Color = RGB(255, 100, 100)
                ws.Cells(i, 5).Font.Bold = True
                ws.Cells(i, 7).Value = "Expired" ' Update status
            ElseIf daysLeft <= 30 Then
                ' Expiring within 30 days - amber/orange
                ws.Cells(i, 5).Interior.Color = RGB(255, 180, 100)
                ws.Cells(i, 5).Font.Bold = True
            ElseIf daysLeft <= 90 Then
                ' Expiring within 90 days - yellow
                ws.Cells(i, 5).Interior.Color = RGB(255, 255, 150)
            End If
        End If
    Next i

    MsgBox "Expiry highlighting complete.", vbInformation
End Sub

Red means expired. Amber means 30 days or fewer remain. Yellow means 90 days or fewer. Anything further out stays unformatted. The macro also automatically updates the Status column to “Expired” for past-due entries.

Step 5: Filtering by Territory or Window Type

This macro prompts the user to filter the avails grid by territory, window type, or both:

Sub FilterAvails()
    Dim ws As Worksheet
    Dim territoryFilter As String
    Dim windowFilter As String

    Set ws = ThisWorkbook.Sheets("AvailsGrid")

    ' Clear any existing filters
    If ws.AutoFilterMode Then ws.AutoFilterMode = False

    territoryFilter = InputBox("Enter territory code to filter (or leave blank for all):", "Filter Avails")
    windowFilter = InputBox("Enter window type to filter (SVOD, TVOD, EST, Free TV, Pay TV, Theatrical) or leave blank:", "Filter Avails")

    ' Apply AutoFilter to the data range
    Dim lastRow As Long
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    ws.Range("A1:G" & lastRow).AutoFilter

    If Len(Trim(territoryFilter)) > 0 Then
        ws.Range("A1:G" & lastRow).AutoFilter Field:=2, Criteria1:=UCase(Trim(territoryFilter))
    End If

    If Len(Trim(windowFilter)) > 0 Then
        ws.Range("A1:G" & lastRow).AutoFilter Field:=3, Criteria1:=Trim(windowFilter)
    End If

    MsgBox "Filter applied. Use the dropdown arrows to adjust, or run this macro again to change filters.", vbInformation
End Sub

Step 6: Add a Refresh All Button

Back in the VBA editor, add one more macro that chains everything together:

Sub RefreshAll()
    Call ValidateTerritoryCode
    Call HighlightExpiries
    MsgBox "All validations and formatting have been refreshed.", vbInformation
End Sub

To create the button: go to the AvailsGrid sheet, click Insert > Shapes, draw a rounded rectangle, type “Refresh All” on it, right-click the shape, select Assign Macro, and choose RefreshAll. Now a single click validates territory codes and updates expiry highlighting across the entire grid.

Tips: When to Outgrow This Spreadsheet

This tracker works well for catalogs up to a few thousand titles. You will start hitting limits when:

Multiple people need to edit the avails grid simultaneously
You need audit trails showing who changed what and when
Buyers need self-service access to check availability
You are managing complex holdback and exclusivity rules that overlap across territories and window types
You need to ingest avails data from or push it to platforms like iTunes Connect, YouTube CMS, or Amazon

At that point, consider dedicated rights management software that can handle multi-user access, automated conflict detection, and platform integrations.

Download the pre-built version of this tracker: Avails Tracker Template