When I’ve pulled some data into Google Sheets I often want a way to quickly chart the count of something, maybe it’s support tickets and I want to chart the incidence of particular tags or count tickets for each customer. In this situation I want a nice neat chart showing from ordered data.
Let’s say this is my data, and I want to see which are the most used names:
Previously I’ve been using
=UNIQUE(Data!B2:B) to grab the unique names, then something like
=COUNTIF(Data!B:B,D2) to count each row. This ends up with summary data which is not in order, so when I generate my chart it’s not as readable:
Using the Google Sheets QUERY() function I can retrieve the data, count, and sort it at the same time:
=QUERY(Data!A:B,"SELECT B, COUNT(B) WHERE B IS NOT NULL GROUP BY B ORDER BY COUNT(B) DESC", 1). Which gives us this much more pleasing chart:
The Google Sheet is here if you want to see it in action. Improvements welcome!