Giving Users a Template Instead of Rules for Uploads

Somewhere there is a document titled "Upload requirements", with fourteen numbered rules. Use one header row. Dates in ISO format. Do not merge cells. Quantities as whole numbers. Codes as text. Use the allowed list of units. People read the first three, forget the rest and send a file that breaks five. A template does the work of the document. The header is already in place. The date column accepts only dates. The unit column is a dropdown. The example row shows what a finished row looks like. This article covers how to turn rules into a template people use, how to distribute it and how to tell whether it is working. It belongs to building Excel templates that import cleanly and supports controlling what goes into a spreadsheet.

Why rules lists fail

A list of rules asks the sender to do three things: read it, remember it and apply it at the moment of typing. Each step loses people. The reader skims. The memory fades by the time the data is ready. The application depends on discipline under time pressure. And the rules sit in a different place from the file, usually an email or a web page, so the sender never has both in front of them.

A template changes the arithmetic. The rule is enforced where the sender is working. A date cell that refuses text does not need to be remembered. A dropdown with the allowed units needs no list. The header is correct because the sender never typed it. The rules still exist, in the template and in the loader's checks, and the sender meets them as behavior and not as reading.

What goes into the template

The header, done. Row 1, machine names, frozen, filtered (one header row and no merged cells in import templates). The sender never touches it.

Formats set. Text for codes, dates as yyyy-mm-dd, numbers with the right decimals. Typing then behaves correctly by default.

Validation. Dropdowns for categories and flags, number ranges, date windows, length limits. Input messages that appear on selection ("Enter a date such as 2026-10-03") and error messages that say what to do (data validation lists and their limits in Excel sheets).

An example row. One complete, correct row. Put it on the instructions sheet, not the data sheet, so it is not loaded as data; or mark it clearly on the data sheet and have the loader skip a row whose first cell reads "EXAMPLE". Examples are the fastest way to teach a format.

Required columns marked. A header fill or a note: "Required". Optional columns left plain.

One instructions sheet, first. Purpose, due date, contact, one line per column with its meaning, and the three most common mistakes. If it does not fit on a screen, it is too long.

Protected structure. The sheet protected so that headers, formulas and lists cannot be overwritten, with input cells open (locking down shared input sheets without breaking them).

A version. Inside the file (versioning template layouts so old files still import).

Generate all of it from the schema, so the template and the loader agree, and so a new column is a one-line change.

The example row, in code

from openpyxl import Workbook

wb = Workbook()
ins = wb.active
ins.title = "Instructions"
ins["A1"] = "Weekly stock count"
ins["A2"] = "Fill the Data sheet, one row per location and SKU. Send by Friday 17:00 to [email protected]."
ins.append([])
ins.append(["Column", "Meaning", "Example"])
ins.append(["site", "Your site: North, South or East", "North"])
ins.append(["location_code", "Letter and three digits", "B014"])
ins.append(["sku", "Product code, as printed on the label", "00417"])
ins.append(["qty", "Boxes counted, whole number", "40"])
ins.append(["counted_on", "Date of the count, yyyy-mm-dd", "2026-10-02"])

data = wb.create_sheet("Data")
data.append(["site", "location_code", "sku", "qty", "counted_on"])
wb.save("stock_count_2026.3.xlsx")

The instructions table doubles as the column glossary. Pair it with validation on the Data sheet, built as in the earlier articles. The point is that a first-time sender sees, on one screen, what the file is and what a good row looks like.

Explain why, briefly

People accept constraints that come with a reason. Two sentences on the first sheet, written in terms of what the sender gets: "We load this file automatically every Friday evening. If it follows the template, your counts appear in the stock report on Monday with no follow-up questions." That is more persuasive than a list of prohibitions. It also sets expectations: what happens when the file is right, and when it is not (they receive an email listing the rows to fix).

Do not threaten. Do not apologize for the template either. State the benefit and move on.

Publish it where people look

A template nobody can find is a template nobody uses. Give it one stable address, such as a page named for the process, with the current version prominent and the older ones archived. Put the link in the email that requests the file, in the rejection message and in the signature of the people who answer questions. Name the file with the version (stock_count_2026.3.xlsx) so that a stale copy is recognizable.

Avoid attaching the template to each request email, since attachments get saved under new names and edited. Link to the single copy. For senders who need the file by email, regenerate it for them from the same source with their site pre-filled, which also removes the chance of a wrong site code.

Pre-fill what you know

Anything the sender should not have to type, pre-fill. Their site or supplier code, the reporting period, the list of locations or SKUs they are expected to report. A template generated per sender, with their identifiers filled in and locked, removes a whole class of errors, and it lets the loader check that the file is the one that was issued to them. A pre-filled list of expected SKUs also turns a blank-sheet task into a fill-in-the-quantities task, which is faster and less error-prone.

Pilot with real senders

The authors cannot see their own template. Give a draft to two or three senders and watch them use it, or ask them to send you their first attempt and what confused them. Typical findings: a header that means something different in the sender's world, a list missing a value they need, an instruction that nobody read because it was on the second sheet, a dropdown that did not appear because they pasted. Fix the template, not the sender.

Ask what they were about to do before they found the template. If they were planning to export from their own system, find out what that export looks like. Sometimes the best fix is to accept a CSV from that system instead of an Excel template at all.

Measure adoption

Track, for each cycle:

• The share of files that arrive on the current template version.

• The share that load without errors on the first try.

• The most common rejection codes and columns.

• The number of support questions and their topics.

If the first number is low, the distribution is the problem: the link is not found, or the announcement was missed. If the second is low, the template or the instructions are the problem. If one column dominates the errors, change that column: a dropdown instead of text, an example in the instructions, a different name.

Share the numbers with the senders' managers if you can. A site that sends clean files every week deserves recognition, and a site that struggles may need a call.

Keep it current

A template is never finished. Review it every quarter: remove unused columns, update the lists, check that the instructions match reality, fix the confusing header. Announce changes with the version and the changelog. Retire the old version on a stated date. A template that is looked after earns trust; senders learn that the file is correct and they stop second-guessing it.

When a template alone will not change behavior

A template cannot fix a sender who has no incentive to follow it, a process where the data is typed in a hurry from paper, or a source system that cannot produce the columns you need. For senders with their own systems, offer an API or a CSV with a published schema. For incentives, make the benefit visible and the cost of failure small: fast feedback, an easy resend. For systems that cannot comply, build a converter on your side for that sender and keep it out of the main path. And a template cannot compensate for a loader that accepts anything: the checks must stay (rejecting files that do not match the template cleanly). The openpyxl documentation covers generating a template from code, and the XlsxWriter documentation covers formats and validation.