If you regularly live in Excel on a jobsite or in the office, you already know the problem: merged cells, mystery columns, inconsistent headers, and blanks that should have been filled in. That shows up on bid forms, punch lists, analysis worksheets, tracking logs, and dozens of other routine construction spreadsheets—not just estimating tabs.
The fix is usually boring office work—normalize headers, flag blanks, apply a few formatting rules. An Office Script in Excel can do that repeatable cleanup for you, and ChatGPT is good at drafting the script so you don’t have to write the code from scratch.
This how-to walks through one practical pattern on a sample bid tab: download a messy practice workbook, paste in a ready-made Office Script, run it on a copy, review the output, then use ChatGPT to tweak the script for the sheets your team actually uses.
What is an Office Script? A small TypeScript automation that runs inside Excel for the web from the Automate tab. It is not a macro and it does not use VBA. You paste the code into a script editor in the browser, save it, and click Run.
What you need
- Excel for the web with access to Automate (Office Scripts). Your Microsoft 365 admin may need to enable it.
- A copy of the workbook you want to clean up. Never run a new script on the only copy.
- An AI assistant your company allows—ChatGPT, Claude, or Gemini—using each provider’s latest coding-focused model (not a general chat default). Do not paste confidential bid numbers into a model your policy does not permit.
Why use ChatGPT for this
Office Scripts use TypeScript against Excel’s ExcelScript API. You can read Microsoft’s docs and write the calls by hand. Most field and office teams should not have to. ChatGPT is decent at scaffolding the script, looping rows, and suggesting formatting. It is not reliable at guessing your exact column layout or getting every boundary case right on the first try. Treat every generated script as draft code until you run it on a copy and read the output.
Step 1: Download the practice workbook
Download Example Bid Tab — a fictional .xlsx practice file with a sheet named Bid Tab - Messy that matches the script below. The workflow applies to any similar cleanup job on bid forms, punch lists, logs, or analysis sheets you already maintain in Excel.
If your browser opens it in Excel Online instead of saving to disk, right-click the link → Save link as…. Treat this file as practice data, not a live bid package.
Step 2: Open the workbook in Excel for the web
- Sign in to Excel for the web (Microsoft 365).
- Open the downloaded workbook (
plan-north-messy-construction-bid-tab.xlsx). - Click the Automate tab in the ribbon.
- Choose New Script.
A code editor opens on the right with a default template (function main(workbook: ExcelScript.Workbook) { ... }).
Step 3: Paste and run the Office Script
Copy the full script below using the Copy button in the top-right of the code block. The whole script is one block—do not try to copy it section by section.
Then in Excel:
- Click inside the script editor and select all of the default template (Ctrl + A / Cmd + A).
- Paste your copied script. The editor should now show only the script you just pasted.
- Click Save script and give it a name like
Clean Bid Tab. - Click Run.
Watch it run
What the script does. It finds the sheet Bid Tab - Messy, copies it into a new sheet named Clean Bid Tab (and replaces an old Clean Bid Tab if one exists), then applies headers, borders, currency formats, low-bid highlighting, and a summary block. It does not edit the messy source tab. Missing bidder cells are filled with MISSING so they can be counted and styled.
Run this on a copy first. Double-check
MISSINGagainst true blanks, theCOUNTIF/INDEX/MATCHformulas, and the dollar totals. This script formats and flags a sheet. It does not decide scope coverage, exclusions, qualifications, or award. That judgment stays with your team.
Step 4: Review the output
Switch to the new Clean Bid Tab sheet. Look at:
- Headers in row 5 — readable, correct names.
- Bid columns (G, H, I) —
MISSINGshown in soft orange, lowest bid in soft green. - Row formulas in column F (totals) and column J (low bid per row).
- Summary block below the table — apparent low bidder, low total, and missing-cell count.
If anything looks off, that’s normal. The script is a starting point, not a finished company standard.
Step 5: Take the script into ChatGPT for changes (the Workshop)
Once Clean Bid Tab looks roughly right, treat ChatGPT like a shop bench. Copy the whole script out of the Automate editor, paste it back into ChatGPT, and ask for one small change at a time instead of re-describing the entire bid tab from scratch.
A few practical notes before you do this:
- Use the latest coding model from your provider (for example ChatGPT’s current code-oriented tier, Claude’s latest Sonnet/Opus coding release, or Gemini’s latest code model). These are noticeably better at scripts than general chat defaults.
- AI is non-deterministic. Ask the same question twice and you can get two slightly different scripts. That is normal. Read the code, run it on a copy, and judge by the output—not by how confident the answer sounds.
- Expect a few iterations. Two to four passes is normal for a construction spreadsheet. If the first response breaks something, that does not mean the approach is wrong.
Example prompt: lighter headers and italic quantities
Say you want the table header row a bit softer than the dark navy, and you want Column D (Qty) italicized for readability. Paste your current working script into ChatGPT, then add a prompt like this:
ChatGPT will usually touch the header fill (for example around A5:K5) and add a format pass on D6:D16. Replace the script in Automate with the full code it returns—do not paste a fragment in the middle unless you know exactly where it belongs.
Step 6: Test the revised script on a fresh copy
Every time you change the script, you want a clean starting point.
- Download the practice workbook again (or duplicate your original), so
Bid Tab - Messyis in its original messy state. - Open the fresh copy in Excel for the web → Automate.
- Replace your saved script with the updated code.
- Click Run and review
Clean Bid Tabthe same way you did in Step 4.
If the header color is too pale, or italics ran onto a row you did not want, reply to ChatGPT with one correction at a time: “Use #2D5A87 for the header fill” or “Italicize D6:D16 only, not the total row.” Small prompts, small diffs—same habit as redlining a spreadsheet before you send it to the field or the owner.
Step 7: Save and reuse the script
Once the script does what you want:
- The script is already saved in Automate under the name you gave it. You can re-run it on any workbook that has a
Bid Tab - Messysheet (or whatever source name you used). - To use it on a real bid tab, either rename your source sheet to
Bid Tab - Messy, or change thesourceSheetNamevalue at the top of the script to match your tab. - You can also share the script with teammates inside your Microsoft 365 tenant from the Automate task pane.
Using this on your own spreadsheets
The practice workbook matches the script exactly. Real bid forms, punch lists, tracking logs, and analysis worksheets will not. When you point this at your own file, you will probably need ChatGPT to adjust:
- Sheet name (
sourceSheetNameat the top of the script). - Header row and the address ranges (
A5:K5,A5:K18, etc.). - Bid columns (the script assumes three bidders in columns G, H, I).
- Summary block location.
If you want your AI assistant to draft a new version from scratch, attach the spreadsheet you are automating to the first prompt (when your provider supports file upload). That gives the model real headers, column layout, and sample rows instead of a guess from text alone.
Also give it a tight prompt: goal in one sentence, where the data lives (sheet name, header row, which columns are scope vs. bid), rules you want (formatting, missing-bid detection, totals), and an explicit note that you will run it on a copy first and want no external network calls. A short example of your header row still helps when you cannot attach a file.
Closing
ChatGPT did not replace Excel on construction projects. It can generate code that eliminates the “whack a mole” game of reviewing a product for hidden mistakes and then the manual formatting that follows. Keep the file copy-first, verify formulas before you rely on them, and expect a few iterations. The script formats and flags a sheet. You still make the call on what the numbers mean.