Sorting Data in Excel

Sorting data is ONE of the Main functions of EXCEL. When you have

A whole lot of data from a statistic

A customer base with sales figures

A Budget with many different positions

Any other set of bigdata

That you want to sort out, Excel will do it for you with a bit of your help....


Our apple farm has grown and we have now 23 trees instead of 10 and so it is more difficult to have a good overview. So we want to sort out our trees according to the total number of apple harvested. You can see the graph is also not so nice anymore....

apple tree sorting    graph apple orchard farm

So there are 2 methods and I will start with the most difficult one because it allows you also much more flexibility and gives your more choices and options for sorting.

We will be using the SORT function in the DATA ribbon.

data ribbon sort

We must now select the area we need to sort out. So lets select from row 5 to 27.

selected area apple

Now by pressing the SORT button the following windows will open.

sort window

You can see it gives you the choice to sort according to your 4 columns: apples, morning, afternoon and total number of apples.

You can sort it by Value, Cell Color, Font Color and Cell Icon..... Isn't this great!!!


Now we will stay with Value.

But for example if you had used the conditional formating to color the cells or had colored the cells manually, then you could do some sorting according to the colour.

So by selecting sort TOTAL by Values, the table is sorted the following way.

sorted table

Please OBSERVE the trees 17 and 18, they both have 500 apples. So which one should be first.

That is how we use the ADD LEVEL Function to add one more condition that says we want to sort according to the morning harvest as a second sorting filter.

sorting filter

And the result is the following. Here you can see the two filtered results side by side. Everything is the same, except the trees 17 and 18. This was an easy example, but there might be times when you have multiple data with exactly the same results for some parameters and you want to separate them. That is how it is done.

filtered results

About the Graph

You can change the graph very easily in the quick layout area to remove some of the data that overwhelms it.

layout graph


Use of Tables   

As explained in the basics, Excel gives us the possibility to create tables easily and additionnaly lets you do some easy filtering process.

If you only look for single column filtering, that is what you have to use. Otherwise, use the SORT function explained higher in the text.

First we must create  a table and to do this we must select the row with the header to the last row with tree 23.

select table

We then select the table design we want in the HOME RIBBON Format As Table.

format as table

We can then easyily filter one column or the other by selecting the column we want to filter and select the filtering type.

Here we changed from Smallest to Largest  into Largest to Smaller...

filtering table



So that's it for sorting data. We sorted it out (;-)...

These were only some basics. Now you will learn by doing.