7

PSBased on Windows 10 with MS Ofce 2016 Version

Name .....................................................................................
KI
Class ................... Section ................... Roll No ...................

School .....................................................................................

Address .....................................................................................

Phone .....................................................................................

© Kips Learning Pvt. Ltd. 2020

 CONTENTS
S.No. CHAPTERS PAGE

1 NUMBER SYSTEM 8
l Decimal Number System
l Binary Number System
l Octal Number System
l Hexadecimal Number System
l Computer Arithmetic

2 USING EXCEL AS A DATABASE 18

l Using a Form to Enter Data
l Adding, Searching, and Deleting a Record in a Form
l Sorting and Filtering Data
l Using Advanced Filter and Data Validation
l Adding Subtotal in Database
l Analyzing Data with PivotTable

3 ADVANCED FEATURES OF EXCEL 30

l Components of a Chart
l Commonly used Chart Types

4
l Creating a Chart
l Chart Elements
l Formatting a Chart

l Using Goal Seek

l Grouping Worksheets
l Consolidating Data

LOG ON TO ANIMATE CC
PS
l Combo Charts and Sparklines

44
l What is Animate?
l To Open Animate
l The Workspace
l Setting Document Properties
KI
l Drawing an Object and Grouping Outline with Fill
l Applying Gradient Fill
l Creating a New Gradient
l Modifying a Linear Gradient
l Modifying a Radial Gradient
l Editing Objects
l Importing Graphics
l Animation in Animate
l Tint Tweening
l Creating a Simple Text Shape Tween
l Applying Filters to Text
l Animating Filtered text

5 WORKING WITH LAYERS 64

l Selecting a Layer
l Renaming a Layer
l Adding New Layers
l Changing the Order of Layers
l Hide and Show a Layer
l Lock a Layer
l Creating an Animation with Multiple Layers
l Masking in Animate
l Rotation with Masking Effect
l Onion Skinning

WORKSHEET 1 AND 2
© Kips Learning Pvt. Ltd. 2020
 CONTENTS
S.No. CHAPTERS PAGE

6 MORE ON PYTHON 78
l Types of Operators in Python
l Operator Precedence
l Algorithm
l Flowchart
l Conditional Statements in Python
l Types of Control Structures
l Conditional Statements if, if...else, and if...elif...else

7 INTRODUCTION TO HTML 5 94
l What is HTML and its Brief History
l Tools to be Used
l Creating an HTML Document
l Tags, Elements, and Attributes
l Rules for Writing HTML Code
l HTML Document Structure

8 MORE ON CSS3
l In-Line Style
l Text Properties
PS
l Heading, Paragraph, Line Break Element
l Horizontal Rule, Comment, Bold & Italics
l CSS and Methods of Applying CSS
l Background Properties

110

l Font Properties
l Margin Properties
l Border Properties
KI
9 CYBER TOOLS 122
l Social Networking Sites
l Microblogging and Twitter
l Cloud Computing
l Google Drive
l OneDrive
l YouTube
l Google Maps

10 CYBER THREATS & SECURITY 130

l Cybercrime
l Cybercrimes Against an Individual Person
l Cybercrimes Against Property
l Cybercrimes Against Organisation/Society
l Cyber Security

WORKSHEET 3 AND 4

PROJECT WORK 142

ROBOTICS 147
GOOGLE SHEETS 149
QUICK GLIMPSE OF OFFICE 2016 155
NATIONAL CYBER OLYMPIAD 158
© Kips Learning Pvt. Ltd. 2020
CHAPTER
1
NUMBER SYSTEM
LEARNING IN THIS CHAPTER
Decimal Number System Hexadecimal Number System
Binary Number System Computer Arithmetic
Octal Number System

In early days when there were no means of counting, people used to count with the help of fingers, stones, pebbles,
sticks, etc. These methods were not adequate and had many limitations. To overcome these limitations, many
number systems were introduced with the passage of time, like:
† Decimal number system † Binary number system
† Octal number system † Hexadecimal number system
A number system is a set of values used to represent different quantities.

DECIMAL NUMBER SYSTEM PS

The need for counting paved the way to introduce Decimal number system in which digits 0,1,2,3...9 are used to form
any number. Most of our arithmetic operations are performed with decimal numbers.
Decimal number system consists of ten digits i.e., 0 to 9 with the base 10. Each number can be used individually or
they can be grouped to form a numeric value. E.g., 82, -256, 52.87, etc. The value of each digit in a number depends
upon the following:
† The face value of the digit
† The base of the number system
KI
† The position of the digit in the number
Each position represents a specific power of base (10). The right most digit of a number is called Least Significant
Digit whereas the left most digit is called Most Significant Digit. For example, the number 547 can be represented in
the following way:

5 4 7
7 * 100 units = 7
1
4 * 10 tens = 40
2
Example 5 * 10 hundreds = 500
547

