Click Start 7 [PDF]

Zitiervorschau

Click Star t 7

C

am

br

id

ge

U

ni

ve rs

ity

Pr e

ss

Computer Science for Schools

Revised Edition

Anjna Virmani Shalini Harisukh

University Printing House, Cambridge CB2 8BS, United Kingdom One Liberty Plaza, 20th Floor, New York, NY 10006, USA 477 Williamstown Road, Port Melbourne, VIC 3207, Australia 314–321, 3rd Floor, Plot 3, Splendor Forum, Jasola District Centre, New Delhi – 110025, India 79 Anson Road, #06–04/06, Singapore 079906 Cambridge University Press is part of the University of Cambridge. It furthers the University’s mission by disseminating knowledge in the pursuit of education, learning and research at the highest international levels of excellence. www.cambridge.org Information on this title: www.cambridge.org/9781108855693 © Cambridge University Press 2011, 2014, 2020

20 19 18 17 16 15 14 13 12 11 10 9 8 7 6 5 4 3 2 1

Pr e

ve rs

Printed in India by Rajkamal Electric Press

ity

First published 2011 Second edition 2014 Third edition 2020

ss

This publication is in copyright. Subject to statutory exception and to the provisions of relevant collective licensing agreements, no reproduction of any part may take place without the written permission of Cambridge University Press.

ISBN 978-1-108-85569-3

ge

U

ni

Cambridge University Press has no responsibility for the persistence or accuracy of URLs for external or third-party internet websites referred to in this publication, and does not guarantee that any content on such websites is, or will remain, accurate or appropriate. Information regarding prices, travel timetables, and other factual information given in this work is correct at the time of first printing but Cambridge University Press does not guarantee the accuracy of such information thereafter.

id

notice to teachers

am

br

The photocopy masters in this publication may be photocopied or distributed [electronically] free of charge for classroom use within the school or institution that purchased the publication. Worksheets and copies of them remain in the copyright of Cambridge University Press, and such copies may not be distributed or used in any way outside the purchasing institution.

C

Every effort has been made to trace the owners of copyright material included in this book. The publishers would be grateful for any omissions brought to their notice for acknowledgement in future editions of the book.

Introduction The revised edition of Click Start: Computer Science for Schools is designed around the latest developments in the field of computer science, information and communication technology. Based on Windows 7 and MS Office 2010, with extensive updates on Windows 10 and MS Office 2016, the series aids the understanding of the essentials of computer science including computer basics, office applications, creative software, programming concepts and programming languages.

ve rs

ity

Pr e

ss

Each level of the series has been designed keeping in mind the mental aptitude and learning ability of the learners as well as their interests. Efforts have been made to use examples from day-to-day life, which will help the learners to bridge the gap between the knowledge of the subject and the real world. The books are designed to offer a holistic approach and help in the overall development of the learners.

ni

KEY FEATURES

Snap Recap: Probing questions to begin a chapter and assess pre-knowledge

•

Learning Objectives: List of the learning outcomes of the chapter

•

Activity: Interactive exercise after every major topic to reinforce analytical skills and application-based learning

•

Exercise: A variety of questions to test conceptual clarity

•

Fact File: Interesting concept-related snippets to augment concept knowledge

•

Quick Key and Try This: Shortcuts and useful tips on options available for different operations

•

Glossary: Chapter-end list of important terms along with their definitions

•

You Are Here: Summary to aid quick recapitulation

•

Lab Work: Practical exercises to enable application of concepts through learning-by-doing

•

Project Work: Situational tasks to test practical application of the concepts learnt

•

Who Am I?: Biographies to inspire young learners

•

Sample Paper: Helps in preparing for cyber concepts and knowledge competition

•

Poster: Informative infographics to help students stay safe in connected digital environment

C

am

br

id

ge

U

•

The books, thus, will not only make learning fun but also help the learners achieve a certain level of expertise in this fast changing world of computer science.

Overview

Netwo

Communi and Mob

There can be various cha

nnels for wireless transmi

ssion. A few are explain ed here: es are generally used for long distance wireless communication. Signals can pass through thick obje cts such as non-metalllic substances. Radio waves are used in radio commun ication (AM/FM), commun satellites, radars, compute ication r networks, etc.

Radio Waves: Radio wav

SNAP RECAP

Snap Recap Infrared: Infrared is one Probing questions to of the most primitive form s of wireless communicat an electromagnetic radiatio ion. It is n. It is basically suitable for short dist For example, a television begin chapter and ance comamun ication. and a remote control con nect to each other using rays. The signals travel in infrared a straight line and cannot penetrate wal assess pre-knowledge ls. Microwave: Microwave

LEARNI

Network

1. What is networking? 2. What are the different types of networks? 3. What is an operating system? Why do you need it? 4. Discuss various types of operating system available in the mar ket. 5. State the various uses of a mobile phone.

You will learn ab • computer netw components • types of netwo • network topol • types of transm • mobile operati

Communication and Mobile OS

ss

is the most commonly use d wireless transmission which is spread widely acro medium, ss the world. Most of the wireless access points and wireless devices work on microwave communicat ion. This communication med not only cost effective, it ium is is also extensively used in cellular communication. Bluetooth: Bluetooth is SNA P RECAP another wireless commun ication medium operating over sho rt distances. It has a normal 1. What is networking? range of approximately 3–300 feet depending on the connec 2. What are the different ted devices. These devices can be mob types of ile phones, personal com networks? puters, laptops, digital camera, MP3 play ers, etc. Bluetooth enabled 3. What is an operating devices form a small network. In Bluetoo system? Why do th technology, eight dev you need it? ices can be connected to each other at the same time. Bluetoo A4. Blue toot h devi ce Disc th uss various types of oper can also be found in headsets, hands-f ating ree kits, wireless keyboa em avai rds and mouse. Bluetoosyst radio wave technology whi th uses thelable in the market. ch is not very expensive 5. e the and has low power conStat ous uses of a mobile pho sumptiovari ne. n.

Computer Network

Pr e

A netwLEA ork RN JECorTIV conING sists OB ESe compute of two mor rs linked together for sharing resources such as You will printers lear about: andnexch anging files, or allowing electronic •com com pute r netw mun icat ions.ork and its puters on a netw The com ork may be linkcom ed pon throents ugh cables, telephone line s, radio waves, •satetype llitess,ofor netw infrork ared light beams. •A network topology and large number of usersitsbelo types nging to the same area •or orga typesnisa of tran tionsmis whosion arechan sprenels ad out at different places Compu •canmob ilemun opericat com atinegand systshar emseand itsultype usef s , software prog files rams and i their computers are netw orked. This type of netw orking specific t organisation is called the Intranet. It is a network which is not op

ve rs

ity

Learning Objectives List of the learning outcomes of the chapter

Mobile Operating Sy stem

am

main computer, that ver. This eliminates the time and energy in ating and tracking files t computers throughout ing.

FA10 CT FILE

Extranet is another form of a network. It is private to an organisati on with little external interference and is used for official purposes onl y. That is, it is any Intranet that also uses the Internet.

C

d Software nt: Software can be

haring: Resources such as

printers, fax machines, sca nners and e shared by connecting the m on a network. This sav es space of the s also economical. can be sent and received rapidly using a computer network. This me, and is more convenien t thanFile manual delivery of data. Fact nt: Individually licenced cop ies of many popular softwa re programs toring the software on aInteresting file server and maconcept-related king it available to other ected to it saves money. sitive files and programssnippets to augment on a network are protected by passwords. de available as read only files, which knowledge concept helps to avoid copying of

of a Computer Network

U

ge

id

br

Activity Interactive exercises after every major topic to reinforce analytical skills and application-based learning

ni

Initially, when mobile pho nes were introduced in the late 90s Computer Network , they were just designed to support dial ing and receiving A network consists of two Y IT V TI calls and sending simple or more computers AC text messages. linked together for sharing Usage of mobile phones resources such as has increased printers and exchanging files, or allowing electron Make a list of networking drastically over the last few ic years, which communications. The com 6. Fourth generation languag devices available in you puters on a network may has brought a huge revo is a programming environ ment designed with a specific r ed through purpose be of development ofecomme lution in the link rcial business software. cables, telephone lines, radi computer lab. 7. Fifth generation languag features of mobile handse es are used mainly in artificia o wav8.es, l intelligence research. 4. Add the informsate ts. llite Types of s, languag or e translators are assembler, infrared light beams. compiler and interpreter. ati on sho wn here Nowadays mobile phones A large number of users to the new row. are just like a compact com belonging to the same area puter Fa applications. These mob load ed with tim a bas ic or orga ile nisa app tion lica 6 are spread out at diff tion who s can be designed and uplo of a Computer Network establishment of connec erenA t placEXER aded easi es CISE Computers linked in a network canly. Wir com eles tions can be achieved thro mun s icat e and share useful files, ugh Bluetooth and infr technologies. Mobile pho softwareA. prog State true or false. ram ared thei s and information only whe omputers is a basic need r Ch com oo pute nes se having such features are rs are networked. This type Insert Below 1. A computer requires n of today’s world. Let us referred to the option in the Roofws networki to perform any task. as ng specificinstruc orga rtpnisa study some hontion to tions es. is called only mputer networking. users of an the Intranet. It is a netw & 2.Co lum RAD stands ns Layout tabsma Rapid Accessgro up Development. of the ork which isfornot to insert a row below an ope 3. BASIC supports an assemb n to all. exi

ntages of a computer net work are: develops a fault, users ma y not be able to run the app lication ances of data loss increa se. rk stops operating, comput ers connected to the netwo rk cannot es stored on the network, thus affecting work of the entire system. s on a network the perfor mance degrades unless it is designed lt to manage when a larg e number of computers is connected to a

sting row.

ler as a translator. 4. The Interpreter conver ts an assembly language into a machine language.

Adding a New Colum n

Exercise You can add new colum in the table. variety ofnsquestions 1. Place A the cursor anywhere in the last column. clarity 2. Click to on test the Paconceptual ge La yout tab

option. 3. Label the new colum n Hobby. 4. Add text in the new column which

B.

C.

5. First generation langua ges use machine langua ge. Differentiate between the following: 1. Assembler and Compil er 2. Compiler and Interpr 3. Source code and Object eter code 4. Procedural programming and Object oriented progra mming Match the following.

1

1. First generation langua

ge

2. Second generation langua ge

Rows & Columns group 3. Third generation langua

ge

4. Fourth generation langua

ge

5. Fifth generation langua

ge

is now a part of the table. Choose the Insert Left option to insertQuick TRY THIS the new col umn to the left of Key the selected cell. Select

and Try This

Deleting aShortcuts Row

