There's a better way to build this form, and it starts with rethinking how you collect the data rather than how you organize it. The problem here isn't the testing itself, it's that the form forces a one-to-one relationship between a single submission and a single piece of equipment. That's why last year's version felt clunky: five swabs meant five form entries, and that's not just tedious, it's error-prone. The solution isn't to brute force more questions into the form. It's to separate the "what was tested" from "how each item performed" in a way that lets one submission carry multiple results.
The key move is to use a multi-select field for the equipment, then pair it with a follow-up that asks for pass or fail for each selected item. Most modern form builders, including those that integrate with Excel, allow you to create a question that dynamically references earlier answers. You pick the building, then the room, then check off five pieces of equipment. After that, the form presents five pass/fail prompts, one for each piece you selected. This keeps the entry process natural for the person filling it out, while the underlying data stays flat and chart-friendly. You avoid a hundred individual questions, and you avoid forcing someone to submit the same form repeatedly.
What matters most for trending is that each selected piece of equipment becomes its own row in the output, even though it came from one submission. That's the part that makes your charts work. If you store the data as a single row with multiple columns like "Equipment 1 Result," "Equipment 2 Result," you'll fight your own pivot tables later. But if the form writes one row per equipment item, with a shared submission ID linking them, you can filter by date, building, room, or result without any extra cleanup. That's the difference between a form that feels like a chore and one that quietly does its job.
So here's the concrete takeaway: stop thinking about this as a form with questions, and start thinking about it as a data entry tool with a logical flow. Test the multi-select approach with a small pilot, maybe one room with three pieces of equipment, and see how the output lands in Excel. If the rows come out clean and the chart updates without manual tweaking, you've solved it. If not, adjust the form's logic until it does. The goal isn't to make the form shorter. It's to make the data more usable. That's what turns a clunky process into a smooth one.