OBSERVATION
The positional value of each digit increases ten folds as we move from right to left. In the above mentioned example;
5, 4, and 7 are the face values and their place values are hundreds, tens, and units respectively. The place value
depends on the position of the digit in the number.
Now let us discuss about the various types of number systems that are used in a computer.

© Kips Learning Pvt. Ltd. 2020 8

 BINARY NUMBER SYSTEM Fact File
The Binary number system consists of only two digits, i.e., zero and one (0 and 1). Since
this system uses two digits, it has the base 2. All digital computers use this number
system and convert the input data from the decimal format into its binary equivalent.
WHY BINARY?
A computer cannot understand human language, rather it understands only the binary Aryabhat was India’s
greatest mathematician
code. Therefore, the data that is entered into a computer is converted into its binary and astronomer. He
equivalent. It further converts the binary results into their decimal equivalents for introduced the concept of
0 (zero) without which
output.
modern computer
CONVERSION OF DECIMAL INTO BINARY NUMBER SYSTEM technology would have
been non-existent.
The equivalence between binary and decimal numbers can be understood with the given
examples. To convert a decimal number into binary number, follow the given rules:
Step 1: Divide the given decimal number with the base 2.
Step 2: Write down the remainder, divide the quotient again by 2. Let’s Know More

examples:

Example 1:
PS
Step 3: Repeat step 2 till the quotient is zero.
Let us understand the conversion of Decimal number into Binary number with the given

Example 2:
Base or Radix of a
Number System
The base of the number
system is the number of
digits used in it. E.g., Since
the decimal number
system uses 10 digits, its
base is 10.

2 25 2 321

2 12 1 Least Significant Digit 2 160 1 Least Significant Digit

KI
2 80 0
2 6 0
2 40 0
2 3 0 Fact File
2 20 0
2 1 1
2 10 0
Example 0 1 Most Significant Digit
2 5 0
2 2 1
Thus ( 25 )10 = ( 11001 )2
2 1 0
Gottfried Leibniz,
0 1 Most Significant Digit
a German mathematician
The base of number is given as is credited with the
subscript. Thus ( 321 )10 = ( 101000001 )2 invention of the modern
Binary number system.

Remainders, which are obtained in each step are written in reverse order, i.e., placing the
Least Significant Digit at the top and Most Significant Digit at the bottom, to form the
Let’s Know More
binary equivalent of the decimal number.
Which number system do
BINARY TO DECIMAL NUMBER we use?

To convert a binary number into decimal number, follow the steps given on the next page.
© Kips Learning Pvt. Ltd. 2020 9
† Multiply each binary number with its positional value, which is in terms of powers of 2, starting from the extreme
right digit.
† Increase the power one by one, keeping the base fixed as 2.
† Sum up all products to get the decimal number.

Example 1: Example 2:
(1010)2 ( 1 0 0 1 )2
0x2 0
- Units = 0 1 × 20 = 1
1 x 21
1
- Tens = 2 0×2 = 0
0 x 22 - Hundreds = 0 0×2
2
= 0
1 x 23 - Thousands = 8 1 × 23 = 8
Thus (1010)2 =(10)10 Thus (1001)2 = (9)10

Example
Example 3:
( 110001001 )2 = 1 × 28 + 1 × 27 + 0 × 26+ 0 × 25 + 0 × 24+ 1 × 23+ 0 ×22+ 0× 21 + 1 × 20

OCTAL NUMBER SYSTEM

PS = 256+128+0+0+0+8+0+0+1
= 393
Thus (110001001)2 = (393)10

The Octal number system (Oct) consists of 8 digits: 0 to 7 with the base 8. The concept of Octal number system came
from the Native Americans as they used to count numbers by using the space between their fingers rather than
using their fingers. The procedure of ‘octal to decimal’ conversion is similar to 'binary to decimal' conversion, the
KI
only difference is the change of base. So, if we want to convert any octal number to decimal number, we have to
start multiplying the digits of the number from right hand side with the increasing power of 8 starting from 0. And
finally summing up all the products.

Example 1: Example 2:
(345)8 (317)8
2 1 0
(3 × 8 ) + (4 × 8 ) + (5 × 8 ) (3 × 82) + (1 × 81) + (7 × 80)

Example 192 + 32 + 5 192 + 8 + 7

Thus (345)8 = (229)10 Thus (317)8 = (207)10

HEXADECIMAL NUMBER SYSTEM

This number system consists of 16 digits: 0–9 and the letters A–F, where A-F represents digits 10 to 15 with the
base 16. This number system is also known as Hex, where Hex=6 and Decimal=10, so it is called Hexadecimal. The
procedure of converting hexadecimal to decimal is similar to the methods shown above, the only difference is the
change of base. To convert hexadecimal number to decimal, multiply the number with base 16.

© Kips Learning Pvt. Ltd. 2020 10

 Hex to Decimal Conversion