and You can delete useful rows tips from on a table. To delete any row: options available for 1. Place the cursor any where in the row deleted. different operations to be

a. BASIC b. Artificial language

Insert Right

c. Assembly language d. Machine language e. SQL

67

the column or cell to be deleted. Right click on the mouse and try using the Delete Columns or Delete Cells… option in the shortcut menu.

2. Select the Layout tab Rows & Columns group Delete Rows option.

Deleting a Column You can delete columns from the table. To delete any column: 1. Place the cursor any whe

Delete drop-down list

7. The Import Data dialog box appears. Specify the Absolute Cell Reference if the data has to be importe address d in the current worksheet or in a new worksheet of same workbook. the 8. Click on the OK button. The data will be importe d in the current worksheet from A1 (Fig. 3.22). starting

Fig. 3.22 Data imported

C.

State the differences between:GLOSSARY

Glossary Chapter-end list of important terms along with their definitions

1. LAN and MAN Absolute reference It2. Star and Bus topology to the actual addressOS 3. Desktop and Mobile OSCell 4.refers Android and iPhone of a cell in a specific location reference It identifies . the location of a cell or group 5. Client/server and Peer-to-peer of cells in the Chart architecture

D.

Match the following.

1. Android 2. webOS

3. Smartphones 4. iOS

5. Mobile apps

E.

spreadsheet. It is a graphical represen tation of information. Filtering It is the process of selecting records from a large amount of data based conditions. on certain a. Apple products Mixed reference It is a cell address which has absolute column and a relative b. Small software units functions absolute row or rowwith and alimited relative column. Relative reference It is the c. Linux based OS address of a cell which is based on the relative position of the cell contained in the formula . Sorting It is the physica d. Compact computers l rearrangement of data in ascending or descending order.

e. Prone to malware threat

Answer the following56 questions.

1. What is networking? State its advantages and disadvantages. 2. What are the different components of a computer network? Explain them briefly. 3. What is a topology? Explain the different types of topologies used in computer networks. 4. What is Mobile OS? Give few examples. 5. Give two reasons for developing a mobile app.

C.

GLOSbetween SARY Differentiate the following.

LAB WORK

1. Relative and Absolute Reference 2. Filter and Advanced Filter Loop or Iteratio n It is the repetition of a statement(s) in a program 3. Ascending and Descending Orders 4. Auto Filter. and Custom Filter 5. Chart Area and Plot Area FOR ... NEXT structure

ARE

You Are Here Summary to aid quick recapitulation

is used when you

want to perform a loop for HERE number What is a cell specific reference? Explain types variable of cell references with examples. a of times.all It three uses a counter which is incremented or decremented with each repetitio n of the is loop. What is sorting? How many types of sorting possible? 2. A DO WHILE ... LOOP is perform ed as long as the conditio is true. What are charts in MS Excel 2010? Give one advantage of using charts. n being tested The DOexamples ... UNTIL LOOP Give some3.real-life where chartst from can DO be used. is differen ... WHILE as it executes statements until the conditio the n isMS true.Excel 2010. the steps for importing data in 4.Write The WHILE ... WEND

6

is termed as entry controll ed loop and is executed loop condition is true. If only when the the loop condition is false in the beginning itself, the may not execute even once. loop body 5. The DO WHILE ... LOOP is termed as exit controll ed loop and is executed once because the conditio minimum n is tested after the loop body has been executed. condition is false the loop If the terminates else the next LAB WORK iteration is performed. 6. To come out of a loop before the expected number of execution, EXIT comman followed by IF. d is used

16

ity

Make an MS Excel 2010 spreadsheet on the average amount of water that each member of your family uses. Analyse and compare your family’s water usage to that of others in the class, and suggest three ways to save water. Measure the usage of water by your family members under the following categories. EXERCISE 1. Bath 2. Shower 3. Brushing teeth 4. and face 5. Washing dishes 6. Toilet flushes A. Washing State truehands or false. 1. Iteration 7. Drinking water 8. Washing clothes 9. Cooking food is a repetition of stateme nts in a program. 10. carof the following tags: C. Washing Give 2. Stepuses is used to repeat certain steps a fixed number of times. 1. 2. 3. 4. 3. StartValue is the initial value of the counter_variab le in FOR … NEXT loop. the following questions. Answer

ve rs

D.

1. What are forms? How do you create forms in HTML? 2. What is the purpose of the tag? Explain. PROJECT WORK 3. Give the attribute listing of the tag. 4. MS What are 2010 different types oftobuttons available HTML?conducted unit tests/ Create an Excel spreadsheet record the data of in recently

Project Work Situational tasks to test practical application of the concepts learnt

82

ni

exams for your class. Enter the following data: Student ID, Name, Class, Section and Marks of all the subjects. Find the total marks and percentage for each student. Using conditional formatting, highlight the marks below 40 with red fill and the percentages above 90 with yellow fill. Now apply Filter to extract the details of all the students who LAB WORK have got more than 75% marks in this examination.

ge

PROJECT WORK

id

Design a form to: • enter route details

Lab Work Practical exercises to enable application of concepts through learning-by-doing

U

A. Design a form to organise inter-school cultural activity. B. Imagine yourself as the owner of an exotic restaurant. Create a form to take feedback about the food menu of your restaurant.

• bus number along with parental information

157

Tick ( ) the correct option .

Paper

1.

Identify the incorrect sentence. a. Server is a computer that manages the networ k resources, software and b. Data is exchanged betwee files. n two computers in a networ k using transmission channels. c. Peer-to-peer architec ture is a type of networ k in which each worksta equivalent capabilities tion has and responsibilities. d. In bus topology, failures in the central hub will break down the entire networ k. 2. Select the examp les of wireless commu nication. i. Bluetooth ii. Cell phones iii. Fibre optics iv. Radio waves a. i. and ii. b. i., ii. and iii. c. ii. and iii. d. i., ii. and iv. 3. Choose the wrong pairing from the given options. a. WebOS – Linux based b. Gingerbread – Android c. Blackberry – iOS d. Microsoft – Window s phone 4. Which of the followi ng option(s) from Insert tab, you can use for creatin in MS Word 2010? g tables i. Insert Table grid ii. Insert Table option iii. Draw Table option a. Only i. b. Only ii. c. Only iii. d. All of these

RMISSIO PE

N

Who Am I? Biographies to inspire young learners

Sample Paper Helps in preparing for cyber concepts and knowledge competition

am

WHO AM I? I was born in Chicago Illinois on December 26, 1928. I earned my Bachelors and Masters degree in Electrical Engineering from the Illinois Institute of Technology in 1950 and 1957 respectively. I headed the team that invented the technology responsible for the cell phone when I was the Director of Research and Development at Motorola. I am also known as the first person to make a call on a cell phone. I am ............................................................... .

Sample

br

Link these two web pages with the homepage of your class created in the previous chapter.

C

58

ss

Answer the following questions. 1. The YOU 1. 2. 3. 4. 5.

Pr e

D.

A. Find out the topology of the network used in your school’s computer lab. Find out the main reason for implementing that specific topology in the lab. Write an article in MS Word 2010 stating the definition, advantages and disadvantages of using that specific topology. Insert a few relevant pictures using the Internet. B. How do cell phones work? Try to find out the technology used and the wireless transmission channel used in the cell phone technology. Take help of the Internet to research on this topic. C. Make a presentation on different types of Mobile OS. Take help of the Internet to get information and insert pictures wherever possible. D. These days we can buy and sell products like clothes, cars, furniture, medicines, cosmetics and lot more through mobile apps. Make a document of any five most commonly used apps for buying and selling products these days. Also, name a few websites associated with these specific apps listed by you.

Make sure you have permission to view and share information online.

POSITIVE Make sure the information you share is positive.

PROFILE Do not give out any personal information.

© Cambridge University

Press 2011, 2014, 2020

181

PROTECT In an incident of cyber bullying, don’t bully back. Save evidence and seek help.

VA PR I C Y Keep your passwords and personal details private.

Poster Informative infographics to help students stay safe in connected digital environment

C ntents 01 Network Communication and Mobile OS ................... 1 ° Types of transmission channels ° Mobile OS and its types

Introduction HTML HTML editor HTML tags Structure of an HTML document ° Some important points ° HTML document heading ° HTML paragraph tag ° ° ° ° °

02 Creating Tables in MS Word 2010 .............................. 18 09

10

ni

° Important terms and definitions

U

° Introduction ° Generations of programming languages ° Computer language translators

ge

05 More on Scratch ........................................................... 69 ° Introduction ° Pen block

11

br

id

° Looping blocks in the Control block

am

C

° DO UNTIL … LOOP ° WHILE … WEND ° EXIT

07 QB64 – Graphics and Sound ...................................... 85 ° Introduction ° Graphics

° Sound

12

° Inserting tables ° Changing text type, colour and size ° How to adjust page margins

Creating Forms in HTML ......................................... 142 ° Introduction ° Form tag ° Interface elements used inside tag ° tag

° tag ° tag ° Designing a complete form

Flash – Tweening and Publishing ............................ 158 ° Introduction ° Tweening in Flash ° Creating and integrating scenes into a Flash movie

06 QB64 – Programming Statements II .......................... 76 ° Introduction ° FOR … NEXT ° DO WHILE … LOOP

Introduction Creating lists Unordered lists Ordered lists Description lists Nested lists

ve rs

04 Introduction to Programming Concepts .................... 60

Elementary HTML .................................................... 123 ° ° ° ° ° °

° Conditional formatting ° Creating charts ° Importing data

Pr e

03 MS Excel 2010 Advanced Features............................. 37 ° Cell reference ° Sorting data ° Filtering list

No break tag Formatting tags Underline tag Center tag Horizontal rule tag

° Comment tag

° Inserting images ° Hyperlinks ° ° ° ° °

ss

° Table styles ° Applying borders and shading

° Introduction ° Creating tables ° How to modify tables

Introduction to HTML ................................................ 95

ity

° Introduction ° Basic components of a network ° Types of network ° Network topology

08

° Publishing Flash movies

World Wide Web ........................................................ 170 ° ° ° °

Introduction Protocols on the Web Components of the Web Working of WWW

° Web hosting ° Uses of World Wide Web ° Some other uses of the Web

Sample Paper ............................................................. 181

Network

ss

Communication and Mobile OS LEARNING OBJECTIVES

ve rs

ity

You will learn about: • computer network and its components • types of network • network topology and its types • types of transmission channels • mobile operating systems and its types

br

id

ge

U

ni

1. What is networking? 2. What are the different types of networks? 3. What is an operating system? Why do you need it? 4. Discuss various types of operating system available in the market. 5. State the various uses of a mobile phone.

Pr e

SNAP RECAP

am

Computer Network

C

