If you are spending a very long time on data tasks to be repeated in Google Sheets, you are not alone. I used to lose hours for the same old steps every week – until I stumbled at the same function, which raised heavy for me. Get me introduce you to the query function.
5
An automatic sorting that updates itself in real time
The Query function has improved my spreadsheet in the most immediate way, which is ending my continuous re-re -rting headache. You know that the despair cycle where you sort your data, then after an hour, new information comes, and suddenly your sorting list is completely out of order. This is
It is said that you want to track your company’s inventory at the price. Instead of sorting endlessly again, you can use a query like this:
=QUERY(A:G, "SELECT * ORDER BY E desc")
In this example, you are telling the Google sheet to draw everything from the column A through G and sort it by column E (which holds prices in this case).
Immediately, your most expensive cars float at the top directly.
What is this a game-chanter that it does not stop working. When a new $ 250,000 car is added to your spreadsheet, it immediately appears on top of your query results. There is no need to sort again.
I used to take several minutes of clicking and pulling throughout the day, now it happens automatically. Even more importantly, I never miss objects with high-primaryness because they are always right where they should be.
4
A combination of several stages in a formula
I can’t tell you how many times I found myself dancing this: filtering the data, sorting it, hiding some columns, and perhaps combining things together. Now, thanks to Query, I can do all this in a shot.
Suppose I want to prepare a sales review, and I need to do the following:
-
Filter only sold cars
-
Remove any car under $ 30,000
-
Exclude teslas
-
Sort by price to see the first biggest sales
These are four different stages. If I had to do this manually and make a mistake anywhere, I have to start AFRESH. Instead, I type:
=QUERY(Test!A:G, "SELECT * WHERE F = TRUE AND E > 30000 AND NOT B contains 'Tesla' ORDER BY E desc")
A formula replaces four stages. Because I put a query function on an empty sheet, I added the name of the sheet with an amazing mark (test!) Before specifying the column with my data.
The best thing is that I can also get fanaticism even with segments like pivot, label, etc., for example, when I need to see this. group by,
=QUERY(Test!A:G, "SELECT D, SUM(E) WHERE F = TRUE GROUP BY D ORDER BY SUM(E) desc")
This shows me revenue from the release of each year so far, automatically solved from the highest to the lowest.
Time saving adds rapidly. Whether I was used for 10–15 minutes of clicks, filtering and organizing, now it takes 30 seconds to type the formula. And unlike my old multistap process, I never accidentally leave a step or mess up the order.
Connected
This 8 google sheet formula simplifies my budget spreadsheet
When Google sheets can do heavy lifting, why can crunch number do manually?
3
Large scale dataset without any interval
This picture: You are trying to find your most recent customers from a database of over 50,000 rows to send a targeted email campaign. This is quite simple, right?
Wrong. Every time you try to sort, filter or maneuver that kind of such massive dataset, the Google sheet will probably freeze. I deal with this until I discovered Query, he can handle a heavy dataset.
Here is an example:
=QUERY('50000 Sales Records'!A:N, "SELECT * ORDER BY H desc LIMIT 100")
The first formula will catch the top 100 most recent shipment, as the column H includes shipment dates. Meanwhile, the other one will catch 61 orders through 160.
Instead of creating your computer process and displaying all 50,000+ rows, when taking its sweet time, just because you can see the top 100, querry (with border and offset clause) is quite smart that you need and leave the rest.
You can also use boundary and offset with all other query features – grouping, sorting, filtering without fighting your browser and wasting time -.
Connected
Find my right browser on windows
If only it was simple.
2
Analysis of data in many sheets or files
Are you still copying data between a spreadsheet or workbook to only report only a report? Now you can stop. Querry enables you to analyze data in many sheets – or even in completely separate files – anytime without touching Ctrl+C.
Mix several tabs at a time
It is said that you have found quarterly data split in tabs like sales_q1 and sales_q2, you can merge them into single dataset using curly brackets. Then, run your analysis in the same way you will be on the same sheet.
=QUERY({Sales_Q1!A:N; Sales_Q2!A:N; Sales_Q3!A:N; Sales_Q4!A:N}, "SELECT Col3, Col1, SUM(Col9) WHERE Col5 = 'C' GROUP BY Col3, Col1")
Suppose the column 3 (COL3) item type, column 1 area, column 9 units sold, and column 5 order priority. Just make sure that each sheet match structure (column), and you are good to go.
I combined four sheets with data from different quarters to obtain the total units sold per item and area for C -priority orders. Easy peas!
Pull data from another Google spreadsheet (no download is necessary)
If you need data from a completely separate file, you can use Importrange with query to bring it. Say that you want to bring data from our car sale and Juxtapose it with the data we have received from four sheets, you can use this formula:
=QUERY(IMPORTRANGE(" "Test!A:G"), "SELECT Col4, SUM(Col5) GROUP BY Col4")
You will need to allow before drawing data from the external spreadsheet.
Once you provide access, you can catch data from the external spreadsheet in real time, and it will update if the source data changes.
The query lets you analyze data in tabs and files without opening a second tab or file.
1
Sorting and filtering without rewriting formulas
Every time you want to separate or filter your data without re -writing your entire query formula? You can do with a simple setup using double quotes and ampersands:
=QUERY({Sales_Q1!A:N; Sales_Q2!A:N; Sales_Q3!A:N; Sales_Q4!A:N}, "SELECT * WHERE Col1 = '"&G21&"'", 1)
When your boss wants to see European data, you type “Europe” in SAIL G21.
When he wants sub-Sahara Africa data, you type “sub-Sahara Africa”. Just make sure that this column 1 has a valid entry. The formula remains the same, but the results update immediately. Once you hang it, it is incredibly powerful.
The real magic occurred when I started using it for the date range. The dates are notorious in Query Sutras. They need to break the perfect format (Yyyy-MM-DD) or everything. But with cell references, you can set the user friendly date control:
=QUERY({Sales_Q1!A:N; Sales_Q2!A:N; Sales_Q3!A:N; Sales_Q4!A:N}, "SELECT * WHERE Col6 >= date '"&TEXT(G142, "yyyy-mm-dd")&"' AND Col6
Now, I have SAIL G142 for Start Date, Reply for the last date Sale i142, and the column 6 represents the order date column. When someone asks what our sales were from March 15, 2011 to April 30, 2015, I replace those two cells instead of wrestling with Formula Syntax.
The best thing is that I can share these spreadsheets with colleagues who are not formula experts. They look at clean input cells where they can change the area or adjust the date range, and they do not know that a complex query is running behind the curtain.
Connected
Top 6 Excel Sutra should know every office worker
Once you learn these sutras, you will be surprised how you ever worked without them.
The query function is not just a formula in the Google sheet. It is a fully developed data automation powerhouse. Whether you are doing tens of tens of thousands of rows, mimicking data between the sheets, or just being ill with repeated sorting and filtering, Querry handles all this with ease (and style).
This is sharp. It is flexible. And once you start using the query function in the Google sheet, you will be seriously surprised how you ever manage without it.
