(3 Iww wm

Advanced Features
of Excel
OwDDOww w ww wwn wwww ww w www wwwww www wwww
DO
LET'S SURF
Sorting Data Conditional Formatting
Filtering Data
LET'S N
statements are True False.
State whether these
or
Area chart displays the data trends over period
a of time.
1
2. A charthelps us to represent data pictorially.
other components.
3. Plot area contains the actual chart and its
4 Excel provides eight categories of charts.
In the previous chapter, you have learned about different types of charts in Excel. In this chapter you
are going to learn how to sort and filter data. You will also learn about conditional formatting.
SORTING DATA
easier to work with sorted data. Sorting of data means
Every day, you deal with a lot of data. It is
to organise the data in ascending or descending
order. Excel allows you to sort both numeric and
column as well as a range of data. To sort
textual data. You can sort the data according to a particular
data, follow these steps:
to be sorted. In this case, we have selected the range
Step 1: Select a column or range of data
A3:D8
tab. A dropdown
Step 2: Click on the Sort & Filter command from Editing group under the Home
list appears. 3
the
Step 3: Click on the Sort A to Zz (for text) and Sort
Smallest to Largest (for numbers) to sort
data in ascending orde.
O w w Owwwwwwww
Advanced Features of Excel 25
 Share
ce
Feck ianf fa d The Sort dialog box opens.
you
ahat
t me
TCondtonal Fomalbmg hsen
Rvin
PopeLayod Gene Check My data has
romal Tbe Select Step 4 headers checkbox, if the
Cels SontAtoZ selected columns have a
Sgn Zto A Step 5: Click on the Sort by box and heading at the top
select the column
en Cystom Sort header according to which
E
sort the data. In this case, we have you wantto
Chmg
Sharme Y Eiter selected Client Name header.
Step 6: Click on
the Sort On box and select
Cell Values
Billing Details option.
98755214chiras@kO10nAEO Step 7: Click on the Order box and select the
895866485Skaushik@[Link]

A to Z or Z to A
Nagar, Deil option. In This
895755642kumarORmallcom
selected A to Z option. case, we have
Chirag Sharma A-23, Ashok
Dvya Kaushik E/134, Surajpur, Noi 54675895pkngirl@[Link]
G-2132/34, Srojnl
Nagar, Delh
umar 7856895654Kovalanshu@[Link]
Anil Step 8 Click on the Add Level button at the of
AKansha Gi D1/23,Nehnu Place, Delhi
iab S6S48b655|[Link]@[Link]
top the Sort dialog box to add
Anshu Goya B-22 Kainagar, ona sort. In this case, we have added another columnto
ea
Muskan Sharma House Na.34, G6l0 Amount Billed column.
Click on the OK button.
Step 9
6
- Sum 4 R E
Avrage L04577728 Coumt 4
Sorting data
order. pede
the n a m e s in ascending Sert On
sorted according to co 2 s
sort byCient iae cel Vauts to
The selected data will be
Custem u
Custom Sorting column is in ascend.
columns are to be sorted in
such a way that the first
In case, more than one
the second column of such rows get
nding
for more than one rows then gets
order and if some data is same
can do this in Excel using
Custom Sort. 10 use Custom Sorting, follo
sorted in descending order. You Sort dialog box
these steps: The data will be sorted according to the criteria defined.
Step 1: Select the range of columns to be sorted.
Step 2: Chck on the Sort &Filter command from the Editing group under Home tab. A drop
Tech Hint
down list appears. To sort data: click Data
Sort
Step 3: Click on the Custom Sort option from the drop-down list.
ome Page LayoutFormuis Data Revew Ves 9Tel me what you want to do LEr's CATCH UP
Shan
1A me GCondtonal Fomating nsert
S% Fomal as Tabie Delete & d &
Why sorting is important?
pbeard
Fomat Rhe-S
Seiect
Agneen Nunber yns Sort to Z
Client Name Sgrt Z to
D G H
Cton Sed
Y ter
Projects Detall
Oe NameProject Name Start Date Na of
Ankita
Hours Spent Amount Billed
4
|Sareenshots 6/5/2020
NiOn 2500
Editing 9/5/2020
Navy 3500
KO0E Keeding 10/6/2020 FILTERING DATA
1500
V42020
Barasn 45000
7M2020
35000 You must have studied about filtration
process which is used to unwanted material froma
separate
0 mixture. Excel also allows you to filter unwanted data from a set of data. To
apply filters, follow these
steps:
age 255466 Cou 3 82
115
Step 1: Select the range of columns to be filtered.
Custom sorting
Step 2: Click on the Sort & Filter command from Editing group under Home tab. A drop-down
26 Touchpad PLUS (Version 2.0VII list appears.
Advanced Features of Excel 27
 T0

ophMi

thw
1lter Step : Place your mouse er the
(k
on

select the Greate

umter fiters tnpiun
top 4
M Than optien
The Custom Autofilter dialoy bon
appears
Me Step Enter 80 in the criteria
hos anid ciu
on the OK
button
MArh Dololly

Custom Autof lter

dlalog hoz
um
44
i
177

Usng Filter
the column headers.
the
of all
in front
small
arrows
appear
otice that only the talls of the students who
notice
that have obtalned marks greater
You will MarkeDotalls and the remaining rowS remain hidden.
in front of the
header
than ) are
deplayed
A drop
arrow

on
the Term.
Clck
Step 4
Marks
Obtalned in
Second

notice that all the Removing Filters

down list
appears.
You wll
the list An
The filters once applied
can de easuy
removed. Cick
present in anywhere
in the
in the
column are
Click
to a0oly filters. You
wil notice that the Titer
arrows in front of
worksheet and repeat steto
entries
the beginning. column headers
with small
checkboxes in
them.
ddherth hidden rows also reappear. disap0ear and the
checkboxes to uncheck i74

s o m e of
the 4
notice that
You will
Step 5: Click on the OK button.
u n c h e c k e d data
are
removed cONDITIONAL FORMATING
the rows of
as the Cnnose you do not want to hide
You need not
to worry any rows but stll want to
from the list.
The unchecked rows
have condition, for example greater than 80. This type of formatting ishighlight
known as
all the cells that
satisty a

data is not lost. Filtering data Conditlonal Formatting

the display. in Excel. To apply conditional formatting to a series of data, follow these
justbeen hidden from steps:
Step 1: Select the data to which
formatting is to be applied.
Marks Details Click the Conditional
on
laFist Ten cMarksObtalnedin
SecondTerY
Step 2:
Formatting command from Styles group under Home tab. A
Name ofthe stude arks Obtalined drop-down list appears. This list shows various criterla like:
AL
Anab Highlight Cells Rules: This option is selected when you want to highlight all the cells satistying a
given condition. When you hover the mouse pointer over this option, it opens a sub-list showing
Anite
Griharsh
criteria like Greater Than, Less Than, Equal To, Between, etc.
Siddharth
Filtered data Rules: This option is selected when you want to highlight some top or bottom
Top/Bottom
To get the data back, open the filter drop-down list again and check the unchecked entries. number of items in a data serles. When you hover the mouse pointerover this option, it opensa
Excel also alows you to use custom filter. Suppose, you want to know the names of the students who sub-list showing criteria like Top 10 Items, Top 10%, Bottom 10 Items, Bottom 10%, etc.
numeric
have scored more than80marks in second term. Follow these steps to get the required information: Data Bars: This option is selected when you want to add data bars to the cells having
barsof
Step 1: Apply filters to the data. data. When you hover the mouse pointer over this option, it opens a sub-list showing
different types and colours that can be added to the cells.
Step 2: Click the Marks Obtained in Second Term header to
open the filter drop down list.
on

Advanced Features of Encel29

28 Touchpad PLUS (Version 2 0)-VII
 you
want
to
d Colour
to
bottom
wnen ton
selected from

TEST YoUR SsoaLS

varying

This
optionis

cels
sowy

pointer
over
this option, t
thie

that can be
#s.

Scales:
mouse varations
selected

the correct option.

Color

all the

Tick ()
colour

to hover
schemes diferent
When
you

showing
Sort &Filter command is present under the
a. tab.
FACTOPEPA to add icon
items. ade

sub-list

wantto
want

Sets
3 you Home
opens

addedtoces
when
selected
winich
are moderate
moder

and (i) Insert

is
This
option
acceptabie,
of
icon sets that can
sets
(i)Formula (v) View
types

the mouse
are
Sets:
diterent th
lcon
cels when

We use Sort A to Z option to sort

which
opens
Ine
show

sub-list
that
b.
to attention.

the
need in Numbers
which shown

option.
(i) Text
added
are
over
this
formatting.
In this case
be
is
hovered

conditional
ind
(i) Symbols O ) All of these
pointer

the
desired Data
Bar
option
the When we applyfiter feature, smallarows appearnear
Freeze Panes feature
formatting
Orange
Select
the
conditionalfon
C.
selected

allows you to
lock Step3 have
The
selected Row headers i) Column headers
we
category.

