How to Automate Reference Screening with Zotero and Excel

By Dr. Zubair Khalid, DVM, MS, PhD · 2026-08-27

Quick Answer

• Export your Zotero collection to Excel, then use formulas to flag duplicate titles and screen keywords across title and abstract fields.
• Record screening decisions in a dedicated Excel column and re-import the annotated file into Zotero to preserve your work.
• This semi-automated approach reduces manual effort but does not replace human judgment for eligibility decisions.

Understanding the Reference Screening Problem

Systematic reviews, scoping reviews, and large literature searches generate hundreds or thousands of candidate references. Screening each title and abstract manually consumes days or weeks of research time. Researchers without programming skills often believe automation is out of reach, yet a practical middle ground exists using tools most researchers already own.

Zotero is a free, open-source reference manager that stores citations, PDFs, and metadata. Excel is a spreadsheet application that can sort, filter, and apply formulas to structured data. Together, they form a semi-automated screening pipeline that requires no coding knowledge.

The workflow operates in three phases. First, you export a Zotero collection to a structured file format. Second, you use Excel formulas to flag duplicates and identify records matching your inclusion keywords. Third, you record screening decisions and re-import the annotated data back into Zotero.

This approach suits individual researchers, small teams, and students conducting course-based reviews. It also works for laboratory staff tracking animal studies, veterinary professionals reviewing clinical literature, and life-science professionals managing evidence for grant applications or institutional reports.

Core Principles of Semi-Automated Screening

Screening Is a Reproducible Process

Reference screening should follow a documented, repeatable procedure. The National Library of Medicine Research Methods Resources gateway provides access to biomedical books and research-method references that describe structured approaches to literature review. A reproducible screening process means another researcher can follow your steps and reach similar decisions.

Document your screening protocol before you begin. State your research question, your inclusion criteria, your exclusion criteria, and your keyword list. Record the date you ran each database search and the date you exported references into Zotero. This documentation supports transparency and allows others to verify your work.

Reporting Guidelines Shape Screening Decisions

Reporting guidelines help researchers decide what information to collect and report during a review. The EQUATOR Network maintains a comprehensive collection of reporting guidelines for health research, including PRISMA for systematic reviews and other reporting standards for observational studies, diagnostic accuracy studies, and qualitative research.

Select the reporting guideline that matches your study design before you begin screening. The guideline will tell you which screening steps to document and which data to extract. For example, a PRISMA-style flow diagram requires you to report the number of records identified, screened, excluded, and included. Your Excel screening log should capture these numbers automatically through formulas and filters.

Ethics and Integrity in Screening Records

Screening decisions are research data. The Committee on Publication Ethics Core Practices describe expectations for data integrity, authorship, and peer review. Apply these principles to your screening workflow by maintaining accurate records, avoiding selective reporting, and preserving your original screening decisions even when you later change an eligibility judgment.

Do not delete excluded records from your spreadsheet. Mark them as excluded with a reason code. This preserves the audit trail and allows reviewers to verify your decisions. If a co-author disagrees with a screening decision, the original record remains available for discussion.

Setting Up Zotero for Screening

Organize Your Collection

Create a dedicated Zotero collection for each screening project. Name the collection with your project identifier and date, such as "Systematic Review Canine OA 2025-06." This naming convention prevents confusion when you manage multiple reviews simultaneously.

Import all references from your database searches into this collection. Zotero can import RIS files, BibTeX files, and other common citation formats. Check that each record contains a title, abstract, authors, and source information. Records missing abstracts may require manual lookup or exclusion from abstract-level screening.

Clean Your Metadata Before Export

Metadata quality determines screening quality. Zotero imports sometimes produce duplicate records, incomplete titles, or garbled characters. Review your collection for obvious errors before exporting to Excel.

Check for duplicate records by sorting your Zotero library by title and scanning for repeated entries. Zotero has a built-in duplicate detection feature that groups suspected duplicates. Resolve duplicates before export to reduce the number of rows you must screen in Excel.

Verify that each record has a stable identifier such as a DOI, PMID, or ISBN. These identifiers help you match records across databases and retrieve full texts later. Records without identifiers may require manual verification.

Exporting Zotero Collections to Excel

Choose the Right Export Format

