402_IT_X (1) - PDF - Advanced Writer Features

3 CHAPTER Advanced Features of Writer As we all know that LibreOffice Writer is a word processor that provides a variety of features. You have already learned some of the features, such as formatting, editing, inserting tables and images, styling the text and inserting...

3 CHAPTER Advanced Features of Writer As we all know that LibreOffice Writer is a word processor that provides a variety of features. You have already learned some of the features, such as formatting, editing, inserting tables and images, styling the text and inserting graphics in the document. In this chapter, you will be learning some advanced features of LibreOffice Writer that will be used to create professional documents. To give the listing of the contents of the document, we can use the Table of Contents feature, which is based on different types of heading styles. LibreOffice Writer also provides templates to create professional documents. When multiple users are working on a single document, the Track Changes feature can be used to keep a track of the editing being done by each user. Table of Contents In Class IX, you had learned to insert tables in a document, where the data was represented in a row and column format. In this chapter, we will discuss Table of Contents (ToC). You must have seen table of contents in the beginning of your books. It contains a list of topics and subtopics that have been covered in the book along with page numbers. Table of Contents, allows to insert an automated table of contents in a document. The entries or contents of this table are automatically taken from the headings and sub headings of the document. Also, these contents are hyperlinked in the table. So by clicking on any topic in the table of contents, we can navigate directly to the selected topic. But before creating table of contents, you need to first understand the concept of hierarchy of headings. 2023-24  Hierarchy of Headings LibreOffice Writer supports up to 10 levels of headings H1 to H10. These headings are applied to the headings of the document. The following process will demonstrate how to apply these headings to the headings in the document. Step 1. Create a new document in LibreOffice Writer and enter the text Heading 1 to Heading 10 as shown in Fig. 3.1. Step 2. Click on the Styles button from the right side bar. Click on the Paragraph Styles and then Headings. The Styles dialog window will display the list of headings from Heading 1 to Heading 10 as shown in Fig. 3.1. Step 3. Place the cursor on Heading 1 as typed in the document. Fig. 3.1: Hierarchy of 10 Headings Then click on the Heading 1 from the Styles dialog window. Step 4. Repeat Step 2 for the words Heading 2 to Heading 10. Step 4. Observe that Heading 1 to Heading 10 is applied to the words Heading 1 to Heading 10 of the document and these headings have changed as per the applied style as shown in Fig. 3.2. Observe the change in Figs. 3.1 and 3.2. Once the desired heading styles are applied in the document, the same Fig. 3.2: Document after applying 10 Headings hierarchy will be reflected in the table of contents also. Creating a Table of Contents (ToC) Before you start creating the table of contents or ToC in a document, you must ensure that proper heading styles, such as Heading 1, Heading 2, Heading 3 and so on are inserted in the document. The correct ToC will be 48 Domestic Data entry operator – class X 2023-24 generated only if proper paragraph styling for headings is applied to the document. Once a styled document is created, follow the steps given below to insert the ToC in the given document: Step 1. Open the earlier created file “documentation.odt”. Step 2. Assign proper heading styles to the various headings in the document from the Styles dialog box. For example, assign the heading styles as shown in Fig. 3.3 in the “documentation. odt” file. Step 3. Place the cursor at Fig. 3.3: Planning for assigning headings styles the position where the table of contents is to be inserted. Usually, it is in the beginning of the document. Step 4. From main menu, select Insert > Table of Contents and Index > Table of Contents, Index or Bibliography. The Table of Contents, Index and Fig. 3.4: Table of Contents, Index or Bibliography dialog box Bibliography dialog box will be displayed as shown in Fig. 3.4. Step 5. By default, the Type tab will be selected with a preview of ToC on the right side of the dialog box. On the left of the dialog box, is the Types and Title section. In the Title text box, the default name of the ToC, i.e. Table of Contents is displayed. You can change this name, just by typing in the text box. Type the desired title of the ToC say “Contents” in the Title text box. The title will be changed in the Preview section as shown in Fig. 3.4. Digital Documentation (aDvanceD) using libreoffice Writer 49 2023-24  By default, the checkbox for Protected against Manual Changes option is selected. This protects the ToC from any accidental change. If this box is unchecked, then the contents of ToC can be changed directly on the document page, just like any other text on the document. In the Create Index or Table of Contents List box, the Entire Document Fig. 3.5: Table of Contents inserted in a document option is selected in the For list box. So the ToC will be automatically created from the headings and sub-headings of entire document. The Outline check box is selected, so the complete outline of the ToC is shown in the preview. Click and deselect it if you do not want to see it. Step 5. Click OK button. The Table of Contents will be inserted in the document as shown in Fig. 3.5. Note that all the headings will appear with page numbers in the ToC. The entries in the ToC are hyperlinked. Moving a mouse pointer over any of the entries will show a tool tip stating to press Ctrl+click to open hyperlink followed by the heading title. Pressing Ctrl+click the cursor will directly move on to the selected section heading. Practical Activity 3.1 Rimjhim has created a report on Environmental Pollution in LibreOffice Writer. She has applied proper heading styles to all section headings of the document. Now, to add table of contents to the document with the heading as Rimjhim’s Table of Contents, follow the steps given below. Step 1. Open the document in LibreOffice Writer. Step 2. Select Insert > Table of Contents and Index > Table of Contents, Index or Bibliography. The Table of Contents, Index and Bibliography dialog box will be displayed. 50 Domestic Data entry operator – class X 2023-24  Step 3. In the Type the Titles text box, type Rimjhim’s Table of Contents. Step 4. Click OK. The Table of Contents will be inserted in Rimjhim’s document. Customisation of ToC Once the ToC is inserted, we can customise it according to our requirements. To do so, right click anywhere on the ToC and select Edit Index option from the popup menu as shown in Fig. 3.6. The Table of Contents, Index or Bibliography dialog box will be displayed as shown in Fig. 3.7. As seen in the previous section, the dialog box has five tabs – Type, Entries, Styles, Columns and Background. The options in these tabs can be used to edit the table in various ways. The changes made will be reflected in the Preview section of the dialog box. Fig. 3.6: Selecting Edit Index option from the pop up menu Fig. 3.7: Selecting Entries Tab in Table of Contents, Index or Bibliography dialog window Digital Documentation (aDvanceD) using libreoffice Writer 51 2023-24  Type Tab: is active by default after opening the Table of Contents, Entries or Bibliography dialog box to insert the ToC. Entries Tab: contains options to set styles for various entries in the ToC. Styles Tab: contains options to apply the desired styles to the text of each level in the table of contents. To apply a custom paragraph style to any level, follow the steps given below. Step 1. Click and select the level from the Levels list box. Step 2. Click and select the desired paragraph style from the Paragraph Styles list box. Step 3. Click the OK button to apply the selected paragraph style to the selected outline level. The assigned style appears in square brackets in the Levels list and will also be visible in the Preview pane. Step 4. Click OK button to apply the selected styles. To remove the applied paragraph styling, select the desired level in the Levels list box, Fig. 3.8: Background Tab in Table of Contents, Index or Bibliography dialog window and then click the Default button. Columns Tab: contains options to set the number of columns that we want to have in our ToC. Background Tab: contains options to change the background of the ToC as shown in Fig. 3.8. The current background color will be displayed in the Active Color window. 52 Domestic Data entry operator – class X 2023-24  To change the background color follow the following steps. Step 1. Click the Color button on the top of the dialog box. Step 2. Select the desired color from the Colors palette. The selected color will appear in the New color window. Step 3. To add the selected color to the custom palette, click Add Button below the color palette. Now, the added color can be re-used whenever required. Step 4. Click OK to apply the desired color to the ToC (Fig. 3.9). To remove the Fig. 3.9: ToC with changed background color background colour from the ToC, click None button present on the top of the dialog box. LibreOffice Writer also allows to add a graphic as a background of the ToC. For this, select the Bitmap button in the Background tab of the dialog box. Select the desired graphic option and click OK button Fig. 3.10: Adding graphic as a background of the ToC (Fig. 3.10). Maintaining a Table of Contents As we know now that a table of contents is a snapshot of the entire document at any given point in time. If any changes are made to the document section headings or page numbering, it should be reflected in the Table of Contents as well. Maintaining a Table of Contents consists of updating and deleting the ToC. Digital Documentation (aDvanceD) using libreoffice Writer 53 2023-24  notes Updating the ToC Writer does not update the ToC automatically. The following activity shows how to update a ToC manually. Practical Activity 3.2 Some more sections are added in the document, accordingly the ToC needs to be changed. To update a ToC manually, follow the steps given below. Step 1. Right-click anywhere in the ToC. Step 2. From the pop-up menu as shown in Fig. 3.11, select Update Index option. Writer updates the ToC so as to reflect any changes made in the document. The reflected changes in ToC is shown in Fig. 3.12. Fig. 3.11: Selecting options for updating and editing a ToC Fig. 3.12: Updated ToC 54 Domestic Data entry operator – class X 2023-24 Deleting ToC notes To delete the ToC, right click on the table and select Delete Index option from the pop-up menu. The ToC will be deleted. Practical Activity 3.3 Rimjhim has edited her document after the ToC was inserted. Now she wants to update the ToC and also wants a pink coloured background. Write the steps that Rimjhim should follow. Step 1. To update the ToC, right-click anywhere in the ToC. From the pop-up menu, select Update Index option as shown in Fig. 3.11. Step 2. To apply a coloured background, right click anywhere on the ToC and select Edit Index option from the pop- up menu. Step 3. From the Table of Contents, Index or Bibliography dialog box, select the Background tab. The current background color will be displayed in the Active Color window. Step 4. Click the Color button on the top of the dialog box. Step 5. Select the desired color from the Colors palette. The selected color will appear in the New color window. Step 6. Click OK to apply the desired color to the ToC. Using Templates A template is a preset layout that helps to create professional and/or formal documents easily. In the previous chapters, we have learned styling of different document objects using templates. In this section, we will learn to create document templates. Sometimes there is a need to copy specific content including graphics, such as logo of a company, image of a product or text, such as tag lines of a product or a company, legal notices or even headers and footers in multiple documents. Instead of adding and then formatting the objects in all documents, we can use templates. For example, templates can be used to create a resume, chapter or project report. A template can contain all the features or objects that a regular document can contain – text, graphics, styles and can even use any language. In a template we can create and save defined headings, text formats, styles, page numbers, headers and footers. Digital Documentation (aDvanceD) using libreoffice Writer 55 2023-24  When new documents are created from these templates, they have the same content segregation, formatting features and appearance as that of the applied templates. At the same time, Writer also allows us to edit the styles and contents of the document to which the template is applied. All documents in Writer are based upon templates. If no specific template is specified, then Writer uses the default template as blank document template for the file being created. Checking the template of the document To see what template is associated with a document, select Properties option from the File menu. The Properties dialog box will be displayed as shown in Fig. 3.13. Since the document was created from the default template, no template is listed in Fig. 3.13. Fig. 3.13: Document properties with no template 56 Domestic Data entry operator – class X 2023-24  When the document is created with template, this window will display the name of the template. For example, if you want to create a document with template. Follow the steps given below. Step 1. Create a new document from File > New > Templates … Step 2. A template selection window will be displayed as shown in Fig. 3.14. Select a template, for example, select the first template ‘Modern business letter’. Fig. 3.14: Selecting a template Step 3. Click on Open button, a document with the selected template will open as shown in Fig. 3.15. Step 4. Now check the template of the created document from File > Properties. Observe that the template name ‘Modern business letter sans-serif’ is displayed under Template in the Property dialog box as shown in Fig. 3.16. Digital Documentation (aDvanceD) using libreoffice Writer 57 2023-24  Fig. 3.15: Document created by selecting template Fig. 3.16: Document Properties with template name Modern business letter sans-serif 58 Domestic Data entry operator – class X 2023-24 Creating a Template You have learnt how to create a document with predefined template. It is also possible to create your own template and save the current styles and appearance of a document for future use. Follow the steps given below to create and save a template in Writer. Step 1. Open the document in LibreOffice Writer whose template is to be created. Step 2. From main menu bar, select File > Templates > Save. The Save As Template dialog box appears as shown in Fig. 3.17. Step 3. Type the name of the new template (T1) in Template Name text box. Step 4. Select the category of the template being created. Fig. 3.17: Save As Template dialog box A category is just like a folder that helps to organise the templates. Some of the categories that can be seen in the dialog box are My Templates, Business Correspondence, Online Business Documents and Presentations. Step 5. Click and select Set as default template checkbox to make the current template as the default template. Step 6. Click Save button to save the template. A template in Writer is saved with an extension.ott. Now this template file can be used by any document in LibreOffice Writer. Using In-built/Saved Templates LibreOffice allows to use the in-built templates, online templates as well as the templates created and saved by us. Follow the following steps to use a predefined template to style a document. Step 1. Open the new document in LibreOffice Writer. Digital Documentation (aDvanceD) using libreoffice Writer 59 2023-24  Step 2. From main menu bar, select File > Templates > Manage Templates. You can also use the keyboard shortcut key Ctrl+Shift+N to open the Templates dialog box. The Templates dialog box will be displayed as shown in Fig. 3.18 showing a list of all available templates. Observe that it also shows the Template T1 created in the previous section. Fig. 3.18: Templates dialog box Step 3. Select the desired template, (say T1) and click Open button. The new document (with name as Untitled) will be opened with the same content, appearance and formatting effects as the saved template. Step 4. Make the desired changes and save the file. 60 Domestic Data entry operator – class X 2023-24  notes Practical Activity 3.4 Niaz works in a garment factory. He has to give the report of each garment being designed and stitched in his factory. His manager wants the same type of report for each garment. Instead of typing every time, he has been suggested to use the template feature of Writer. He wants to create his own default template and then use it to create multiple reports with same format and style. Give the solution to complete this task. Procedure Follow the following steps to create and save a template in Writer. Step 1. Create or open the document whose template is to be created. Step 2. From main menu bar, select File > Templates > Save. The Save As Template dialog box appears as shown in Fig. 3.17. Step 3. Type the name of the new template (Niaz_template) in the Template Name text box. Step 4. Select the category of the template (Business Correspondence) Step 5. Click and select Set as default template check box. Step 6. Click Save button to save the template with an extension.ott. Now follow the steps given below to use the template. Step 1. Select File > Templates > Manage Templates. Step 2. Select the desired template, (Niaz_template) from the Templates dialog box. Step 3. Click on Open button. The new document with name as Untitled, will be opened with the same content, appearance and formatting effects as the saved template. Step 4. Make the desired changes and save the file. Using Online Templates LibreOffice provides a wide range of online templates, which can be downloaded from Internet. Once these templates are installed on the computer, they will appear in Templates window. To view and select online templates provided by Writer, follow the steps given below. Step 1. From main menu bar of LibreOffice Writer, select File > Templates > Manage Templates. Digital Documentation (aDvanceD) using libreoffice Writer 61 2023-24  Step 2. Templates dialog box will be displayed as shown in Fig. 3.18. Observe the small circular icon Browse Online Templates in the lower left corner above the Help button. Fig. 3.19: Browse Online Templates option in Templates dialog box Step 3. Click on Browse Online Templates button on the bottom left of the Templates window (Fig 3.19). The official templates page of OpenOffice Writer (https://extensions.libreoffice.org/templates) will open in the browser as shown in Fig. 3.20. Fig. 3.20: The official templates page of libreoffice.org 62 Domestic Data entry operator – class X 2023-24 Step 1. If you click on any of the templates, you will see notes the detailed information about that template. Step 2. Download the desired template, say Calendar Creator and save it on your computer. Step 3. Open LibreOffice Writer. Step 4. Click File > Templates > Open Template... Step 5. From the Open dialog box, browse for the template Company Letter, that is to be opened, and click on Open button. Step 6. The template file Company Letter.ott will open. Step 7. Make the desired changes in the content or appearance. Step 8. Save the file as text file Company Letter.odt Libreoffice.org also provides extensions on https:// extensions.libreoffice.org/extensions. These are independent programs that can be added or removed from the main existing application. Some templates are available in the form of extensions which can be downloaded and installed on the computer. Importing a Template Once a template is downloaded and saved in any file or folder, it is possible to import it so that it is visible in the list of templates in the Templates dialog box. Use the following steps to import the template. Step 1. Open the Templates dialog box. Step 2. Click Import Templates button located in the bottom right corner of the dialog box. The Select Category dialog box will open as shown in Fig. 3.21. Step 3. To add the new template into any existing category, click and choose that category from the list box. Otherwise click and select Create a New Category check box. Step 4. Type the name of new category in the text box. Step 5. Click OK button. The Open dialog box will appear. Step 6. Browse for the folder where the downloaded template file Company Letter was saved. Digital Documentation (aDvanceD) using libreoffice Writer 63 2023-24  Fig. 3.21: Select category dialog box Step 7. Select the file and click Open button. The selected file will be added to the list of templates as shown in Fig. 3.22. Fig. 3.22: Templates dialog box showing the imported template 64 Domestic Data entry operator – class X 2023-24  notes Practical Activity 3.5 Niaz’s colleague, Balwinder has to create Employee IDs for all the employees of her company, but there is no pre-defined template for the same. She searched some templates on the Internet that suit her requirement. Although, she has learned to create and use a template but does not know how to download and use an online template from the Internet. How will she resolve this issue? Follow the steps given below to solve this problem. Step 1. Open the new document in LibreOffice Writer. Step 2. Open the Templates dialog box by using the keyboard shortcut Ctrl+Shift+N. Step 3. Click on the Online Templates button. Step 4. From the templates page in the browser click and select the desired template, Employee ID Badge, as shown in Fig. 3.23. The description of the template will be displayed. Fig. 3.23: Online template Employee ID Badge Digital Documentation (aDvanceD) using libreoffice Writer 65 2023-24  notes Step 5. Download the template Employee ID Badge and close the browser. By default the template gets downloaded and saved in Download folder. Step 6. Open Templates dialog box again and click on Import button, as shown Fig. 3.24: Clicking on Import Template in Fig. 3.24. Step 7. Then select the category in the Select Category dialog box and click on OK button as shown in Fig. 3.25. Fig. 3.25: Selecting Category while importing Template.jpg Step 8. Browse the Download folder to select the template Employee ID Badge, as shown in Fig. 3.26. Fig. 3.26: Select the template Employee ID Badge to import 66 Domestic Data entry operator – class X 2023-24  Step 9. Click on Open button to add the Employee ID Badge notes template. The template imported appears in the list of templates as shown in Fig. 3.27. Fig. 3.27: Updated list of templates in Template dialog window Step 10. Double click on Employee ID Badge template. A new file in a new window with the selected template will be opened as shown in Fig. 3.28. Fig. 3.28: New document created with selected template Step 11. Make the desired changes and save it as a document file. Digital Documentation (aDvanceD) using libreoffice Writer 67 2023-24  Editing a Template Once a template is created, desired changes can be made in it by following the steps given below. Step 1. Click File > Templates > Manage Templates. The Templates dialog box will be displayed. Step 2. Right click on the template file (say T1) that has to be edited. Step 3. Select the Edit option from the popup menu. The template file will be opened. Make the desired changes and save the file. Now if we apply this template for any document, the edited file will be used. Setting Up a Custom Default Template Any template can be set as a default template. Writer gives a blank document as a default template. At the same time, any template file can be set as a default template by following the steps given below. Step 1. Open the Templates dialog box by pressing Ctrl+Shift+N. Step 2. Right click on the template that you wish to set as the default template. Step 3. From the popup menu, select option Set as Default as shown in Fig. 3.29. The default icon will appear on the top left of the template file. The next time you create a new document by selecting File > New > Text Document, the new document will use the last edited template. Moving a Template When you place a mouse pointer on any of the templates in the Templates dialog box, a tool tip appears Fig. 3.29: Setting up a default template displaying the name of 68 Domestic Data entry operator – class X 2023-24 the template and its respective category. You can move a template from one category to another by following the steps given below. Step 1. Open the Templates dialog box. Step 2. Click and select the template to be moved. Step 3. Click Move button. Step 4. Select the new category where the template has to be moved. Step 5. Click OK button. Exporting a Template Export template feature allows to store the template file in the desired folder on your computer. It is different from moving a template from one category folder to another. When a template is exported, it is saved as a template file at any desired location. Exporting the template is a useful Fig. 3.30: Selecting Export Template button feature for sharing the templates with multiple users. Follow the steps given below to export a template: Step 1. In the Templates dialog box, select the template to be exported. Step 2. Click on Export button located in the bottom right of the dialog box as shown in Fig. 3.30. Step 3. Then the Select Path dialog box appears as shown in Fig. Fig. 3.31: Select Path dialog box 3.31 to select the folder where you want to export the template. Digital Documentation (aDvanceD) using libreoffice Writer 69 2023-24  Step 5. Select the folder and press on OK button. The selected template will be exported in that folder and a confirmation dialog box, as shown in Fig. 3.32 will be displayed. Applying Templates to a Blank Document You can apply the template Fig. 3.32: Confirmation after exporting a template from the available template list to any blank document. Follow the steps given below to apply the template. Step 1. Open a new document in LibreOffice Writer by selecting File > New > Text Document. Step 2. Select File > New > Templates to display the Templates dialog box. Step 3. Select the desired template. The template will be opened in a new window. Step 4. Copy the entire content of the template by using keyboard shortcut key Ctrl+A for selection and Ctrl+C to copy. Step 6. Open the blank document and paste the copied content of the template by using keyboard shortcut key Ctrl+V. Step 7. Add or delete the content as desired and save it as a text file. Once a template is created, it can be applied to an existing document by selecting Insert >Text From File option. Browse and select the template file that has been saved on the computer. The selected template will be inserted in the document. 70 Domestic Data entry operator – class X 2023-24  Practical Activity 3.6 Niaz wants to store the template created by him and another one that was imported by Balwinder in a folder named Company_ Templates on the desktop. Follow the steps given below to complete this task. Step 1. Create a new document in LibreOffice Writer and press Ctrl+Shift+N to open the Templates dialog box. Step 2. Select the template (Niaz_template) to be exported. Step 3. Click Export button in the bottom right of the dialog box. The Save As dialog box appears. Step 4. Browse for the folder named Company_Templates stored on the desktop. Step 5. Click Select Folder button to export the selected template. Step 6. Repeat the steps 1 to 5 to import the template Employee ID Badge in the Company_Templates folder. Track Changes Feature Track Changes is a powerful tool that makes the process of commenting, editing and reviewing of a document easy between multiple users. A document created by one user need to be reviewed and edited by self or other users. If any modifications are made in the document directly, the original document might get lost. Instead, the Track Changes feature of Writer offers an alternative method to keep a record of all the changes made in the original document. All the changes that are recorded can be either accepted or rejected by the original author. Furthermore, the Track Changes feature also gives us the option to add comments while reviewing a document. The Track Changes toolbar contains various tools that help to track the changes made by different users. To view the Track Changes toolbar, from main menu bar, select View > Toolbars > Track Changes. The Track Changes toolbar will appear in the bottom left corner of the Writer window as shown in Fig. 3.33. Fig. 3.33: Track Changes Toolbar Digital Documentation (aDvanceD) using libreoffice Writer 71 2023-24  notes The various buttons present on the Track Changes toolbar shown in the Fig. 3.33 are briefly explained below. (a) View Track Changes – Clicking on this button displays all the changes made in the document by different users. (b) Record Track Changes – Clicking on this button, turns on the Track Changes feature. After this, any sort of editing done will be marked. Any character added to the document will be displayed in a different color and any deletion done will be seen in strike-through style. (c) Previous Track Changes/Next Track Changes – Click on these buttons to navigate between the changes made. (d) Accept/Accept All Track Changes – Once the editing is done, the original author may accept the change made to the document by clicking Accept All Track Changes button will accept all the changes made to the document. (e) Reject/Reject All Track Changes – The original author of the document may reject a single change or all changes made to the document by clicking Reject Track Changes or Reject All Track Changes button, respectively. (f) Manage Track Changes – By clicking on this button the Manage Changes dialog box is displayed, which contains a detailed list of all changes made to the document along with the author’s name and date and time of modification. (g) Insert Comment – This button is used to add a comment in a document. Preparing a Document for Review Track Changes feature is used when a document is shared with one or more users for review or editing purposes. So, before the document is shared, one should make sure that the changes made should be recorded. This will ensure that after the review is done, the original author of the document has the option to accept or reject the changes made. Hence, before the document is 72 Domestic Data entry operator – class X 2023-24 sent for review, the original author should prepare the document for review and start recording the changes being made. For that, select Edit > Track Changes >Record option. Alternatively, select the Record button from the Track Changes toolbar. To make sure that no user is able to disable the track changes option, we can protect the document with password. Follow the following steps for the same. Step 1. Create a new document in LibreOffice Writer. From the main menu, select Edit > Track Changes > Protect option. The Enter Password dialog box will appear as shown in Fig. 3.34. Alternately, you can click on Protect Track Changes button located on the Track Changes Toolbar to protect the document. Step 2. Enter the same password in Password and confirm text box and click on OK button. After protecting the document with password, if any user tries to disable the Track Fig. 3.34: Enter Password dialog box allowing Changes feature, Writer will prompt to enter password after clicking on Protect Track Changes button to enter the password. Recording Changes Once the Track Changes features is ON, the reviewers can begin recording the changes in the document. For that, click Edit > Track Changes > Record option. Alternatively select Record button from the toolbar. The shortcut key to start recording the changes is Ctrl+Shift+C. Once the Record option is selected, the Track Changes Fig. 3.35: Recording the changes Digital Documentation (aDvanceD) using libreoffice Writer 73 2023-24  feature is ON. Now, any character being deleted will be shown as strike through text and any character added will be shown in different colour as shown in Fig. 3.35. Also when you place the mouse pointer over any of the changes, a tool tip is displayed giving details of the author, change made, date and time of change done as Fig. 3.36: Tool tip showing the details of changes made shown in Fig. 3.36. To stop recording, deselect the record option by selecting Edit > Track Changes > Record or click the Record button on the toolbar. Accepting and Rejecting Changes Once the changes are made by all the reviewers, the original author may accept or reject them. Open the document and follow the steps given below. Step 1. To accept or reject a change, click on the change made and then select Accept Track Change / Reject Track Change button. Step 2. To navigate between the changes made to the document click Previous Track Changes and Next Track Changes buttons. Step 3. To accept or reject all the changes made, select Accept All Tracked Changes / Reject All Tracked Changes button respectively. Step 4. If Manage Track Changes button is clicked, a Manage Changes dialog box appears as shown in Fig. 3.37. It contains the details of all the changes made in the Fig. 3.37: Manage Changes dialog box document. The dialog box contains buttons to accept and reject changes as well. 74 Domestic Data entry operator – class X 2023-24 Adding Comments To add comments while reviewing, we can use the Track Changes toolbar as well. Click at the place in the document where the comment is to be placed. Thereafter, follow the steps given below to add comments in a document. Step 1. Click Insert Comment button on the Track Changes toolbar. A comment box will be inserted on the right side of the window. It will have the name of the author or reviewer and date and time of the comment being made, as shown in Fig. 3.38. Step 2. Type the comment. Step 3. Once done click anywhere on the document to activate it. If more than one or two of users add comments then the comments by different users will be shown in different coloured comment boxes in Fig. 3.38. Fig. 3.38: Inserting Comments using the option under Track Changes toolbar Digital Documentation (aDvanceD) using libreoffice Writer 75 2023-24  Deleting Comments To delete any comment, click on the down arrow on the bottom right of the comment box. A popup menu will be displayed as shown in Fig. 3.39. It gives the option to delete only the current comment, all comments by a particular author and to delete all comments. Select the desired option. Fig. 3.39: Deleting Comments from pop-up menu Comparing Documents Once the reviewers have made the changes and given their comments, Writer allows to compare the original document with the reviewed document and then choose the option(s) that suits the best. Follow the steps given below to compare the documents. Step 1. Open the edited document. Step 2. Select Edit > Track Changes > Compare Documents option. Alternatively, click Compare Non-Tracked Changed Document from the toolbar. 76 Domestic Data entry operator – class X 2023-24 Step 3. The Compare To dialog box will appear. Browse and select the original file to be compared as shown in Fig. 3.40. Fig. 3.40: Selecting original file Step 4. The Manage Changes dialog box is displayed as shown in Fig. 3.37. Accept or reject the desired changes. Step 5. Close the dialog box when done. Step 6. Save the edited file. Practical Activity 3.7 Pradeep has created a document on ‘Humans and Nature’. He wants his friends to review the document and give their comments wherever required. The following process will demonstrate how his friends add comments in the document. Step 1. Open the document created by Pradeep to review. Step 2. Click on Insert Comment button on the toolbar. Step 3. Type the comment in the comment box. Step 4. Once done click anywhere on the document to activate it. Step 5. Repeat the steps given above to add more comments in the document. Digital Documentation (aDvanceD) using libreoffice Writer 77 2023-24  notes Practical Activity 3.8 After incorporating the reviews, Pradeep mailed the document to his editor with the Track Changes ON. He made certain changes in the document. Pradeep wants to compare his original document with the reviewed document. The following process will demonstrate how Pradeep will compare the edited document with the original document. Step 1. Open the edited document. Step 2. Select Edit > Track Changes > Compare Documents option. Step 3. The Compare To dialog box will appear. Browse and select the original file to be compared as shown in Fig. 3.40. Step 4. The Manage Changes dialog box is displayed as shown in Fig. 3.37. Step 5. Accept or reject the desired changes. Step 6. Close the dialog box when done. Step 7. Save the edited file. summary A Table of Contents (ToC) contains a list of topics and subtopics that have been covered in the book along with page numbers. A ToC in Writer allows to insert an automated table of contents in a document. The contents in the ToC are hyperlinked in the table. LibreOffice Writer supports up to 10 levels of headings H1 to H10. To insert a ToC, select Insert > Table of Contents and Index > Table of Contents, Index or Bibliography. To add a graphic as a background of the ToC, select the Bitmap button in the Background tab of the Table of Contents, Index or Bibliography dialog box. Once inserted, a ToC can be edited or deleted from the document. A template is a preset layout that helps to create professional and/or formal documents easily. In a template we can create and save defined headings, text formats, styles, page numbers, headers and footers. 78 Domestic Data entry operator – class X 2023-24  The new documents created from these templates notes have the same content segregation, formatting features and appearance as that of the applied templates. To create a new document with a template, select File > New > Templates … To create and save your own template, select File > Templates > Save. LibreOffice provides a wide range of online templates which can be downloaded from then Internet. Once these templates are installed on your computer, they will appear in Templates window from where they can be viewed and selected. Any template can be set as a default template. Export template option allows to store the template file in the desired folder on your computer. Exporting a template is a very useful feature to share the templates with multiple users. Track Changes feature of Writer makes the process of commenting, editing and reviewing of a document easy between multiple users. The Track Changes feature of Writer helps to record all the changes made in the original document. All the changes that are recorded can be either accepted or rejected by the original author. The Track Changes feature also gives us the option to add comments while reviewing a document. The Track Changes toolbar contains various tools that help to track the changes made by different users. To view the Track Changes toolbar, from main menu bar, select View > Toolbars > Track Changes. Practical Exercises 1. Rajnikant has created a detailed report on Education in India using the LibreOffice Writer tool. He has styled the document well by inserting proper paragraph and section headings. His colleague Murugan advised him to insert a Table of Contents that will give a complete glimpse of his report. Help Rajnikant to insert a ToC with a green background. Digital Documentation (aDvanceD) using libreoffice Writer 79 2023-24  notes 2. After the ToC was inserted, Rajnikant made some changes in the report but the changes were not reflected in the ToC. Will he have to again insert a new ToC? Suggest him a better way to update the ToC. 3. Rukhsat has just downloaded a template from libreoffice.org. Now she wants to import it in My templates category of the Templates folder. How can she complete her task? 4. Rimit loves to create cards on his computer. This time he decided to make his own Christmas cards and send them to his friends. He wants to do the task using the templates feature of Writer. He has already saved and imported the desired template(card.ott). Help him to create cards for at least five of his friends. 5. Teena is a member of the editorial team of her school. She wants to set the newspaper template as her default template. Write the steps to help Teena complete the task. 6. Venkat has created a document on India’s Trade Review. He wants two of his friends, Rinky and Sujoy to review his document and if required, add comments to it. But none of them know how to use the Track Changes feature of Writer. Help them to complete the task. 7. Venkat has received the reviewed file with modifications and comments. What steps will he follow now to accept or reject the changes done by his friends. 8. Write steps to compare the original document created by Venkat and the edited document sent by Rinky and Sujoy. Check Your Progress A. Multiple choice questions 1. Which of the following is NOT true about Table of Contents, Index or Bibliography dialog box? (a) It has four tabs (b) On the Type tab, by default, the checkbox for Protected against Manual Changes option will be selected. (c) The Styles tab contains options to change the background colour. (d) None of the above 2. Which of the following tabs is by default active when the Table of Contents, Entries or Bibliography dialog box is opened? (a) Entries (b) Background (c) Styles (d) Type 80 Domestic Data entry operator – class X 2023-24  3. Which of the following tabs contains options to set styles notes for various entries in the ToC? (a) Entries (b) Background (c) Styles (d) Type 4. Which of the following can be added in the background of Table of Contents in LibreOffice Writer? (a) Color (b) Graphic (c) Both a and b (d) Neither a nor b 5. Which of the following is NOT true about templates? (a) The styles and formatting features can be reused. (b) LibreOffice provides online templates (c) We cannot create our own templates. (d) None of the above. 6. Which of the following is the shortcut key to open the Templates dialog box? (a) Ctrl+Alt+N (b) Shift+Ctrl+N (c) Ctrl+Alt+T (d) Shift+Alt+T 7. Which of the following buttons, in the Templates dialog box, will be clicked to save a template displayed in the list of templates? (a) Export (b) Import (c) Move (d) None of the above 8. Which of the following is the shortcut key to select he entire document? (a) Ctrl+S (b) Ctrl+A (c) Ctrl+D (d) Ctrl+B 9. Which of the following is the correct sequence of options to open the Templates dialog box? (a) File > Manage Templates > Templates (b) File > Templates > Manage Templates (c) Insert > Templates >Manage Templates (d) Insert > Manage Templates > Templates 10. Which of the following is true about Track Changes feature of Writer? (a) You cannot record a change made in the document. (b) A comment of a particular author only can be deleted (c) Any change made to the document is permanent. (d) None of the above 11. Which of the following menus contains the Track Changes option? (a) File (b) Edit (c) View (d) Insert 12. Which of the following is the shortcut key to start recording the changes being made in the document? (a) Ctrl+Shift+C. (b) Alt+Shift+C (c) Ctrl+Alt+C (d) Shift+C+F2 Digital Documentation (aDvanceD) using libreoffice Writer 81 2023-24  notes B. Fill in the blanks 1. To navigate to the topic from the ToC, press ___________ key while clicking the mouse button on that topic. 2. To remove the applied paragraph styling in the ToC, select the outline level in the Levels list box, and then click the _______ button. 3. If the checkbox for___________________ option is selected, the ToC is protected from any accidental change. 4. To update the ToC manually, right click and select ___________ option from the pop up menu. 5. The ___________ tab contains options to set the number of columns that we want to have in our ToC. 6. A ________________________ is a preset layout that helps us to create professional and formal documents easily. 7. The default template in Writer is ____________________. 8. To find the template that is being used in the current document, select ___________ option from the File menu. 9. The ____________________ button is clicked in Templates dialog box to view online templates. 10. The __________________ feature of Writer offers us an alternative method to keep a record of all the changes made in the original document. 11. The shortcut key to start recording the changes is _________. 12. After the Track Changes feature is ON, the added characters are shown as ____________ text. C. State whether the given statements are True or False 1. The topics in Table of Contents are hyperlinked. 2. The Table of Contents in LibreOffice Writer can be updated automatically. 3. TABLE of Contents can be inserted even if the section headings are not styled. 4. Once a ToC is created, it cannot be edited. 5. We cannot add a graphic as a background of ToC. 6. A single template can be used for multiple documents. 7. A template cannot contain graphics. 8. All documents in Writer are based upon templates. 9. The online templates cannot be added to the list of templates in the templates dialog box. 10. A template once created can be edited again and again. 11. The changes recorded have to be accepted by the original author. 12. We can delete the comments added in a document by the user. 82 Domestic Data entry operator – class X 2023-24  D. Answer the following questions notes 1. What is the need of table of contents? 2. What will happen if the ‘Protected Against Manual Changes’ option is not selected in the Type tab of Table of Contents, Index or Bibliography dialog box? 3. Name the five tabs present in the Table of Contents, Index or Bibliography dialog box. 4. What do you mean by customization of ToC? 5. How headings and sub-headings of a document differentiated in ToC? 6. Define a template. 7. Give any one advantage of using a template for your document. 8. What is the difference between importing and exporting a template? 9. Name any two categories of templates. 10. When is exporting of templates useful? Give any one reason. 11. What is the difference between Accept Track Change and Accept All Tracked Changes buttons? 12. How do we prepare a document for review? 13. Identify and label the Record button, Insert Comments button, Accept All Tracked Changes, Reject Track Change buttons in the following figures of Track Changes toolbar (shown in Fig 3.41). 14. Identify and label “Browse Online Templates button, Export button, Import button” in the following Templates dialog box. Fig. 3.41: Track Changes Toolbar Digital Documentation (aDvanceD) using libreoffice Writer 83 2023-24 2 Electronic Spreadsheet (Advanced) using LibreOffice Calc IntroductIon In the modern digital world, one of the most important thing is counting and calculations. Every profession, household, institution and even our smart devices have to deal with numbers. In computers we can do the calculations on number of records spread over the long sheet using the spreadsheet software that helps to work with numbers efficiently. It resembles an accountant’s ledger book wherein everything is recorded under suitable headings. We have already learned in Class 9, that how the spreadsheet software performs calculations and present data in tabular format. It becomes easy to visualise a huge grid of numbers when represented using charts in electronic spreadsheet. Moving further, in this unit you will learn advanced features of electronic spreadsheet using LibreOffice Calc to perform analysis, automate repeated tasks, link, share and review data. It includes – analysing data to extract useful information for making effective decisions. Macros, is the another powerful feature of spreadsheet covered in this unit. Macros are a set of stored functions used to automate processes repeatedly. They are tools which can be used 2023-24 to perform most of the redundant tasks with relative notes ease. In the second chapter of this unit, you will learn to create multiple sheets and link the data in multiple sheets. It can be done in two ways, one is creating reference to other sheets by using keyboard and mouse, and other is by linking external data. LibreOffice Calc also provides the sharing feature that allows to share the spreadsheet for editing purpose. This saves to keep track of multiple copies of the same spreadsheet corrected by different users. Sharing allows the users to work on the same spreadsheet in collaboration. ElEctronic SprEadShEEt (advancEd) uSing librEofficE calc 85 2023-24  Analyse Data using 4 CHAPTER Scenarios and Goal Seek IntroductIon Analysing data is the process to extract useful information for making effective decisions. The spreadsheet is one of the best software used for data analysis. It is used to retrieve, correlate, explore and visualise data to identify patterns, trends and relationships. The spreadsheet component in LibreOffice known as Calc includes several tools used to manipulate the data in the spreadsheet. You can analyse the data and interpret the results from it. In this chapter, you will learn to analyse data using LibreOffice Calc. Consolidating Data Consolidate is a function used to combine information from multiple sheets of the spreadsheet into one place to summarize the information. It is used to view and compare variety of data in a single spreadsheet for identifying trends and relationships. You need to check the following before consolidating data. Open each sheet in the spreadsheet and check that the data types must match which you want to consolidate. Match the labels from all the sheets which are used for consolidating. Enter the first column as the primary column on the basis of which the data is to be consolidated. Steps to consolidate the data are as follows: Step 1. Open the spreadsheet which has the data to be consolidated. Step 2. Create a new sheet where the data has to be consolidated. Step 3. Choose Data > Consolidate option that will open Consolidate dialog as shown in Fig. 4.1. 2023-24 Step 4. Observe that the default function “Sum” is seen in the Function drop- down. You will be able to see the list of functions as shown in the Fig. 4.2 by clicking on the Function drop-down. Step 5. Choose the required function from the drop-down list. Step 6. The sources data range list contains existing named ranges to quickly select from that. But if the source range is undefined, then click and select the range from the sheet Fig. 4.1: Consolidate dialog box which is to be consolidated. Step 7. Click on Add to add this range under the ‘Consolidation ranges’ of the Consolidation dialog. Step 8. Repeat steps 6 and 7 to add more sheets to be consolidated. Step 9. Remember to check the target range specified under Use ‘Copy results to’. If it is not mentioned, then click on the cell of sheet where the final data has to be produced. Step 10.Click on Options that will list two checkbox under Consolidate by Fig. 4.2: Functions under consolidate “Row labels” and “Column labels” and “Link to source data” under Options, as shown in Fig. 4.3. From this you can select the additional feature as per requirement. The option consolidate by rows and columns are checked to consolidate data as per row labels and column label. Link to source data is checked to make the modification automatically in the consolidated (target) sheet while making any changes in the source data. Step 11.Finally click on OK button. Let us illustrate this through following Fig. 4.3: Consolidate options activity. ElEctronic SprEadShEEt (advancEd) uSing librEofficE calc 87 2023-24  notes Practical Activity 4.1 Let us take an example that we have two branches of our shop namely ABC and XYZ. We have the Sales records for the month of January and February of both the branches in two different sheets named ABC_Branch and XYZ_Branch. Now we have to consolidate these two sheets to get the sum of both the sheets monthly to get the insight about the sale as per product and branch. Now let us create the following sheets in a spreadsheet sales. Fig. 4.4: Data for ABC_Branch Fig. 4.5: Data for XYZ_Branch Now let us use the two sheets ‘ABC_Branch’ and ‘XYZ_Branch’ of a spreadsheet sales.ods which will be consolidated using the following steps: Step 1. Add a new sheet and rename it as Consolidate Step 2. Click on Data > Consolidate or use the keyboard shortcut key ALT+D, the Consolidate dialog window will open as shown in Fig. 4.6. Fig. 4.6: Consolidate dialog window Step 3. In the Function box, choose Sum function from the drop down as we want to add the data from all the sheets. Step 4. Click in the Source data ranges box and then click on the worksheet ABC_Branch and select the area to be consolidated and then click on the Add button will add the copy reference in the consolidation ranges (Fig. 4.7). 88 Domestic Data entry operator – class X 2023-24  notes Fig. 4.7: Selecting the source data range from the sheet Step 5. Click on the sheet XYZ_Branch and select the area to be consolidated and then click on ADD will add the next consolidation range (Fig. 4.8). Step 6. Click on the ‘+’ sign next to Options in the Consolidate dialog window to change the settings. ‘Consolidate by’ has two options Row labels and Column labels. Check row label or column label or both if you want to consolidate it by matching the label. If Link to source data is checked, then it will keep on updating the data of the Consolidate sheet automatically if there is any change made in the selected ranges. Fig. 4.8: Options for setting more properties Step 7. Click on ‘Copy results to’ and then click on any of the cell in the Consolidate sheet where you want to copy the final result after consolidation. Step 8. Click on OK button. Fig. 4.9 will display the Consolidate sheet with data after Fig. 4.9: Consolidated sheet consolidation. ElEctronic SprEadShEEt (advancEd) uSing librEofficE calc 89 2023-24  notes The consolidated sheet will have all the consolidated data along with the original data. You can view the original data of both the sheets and by clicking on the ‘+’ sign in front of the consolidated row. Fig. 4.10 shows the original data and consolidated data. Fig. 4.10: Consolidated sheet details by clicking on the + sign in front of rows Practical Activity 4.2 Create two sheets Marks_Term1 and Marks_Term2 to store the marks in Term1 and Term2 for a set of students taking the following columns: 1. Name 2. Marks in English 3. Marks in Hindi 4. Marks in Maths 5. Marks in Science 6. Marks in Social Science 7. Total marks (using function Sum) 8. Percentage (using function Average) Now, use the consolidate function to merge these two sheets. Groups and Subtotals Group and Outline in Calc is used to create an outline of the selected data and can group rows and columns together so that one can collapse (-) to hide it or expand (+) it using a single click on it. Select the data to be grouped, click on Data>Group and Outline. Then choose Rows to group the data on the basis of rows or columns to group it on the basis of columns. 90 Domestic Data entry operator – class X 2023-24  The Subtotal tool in Calc creates the group automatically and applies common functions like sum, average on the grouped data. One can use any type of summary function for each column as per the requirement of data analysis. It can group subtotals by using category and sorts them in ascending or descending order so that one need not to use filters. Follow the following steps to apply Subtotal tool. Step 1. The sheet where this is to be applied must have labels to the Fig. 4.11: Choosing subtotal option from column Data menu Step 2. Click on Data menu and choose Subtotals (Data>Subtotals) as shown in Fig. 4.11. Step 3. Choose the column in the Group by list in the subtotal dialog which is to be used for grouping the data in the sheet as shown in Fig. 4.12. Step 4. Select the column by clicking the checkbox under Calculate subtotals for to create subtotals for. Fig.4.12: Choose the column in the Step 5. Select the desired function by Group by list clicking the function under Use function. Step 6. You can use the 2nd Group and 3rd Group tabs to group the data in further levels. Step 7. Click on OK button. Practical Activity 4.3 Let us take an example to store marks in various subjects by students of Class X as shown in the table given in Fig. 4.13. To take out the average of the subjects scored in each class, use the subtotal tool which automatically group the data on a particular column and perform the selected function on any of the column. ElEctronic SprEadShEEt (advancEd) uSing librEofficE calc 91 2023-24  notes To solve this, perform the following steps: Step 1. Create a sheet as shown in the Fig. 4.13 to take the marks for students in a term for a class. Fig. 4.13: Spreadsheet with student marks in two class Step 2. Now, to calculate the average marks scored by the students of each class in each subject, i.e. class-wise and subject analysis. Step 3. To do so, click on Data > Subtotal, the subtotal dialog as shown in Fig. 4.14 will open. Fig. 4.14: Subtotal dialog window Step 4. Choose the column i.e. Class under Group by in the 1st Group on which we need to group the whole data. Also, select the subjects i.e. English, Hindi, Maths, Science and Social Science under Calculate subtotals for. For each subject click on the Average under Use function individually otherwise Sum function will be used by default. Step 5. Click OK and the sheet will look like as shown in the Fig. 4.15. Fig. 4.15: Marks list after Subtotal 92 Domestic Data entry operator – class X 2023-24 Observe that outline to the left of the row numbers notes which is inserted after performing the subtotal tool. This outline shows the hierarchical structure which can be used to show or hide different levels by clicking on the group indicators ‘+’ sign to expand and ‘–’ sign to collapse the data. You can hide the low-level details and just look at the final totals and grand totals. If you want to remove the outline feature from the sheet at any point of time then it is possible by just clicking on Data > Group and Outline > Remove Outline. Practical Activity 4.4 Create a sheet as shown in Fig. 4.16 and then calculate the average of marks subject wise on the basis of class and stream. Also, try out another function Count to calculate the number of students in each subject. Fig. 4.16: Marksheet What-if Scenarios What-if scenario is a set of values that can be used within the calculations in the spreadsheet. A name is given to the scenario and several scenarios can be created on the same sheet. It is used to explore and compare various alternatives depending on changing conditions. It can be used in the beginning of any project to optimise the output. This tool is used to predict the output while changing the inputs which reflects the output and thus one can choose the best plan of action based on it. ElEctronic SprEadShEEt (advancEd) uSing librEofficE calc 93 2023-24  For example, a person who is taking car loan has to decide on certain factors as given below: The number of years for which the car loan is taken. The total amount of car loan The above two factors, i.e. Principal amount and Number of years can display the EMI to be paid monthly. Follow the following steps to create scenario. Fig. 4.17: Create Scenario Step 1. Select the cells which contains values in the dialog window sheet that needs to be changed. To select multiple cells, hold Ctrl key and click on the cell to be selected. Step 2. Choose Tools>Scenarios will open scenario dialog window as shown in Fig. 4.17. Step 3. Enter a name for the new scenario and leave the other fields unchanged. Step 4. Click on OK button. Step 5. This will create a new scenario which is activated automatically. Practical Activity 4.5 Let us take an example to get the insight of the EMI, Total Amount paid and Total Interest paid on the Loan Amount (Principal) taken for a fixed Rate of Interest for a number of time period. So, now we want to see what will be the EMI and how much is the total interest paid for different loan amount and time period. To do so, let us use the Scenario tool by using the following steps: Step 1. Create the following sheet (Fig. 4.18) and remember to put the formula to calculate EMI in B3 as = ABS(PMT(B2/1200,E1*12,B1)) Total Amount in B4 = B3*E1*12 Total Interest in B8 = B4-B1 Fig. 4.18: Loan sheet 94 Domestic Data entry operator – class X 2023-24  Step 2. Select the cells B1 and E1 using Ctrl + Click on these notes cells as these two are the variables in this situation Step 3. Choose Tools > Scenario will display the Create Scenario dialog window as shown in Fig. 4.19. Fig. 4.19: Create Scenario dialog window Step 4. Write the name of the Scenario and click on OK button will add this in the scenario list. Step 5. Let us create two more scenarios as LAmt_2 and LAmt_3 with Loan Amount as 2500000, No. of years as 12 and Loan Amount as 2500000, No. of years as 10 respectively. We can define different scenarios to do so on the same sheet, each with some different values in the defined cells. Click on Tools > Scenario, write the name of the scenario and click on OK. The Loan sheet will look like as shown in the Fig. 4.20 Fig. 4.20: Loan sheet with scenarios Step 6. Click on the navigator icon in the toolbar and then click on scenarios. Click on the scenario name will display all the details as per that scenario. Practical Activity 4.6 Calculate the income tax by entering the salary using different scenarios for two variables income tax slab and tax rate. The sheet should look as shown in Fig. 4.21. The different scenarios to be taken as given below: Income Slab Tax rate 7.5 to 10 lakhs 15% 10 to 12.5 lakhs 20% ElEctronic SprEadShEEt (advancEd) uSing librEofficE calc 95 2023-24  notes 12.5 to 15 lakhs 25% >15 lakhs 30% Fig. 4.21: Income tax sheet What-if Analysis Tool What-if tool uses Data > Multiple Operations and is a planning tool for what-if questions. In this, the output is not shown in the same cells, whereas it uses a drop-down list to display the output depending upon the input. The Multiple Operations tool creates a formula array to display the list of results applying the formula on a list of alternative values used in the formula. This tool uses two arrays of cells, one array contains the input values and the second array uses the formula and display the result. It is useful to check in the beginning to understand from the output for the efficiency. What-if analysis tool is very helpful when we want to know how much profit we earn for a particular product for a series of selling units. Following steps are used for what-if analysis tool: Let us calculate with one formula and one variable: Step 1. Enter the data in the cells and then enter a formula to calculate a result from values in other cells. Step 2. Create an array of input values on the basis of which the output is to be generated using the formula. Step 3. Select the cell range of input array and output array. 96 Domestic Data entry operator – class X 2023-24 Step 4. Click on Data>Multiple Operations will display the multiple operations dialog window as shown in Fig. 4.22. Step 5. Enter the cell address in the Formulas box from the sheet which contains the formula Fig. 4.22: Multiple operation dialog window Step 6. Now, enter the cell address of the cell which is a variable and is used in the formula in Column input cell box. Step 7. Click on OK will generate all the possible outputs based on the formula. Practical Activity 4.7 Let us take an example to calculate the profit on annual series of sale depending on the Selling Price, Cost Price and Fixed Cost expenses. Step 1. Create the sheet as given below in Fig. 4.23 Fig. 4.23: Sale_incentive sheet Step 2. In Cell B5 enter the formula = B4 * ( B1 – B2) – B3 Step 3. Select the cell range from D2:E11 Step 4. Click on Data > Multiple Operations will display the Multiple operations dialog window as shown in Fig. 4.24. Fig. 4.24: Performing Multiple Operations ElEctronic SprEadShEEt (advancEd) uSing librEofficE calc 97 2023-24  notes Step 5. We have formula in cell B5, as we want to use the same cell address to be copied therefore, we have used absolute cell referencing and written it as $B$5. Step 6. In this example, we have another variable as number of items sold which is in the cell $B$4 Step 7. Click on OK will generate a sheet as shown in Fig. 4.25. Fig. 4.25: Result for multiple operations Practical Activity 4.8 Create the following sheet (Fig. 4.26) to calculate the profit and then decide how many books are to be published based on the profit. Fig. 4.26: Practical exercise Goal Seek It general we fill in the values in the cells and then create formula on these values to get the required result. To predict the output, we keep on changing all the input values to obtain the desired output. Goal seek 98 Domestic Data entry operator – class X 2023-24 helps in finding out the input for the specific output. For example, if you want to know the number of units produced to get the desired output then use Goal seek analysis tool. Follow the following steps to use Goal seek tool. Step 1. Enter the values in the worksheet. Step 2. Write the formula in the cell where the calculation has to be used. Step 3. Place the cursor in the formula cell, choose Tools > Goal Seek. Step 4. The Goal seek dialog window will appear as shown in Fig. 4.27, the Formula cell box will have the correct formula. Step 5. Place the cursor on the Variable cell box and Fig. 4.27: Goal seek click on the cell that contains the value to be dialog window changed. Step 6. Enter the desired result in the Target value box. Step 7. Click on OK button. Practical Activity 4.9 Let us take a very simple example to perform the backward calculation to find out an input depending upon the specific output. A student has received marks in 4 subjects and has to appear for the 5th subject and plans an aggregate as 70. So, he can use goal seek tool to check how many marks he has to score in the 5th subject to get the required percentage. Follow the following steps to perform this task. Step 1. Create the marksheet as shown below in the Fig. 4.28. Enter the average function in cell B7 as = Average(B2:B6) Fig. 4.28: Marksheet ElEctronic SprEadShEEt (advancEd) uSing librEofficE calc 99 2023-24  notes Step 2. Click on Tools > Goal Seek will display the Goal seek dialog window as shown in Fig. 4.29. Fig. 4.29: Goal seek dialog window Step 4. Write the cell address of the formula, i.e. B7 in the Formula cell box. Step 5. Write the cell address of the cell which is to be generated by the system in the Variable cell box. In this example, it is cell B6 which we want to be filled as per the average marks needed. Step 6. Enter the result in the Target value box. As per our question, we have to get minimum 70 aggregate so we will write 70 in the target value. Step 7. Clicking on OK will display an error as LibreOffice Calc will not accept the Variable cell to be empty. So, this error dialog window will prompt to add 0 value in the variable cell as shown in Fig. 4.30. Fig. 4.30: Goal Seek failed alert Step 8. Click on Yes and then repeat the step 2 will display the dialog window and prompts whether to change the cell with the calculated value as shown in Fig. 4.31. Fig. 4.31: Goal Seek succeeded Step 9. Click on Yes will change the value with the calculated one in the sheet as shown in the Fig. 4.32. Fig. 4.32: Marksheet after applying Goal seek 100 Domestic Data entry operator – class X 2023-24  notes Practical Activity 4.10 Create the following spreadsheet (Fig. 4.33) to find out the quantity to be sold for the item Talc so as to get the total amount of 50000. Fig. 4.33: Practical Exercise Check Your Progress A. Multiple choice questions 1. Which of the following feature is not used for data analysis in spreadsheet? (b) Consolidating data (c) Goal Seek (d) Subtotal (e) Page layout 2. Which of the following office tool is known for data analysis? (a) Writer (b) Calc (c) Impress (d) Draw 3. Which of the following operations cannot be performed using LibreOffice Calc? (a) Store and manipulate data (b) Create graphical representation of data (c) Analysis of data (d) Mail merge 4. What is the extension of spreadsheet file in Calc? (a).odb (b).odt (c).odg (d).ods 5. The default function while using Consolidate is ____________.