A network consists of two or more computers linked together for sharing resources such as printers and exchanging files, or allowing electronic communications. The computers on a network may be linked through cables, telephone lines, radio waves, satellites, or infrared light beams. A large number of users belonging to the same area Computers linked in a network or organisation who are spread out at different places can communicate and share useful files, software programs and information only when their computers are networked. This type of networking specific to only users of an organisation is called the Intranet. It is a network which is not open to all.

1

Advantages of a Computer Network

id

ge

U

ni

ve rs

ity

Pr e

ss

Networking of computers is a basic need of today’s world. Let us study some advantages of computer networking. FACT FILE 1. Centralised Software Management: Software can be Extranet is another form of a network. loaded on the main computer, that It is private to an organisation with little is, the file server. This eliminates the external interference and is used need to spend time and energy in for official purposes only. installing, updating and tracking files That is, it is any Intranet that on independent computers throughout also uses the Internet. the same building. 2. Resource Sharing: Resources such as printers, fax machines, scanners and modems can be shared by connecting them on a network. This saves space of the work area and is also economical. 3. Speed: Files can be sent and received rapidly using a computer network. This method saves time, and is more convenient than manual delivery of data. 4. Cost Efficient: Individually licenced copies of many popular software programs can be costly. Storing the software on a file server and making it available to other computers connected to it saves money. 5. Security: Sensitive files and programs on a network are protected by passwords. They can be made available as read only files, which helps to avoid copying of programs.

br

Disadvantages of a Computer Network

C

am

Some of the disadvantages of a computer network are: 1. In case the server develops a fault, users may not be able to run the application programs and chances of data loss increase. 2. In case the network stops operating, computers connected to the network cannot access the data/files stored on the network, thus affecting work of the entire system. 3. As traffic increases on a network the performance degrades unless it is designed properly. 4. It becomes difficult to manage when a large number of computers is connected to a network. 5. In case the server is hacked or attacked by a virus, the security of data of all connected computers is at risk.

2

Basic Components of a Network Computers are connected to a network using some special hardware devices which are termed as network devices or components of a computer network. Following are the different components of a computer network.

Server

Server

Workstations

Pr e

Fig. 1.1 Workstations connected to a server

ity

For example, a file server is a computer that manages storage and retrieval of files. A print server is a computer that manages one or more printers required on a network.

ss

A server is a computer that manages the network resources, software and files (Fig. 1.1). It is normally dedicated to an allocated task and it performs no other task.

ve rs

Workstation

U

ni

A workstation is a computer intended for individual use in a networking environment. It is like a personal computer except that it is also connected to other computers along with the main computer, that is, the server.

ge

Network Interface Card

Workstation

am

br

id

A network interface card (NIC) is a piece of hardware placed inside the system unit. It is part of the motherboard. It is designed to allow computers to communicate over a computer network. It provides physical access to a networking medium.

Hub

C

Network Interface Card

A hub is a device that connects multiple devices and makes them work as a single network segment. A hub has multiple ports. A specific cable connects the hub to the NIC. The NIC transfers the data on a computer to a hub, which then transfers it to the other connected computers.

Hub

3

Transmission Channels

FACT FILE

Each computer on a network is interconnected through transmission channels. These channels can be wired or wireless. Data is exchanged between two computers on a network using these channels.

A switch is a smarter replacement of a hub. This is because, a hub transfers the data received from an NIC to all the computers whereas, a switch transfers it to the specific computer.

ss

The wired channels can be cables such as twisted pair, co-axial and fibre-optics etc. The wireless channels can work through satellites using microwaves, radio waves, etc. You will learn more about them later in this chapter.

Pr e

Types of Network

A network can be categorised on the basis of the geographical area as mentioned below.

ity

Personal Area Network (PAN)

ge

U

ni

ve rs

It is a network for communication among personal devices of an individual. The devices can include cell phones, computers, cameras, laptops and tablets. These devices are connected through a wired or wireless PAN. PAN covers a short radius of only a few metres. It is used to share or transfer files, songs, etc. USB cable is a source of a wired PAN. Bluetooth and Wi-Fi are sources of a wireless PAN.

Local Area Network (LAN)

am

br

id

When computers are interconnected within a limited geographical area, they form a Local Area Network. For example, a network within a building, an office and school. Such a network covers a radius of a few kilometres.

C

In addition to operating in a limited space, LANs are also typically owned, controlled and managed by a single person or organisation. A LAN which is created with the help of Wi-Fi, without using cables, is called a WLAN.

Wide Area Network (WAN) When a network is spread across cities, countries or even continents, covering a large geographical area, it becomes a Wide Area Network. The Internet is the best example of a WAN as it is the largest WAN covering the globe. Computer networks may also be classified according to the functional relationships that exist among the elements of the network. For example, client server and peer-to-peer (workgroup) architecture.

4

Metropolitan Area Network (MAN) When computers are interconnected within the same city, for example in different branches of an organisation in one city, it becomes a Metropolitan Area Network. Local libraries, different divisions of the same school in a city are some such examples. A MAN spans over a larger physical area than a LAN but is smaller than a WAN, such as a city. It is typically owned and operated by a single entity such as a bank, government body or large corporation.

FACT FILE

Pr e

ss

There is another type of network connecting multiple LANs confined within a campus. This is known as a Campus Area Network (CAN). This network is smaller than a MAN.

ve rs

ity

For example, different colleges connected in the same university campus and branches of the same office in a specific region.

Client/Server Architecture

C

am

br

id

ge

U

ni

Client/Server architecture is defined as a specific type of network which consists of a single powerful computer acting as a server, usually connected to multiple computers called clients (Fig. 1.2). Generally one server supports numerous clients. The server has powerful central processors, added memory, and larger disk drives in comparison to the clients. A server device typically stores files Client and databases including more complex applications like websites. The client contains the software programs based on the requirements of its users. Client Client Network clients make requests to a server by sending messages, and servers respond to their clients by acting on each request and returning results. Client Client A client/server network can be utilised by desktop computers and laptops, as well as other mobile Server devices that are properly equipped. Fig. 1.2 Client-Server architecture An example of a client/server is when you try to access your bank account from a computer. A client program on your computer forwards the request to a server program at the bank. The server accesses the data of that specific account and sends the request back with the account details to the client machine.

5

Advantage: The centralised handling of data provides increased security. For example, password protection which ensures that the data is only available to qualified individuals.

Disadvantage: It runs the risk of a system overload. If too many different clients attempt to reach a shared network at the same time, there may be a failure or slowing down of the connection.

Peer-to-peer Architecture Peer-to-peer architecture is a type of network in which each workstation has equivalent capabilities and responsibilities.

Pr e ity ve rs ni

Fig. 1.3 Peer-to-peer architecture

ge

U

Peer-to-peer networks can be set up among only a few computers within an office or single room. Such a network is much simpler to set up in comparison to client/server networks.

ss

The workstations are connected to each other but do not have a server (Fig. 1.3). Files can be shared among workstations, and a printer connected to one workstation can be also accessed by other workstations.

id

Advantage: It supports distributed processing, so the increased load does not affect

br

the system overall.

C

data backup.

am

Disadvantage: It does not support centrally managed security, nor does it provide FACT FILE

There are sets of rules that governs data communication in a networking environment known as protocols. A few of them are given below: •

Hyper Text Transfer Protocol (HTTP)

•

Simple Mail Transfer Protocol (SMTP)

•

File Transfer Protocol (FTP)

• Transmission Control Protocol/Internet Protocol (TCP/IP)

6

Network Topology The physical arrangement of cables, computers and other peripheral devices to form a network is known as a topology. A few such network topologies have been described below.

Bus Topology

ss

A bus topology is made up of a main single cable with terminators at both ends (Fig. 1.4). It is a shared communication medium that makes the backbone of the system. Computers and other devices including the server are connected to this linear cable for communication.

Advantages: The advantages of a network with bus topology

Fig. 1.4 Bus topology

ity

Pr e

are given below: • It is easy to install. • It does not require much cabling, hence it is very cost effective.

ni

If the backbone cable fails, the entire network becomes inoperable. In case of an entire network shut down, it becomes very difficult to locate the problem area.

U

• •

ve rs

Disadvantages: The disadvantages of a network with bus topology are given below:

ge

FACT FILE

am

br

id

The data to be delivered on a network is divided into small packets. Each packet is given a sequence number. The first packet has a destination address followed by the next packet number.

C

The last packet has an end of file pointer that helps the destination computer to know that the number of packets are complete. Any data loss while data transmission on a network can be found out by tracing the missing packet number.

Star Topology Star topology is the most commonly used topology. In such a setup all the workstations are connected to a central connection point called a hub (Fig. 1.5). Any data that is sent, first goes to the central hub and is redirected from there to the destination computer.

Fig. 1.5 Star topology

7

Advantages: A few advantages of a star topology are: • •

It is easy to add and remove workstations by upgrading the hub. It is easy to install.

Disadvantages: A few disadvantages of a star topology are: • •

It requires more cable length. Failure in the central hub will break down the entire network.

Pr e

In a ring topology, every workstation has exactly two neighbours for communication purposes. All messages travel through a ring in the same direction either clockwise or anticlockwise (Fig. 1.6).

ss

Ring Topology

It is easier to detect faults in the network. Less number of cable wires are required.

ve rs

• •

ity

Advantages: A few advantages of a ring topology are:

Fig. 1.6 Ring topology

Disadvantages: A few disadvantages of a ring topology are: A failure in any cable or device breaks the loop leading to breakdown of the entire network. Adding or removing a device/workstation requires rewiring and re-routing all the existing cables.

ge

•

U

ni

•

br

id

Types of Transmission Channels

C

am

The medium used for transmission of data between the nodes in a network is called a transmission channel. This channel can be: • Wired • Wireless

Wired Transmission Channels Wired transmission channels help in transmission of data over a wire based networking. A few examples of wired channels are explained here.

Coaxial Cable: It is one of the cheapest and the most commonly used wire based networking mediums. This type of cable is suitable for transmitting low powered signal over a small distance. It is widely used in small networks, cable TV, etc.

8

Coaxial cable

Twisted Pair Cable: It consists of two independently insulated wires, twisted around one another. The twisting eliminates hindrance of signals due to adjacent pairs or other sources.

Twisted pair cable

FACT FILE

Fibre-optic Cable: It is made up of optical fibres that

Wireless Transmission Channels

Fibre-optic cable

ve rs

ity

Pr e

use the concept of light for transmission of data. High speed data can be made to travel over a long distance with less damage to it. It is steadily replacing copper wire as an appropriate means of communication through signal transmission.

ss

There are generally two types of twisted pair cables. These are Shielded Twisted Pair (STP) and Unshielded Twisted Pair (UTP).