Zotero offers several export formats. For Excel screening, use the CSV (comma-separated values) format. CSV files open directly in Excel and preserve the structured fields you need for screening.

To export, right-click your collection in Zotero, select "Export Collection," choose "CSV" as the format, and save the file. Zotero exports the fields you have selected in your export preferences. Ensure your export includes the title, abstract, authors, publication date, DOI, and item type fields.

Structure Your Export for Screening

A clean export structure makes Excel formulas easier to write. The default Zotero CSV export includes columns for each metadata field. You will add new columns for screening decisions, keyword flags, and duplicate flags.

Open the CSV file in Excel and inspect the column headers. Confirm that the title column contains complete titles and the abstract column contains the full abstract text. If abstracts are missing from many records, consider whether your review requires abstract-level screening or whether title-only screening is sufficient.

Save Your Working File

Save the CSV file as an Excel workbook (XLSX format) before you begin editing. This preserves your original export and creates a working file for screening. Name the working file with your project identifier and version number, such as "Screening Log Canine OA v1.xlsx."

Keep the original CSV export unchanged as a backup. If you make a formula error or accidentally delete data, you can return to the original export and start again.

Building the Excel Screening Workflow

Add Screening Decision Columns

Add columns to your Excel workbook for each screening decision you need to record. A typical screening workbook includes these columns:

• Screening status (include, exclude, uncertain)
• Exclusion reason code
• Screener initials
• Screening date
• Notes

Create a data validation list for the screening status column. This restricts entries to the allowed values and prevents typographical errors. Use Excel's Data Validation feature under the Data tab to create a dropdown list with your status options.

Flag Duplicate Records with Formulas

Duplicate detection in Excel uses the COUNTIF function to identify repeated values in a column. Add a new column labeled "Duplicate Flag" and enter a formula that counts how many times the title in the current row appears in the title column.

The formula structure is:

=COUNTIF($B$2:$B$500, B2)

This formula counts occurrences of the title in cell B2 across the range B2 to B500. A result greater than 1 indicates a duplicate. Add a second formula that labels the first occurrence as "Keep" and subsequent occurrences as "Duplicate":

=IF(COUNTIF($B$2:B2, B2)=1, "Keep", "Duplicate")

This formula counts only the cells from the top of the column to the current row. The first occurrence returns 1 and is labeled "Keep." Subsequent occurrences return higher numbers and are labeled "Duplicate."

Screen Keywords with Excel Formulas

Keyword screening uses the SEARCH function to look for inclusion terms in the title and abstract columns. The SEARCH function returns the position of a text string within another text string and is not case-sensitive.

Add a column for each keyword or keyword group in your screening protocol. For example, if your review includes studies on canine osteoarthritis, you might create columns for "canine," "dog," "osteoarthritis," and "joint disease."

The formula structure for a single keyword is:

=IF(ISNUMBER(SEARCH("osteoarthritis", C2)), "Yes", "No")

This formula checks whether the text "osteoarthritis" appears in cell C2, which contains the title or abstract. If the text appears, the formula returns "Yes." If not, it returns "No."

Combine Keywords into a Screening Decision

A single keyword rarely determines eligibility. Combine multiple keyword flags into an overall screening recommendation using the OR and AND functions.

For example, to flag records that mention either "canine" or "dog" AND "osteoarthritis," use:

=IF(AND(OR(D2="Yes", E2="Yes"), F2="Yes"), "Screen", "Skip")

This formula checks whether the record mentions a dog-related term and an osteoarthritis-related term. Records meeting both conditions are flagged for full screening. Records failing the condition are flagged for potential exclusion.

Use Filters to Prioritize Screening Work

Excel filters allow you to view subsets of your data based on column values. Apply a filter to your screening workbook and sort by the keyword recommendation column. Screen the records flagged "Screen" first, then review the "Skip" records to confirm they are truly irrelevant.

Filters also help you manage large datasets. If you have 2,000 records, you can filter to show only records with missing abstracts, only duplicates, or only records from a specific database. This targeted approach reduces the cognitive load of screening.

Recording Screening Decisions

Assign Screening Status Codes

Use consistent status codes for your screening decisions. Common codes include:

• Include
• Exclude
• Uncertain
• Full text needed

