Flipp Book Class-8 new1

SPELLING AND GRAMMAR You might have noticed that red or blue wavy lines under some words when you are typing text in Ms-Word. A red underline wavy underline indicates a misspelled word and a blue wavy line indicates grammatical mistakes. Often there is not enough time to check a document for spelling and grammar mistakes. Your Microsoft Office program provides tools that can help you cor- rect these mistakes faster. The Spelling & Grammar tool of MS-Word helps you check the spelling of the words in an entire document or a part of the document. To check the spelling and grammar, follow the following steps: Steps to Follow: Step-1 : Place the cursor at the location of document where you want to check the spelling. Step-2 : Click the Review Tab on the Ribbon Step-3 : Click Spelling & Grammar on the Proofing Group. The spelling task pane appears on right side of the document window. Step-4 : The unrecognized word is highlighted and the sugges- tions are displayed in the sug- gestions box. You can choose in the one of the following ways: • Click the correctly spelled word in the suggestions list. • Click Change option to change the word, or Click Change All option to change the word in the entire document. Smart Computer Science Book-8 95

• Click Ignore, if correction is not needed. Or Click Ignore All, if the cor- rection is not needed throughout the document. • Click Add to add the word to the word dictionary. • Clicking on the desired option button, the action is taken then next word is highlighted. Step-5 : Repeat step 4 until the message box appears 'Spelling and Gram- mar check complete. You are good to go !' Step-6 : Click 'Ok' Trick & TIPS You can also make correction for misspelled word by right clicking the word and selecting the desired spelling from the option displayed in the pop-up menu. If no change is needed, click Ignore. Thesaurus Using the thesaurus feature, you can look up synonyms (different words with the same meaning) and antonyms (words with the opposite meaning). Step-1 : Click the Review Tab of the Ribbon Step-2 : On the Proofing Group, Click the Thesau- rus Button Step-3 : The thesaurus task pane will appear with the list of synonyms from the selected word on the right side of the screen. Step-4 : To use one of the words from the list of re- sults or to search for more words, select one of the following: • To use one of the words, select it and click the drop down arrow, then click Insert or Copy. • To look up more related words, click and scroll up and down the scroll bar of Thesaurus task pane. 96 More Features on Ms-Word

Customize Auto Correct You can set up the Auto Correct tool in Word to retain certain text the way it is. To customize AutoCorrect do the follow- ing: Steps to Follow: Step-1 : Click the File Menu. Step-2 : Click the Word Options But- ton. Step-3 : Click the Proofing tab. Step-4 : Click AutoCorrect Options button. Step-5 : On the AutoCorrect Tab, you can specify words you want to replace as you type. Step-6 : Click on 'Add' button. Create a New Default Dictionary Often you will have business or educational special word that may not be rec- ognized by the spelling and/ or grammar check in Word. You can customize the dic- tionary to recognize these words. Step-1 : Click the File menu . Step-2 : Click the Word Options Button. Step-3 : Click the Proof- Smart Computer Science Book-8 97

ing tab. Step-4 : From When Correcting Spelling tab. Click Custom Dictionaries, Step-5 : Click Edit Word List. Step-6 : Type in any words that you may use that are not recognized by the current dictionary, then click the 'Add' button. Step-7 : Finally, click 'Ok' button. PAGE SETUP The page setup refers to modify page margins, orientations and set the paper size, number of columns on the page, page border and shading, etc. It lets you change the structure and layout of pages in a Microsoft Word document. Set the Margin A margin is the space between the text and the edge of your document. By default, a new document's margins are set to Normal, which means it has a one-inch space between the text and each edge. Depending on your needs, Word allows you to change your document's margin size. To change the page margin, follow the following steps: Steps to Follow: Step-1 : Click the 'Page Layout' Tab on the Ribbon. Step-2 : On the 'Page Setup' Group, Click 'Margins'. Step-3 : A drop-down menu will ap- pear. Click the predefined margin size you want. or Step-4 : Click 'Custom Margins' The Page Setup dialog box will appear. 98 More Features on Ms-Word

Step-5 : Adjust the values for each margin, then click OK. To change the Orientation, Size of the Page, or Columns. Step-1 : Click the 'Page Layout' Tab on the Ribbon. Step-2 : On the Page Setup Group, Click the Mar- gins, Orientation, Size, or Columns drop down menus. Step-3 : Click the appropriate choice. Trick & TIPS You can also set these properties from the 'Page Setup' dialog box. Apply a Page Border and Color To apply a page border or color, do the following: Smart Computer Science Book-8 99

Steps to Follow: Step-1 : Open the Page Setup dialog box. By navigating to the Page Lay- out tab and clicking the small arrow in the bottom-right corner of the Page Setup group. Step-2 : Click the 'Borders' option button. You get the Borders and Shad- ing dialog box. Step 3 : Adjust the parameters as you need from the dialog box. BREAKS Adding breaks to your document can make it appear more organized and can improve the flow of text. Depending on how you want to change the pagination or formatting of your document, you can apply a page break or a section break. Insert Page Breaks Page breaks allow you to move text to the next page before reaching the end of a page. You might use a page break if you're writing a paper that has a title 100 More Features on Ms-Word

