W-9 and 1099 eligibility tracker in Excel with Copilot or ChatGPT for 2026

45 minutes ago

10 min read

Cover for W-9 and 1099 eligibility tracker in Excel with Copilot or ChatGPT for 2026, Data Studios original cover

For payments made in 2026, the IRS minimum reporting threshold for nonemployee compensation on Form 1099-NEC, and for rents, other income and medical and health care payments on Form 1099-MISC, is $2,000, up from the $600 that applied for decades. The IRS instructions for Forms 1099-MISC and 1099-NEC state that the threshold may be adjusted for inflation beginning in calendar year 2027. Royalties keep their $10 threshold, and gross proceeds paid to an attorney stay at $600. A vendor list that was screened with the old $600 rule will therefore produce the wrong population of forms in January 2027 unless the logic in the spreadsheet is rebuilt.

Most small and mid-size US companies do this screening in Excel. Accounts payable exports a vendor payment report from QuickBooks, NetSuite, Sage or another ledger, someone adds the tax classification from each vendor's Form W-9, and a series of filters decides who gets a 1099-NEC, who gets a 1099-MISC, and who is exempt. Copilot in Excel and ChatGPT can build that tracker, write the eligibility formulas and flag missing W-9s, but they apply whatever rules the prompt gives them. A model that defaults to the $600 figure it has seen in years of training data, or that forgets to strip card payments out of the totals, produces a confident and wrong filing list.

The tracker below works on an illustrative ten-vendor list, gives the exact prompts and formulas to use with each tool, and shows the correct answer computed independently: three Forms 1099-NEC totaling $10,500, two Forms 1099-MISC totaling $22,450, and two vendors whose missing W-9 creates a backup withholding issue. The filing rules themselves are covered in the Data Studios article on Form 1099-NEC and 1099-MISC for 2026. The tracker here applies those rules to a payment export and checks the result against a known answer.

··········

WHAT A W-9 AND 1099 TRACKER HAS TO DECIDE.

A 1099 eligibility tracker answers four questions for every vendor paid during the calendar year. The first is whether the payee is exempt by its federal tax classification. The IRS instructions say payments to corporations are generally not reportable, with exceptions that include attorneys' fees on Form 1099-NEC and medical and health care payments and gross proceeds to attorneys on Form 1099-MISC. An LLC is classified according to the tax classification its owner selects on line 3a of the W-9: an LLC taxed as an S corporation or C corporation is treated as a corporation, while a single-member LLC that is disregarded reports under its owner.

The second question is what the payment was for. Payments for services go to Form 1099-NEC, rents and royalties go to Form 1099-MISC (box 1 and box 2), and payments for merchandise or goods are not reportable at all. A vendor that sells you both goods and services needs its payments split, and a payment export that carries one category per vendor will not do that on its own.

The third question is how the vendor was paid. The IRS instructions state that payments made with a credit card or payment card are reported on Form 1099-K by the payment settlement entity and are not subject to reporting on Form 1099-MISC or Form 1099-NEC. Only payments by check, ACH, wire or cash count toward the $2,000 test on the payer's forms.

The fourth question is whether a valid taxpayer identification number is on file. Under the IRS Instructions for the Requester of Form W-9, the backup withholding rate is 24% for reportable payments, and it applies when a payee does not furnish a TIN, when the IRS notifies the payer that the TIN is incorrect, or when the payee fails to certify. A tracker that flags missing W-9s in November gives the team time to collect them before the January 31 deadline for Form 1099-NEC.

··········

COPILOT IN EXCEL AND CHATGPT: DOCUMENTED REQUIREMENTS FOR THIS TASK.

The two tools approach the job differently. Copilot works inside the workbook and can write formulas and formatting directly into the sheet. ChatGPT reads an uploaded file and runs Python on it, then returns a new file or a table. The requirements below were checked on the vendors' official help pages on November 12, 2026.

........