Record the status in the screening status column. Add an exclusion reason code when you mark a record as excluded. Common reason codes include:

• Wrong population
• Wrong intervention
• Wrong outcome
• Wrong study design
• Duplicate
• No abstract available

Create a code sheet in a separate worksheet within your Excel workbook. List each code and its definition. This documentation helps co-screeners apply codes consistently.

Track Screener Performance

If multiple people screen the same records, add a screener initials column and a screening date column. This allows you to calculate inter-rater agreement and identify records requiring adjudication.

To measure agreement, use Excel formulas to compare screening decisions across screeners. A simple approach is to create a column that flags disagreements:

=IF(G2=H2, "Agree", "Disagree")

This formula compares the screening decision in column G with the decision in column H. Records flagged "Disagree" require discussion and resolution.

Document Your Screening Log

Your Excel workbook is your screening log. Preserve it as part of your research records. The National Institutes of Health Grants and Funding pages describe expectations for grant applications and award management, including the need for accurate research records. A complete screening log supports your grant reports and publications.

Save a version of your screening log after each screening session. Use version numbers or dates in the file name. This creates a chronological record of your screening decisions and protects against accidental data loss.

Re-Importing Screening Decisions into Zotero

Prepare Your Annotated File for Import

After completing screening in Excel, you may want to organize your Zotero library based on your decisions. Zotero does not directly import Excel screening status columns, but you can use your screening log to create subcollections.

Filter your Excel workbook to show only records marked "Include." Export this filtered view as a CSV file. Import this CSV into Zotero using the "Import" function under the File menu.

Create Subcollections for Included Records

Create a new Zotero collection named "Included Studies" or a similar project-specific name. Import the included records into this collection. This gives you a clean collection for full-text retrieval and data extraction.

Repeat this process for records marked "Full text needed." Create a separate collection for these records so you can track which PDFs you need to locate.

Preserve Your Original Collection

Do not delete records from your original Zotero collection. The original collection represents all records identified by your search. The included subcollection represents your screening decisions. Keeping both collections preserves the audit trail and allows you to regenerate your PRISMA flow diagram.

Using Excel Formulas for Screening Metrics

Calculate Screening Yield

Screening yield describes the proportion of records that pass each screening stage. Use Excel formulas to calculate these metrics from your screening log.

To calculate the inclusion rate, divide the number of included records by the total number of records screened:

=COUNTIF(F2:F500, "Include") / COUNTA(F2:F500)

Format the result as a percentage. This metric tells you how selective your screening criteria are and helps you estimate the workload for future reviews.

Track Exclusion Reasons

Use the COUNTIF function to count records by exclusion reason. Create a summary table in a separate worksheet:

This summary table feeds directly into your PRISMA flow diagram and helps you identify patterns in your exclusion decisions.

Monitor Screening Progress

Track your screening progress by counting the number of records with a screening status. Use the COUNTA function to count non-empty cells in the screening status column:

=COUNTA(F2:F500)

Compare this number to the total number of records to calculate your completion percentage:

=COUNTA(F2:F500) / COUNTA(A2:A500)

This progress metric helps you estimate remaining screening time and plan your work schedule.

At a Glance

Common Failure Patterns and How to Avoid Them

Failure Pattern 1: Incomplete Metadata

Records missing abstracts or titles create gaps in keyword screening. The SEARCH function returns "No" for missing text, which may incorrectly flag a relevant record for exclusion.

Prevention: Check your Zotero collection for missing abstracts before export. Use Zotero's "Find Available PDFs" feature or manually retrieve missing abstracts from publisher websites. Document the number of records without abstracts in your screening log.

Failure Pattern 2: Overly Broad Keyword Flags

Broad keywords generate many false positives. A keyword like "dog" appears in studies about dog breeds, dog behavior, and dog nutrition, even when the study is not relevant to your research question.

Prevention: Combine keywords with AND and OR logic to narrow your flags. Test your keyword combinations against a sample of known relevant and irrelevant records. Adjust your keywords until the flags match your manual screening decisions.

Failure Pattern 3: Formula Errors from Merged Cells

Merged cells in Excel break formulas that reference ranges. If you merge title cells across rows, your COUNTIF and SEARCH formulas may return incorrect results.