page or a bibliography to ensure it starts on a new page. To insert page break, follow the following steps : Steps to Follow Step-1 : Place the insertion point where you want to create the break. Step-2 : On the Insert tab, click the Page Break command. Alternatively, you can press Ctrl+Enter on your keyboard Step-3 : The page break will be applied to the document, and the text will move to the next page. Insert Common Header and Footer Information The header is a section of the document that appears in the top margin, while the footer is a section of the document that appears in the bottom margin. Headers and footers generally contain additional information such as page numbers, dates, an author's name, and footnotes, which can help keep lon- ger documents organized and make them easier to read. Text entered in the header or footer will appear on each page of the document. To insert Header and Footer information , follow these steps: Steps to Follow: Step-1 : Click the Insert Tab on the Ribbon. Step-2 : Click the Header or Footer drop down menu from 'Header & Footer' group. You will see the Header gallery with different de- signs. Step-3 : Click the Blank header style or scroll down to choose any style or scroll down to choose any oth- er style. Smart Computer Science Book-8 101

Step-4 : Now, the Design tab will display under Header/Footer TOOLS on the Ribbon. This tab appears only when your cursor is in the header or footer area. Step-5 : The cursor will be placed in the header section of the document. Type the header text. In some designs, you need to fill in the blanks for information such as document title and author's name. Step-6 : Now, click Go to Footer in the Navigation group. The footer area will appear at the bottom of the document. Or You can click Footer drop down menu in the 'Header and Footer group to display the Footer gallery. Then, click the desired Footer style. Step-7 : Type the Footer text and fill in the blanks as required. Step-8 : Now, click close button to close the 'Header & Footer' in the rib- bon or you may press Esc key. Fast Access Double-click anywhere on the top or bottom margin of your document to active Header & Footer text box. Trick & TIPS Header and Footer only can be seen in the Print Layout view. If you are in different view, click the 'Print Layout' view in the 'Views' group of 'View' tab of ribbon. If you want to use a different header and footer for the first page or want to hide header and Footer fro the first page, then, click and select the check box for the 'Dif- ferent First Page option. Adding Date/Time into Header/Footer Sometimes it's helpful to include the date or time in the header or footer. For example, you may want your document to show the date when it was created. On the other hand, you may want to show the date when it was printed, which you can do by setting it to update automatically. This is useful if you frequently update and print a document because you'll always be able to tell which ver- 102 More Features on Ms-Word

sion is the most recent. To insert date/me, follow the following steps: Steps to Follow: Step-1 : Double-click any- where on the header or footer to unlock it. Place the insertion point where you want the date or time to ap- pear. Step-2 : The Design tab will appear. Click the Date & Time command. Step-3 : The Date and Time dialog box will appear. Select the desired date or time format. Step-4 : Check the box next to Update Automatically if you want the date to change every time you open the document. If you don't want the date to change, leave this option unchecked. Step-5 : Click OK. Step-6 : The date will appear in the header. Click Close Header and Footer in the ribbon or press Esc Adding Page Number Word can automatically label each page with a page number and place it in a header, footer, or side margin. When you need to number some pages differ- ently, Word allows you to restart page numbering. To add page number, follow the following steps: Steps to Follow: step-1 : Double-click anywhere on the header or footer to unlock it. • If you don't already have a header or footer, you can double- Smart Computer Science Book-8 103

click near the top or bot- tom of the page. • The Design tab will appear on the right side of the Rib- bon. Step-2 : Click the Page Number com- mand from the Header & Footer group. In the menu that appears, hover the mouse over Current Position and select the desired page num- bering style. Step-3 : Page numbering will appear. Step-4 : To edit the font, font size, and alignment of page numbers, select a page number and click the Home tab. Word's text formatting op- tions will appear. Step-5 : When you're finished, press the Esc key. Now, page numbering is formatted. Trick & TIPS • To edit header and footer, just double click on the header or footer, then edit or delete the text as required. • The command on the DESIGN tab under HEADER & FOOTER TOOLS allow you to create various headers and footers. For example, you can have different headers on odd and even pages. 104 More Features on Ms-Word

Lab Activities 1. Sonika Rai has been as- signed a task by her class teacher to submit the sample article to participate in the easy writing competition on the occasion of 'Environment Day'. The article submitted by Sonika is as given you. You are also asked to format the article in the same appearance using the following parameters: a. Page setup Parameters: • Top Margin :1.5\" • Bottom Margin: 1.5\" • Left Margin :1.5\" • Right Margin 1.5 \" • Orientation : portrait b. Insert Header and Footer parameters • Odd page : Celebration of Environment Day • Even Page : World Environment Day. • Insert date and Time in the Header c. Insert Page numbers d. Check the spelling and grammar in your document World Environment day. e. Customize your Auto correct if you type Envir should be changed Environ ment, Stu = student, Nep=Nepal as you like. f. Apply page border and color parameters • Top :4 p • Bottom :4 p • Left : 4 p • Right : 4 p Smart Computer Science Book-8 105

