Non-Negotiable Data Integrity Rules

• Do NOT alter, overwrite, delete, or reformat any cell values. Only apply visual styling.
• Do NOT sort, filter, or reorder any rows or columns.
• Do NOT add, remove, merge, or split any data cells. Header cells may be styled but not merged if they are part of the data range.
• Do NOT change number formats in a way that alters precision or meaning (e.g., do not convert “25” to “25.00” if the original is plain text).
• Do NOT rename any existing Profile sheet. Only new Quotas sheets are created by you.
• Do NOT protect, lock, or hide any sheet, row, column, or cell unless explicitly instructed below.
• Before applying any formatting, make a mental note of the original sheet names, column count, and row count. After formatting, verify none of these have changed.

Sheet Architecture

Each workbook contains one or more Profile sheets. Each Profile sheet represents a participant group for a research project. For every Profile sheet, you must create a corresponding Quotas sheet. The Quotas sheet is always a new sheet you generate — it does not exist in the original file.

Naming Convention

• Profile sheet names are pre-existing and must not be changed. Example: Group 1, Online Segment B.
• For each Profile sheet named [X], create a Quotas sheet named exactly [X] - Quotas. Example: Group 1 → Group 1 - Quotas.
• Place each Quotas sheet immediately after its corresponding Profile sheet in the tab order.
• If the workbook has three Profile sheets, it should have six total sheets after your work: Profile, Quotas, Profile, Quotas, Profile, Quotas — in paired order.

Tab Color Coding

Fieldwork Official Color Palette

Use only these colors throughout the workbook. Do not introduce any other colors.

Font Rules for Excel

The Fieldwork brand uses essonnes-display (serif) for headlines and canada-type-gibson (sans-serif) for body copy. These are web fonts not available in Excel. Use the approved Excel substitutes below, which match the brand’s serif/sans-serif intent.

Styling the Participant Profile Sheet

Row 1 — Sheet Title Banner

• If Row 1 is a merged title row containing the project/sheet name, style it: fill #005499, font Georgia 16pt Bold, text color #ffffff, row height 36pt, vertical align middle, horizontal align left with 12pt left indent.
• If no title row exists, do not create one. Proceed to the column header row as-is.

Column Header Row

• Identify the row containing column labels (e.g., Name, Age, Gender, Session Time). Apply: fill #005499, font Arial 11pt Bold, text ALL CAPS, color #ffffff, row height 28pt, vertical align middle, text wrap ON, freeze this row so it stays visible when scrolling.
• Apply a bottom border to the header row: medium weight (2pt), color #FCBA12.

Data Rows — Alternating Fill