Item	Copilot in Excel	ChatGPT data analysis
Access	Microsoft 365 Personal or Family with an AI credits plan, Microsoft 365 Premium, a commercial Copilot subscription, or a Copilot Chat-eligible business or enterprise subscription	ChatGPT account with file uploads
What it does	Generates formulas, PivotTables, charts and formatting; edits the workbook	Writes and runs Python (pandas) on the uploaded file
File limits	Modern format such as .xlsx; Calculation Options set to Automatic for editing	512 MB per file; spreadsheets about 50 MB; up to 10 files per conversation
Where data goes	Stays in the workbook; saved Copilot changes are visible to anyone with access	Uploaded to OpenAI; retention follows the chat and the plan
Training on data	Governed by Microsoft's Copilot data, privacy and security terms for work accounts	Not used for business offerings such as ChatGPT Enterprise; may be used for individual plans

........

Microsoft's FAQ for Copilot in Excel also notes that Copilot in Excel on Windows or Mac does not work with files from certain SharePoint sites, where Excel for the web does, and that a checked-out SharePoint file must be checked back in. For a W-9 tracker, the data-handling row decides more than the feature row. Taxpayer identification numbers are sensitive, and the safest design keeps the TIN column out of anything sent to a chatbot. The tracker only needs a "TIN on file" yes/no column to do its work.

··········

BUILDING THE VENDOR 1099 TRACKER: THE ILLUSTRATIVE DATASET.

The dataset is illustrative. It represents a vendor payment export for January to November 2026 after the tax classification from each W-9 has been added. Payments are already split by method, because card payments must be excluded before any threshold test, and each vendor has one payment type to keep the example readable.

........

ID	Vendor	W-9 tax classification	Payment type	Check/ACH paid	Card paid	W-9 on file
V01	Maria Lopez Design	Individual/sole proprietor	Services	3,400	600	Yes
V02	Brightline Consulting LLC	LLC taxed as S corporation	Services	12,000	0	Yes
V03	Harbor Legal PC	C corporation	Attorney fees	4,500	0	Yes
V04	Oakridge Properties LP	Partnership	Rent	22,200	0	Yes
V05	James Carter	Individual/sole proprietor	Services	1,800	0	Yes
V06	Pixel Freight Inc	C corporation	Services	8,750	0	Yes
V07	Nova Supplies LLC	Single-member LLC (disregarded)	Goods	15,300	0	Yes
V08	Sam Patel	Individual/sole proprietor	Services	1,500	2,500	Yes
V09	Elena Rossi	Individual/sole proprietor	Royalties	250	0	No
V10	Greenway Landscaping	Individual/sole proprietor	Services	2,600	0	No

........

Total payments in the list are $75,400, of which $3,100 were made by card and $72,300 by check or ACH. Several vendors are placed to test a specific rule. Sam Patel received $4,000 in total, but only $1,500 outside card networks, so he falls below the threshold. James Carter at $1,800 would have received a Form 1099-NEC under the old $600 rule and does not for 2026 payments. Harbor Legal PC is a corporation but receives attorneys' fees, which stay reportable. Nova Supplies is a disregarded LLC, so its classification would not exempt it, but it sold goods.

The same structure applies to a real export. If the ledger already tags vendors as 1099-eligible, compare those flags against the W-9 classification column rather than trusting them, because the flag is usually set once at vendor onboarding and never revisited. A similar classification exercise for bank lines is described in the Data Studios guide to categorizing bank transactions in Excel with Copilot, Claude or ChatGPT.

··········

THE 1099 ELIGIBILITY FORMULA IN EXCEL.

With the data formatted as an Excel table, one formula column can return the filing outcome for each vendor. The version below uses standard Excel functions and structured references, so it can be pasted into the table or used as the target when asking Copilot to write the column. Column names must match the headers of your table.

