Excel : SORT AND FILTER

Sorting:

The literal meaning of sorting is to rearrange the stuff in a desired manner. The exactly same facility is provided by MS Excel to sort the data.

While sorting a table, we rearrange the rows based on the contents of a particular column for example we need to sort a table in such a manner that the names must be in alphabetical order or, column must be sorted by the totals etc.

(MS Excel 2007 has been referred for all the examples used in this article.)

How sorting is done

To sort a table by a particular column, go to Data and click on any of desired symbol (as shown in figure below).

The given symbols are used to sort the data in ascending and descending orders respectively. Another way to perform sorting is to click the drop-down in the column header and choose one of the sort commands.

Ascending and descending orders for different data types:

  • Character

Ascending order:-A-Z

Descending order:-Z-A

  • Numeric

Ascending order:-smallest to largest

Descending order:-largest to smallest

  • Date

Ascending order:-oldest to newest

Descending order:-newest to oldest

Example:

A table is shown below, which depicts the property details along with respective agent names.

Table 1

1.Sorting in ascending order of column “Agent” –

Select any cell in desired column then go to sort and click on the option a-z. Thus Agent column will be sorted as below figure.

Table 2

2.Sorting in descending order of column “Agent” –

Select any cell in desired column then go to sort and click on the option z-a. Thus Agent column will be sorted as below figure.

Table 3

3.Advanced sorting

  •  Customized sorting: Excel provides us with the option to customize the sorting order. This is done using advanced sorting.

Example:

We will sort the same data used in previous example, in following order of area –

 S.County

Central

N.County 

Steps to customize the order:

At first go to “Sort & Filter” option in “Data”, and click on sort option there as shown below.

Now select the column “area” and click on the drop down in order field, now click on the option “custom”.

One pop up window will appear as shown below, there any desired order can be given. For this example we will select list entry as given in figure, then click “OK”.

The table will be sorted in desired manner as depicted in figure below.

Table 4

  • Multiple columns sort: To sort the data in ascending order of list price and descending of baths, firstly click the “sort” option then select the column “list price” and order “smallest to largest” then  click on “Add Level”, this will add a new level  for sorting ,select column “Bath” and order “largest to smallest”(as shown in figure below).

Then click OK. Thus the table will be sorted in a desired manner.(result table is shown below)

Table 5

Filter:

Filtering a table refers to displaying only the rows that meet certain conditions. (The other rows are hidden.)

How to get the filter on table:

  • Click any single cell inside a data set.
  • On the Data tab, in the Sort & Filter group, click Filter.

Short cut key ->Ctrl+shift+L

Example:

Using the table given in figure 1, assume that we are only interested in the data for the S. Country area.

To do that, firstly press Ctrl+shift+L, drop downs will appear on each column of table as shown in figure below.

Table 6

Click the drop-down in the Area Row Header and remove the check mark from Select All, which unselects everything.

Then, place a check mark next to S. County and click OK. The table is now filtered to display only the listings in the S. County area.

Table 7

Advanced filter:

If the data requires complex criteria then Advanced Filter can be used.To open the Advanced Filter dialog box, click Data > Advanced.

Different filter criteria:

  • Multiple criteria, one column, any criteria true:

Example:

Filtering table 1 to get all the records of Agents “Randolph” and “Kelly”:

Steps to follow:

a. To find rows that meet multiple criteria for one column, type the criteria directly below each other in separate rows of the criteria range.

Table 8

b. Click a cell in the list range.

c. On theData tab, in the Sort & Filter group, click Advanced.

d. Do one of the following:

  • To filter the list range by hiding rows that don’t match your criteria, click Filter the list, in-place.
  • To filter the list range by copying rows that match your criteria to another area of the worksheet, click Copy to another location.

e. Now set the criteria range and location where you want to copy the result as shown in figure below.

f. The result table would be as follows:

Table 9

  • Multiple criteria, multiple columns, all criteria true

Example:

Filtering table 1 to get all the records for flats of “Central” with pool and “S. County” without pool.

Steps to follow:

a. To find rows that meet multiple criteria ,multiple column, type the criteria in the criteria range as shown below-

Table 10

b. Repeat the remaining steps as mentioned in previous example.

c. The result would be as follows-

Table 11

  • AND condition [condition should be given in Same row but different columns]

Example:

Filtering all the flats of Central Area between the list price of 200000 to 300000

Boolean logic for this example:      (list price > 200000 AND list price < 300000)

Steps to follow:

a. In such cases, first of all include multiple columns with the same column heading and type the criteria in the criteria range, as shown:

Table 12

b. Repeat the remaining steps as mentioned in previous example.

c. Result is shown in table below:

Table 13

  • OR condition [ Condition Should be given in different rows]

Example:

Filtering all flats listed under Central with list price above 250000 with 1 bath or 2 baths.

Boolean logic for this example:      (list price > 250000 AND (bath=1 OR bath =2))

Steps to follow:

a. In this case, include different rows with the same name(as per the criteria) and type the criteria in the criteria range, as shown:

Table 14

b. Repeat the remaining steps as mentioned in previous example.

c. Result is shown in table below:

Table 15

References

 

 

 

 

 

About Prachi Karmahe:

Prachi Karmahe is a M.Tech(Digital Communication) . Currently she is working as an Analyst Intern with NikhilGuru Consulting Analytics Service LLP, Bangalore. She has prior worked for around 1 year as an Assistant Professor with Sardar Patel College of Engineering.

 

Be the first to comment on "Excel : SORT AND FILTER"

Leave a comment

Your email address will not be published.


*


error

Subscribe for Data Analytics Edge Newsletter & Share..:-)

error: Content is protected !!