Know the Fact
Hex Decimal Value Hex Decimal Value 4 bits = 1 nibble
0 0 10 A 2 nibbles = 8 bits
8 bits = 1 byte
1 1 11 B
2 2 12 C
3 3 13 D
4 4 14 E
5 5 15 F
Let’s Discuss
6 6
7 7 Why do we use Binary
Number system in
8 8 computers?
9 9

Example 1: Example 2: Let’s Know More

(3B)16 (4D2)16
Most computer operations

Example 48 + 11
PS
(3 × 161)+(11 × 160)

Thus (3B)16 = (59)10

COMPUTER ARITHMETIC
(4 × 162) + (D × 161) + (2 × 160)
1024 + 208 + 2
Thus (4D2)16 = (1234)10
use the byte, or a multiple
of the byte (16 bits, 24, 32,
64, etc). Hexadecimal
makes it easier to write
these large binary
numbers.

As a computer understands only the binary code, the data input by the user is converted
into binary code for processing. This processing may involve various kinds of arithmetic
operations, such as addition, subtraction, multiplication, division, etc., on binary
KI
numbers.
BINARY ADDITION
The technique used to add binary numbers is very easy and simple. This is performed in
the same way as you perform addition with decimal numbers. The following table
illustrates the addition of two binary digits:
Quick Quiz
Binary Addition
How will you find whether
a b a+b=c a number is represented in
0 0 0+0 =0 Decimal / Binary / Octal or
Hexadecimal system?
0 1 0+1 =1
1 0 1+0 =1
1 1 1 + 1 = 10

Quick Quiz
While adding 1 + 1, the output will be 10, where 0 is written under the same column
Which number system has
Tips
and carry over 1 is shifted to the next place as it happens in decimal number addition. ‘8’ as its base?

© Kips Learning Pvt. Ltd. 2020 11

 Example 1: Example 2:
Compute (1000)2 + (111)2 Compute (11111)2 + (1011)2
1000 1111 Carry over
+0111 11111
Example + 01011
1111
101010

Binary Subtraction
BINARY SUBTRACTION a b a-b = c
The rules given in the table must be followed to perform binary 0 0 0-0 = 0
subtraction: 1 0 1-0 = 1
NOTE 1 1 1-1 = 0
The number is borrowed when 1 is subtracted from 0 ( 10 - 1 = 1 ).
0 1 0-1 = 1

Example 1: Example 2:
Compute (1111)2 - (1010)2 Compute (1100)2 - (11)2

Example
-
1
1
0
PS1
0
1
1 1
1 0
0 1
-
1
0
1
Borrowed 1

Balance
0
1
0
0
10
Balance
1
0
1
0
Again
Borrowed 1
Number is now

1
1
10
0 -1
1

Binary Multiplication
BINARY MULTIPLICATION a b a * b= c
The rules for performing multiplication using binary numbers is same as 0 0 0 * 0=0
KI
that of the decimal numbers. The given table illustrates the multiplication 0 1 0 * 1=0
of two binary digits:
1 0 1 * 0=0
1 1 1 * 1=1
Example 1: Example 2:
Compute (101)2 × (11)2 Compute (1111)2 × (101)2
101 1111
× 11 × 101
101 1111
Example + 1 0 1× 0 0 0 0×
Sum = 1 111 + 1 1 1 1 ××
10 0 10 1 1

BINARY DIVISION
The method to perform division of two binary numbers is same as that of decimal numbers. See the example
given below:

© Kips Learning Pvt. Ltd. 2020 12

 Example 1: Compute (110)2 ÷ (10)2 Example 2: Compute (10000111 )2 ÷ (1001)2
11 Quotient 01111 Quotient
10 110 Dividend 1001 10000111 Dividend
10 1001
010 001111
10 1001
00 Remainder 01101
Example
1001
01001
1001
0000 Remainder

ECAP

Most Significant Digit.

PS
† The commonly used number system is Decimal number system with the base 10.
† The right most digit of a binary number is called Least Significant Digit whereas the left most digit is called

† The Binary number system consists of two digits i.e., 0 and 1 and has the base 2.
† The Octal number consists of 8 digits and has the base 8.
† The Hexadecimal number consists of 16 digits and has the base 16.
KI
BRAIN
DEVELOPER

SECTION - A
A. Fill in the blanks.

1. The base of Binary number system is .............................................................................. .

2. The base of .............................................................................................................................................................. system is 10.

3. Octal Number system consists of ............................................................................... digits.

4. In Binary addition, 1+1 equals to .............................................................................. .

5. ............................................................................... number system is understood by the computer system.

© Kips Learning Pvt. Ltd. 2020 13

6. ............................................................................... uses 16 symbols to represent numbers.

7. In Binary subtraction, 1-1 equals ...............................................................................

HINTS 0 Binary Decimal number Hexadecimal 2 8 10

B. State True or False.

1. You cannot perform arithmetical operations on binary numbers.

