A single Excel worksheet holds 1,048,576 rows and 16,384 columns. [1] That limit is not the main problem with a global patent-expiration database. The main problem is that the data does not stay still, and the rules that produce each date are different in every country.
This article explains where an Excel-based patent-expiration database fails. It uses statutes, court opinions, regulator records and documented spreadsheet failures. It also includes calculations that readers can repeat. DrugPatentWatch, a drug patent intelligence platform, appears where its data helps to explain the problem.
The Short Answer
Why does Excel fail at global patent-expiration tracking?
Excel can store a global patent-expiration list. It cannot keep the list correct. Four problems cause this: hard size limits, silent data conversion, jurisdiction-specific date rules, and source data that changes after you copy it.
A team that treats the spreadsheet as a database ends up with a file that looks current and is not. The sections below show each failure with a source.
The eight findings that matter
Excel allows 1,048,576 rows and 16,384 columns per worksheet. [1] If a database covers 134 countries and splits rows evenly, each country gets about 7,825 rows. This is a calculation, shown in the scale table below.
Ziemann, Eren and El-Osta found gene-name errors in 704 of 3,597 papers that had Excel gene lists. That is 19.6%. [3] The cause was default Excel conversion of text to dates and numbers.
Public Health England did not report 15,841 positive COVID-19 cases between 25 September and 2 October 2020. Reports attributed the failure to a spreadsheet that reached its maximum capacity. [2]
US patent term extension is capped at five years and at 14 years of term after approval. [7] An EU supplementary protection certificate is capped at five years. [6] Japan caps pharmaceutical extensions at five years. [16] The formulas differ.
In the worked example below, one hypothetical patent expires one year earlier in the EU than in the US because of the EU formula. This is an analysis based on the statutes, not a real drug.
On 20 December 2024, the Federal Circuit held that a patent must claim at least the active ingredient to be listed in the Orange Book. [4] On 21 May 2025, the FTC disputed more than 200 patent listings for 17 brand products. [5] A copy of the Orange Book can go stale in one court ruling.
The Purple Book lists only the patents a sponsor disclosed during the patent dance, and sponsors do not have to update the list. [15]
I-MAK’s September 2021 revision of its Humira report counts 257 patent applications and 130 granted patents. [19] A patent count does not tell you an expiration date.
What Is a Global Patent-Expiration Database?
Definition: patent expiration versus loss of exclusivity
A patent expiration date is the day a patent right ends. Loss of exclusivity (LOE) is the day a drug stops having legal protection from generic or biosimilar competition. The two dates are not the same.
In the US, a utility patent ends 20 years from its filing date. If the application claims an earlier application under the statute, the 20 years run from the earliest referenced filing date. [8] FDA also grants regulatory exclusivities that run separately from patents. A new chemical entity gets five years. An orphan drug gets seven years. A pediatric study adds six months to existing patents and exclusivities. [9]
A global database must therefore store at least two things for each drug: every protection right, and the date each right ends.
Original framework: six types of expiry date
This framework is original to this article. It is not an industry standard. It separates the dates that a single “expiry” column usually mixes together.
Type
Name
What sets the date
Example source
1
Base expiry
Filing date plus statutory term
35 U.S.C. § 154 [8]
2
Adjusted expiry
Base expiry plus patent office delay days
35 U.S.C. § 154(b) [8]
3
Extended expiry
Adjusted expiry plus a regulatory-review extension
The date generic entry is legally and practically possible
Analysis of types 1 to 5 plus settlements
Why types 1 to 3 need separate columns
A spreadsheet row with one “Expiry Date” cell hides which rule produced the date. A user cannot tell if a date includes an extension or if the extension is still pending. A database needs one field for each type, plus the source and the date of the last check.
Why type 6 is a judgment, not a field
Effective LOE depends on the other five types and on events outside any statute. A settlement can set a launch date that differs from every patent date. Analysts should label type 6 as an estimate and show the inputs.
Methodology and Limits of This Analysis
What data does this article use?
This article does not analyze a proprietary patent dataset. It analyzes public legal and regulatory text and applies it to a spreadsheet design. The sources are: Microsoft’s Excel specifications, a Microsoft support note, a peer-reviewed study of Excel gene-name errors, a Public Health England statement, US statutes and regulations, an EU regulation, a Japan Patent Office document, a Federal Circuit decision as reported by counsel, an FTC press release, and an advocacy report on Humira.
Inclusion rules
A source is included if it states a rule, a number or an event that bears on building or maintaining a patent-expiration database. Each figure is attributed to the document where it appears. Where a figure comes from a calculation, the text says so.
Limits
The worked example uses hypothetical dates. It simplifies the statutes. It ignores due-diligence reductions, patent term adjustment, and the requirement that a patent be in force when the extension is filed. The article did not test patent identifier formats in Excel. Where it discusses that risk, it labels the point as an inference.
How Do Excel’s Hard Limits Affect a Patent Database?
Rows, columns and cell size
Excel limits a worksheet to 1,048,576 rows by 16,384 columns. A single cell holds up to 32,767 characters. [1] These limits apply to every worksheet in the workbook, so a design that needs more rows must split data across sheets or files.
Microsoft states that 64-bit Excel has no hard file size limit and that size depends on available memory. [1] The 32-bit version shares 2 GB of virtual address space between Excel, the workbook and add-ins. [1] A file that opens on one analyst’s computer can fail on another’s.
What does 134 countries mean for the row limit?
DrugPatentWatch states on its website that it covers 134 countries. [10] That is a vendor claim, and this article did not audit it. It is still a useful input for scale arithmetic.
If a database stores one row per patent per country, a worksheet has to share its 1,048,576 rows across all countries. The calculation is: 1,048,576 ÷ 134 = about 7,825 rows per country.
Now add events. Suppose a drug has 40 patents, each in 20 countries, and each patent-country pair has 5 recorded events such as grant, extension, opposition, payment and lapse. The calculation is: 40 × 20 × 5 = 4,000 rows per drug. At 4,000 rows per drug, one worksheet holds 1,048,576 ÷ 4,000 = about 262 drugs. These inputs are illustrative assumptions, not measured data.
Scale table
Calculation
Inputs
Result
Type
Rows per country
1,048,576 rows [1]; 134 countries [10]
About 7,825
Derived
Rows per drug
40 patents × 20 countries × 5 events
4,000
Assumption
Drugs per worksheet
1,048,576 ÷ 4,000
About 262
Derived from assumption
A wide layout does not solve this. A layout with one column per country uses 134 of 16,384 columns. [1] It then needs a separate column set for each date type, and every new country forces a schema change.
Why Do Patent Identifiers and Dates Break in Excel?
Silent conversion: what the Ziemann study found
Ziemann, Eren and El-Osta screened 35,175 supplementary Excel files from papers published between 2005 and 2015. [3] They found 7,467 gene lists in 3,597 papers. They confirmed gene-name errors in 704 papers. [3]
The error rate was 19.6% of papers with Excel gene lists. [3] The check is simple: 704 ÷ 3,597 = 19.57%, which rounds to the reported figure.
The mechanism matters more than the number. Excel’s default settings turned the gene symbol SEPT2 into the date “2-Sep”. They also turned the identifier 2310009E13 into the floating-point number 2.31E+13. [3]
Analysis: the study did not test patent identifiers, so this section does not claim that patent numbers break the same way. The inference is narrower. Any identifier that looks like a date, or like a number in scientific notation, meets the same type-guessing step when a user pastes or imports it with default settings. A team that imports patent data from several national offices should test every identifier format before it trusts the file.
The 1900 leap-year rule
Microsoft documents that Excel treats 1900 as a leap year, which is wrong. Lotus 1-2-3 made the same assumption, and Excel kept it for compatibility. [11] Microsoft says that correcting it would shift almost all dates in existing worksheets by one day and break serial-date compatibility with other programs. [11]
What is the real date risk for pharmaceutical patents?
The 1900 rule is a small risk for this use. Microsoft states that the WEEKDAY function returns wrong values only for dates before 1 March 1900. [11] No modern drug patent has a date in that range.
The larger date risk is that Excel stores a date as a number with a display format. A date cell cannot record which legal rule produced it, whether the date is confirmed or estimated, or when someone last checked it. Those facts must live in other columns, and nothing in Excel forces a user to fill them in.
What Happened When a Spreadsheet Hit Its Limit? The Public Health England Case
Timeline of the 2020 COVID-19 reporting failure
The Public Health England case is the clearest documented example of a spreadsheet losing records without an error message. The dates and figures below come from a PHE statement as reported by DIGIT on 5 October 2020. [2]
Date
Event
Source
25 September to 2 October 2020
15,841 positive cases were not included in reported totals
PHE statement [2]
Overnight, Friday 2 October
PHE identified the technical issue
PHE statement [2]
By 01:00, 3 October
Missing cases passed to contact tracers
PHE statement [2]
3 and 4 October
Missing cases added to daily totals, which reached almost 13,000 and 23,000
DIGIT report [2]
After the incident
PHE split the Excel file into multiple smaller files
DIGIT report [2]
“After rapid investigation, we have identified that 15,841 cases between 25 September and 2 October were not included.” Michael Brodie, Interim Chief Executive, Public Health England, in a statement reported by DIGIT. [2]
The DIGIT article says the capacity detail came from reports and not from an official PHE statement. [2] This article therefore does not state the exact row limit that PHE’s file reached.
What the case means for patent data
Analysis: the failure pattern, not the subject, applies to patent data. A process that appends records to a spreadsheet can lose records when the file reaches a limit, and the user sees no alarm. A patent database that grows by pasting new country data into old sheets has the same exposure.
The PHE fix is also instructive. PHE split the data into smaller files. [2] That fix works for a single day of results. It does not work for a patent database, because users need to query across all countries and all years at once.
Which US Expiry Rules Must a Patent Database Model?
How does the 20-year term and patent term adjustment work?
A US patent term ends 20 years from the US filing date, or from the earliest referenced earlier filing date. [8] The statute then adds days when the USPTO causes delay. The three delay categories are office delays, a three-year pendency guarantee, and specific proceedings such as derivation proceedings or secrecy orders. [8]
The statute does not count overlapping delay days twice. It also reduces the adjustment when the applicant did not make reasonable efforts to conclude prosecution. [8] A spreadsheet that adds a flat number of days to every patent cannot reproduce this.
How does patent term extension under 35 U.S.C. § 156 work?
Patent term extension restores some of the term lost during FDA review. The rules have five parts:
The regulatory review period used for the extension may not exceed five years. [7]
The term remaining after approval, plus the extension, may not exceed 14 years. [7]
Only half of the testing phase counts. The full application-review phase counts. [7]
The owner must apply within 60 days after the product receives permission for commercial marketing. [7]
Only one patent may be extended for the same regulatory review period for any product. [7]
The last rule matters for database design. A drug can have many patents, but only one can carry a § 156 extension for a given review period. A spreadsheet must record which patent holds the extension.
Which exclusivities and stays sit outside the patent?
FDA lists these exclusivity periods: five years for a new chemical entity, seven years for an orphan drug, and six months for pediatric exclusivity. [9] The pediatric period “does not stand alone, but attaches to existing exclusivity,” according to FDA. [9] FDA also describes two 180-day exclusivities for ANDAs: Patent Challenge and Competitive Generic Therapy. [9]
A statutory stay can delay approval of a generic. If a patent owner sues within 45 days of a Paragraph IV notice, FDA may defer approval for the 30-month period that begins when the notice is received, subject to court-ordered changes. [12] Analysts often call this the 30-month stay.
Which patents may the Orange Book list?
FDA’s regulation allows three patent categories: drug substance, drug product (formulation and composition), and method of use. [13] The regulation also says that process patents, packaging patents, metabolite patents and intermediate patents “are not covered by this section.” [13]
The statute requires an applicant to submit the patent number and expiration date of each patent that could reasonably support an infringement claim. [12] The application holder must file patent information within 30 days after approval. It must file information on later patents within 30 days of issuance. [12]
Why Is the Orange Book Not a Clean Source of Truth?
FDA’s patent listing role is ministerial
FDA says its patent listing role is “ministerial.” [9] The agency lists patent information only if the NDA holder provides it on Form FDA 3542. [9] FDA also says it generally does not respond to questions about whether a specific patent can be listed. [9]
A listing therefore shows that someone submitted a patent. It does not show that the patent is valid, enforceable or eligible. A spreadsheet that copies Orange Book rows copies that gap.
Teva v. Amneal and the FTC challenges: a timeline
Date
Event
Source
20 December 2024
The Federal Circuit, in No. 24-1936, affirmed a District of New Jersey order that required Teva to delist five inhaler patents for ProAir HFA (albuterol sulfate). The patents claim device parts such as a dose counter and canister, and none recites albuterol sulfate.
Cooley LLP [4]
21 May 2025
The FTC disputed more than 200 Orange Book listings across 17 brand products used for asthma, diabetes, epinephrine autoinjector needs and COPD. Warning letters went to seven recipients across five named companies.
FTC press release [5]
The Federal Circuit held that “to claim that drug, the patent must claim at least the active ingredient.” [4] The FTC says the challenged patents “do not meet the statutory criteria for listing in the Orange Book, as confirmed by a recent ruling in the U.S. Court of Appeals for the Federal Circuit.” [5]
FTC Chairman Andrew N. Ferguson stated: “When firms use improper methods to limit competition in the market, it’s everyday Americans who are harmed by higher prices and less access.” [5]
What this means for a static spreadsheet copy
Analysis: a patent that a team copied into a spreadsheet in 2023 may no longer be listed. The ruling and the FTC letters changed what belongs in the Orange Book. A spreadsheet row holds no signal that its source changed.
FDA’s Orange Book page says the downloadable data files are updated monthly. It also links to lists of newly added and delisted patents. [14] A maintained database can use those lists. A spreadsheet needs a person to remember to check them.
What does the Purple Book show for biologics?
The Purple Book Continuity Act, enacted in December 2020, requires the reference product sponsor to submit to FDA the patent list it disclosed to a biosimilar applicant. The sponsor must do so within 30 days of the disclosure. [15] The list is not the sponsor’s full patent estate. [15]
Posting is not required until a first biosimilar applicant appears. [15] Sponsors also have no obligation to keep the posted information current. [15] A biologic row in a spreadsheet that relies only on the Purple Book can understate the patents that a biosimilar developer faces.
EU, Japan and Canada: Same Drug, Different Patent Clock
How does an EU supplementary protection certificate set an expiry date?
Regulation (EC) No 469/2009 sets the SPC rule in Article 13. The certificate takes effect at the end of the lawful term of the basic patent. [6] Its length equals the time between the patent filing date and the first EU marketing authorisation, reduced by five years. [6]
The duration cannot exceed five years from the date the certificate takes effect. [6] Six months are added where Article 36 of Regulation (EC) No 1901/2006 applies, and the period can be extended only once. [6]
The US and EU formulas use different inputs. The US formula counts half the testing phase plus the application phase. [7] The EU formula counts the whole span from filing to authorisation, minus five years. [6]
How does Japan extend pharmaceutical patent terms?
Japan allows an extension of up to five years under Article 67(4) of the Patent Act when a designated disposition, such as drug approval, prevented the patentee from working the invention. [16] Only the patentee may apply. The patentee must file within three months of obtaining the approval, and cannot file after the original 20-year term ends. [16]
Japan treats the extension as a right tied to each approval. A patent can receive separate extensions for separate dispositions that were needed to work it. [16] A substance patent, a use patent and a process patent may each receive an extension if the approval was needed for each. [16] Patents on intermediates, catalysts or manufacturing equipment are ineligible. [16]
How does Canada’s patent register and 24-month stay work?
In Canada, a patent does not appear on the Patent Register on submission alone. The Minister of Health decides whether a patent is eligible. The Minister checks whether it “claims a medicinal ingredient, formulation, dosage form or use set out in the first person’s regulatory submission.” [17]
If a patentee sues after a second person serves a notice of allegation, the action prevents the Minister from issuing a notice of compliance for 24 months after the action is filed. [17] A second person must address only the patents on the Register as of its filing date. [17] The Canadian stay is 24 months. The US stay is 30 months. [12][17]
What do the Unitary Patent and the UPC change?
The Unified Patent Court began operating on 1 June 2023. It covers 17 EU member states that participate in the agreement. [18] A European patent can take unitary effect across those states instead of being validated state by state. [18] Reed Smith places SPCs under the court’s jurisdiction for validity and infringement, and says SPC treatment depends on whether the linked patent has unitary effect. [18]
Analysis: a database now needs to record, for each European patent, whether it has unitary effect or national validations. That choice changes which court hears a dispute and which SPC regime applies.
Comparison table: expiry-extension rules by jurisdiction
Jurisdiction
Mechanism
Maximum extension
Key input
Filing window
Source
United States
Patent term extension, 35 U.S.C. § 156
Five years; 14 years of term after approval
Half of testing phase plus full application phase
60 days after approval
[7]
European Union
SPC, Regulation 469/2009
Five years, plus six months for paediatric rules
Filing date to first EU authorisation, minus five years
Not covered in sources reviewed
[6]
Japan
Patent term extension, Patent Act Art. 67(4)
Five years
Period the invention could not be worked during review
Three months after approval
[16]
Canada
Patent Register and 24-month stay
Not a term extension
Minister’s eligibility decision
Not covered in sources reviewed
[17]
Worked Example: One Hypothetical Patent, Three Scenarios
Inputs for the scenario analysis
This example uses hypothetical dates. It does not describe a real drug. It simplifies the statutes as stated in the Limits section. The purpose is to show that one patent produces different dates under different rules.
The patent is filed on 1 January 2000 in the US and the EU. Base expiry is 1 January 2020. [8]
The US patent issues on 1 January 2002. Testing starts on 1 January 2003. The NDA is submitted on 1 January 2010. FDA approves on 1 January 2012.
In scenario 1, the EU authorisation date is 1 January 2012. In scenarios 2 and 3, it is 1 January 2009.
In scenario 3, both US and EU pediatric rules apply.
Calculations and results
US calculation. The testing phase is 7 years, and half counts: 3.5 years. The application phase is 2 years. The total is 5.5 years. The five-year cap applies, so the extension is 5 years. [7] The 14-year check is: 1 January 2012 plus 14 years is 1 January 2026, and 1 January 2025 is earlier, so the cap does not bind. [7]
EU calculation, scenario 1. Filing to authorisation is 12 years. Minus 5 years gives 7 years. The five-year cap applies. The SPC runs from 1 January 2020 to 1 January 2025. [6]
EU calculation, scenario 2. Filing to authorisation is 9 years. Minus 5 years gives 4 years. The SPC runs from 1 January 2020 to 1 January 2024. [6]
Scenario 3 adds six months in each region. FDA says the pediatric period attaches to existing patents and exclusivities. [9] The EU rule adds six months to the periods in Article 13(1) and 13(2). [6]
Scenario
Base expiry
US end date
EU SPC end date
Gap
1: same approval date (1 Jan 2012)
1 Jan 2020
1 Jan 2025
1 Jan 2025
None
2: EU authorised 1 Jan 2009
1 Jan 2020
1 Jan 2025
1 Jan 2024
1 year
3: scenario 2 plus pediatric rules
1 Jan 2020
1 Jul 2025
1 Jul 2024
1 year
What the example shows
A column that holds only base expiry shows 1 January 2020 in all three scenarios. The extended dates are five years later in the US and four to five years later in the EU. The US and EU dates match only in scenario 1.
The NCE exclusivity in this example would end on 1 January 2017, five years after approval. [9] It does not control the outcome because the extended patent runs longer. A database must still store it, since it controls in cases where the patent is weaker or has already expired.
Patent Counts Are Not Expiry Dates: The Humira Case
What do the Humira numbers show?
I-MAK’s Humira report, in its September 2021 revision, counts 257 patent applications and 130 granted patents. It states that 90% of the applications were filed after FDA approval. [19] I-MAK is an advocacy organization. These counts are its own, and this article did not verify them against patent office records.
From those figures, 130 ÷ 257 = 50.6%. This is a calculation, and it shows that about half of the counted applications became granted patents. The calculation depends on how I-MAK defined its set.
What this means for a spreadsheet
Analysis: the same drug can show different patent counts depending on whether a team counts applications, granted patents, patent families, US patents only, or listed patents only. A column named “Number of patents” has no meaning without a definition. A database should store each patent record with its type, status and source, and should calculate counts from those records.
Counts also do not set expiry. Ten patents in one family can share a base expiry date. Two patents in different families can end years apart. The date comes from the filing date and the rules above, not from the count.
What Does a Database-Grade Alternative Need?
Requirements checklist
Analysis: a patent-expiration system that replaces Excel needs four functions.
Provenance for each field: a source, a retrieval date and a reviewer.
Jurisdiction rules kept as code or configuration, so the US, EU and Japan formulas are calculated and not typed by hand.
A change history that records delistings, court rulings and extension grants, with the date each took effect.
Validation at import: identifier type checks, row-count reconciliation and alerts when a load is smaller than the last one.
The second and fourth functions respond directly to the failures documented above. Jurisdiction rules address the formula gaps in the worked example. Import validation addresses the silent-loss pattern in the Ziemann and PHE cases. [2][3]
Where does DrugPatentWatch fit?
DrugPatentWatch is a commercial drug patent database. Its website states that it has operated since 2002, covers 134 countries and refreshes every drug profile from primary sources daily. [10] The site names these sources: the USPTO, the FDA Orange Book, ANDA filings, litigation records and patent registers across 134 countries. [10]
The site also lists a litigation tracker that gives access to complaints and court documents, a generic-entry opportunity view, and tracking of investigational compounds. [10] These are vendor statements. This article did not audit coverage or update frequency.
A platform like this removes the need to build the import, calculation and change-tracking layers. It does not remove the need to check primary sources for a decision that carries legal or financial weight. Analysts should open the Orange Book entry, the patent document or the court opinion before they act on any database date.
Build, buy or hybrid?
Analysis: a team has three options. It can build its own database, buy access to a maintained one, or keep a small internal model that reads from a maintained source. The hybrid option fits teams that need custom scenario work and do not want to maintain data collection for 134 countries.
When is Excel still the right tool?
Excel works for a snapshot of one drug in one jurisdiction. It works for scenario models that fit in a dozen cells, such as the worked example above. It works when a user adds a source column and a check date to every row.
It fails when the file becomes the system of record for many drugs and many countries, because no one can see which rows are stale.
Key Takeaways
An Excel worksheet holds 1,048,576 rows and 16,384 columns. [1] At 134 countries, an even split leaves about 7,825 rows per country. This is a calculation. [10]
Default Excel conversion affected 704 of 3,597 papers with gene lists, or 19.6%. [3] Teams should test every identifier format before import.
PHE did not report 15,841 cases between 25 September and 2 October 2020. [2] A spreadsheet can lose records with no visible error.
US, EU and Japan extensions follow different formulas, and each caps the extension at five years. [6][7][16] In the worked example, one hypothetical patent ends one year apart in the US and the EU.
Orange Book listings change through court rulings and regulator action. [4][5] FDA’s role in listing is ministerial. [9]
Patent counts need definitions. I-MAK’s Humira counts are 257 applications and 130 granted patents. [19]
FAQ
How many rows can an Excel worksheet hold?
One worksheet holds 1,048,576 rows and 16,384 columns. [1] A cell holds up to 32,767 characters. [1]
Can Excel hold patent data for 134 countries?
Excel can store it in several sheets. A single sheet gives about 7,825 rows per country if rows split evenly, which is a calculation from [1] and [10]. Maintaining the data is the harder problem.
Is patent expiration the same as loss of exclusivity?
No. A US patent ends 20 years from filing, plus any adjustment. [8] Regulatory exclusivities, such as five years for a new chemical entity, run separately. [9] LOE is the later practical result of all of them, plus stays and settlements.
How long can a US patent term extension last?
The review period used may not exceed five years. The remaining term after approval plus the extension may not exceed 14 years. [7] The owner must apply within 60 days of approval. [7]
How long is an EU supplementary protection certificate?
Its length is the time from patent filing to first EU authorisation, minus five years, capped at five years. [6] Paediatric rules can add six months. [6]
Does Japan extend pharmaceutical patents?
Yes, by up to five years. The patentee must file within three months of the approval. [16]
Does an Orange Book listing prove a patent is valid?
No. FDA calls its listing role ministerial. [9] The Federal Circuit also held on 20 December 2024 that a listed patent must claim at least the active ingredient. [4]
Does the Purple Book list every patent for a biologic?
No. It lists the patents the sponsor disclosed to a biosimilar applicant during the patent dance, and sponsors do not have to update the list. [15]
What is the 30-month stay?
If the patent owner sues within 45 days of a Paragraph IV notice, FDA may defer approval for 30 months from receipt of the notice. A court can change the period. [12]
Where can I find recently delisted Orange Book patents?
FDA’s Orange Book page links to lists of newly added and delisted patents. [14]
