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:
| ID | Name |
| 1 | Linda |
| 2 | Ahmed |
| 3 | Jenny |
| 4 | Lena |
| 5 | Ahmed |
| 6 | Stef |
| 7 | Simon |
| 8 | Terry |
| 9 | Terry |
| 10 | Ahmed |
| 11 | Linda |
| 12 | James |
| 13 | Stef |
| 14 | James |
| 15 | Steph |
| 16 | Carol |
| 17 | Alice |
| 18 | Lucy |
| 19 | Alice |
| 20 | Stef |
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!