World Environment Day Introduction World Environment Day is celebrated annually on June 5th and was created to inspire people around the globe to become active about the environment and learn more about ways they can help to ensure the future of our planet is safe. Lets find out more about World Environment Day! What is World Environment Day? The very first World Environment Day took place in 1973, inspired by the United Nations Conference on the Human Environment that took place the year be- fore starting on June 5th. Each year the United Nations pick a theme and a host city where environmental top- ics can be discussed , followed by different kinds of exhibits and displays to promote environmental aware- ness, in many ways very similar to Earth Day. How Do You Celebrate? Even though the United Nations pick a specific host city every year, people around the world still cel- ebrate World Environment Day in their hometowns with parades, concerts, clean up and recycling ini- tiatives, tree planting groups and all kinds of green action initiatives that are meant to promote and in- spire people in every community to work towards maintain our beautiful planet and battling pollution. 106 More Features on Ms-Word

INSERT A COVER PAGE Some times, it is necessary to design the cover page of document. MS-Office word supports to insert varieties of cov- er pages: Steps to Follow: Step-1 : Click the Insert Tab on the Ribbon. Step-2 : Click the Cover Page drop down button on the Pag- es Group. You get the pre- defined cover page template. Step-3 : Choose a desired style for the cover page. INSERT A BLANK PAGE To insert a blank page, do the following: Step-1 : Click the Insert Tab on the Ribbon. Step-2 : Click the Blank Page Button on the Page Group. MACRO Macros are advanced features that can speed up editing or formatting in a Word document. They record sequences of menu selections that you choose so that a series of actions can be completed in one step. To record a Macro, do the following Steps to Follow: Step-1 : Click the View Tab on the Ribbon. Step-2 : Click Macros dropdown option. Step-3 : Click Record Macro. Step-4 : Enter a name (without spaces) as given in picture. Step-5 : Click whether you want it assigned to a button (on the Quick Access Smart Computer Science Book-8 107

Toolbar) or the keyboard (a se- quence of keys) Step-6 : To assign the macro a button on the Quick Access Toolbar: Click on Button. Step-7 : Under the Cus- tomize Quick Access Toolbar, select the docu- ment for which you want the Macro available. Step-8 : Under Choose Commands: Click the Macro that you are recording. Step-9 : Click on Add. Step-10 : Click 'OK' to begin Recording the Macro. Step-11 : Perform the actions you want recorded in the Macro Step-12 : Click drop down button of Macros Step-13 : Click Stop Recording Macros Step-14 : To assign a macro button to a keyboard shortcut: • Click Keyboard. • In the Press New Shortcut Key box, type the key sequence that you want and click As- sign. • Click Close to begin record- ing the Macro. • Perform the actions you want recorded in the Macro • Click on Macros drop down option from the view tab. • Click Stop Recording Mac- ros. 108 More Features on Ms-Word

Running a Macro Running a macro depends on whether it’s been added to the Quick Access Tool bar or if it’s been given a Keyboard Shortcut or from Step-1 : To run a Macro from the Quick Access Tool- bar, simply click the Macro Icon. Step-2 : To run a Macro from the Keyboard shortcut, simply press the keys that you have pro- grammed to run the Macro. CREATE A TABLE OF CONTENTS The table of contents of the document which shows the what topics , sub top- ics are included with page number. Steps to Follow: Step-1 : Apply the heading style of the document like headin 1, heading 2, respectively which headings come in the table of content. Step-2 : Place the cursor where you want the Table of Contents in document. Step-3 : Click the References Tab. Step-4 : Click the Table of Contents drop down button from 'Ta- ble of content' group. Step-5 : Select the desired 'Table of Content' style from the 'Built-in Table of Content Gallery'. Update Table of Contents If you have added or removed headings or other table of contents entries you can update by: Step-1 : Apply headings or mark individual entries as di- rected above. Step-2 : Click the References Tab in the Ribbon. Step-3 : Click Update Table. Smart Computer Science Book-8 109

Delete Table of Contents To delete a table of contents: Step-1 : Click the References Tab on the Rib- bon. Step-2 : Click Table of Contents drop down button option. Step-3 : Click Remove Table of Contents. Lab Activities 1. After completing the project work of social studies, you have to pre prepare the cover page . Now, insert a new blank page and insert a suitable predefined cover page template then edit your con- tent. If you are not getting suitable pre- defined template, design yourself. 2. Type the following short paragraph and heading and create a keyboard macro for main heading, sub heading and button macro for paragraph formatting. After running all three macros, your document should look as follows: HISTORY OF COMPUTER Introduction We are living in the computer age today and most of our daily activities cannot be ac- complished without using computers. Sometimes knowingly and sometimes unknowingly we use computers. Computer has become an essential and multipurpose tool. Most of the people used computer for a calculation or a computation, but actually it is much more than that. Such device can be defined that \"Computer is an electronic device for perform- ing arithmetic and logic operation and produces meaning full information.” 110 More Features on Ms-Word