2. The decimal number system consists of 10 digits i.e., 0 to 9.

3. The method to perform division of two binary numbers is not the same as that of decimal numbers.

4. 1 multiplied by 0 equals to 0.

5. Charles Babbage introduced the concept of 0 (Zero).

6. The numbers used in Octal number system are 1 to 7.

A.

1.
Multiple-choice questions.
PS
...............................................................................

a. Ada Lovelace
introduced the concept of 0 (Zero).

b. Aryabhat
SECTION - B

c. Bill Gates

2. A ............................................................................... converts the decimal format into its binary equivalent.

a. Digital Computer b. Cell Phone c. Abacus

KI
3. A computer understands only............................................................................... code.

a. English b. French c. Binary

4. In Binary multiplication, 1x1 equals to ............................................................................... .

a. 0 b. 1 c. 2

5. To convert Decimal number into Binary number, divide the number by ............................................................................... .

a. 2 b. 8 c. 10

B. Answer the following questions.

1. What is a Number system? Name the different types of number system used.
.................................................................................................................................................................................................................................................................................................

.................................................................................................................................................................................................................................................................................................

................................................................................................................................................................................................................................................................................................

© Kips Learning Pvt. Ltd. 2020 14

2. What are the rules to convert a Decimal number into a Binary number?
.................................................................................................................................................................................................................................................................................................

.................................................................................................................................................................................................................................................................................................

3. Write the rules to multiply two Binary numbers.

.................................................................................................................................................................................................................................................................................................

.................................................................................................................................................................................................................................................................................................

...............................................................................................................................................................................................................................................................................................

4. Briefly explain the Octal number system.

.................................................................................................................................................................................................................................................................................................

.................................................................................................................................................................................................................................................................................................

................................................................................................................................................................................................................................................................................................

5. What do you understand by Hexadecimal Number System? PS

.................................................................................................................................................................................................................................................................................................

.................................................................................................................................................................................................................................................................................................

.................................................................................................................................................................................................................................................................................................

A CTi V iTY
SECTION
KI
LAB SESSION Perfection Through Practice

A. Convert the following Decimal numbers into Binary numbers.

a. 68 b. 987 c. 657

© Kips Learning Pvt. Ltd. 2020 15

B. Convert the following Binary numbers into Decimal numbers.
a. 1011 b. 100110 c. 10101

C. Perform Binary addition on the following.

a. 10101 + 00111 b. 1001101 + 1000101101 c. 1101 + 1001

D.
PS
Find the difference between the following Binary numbers.

a. 10011 - 01010 b. 11001001 - 01100110 c. 111 - 001

KI

E. Multiply the following Binary numbers.

a. 101 x 011 b. 1011 x 101 c. 101010 x 1011

© Kips Learning Pvt. Ltd. 2020 16

F. Divide these Binary numbers.
a. 1111 ¸ 11 b. 111001 ¸ 101 c. 111111111 ¸ 1011

GROUP DISCUSSION For Concept Clarity

Divide the class into two groups and discuss the topic.

PROJECT WORK
PS
Decimal Number System vs Binary Number System

Using Creativity

Make a presentation on Number System. Set a beautiful background. Apply nice

formatting and animation effects on it.
KI
ONLINE LINKS Looking For More

To learn more about Number System, visit the following websites.

† http://www.tutorialspoint.com/computer_fundamentals/computer_number_system.htm
† http://code.tutsplus.com/articles/number-system-an-introduction-to-binary-
hexadecimal-and-more--active-10848

© Kips Learning Pvt. Ltd. 2020 17

CHAPTER
2
USING EXCEL AS A DATABASE
LEARNING IN THIS CHAPTER
Using a Form to enter data Using Advanced Filter and Data Validation
Adding, Searching, and Deleting a record in a Form Adding Subtotal in Database
Sorting and Filtering data Analysing data with PivotTable

A database is a collection of information related to a particular object, such as maintaining addresses and phone
numbers, a list of library books, keeping students’ record pertaining to academic and co-curricular achievements,
keeping employees' information, etc. It helps the users to organise, retrieve, sort, and edit data as per their requirement.
Database consists of fields and records. In simple terms, records refer to rows and fields refer to columns in Excel.
FIELD : A column within a database that contains only similar type of data is called Field. For example, Roll No.,
Name, Address are different fields.

PS
RECORD : A row in a database is called a Record that consists of the
information about one person or one object. For example, in the given
figure the data – 101, Anju, 75, 82, 90, 247, 82.33 of one student, forms a Record
record.
FIELD NAME : It is a column label for the field in a database. All the
field names appear in one row. For example – Roll No, Name, English,
Field Name

Hindi, Math, Total, Per are the field names in the given database. Figure 2.1: Excel Database

USING A FORM TO ENTER DATA