Prevention: Do not merge cells in your screening workbook. Use the "Center Across Selection" formatting option instead of merge if you need visual grouping. Keep your data in a flat table structure with one record per row.

Failure Pattern 4: Losing Track of Screening Versions

Saving over your screening log destroys your audit trail. If you later need to verify a screening decision, you cannot recover the original data.

Prevention: Save a new version after each screening session. Use a naming convention that includes the date and version number. Store backups in a cloud service or external drive.

Failure Pattern 5: Importing Filtered Data Incorrectly

Exporting a filtered view from Excel can produce unexpected results if the filter is not applied correctly. You may accidentally export hidden rows or miss records that should be included.

Prevention: Before exporting, verify that your filter shows the correct records. Check the row count in the status bar. Clear all filters before exporting the full dataset.

Limitations of the Semi-Automated Approach

Human Judgment Remains Essential

Excel formulas flag potential duplicates and keyword matches, but they cannot determine whether a study meets your eligibility criteria. A record may mention your keywords yet fail your inclusion criteria because of study design, population, or outcome measures.

The EQUATOR Network reporting guidelines emphasize transparent reporting of screening decisions. Your Excel flags support your screening process, but your final eligibility decisions require reading and judgment.

Formula-Based Screening Misses Context

SEARCH functions look for exact text strings. They cannot understand synonyms, abbreviations, or context. A study about "canine osteoarthritis" may use the term "degenerative joint disease" instead. Your keyword list must account for terminology variation.

Develop a comprehensive keyword list before screening begins. Consult your research question, relevant reviews, and subject experts to identify alternative terms. Test your keyword list against a sample of known relevant records.

Excel Has Practical Size Limits

Excel handles datasets with tens of thousands of rows, but very large datasets may slow down formula calculations. If your review identifies more than 10,000 records, consider whether Excel is the right tool or whether you need specialized systematic review software.

The National Library of Medicine Research Methods Resources gateway describes alternative approaches for large-scale reviews. For most student projects and small reviews, Excel performs adequately.

Integrating Screening with Research Data Management

Align with Data Management Expectations

The NIH Data Management and Sharing Policy describes expectations for managing and sharing research data. Your screening log is research data that may need to be preserved and shared according to your funder or institution requirements.

Plan for screening data management before you begin. Decide where you will store your screening files, how you will name them, and who has access. Document these decisions in your data management plan.

Maintain Researcher Identity and Records

The ORCID for Researchers pages describe how researchers can maintain a persistent identifier and connect their research outputs. Link your screening protocol and published review to your ORCID record to create a complete research profile.

When you publish your review, include your screening protocol as supplementary material. This allows other researchers to replicate your screening process and verify your results.

Prepare for Publication Ethics Requirements

The Committee on Publication Ethics Core Practices describe expectations for data integrity and transparency in publication. Journals may require you to provide your screening data upon request. A complete, well-documented screening log satisfies these expectations.

Keep your screening log for at least the duration required by your institution or funder. Many journals require data availability statements that describe where screening data can be accessed.

Practical Implementation Steps

Step 1: Define Your Screening Protocol

Write your research question, inclusion criteria, exclusion criteria, and keyword list before you begin. Share this protocol with your team and revise it based on feedback. A clear protocol prevents ad hoc screening decisions.

Step 2: Build Your Zotero Collection

Create a project-specific collection in Zotero. Import all references from your database searches. Check for missing metadata and resolve duplicates. Document the number of records imported from each database.

Step 3: Export and Prepare Your Excel Workbook

Export your Zotero collection as a CSV file. Open the CSV in Excel and save it as an XLSX workbook. Add your screening decision columns and data validation lists.

Step 4: Write and Test Your Formulas

Add duplicate flag and keyword flag columns. Test your formulas against a sample of 20 to 30 records. Compare the formula results with your manual screening decisions. Adjust your keywords and formulas until the flags match your judgment.

Step 5: Screen in Batches

Screen records in batches of 50 to 100. Record your decisions in the screening status column. Take breaks between batches to maintain concentration. Document any questions or uncertainties in the notes column.

Step 6: Resolve Disagreements

If multiple screeners are involved, compare decisions and flag disagreements. Discuss each disagreement and reach a consensus. Document the resolution in your screening log.