Lab Activities 1. Prepare the document in MS-Word as given in the electronic version in practical material CD, other wise download from our website www.memorica publication.com and create table of content as given format. Smart Computer Science Book-8 111

BULLETED AND NUMBERED LISTS Bulleted lists have bullet points, numbered lists have numbers, alphabet, roman num- bers , and outline lists combine numbers and letters depending on the organization of the list. The list which is used to create using bullets is called unordered list and list created by using numbers called order list. To add a list to existing text: Steps to Follow: Step-1 : Select the text you wish to make a list. Step-2 : On the Home Tab, from the Paragraph Group, Click the Bullets or Number Lists drop down option. Step-3 : Select type of bullet or number list. To create a new list: Steps to Follow: Step-1 : Place your cursor where you want the list in the document. Step-2 : On the Home tab of Para- graph group, Click the drop down button of Bulleted or Numbered Lists button. Step-3 : Select type of bullet or num- ber list. Step-4 : Begin typing. 112 More Features on Ms-Word

Nested Lists A nested list is a list with several levels of indented text. It is also called multi level list. To create a nested list: Steps to Follow: Step-1 : Select the text you wish to make a list. Step-2 : On the Home tab of Para- graph group, Click the drop down button of Bul- leted or Numbered Lists button. Step-3 : Select type of a option list. Step-4 : Click the Increase or Decrease Indent button. Formatting Lists The bullet image and numbering format can be changed by using the Bullets or Numbering dialog box. Steps to Follow: Step-1 : Select the entire list to change all the bullets or numbers, or Place the cursor on one line within the list to change a sin- gle bullet. Step-2 : Right mouse click Step-3 : Click the arrow next to the bullet or number list and choose a bul- let or numbering style. Smart Computer Science Book-8 113

INSERT FOOTNOTE/ENDNOTE Some types of academic writing utilize footnotes. Foot notes and end notes are used to make more explanatory of a word in the document. A footnote is a note at the bottom of a page in a document which provides more detailed information about something that is mentioned on that page, rather than Endnote comes at the end of page of the document. To insert a footnote or endnote, do the following: Steps to Follow: Step-1 : Click the References Tab on the Ribbon. Step-2 : Place the cursor with the text where you would like the superscript number to appear whose Foot note or End Note to be displayed. Step-3 : Click Insert Footnote (or Insert Endnote depending on your needs) Step-4 : Begin typing the footnote. 114 More Features on Ms-Word

Lab Activities 1. Harpita Sing has missed the class of creating list. As you study the create list (Bullet list and number list) Prepare the follow- ing given list of item using Ms-Word in the computer Lab, then help to Harpi- ta sing, how to create different type so list in MS-Word. Green House Computer ITEMS • Ramila Shrestha • Characteristic 1) Fruits • Pemba Sherpa  Speed a) Banana • Shanti Gupta  Automatic • Rahul Khadka  Versatile b) Apple • Manoj Dev  Diligence c) Orange • Rabina Magar  Accuracy 2) Animal • Sabin Pariyar a) Cow • Manoj Bista • Limitation b) Dog  Need instruction c) Tiger  No intelligence 2. Prepare the following document and insert the footnote as given. And also insert the end note. Smart Computer Science Book-8 115

Lab Activities 116 More Features on Ms-Word

Points to Know • Quick Styles are combinations of different formatting options and are displayed in a thumbnail in the Quick Style gallery and Style Window. • Table is a collection of row and columns where data is arranged in the tabular form. • Generally picture refers to image captured by camera or drawn ourself. • Smart Art is a collection of graphics you can utilize to organize information with- in your document. • A watermark is a transparent image that appears behind the primary text in a document. • The page formatting refers to modify page margins orientations and set the paper size, number of columns on the page, etc. • Macros are advanced features that can speed up editing or formatting you may perform often in a Word document. • Table of Content shows the what topics, sub topics are included with page number. • The list which is created using bullets is called unorder list and list created by using numbers is called order list. • A nested list is a list with several levels of indented text. • Foot notes and end notes are used to make more explanatory of a word in the document. Terms to Know Footnote : Text note placed at the bottom of a page. Endnote : Text note placed at the end of a document. Margin : Space separating text or other elements from the edge of the paper A program to allow the user to communicate and use the computer or program. Text Wrap : Surround a picture or diagram with text. Watermark : Appearance of image or text with shades of lightness/darkness. Smart Computer Science Book-8 117