your column/row so cell

range.

scroll
Data
Bars

to
the
selected
(ii) Sheet tab O ) None of these
that, when you applied

view
s is not a category used in conditional formatting?
down or up to n27 d.
pet Sot&Fed
sheet,
the rest of your eme
Long ()Data Bars (i) AtoZ
the locked column

row remain on
the
LD

(i)Top/BottomRules O (iv) Icon Sets

option is used to remove conditionalformatting
Screen.
e. The
)NewRule (i) Clear Rules
so

(i)RemoveRules O (v) Delete Rules

true and 'F for false.

2.
Write T for

a.
Excel can aange data in ascending order only.
Usingconditional formatting
than one columns at a time in a selected range of cells.
h You cannot sort more
highlighting
cels by selecting
The Add Level button is available under the
rule for lInsert tab.
erliest
define your
own
Formatting
drop-down lict C.
llowed for also
You can
& Filter group under the Data tab.
Conditional

from the data range can can also be done through Sort
ulations inExcelis New Rule option features applied
to a d.
d. Sorting
formatting the Conditional
data.
January 1, 1900. The
conditional

Clear Rules
option from e.
Conditional Formatting is only used with numeric
cleared by selecting
be answer type questions.
Formattingdrop-downlist.
3. Short
a. Define sorting.
LT'S BACK-UP
or frilters?
data in ascending How do you remove
to organise the b.
Sorting data
means