am

br

id

ge

U

ni

Wireless communication is the transfer of information over a distance without use of wires. It is the fastest growing segment of the communication industry today. The distances involved may be short, that is, a few metres as in the case of the television remote control or long as in the case of thousands or millions of kilometres for radio communications. There are many types of wireless networks that make data communication possible, both over long range and short range. All of these networks operate at different frequencies.

C

Some examples of wireless communication are: • Remotes of electronic devices •

Bluetooth

•

Cell phones

•

Wireless networking

•

Wireless computer devices like mouse and keyboard

•

Satellite televisions

•

Radio waves

FACT FILE Wi-Fi (Wireless Fidelity) is a wireless LAN technology that enables laptops, PCs and other devices to connect easily to the Internet.

9

There can be various channels for wireless transmission. A few are explained here:

Radio Waves: Radio waves are generally used for long distance wireless communication. Signals can pass through thick objects such as non-metalllic substances. Radio waves are used in radio communication (AM/FM), communication satellites, radars, computer networks, etc.

Infrared: Infrared is one of the most primitive forms of wireless communication. It is an electromagnetic radiation. It is basically suitable for short distance communication. For example, a television and a remote control connect to each other using infrared rays. The signals travel in a straight line and cannot penetrate walls.

Microwave: Microwave is the most commonly used wireless transmission medium,

Pr e

ss

which is spread widely across the world. Most of the wireless access points and wireless devices work on microwave communication. This communication medium is not only cost effective, it is also extensively used in cellular communication.

Bluetooth: Bluetooth is another wireless communication

id

ge

U

ni

ve rs

ity

medium operating over short distances. It has a normal range of approximately 3–300 feet depending on the connected devices. These devices can be mobile phones, personal computers, laptops, digital camera, MP3 players, etc. Bluetooth enabled devices form a small network. In Bluetooth technology, eight devices can be A Bluetooth device connected to each other at the same time. Bluetooth can also be found in headsets, hands-free kits, wireless keyboards and mouse. Bluetooth uses the radio wave technology which is not very expensive and has low power consumption.

br

Mobile Operating System

C

am

Initially, when mobile phones were introduced in the late 90s, they were just designed to support dialing and receiving calls and sending simple text messages. Usage of mobile phones has increased drastically over the last few years, which has brought a huge revolution in the features of mobile handsets.

Y AC TIVIT Make a list of networking devices available in your computer lab.

Nowadays mobile phones are just like a compact computer loaded with basic applications. These mobile applications can be designed and uploaded easily. Wireless establishment of connections can be achieved through Bluetooth and infrared technologies. Mobile phones having such features are referred to as smartphones.

10

To manage all these important features in phones, we need a special type of operating system called Mobile Operating System (or mobile OS). This operating system is used in smartphones, tablets and other mobile devices.

Difference Between Mobile OS and Desktop OS

Pr e

ss

Desktop operating systems are designed keeping computers in mind. They provide an environment that helps in better functioning of different application software having a wide range of connected hardware. Desktop OS also supports multiple users sharing a network. The same OS cannot be used for mobile phones as they are compact computers having limited power, processing speed and memory. Keeping these limitations in mind, separate operating systems, specially designed for mobiles were introduced. They are designed keeping in mind different devices and they perform different functions.

id

Types of Mobile OS

ge

U

ni

ve rs

ity

Development of both, computer and mobile OS, is different for different set of users. Following factors determine the requirement of different OS for smartphones: • A mobile phone is a compact machine with small battery size so the OS should be designed in a simple way to work with limited power for a longer period of time. • Limited memory is available in mobile phones, therefore the OS should be less complex to occupy less storage capacity. • Mobile phones are equipped with in-built keyboard, so a different Graphical User Interface (GUI) is required.

C

am

br

The technological advancements mentioned above resulted in development of various solutions that cater to functioning of mobile phones. Some of the popular mobile OS are Android by Google, iOS by Apple, Windows phone by Microsoft, BlackBerry OS by RIM, webOS by Hewlett-Packard and Symbian OS by Nokia. A detailed description of these OS is given below.

Android OS It is one of the most popular operating systems designed by Android, Incorporation. in Silicon Valley before Google acquired it in 2005. Its first commercial version was officially launched in 2008. It is an open source and freely available Linux based operating system mainly designed for smartphones and tablets with touchscreens. It has the basic operating system features with calls and messaging services that can be used to establish connections using different communication networks on different devices.

11

Different versions of the Android OS are named after desserts, for example, Android 1.5 is known as Cupcake, 1.6 as Donut, 2.0/2.1 as Eclair, 2.2 as Froyo and 2.3 is dubbed Gingerbread. The latest 9.0 version is called Pie. Since it is an open source system, it can be easily used and modified by anyone making it more prone to malwares and data stealing.

iOS

Pr e

ss

This is a mobile operating system designed by Apple Incorporation mainly for iPhones, iPods and iPads. It was made available commercially in 2007 as an operating system for iPhones. It is the second most popular mobile operating system after Android. iOS is a closed source system owned by Apple and no other company or person can use it or modify it, thus providing strong security features against data theft or malwares. iOS 12 is the latest version of iOS in iPhones and iPads.

Windows Phone OS

U

ni

ve rs

ity

It is a mobile operating system developed by Microsoft. It was first made available for Microsoft’s Pocket PC 2000. The same was later designed for Nokia devices. It has a tile-like interface. Windows Mobile 2003 – Ozone, Windows Mobile 5 – Magento, Windows Mobile 6 – Crossbow, etc. are different versions of the Windows phone. Windows 10 is its latest version.

ge

BlackBerry OS

C

am

br

id

This mobile operating system has been developed by Blackberry Limited for its own devices just as iOS has been designed only for iPhones. The BlackBerry platform is best known for strong wireless activation and synchronisation of emails, calendar, notes, contacts and tasks. Blackberry 10 is the latest version of this OS. Nowadays, this mobile OS seems to be losing popularity because of increasing market demand for Android and iOS based phones.

webOS This operating system was developed by Palm Incorporation. It is a Linux based open source operating system which was taken over by HP and later sold to LG for smart phones and smart TVs. webOS strongly supports multitasking and is better than iOS when running multiple applications at one time. Its various versions are Pre, Pixi and Veer for smartphones. Since 2014, LG has been using webOS for its smart projectors, refrigerators and smart TVs.

12

Symbian OS This mobile operating system has been discontinued with smartphones. It supported closed-source operating system which was initially designed for Personal Digital Assistant (PDA) in 1988 by Symbian Ltd. It was used by many popular brands such as Samsung, Sony Ericsson and Nokia. It was the first modern mobile OS for smartphones designed for Ericsson R380. In 2006, it was the most popular mobile OS so Nokia took over the company in 2010 and named it Symbian Foundation. Its touchscreen was not as smooth as Android and iOS, so with increase in popularity of iOS and Android in 2012, it lost its market value and Nokia discontinued its usage as a smartphone mobile OS.

ss

Mobile Applications Development

ve rs

ity

Pr e

Mobile application development is a set of instructions written to develop applications for smartphones and mobile devices. Mobile apps are small software units with limited functionalities. Initially mobile apps were device specific but recently app developers have developed applications that can work efficiently on multiple platforms, including Android, iOS and Windows.

id

ge

U

ni

Due to a drastic increase in the use of smartphones, there are more mobile users in comparison to desktop users today. With mobile apps, the user can access general information about any product, prices, booking forms, search features, user accounts, messengers, news feeds and much more.

am

br

Y AC TIVIT

Find out the language used to develop Android operating system. List its two important features.

2.

Can we shut down Mobile OS? List out the steps to shut down an Android OS.

C

1.

GLOSSARY Android OS It is an open source and freely available Linux based mobile OS. BlackBerry OS This mobile OS has been developed by Blackberry Limited for its own devices.

13

ve rs

ity

Pr e

ss

Client It is a computer connected to the server on a network. File server It is a computer that manages the storage and retrieval of files. Hub It is the central connection point on a network. iOS This mobile OS has been designed by Apple Incorporation, mainly for iPhones, iPods and iPads. Mobile OS It is a GUI developed for smartphones. Print server It is a computer that manages the printers required on a network. Server It is a computer that manages the network resources. Symbian OS It supported the closed-source operating system, which was initially designed for Personal Digital Assistant (PDA) in 1988 by Symbian Ltd. Topology It is the physical arrangement of computers on a network. webOS It is a Linux based open source operating system which was taken over by HP and later sold to LG for smart phones and smart TVs. Windows phone OS It is a mobile OS developed by Microsoft for Windows based phones. Workstation It is a computer intended for individual use on a network.

1. A network consists of two or more computers linked together for the purpose of sharing resources. 2. Workstations, Server, Hub, Network Interface Card and Transmission channels together form a network. 3. When a network connects personal devices of an individual, it forms a Personal Area Network (PAN). When the computers are interconnected within a limited geographical area it becomes a Local Area Network (LAN). A network connecting multiple LANs limited within the same campus is known as Campus Area Network (CAN). When computers are interconnected within the same city, that is, branches located at different places in the same city, it becomes a Metropolitan Area Network (MAN). When a network is spread across cities, countries or even continents covering a large geographical area, it becomes a Wide Area Network (WAN). A specific type of a network consisting of a single powerful computer acting as a server usually connected to multiple computers called clients is known as a Client/Server architecture. Peer-to-peer architecture is a type of network on which each workstation has equivalent capabilities and responsibilities.

U

ni

YOU ARE HERE

6. 7. 8.

9.

14

am

5.

C

4.

br

id

ge

1

Pr e

ss

10. Medium used for transmission of data between the nodes on a network is called transmission channels. These channels can be wired or wireless. 11. Wired transmission channels refer to transmission of data over a wire based networking. For example, twisted pair, coaxial cable and fibre-optic. 12. Wireless communication is the transfer of information over a distance without the use of wires. Microwaves, bluetooth, infrared rays are used in wireless communication. 13. Development of both computer and mobile OS is different for different set of users. 14. Some of the mobile OS are Android by Google, iOS by Apple, Windows phone by Microsoft, BlackBerry OS by RIM, webOS by HP and Symbian OS by Nokia. 15. Mobile application development is a set of instructions written to develop applications for smartphones and mobile devices.

ve rs

A.

ity

EXERCISE State true or false.

U

ni

1. Bluetooth technology is a form of wireless communication.

ge

2. In a bus topology, all workstations are connected to the central hub.

id

3. A mobile phone is capable of handling unlimited memory.

am

br

4. Androids are more prone to malwares and data stealing.

B.

C