Worksheet Objective Questions 1. Fill in the blanks: a. ....................................... are set of styles that are designed to work to- gether to create an attractive document. b. .............................. is a collection of row and columns. c. ............................ and .......................... feature allows to make correc- tion spelling and grammatical mistakes. d. ................................. formatting refers to modify page margins, orienta- tions, set the paper size, etc. e. ..................................... helps to speed up editing or formatting in the document. f. ................................ of .............................. of the document shows the topics and sub topics with page number. g. .................................... is displayed in the end of the page. h. .................................. is displayed in the end of the document. 2. State whether the following statements are true(T) or false (F): a. ClipArt can be downloaded from internet. b. Graphics can be resized by single clicking the image. c. A water marks appears infront of the primary text in a document. d. You can set up the AutoCorrect tool yourself to retain certain text. e. Set the number of columns in the document is a kind of page formatting. f. You can insert page break in only specified place of the document. g. The list which is created using bullets is called order list. 118 More Features on Ms-Word

h. Nested list is also called multilevel list. 3. Choose the correct option: a. The Spelling & Grammar option is available on the tab. i) Home ii) Review iii) Insert iv) None of these b. Which option is used to get a list of synonyms? i) Find ii) Replace iii) Thesaurus iv) None of these c. Which option will you click from spelling task pane to change a word in an entire document ? i) Change All ii) Change iii) Add iv) None of these d. Which of the following option is available on the HOME tab? i) Change Case ii) Find iii) Replace iv) All of these e. Footnote option is available on the on the tab. i) Insert ii) Reference iii) Replace iv) None of these f. How many types of Macro can you create in MS-Word? i) One ii) Two iii) Three iv) None of these g. In which tab you get the Macro? i) View ii) Developer iii) Both of these iv) None of these h. Page number option is within the group of : i) Links ii) Page iii) Header & Footer iv) None of these i. Watermark option is under the tab of i) Links ii) Page iii) Header & Footer iv) None of these j. SmartArt is under the tab of i) Home ii) Design iii) Insert iv) None of these k. Alternate command of Page Breaks i) Ctrl+Enter ii) Shift+Enter iii) Alt+Enter iv) None of these Smart Computer Science Book-8 119

Descriptive Questions 1. Answer the following questions: a. What is ClipArt? b. How do you get ClipArt image? c. What do you mean by text warp? d. What is SmartArt? e. What does SmartArt include? f. How do you resize graphics image? g. What is watermark? h. What is quick style? i. Which feature do you use to make correction in the MS-Word. j. What does page formatting include? k. What is cover page? l. Where is cover page drop down option located? m. How do you get the cover page built-in gallery? n. What is macro? When do you use macro? o. What do you mean by table of contents? p. When do you use bullet and number list? q. What do you mean by formatting list? r. Why do you use footnote and end note? Analytical Case Study Questions: 1. Rampati Chaudhary was asked to create a list of popular cities of Nepal in MS-Word. She created bulleted list. a. Which tab and group have the option of creating a bulleted list ? b. Can she convert the list to a number list ? If it is possible, how ? 120 More Features on Ms-Word

2. Study the given picture, then answer the following questions: . a. How do you get this task pane?. b. If you are willing to add more words which are not supported by existing dictionary of MS-Word, Which option will you select ? c. Why do you click the Ignore All op- tion button? d. Differentiate between Change and Change All options. e. Why do you click Ignore button option ? 3. Priyanka has written a narration on re- cently watched movies. She has been asked to change some words with same meaning and add more new words in the dictionary which are not supported by MS-Word dictionary. a. Now, Which feature of MS-word will she use? b. Which tab and group are clicked to change the words with same meaning ? c. How does she add more words in the dictionary 4. Sumitra has prepared a project work of computer science. Now, she has to prepare table of content . She has define three types of title. Main title, medium title and small title in the table of content. a. How does she define the title ? b. Which tab and group is clicked to get the table of content ? c. She wants to edit text and title. After editing, the name of tile and page number may varies. Now, how should she handle this problem? Smart Computer Science Book-8 121

5. Samikshya has prepared a project work on computer science. She has for- got to insert header, footer and page number. a. Which tab and group are clicked to perform these tasks ? b. How does she do, to insert page number in right side of the document in the odd page and left hand side of the document in even page with name of proj- ect work? c. How des she insert footer with date and time. The date and time must be up- dated automatically, when open the document. 6. Sidhartha has to make new setup for the following . How can you help him. a. To crete new default dictionary b. To set the auto correction. 7. Supriya has created a document for the School notice board about the pic- nic program. She has been asked to put the watermark in the document. When she gets the following dialog box to insert the watermark, how does she do the following: . a. If She has to put the school 's emblem as a watermark ?. b. Why should she use Scale option? c. If she removes the check mark of Washout, what dif- ference is seen ? d. If she has to remove the school emblem and insert new text base watermark with sky blue color, what pa- rameters should be adjusted e. If she has unchecked the Semitransparent option, what difference is seen? f. If she has checked the horizontal radio button, what difference is seen? 122 More Features on Ms-Word

