Sorting In Numbers

Learn how to sort tables in Numbers. You can sort by a single column, but also by multiple columns. See some techniques for making the tables look better once they are sorted.
Video Transcript: Hi this is Gary with MacMost.com. Today let's take a look at Sorting in Numbers.
I've imported some data into a Numbers spreadsheet. These are US cities with the State, population, and area. Everything here from the initial list is sorted by city. It's just alphabetical and it is great if you want to be able to look up some information. 
But what would be more useful is if we sorted by population. The way to do that is first let's select a cell inside of a table. Then we have the column headings here. When I click on them I get some commands. I can Sort Ascending. I'm going to do it by column C which is population. I'm going to sort by ascending which gives me a population starting with the smallest going to the largest. These are the one hundred largest cities in America so it makes more sense to actually sort the other way, descending, so I can see the largest city, next largest city, etc going down.
Now I can do the same thing by area. If I wanted to resort everything by area I can see what the largest city by area is.
Let's make things more interesting. Say I wanted to sort by population density. So first let's calculate the population density.
I'm going to drag this over here to create an extra column and call this density. The way to calculate density I'm going to type = and then do population divided by area gives me the population density of Anchorage. Now I want to paste that quickly into everything here so I'm going to select Command C for copy, select the entire column but I'm going to Command and click and deselect the header here and just paste it into all of the rest of the cells.
Now I've got population density for each city. Now I can simply sort and I get the population density which you can imagine Anchorage is the least dense there. The most dense would be New York City.
Let's go back to the original table. Let's say we want to sort by State. Right now it is sorted by population. So we can select Sort Ascending by state and you can see it starts with Alaska, Arizona, etc. The thing is though, inside of this we see that the cities are not sorted by anything in particular. Actually they are sorted by population because that is what we had previously. But it would be nice to have these sorted alphabetically inside of the state.
So in order to do that what we can do instead of just sorting with one column is go to Sort & Filter Options which brings up these controls here on the right. We can then control exactly the sorting order. 
So instead of sorting by anything we will just delete these, clear these out, Sort the Entire Table, Add a Column, and sort by State. Then add another column and sort by City. So it is going to sort first by state ascending and then by city ascending. You can see here inside of Arizona everything is in alphabetical order.
You can quickly change that to sort by, say, population. Now we have Arizona sorted in population. We can do it in descending order and see the most populace city in every state until it gets to the next state.
Now what would be great if we could more clearly see where each state started here. So in other words we don't need Arizona repeated all these times, we can just have in the first one and then have these blank. One way to do this is to create another column here. Let's create a formula. 
I'm going to put a formula here in the second cell. Use If and then Exact. So it is going to look for an exact match between this cell and the one above it. If it is true I'm going to have it blank, I'm just going to put quotes there. If it is false I'm going to have it show the value there. When I do that you can see it has Arizona there but if I pasted it in here it does nothing. Let's actually paste it into the entire column here. I select it. Deselect the header row there and paste.
Now you can see it does exactly what I wanted. It is going to only put the name of the state in the first spot where the star is there. So I'm going to now move this over by dragging it to here. I'm also going to name the state and then I'm going to go here and hide this column. So it is still there, still part of the spreadsheet, but it is hidden. Now I can see very clearly the state and then the city.
What will make it even better is if I would move this one over here, like that, and now you have a very nice chart where you can see the name of the state first and then all the cities. I can sort by anything. The city name, the population, the area by simply going to the sort options here and changing this.
Sorting In Numbers

Welcome to MacMost

Free Weekly Newsletter

MacMost Online Courses

Keyboard Shortcuts PDF

Connect with MacMost

MacMost Sections

Popular Tutorials

Information