KI
A Form is a window that contains numerous fields to enter, modify, and view one record at a time. Each field holds a
field name so that any user who views the form or enter data in it, gets an idea of its contents. Forms often contain
Command buttons and other controls that perform various tasks. When you enter information into a form, the data
gets saved directly in the database.
NOTE
Before adding a record in the form, the
worksheet must have field names at the top of
each column. Microsoft Excel uses these field
names as a label to ease the process of
entering records in a form. Make sure that
there are no blank lines in the range of data.

† In Excel 2016, the Forms option is hidden. To

add a Form button, select File > Options.
The Excel Options dialog box will open.
Figure 2.2: Excel Options Dialog Box
© Kips Learning Pvt. Ltd. 2020 18
† Select the Quick Access Toolbar option in the left pane. Know the Fact

† Click on the drop-down arrow of Choose commands from the list box. Rules To Enter Field
Names:
† Select Commands Not in the Ribbon option from the drop-down list and choose † Each Field name should
be placed in a separate
the Form command. cell.

† Click on the Add button ............... Click OK and you will find the Form button........on † Field names should be
unique.
the Quick Access Toolbar.

ADDING A NEW RECORD IN A FORM

Using the data form, we can add a new record in the database. Follow these steps to
enter new records:
Know the Fact
† Click on the cell A1. Select the Form button on
An easy way to add a
the Quick Access Toolbar. The Sheet1 dialog box custom style from another
appears. It contains the record of the first workbook is to copy the
cells formatted with that
student in your database. style and paste them into
†
†
†
PS
To add a new record, click on the New button.
Type the data in the respective fields of the Form.
After you have finished typing data, press Enter
key to add the row to the bottom of the range or
table.
a new workbook.

† You can move to the previous or next record by

clicking on the Find Prev and Find Next buttons
Figure 2.3: Adding a Record
respectively. Using Form
KI
† Click on the Close button to close the data form.

SEARCHING A RECORD
This option is used to search the records with specific
values.
† Click on the Form button. Quick Quiz

† Select the Criteria button in the Sheet1 dialog What is a Database?

box.
† Type the data that you want to search in the
appropriate field. For example, type 104 in Roll
No: text box and press the Enter key.
Let’s Discuss
† If the record exists and matches the given value
Importance of using Form.
for that field, it will be displayed. Otherwise, the
Figure 2.4: Searching a Record
first record will be displayed. Using Form

© Kips Learning Pvt. Ltd. 2020 19

 DELETING A RECORD
To delete a particular record:
† Click on the Form button. In Sheet1 dialog box, move to the desired record by clicking either on Find Prev
button or Find Next button.
† After selecting it, click on the Delete button. Excel prompts you to confirm the operation. You cannot undo a
row deletion after you confirm it.

SORTING DATA
Sorting means, arranging the data either in an ascending or descending order. In a worksheet, data can be sorted in
rows on the basis of text, numbers or dates. Once the data is organised, it becomes easy to work on it.

Follow these steps to sort the given data:

† Open any worksheet and select the cell range that you want to sort.

† Click on any cell, say B2. Now select the Sort button ........in the Sort & Filter group
in the Data tab. The Sort dialog box appears and the entire database gets selected.

†
PS
Click on the Sort by drop-down arrow and select the field on the basis of which you want to sort the data. For
example, select the Name field.

Select the Values option from the Sort On drop-down list. Select the sorting order from the Order drop-down
list. If Sort by contains alphanumeric data then A to Z option
gets selected automatically. In case, Sort by contains
numeric data then Smallest to Largest option gets selected.

† Select the My data has headers checkbox to exclude the

first row, containing the column headings for sorting.
KI
Figure 2.5: Sort Dialog Box
Otherwise, uncheck the checkbox to include the first row headings in sorting.

† Click OK. The database will be sorted in descending order on the basis of Name field.

In Excel, you can also sort more than one column. For example, after sorting by Name, you want to arrange the list by
Roll No.

To sort by more than one column, follow the given steps.

† Click on the Add Level button in the Sort dialog box. A new
level gets added below the first level.

† Select the column name as Roll No in the Then by

drop-down list and order of sorting as Largest to Smallest in
the Order drop-down list in the new level. Click OK.
Figure 2.6: Adding Level

Excel 2013
In Excel 2013, also you can add, search, delete records using forms in the same way as you do in Excel 2016.

© Kips Learning Pvt. Ltd. 2020 20

 FILTERING DATA Let’s Know More
The Filter feature is one of the interesting features of Excel which helps you to display Remove Duplicates button
only those records that meet the specified criteria and hide rest of the records. under Data tab is used to
remove duplicate values
† Select any cell within the database range, say D1. Click on the Filter button in the from one or more
Sort & Filter group in the Data tab. columns.

† Small arrows will be added in the