Lab Project Work-1 1. Prepare the following marks sheet. Gyan Mala Secondary School Gyan Mala Birgunj-4 Emai:[email protected] Ph: 029448967 Student Progress Report ............................. Terminal Examination 20............. Smart Computer Science Book-8 123

Lab Project Work-2 2. Prepare the following chart : na e Season & Months e 1. Spring 3. Prepare the given list with text borders : a. March b. April c. May 2. Summer a. June b. July c. August 3. Autumn a. September b. October c. November 4. Winter a. December b. January c. February 124 More Features on Ms-Word

Lab Project Work-3 4. Prepare the following Certificate Gyan Mala H. Secondary School Gyan Mala Birgunj-4 Emai:[email protected] Ph: 029448967 Certificate of Achievement Master/Miss : Class : House : For Securing Position in held on the date : (Program Director) (Chairperson) (Principal) Smart Computer Science Book-8 125

CHAPTER 7Advance Features in Ms-Excel Juna : Is it possible to prepare This chapter includes: the result sheet in Ms- Excel ? • Conditional Format- ting Muna : Of course, why not? You can make calculation • Sorting data. with finding division, fil- • Filtering ter and separate data • Advance in IF function as you like. • Charts. • Sparkline Juna : Rather than this are there any more features in MS-Excel? Muna : There are a lot of special commands like for arrang- ing data in ascending or descending order, you can format data conditionally as you like, You can present your data with different type of chart. CONDITIONAL FORMATTING: Conditional formatting refers to format your worksheet in the basis of condition which allows to format your worksheet when specified condition is matched . Conditional formatting includes Highlight cell rule, Top bottom rule, Data bar, Color Scale, Icons sets. You can create, edit, delete, and view all conditional formatting rules in the workbook. 126 Advance Features in Ms-Excel

To perform the conditional formatting, do the following: Steps to follow: Step-1 : Prepare the worksheet. Step-2 : Select the range of cell to format. Step-3 : On the 'Home' tab, from 'Style' group, Click on the ‘Condi- tional Formatting’ drop down option but- ton. You will get the ‘Conditional Format- ting’ menu as given. Step-4 : Select ‘Highlight Cell Rules’. Step-5 : Select and click the criteria or condition. Here is selected ‘greater than’. You get the small dialog box. Step-6 : Type argument value. Here, argument value is typed 95. Select the formatting cell color. Step-7 : Click on ‘Ok’ button Step-8 : Now, you can see matched cell will be highlighted with selected color. Smart Computer Science Book-8 127

DATA SORTING Arrangement of data in alphabetical ascending or descending order is known as sorting. Sorting helps you quickly visualize, understand, and find the data. Single Column Sorting This feature allows you to sort the data in the basis of single column. To per- form the single column sorting, follow the following steps. Steps to Follow: Step-1 : Prepare the worksheet. Step-2 : Select any cell in which column you sort. Step-3 : Click the 'Home' tab. Step-4 : In the 'Editing' group, Click the 'Sort & Filter' drop down option. Step-5 : Select option A to Z or Z to A for sorting. Lets see, data is sorted. Multiple Column Sorting This feature allows you to sort the data in the basis of more than one columns. To perform the multiple column sorting, follow the following steps. Steps to Follow: Step-1 : Select whole range of data or click on any cell. Step-2 : Select any cell in which column you sort. Step-3 : Click the 'Home' tab, Step-4 : In the 'Editing' group, Click the 'Sort & Filter' drop down option. Step-5 : Select 'Custom Filter..' option for sorting. You get Sort dialog box. or Click the 'Data' tab . In the Sort & Filter group, click Sort. You get 128 Advance Features in Ms-Excel

the following Sort dialog box. Step-6 : Deselect 'My data has headers' checked mark, if you do not have header of data. Otherwise leave as it is by default. Step-7 : Under column section , select the first column to sort, in this dialog box, 'Department' is selected. Under the Sort on section, select values, and under Order section, select order type. In this dialog box, A to Z is selected. Step-8 : Click 'Add Level' to sort next column. An new row is added. Step-9 : Select the second column for sorting in the Column section, select values in the 'Sort' section then select order type in 'Order' sec- tion Step-10 : click the 'Ok' button. Trick & TIPS • You can also sort your data from the 'Data' table • No need to select a column or a data range in a data list. The Sort command automatically sorts the entire list, in the basis of field of the cell selected. • You have to be careful while sorting, if there are formulas in the cells. Smart Computer Science Book-8 129

