Information can tell a story if you know how to sort your data properly. Looking at a jumbled spreadsheet is only going to confuse you, but spreadsheets like Google Sheets make it easy to sort your data in a logical way.
There are a few different ways to sort the columns in Google Sheets, including adding filters or sorting by multiple columns. And once everything is sorted, you can visualize your data with graphs and charts to make it even easier to understand.
Sorting Google Sheets by Columns
Spreadsheets use columns and rows. Columns go from top to bottom, and rows go across from left to right. Rows are numbered, and columns are referenced with letters. The numbers and letters appear on the left and top edges of the spreadsheet, respectively.
Spreadsheets are usually structured so that each row represents one group of information, and each column represents one piece of that information. For example, here is a selection of data from Spotify's music database:
Each row represents one song. And each column represents information about that song: performer, song title, genre, and so on.
If you hover over a column letter, you'll see a dropdown arrow icon. Click the arrow to see a dropdown menu of actions you can do with that column.
Looking at the fourth section of the dropdown, you'll see two Sort sheet options. When you click one of those options, Google sheets will alphabetize all the data in that column, making sure all the data in each row sticks together.
In other words, you're sorting the whole document by the data in that column. You're not just sorting that column. You can also right-click the column headers to reveal the actions dropdown.
Another way to sort the document is by clicking a single cell and selecting Data from the menu bar. You can see the same Sort sheet options available at the very top of this menu.
They also specify which column the sheet will sort by for clarity.
If you're just getting started with spreadsheets, here are the best ways to find Google Sheets templates so you don't need to start from scratch.
Clarify Your Columns by Naming Them
Looking at the raw data can be confusing. This is especially true for unnamed data, like you can find Spotify's music database.
It's helpful to have a name at the top of each column, rather than just a letter so that you can identify each column. Right-click the first row and select Insert 1 above to create a new row above it. Now type a name for each of your columns in this row.
You can also resize the column widths to read their contents better. If you hover over the right edge of the column header, you'll see a right arrow. Drag that to change the width of the column. This also works on rows but isn't used as often.
Now that we've named our columns, we have another problem. If you sort the columns, the column names will get lost with the rest of the data because Sheets doesn't know that it's not regular data. But there's a trick to freeze that row in place.
You don't need to have anything special selected to freeze the top row. From the menu, select View > Freeze > 1 row. You can see that it worked if you scroll down in your document. You'll notice that the frozen row sticks to the top of the sheet.
There's another way to do this as well. If you look at the above picture, you'll notice a thick gray line that separates the frozen row. It's usually in the upper-left-hand corner in the empty cell. You can see that the vertical divider that's used to freeze columns is still there.
You can drag those gray lines to adjust the columns and rows that are frozen. When moving the dividers away from a column or row, make sure you're dragging the divider and not resizing the column or row.
Find Hidden Relationships by Sorting by Multiple Columns
Sorting by single columns is a great way to organize your data. If you want to see an alphabetical list of songs or albums, then that's all you need. But if you want to analyze the data, it helps to sort by multiple columns.
For example, let's say you want to see what the most popular songs in each album are. The first thing to do is to select all of your data. You can do that by clicking the empty cell in the upper-left-hand corner or by pressing Ctrl + A. Next, select Sort range from the Data dropdown in the menu bar.
Click Data has header row to see the frozen column titles instead of the letters. This helps greatly if you have many columns, like in this spreadsheet that goes from A to Z.
The next step is to select the first column you want to sort by. Then click Add another sort column and select another one. You can sort by as many columns as you want. And you can sort them in different directions. In this example, popularity is sorted from high to low so that we can see the most popular tracks first.
The order in which you add columns is important because Google Sheets will sort them in that order. So if we chose popularity first, then we chose albums next, we'd get a list of all songs in order of popularity. Then the albums that have songs with the same popularity would be sorted alphabetically.
What we want to do instead is to group albums by name first, then find the songs with the most popularity from within that album.
Sorting your spreadsheet in this way is only the beginning of what you can do with Google Sheets. To learn more, check out these powerful Google Sheets scripts.
Go to the Next Level With Filters
Now that you've learned how to sort columns, the next step is to learn how to filter them. Filtering works really well with sorting as it lets you search for specific data and limit the shown data.
For example, you can search for an artist and sort their albums in alphabetical order. Or, you can filter the sheet to only show songs with a danceability score of 0.8 or higher and nothing else.
You can activate filtering by clicking Create a filter from the Data dropdown in the menu bar. Then the column titles will show an icon with three lines next to them. If you click this icon, a menu containing search and filter options will pop up for you to explore
Congratulations! You're now one step closer to managing your data like a pro.
