How to Give AI a Data Dictionary for an Unfamiliar Spreadsheet
Summary
- A data dictionary removes ambiguity by defining each column's meaning, type, allowed values, and business rules.
- The fastest way to help AI with an unfamiliar spreadsheet is to provide a compact, structured dictionary plus 5-10 representative rows.
- Include targeted checks (uniqueness, ranges, cross-field consistency, and missingness) so AI can validate assumptions before analyzing.
- Use a reusable “Data Dictionary Pack” format (table + rules + examples + questions) to get consistent results across files and teammates.
- You can save and reuse your dictionary prompts and templates so every new spreadsheet starts with the same clarity and checks.
When you paste an unfamiliar spreadsheet into an AI tool and ask for analysis, the AI has to guess what columns mean. That guesswork is where errors start: IDs get treated like numbers to sum, “status” codes get interpreted as free text, dates get parsed incorrectly, and “amount” might be cents in one file and dollars in another.
A data dictionary is the antidote. It tells AI (and humans) what each column represents, what values are valid, how to interpret blanks, and what relationships should hold across columns. This article shows exactly what ambiguity looks like, a before-and-after example, a reusable dictionary format you can paste into any chat, and targeted checks that catch wrong assumptions early.
The specific ambiguity problem: AI cannot infer your business meaning
AI can recognize patterns (dates, numbers, repeated codes), but it cannot reliably infer business semantics without help. In unfamiliar spreadsheets, the same-looking column can mean very different things:
- Codes vs labels: “Status” might be {0,1,2} or {Open, Closed} or {A, C, H}.
- Units: “Amount” might be dollars, cents, hours, or points.
- Identifiers: “Customer_ID” is not a quantity to average; it is a key.
- Dates: “2026-01-02” could be ISO, but “01/02/26” is ambiguous by locale.
- Blanks: Empty might mean “unknown,” “not applicable,” “not yet entered,” or “zero.”
- Grain: Each row might be an order, an order line, a customer-month summary, or a ticket event.
Without a dictionary, AI will fill in gaps with plausible assumptions. Your goal is to replace assumptions with explicit definitions and checks.
Before-and-after: the same spreadsheet question, two very different outcomes
Before (ambiguous input)
You ask: “Can you analyze this spreadsheet and tell me which customers are most valuable and which products are underperforming?”
You paste columns like:
- Cust
- Prod
- Amt
- Dt
- St
- Ch
What can go wrong:
- AI assumes Amt is revenue, but it might be refund amount or cost.
- AI assumes Ch is “channel,” but it might be “chargeback flag.”
- AI treats St as free text, but it might be a numeric code where 2 means “cancelled.”
- AI aggregates at the wrong level (order lines vs orders), inflating totals.
After (dictionary + examples + checks)
You provide:
- A clear statement of what each row represents (the dataset “grain”).
- A column-by-column dictionary (type, meaning, allowed values, units, null meaning).
- 5-10 representative rows (including edge cases).
- Targeted validation checks the AI must run (or at least reason through) before conclusions.
Result: The AI can compute “most valuable customers” using the correct metric, filter out cancelled transactions, interpret dates correctly, and flag data quality issues instead of silently guessing.
The “Data Dictionary Pack” you can reuse (copy/paste template)
Use this format whenever you hand an unfamiliar spreadsheet to AI. It is designed to be compact, explicit, and checkable.
1) Dataset overview (grain + purpose)
- Dataset name: [e.g., “Orders Export - Q3”]
- What each row represents (grain): [e.g., “One row per order line item”]
- Primary keys: [e.g., “Order_ID + Line_Number”]
- Time zone / locale: [e.g., “Dates are US format MM/DD/YYYY; currency in USD”]
- Goal of analysis: [e.g., “Customer value by net revenue; product underperformance by margin”]
2) Column dictionary (structured table)
Fill this in for every column you expect the AI to use.
| Column | Meaning (business definition) | Type | Units / format | Allowed values / domain | Null meaning | Notes / rules |
|---|---|---|---|---|---|---|
| [Column_Name] | [What it represents] | [string/int/decimal/date/bool] | [USD, cents, ISO date, etc.] | [list or pattern] | [unknown / not applicable / zero / not captured] | [e.g., “Unique per row”, “Derived”, “Do not sum”] |
3) Representative rows (include edge cases)
Paste 5-10 rows that include:
- Typical rows
- Rows with blanks
- Rows with “rare” statuses
- Refunds/returns/negative amounts (if applicable)
- Boundary dates (first/last day of period)
4) Business rules and derived metrics
- Inclusion/exclusion rules: [e.g., “Exclude Status in {Cancelled, Test}”]
- Net revenue definition: [e.g., “Gross - Discounts - Refunds; shipping excluded”]
- Customer value definition: [e.g., “Net revenue in period; optionally repeat purchase count”]
- Product underperformance definition: [e.g., “Low margin or declining net revenue vs prior period”]
5) Questions the AI must ask if unclear
- “Is Amt gross, net, cost, or refund?”
- “Are negative amounts refunds or chargebacks?”
- “What does a blank in Status mean?”
- “Is each row an order or an order line?”
- “Are IDs stable and unique?”
A reusable prompt that forces clarity (and prevents silent guessing)
Paste this prompt into your AI tool, then paste your dictionary pack underneath it.
Reusable prompt:
Task: You are helping me analyze an unfamiliar spreadsheet. Before you compute insights, you must build a working data dictionary from what I provide and identify ambiguities.
Instructions:
1) Restate the dataset grain (what one row represents) and the primary key(s). If unclear, ask questions before proceeding.
2) For each column, restate: meaning, type, units/format, allowed values, and what null means. If any field is unknown, list it as an explicit question.
3) Propose a set of validation checks (uniqueness, ranges, domain checks, cross-field consistency, missingness) tailored to this dataset.
4) Only after the checks are defined, outline the analysis steps you will take to answer my goal.
5) Do not assume units, currency, date format, or status meanings unless I explicitly define them.
Input: I will provide (a) a column list, (b) a partial data dictionary, and (c) 5-10 representative rows.
Targeted checks to include (so AI can validate assumptions)
These checks are the difference between “AI summarized my sheet” and “AI helped me trust the summary.” Choose the ones that match your file.
1) Grain and key integrity
- Uniqueness: Are the stated key columns unique per row?
- Duplicates: Are there duplicate keys that indicate repeated exports or joins?
- Row meaning: Do totals make sense if aggregated by the key?
2) Domain and code validation
- Enumerations: List distinct values for status/category columns and confirm each meaning.
- Unexpected values: Flag values outside the allowed set (including casing and whitespace variants).
- Code drift: Identify “new” codes that appear only late in time.
3) Type, format, and unit checks
- Date parsing: Confirm date format and detect impossible dates.
- Currency/units: Check magnitude (e.g., cents vs dollars) by comparing typical values.
- Numeric sanity: Flag negative values where they should not exist (or confirm they represent refunds/credits).
4) Missingness with meaning
- Null rate per column: Identify columns with high missingness.
- Null semantics: Confirm whether blank means unknown vs not applicable vs zero.
- Conditional missingness: Example: “Refund_Reason is required when Status=Refunded.”
5) Cross-field consistency rules
- Conditional logic: If Status=Cancelled, then Amount should be 0 (or should be excluded), depending on your rules.
- Range constraints: Discount between 0 and 1 (if percent) or between 0 and Amount (if currency).
- Referential integrity: Product_ID should exist in a product list (if you have one).
Practical mini-example: a compact dictionary that fixes common spreadsheet confusion
Imagine you receive a sheet with these columns: Cust_ID, Order_ID, Order_Date, Status, Amount, Channel. Here is a dictionary snippet that removes the most common ambiguity:
| Column | Meaning | Type | Units / format | Allowed values | Null meaning | Notes / rules |
|---|---|---|---|---|---|---|
| Cust_ID | Stable customer identifier | string | N/A | Pattern: CUST-#### | Not allowed | Do not sum/average; used for grouping |
| Order_ID | Unique order identifier | string | N/A | Unique | Not allowed | One order can have multiple rows only if this is an order-line table |
| Order_Date | Date the order was placed | date | YYYY-MM-DD (UTC) | Valid calendar date | Unknown (rare) | Use for time series; confirm timezone if comparing to other systems |
| Status | Order lifecycle state | enum | N/A | {Placed, Shipped, Delivered, Cancelled, Refunded} | Unknown | Exclude Cancelled from revenue; Refunded reduces net revenue |
| Amount | Gross order amount before refunds | decimal | USD | >= 0 | Not captured | Net revenue = Amount - Refund_Amount (if present) |
| Channel | Acquisition channel | enum | N/A | {Organic, Paid Search, Email, Referral} | Unknown | Use for segmentation; confirm whether “Email” includes lifecycle + promo |
Even if you cannot fill every field, writing down what you do know (and explicitly listing unknowns) helps AI ask the right follow-ups instead of guessing.
How to deliver the dictionary to AI (without oversharing or losing structure)
Two practical ways to share a data dictionary with AI:
- Inline in chat: Paste the “Data Dictionary Pack” followed by a small sample of rows. This is fastest for one-off analysis.
- As a reusable template: Keep a standard dictionary prompt and fill it per spreadsheet. This is better when you analyze many exports from the same system.
If your spreadsheet contains sensitive fields, consider redacting or replacing values with realistic placeholders while keeping formats and edge cases intact (for example, keep date formats and status codes, but mask names/emails).
Using CopyCharm to save, find, and reuse your data dictionary workflow
If you repeatedly analyze spreadsheets (exports from billing tools, CRM downloads, ops trackers), the slow part is rewriting the same “please interpret these columns correctly” instructions and re-checking the same assumptions. CopyCharm can help you keep that workflow consistent because it is a Windows desktop app that saves copied text locally, lets you search past clips, favorite important clips, and separately save reusable prompts.
A concrete workflow you can use
- Save: Copy your best “Data Dictionary Pack” template and save it as a reusable prompt (separate from favoriting a clip). Also favorite a couple of high-quality examples: a completed dictionary for a past spreadsheet and a checklist of validation checks you like.
- Find: When a new spreadsheet arrives, search your past clips for the closest prior export (for example, “Order_Date Status Amount”) and pull up the dictionary you used last time.
- Reuse: Paste the saved prompt template into your AI tool, then paste the updated column list and 5-10 representative rows. Reuse your targeted checks section to force clarification before analysis.
If you use ChatGPT: connector vs manual reuse
There are two different ways to reuse your dictionary content with ChatGPT:
- Manual: Search or retrieve the dictionary in CopyCharm, then copy/paste it into ChatGPT.
- Authenticated connector (supported Synced Data only): After eligible account authorization and AI Access sync, and after you authorize the ChatGPT connector, ChatGPT can search and retrieve supported Synced Data (Favorite Clips, Saved Prompts, and optional Other Clips within your selected time range). ChatGPT cannot access unsynced local CopyCharm data.
For Claude, Gemini, Cursor, email, documents, and other applications, the workflow is manual: you search/retrieve in CopyCharm and then copy/paste into the destination app.
Try CopyCharm for saving and reusing your data dictionary prompts
Common pitfalls (and how to prevent them with dictionary details)
Pitfall 1: Mixing “ID” columns into math
Fix: Mark identifier columns as “Do not sum/average; grouping only.” Add a check: “IDs should be unique at the stated grain.”
Pitfall 2: Confusing gross vs net
Fix: Define each money column with a formula: “Net = Gross - Discounts - Refunds.” If refunds are separate rows, say so.
Pitfall 3: Wrong date interpretation
Fix: Specify date format and timezone. Add a check: “Are there dates outside the expected range?”
Pitfall 4: Status codes that change meaning
Fix: Provide an explicit mapping table for codes. Add a check: “List distinct statuses and counts; flag unknown statuses.”
Pitfall 5: Wrong row grain
Fix: State grain in one sentence and include a key rule. Add a check: “Does Order_ID repeat? If yes, confirm line-item table.”
Frequently Asked Questions
FAQ 1: What is a data dictionary for a spreadsheet, in practical terms?
Answer: It is a column-by-column set of definitions that explains what each field means, what type it is (date/number/code), what values are valid, what blanks mean, and any rules (like “do not sum” or “exclude cancelled rows”). For AI, it is the difference between guessing and interpreting your sheet the way your business does.
Takeaway: A data dictionary turns column headers into unambiguous instructions.
FAQ 2: What is the minimum I should give AI to interpret an unfamiliar spreadsheet correctly?
Answer: Provide (1) what each row represents (grain), (2) a short dictionary for the columns you care about (meaning, type, units, allowed values), and (3) a handful of representative rows. If you cannot define something, explicitly list it as an open question instead of letting AI assume.
Takeaway: Grain + column meanings + a few real rows is the minimum viable context.
FAQ 3: How many sample rows should I include with the data dictionary?
Answer: 5-10 rows is a practical starting point if they include edge cases (blanks, rare statuses, negative amounts, boundary dates). If the sheet has multiple “modes” (for example, both invoices and credits), include a few rows of each mode so AI can see the differences.
Takeaway: Small samples work if they are representative and include edge cases.
FAQ 4: What targeted checks should I ask AI to perform before it analyzes the data?
Answer: Ask for checks that validate your definitions: key uniqueness (does the primary key uniquely identify rows?), domain checks (are statuses only from the allowed set?), type/format checks (dates parse correctly, currency magnitude makes sense), missingness checks (what percent is blank and what does blank mean?), and cross-field rules (for example, “Refund_Reason required when Status=Refunded”).
Takeaway: Checks should confirm assumptions before conclusions.
FAQ 5: How do I handle ambiguous columns like “Amount,” “Status,” or “Channel”?
Answer: Define them with (a) units and sign conventions for Amount (USD vs cents; whether negatives are refunds), (b) an explicit mapping for Status codes/labels and which statuses to include/exclude, and (c) a controlled list for Channel plus any grouping rules (for example, whether “Email” includes lifecycle and promotional sends). Then add a check that lists distinct values and flags anything outside the mapping.
Takeaway: Ambiguous columns need explicit domains and rules, not just descriptions.
FAQ 6: How do I describe the “grain” of a spreadsheet so AI does not aggregate incorrectly?
Answer: Write one sentence: “Each row represents ___.” Then name the key columns that uniquely identify a row (for example, Order_ID + Line_Number). If Order_ID repeats, say whether that is expected (line items) or a data issue (duplicates). This prevents AI from summing line items as if they were orders, or treating monthly summaries as transactions.
Takeaway: Grain + keys prevents the most common aggregation mistakes.
FAQ 7: Can I give AI a data dictionary without sharing sensitive data?
Answer: Yes. You can share the dictionary (column meanings, allowed values, rules) and provide masked sample rows that preserve formats (date patterns, code values, numeric ranges) without exposing real names, emails, addresses, or account numbers. The key is to keep the structure and edge cases intact so the AI can still validate logic and parsing.
Takeaway: Preserve structure and edge cases; redact identifying values.
FAQ 8: How can CopyCharm help me reuse a data dictionary prompt across spreadsheets?
Answer: You can save your “Data Dictionary Pack” prompt as a reusable prompt in CopyCharm, favorite strong past dictionaries, and later search your saved clips to retrieve the closest template for a new spreadsheet. With ChatGPT specifically, after eligible account authorization and AI Access sync, ChatGPT can search and retrieve supported Synced Data; it cannot access unsynced local CopyCharm data. For other apps, you can still retrieve the template in CopyCharm and copy/paste it where you need it.
Takeaway: Reusing a consistent dictionary template reduces repeated setup work across spreadsheets.