number

Google Sheets - Sort - by Number, Date, Multiple Columns - Part 6



Sharing buttons:

okay so let's talk about sorting in

Google sheets so I have a couple of

columns here with some dates and I'm

gonna sort them I'm gonna start with

this one I'm gonna select the data first

I'm gonna go under data and here we'll

have this option sort range I'm gonna

click on that and here there is this

check box data has header row header row

would be when you have a label or a name

for a column similar to this column on

the right where it says date now we do

not so I'm gonna leave it unchecked and

column a is what we want to sort by I'm

gonna go a through Z hits sort and there

it is it's sorted early is date on top

latest date all the way down now if your

data has a label on top or a header

whatever people like to call it then you

select it I'm including the header I go

on their data again sort range and here

I'm going to check the box data has had

a row because we do have it and now I'm

gonna go A to Z or Z to a I'll do Z to a

this time so sort so now we have the

latest date on top and earliest all the

way down so just make sure you pay

attention do I have a header on top or

not and based on that just use the check

box and if you do that you'll be fine

now let's talk about what you should

expect when you sort data let's go here

so we have a little mix of different

type of data here we have some text some

numbers some text that looks like a

number but it's not a number see a line

to the left we have some blank cells

here as you can see I'm gonna add a

couple of dates here just to make sure

we have

all types of data here there it is now

I'm gonna select this column and I'll go

under data sort range and then this

doesn't have a header so I'll do a to Z

so let's try to pay attention what

happened here so we got our numbers

sorted here right then we have this

dates and then we have some numbers now

these are not really numbers these are

text if you remember so now because this

is text if you look our numbers 3 4 2130

that's the way you would expect numbers

to be sorted now because this is text

the way this is sorted if you look at

this 4 it didn't go before 20 it went

after 31 because we're basically sorting

this as text so we look at the first

character and alphabetically it goes to

2 3 4 even though when you think about

numerically we would put this 4 above so

if you have text that's not a number

this is what you should expect that

should happen then we have our batted

characters see all the text and then if

you also remember we had some blanks now

blanks always go all the way down I'm

gonna undo this so you can see so look

here we have a blank here we have

another blank where is the other one

here so when we sort it those blanks

went all the way down and interesting

thing about blanks is that no matter how

you sort it's always gonna go to the

bottom of your data so if I do data and

do sort range and do a to Z I hit OK see

blanks are gonna be all the way down

here and then similarly if I undo this

and do sort range and do Z to a

the other way around I sort this while

the rest of the data sorts the way we

would expect it just goes the other way

see blanks are always all the way down

so that's something to pay attention to

I'm gonna undo this and finally let's

take a look at these dates now just to

sort this again let me just highlight

distance or this one more time select

data sort range A to Z sort that's fine

now we have our numbers and then we have

this dates sorted below let's take a

look at this dates in general formatting

so I'm gonna do number that's fine see

this number for this date is twenty one

thousand one hundred and eighty six this

is forty three thousand four hundred and

thirty six I'm gonna undo that so why am

i mentioning this let's try to put a

number like thirty thousand in here and

then I'm gonna select this this time I'm

gonna press command a to select this and

then it will go on their data and then

sort range and I'll just do a through Z

so we can see what happens

now you see this number went between

this dates so the way we're sorting

dates

we're basically sorting them as a number

we take the serial number of the dates

so if you take the date and change it to

a number that's that serial number so if

I just just do four all of these see

we're sorting these as a number even

though those are dates that's what you

should expect when you're sorting

different types of data that are all

grouped together the other thing to keep

in mind is when you have something like

this so if I take this and do data and

sort range I'm gonna do a two

see there is no headers so sort see the

way it's gonna sort again this is text

so it's gonna go D 1 D 10 D 11 and then

D 2 is gonna go below because after D 1

there's D twos usually if you were going

to sort this you would probably want to

sort this like d1 d2 d3 d4 and the 11

would go after those right but that's

not what's going to happen when you sort

in Google sheets now to go around this

what you could do you could do something

like this so you could create a function

mmm I'm gonna use reg ex extract

function and that's a regular expression

function I'm gonna click on this text

comma and then the expression is gonna

be the plus and what this is doing I'm

not gonna go a lot into regular

expression if you want to watch more

about this I have a video covering this

function please look in the channel and

watch that but generally speaking I'm

just grabbing the number part from that

text on the left and if I enter see I'm

getting 15 but if you pay attention this

15 is aligned to the left because this

is 15 as text not 15 as a number so if I