Lab Activities 1. Computer science teacher has assigned the following tasks for stu- dents after teaching the lesson : a. Prepare the given worksheet and sort the data in ascending order on the basis of name. b. Make the conditional format- ting with dark blue color whose marks is above 90 in the cell range C2:G6. 2. Saloni is assigned a work by her class teacher to prepare the work- sheet and perform as mentioned: a. Sort the name list in two columns one is Class in ascending order and an- other is Bus no. in de- scending order so that she can see the name list at a glance in the bus. b. Perform the condition- al formatting in the cell range C2:C8 with blue color whose bus No. is 2. 130 Advance Features in Ms-Excel

DATA FILTERING Data Filtering is a strong feature of MS-Excel. It allows us to find and work with selected data in quick and easy method. It does not rearrange data un- like Sorting. It hides the unnecessary row and column temporarily. Specially, it blocks or hide the data that yo don't want to see. The filtered data range dis- plays only those rows/columns that meet the condition or criteria you specify. There are two commands for filtering data range: a. Filter (for simple criteria) b. Advance Filter (for complex criteria with multiple condition) We discuss the simple Filter in this chapter. To perform the simple filter, follow the following steps: Steps to Follow : Step-1 : Prepare a worksheet as given. Smart Computer Science Book-8 131

Step-2 : Select the data range of A1:E14 Step-3 : On the Data tab. In the 'Sort & Filter' group, click 'Filter' option. or Click the HOME tab. In the Editing Group, click the Sort & Filter and select Filter option in the drop down list. Drop-down menu arrows will appears next to each column heading. Step-4 : Click the drop down arrow next to desire column on which you fil- tered data. Here, it is clicked drop down arrow next to the Section column heading. You get the varieties of menu option. Step-5 : Click the Select All to clear all the check boxes, and click the check box next to desire data value. Here, it is selected 'A' Step-6 : Click OK. Step-7 : Now, you get the data sub set of only 'A' section. 132 Advance Features in Ms-Excel

Now, you get the following subset of filtered data. Trick & TIPS • If you have make any modification in the data, click Reapply to filter the new data. • To remove all the filters, click again Filters button. To apply Filter in multiple column Filters are cumulative, which means you can apply multiple filters to help nar- row down your results. In this example, we've already filtered our worksheet to show the list of only section 'A', and we'd like to narrow it down further to only show the 8, 9 and 10 class, lets follow the following steps: Steps to follow: Step-1 : Click the drop-down arrow for the column Class. Step-2 : The Filter menu will appear. Step-3 : Click the Select All to clear all the check boxes, and click the check box next to desire data value. Here, it is selected '8',9 & 10 Smart Computer Science Book-8 133

Step-4 : Now, click Ok button. You get the following sub set of filtered data Custom Filtering MS-Excel supports AutoFilter to filter data based on certain specific condi- tions which is called custom filtering. For example: if you are willing to list the those data having more than num- ber of girls 18, in this case, you have to use Custom Filtering: To solve this problem, do the following: Steps to Follow: Step-1 : Select the data range of A1:E14 Step-2 : On the Data tab. In the 'Sort & Filter' group, click 'Filter' option. 134 Advance Features in Ms-Excel

Step-3 : Clicked drop down arrow next to the No. of Girls column heading. You get the menu option. Step-4 : Point to the Number Filters in the in the AutoFilter drop down menu of the column. A sub menu appears containing varieties of comparison operators. Step-5 : Click the Custom Filter... option. Now, open the Custom AutoFilter dialog box. Smart Computer Science Book-8 135

Step-6 : Define the parameters as given in the dialog box. Step-7 : Click the Ok button. You get the following sub set of data list. Highest or Lowest Values Filtering: Filtering features allows to list the highest or lowest values from the large set of data. For example, if you want to display top 3 data set values from the No. of Girls column of your worksheet, do the following: Steps to Follow : Step-1 : Click any cell having number data of the worksheet. Step-2 : Click the DATA tab, In the Sort & Filter group, click the Filter op- tion. Step-3 : Click the drop down button next to the any column. Step-4 : Point to the Number Filters, then click the Top 10 option from the sub menu. The Top 10 AutoFilter dialog box is displayed. Step-5 : Type 3 in place of 10. Step-6 : Click the Ok button. Now, your first top three values of no. of girls column records are displayed as given: 136 Advance Features in Ms-Excel

Filtering by Searching Data Sometimes, such types of case may come, where you have to search specific value out of thousands of row of data, in such a case you have to use the technique of Filtering by Searching Data. For example: If you want to display list of rows having section 'A' using the Filtering by Searching Data, do the following : Steps to Follow : Step-1 : Click any cell of the worksheet. Step-2 : Click the DATA tab. in the Sort & Filter group, click the Filter op- tion. Step-3 : Click the drop down button next to the Section column. Step-4 : In the AutoFilter drop-down menu of Section column, Type A in the Search box. Smart Computer Science Book-8 137

Step-5 : Click Ok. Now, you get the following sub set of data. 138 Advance Features in Ms-Excel