• Odd data rows (1st, 3rd, 5th…): fill #ffffff.
• Even data rows (2nd, 4th, 6th…): fill #f5f5f5.
• All data rows: font Arial 11pt Regular, color #1e1e1e, row height 20pt minimum, vertical align middle.
• Apply thin inner borders (1pt, color #dcdcdc) between all data cells.

Column Width

• Auto-fit all column widths to content after styling is applied, with a minimum width of 80pt and a maximum width of 200pt.
• Exception: columns containing long free-text responses (e.g., open-ended screening answers) should be capped at 200pt with text wrap ON.

PII Column Identification

• Identify PII columns automatically by detecting header names that match or closely resemble: Name, First Name, Last Name, Email, Phone, Mobile, Address, DOB, Date of Birth.
• Apply a subtle left border accent (3pt, color #7585c2) to each PII column to visually group them — do NOT hide, lock, or alter their values.
• Do NOT include PII columns in the Quotas sheet.

Outer Border

• Apply a medium outer border (2pt, color #005499) around the entire data table (from header row to last data row, across all columns).

Generating the Quotas Sheet

Sheet Layout — Top to Bottom Order

• Row 1: Title banner. Fill #005499, text: [Profile Sheet Name] — Participant Quotas, Georgia 18pt Bold, color #ffffff, row height 42pt.
• Row 2: Metadata row. Display: total participant count, date generated, source sheet name. Arial 10pt, color #505050, fill #f5f5f5.
• Rows 4+: One summary block per quantifiable field (see rules below), separated by a blank row between each block.
• Charts: Embedded to the right of each summary table block (same row range), sized consistently at approximately 300pt wide × 200pt tall.

Field Classification — What to Include

Summary Table Structure (per field)

• Section label above the table: field name in Arial 10pt Bold ALL CAPS, color #505050.
• Table columns: Value | Count | % | Quota / Goal (if a quota/goal column exists in the Profile data for this field) | Remaining (if quota exists: Goal minus Count).
• Header row: fill #005499, Arial 10pt Bold, color #ffffff.
• Data rows: alternating #ffffff / #f5f5f5, Arial 10pt, color #1e1e1e.
• Last row of each table: Total row. Bold, fill #7585c2, color #ffffff.
• Percentages: display as XX.X% format. Count columns: integer format, no decimals.

Quota / Goal Tracking

• Scan Profile sheet column headers for any column whose name contains the words Quota, Goal, Target, or Needed.
• If found, include Quota/Goal and Remaining columns in the relevant summary table. Remaining = Goal − Count. If Remaining is negative (overbooked), display the value in red (#e61920).
• If no such column exists, omit those columns from the summary table entirely. Show only Count and %.

Chart Styling Rules

• Chart title: match the field name, Georgia 13pt, color #005499.
• Chart area background: #ffffff. Plot area background: #f5f5f5.
• Chart border: 1pt solid #dcdcdc.
• Bar/Pie series colors (cycle in this order): #005499, #7585c2, #f27836, #FCBA12, #e61920. If more than 5 categories, repeat the cycle.
• Show data labels on all chart series (count value, not percentage — the table already shows percentages).
• Remove gridlines from bar charts. Remove legend from pie charts if there are fewer than 5 slices (labels directly on slices instead).
• Align all charts to the right of their corresponding summary table with consistent sizing.

Workbook-Wide Formatting Rules

• Set default workbook font to Arial 11pt before applying any per-element overrides.
• Set zoom level to 100% on all Profile sheets and 90% on all Quotas sheets.
• Freeze the top pane (column header row) on all Profile sheets.
• Set print area on each Profile sheet to encompass the full data table. Set print orientation to Landscape. Fit to 1 page wide × as many pages tall as needed.
• Set print area on each Quotas sheet to encompass all content. Landscape orientation. Fit to 1 page wide.
• Add a print header to all sheets: left section = Fieldwork logo placeholder text “[FIELDWORK]” in Arial 10pt; right section = sheet name and page number.
• Ensure all sheets have gridlines hidden (use white cell borders instead — the styled borders replace them).
• Do not add any conditional formatting rules unless instructed above (the overbooked quota flag in red is the only exception).

Step-by-Step Execution Order

Follow this order precisely to avoid errors and ensure data integrity throughout.

How to Handle Ambiguous Situations

• Ambiguous column type: If you cannot determine whether a column is PII or demographic, treat it as PII (exclude from Quotas, apply periwinkle left-border accent).
• Mixed data in a column: If a column contains both numeric and text values, treat it as a text/categorical field. Include in Quotas only if fewer than 20 unique values exist.
• Empty columns: Apply styling but do not include in Quotas.
• Empty rows within data: Style them with the alternating fill pattern. Do not delete them.
• Merged cells in original data: Do not unmerge them. Apply fill color and font only. Do not re-merge or alter merge spans.
• Multiple header rows: Apply the column header style (#005499, white, bold) to all rows identified as headers. Freeze below the last header row.
• Date/time columns: In Quotas, group by date (not time) for the bar chart. If sessions span multiple days, each date is a separate bar. If only one date exists, group by time slot (hour block).
• Profile sheet already has some formatting: Replace all existing fills, fonts, and borders with the brand specification. Do not preserve prior formatting unless it is the data itself.
• More than 20 unique values in a categorical column: Skip charting for that column. Include a summary table only, listing the top 10 values by count plus an “Other (N)” row for the remainder.