drag this down right now and I try to

sort this so I'm gonna now sort these

two together I'll go here data sort

range and I'll sort it by column F and

again there is no header row so sort C

I'm still getting the same thing because

we're sorting these as text these are

not numbers so I'm gonna undo this so to

make these a number I can do this trick

of adding a zero to this and then I drag

it down you can see how this now aligns

to the right that is a clue that this is

now a number and I'm gonna drag it down

now these are numbers so if we sort

damn they should get sorted as a number

column f it is e there we are so I'm

using this helper column to sort it and

now it works out fine alright moving on

those are some common basic things you

need to understand about sorting when

you sort let's go to this next tab now

let's sort some data that has multiple

columns I kind of already did it when I

had that helper column but now let's do

this again so when you're sorting some

data that has multiple columns your

first thing should be to highlight the

entire data if you have headers on top

highlight headers if you don't just

highlight the data so I'm going to

select all the way from right to the

bottom so I go under data sort range and

then because we have headers on top

we're going to check this box and then

you select which column you want to sort

by and because we have headers it's

going to be easy to select we don't have

to look at ABC we can just choose the

name so if I wanted to sort this by

sales number I would go sales and if I

want the highest number on top I would

do Z to a hit sort and there it is

everything is sorted and the highest

sale is on top I'm going to undo this

command-z or control-z that would take

us back here

let me try this again I'm gonna go here

now I'm gonna try to show you how we can

do multi-column sorting instead of a

single column sorting and what I mean by

that let me first just sort this by

sales wrap alphabetically so I'm gonna

go data sort range data has headers

sales wrap a to Z sort great now we have

this sorted alphabetically as you can

see by our salespeople now I have the

same salesperson repeating multiple

times here again now what I want to do I

want to do a second level sort when I

have the highest sale for that sales

wrap appearing on top of my data

in this particular case if I look at

these see we have multiple sales here on

the right and I want this 529 to appear

on top and then 368 would be the next

one and so on right so what do you do

again you select your whole data data

sort range you make sure you check this

box we first select the sales rep column

and that would be a to Z and then what

you do you add another sort column and

as a damn by column I'm gonna choose

sales and I want the highest on top so Z

to a for this sort and here we go now we

have this sorted by this column first

and then by this column and you could do

multiple levels so you could sort by

sales wrap them by region and then by

sales or whatever you want to do it's

pretty much the same thing you simply

just highlight date short range and

anytime you're sorting by multiple

columns just click add another sort

column and now you have multiple options

to choose from so that's that so sorting

as you can see largely is very easy once

you understand all the little details of

how it works but more importantly I want

to show you some things that you should

never do when you sort so let's look at

this data so one thing you should never

do when you sort is just select one

column so if you wanted to select this

one column because I want to sort by

sales rep and I go under data sort range

and I say it has headers because it does

and I'm gonna do a to Z right now let's

try to pay attention here so this is

this first guy and his date of sale is

to 24 2016 and that's hundred and sixty

dollars I'm gonna hit sort and you see

what happened all of the rest of columns

they stayed the same

while these columns sorted by itself so

we essentially just broke our data set

by doing this I'm gonna undo this is our

data so don't select one column only

don't select two columns or three

columns unless you want that sorted

separately from the rest of the data

which I don't think you will ever want

to do you want to make sure you select

all the columns in your data from right

to left even though you're sorting by

one column so the other thing is this so

people have this habit of having their

totals all the way down below their data

set so they go here and when they select

data they also select those totals so if

you go under data now and sort and then

I try to sort this by sales let's say

and I'll do Z to a I hit sort see now

what happened is well first of all

nothing works you can see that right but

the reason it doesn't work I'm gonna

undo this it's sorting this by this

sales column and it thinks that this is

one of our rows in our data set so

because I'm sorting by sales from the

highest number to the lowest number it

is going to think that this is the

highest number so it's trying to take

this number and put it above but this is

a formula that does the sum of this

column and the moment it moves it up

here it doesn't know what to do with

that formula and that formula breaks so

if I redo this see here we go

now this is a reference error because it

doesn't know what to do with this range

for this function so I'm gonna undo the

correct way of doing this obviously is

to still select all the columns in this

data like we did before just don't

select your totals row or any road that

doesn't really belong to this data set

all the data that needs to be sorted

just select that and you're on your data

sort range and just sort that

pretty much it so if you just follow

those common-sense rules you should be

fine and that's all there is to it

thanks for watching please subscribe and

I'll see in the next video