=LET(
  amt, [@[Check/ACH paid]],
  typ, [@[Payment type]],
  corp, ISNUMBER(SEARCH("corporation", [@[W-9 tax classification]])),
  IF(typ="Goods", "Not reportable (goods)",
  IF(AND(corp, typ<>"Attorney fees", typ<>"Medical"), "Exempt payee (corporation)",
  IF(typ="Medical", IF(amt>=2000, "1099-MISC box 6", "Below threshold"),
  IF(typ="Royalties", IF(amt>=10, "1099-MISC box 2", "Below threshold"),
  IF(typ="Rent", IF(amt>=2000, "1099-MISC box 1", "Below threshold"),
  IF(amt>=2000, "1099-NEC", "Below threshold")))))))

The formula tests goods first, then the corporate exemption with its attorney and medical exceptions, then the medical, royalty, rent and services thresholds. Medical and health care payments go to box 6 of Form 1099-MISC at $2,000 or more, including when the payee is a corporation. It reads only the check and ACH column, so card payments never enter the test. A second column flags missing documentation:

=IF(AND([@[W-9 on file]]="No", LEFT([@[1099 outcome]],4)="1099"), "Request W-9: backup withholding 24%", "")

··········

PROMPTS FOR COPILOT IN EXCEL AND CHATGPT.

The prompts state the 2026 thresholds and the card exclusion explicitly. Leaving them out is the most reliable way to get a tracker built on $600, because that number dominates the material the models were trained on.

Copilot in Excel prompt:
In the table VendorPayments, add a column "1099 outcome". Use only the
"Check/ACH paid" column; ignore "Card paid". Rules for 2026 payments:
Goods = "Not reportable (goods)". If "W-9 tax classification" contains
"corporation" and Payment type is not "Attorney fees" or "Medical" =
"Exempt payee (corporation)". Royalties >= 10 = "1099-MISC box 2".
Rent >= 2000 = "1099-MISC box 1". Medical >= 2000 = "1099-MISC box 6".
Services or Attorney fees >= 2000 =
"1099-NEC". Otherwise "Below threshold". Then add a column "W-9 flag"
that shows "Request W-9" when W-9 on file is "No" and the outcome starts
with "1099". Highlight those rows.

ChatGPT prompt (upload the file without TINs):
The attached file lists 2026 vendor payments. Using pandas, classify each
vendor with these rules: exclude card payments; goods are not reportable;
corporations (including LLCs taxed as S or C corporations) are exempt except
for attorney fees and medical payments; 1099-NEC for services and attorney
fees of $2,000 or more; 1099-MISC box 1 for rent of $2,000 or more; 1099-MISC
box 2 for royalties of $10 or more; 1099-MISC box 6 for medical payments
of $2,000 or more. Return a table with vendor, outcome and
amount, the count and total by form, and the vendors with reportable
payments and no W-9. Show the code you ran.

Asking ChatGPT to show its code turns the answer into something a reviewer can audit line by line. With Copilot, open the formula it writes in the new column and compare it with the reference formula above before accepting the change.

··········

EXPECTED RESULT: WHAT THE TRACKER SHOULD RETURN.

Computed independently in Python with the same rules, the correct outcome for the illustrative list is below. Any AI-built tracker that returns something different has a rule wrong, and the row where it differs shows which one.

........

ID	Vendor	Reportable amount	Outcome	W-9 flag
V01	Maria Lopez Design	3,400	1099-NEC	-
V02	Brightline Consulting LLC	0	Exempt payee (corporation)	-
V03	Harbor Legal PC	4,500	1099-NEC	-
V04	Oakridge Properties LP	22,200	1099-MISC box 1	-
V05	James Carter	0	Below threshold (1,800)	-
V06	Pixel Freight Inc	0	Exempt payee (corporation)	-
V07	Nova Supplies LLC	0	Not reportable (goods)	-
V08	Sam Patel	0	Below threshold (1,500)	-
V09	Elena Rossi	250	1099-MISC box 2	Request W-9
V10	Greenway Landscaping	2,600	1099-NEC	Request W-9