descending order.
more than one
Ifyou need1 second sorting is used
to apply sorting on to hide unimportant data?
Custom
different criteria.
[Link] command is used
to fill out 1 cl, it column in selected range
on

would take you 545 and removed according to

The fiters can be easily applied
years to complete the the user's requirements.
whole worksheet. on the basis of
Conditional fomatting can be applied
different critena. Advanced Features of Excel 31

30 Touchpad PLUS (Version 2.0-VI

 questions. Periodic Assessment 1
5 Long a n s w e r type and filtering data?
difference between
sorting data
a. What is the conditional formattina
on the
basis of which ing to 3)
on chapters 1
names of the criteria
can be
b. Write the a (Based
Sort feature.
Custom
[Link] the steps to apply
conditional formatting.
d. Write the steps to apply statements.
incorrect
the its LSD.
Rewrite called
A. in a number system is
number of digits used
1.
The total
FUN ZONE 0-15.
consists of 16 digits from
number system
Hexadecimal
LET'S SouE
Application based question. elimination.
for
marks obtained in exams. He wants. BEDMAS rule, E stands
of students with their to a In
a
John is preparing list students names. him a feat.
order according to the Suggest ature
the data in ascending
of t absolute referencing.
his work quickly. can be used only for
which helps him in doing 4 $ sign
who am I? under Data tab.
2. Guess command is present
contains the Sort & Filter command. Conditional Formatting
tab that
Iam a group ofthe Home 5.
unwanted data from a range of data.
b. I feature in Excel to separate
am a
each.
or descending order.
to arrange data in ascending one example of
C. lam a feature in Excel use and
on the basis ofa criteria. State the
B.
d. lam a type formatting that can be applied
of from smalest to largest. 1. Column chart
lam the which the data is arranged
e. order in
2. Pie chart

LEr'S ExPLORE 3. Area chart

data to make their everyday work easie
out the various ways in which people organise Bar chart
Find
You can start this by asking a shopkeeper. 4
Scatter Plot chart
XY
5.
Subject Enrichment the odd one out.
TECH PRACTICE C. Circle
Scientific Binary
for the distance of their 1. Decimal Octal
Make a
list of your 10 friends in Excel. Make columns home F
H
from their home, etc. Use sorting to arrange data in A
from school, distance of post office 2. Particular
an order. the cells which have distance less than 2.5 km in any cell. Relative
Highlight Absolute Mixed
3. AVERAGE DAY
No iend Name |Distance rom thelr home to school Distance from thelr home to post office 4. TODAAY YEAR

D. Match the following columns.

Column B
Column A
[Link] a ist of 15 teachers and subjects taught by them. Apply Filters on the subject's
field. Take printouts of various filter options of subjects and their result.
a. (1111)
(26)30
b. (101.101)2
2 (64)10
For The Teacher
& 3 (5.625)o (11010)
1. Demonstrate to the students the concepts discussed in the d. (100.001)»
chapter. 4 (15)10
2. Make the students understand differences between
filtering and sorting features. e. (1000000)
5. (4.125)10
Periodic Assessment 1(33
Touchpad PLUS (Version 2.0
32 VII