5. webOS is a Linux based open source operating system. Fill in the blanks. 1. Types of transmission channels are .............................. and .............................. . 2. .............................. is a computer that manages storage and retrieval of files. 3. In .............................., computers are interconnected within a limited geographical area. 4. ................................................ is a set of instructions written to develop applications for smartphones and mobile devices. 5. .............................. is one of the most popular operating systems designed by Android, Inc.

15

C.

State the differences between: 1. LAN and MAN 2. Star and Bus topology 3. Desktop and Mobile OS 4. Android and iPhone OS 5. Client/server and Peer-to-peer architecture

E.

Match the following. a. Apple products

2. webOS

b. Small software units with limited functions

3. Smartphones

c. Linux based OS

4. iOS

d.

Compact computers

5. Mobile apps

e.

Prone to malware threat

ss

1. Android

Pr e

D.

Answer the following questions.

br

LAB WORK

id

ge

U

ni

ve rs

ity

1. What is networking? State its advantages and disadvantages. 2. What are the different components of a computer network? Explain them briefly. 3. What is a topology? Explain the different types of topologies used in computer networks. 4. What is Mobile OS? Give few examples. 5. Give two reasons for developing a mobile app.

C

am

A. Find out the topology of the network used in your school’s computer lab. Find out the main reason for implementing that specific topology in the lab. Write an article in MS Word 2010 stating the definition, advantages and disadvantages of using that specific topology. Insert a few relevant pictures using the Internet. B. How do cell phones work? Try to find out the technology used and the wireless transmission channel used in the cell phone technology. Take help of the Internet to research on this topic. C. Make a presentation on different types of Mobile OS. Take help of the Internet to get information and insert pictures wherever possible. D. These days we can buy and sell products like clothes, cars, furniture, medicines, cosmetics and lot more through mobile apps. Make a document of any five most commonly used apps for buying and selling products these days. Also, name a few websites associated with these specific apps listed by you.

16

PROJECT WORK

C

am

br

id

ge

U

ni

ve rs

ity

Pr e

ss

Work in groups and select a mobile OS for your group. Now prepare a chart mentioning the latest devices which use that particular mobile OS and mention their important features.

17

Creating Tables

ss

in MS Word 2010

LEARNING OBJECTIVES

ve rs

ity

You will learn about: • creating tables in MS Word 2010 • modifying tables in MS Word 2010 • table styles • applying borders and shadings

id

ge

U

ni

1. Mention any four features of MS Word 2010. 2. What are the basic formatting commands that can be used in MS Word 2010? 3. How can you find a word in a document and replace it with another word? 4. What are the different kinds of text alignments?

Pr e

SNAP RECAP

br

Introduction

C

am

Tables are used to present given data in the form of rows and columns. It is very useful in organising and presenting data in an effective way. For example, if you want to maintain record of your marks in various subjects (Table 2.1), then you can use this feature of MS Word 2010. Row: It is the horizontal series of cells in a table. Column: It is the vertical series of cells in a table. Cell: The intersection of a row and a column makes a rectangular box called a cell. Table 2.1 Example of a table Row

18

Subject Mathematics English Geography

Marks in Test 1 75 83 89

Marks in Test 2 91 79 88

Column Cell

Creating Tables in MS Word 2010 In an MS Word 2010 document, a table can be created in different ways as shown in Fig. 2.1.

Table grid Insert Table…

ss

Draw Table

ity

Excel Spreadsheet

Pr e

Convert Text to Table

Quick Tables

ve rs

Fig. 2.1 Options for creating tables

Using Insert Table Grid

C

am

br

id

ge

U

ni

1. Bring the cursor to the location where a table is to be inserted. Click on the Table drop-down list in the Tables group of the Insert tab. 2. Select the required number of rows and columns in the Insert Table grid. For example, to create a table of five columns and four rows select the cells as shown in Fig. 2.2. 3. Press the Enter key or click the left mouse button to create an empty table in the desired location.

Fig. 2.2 Highlighting the number of rows and columns

19

Using Insert Table Option

Pr e

ss

1. Bring the cursor to the location where a table is to be inserted in the document. 2. Select the Insert Table… option from the Table drop-down list in the Tables group of the Insert tab. 3. The Insert Table dialog box appears (Fig. 2.3). Type or select the number of rows and columns to get the desired table size.

Type the number of rows

Click OK

U

ni

ve rs

ity

Type the number of columns

ge

Fig. 2.3 Insert Table dialog box while using Insert Table... option

C

am

br

id

4. Select Auto in the Fixed Column width: option. This allows Microsoft Word to automatically determine the column widths based on the maximum width of the content stored in any column. You can also enter the desired column width as per your requirement. 5. Click on OK. Your table should look like the one shown here, say, with four columns and two rows (Fig. 2.4).

Fig. 2.4 The table with four columns and two rows

20

Using Draw Table Option

Pr e

ss

When you click on the Draw Table option, the mouse pointer changes to a pencil. This pencil is then used to draw the rows and columns of a table of the desired dimensions (Fig. 2.5).

ity

Fig. 2.5 To create a table using the Draw Table option

ve rs

Converting Text to a Table

C

am

br

id

ge

U

ni

You can convert text to a table; however, a comma, paragraph marker or a tab must separate columns of the text. In the given example, you will learn to convert comma delimited text into a table. 1. Type the following in MS Word 2010: Anya, Class 7, 11 years Joy Class 6, 10 years 2. Highlight the text. 3. Click on Insert tab Tables group Table drop-down list Convert Text to Table… option. 4. The Convert Text to Table dialog box appears (Fig. 2.6).

Fig. 2.6 Convert Text to Table dialog box while using Convert Text to Table… option

21

5. Type 3 in the Number of columns: field. 6. Type 2 in the Number of rows: field. 7. Select Auto in the Fixed column width: field. 8. Select Commas in the Separate text at section. 9. Click on OK. 10. The text will now appear in a table format (Fig. 2.7). Class 7

11 years

Joy

Class 6

10 years

ity

Using Excel Spreadsheet

Pr e

Fig. 2.7 Table format

ss

Anya

C

am

br

id

ge

U

ni

ve rs

You can also insert a table in the form of an Excel spreadsheet. For this, click on the Insert tab Tables group Table drop-down list Excel Spreadsheet option. An editable excel sheet will appear (Fig. 2.8).

Fig. 2.8 Using Excel Spreadsheet option to insert a table

22

Using Quick Tables

ity

Pr e

ss

Quick Table option in MS Word 2010 provides various templates for creating a table (Fig. 2.9). Some templates available in this option are: Calendar, Matrix, Tabular List, etc. You can select one of the templates from the given options and start filling your data.

ve rs

Fig. 2.9 Using Quick Tables option to insert a table

U

ni

Y AC TIVIT

ge

Complete the following activity based on the instructions given. Create a table that should have rows equivalent to the number of your friends and columns equivalent to the topics in your scrapbook.

2.

Enter data in the table for at least three of your friends.

3.

Name it as MyFriendsList.docx and save it.

C

am

br

id

1.

How to Modify Tables in MS Word 2010 Moving and Resizing a Table If the mouse is placed over the table, a four-headed arrow and open box resizing handle appear in the upper-left and lower-right corners of the table respectively (Fig. 2.10). Click and drag the four-headed arrow enclosed in a rectangle to move the table.

TRY THIS Click on the Move Handle to select the table. The selected table can be cut, copied and pasted. It can also be resized or formatted.

23

Release the mouse button when the table is positioned at the desired location. Click and drag the open box handle to resize the table.

Move handle

Resize handle Fig. 2.10 Moving and resizing a table

Moving Around a Table

ss

Each box in a table is called a cell. Use the Tab key to move from cell to cell in the left to right direction. Use the Shift + Tab keys to move from cell to cell in the right to left direction.

ve rs

ity

Pr e

The following exercise demonstrates how to move around a table. 1. Click on the first cell in the first column. 2. Press the Tab key twice. The cursor moves two cells in a forward (right hand) direction. 3. Press Shift + Tab keys once. The cursor moves one cell in the backward direction.

ni

Entering Text Into a Table

am

br

id

ge

U

To enter text into a table, simply type as you normally would. Press the Tab key to move to the next cell. Let us enter the text in a table as given below. 1. Type Name in the first cell of the first column. Press the Tab key. 2. Type Class in the first cell of the second column. Press the Tab key. 3. Type Section in the first cell of the third column. Press the Tab key. 4. Continue till you have entered all of the text given in the table below.

C

Name Adil Ananya John Sara

Class 7 6 8 7

Section A C B B

All the formatting options that you have learnt in earlier classes can also be used to format the text in the table.

24

Selecting a Row or a Column

Name Adil Ananya John Sara

To select a row, bring the mouse pointer to the desired location and drag the left mouse button (Fig. 2.11). Use the same method to select a column. Class 7 6 8 7

Section A C B B

Fig. 2.11 Selecting a row

Section A C B B

To select an entire column, bring the mouse pointer to the top of the particular column in the table, an arrow will appear known as the column selection bar, click the arrow to select the entire column (Fig. 2.12).

ss

Name Adil Ananya John Sara

Class 7 6 8 7

Pr e

Fig. 2.12 Selecting a column

Adjacent cells can be selected by clicking and dragging the left mouse button through them.

ity

Adding a New Row

Layout tab

ge

U

ni

ve rs

You can add a new row anywhere in the table. This has been demonstrated in the following exercise.

Insert Right

C

am

br

id

Insert Above option

Delete drop-down list

Merge Cells option Split Cells option

Fig. 2.13 Various options in the Layout tab

Let us consider Fig. 2.13. To add a row just above John (see Fig. 2.12 above) in the table follow these steps. 1. Place the cursor anywhere in the fourth row. 2. Click on the Layout tab. 3. Choose the Insert Above option in the Rows & Columns group.

25

4. Add the information shown here to the new row. Fatima 6 A Choose the Insert Below option in the Rows & Columns group of the Layout tab to insert a row below an existing row.

Adding a New Column

Insert Right

ity

ni

Deleting a Row

TRY THIS

Select the column or cell to be deleted. Right click on the mouse and try using the Delete Columns or Delete Cells… option in the shortcut menu.

ve rs

Choose the Insert Left option to insert the new column to the left of the selected cell.

Pr e

ss

You can add new columns in the table. 1. Place the cursor anywhere in the last column. 2. Click on the Page Layout tab Rows & Columns group option. 3. Label the new column Hobby. 4. Add text in the new column which is now a part of the table.

Delete drop-down list

am

br

id

ge

U

You can delete rows from a table. To delete any row: 1. Place the cursor anywhere in the row to be deleted. 2. Select the Layout tab Rows & Columns group Delete Rows option.

C

Deleting a Column

