Excel 2010 Masterclass – Sorting and Querying Data

blackboardexcel7Okay, we have now created our spreadsheets and got them looking great but now we need to put them to good use.

There are many tips and tricks we can employ and one of the most common and useful techniques to help us on our way is the ability to sort and query our data.

Microsoft Excel has built-in functionality exactly for this purpose so let’s take a look at what is possible.

You can download a sample staff details spreadsheet >>here<<, in Excel 2010 format, to accompany these instructions.

If you have downloaded the file, open it and click on the box to Enable Editing. This will put the instructions in context and make it easier to follow the blog post.

Now that the spreadsheet is open, the first thing we will look at is sorting data.

Sorting Data

excel005_01

We are going to apply a custom sort to our data according to Surname and then First Name.

On the Home ribbon bar click on the Sort & Filter button that you will find in the Editing section (shown right).

Select the Custom Sort option.

The Sort dialogue box appears.

In the Sort by box, click on the down arrow and select Surname from the options on show.

Now, click on the Add Level button on the top-left of the dialogue box.

A new row has been added.

In the new Sort by box, click on the down arrow and select First Name from the options on show. (Shown below).

excel005_02

Click OK.

Examine your data and note that your changes have taken place.

Filtering Data

We are now going to query our data by using a function called Filter.

On the Home ribbon bar click on the Sort & Filter button that you will find in the Editing section (shown right).

Select the Filter option.

Notice that your headings now have a down arrow displaying on their right-hand side. (Shown below).

excel005_03

Now, imagine you have been asked by the local council to supply the number of employees that live in Kilmarnock and earn more than £20,000.

excel005_04

First of all we will filter out the number of employees that live in Kilmarnock.

Click on the down arrow to the right of the Town column heading.

In the dialogue box that appears (shown right), you will see all of the town options that are available. We are only interested in the town of Kilmarnock for this exercise so…

Click on the (Select All) box that you will find above all the town names. This will de-select all the towns.

Now, click in the box beside the Kilmarnock option.

Click OK.

Note that the spreadsheet is now only displaying the employees living in Kilmarnock and that the down arrow to the right of the Town title has changed, and added a filter icon.

Now let’s sort out the salary constraint.

Click on the down arrow to the right of the Salary column heading.

Select the Number Filters option.

In the new dialogue box that appears, select Greater Than.

In the Custom AutoFilter dialogue box, type 20000 in the box to the right of where it says “is greater than”.

Click OK.

How many employees are left that comply with the filters you set?

If the answer is six, you have done very well.

That is the end of our Sorting and Querying Data masterclass.

Please use the comments box below to let us know what you think of our blogs.

Bob Thomson is an IT professional with over 20 years experience in the industry having worked as a Consultant, Director, Manager and Trainer. He has vast experience using Microsoft software packages as an individual and in businesses situations.

Bob Thomson
Owner / Managing Director
Real Options