I’m having trouble with a QUERY formula that’s behaving weirdly. When I use =QUERY(D2:F,"SELECT E WHERE D='Online' ") it returns the correct data but also combines the first 64 values from column E into one cell at the top of my results.
The strange part is that if I limit my range to just D2:E the issue disappears completely. But I need to include more columns in my actual spreadsheet. I also noticed that when I clear all data from column F the problem stops happening.
Another weird thing is that changing column F from automatic formatting to number format fixes it too. But I don’t get why the formatting in a column I’m not even selecting would mess up my query results.
Has anyone seen this kind of behavior before? What could be causing the QUERY function to merge data like this when including additional columns in the range?
I encountered this exact same concatenation issue last month while working on a sales report. What fixed it for me was explicitly casting the data types in my query rather than relying on Google Sheets to interpret them automatically. The problem occurs because QUERY reads metadata from all columns in your specified range to establish a schema, regardless of which columns you actually select. When column F has mixed or undefined formatting, it creates inconsistencies in how the function processes your results. Instead of modifying the formatting of column F directly, try using =QUERY(D2:F,"SELECT E WHERE D='Online' AND F IS NOT NULL") or similar constraints that force the function to handle the data more predictably. This approach worked better for me than changing cell formatting since it maintains the integrity of your original data structure.
yeah ive seen this before too. its actually a bug with how google sheets handles the query range when theres mixed formatting. even tho youre not selecting column F, query still reads it to figure out data types. try adding a WHERE clause that excludes empty cells in F or just expand your range more carefully. annoying but thats how it works
This sounds like a known issue with mixed data types in Google Sheets QUERY function. I’ve encountered something similar when working with datasets that have inconsistent formatting across columns, even when those columns aren’t being selected in the query itself. The QUERY function actually scans the entire range you specify to determine data types, not just the columns you’re selecting. When column F contains mixed data types or inconsistent formatting, it can cause the function to misinterpret how to handle the results from your selected columns. Your observation about changing column F to number format fixing the issue confirms this - you’re essentially forcing consistent data typing across the range. The concatenation behavior you’re seeing is likely the QUERY function’s attempt to handle what it perceives as text data when it expects numeric data or vice versa. A workaround I’ve used is to wrap problematic columns in the range with TEXT() or VALUE() functions to ensure consistent data types, or alternatively structure your query to only include the minimum necessary columns in the range while still achieving your desired results.