You can delete columns from the table. To delete any column: 1. Place the cursor anywhere in the column to be deleted. 2. Select Layout tab Rows & Columns group Delete drop-down list Delete Columns option.

Merging Cells Using Microsoft Word, you can merge cells, that is, you can combine two or more cells into one cell. The steps are: 1. Select the cells that are to be merged.

26

2. Click on the Layout tab

Merge group

Merge Cells option.

Splitting Cells

am

br

id

ge

U

ni

ve rs

ity

Pr e

ss

You can also split the cells. The steps to split cells are: 1. Select the cells that are to be split. 2. Click on the Layout tab Merge group Split Cells option. 3. The Split Cells dialog box appears (Fig. 2.14). Specify the desired number of rows and columns. 4. Click on OK.

Fig. 2.14 Split Cells dialog box

C

Resizing the Columns You can easily change the width of a particular column or all columns in a table. To change the width of all columns, select the entire table and adjust all the column widths. To select the width of a particular column, select the desired column and do either of the following: Adjust the column width using the mouse handle/pointer.

27

ni

ve rs

ity

Pr e

ss

OR 1. Right-click anywhere on the document and from the shortcut menu that appears, select Table Properties... (Fig. 2.15).

U

Fig. 2.15 Selecting Table Properties

C

am

br

id

ge

2. The Table Properties dialog box appears (Fig. 2.16). Click on the Column tab.

Fig. 2.16 Table Properties dialog box

28

3. Type or select the desired column width in the Preferred width: box. Select the unit (inches, mm, etc.) from the Measure in: drop-down list. 4. Click on OK. You can resize the column widths by placing the cursor on the line that separates the two columns. This causes the width indicator to appear. Left click and drag the width indicator with the help of the mouse to adjust the column width.

ss

TRY THIS

ve rs

ity

Pr e

Change the row height and column width using the Height: and Width: options, respectively, in the Cell Size group of the Layout tab.

ni

Y AC TIVIT

U

Complete the following activity by using the table created earlier. Insert a new column in the left of the table. Add the details of a friend living in some other town.

2.

Add a new column at the end with a heading ‘My Favourite Book’.

3.

Save the changes in your file.

am

br

id

ge

1.

C

Table Headings

If Microsoft Word splits your table with a page break, the table heading is displayed on the first page but not on subsequent pages. To correct this, you can designate the selected rows as heading rows. Such rows are repeated on the top of the table on each page. The steps to repeat table headings are: 1. Select the desired row. 2. Click on Layout tab Data group Repeat Header Rows option (Fig. 2.17).

29

ss Pr e

Fig. 2.17 Repeating Table headings

ity

3. The same heading will appear on the top of the table on each page.

ve rs

Table Styles

C

am

br

id

ge

U

ni

You can use Table Styles to apply borders, shading, special fonts and colours to the table. Microsoft Word lists all the formats available for use. You can customise how the format is applied. Follow these steps to apply a Table Style to your table: 1. Click anywhere on the table. 2. Go to Table Styles group in the Design tab. 3. Scroll down to choose the desired style from the given list (Fig. 2.18).

Fig. 2.18 Table Styles

30

ve rs

ity

Pr e

ss

4. Click the left mouse button on the desired style to apply it to the selected table. 5. The More button can be used to see the entire list of Table Styles available in Microsoft Word 2010 (Fig. 2.19).

ni

Fig. 2.19 Table Styles list

ge

U

Bring the mouse pointer on a style and Microsoft Word will show a preview of your table in that style.

br

id

Applying Borders and Shading

C

am

You can apply borders and shading effects to the table created in MS Word 2010. To add borders and shading to a table follow the steps given here. 1. Select the entire table or the cells where borders and shading is to be applied. 2. Under the Table Tools, click on the Design tab. 3. In the Table Styles group, click the Borders drop-down list (Fig. 2.20a) to apply borders and the Shading drop-down list to apply shading to the table (Fig. 2.20b). 4. Select the predefined border and colour options respectively. You can remove borders from the entire table by selecting the No Border option.

31

ss

br

id

ge

U

ni

ve rs

ity

Pr e

Fig. 2.20 a Borders options

C

am

Fig. 2.20 b Shading options

Y AC TIVIT Complete the following activity. 1.

Give a heading to the table created by you in the earlier activities as MY SCRAP BOOK.

2.

Format your table using the Design tab.

3.

Save the changes made in your file.

32

TRY THIS Select the table. In the Table Tools, click on the Design tab. In the Tables Styles group, click on Borders drop-down list and select Borders and Shading… option. Now apply different borders and colours to your table using the Borders and Shading dialog box.

QUICK KEY Tab

Go to previous cell

Shift + Tab

Pr e

ss

Go to next cell

Alt + PageUp

Go to beginning of column

Alt + Shift + PageUp

Go to end of column

Alt + PageDown

ve rs

ity

Select from the beginning of column to the current place

Alt + Shift + PageDown

Go to beginning of row

Alt + Home

U

ni

Select from the current place to the end of column

ge

Select from the beginning of row to the current place

Alt + Shift + Home Alt + Shift + End

Go to end of row

Alt + End

C

am

br

id

Select from the current place to the end of row

GLOSSARY Cell It is a rectangular box formed by intersection of a row and a column. Column It is a vertical series of cells in a table. Merge cell It is the process of combining two or more cells in a table to form a single cell. Row It is a horizontal series of cells in a table. Split cell It is the process of dividing a big cell into multiple smaller cells.

33

YOU ARE HERE

2

1. A table can be created using: a. Insert tab Table grid.

Tables group

Table drop-down list

Insert

b. Insert tab Tables group Table… option.

Table drop-down list

Insert

c. Insert tab Table option.

Table drop-down list

Draw

Tables group

ge

U

ni

ve rs

ity

Pr e

ss

2. A row or a column can be easily added or deleted in an existing table. 3. Cells can be merged in MS Word 2010. 4. You can convert text into a table, however, a delimiter such as a comma, paragraph marker, or a tab must separate columns of text. 5. Table headings can be displayed on the subsequent pages in case the table is continued, provided the selected rows are designated as headings. 6. You can use Table Styles to apply borders, shading, special fonts and colour to your table. MS Word 2010 lists all formats in the Table Styles group.

State true or false. 1. A table is used to create data in the form of rows and columns.

am

A.

br

id

EXERCISE

C

2. Delete row in the Insert tab helps to create tables. 3. When you click on the Quick Table option, the mouse pointer changes to a pencil which can be used to draw tables in a document. 4. The intersection of a row and a column is called a cell. 5. A column can be deleted by using Insert table option in the Rows and Columns group of the Layout tab.

34

B.

Match the following. 1. Go to the next cell

a. Alt + End

2. Go to the end of row

b. Alt + PageUp

3. To go to the beginning of the column

c. Alt + Home

4. To go to the beginning of the row

d. Tab

Give the steps to do the following. 1. Deleting the first row 2. Adding a column to the right 3. Splitting the cells 4. Adding a Table Style 5. Applying Borders and Shading effects to a cell in the table

D.

Answer the following questions. 1. Write the steps for inserting a table in a document using the Table grid option. 2. Define a row, a column and a cell. 3. Can you add a new row in the middle of an existing table? If yes, give the steps. 4. How can Table Styles feature be applied to an existing table? 5. Mention the steps to create a table with four rows and two columns using the Draw Table option.

ge

U

ni

ve rs

ity

Pr e

ss

C.

br

id

LAB WORK

C

am

A. Create your class timetable and apply the following features. 1. Draw a table with six rows and eight columns. 2. First cell of each row should be used to maintain the details of the number of periods in a day. 3. First cell of each column should be used to maintain the name of working days in a week. 4. Add the relevant details of the timetable. 5. Format the text by applying appropriate font size and font colour. 6. Add a new column after fourth period which specifies Recess. 7. Add a new row at the end for Saturday. 8. Apply any Table Style of your choice.

35

B.

Pr e

ss

Create a table mentioning your friend’s names. 1. The table should have minimum ten names with five columns: Name, Address, Phone Number, Bus Route Number and Date of Birth. 2. Format the table with proper font styles and background. 3. Give it a heading ‘MY LIST OF FRIENDS’. C. Create a subject-wise list of activities to be conducted in your school for a month. 1. The rows should contain names of subjects. 2. The columns should have: subject name, week1, week2, week3, week4. 3. Format the table and give a suitable heading. Save the file.

ve rs

ity

PROJECT WORK

id br am C 36

Quantity

U

Items required for the party

ge

Serial No.

ni

Prepare a list of items required for a birthday party. The structure of the table should be as given below. In stock or not (Y/N)

Price

MS Excel 2010

ss

Advanced Features LEARNING OBJECTIVES

ve rs

ity

You will learn about: • cell reference • sorting data • filtering list • conditional formatting • creating charts • importing data from another document

am

Cell Reference

br

id

ge

U

ni

1. Why do you need MS Excel program? 2. What do you understand by workbook and worksheet? 3. Define rows, columns and cells. 4. Can you write mathematical formulas and execute functions in an MS Excel spreadsheet program? 5. Differentiate between an operand and an operator.

Pr e

SNAP RECAP

C

In Excel, a cell reference identifies the location of a cell or group of cells in the spreadsheet. Cell reference is also known as cell address. A cell reference consists of the column letter and row number that intersect to form a cell. These references are used in formulas, functions, charts, etc. There are three types of cell references. They are discussed here.

Relative Reference A relative cell reference in a formula is based on the relative position of the cell contained in the formula. By default, a spreadsheet cell reference is relative. This means, when the formula or function is copied and pasted to other cells, the cell references in the formula or function change relative to the location where it is copied.

37

For example, if a formula =A1+B1 or SUM(A1:B1) is written in cell D1 and is copied to E1, then the formula will change to =B1+C1 for cell E1.

ity

Pr e

ss

If it is copied down to D2, then the formula will change to =A2+B2 for D2 and =B2+C2 for E2 (Fig. 3.1).

ve rs

Fig. 3.1 Example of a relative reference

U

ni

If there is no value in the cell, it takes a null value by default.

ge

Absolute Reference

am

br

id

Absolute cell reference in a formula always refers to a cell in a specific location. This is done by fixing the cell by using the dollar sign ($) in front of a row and a column. Here, even if the position of the cell that contains the formula changes, the reference of the cell will remain the same.

C

If you copy the formula across rows or down columns, the formula value does not change. For example, if a formula =$A$1+$B$1 is written in cell D1 and is then copied to E1, the formula will remain as it is for D1, that is, =$A$1+$B$1. It will not change even if it is copied to cells D2 and E2 (Fig. 3.2).

38

Fig. 3.2 Example of an absolute reference

Mixed Reference

id

ge

U

ni

ve rs

ity

Pr e

ss