Lab Activities 1. Marketing Manager of Sushma Trading Pvt. Ltd. wants the fol- lowing worksheet of the sales report with the filtered sub set in different condition: a. List out the report of only 1st quarter. b. List out the Report of 1st quarter of Udaypur and Siraha district. c. List out the report of 3rd quarter and 1st quarter. d. List out the report of above 50,000,00 and below 100,000,00. e. List the report of first top three sales by sales amount. f. List the report of last three bottom sales by value g. Search and list the report of Morang district. Smart Computer Science Book-8 139

IF( ) FUNCTION The IF function can perform a logical test and return one value for a TRUE result, and another for a FALSE result. It is a built-in function in Excel that is categorized as a Logical Function. For example: to \"pass\" scores above 70: =IF(A1>70,\"Pass\",\"Fail\"). More than one condition can be tested by nesting IF functions. The IF function can be combined with logical functions like AND and OR. Syntax of single IF : IF(logical_test, value_if_true, value_if_false) Example: = IF(D2>= 100, \"OK\", \"NOT OK\") = IF(D3 >=100, \"OK\", \"NOT OK\") = IF(D4>= 100, \"OK\", \"NOT OK\") • Here, condition is matched in D2 and D3, result is OK, where condition is not matched in D4, result is NOT OK. In this way IF test the data and give the out- come. Syntax of Nested IF : IF(logical_test, value_if_true, IF(logical_test, value_if_true, IF(logical_test, value_if_true, ..............., value_if_false))) Example: =IF(A2<40,\"Grade-D\",IF(A2<50,\"Grade-C\",IF(A2<80,\"Grade-B\",\"Grade- A\"))) 140 Advance Features in Ms-Excel

• Here, C2 is matched, it has given the result Grade D. In this way, according to the matched the test value, result is displayed. Combining the IF function with other LOGICAL Functions Quite often, you will need to specify more complex conditions when writing your formula in Excel. You can combine the IF function with other logical func- tions such as AND, OR, NOT, etc. Let's explore this further. AND function The IF function can be combined with the AND function to allow you to test for multiple conditions. When using the AND function, all conditions within the AND function must be TRUE for the condition to be met. This comes in very handy in Excel formulas. Step-1 : Select cell D2 and enter the following formula. =IF(AND(A2>10, B2>5), \"Correct\", \"Incorrect\") =IF(AND(A3>10, B3>5), \"Correct\", \"Incorrect\") • In the first case, it returns FALSE because the test value in cell B2 is not higher than 5. As a result the IF function returns Incorrect. • In the first case, it returns TRUE because the value in both cell A3 and B3 are higher than the 7 and 5 and . As a result the IF function returns correct. Smart Computer Science Book-8 141

OR Function The OR function returns TRUE if any of the condition is TRUE and returns FALSE if all conditions are false. Step-1 : Select cell E2 and enter the following formula. =IF(OR(A2>10, B2>5), \"Correct\", \"Incorrect\") =IF(OR(A3>10, B3>5), \"Correct\", \"Incorrect\") =IF(OR(A4>10, B4>5), \"Correct\", \"Incorrect\") • In the firs case, the OR function returns TRUE because the value in cell A2 is higher than 10. As a result the IF function returns Correct. • In the firs case, the OR function returns TRUE because the value in cell A3 and B3 both are higher than 10. As a result the IF function returns Correct. • In the third case the OR function returns FALSE because the value in cell A4 and B4 both are not higher than 10. As a result the IF function returns Incorrect. NOT Function The NOT function returns TRUE if the condition is FALSE and returns FALSE if condition is TRUE. Step-1 : Select cell D2 and enter the following formula. =IF(NOT(B2>10), \"Correct\", \"Incorrect\") =IF(NOT(B3>10), \"Correct\", \"Incorrect\") • In the first case, the NOT function returns FALSE because the value in cell B2 is below 10. As a result the IF function returns 'Correct'. 142 Advance Features in Ms-Excel

• In the second case, the NOT function returns TRUE because the value in cell B2 is above 10. As a result the IF function returns 'Correct'. Lab Activities 1. The Computer Science teacher of Star Shining School has as- signed the task to the students to use the IF () function in fol- lowing worksheet to perform the given tasks. a. Find the result, if percentage is above 40 'Pass\", otherwise failed. b. Find the grade in the basis of following condition. Percentage Grade <40 D <50 C <80 B <100 A a Smart Computer Science Book-8 143

Lab Activities 1. At the Sunagava English School, Computer Science is an optional subject. To study the computer, stu- dents should meet the some crite- ria. So the Class teacher of class eight has assigned a task to Sum- nima class captain to prepare the following worksheet and find the student who are eligible and who are not eligible to study Computer Science. If any one subject marks is below 50 out of these three subjects, they are not eligible to study the computer, otherwise, they are eligible. 2. Monika has been assigned a task to prepare the following worksheet and find out who can and who can not cast vote for School captain election. If age is not below 10, they can cast vote otherwise cannot cast vote 144 Advance Features in Ms-Excel