column header. These arrows are
used to specify conditions to filter
the data.
† Click on the arrow next to the Hindi
column header in cell D1. Uncheck
the Select All check box to deselect
all the check boxes. Now select the
check boxes of the values 82 and 98
from the drop-down list. Click OK.
†
PS
You will notice that the list gets
filtered and displays only those
records that meet the specified
criteria, i.e., records related to marks
82 and 98 in Hindi.
Figure 2.7: Using Filter Command
Let’s Know More
Conditional Formatting is
the feature in Excel that
sets a cell's format
according to conditions
that you specify. Using this
option, you can change
To clear all filters from the worksheet, click on the Clear button present in the the font colour, styling,
etc., of the data in the
Tips
Sort & Filter group on the Data tab or click on the Filter button in the Data tab. If you selected cells. Select
have modified the data and want to filter the new data, click on Reapply button . Home > Conditional
KI
Formatting in Styles group
and specify the condition
USING ADVANCED FILTER as needed.
Advanced Filter option is used to filter the data in multiple fields using specified criteria,
to copy the filtered records to a different location, or to find unique records. Let us use
this option.
† Insert at least three blank rows above
the range, which is to be used as a
criteria range, by clicking on the Home
tab and selecting the Insert > Insert
Sheet Rows option.
† The criteria range must have column
labels. There should not be any blank
row between the criteria values and
Let’s Discuss
the range.
Advantages of Filters
† Select the range A4:D4. Press Ctrl+C to
copy the range. Figure 2.8: Using Advanced Filter
© Kips Learning Pvt. Ltd. 2020 21
† Select the cell A1 and press Ctrl+V. Type the criteria which you want
to match in the row below the column labels. Type = 10000 under the
field name Salary i.e., D2.

† Click on a cell within the data range. Select Data tab and click on the
Advanced button in the Sort & Filter group.

† You will notice the range is already defined in the List range:
$A$4:$D$13.

† Select Copy to another location radio button in the Action section of

Advanced Filter dialog box.

† Define the Criteria range box, by entering the reference of the

criteria range, including the field names.
Figure 2.9: Advanced Filter
Dialog Box
† Define the range of the target location where the
filtered rows will be copied in the Copy to box,

NOTE
PS
Figure 2.10: Result of Advanced Filter
i.e., $F$6:$I$6. Click OK. The data will get filtered
according to the specified condition and be
copied in the place, which you have defined in the
Copy to box.

In Microsoft Excel, you can also filter and sort the data by cell colour.

USING DATA VALIDATION

Data Validation is used to restrict the type of data or value that users enter a cell. Let us use data validation to restrict
KI
the data entry to whole numbers between 0 and 100.
† Select the cell range E5:E13. Choose Data tab > Data Validation ..... in the Data Tools group. The Data Validation
dialog box appears. The Settings tab is selected by default.
† In Allow drop-down list, select the Whole number option.
† In Data drop-down list, between option is selected by default.
† In Minimum: text box, type 0, and in Maximum text box, type 100.
† Click on the Input Message tab. In Title: text box, type
‘Validation’. In Input message box, type ‘enter between 0 and
100'.

† Select the Error Alert tab and type ‘Input is wrong’ in Error
message box. Click on OK to close the dialog box.
† If you try to enter data beyond the specified limit in the selected
range, an error message will be displayed. Click on Retry to enter
another value in the cell. Figure 2.11: Data Validation Dialog Box

© Kips Learning Pvt. Ltd. 2020 22

 ADDING SUBTOTAL IN DATABASE
The Subtotal feature in a database helps us to manage, analyse, and
extract specific information from rows of related data. To apply
subtotals, the database must be sorted. Let us learn the use of this
feature through the given table. Follow these steps:
† Open a new worksheet and enter the data as shown in the Figure 2.12.
† Select any cell within the range, and
Figure 2.12: Data for Subtotal
choose Data tab > Subtotal option in
the Outline group. The Subtotal dialog box appears. Choose 'Month' from At
each change in: drop-down list.
† To calculate the sum of Sales, select Sum function from Use function: drop-down
list.
† In Add subtotal to: list box, select the Sales checkbox.
† Deselect the Replace current subtotals option by clicking on its checkbox. This

PS
Figure 2.13: Subtotal Dialog Box
option overwrites the existing
subtotal if present.

† The Summary below data checkbox is marked by default. This

option will place the total below the data of each month.
† Click OK. The subtotals will be displayed as shown in the Figure 2.14.
Figure 2.14: Result of Subtotal
KI
NOTE
To remove all the subtotals in your worksheet, click on any cell within the range and select Data tab > Subtotal option.
In the dialog box, select the Remove All button.

ANALYSING DATA WITH PIVOTTABLE