........

The summary the team should reconcile to is three Forms 1099-NEC for $10,500 (V01, V03 and V10) and two Forms 1099-MISC for $22,450 (V04 rent of $22,200 and V09 royalties of $250). Two payees with reportable payments have no W-9 on file. At the 24% backup withholding rate, the year's payments to them correspond to $624 for Greenway Landscaping and $60 for Elena Rossi, which shows the size of the issue if the forms cannot be obtained before payments continue. The Requester instructions also say that some payments, including nonemployee compensation, are subject to backup withholding immediately even when the payee returns a W-9 marked "Applied For".

........

Check	Correct value	Typical AI error
1099-NEC count	3	5 when $600 is used (adds V05 and V08)
1099-NEC total	10,500	15,100 if card payments are counted
1099-MISC count	2	1 if royalties are tested at $2,000
Harbor Legal PC	1099-NEC	Exempt, if the attorney exception is missed
Nova Supplies LLC	Not reportable	1099-NEC, if the payment type is ignored

........

The $15,100 figure in the second row is what results when card payments are counted: Sam Patel's $4,000 becomes reportable and Maria Lopez Design rises from $3,400 to $4,000. The error rows are examples of failure modes to test for, not observed outputs of either product.

··········

REVIEW CONTROLS BEFORE THE JANUARY 31 FILING.

The tracker is a working paper, and it needs the same controls as any other year-end schedule. Reconcile the total of the payment export (here $75,400) to the accounts payable subledger for the same period, so no vendor paid outside the main AP workflow is missing. Payments made through expense reports, petty cash or a second bank account are the usual gaps. The Data Studios explainer on accounts payable aging and supplier payments covers the subledger side.

Verify the tax classification against the signed W-9 itself for every vendor marked exempt. The Requester instructions allow reliance on an exempt payee claim unless the payer has actual knowledge that the code or classification is not valid, and they state that a certificate stops being valid once the payer knows or has reason to know that the circumstances have changed. A vendor whose name or entity changed during the year should be asked for a new form. For partnerships, the current W-9 has a line 3b indicating whether there are foreign partners, owners or beneficiaries, which the IRS says the requester may rely on unless it knows the information is incorrect.

Use the IRS TIN matching program where available. The Requester instructions describe it as a way for payers that must file information returns to match name and TIN combinations against IRS records, and they say participants can generally rely on a verified match as reasonable cause. Keep the TIN column out of AI prompts and uploads, and keep the final filing list human-approved, with the reviewer signing off the reconciliation and the exempt list.

Recheck the thresholds every year. The $2,000 figure applies to payments made in 2026, and the IRS has said it may be adjusted for inflation beginning in 2027, so the constant in the formula should sit in a single labeled cell rather than inside every formula. The same single-cell approach is used in the Data Studios economic nexus tracker in Excel with Copilot or ChatGPT, where state thresholds also change.

··········

W-9 AND 1099 TRACKING WITH AI: WHAT THE TOOLS CAN AND CANNOT DO.

Copilot in Excel and ChatGPT can both be asked to build a 1099 eligibility tracker from a vendor payment export, and the result is only as correct as the rules in the prompt: the $2,000 threshold for services, rent and other income, $10 for royalties, the exclusion of card payments, and the corporate exemption with its attorney and medical exceptions. On the illustrative list, the correct outcome is three Forms 1099-NEC for $10,500, two Forms 1099-MISC for $22,450, and two vendors to chase for a W-9.

The decisions that remain with the finance team are the ones the tools cannot see: whether the payment export is complete, whether a mixed vendor's payments were split correctly between goods and services, whether each exempt classification matches a signed W-9, and whether the threshold for 2027 payments has been adjusted. The Form 1099-NEC deadline of January 31 applies to both the IRS filing and the payee statement, so W-9 requests sent in November leave time to resolve missing TINs before forms are prepared.

··········

·····

DATA STUDIOS

·····

datastudios.org