A mixed reference has an absolute column and a relative row, or an absolute row and a relative column. An absolute column reference takes the form $A1, $B1, and so on. An absolute row reference takes the form A$1, B$1, and so on. If the position of the cell that contains the formula changes, the relative reference is changed, but the absolute reference does not change. For example, if you copy a mixed reference formula =A$1+B$1 from cell D1 to cell E1, the formula changes to = B$1+C$1. In this case, the row is fixed. It will not change even if the location changes in the worksheet (Fig. 3.3).

am

br

Fig. 3.3 Example of a mixed reference

Sorting Data in MS Excel 2010

C

In a spreadsheet, it is easy to maintain data as the sheet is divided into rows and columns. Data when entered in a sheet may not be arranged in an order. However, you can arrange it in either ascending or descending order. This physical rearrangement of data in ascending or descending order is called sorting. In Excel, sorting data is one of the most useful features.

Types of Sorting Sorting is of two types. 1. Ascending Order: Arranging the data in an increasing order. For example, letters from A to Z and numbers from lowest to highest.

39

2. Descending Order: Arranging the data in a decreasing order. For example, letters from Z to A and numbers from highest to lowest. Sorting can be done in two ways. These are explained below.

Sorting on One Column Here data is arranged in ascending or descending order for only one column. For example, a list of students in a class is maintained with the ‘Name’ of all the students arranged in ascending order.

FACT FILE Sorting, by default, is not case sensitive. Small letters and capital letters will be sorted equally.

Pr e

ss

Follow these steps to arrange the given list in the ascending order of ‘Name’. 1. Create a list in a worksheet. 2. Select the data to be sorted.

ve rs

ity

3. Select Data tab Sort & Filter group (Fig. 3.4). Select Sort A to Z option AZ or Sort Z to A option ZA . In this case, select Sort A to Z option AZ .

br

Sort & Filter group

C

am

Data tab

id

ge

U

ni

4. The data will be sorted in the ascending order of ‘Name’.

Sort A to Z option Sort Z to A option

Fig. 3.4 Selecting the Sort option and the sorted data

40

Sorting on More Than One Column When the data is arranged in ascending or descending order in one column, it can again be sorted within another column. For example, student list sorted on the ‘Name’ column within one class. Here, class is called the primary column and student name sorted within it is called the secondary column. Follow these steps to sort the list in more than one column: 1. Create a list in a worksheet. Select the data to be sorted. 2. Select Data tab Sort & Filter group Sort option (Fig. 3.5).

ss Pr e ity ge

U

ni

ve rs

Fig. 3.5 Sort option

am

br

id

3. The Sort dialog box appears (Fig. 3.5). Click on Sort by drop-down list in the Column section. Select ‘Class’ in this case. Smallest to Largest is already selected in the Order section based on which data will be sorted in ascending order in the ‘Class’ column of the spreadsheet.

Sort option

C

4. Click on the Add Level button in the Sort dialog box; the Then by row Fig. 3.6 Sort dialog box and Sorted data for more appears. Select ‘Name’ than one column from the Then by dropdown list. Select the order as A to Z in the Order drop-down list. 5. Click on OK. The data is arranged as shown in Fig. 3.6. Further, more levels can be added to the Sort dialog box for the given data in the spreadsheet.

41

FACT FILE

Y AC TIVIT

Excel considers the first row of the spreadsheet as the Header row, that is, the first row of the table has headings. Thus, My data has headers checkbox is already selected in the Sort dialog box.

Create a list of your friends names and their birthdays. Sort the list by their dates of birth. Save the file with the name ‘Birthdays.xlsx’.

Pr e

ss

Filtering List in MS Excel 2010

ve rs

ity

You often have to scan through rows in an Excel worksheet for some required data. It may be anything such as the name of a person or contact information. Going through all of the other available material takes time, especially if the worksheet contains hundreds or even thousands of entries. MS Excel has the answer to this problem in the form of Filter.

ge

U

ni

Filtering shows the data you wish to see while temporarily hiding the rest. By defining different filters, you can compare data in multiple ways. Thus, Filter can be defined as a facility in Excel that allows a user to shift items in a list according to a set criteria. You can filter text, numbers or dates.

C

am

br

id

There are two types of Filters which are discussed below. 1. Filter: When you apply Filter to a worksheet, grey colour drop-down controls (Filter switches) appear to the right of the column headings of a list. Using these switches you can filter any the data. 2. Advanced Filter: When you apply Advanced Filters to a worksheet, it helps to filter the list based on a specific criteria or to copy the filtered data to a different location.

Filtering Data You need to follow a few steps to apply AutoFilters to the data. They are as following: 1. Create a worksheet and enter the data. 2. Select the data to be filtered. 3. Click on Data tab Sort & Filter group Filter option (Fig. 3.7).

42

Filter option Data tab

Sort & Filter group

Pr e

ss

Reapply

ity

Filter switch

ve rs

Fig. 3.7 Opening the Filter option

C

am

br

id

ge

U

ni

4. The list appears with the drop-down controls or the Filter switches on the right side of the column headings. 5. Click on the Filter switch. All the values for that particular column appear in a list (Fig. 3.8). 6. If you wish to see the details of students in class 5, then select 5 in the ‘Class’ Filter switch (Fig. 3.9). The Filter switch icon changes to a filter icon in the column where the data has been filtered. Moving the mouse on that icon will show the criteria of filtering the contents of that column. 7. Click on the Reapply option in the Sort & Filter group of the Data tab in case the data has been modified and you wish to apply the same filter to the new data.

Fig. 3.8 Selecting filter criteria

Fig. 3.9 Filtered data

43

FACT FILE Initially all the values in a column are selected in the Filter switch drop-down list. You can deselect all of them using (Select All)) and then select the desired value to filter the data on that basis.

Removing AutoFilter Follow either of these steps to remove the filter from the worksheet: Sort & Filter group OR

Clear option

ss

Click on Data tab

Clear option

ge

U

ni

ve rs

ity

Pr e

Click on the Filter icon drop-down list, select Clear Filter From “Department” (in this case) or click on the (Select All) checkbox (Fig. 3.10).

(Select All) option

C

am

br

id

Clear Filter From option

Fig. 3.10 Removing Filter

Custom Filter When a range has to be specified or there is a specific condition to filter the data, you can use the custom filtering in the Filter switch drop-down list. To apply custom filter to the data, you need to follow these steps: 1. Open the worksheet having data to be sorted. Apply Filter to the selected range.

44

ve rs

ity

Pr e

ss

2. Click on the Filter switch on the ‘Total Marks’ column. Select the Number Filters option from the drop-down list. You can choose a criteria for filtering data from the given options or click Custom Filter… in the submenu (Fig. 3.11).

Fig. 3.11 Selecting Custom Filter... option

C

am

br

id

ge

U

ni

3. A Custom AutoFilter dialog box appears (Fig. 3.12), enter or select the desired values. 4. Click on OK. The data is filtered according to the values set in the Custom AutoFilter dialog box (Fig. 3.13).

Fig. 3.12 Custom AutoFilter dialog box

Fig. 3.13 Filtered data

Conditional Formatting Conditional Formatting is a feature in MS Excel 2010 that is used to format cells using specific colour, border, font, etc. based on a specified set of conditions.

45

Applying Conditional Formatting

U

ni

ve rs

ity

Pr e

ss

Follow these steps to apply conditional formatting to the text: 1. Select the range of cells to which conditional formatting is to be applied. 2. Click on the Home tab Styles group Conditional Formatting drop-down list. 3. Select a rule from the drop-down list. For example, select Between… from the Highlight Cells Rules submenu (Fig. 3.14).

ge

Fig. 3.14 Selecting Conditional Formatting option

C

am

br

id

4. The Between dialog box appears. Type the lower and upper limits and select the formatting condition from the drop-down list (Fig. 3.15).

Fig. 3.15 Between dialog box

46

5. Click OK. The selected text will be formatted based on the conditions specified.

Clearing Conditional Formatting

am

br

id

ge

U

ni

ve rs

ity

Pr e

ss

Follow these steps to clear the conditional formatting applied: 1. Click on the Conditional Formatting drop-down list. 2. Select Clear Rules from the submenu; select Clear Rules from Selected Cells if you wish to remove it from the specified cell(s) or Clear Rules from Entire Sheet if you wish to remove it from the entire worksheet (Fig. 3.16). 3. Conditional Formatting will be removed from the desired portion.

C

Fig. 3.16 Clearing Conditional Formatting

TRY THIS

You can copy the Conditional Fortmatting applied to one cell onto the other. Click on the cell with the Conditional Formatting and select Format Painter from the Ribbon. Select the cell(s) to which the formatting is to be applied. The format in this case too can only be applied to the data which falls under the criteria specified for the Conditional Formatting initially.

47

Y AC TIVIT Consider the spreadsheet ‘Birthdays.xlsx’ created earlier. Use Conditional Formatting to colour the cells containing name of your friends whose birthday is in the current month. Can you think of more than one criteria while using Conditional Formatting? Sort this data and then filter it based on the criteria of your choice.

Creating Charts in Excel 2010

ity

Pr e

ss

A chart is a graphical representation of information. A chart often makes it easier to understand the data in a worksheet because users can easily pick up patterns and trends illustrated in the chart that are otherwise difficult to see. There are different types of charts that can be created easily in Excel. A few are given below (Table 3.1).

ve rs

Table 3.1 Chart Types and Their Usage

Chart Type

Usage

Shows data changes over a period of time or illustrates comparison among items

Line chart

Shows trends in data at equal intervals

Pie chart

Shows the size of items that make up a data series proportional to the sum of the items, containing only one data series

Bar chart

Illustrates comparisons among individual items

Area chart XY (scatter) chart Stock chart

Emphasises the magnitude of change over time Shows the relationships among the numeric values in several data series or plots two groups of numbers as one series of xy coordinates Shows highs and lows in stock data

C

am

br

id

ge

U

ni

Column chart

Surface chart Doughnut chart Bubble chart

Shows 3 sets of data in a graphical way

Radar chart

Compares the aggregate values of a number of data series

48

Finds optimum combinations between two sets of data Shows the relationship of parts of several sets of data to the whole.

Creating a Chart in MS Excel 2010

Pr e

ss

Steps to create a chart are: 1. Select the data for which the chart has to be created. 2. Click on the Insert tab. 3. Select the desired chart type from the drop-down lists of different chart types in the Charts group. OR Click on Charts dialog box launcher and the Insert Chart dialog box appears (Fig. 3.17).

Charts dialog box launcher

ve rs

ity

Insert tab

Charts group

ge

U

ni

Insert Chart dialog box

am

br

id

A preview of chart types in the selected category

C

