OpenRights Entertainment Rights & Catalog Intelligence

Tutorials

Excel Macros & Google Sheets for Rights Management

Practical, step-by-step guides to automate your catalog management workflows. Every tutorial includes copy-paste code and downloadable files.

Category
Domain
Level

Showing 24 of 24 tutorials

Data Cleaning beginner

Detect Invisible Blank Cells That Look Filled

A cell with just a non-breaking space LOOKS blank but ISBLANK returns FALSE, COUNTA counts it, and your required-field validation misses it. Two formulas to catch both.

· 4 min read
Data Cleaning intermediate

Normalize Quarter Codes: 'Q1 2026', '2026-Q1', '202601' in One Formula

Platforms encode quarters four different ways. One nested-IF formula normalizes all of them to canonical YYYY-Qn so reconciliation joins actually work.

· 5 min read
Data Cleaning intermediate

Spot an MG That's Already Fully Recouped in Your Contract Register

Licensor is owed post-recoupment royalties — but only if you know the MG is recouped. SUMIF across reported gross × royalty %, compare to MG balance, flag recouped rows.

· 6 min read
Data Cleaning beginner

Convert 'United Kingdom' to 'GB' in One VLOOKUP

Platforms reject country names when they want ISO codes. One lookup sheet + one VLOOKUP with IFERROR flags unknowns as you go.

· 3 min read
Data Cleaning advanced

Detect Overlapping Exclusive Windows in the Same Territory

Two exclusive SVOD grants to different licensees in GB, same period — the contract is legally unfulfillable. One formula surfaces every overlap in your avails file.

· 7 min read
Excel Macros beginner

Pad Invoice Numbers with Leading Zeros in Excel and Sheets

INV-1 and INV-001 should sort and match as the same format. TEXT() gives you a padded display; a custom cell format keeps underlying values numeric.

· 3 min read
Google Sheets beginner

Why Your COUNTIF Misses Duplicates with Trailing Whitespace

COUNTIF sees 'Ember Lanes' and 'Ember Lanes ' as different values. One TRIM + CLEAN wrapper catches what bare COUNTIF silently misses.

· 4 min read
Data Cleaning intermediate

Expand 'EMEA excluding Turkey' Into a Flat ISO Territory List

Formula-driven expansion of region codes with exceptions — no Apps Script required. Handles EMEA, APAC, LATAM, and arbitrary exclusion lists in one formula.

· 6 min read
Data Cleaning intermediate

Detect Non-ASCII Characters Breaking Your Platform Avails Ingest

Smart quotes, non-breaking spaces, and em-dashes silently break avails ingest. One regex formula surfaces every offending cell so you can fix before shipping.

· 5 min read
Google Sheets beginner

Why Does My Avails End Date Show as Text Instead of a Date?

Your End Date column looks like dates but COUNTIF and MIN won't work, conditional formatting misses rows. One formula fix: DATEVALUE + column format change.

· 4 min read
Data Cleaning advanced

Audit Royalty Calculations Against Contract Terms

Advanced workflow to cross-check platform-reported royalties against contract % splits, MG recoupment status, and holdback windows. Variance report highlights underpayment and reporting gaps.

· 12 min read
Data Cleaning intermediate

Clean Royalty Statements from Platforms into a Canonical Schema

Normalize messy Netflix, Amazon, and Apple royalty CSVs into a canonical title/territory/period schema using an Apps Script that handles each platform's quirks.

· 11 min read
Data Cleaning beginner

Detect Duplicate and Missing Payments with COUNTIFS and Aging Buckets

Beginner workflow using COUNTIFS + aging-bucket formulas to surface duplicate payments and overdue invoices every month. Pure formulas — no scripts.

· 6 min read
Data Cleaning intermediate

Reconcile Payments Against Invoices

Match a bank-side payment batch against your invoice register. Flag duplicate payments, missing payments, and amount mismatches in one joined view.

· 9 min read
Data Cleaning intermediate

Normalize Territory Codes Across ISO, IFTA, and Platform-Specific Formats

Build a lookup sheet that maps ISO α-2 codes to IFTA territory names to platform-specific variants (Amazon UK vs GB, Netflix country names, etc.) so your avails survive every ingest.

· 8 min read
Data Cleaning intermediate

Validate an Avails Sheet Against IFTA + EMA Rules

Apps Script that checks an avails file against IFTA territory codes, EMA date coherence rules, and required-field presence. Writes a single Validation Errors report you can work through row by row.

· 10 min read
Data Cleaning beginner

Standardize Title Metadata Across Your Catalog

Beginner-friendly workflow for normalizing title records to controlled vocabularies for genre, rating, and language using dropdown validation and EIDR format checks.

· 7 min read
Data Cleaning intermediate

Deduplicate Titles by EIDR/ISAN + Fuzzy Title Match

Apps Script that finds duplicate titles via EIDR/ISAN exact match AND Levenshtein fuzzy matching. Catches typo-induced duplicates that pure-key matching misses.

· 11 min read
Data Cleaning beginner

Clean and Deduplicate Your Rights-Holder Contacts

Beginner-friendly workflow for deduplicating rights-holder contacts using Google Sheets formulas and a normalized-key approach that catches casing and punctuation drift.

· 8 min read
Data Cleaning beginner

Validate Contract Metadata Before Filing

A 12-field checklist for validating a contract's metadata completeness before it lands in your register. Catch missing term dates, ambiguous territory grants, and orphan exhibits at ingest.

· 7 min read
Excel Macros advanced

Excel Macro: Bulk Validate EIDR and ISAN Identifiers in Your Title List

VBA macro that validates EIDR and ISAN identifier formats and checksums across your entire catalog, flagging invalid entries for correction.

· 10 min read
Google Sheets beginner

5 Google Sheets AppScript Automations Every Catalog Manager Needs

Copy-paste AppScript snippets for territory code validation, duplicate detection, rights window overlap checking, expiry alerts, and auto-sorting.

· 12 min read
Apps Script beginner

Getting Started with Google Apps Script (for Sheets Users Who Have Never Touched It)

Activate Apps Script in Google Sheets, paste your first function, authorize it, and run it — with a clear explanation of what the OAuth consent screen actually wants.

· 12 min read
Excel Macros intermediate

How to Build an Avails Tracker in Excel That Actually Scales

Step-by-step guide to building a multi-territory avails tracker in Excel with VBA macros for filtering, validation, and automatic expiry highlighting.

· 15 min read