Step 7: Re-Import Included Records

Filter your Excel workbook to show included records. Export the filtered view as a CSV file. Import the CSV into Zotero and create an "Included Studies" collection.

Step 8: Generate Screening Metrics

Use Excel formulas to calculate inclusion rates, exclusion reason counts, and screening progress. Use these metrics in your PRISMA flow diagram and your final report.

Records and Measurements to Maintain

Maintain the following records throughout your screening project:

• Screening protocol document with research question and eligibility criteria
• Keyword list with version history
• Zotero collection with all imported references
• Original CSV export from Zotero
• Excel screening workbook with all decisions
• Version history of the screening workbook
• Screening metrics and summary tables
• PRISMA flow diagram data

Store these records in a project folder with a clear naming convention. Back up the folder to a cloud service or external drive. Share access with your research team as needed.

Professional Escalation Criteria

When to Seek Methodological Advice

Consult a librarian, methodologist, or systematic review expert if you encounter any of the following situations:

• Your search identifies more than 5,000 records and you are unsure whether Excel can handle the workload
• Your keyword flags produce very high or very low inclusion rates that seem inconsistent with your research question
• You are uncertain which reporting guideline applies to your study design
• Your team disagrees on screening decisions and cannot reach consensus

The EQUATOR Network can help you identify the appropriate reporting guideline. Your institutional library may offer systematic review support services.

When to Seek Ethics Guidance

Consult your institution's research integrity office or the Committee on Publication Ethics if you encounter:

• Suspected duplicate publication of the same study across multiple papers
• Questions about authorship of the screening team
• Concerns about data fabrication or falsification in screening records
• Pressure to exclude records that should be included

When to Seek Funding Guidance

If your screening is part of a grant-funded project, consult the National Institutes of Health Grants and Funding pages for guidance on grant requirements and reporting. Your program officer can answer questions about data management expectations and progress reporting.

Frequently Asked Questions

Can I use this workflow without any coding skills?

Yes. The workflow uses only Zotero's export function and standard Excel formulas. No programming knowledge is required. The COUNTIF, SEARCH, IF, OR, and AND functions are built into Excel and do not require macros or scripts.

How long does it take to screen 1,000 references with this method?

Screening time depends on the complexity of your eligibility criteria and the relevance of your search results. The Excel formulas reduce time spent on duplicate detection and keyword triage. Most researchers report that formula-based triage reduces screening time by 30 to 50 percent compared with reading every title and abstract.

What if my keywords miss relevant studies that use different terminology?

Develop a comprehensive keyword list before screening begins. Consult relevant reviews, subject experts, and database thesauri to identify synonyms and related terms. Test your keyword list against a sample of known relevant records and adjust it until the flags match your manual judgment.

Can I use Google Sheets instead of Excel?

Yes. Google Sheets supports the same functions used in this workflow, including COUNTIF, SEARCH, IF, OR, and AND. The main difference is that Google Sheets operates in the cloud, which facilitates team collaboration but requires an internet connection.

How do I handle records with missing abstracts?

Records without abstracts cannot be screened at the abstract level. Flag these records for full-text screening or manual title-based screening. Document the number of records without abstracts in your screening log and report this in your PRISMA flow diagram.

Does Zotero have built-in screening features?

Zotero is primarily a reference manager, not a systematic review tool. It does not have built-in screening workflow features. However, its export and import functions integrate well with Excel

Related Articles

• Reference Managers for Researchers: Choosing a Workflow That Survives Collaboration
• Scientific Literature Review: A Reproducible Search and Synthesis Workflow
• Scientific Literature Review Format: A Reproducible Structure for Researchers
• The Role of the Research Ethics Committee: What Researchers Need to Know
• Ncbi Reference Genome

References and Further Reading

• Research Methods Resources. National Library of Medicine.
• EQUATOR Network. EQUATOR Network.
• Core Practices. Committee on Publication Ethics.
• NIH Grants and Funding. National Institutes of Health.
• ORCID for Researchers. ORCID.
• Data Management and Sharing Policy. National Institutes of Health.
• NCBI Literature Resources. National Center for Biotechnology Information.
• PubMed. National Library of Medicine.

This article is educational and does not replace institutional policy, professional advice, or applicable safety and regulatory requirements.