PivotTable is a powerful tool for consolidating, summarising, and presenting the data. Follow these steps to create
PivotTable:
† Enter the given data in a new worksheet and select the entire
data range.
† Choose the Insert tab > PivotTable option in the Tables group.
† The Create PivotTable dialog box appears.
† In the Table/Range text box, the range that you have selected
for the PivotTable is displayed .
† The New Worksheet radio button is selected by default.
Figure 2.15: Data for PivotTable
† Click OK. The Create PivotTable dialog box closes.
© Kips Learning Pvt. Ltd. 2020 23
† The PivotTable layout is displayed on the new worksheet. The
PivotTable Fields task pane appears on the right side of the screen.
† Click and drag the Name field and drop it into the Rows quadrant as
shown in Figure 2.17.
† Drag the Designation field into the Columns quadrant.
† Drag the Salary field into the Values quadrant. Your worksheet
should look similar to Figure 2.18.
† You can re-arrange the data in any way you like. You can also change
the place of buttons here. If you do not like their placement, click the
Undo button. Figure 2.16: Create PivotTable Dialog Box

NOTE
By default, non-numeric fields are added to the Rows quadrant, date and time hierarchies are added to the Columns
quadrant, and numeric fields are added to the Values quadrant.

PS
KI

Figure 2.17: PivotTable Figure 2.18: PivotTable Layout

Fields List

Excel 2013
You can use all the features, like Sorting, Filtering, Validation, PivotTable, etc., in Excel 2013 also
in the same way as explained above.

© Kips Learning Pvt. Ltd. 2020 24

 ECAP
† A database is a collection of information related to a particular subject.
† A column within a database that contains only one type of data is called Field.
† A row in a database is called a record, which consists of the information about one person or one object.
† Field name is a column label for the field in a database.
† A Form is a window or screen that contains numerous fields or spaces to enter, modify, and view one record at a
time.
† Sorting means arranging the data either in an ascending or descending order.
† The Filter feature is one of the interesting features of Excel which helps you to display only those records that
meet the specified criteria and hide rest of the records.
† Advanced Filter option is used to filter the data in multiple fields using specified criteria, to copy the filtered
records to a different location, or to find unique records.
† Data Validation is used to restrict the type of data or the values that users’ enter into a cell.

related data. PS
† The Subtotal feature in a database helps us to manage, analyse, and extract specific information from rows of

† PivotTable is a powerful tool for consolidating, summarising, and presenting the data.

BRAIN
DEVELOPER
KI
SECTION - A
A. Fill in the blanks.

1. A database helps the user to ............................................................................, retrieve, sort and edit data as needed.

2. A ............................................................................ window or a screen that contains numerous fields or spaces to enter, modify,
and view one record at a time.

3. ............................................................................ is a column label for the field in a database.

4. To restrict the type of data and the values while entering data in a cell, use ............................................................................ option.

5. To search for a record with specific value, click on the ............................................................................ button in a form.

6. ............................................................................ provides an easy way to present the data in a summarised way.

HINTS PivotTable Organise Form Field Name Criteria Data Validation

© Kips Learning Pvt. Ltd. 2020 25

B. State True or False.
1. Records refer to the rows in the database.

2. Field names can be duplicated in the database.

3. You can sort the data only on one field.

4. Field represents a column in the database.

5. Subtotal values in the database are calculated with Count function.

6. In a database, one cannot rearrange the data as per the need.

7. Conditional Formatting sets a cells format according to the condition that you specify.

C. Application-based questions.

1. Kanika is a Chartered Accountant. She looks after the accounts of various companies and keeps their records in
Microsoft Excel. She does not have any idea on how to view only those records that she wants to see. Suggest

2.
PS
the feature of Microsoft Excel, using which she can perform this task.

.................................................................................................................................................................................................................................................................................................

Varun is maintaining the marksheet of his class in Microsoft Excel. The teacher has asked him to restrict the
marks enteries between 1 and 100 in all the subjects. Which feature of Microsoft Excel should Varun use in
order to accomplish the task?

.................................................................................................................................................................................................................................................................................................

SECTION - B
KI
A. Multiple-choice questions.
1. Which among the following features is used to filter the data in multiple fields using a specified criteria?
a. Advanced Filter b. Criteria c. Sorting

2. Which button under Data tab is used to remove a duplicate value from one or more columns?
a. Remove b. Remove Data c. Remove Duplicates

3. Which feature in a database provides an easy way to enter, modify, and view one record at a time?
a. Form b. Adding Subtotal c. Filter

4. Which feature helps you to display only those records that meet the specified criteria?
a. Data Validation b. Sort c. Filter

5. Which feature allows us to arrange the given data according to a particular field either in an ascending or
descending order?
a. Data Form b. Filter c. Sort

© Kips Learning Pvt. Ltd. 2020 26

B. Answer the following questions.
1. Describe the term Database.

.................................................................................................................................................................................................................................................................................................

.................................................................................................................................................................................................................................................................................................

.................................................................................................................................................................................................................................................................................................

2. What is the utility of a Form in a database?

.................................................................................................................................................................................................................................................................................................

.................................................................................................................................................................................................................................................................................................

................................................................................................................................................................................................................................................................................................

3. What is the use of the Sorting feature in Excel?

