A 50 state survey template in Excel needs one row for each of the 50 states and the District of Columbia, and, for insurance work, at least a dozen columns: the answer, its placement as yes, no or conditions, the citation, the quoted passage, the version and its effective date, the as-of date of the survey, the unit of days, the event that starts the clock, the exceptions, the link to the official text, and the names of the person who accepted the row and the counsel who signed it. Building that grid takes an afternoon. Filling it takes days of reading, and keeping it true takes a share of every week after that, because the states keep amending what the rows cite.
This page prices both halves for one insurance question, and shows where a spreadsheet is the right tool and where it stops being one. The software comparison is 50-State Regulatory Research, Botminds' solution in which agents read and cite every state's codes, regulations and bulletins, re-check saved answers when a cited rule changes, and leave acceptance and signature to the analyst and counsel. Its figures here are estimates and sample data on a fictional carrier, Larkspur Mutual, not customer results.
Why the grid is the cheap part
51rows
for the 50 states and DC; 56 if the survey covers every NAIC member
24bulletins
issued by the Texas insurance commissioner from January 2025 to September 2026
25rule projects
listed on Texas's 2025 and 2026 proposed and adopted rules pages
15sections
of Washington's claims rules amended by one order, effective 18 Oct 2026
What should a 50 state survey template in Excel contain?
Every column that a reviewer, an examiner or the next analyst will need to trust the row without redoing it. The test is whether someone who did not build the survey can open the row a year later and tell what the rule said, on what date, and who stood behind the answer. The table lists the columns we would not leave out for an insurance question.
Columns for an insurance 50-state survey, and why each is there
| Column | What goes in it | Why it is there |
|---|---|---|
| Jurisdiction | One row each for the 50 states and DC; territories if you write there | Insurance has 56 regulators; decide the scope once, in the header |
| Answer | The rule in short: "7 calendar days", "15 business days", "no fixed count" | What the business will read |
| Placement | Yes, No, Conditions or No rule found, from a drop-down list | Stops free-text answers that cannot be filtered or counted |
| Citation | Section and subsection, e.g. Tex. Ins. Code §542.055(a) | Lets a reviewer find the text |
| Passage | The words that decide the answer, quoted | A citation alone does not show the reasoning |
| Version and effective date | Which text, in force from when | Sections survive amendments; the words do not |
| As-of date | The date the survey answers for | Without it, nobody can test whether the row is current |
| Day unit and start event | Calendar, business or working days; from notice, proof of loss or a request | The two places a correct citation most often yields a wrong deadline |
| Conditions | Surplus lines, catastrophe extensions, fraud suspensions, line limits | Anything that changes the answer for some claims |
| Official link | The legislature, register or department page | Free copies are convenient; the official text decides |
| Company memo | Your interpretation memo, if any, with its date | Shown beside the rule, never instead of it |
| Accepted by, signed by, date | The analyst who accepted the row and the counsel who signed it | Who answers for the row when it is challenged |
| Next review | A date, or "on change" | Turns upkeep into a schedule instead of a surprise |
How to do a 50 state survey in Excel, step by step
Frame the question before the first row, keep one row per jurisdiction, and make every count a formula rather than a typed date. The steps below take a claims question, how many days an insurer has to acknowledge a homeowners claim, from a blank sheet to a reviewable survey.
1. Write the framed test in a header block. Line of business, first or third party, the event that starts the clock, the company's own standard, the unit of days and the as-of date. Most disagreements about a finished survey turn out to be two readings of the question, and the header settles them before anyone reads a statute.
2. Lock the placement column to a list. Data validation with four values, Yes, No, Conditions and No rule found, keeps the survey countable and forces a decision on every row. Conditional formatting by placement gives the colour map that business readers expect.
3. Count days with formulas and a holiday table per state. Calendar-day deadlines are the notice date plus the count. Business-day deadlines need WORKDAY.INTL with that state's holiday list as its holidays argument, not a national one. Texas leaves out weekends and the holidays its own law recognizes (Ins. Code §542.051(1)), and its holiday law lists eight state holidays on top of nine national ones, including 2 March, 21 April, 27 August and 26 December (Gov't Code §662.003). With a notice on Monday 12 October 2026, ten business days in Washington land on Monday 26 October, day 14 of the calendar; a 15-calendar-day company standard lands a day later.
4. Quote the passage and paste the official link. The passage column is what lets counsel sign without re-reading every statute; the link is what lets an examiner see the source.
5. Record the version and the as-of date on every row, then sign. A row without its version cannot be re-checked when the rule changes, and a survey without names cannot be defended. Save the signed sheet as a PDF so the record of what was signed cannot drift.
What does one 50-state answer cost to build?
Most of it is reading. Our estimate for one insurance question answered by hand puts roughly nine working days into the survey, of which about five go to finding and reading each state's code and regulations and then the bulletins and orders that modify them. Pinning down the question, placing each state, confirming each version and writing the memo for counsel take the rest. Price it with your own loaded hourly rates; we found no public source that publishes a market rate for a 50-state survey.
Where one 50-state answer's days go, by hand and with the agents reading
By hand · about 9 working days (estimate)
- Framing the question · 6%
- Reading codes and regulations · 39%
- Bulletins, orders, memos · 17%
- Placing states, day counts · 16%
- Checking versions · 11%
- Memo and sign-off · 11%
With 50-State Regulatory Research · about 1.6 days (sample)
- Framing · 6%
- Reading · 6%
- Bulletins · 6%
- Placing, checking arithmetic · 19%
- Versions · 6%
- Memo, review and counsel sign-off · 57%
The second bar is not mostly machine time. The agents' part of the sample run took 4 minutes 10 seconds; what remains is people checking the arithmetic on the states that matter, counsel reading the passages and signing, and the business owner deciding. That is the part a buyer should expect to keep, and the part that should grow as a share of the work.
How much does it cost to keep a 50 state survey current?
More than it cost to build, for any answer the business relies on for more than a few months. A spreadsheet has no way to know that a rule it cites has changed. Someone has to read every state's register and bulletins, decide which changes touch which rows, re-read those rows, re-place them and get them signed again. One state's output gives the scale: the Texas commissioner issued 16 bulletins in 2025 and 8 more by September 2026, and the department's rules pages list 11 rulemaking projects in 2025 and 14 in 2026 (TDI bulletins, TDI rules; our counts). Multiply by the states you write in.
Most of those changes touch no given survey. The cost is in finding the few that do. Washington's single order of 18 August 2026 amended 15 sections of its claims rules (WSR 26-17-089); a carrier with a dozen saved claims answers had to check every one that cited any of them. Model-law adoptions move answers too: the NAIC's data security state page for Summer 2026 dates the Alaska and Missouri adoptions to 2026 (NAIC state page, Model 668).
A year of upkeep for one saved 50-state answer
Watching
Hours to read every in-scope state's register and bulletins each week × 52
Shared across all saved answers; the largest fixed cost of keeping any survey current.
Regulatory change desk
Matching
Changes read × minutes to decide whether each touches a cited section
The step a spreadsheet cannot do: it does not know which rows cite which sections.
Analyst
Re-reading
Changes that touch a cited section × hours to re-read, re-place and re-count
Usually a handful of rows, each needing the full four tests again.
Analyst
Re-signing
Answers that moved × counsel hours to review and sign a new version
A signed answer should be replaced by a new version, not edited.
Regulatory counsel
One saved answer's year: what a spreadsheet does not notice
The cost of a stale row is not the hours to fix it; it is the months it spends in use while wrong. The sample carrier's saved answer on acknowledgment clocks shows the shape. Counsel signed version 1 on 4 March 2026. In the sample, one state's amended rule took effect on 1 July and moved that state from conditions to no. Version 2 was accepted on 12 October.
Version 1 of one saved answer, March to October 2026, in a spreadsheet and in the solution
- Right4 Mar–30 Jun · 119 d
- Wrong and unmarked1 Jul–11 Oct · 103 d
- Right4 Mar–30 Jun · 119 d
- Marked "Needs review"From 1 Jul · 103 d
The comparison is deliberately unflattering to software. Marking an answer does not fix it; in the sample, version 2 still waited until a new question came in. What the mark changes is who knows. In a spreadsheet the claims platform, the product team and the next analyst all keep using version 1. With the mark, every reader of the saved answer sees "Needs review" from the day the rule took effect, and the analyst has the changed passage in front of her. The upkeep formula above prices the fix; the timeline shows what the delay costs while nobody knows.
Is there a free 50 state survey template?
Yes, and any blank spreadsheet is one; the grid has never been the expensive part. Free public sources can also seed the citation column. The NAIC publishes a state page for each of its models, listing each member's related statutes or regulations with dates, and state legislatures and insurance departments publish their codes, rules and bulletins at no charge. Those are where the reading starts, not where it ends: the NAIC's pages warn that readers should consult state law for the most current information. How to read them is covered in NAIC model law state adoption charts.
50 state survey template in Word or PDF: when a memo is the right container
Use the spreadsheet to maintain the answer and a Word or PDF memo to deliver it. The business reader wants the short answer, the options and the states that differ, not 51 rows; counsel wants a fixed record of what was signed. A PDF of the signed memo, with the as-of date and the citations in it, is the right audit record. What goes wrong is keeping two masters: a memo edited after signature, or a grid updated without a new memo, leaves no single version that anyone signed. The rule is one source of truth, with the memo generated from it and replaced, never edited, when the answer moves.
Where 50-State Regulatory Research fits, and when a spreadsheet is enough
A spreadsheet is enough for a one-off question, a handful of states, rules that rarely change, or an answer nobody will rely on next quarter. It stops being enough when the same kinds of question recur, when answers become system defaults or notice templates, and when the states amend what the rows cite. That is where the solution fits: it keeps every saved answer with its citations and versions, reads registers, bulletins and emergency orders from every department each night, matches each change to the answers that cite the changed section, and marks them.
Rule changes from the last 12 months, and the saved answers each one touches
Each saved answer keeps its versions, so the difference between them can be read state by state, and the business sees which answer is current before relying on it.
Saved answers with their freshness, use and versions
The solution does not remove the human cost of upkeep; it removes the search. Counsel still signs every new version. For the first build and its alternatives, from outside counsel to research databases, see 50 state survey tools compared on one claims question.
Questions people ask
How long does a 50-state survey take by hand?
Our estimate for one insurance question is about nine working days: half a day to frame it, about five to read codes, regulations, bulletins and orders, and the rest to place states, check versions and write the memo. Questions that turn on case law or many exceptions take longer.
What is a good 50 state survey example?
An acknowledgment-clock question shows every column at work: Florida's 7 calendar days for residential property insurers, New York's 15 business days, Texas's 15th day for admitted insurers and 30th business day for surplus lines. One question, three units and an exception, each needing its citation and version.
How do you count business days by state in Excel?
Use WORKDAY.INTL with a named holiday range for each state, built from that state's holiday law. Texas, for example, keeps state holidays such as 2 March and 21 April that a federal calendar does not include.
Should a 50-state survey live in Excel or Word?
Both, with one master. Keep the grid as the maintained source, and issue the business memo as a PDF generated from a signed version of it, so that the record of what counsel signed never changes.
How often does a 50-state survey need updating?
Whenever a rule a row cites changes, plus a fixed review age as a backstop. One state's pace gives the scale: Texas issued 16 commissioner's bulletins in 2025 and listed 11 rulemaking projects, by our count of its own index pages.