Fig. 3.17 Creating charts in MS Excel 2007

4. The selected chart style is plotted based on the values given and inserted in the centre of the worksheet. 5. Change the location of the chart by dragging it and dropping it at the desired location in the worksheet. Double-click on the chart title and type the chart name. 6. Once the chart has been inserted in the worksheet, three new tabs appear under Chart Tools, i.e., Design, Layout and Format in the Ribbon (Fig. 3.18).

Fig. 3.18 Charts created in MS Excel 2010

49

Components of a Chart The following are the different components of a chart (Fig. 3.19). Chart title Comparison of Household Expenditure 2500

Gridline

2300 2275

1480 1495

1500

Data Label

1275 1230

March April

1000 500

ss

Axis title

Money spent

2000

Food

Electricity

Entertainment

X-axis

ve rs

Axis title

ity

Nature of expenditure

Y-axis

Legends

Pr e

0

ni

Fig. 3.19 Components of a chart

U

Chart Area: The area within which all the components of a chart are present.

ge

Chart Title: The title heading given to the chart.

id

Plot Area: The rectangular area bound by the two axes.

am

br

X-axis: The horizontal axis of the chart. Y-axis: The vertical axis of the chart.

C

Axis Title: The title given to each of the two axes of the chart. Data: The entries used to plot the chart. Legend: The unique colour assigned to the data to make it easily distinguishable. Gridlines: The horizontal and vertical lines extending from the two axes that may or may not be displayed.

Data Label: The label providing additional information about a point on the chart.

50

Formatting a Chart

U

ni

ve rs

ity

Pr e

ss

Different options under the different groups of the Format tab can be used for formatting both the table and its contents. Table 3.2 summarises the list of available options.

ge

Table 3.2 Some formatting options in the Format tab

Group Name

• • Shape Styles

br

id

Select the chart element to be formatted. Format Selection: Launches the Format dialog box to format the selected element of the chart. Reset to Match Style: Removes the custom formatting of the selected chart element and reverts to the style selected initially.

am •

C

Current Selection

• •

Usage

• •

Select a visual style for the chart from the drop-down list. Shape Fill: Fills the selected shape in the graph with the solid colour, gradient, texture, or picture. Shape Outline: Specifies the colour, width and line style of the selected shape in the chart. Shape Effects: Adds a visual effect like glow, reflection, shadow and 3-D rotation to the selected shape in the chart.

51

• • WordArt Styles • •

•

Increase or decrease the chart size by changing the values.

Arrange

Size

ss

•

Selection Pane: For selecting individual objects and changing their order and visibility. Align: Aligns edges of the selected objects.

Pr e

•

Select text in the chart and apply the desired WordArt style to it. Text Fill: Fills the selected text with the selected solid colour, gradient, picture, or texture. Text Outline: Specifies the colour, width and line style for the outline of the selected text. Text Effects: Adds a visual effects like glow, reflection, shadow, 3-D rotation to the selected text.

ity

Layout of a Chart

C

am

br

id

ge

U

ni

ve rs

The Layout tab provides many options for changing the layout and the appearance of a chart in MS Excel 2010. Table 3.3 summarises some of the available options and their uses.

Table 3.3 Some options in the Layout tab

Group Name

Current Selection

52

Usage • • •

Select the chart element to be formatted. Format Selection: Launches the Format dialog box to format the selected element of the chart. Reset to Match Style: Removes the custom formatting of the selected chart element and reverts to the style selected initially.

Analysis Properties

• • • •

Fills or clears the colour in the Plot Area. Chart Wall: Highlights or hides the chart walls. Chart Floor: Highlights or hides the chart floor. 3-D Rotation: Rotates the viewpoint of the chart in the specified direction.

•

Adds or removes different types of trendlines, lines, up/down bars and error bars on the chart.

ge

U

ni

ve rs

ity

Axes: Changes the formatting and layout of each axis. Gridlines: Adds or removes and changes the format of the gridlines.

am

Background

• •

•

C

Axes

ss

•

Chart Title: Adds, removes or positions the text of the chart title. Axis Titles: Adds, removes or positions the text of the axis titles. Legend: Adds, removes or positions the chart legend. Data Labels: Shows, hides or positions the data values in the chart. Data Table: Removes or adds the data table with or without the legend keys

id

Labels

• • • •

Pr e

•

Picture: Inserts picture(s) to the chart from a file. Shapes: Inserts the selected ready-made shape(s) anywhere in the worksheet. Text Box: Inserts a text box anywhere in the sheet.

br

Insert

• •

Chart Name: Specifies the name given to the chart.

Designing a Chart Different options are available in the Design tab to change the design of the chart inserted in the worksheet. Table 3.4 summarises the options available for designing the chart in the worksheet.

TRY THIS Select the data from which you need to create a chart and press F11, the chart will be inserted on a separate sheet titled as chart1.

53

• Data

•

ss

ity

ve rs

•

ni

Type

U

•

Usage Change Chart Type: Changes the type of the chart inserted in the worksheet. Save As Template: Opens the Save As dialog box to save the chart with desired formatting and layout, as a template for future use. Switch Row/Column: Swaps the data over the axis such that the data plotted on the X-axis is shifted to Y-axis and vice versa. Select Data: Opens Select Data Source dialog box to change the data range included in the chart.

ge

Group Name

Pr e

Table 3.4 Some options in the Design tab

Used to change the overall layout of the chart.

Chart Styles

Used to change the overall visual style of the chart.

br

am

•

C

Location

•

id

Chart Layouts •

Opens the Move Chart dialog box to shift the chart to another sheet or tab in the workbook.

Importing Data in MS Excel 2010 Importing data to MS Excel is the process of transferring data from another Excel sheet or other database sources and converting it into .xlsx format. This process eliminates the need to manually enter every item into a new Excel worksheet. Importing data,

54

Fig. 3.20 Charts created in MS Excel 2010

therefore saves the user a lot of time and reduces human error in the best way possible. Follow these steps to import data into an Excel 2010 sheet: 1. Select Data tab. Click on From Text option in the Get External Data group (Fig. 3.20). Import Text File dialog box appears. You can also import data in the Excel sheet from the Web, MS Access and some other sources. You will learn more about MS Access in higher classes. 2. Select the file from its destination folder or type in the name of the file in the File name: box. Click on the Import button.

Pr e

ss

Ensure that in this case the file has a .txt extension that is, it is created in Notepad.

C

am

br

id

ge

U

ni

ve rs

ity

3. The Text Import Wizard dialog box appears (Fig. 3.21). Click on the File origin: drop-down list and select Windows (ANSI). Click on the Next > button.

Fig. 3.21 Text Import Wizard dialog box

4. Mark the desired options in the Delimiters section. Click on the Next > button. A preview of the chart based on the options chosen can be seen in the Data preview section. 5. Select the appropriate column data format in the Column data format section. Click on Finish.

55

U

ni

ve rs

ity

Pr e

ss

6. The Import Data dialog box appears. Specify the Absolute Cell Reference address if the data has to be imported in the current worksheet or in a new worksheet of the same workbook. 7. Click on the OK button. The data will be imported in the current worksheet starting from A1 (Fig. 3.22).

am

GLOSSARY

br

id

ge

Fig. 3.22 Data imported

C

Absolute reference It refers to the actual address of a cell in a specific location. Cell reference It identifies the location of a cell or group of cells in the spreadsheet. Chart It is a graphical representation of information. Filtering It is the process of selecting records from a large amount of data based on certain conditions. Mixed reference It is a cell address which has absolute column and a relative row or absolute row and a relative column. Relative reference It is the address of a cell which is based on the relative position of the cell contained in the formula. Sorting It is the physical rearrangement of data in ascending or descending order.

56

Cell reference is also known as cell address. The three types of cell references are relative, absolute and mixed. Sorting is of two types: ascending and descending. You can filter data in a list according to the set criteria. There are different type of charts in MS Excel 2010, such as Column chart, Line chart and Pie chart. 6. Data can be imported to a worksheet from an external database or Excel sheet. 7. You can also embed pictures or files in MS Excel 2010. YOU ARE HERE

Pr e

ss

3

1. 2. 3. 4. 5.

State true or false.

ve rs

A.

ity

EXERCISE

ni

1. Arranging data either in ascending or descending order is called sorting.

ge

U

2. A worksheet identifies the location of a cell or group of cells in the spreadsheet.

id

3. Filter shows the data we want to see while temporarily hiding the rest.

am

br

4. A chart is a graphical representation of information.

B.

C

5. Data cannot be inserted from any other application software in MS Excel 2010. Match the following. 1. Bar chart

a. The entries used to plot the chart

2. Line chart

b. Specifies the name given to chart

3. Data

c. Used to change the overall layout of the chart

4. Chart layout

d. Illustrates comparison among individual items

5. Chart name

e. Shows trends in data at equal intervals

57

C.

Differentiate between the following. 1. Relative and Absolute Reference 3. Ascending and Descending Orders 5. Chart Area and Plot Area

D.

2. Filter and Advanced Filter 4. Auto Filter and Custom Filter

Answer the following questions. What is a cell reference? Explain all three types of cell references with examples. What is sorting? How many types of sorting is possible? What are charts in MS Excel 2010? Give one advantage of using charts. Give some real-life examples where charts can be used. Write the steps for importing data in MS Excel 2010.

Pr e

ss

1. 2. 3. 4. 5.

ity

LAB WORK

C

am

br

id

ge

U

ni

ve rs

Make an MS Excel 2010 spreadsheet on the average amount of water that each member of your family uses. Analyse and compare your family’s water usage to that of others in the class, and suggest three ways to save water. Measure the usage of water by your family members under the following categories. 1. Bath 2. Shower 3. Brushing teeth 4. Washing hands and face 5. Washing dishes 6. Toilet flushes 7. Drinking water 8. Washing clothes 9. Cooking food 10. Washing car

PROJECT WORK Create an MS Excel 2010 spreadsheet to record the data of recently conducted unit tests/ exams for your class. Enter the following data: Student ID, Name, Class, Section and Marks of all the subjects. Find the total marks and percentage for each student. Using conditional formatting, highlight the marks below 40 with red fill and the percentages above 90 with yellow fill. Now apply Filter to extract the details of all the students who have got more than 75% marks in this examination.

58

MS Office 2016

Updates In MS Excel 2016:

ve rs

ity

Pr e

ss

• Two new groups named ‘Get & Transform’ and ‘Forecast’ have been added in the Data tab. Get & Transform group is used to create a query in Excel while Forecast helps you to understand current trends and predict future trends in just one click.

C

am

br

id

ge

U

ni

• There are six new built-in chart types; they are called Waterfall, Histogram, Pareto, Box and Whisker, Treemap and Sunburst.

59