.................................................................................................................................................................................................................................................................................................

4.
PS
.................................................................................................................................................................................................................................................................................................

How is Filter useful?

................................................................................................................................................................................................................................................................................................

................................................................................................................................................................................................................................................................................................

................................................................................................................................................................................................................................................................................................

5. What is a PivotTable? Describe its utility.

KI
................................................................................................................................................................................................................................................................................................

................................................................................................................................................................................................................................................................................................

.................................................................................................................................................................................................................................................................................................

6. How is Advanced Filter different from Filter?

................................................................................................................................................................................................................................................................................................

................................................................................................................................................................................................................................................................................................

................................................................................................................................................................................................................................................................................................

................................................................................................................................................................................................................................................................................................

7. State the difference between a Field and Record.

................................................................................................................................................................................................................................................................................................

................................................................................................................................................................................................................................................................................................

................................................................................................................................................................................................................................................................................................

© Kips Learning Pvt. Ltd. 2020 27

 A CTi V iTY
SECTION

LAB SESSION Perfection Through Practice

† Open Excel 2016 and type the data as given in the worksheet.
† Save the worksheet using Ctrl+S key combination.
† Click on any cell. Click on the Form button on the Quick Access Toolbar. Select the New button.
† Type the information - 1011, S. R. Electronics, Retailer, Television, 12, 240000 in the respective fields.
† Click on the Close button. Record will be saved and displayed at the end of the worksheet.
† Click on the Form button on
the Quick Access Toolbar. A B C D E F

Now, click on the Criteria 1 Circuit Mall Sales

†
PS
button. Type 'Television' in
Product text box. Press the
Enter key. It will display the
record that matches with the
criteria.
Click on the Find Next button.
2
3
4
5
6
7
8
9
10
Invoice No.
1001
1002
1003
1004
1005
1006
1007
Customer
R.S. Malik & Co.
R.S. Malik & Co.
Gupta Electronics
Ramsons
Ramsons
Malhotra Cottage
Batra Electronics
Type
Whole saler
Whole saler
Retailer
Retailer
Retailer
Retailer
Whole saler
Product
Television
Washing Machine
Mixer
Television
Mixer
Washing Machine
Washing Machine
Qty
35
25
15
10
15
12
50
Price
700000
250000
127500
200000
125000
120000
500000
11 1008 SiaRams Cottage Retailer Mixer 10 85000
The next entry related to
12 1009 Shine Electronics Whole saler Washing Machine 30 300000
'Television' criteria will be 13 1010 Shine Electronics Whole saler Television 60 1200000
displayed. Click on the Close
KI
button.
† Click any cell. Select Data > Sort option. In Sort by list box, select 'Customer' field. Select Z to A option from
Order list box. Click OK and observe the change.
† Click the Data > Filter option. Small arrows will be added to each field name.
† Click the drop-down arrow of 'Product' field name, and uncheck the Select All checkbox to deselect all the
options. Now select 'Washing Machine' from the displayed list.
† Click OK. The list will get filtered and display the records of the product - 'Washing Machine'.
† Now remove all filters.
† Click on the cell address A2. Insert 4 blanks rows.
† Select and copy the cells A7:F7. Click on the cell A2 and paste the copied cells.
† Type ‘Washing Machine’ under the ‘Product’ field name.
† Click on any cell in Data range, i.e., from A7:F8. Select the Data tab and click on the Advanced button in the
Sort & Filter group. Select 'Copy to another location' in Advanced Filter dialog box.
† Specify the List range as $A$7:$F$18, Criteria range as $A$2:$F$3 and Copy to as $I$7:$N$7. Click OK.

© Kips Learning Pvt. Ltd. 2020 28

† The data matching the criteria will be displayed in the output range.
† Click the Data > Subtotal option. Select 'Product' from At each change in list box.
† Select 'Sum' from Use function list box. Click on the 'Price' check box in the Add subtotal to section. Click OK.
Again select SubTotal option and click on Remove All button.
† Select any cell, let us say A10. Click Insert > PivotTable and follow the steps given in the chapter to proceed
further.
† Save the file by pressing Ctrl + S key combination.

GROUP DISCUSSION For Concept Clarity

Discuss on the topic: How is Sorting different from Filtering data?

PROJECT WORK Using Creativity

S.No.
PS
Shikha is a Public Relations Manager in a company. She has prepared a list of her clients along
with their birthdays. She now wants to find out the names of persons, whose birthday falls in
the month of September. Help her to complete this task using Advanced Filter command.
Client's Name Client's Designation Date of Birth

ONLINE LINKS Looking For More

KI
To know more about Microsoft Excel 2016, visit the following websites:
† www.gcflearnfree.org/excel2016/groups-and-subtotals/2/
† www.groovypost.com/howto/create-pivot-tables-microsoft-excel-2016/

© Kips Learning Pvt. Ltd. 2020 29